Name: Type your name
here

Project 5 Save your file
using your first initial, last name, and name of project.

Upload to project 5 link.

This project covers material in Modules 21 and 22. I have extensive budget demonstrations in the
Module 22 folder.

Project 5 Objectives:

1. Develop cost-based
approach to pricing.

2. Develop operating
budgets

3. Analyze the cash
flow of the company.

4. Analyze how
changes in cost impact the budgets.

Grading Rubric:

Part 1

Each question worth 2 pts. each
10

Part 2

Budget 1 1

Budget 2 2

Budget 3 3

Budget 4 3

Budget 5 3

Budget 6 3

Budget 7 3

Question 8 1

Question 9 1

Total pts. possible for Part 2 20

Total points possible for project 5
30

Please note that I will deduct 10 pts. if you do not use
cell referencing on your budget solution by referencing the Part 2
worksheet.

Part 1 Relates to Module 21

Technology Inc. predicted 2017 variable and fixed costs are
as follows:

Variable
costs Fixed costs

Manufacturing 4,80,000 315900

Selling and Administrative 2,16,000 60500

Total 6,96,000 3,76,400

Technology Inc. produces a wide variety of computer
interface devices. Per unit

manufacturing cost information about one of these products,
a high-capacity flash drive is as follows:

Direct
material $10

Direct labor 9

Variable Manufacturing Overhead
7

Fixed
Manufacturing Overhead
9

Total
manufacturing costs $35

The following is the variable selling and administrative
costs for the flash drive:
$6

Management has set a 2017 target profit on the flash drive
of:
$2,50,000

Required:

1. Determine the
markup percentage on variable costs required to earn the desired profit

2. Use the variable
cost markup to determine a suggested selling price for a flash drive. You are determining selling price per
unit)

3. For the flash
drive, break the markup on variable costs into separate parts for fixed costs
and profit.

4. Explain what the
minimum unit selling price a company would use in special order decision, if
the company had excess capacity.

5. In the long run,
what would be the lowest unit selling price the company would sell for? Explain
your answer.

Part 2 relates to Module 22 especially the Review 22-4

Computer Accessories assembles a computer networking device
from kits of imported components.

You have been asked to develop a quarterly and annual
operating budget and a pro-forma income statements for the year ending December
31, 2017.

You have obtained the following information:

I have a template set up on the Part2BudgetSolution
worksheet that you should use to complete the required budgets.

You need to use cell references in the development of your
budgets.

You must use this worksheet to reference the data that is
being inputted onto the budgets on the budget worksheet.

If you type in any numbers in the solution, I will take off
10 pts., since we use Excel so that we can update budgets or do what if
analysis without retyping numbers.

You should use this worksheet as your data field and only
use cell references and formulas in your budgets.

Your grade will be based on accuracy of your solution and
correct usage of excel. The budget worksheet has formatted budgets for you to
complete.

The beauty behind excel is that managers can perform what-if
analysis just by changing the data, so you do not need to retype the budgets if
you

have used cell references and formulas throughout.

Beginning-of-year balances

Cash
$50,000

Accounts receivables (previous quarter’s sales) $61,200

Raw
materials
653 Kits

Finished Goods 510 Units

Accounts
payable
$33,255

Desired end-of-year inventory balances

Raw
materials
500 kits

Finished goods 270 units

Desired end-of-quarter balances

Raw
materials as a portions of the following quarter’s production
20%

Finished goods as a portion of the following quarter’s sales
15%

Manufacturing costs other than raw materials are paid in
month incurred unless it is an noncash expense

Variable Standard cost per unit
Unit of input
Unit price per input Total
cost per unit

Raw
materials 1 kit $50 $50

Direct
labor hours at rate 0.8 hour $25
$20

Variable overhead/labor hour
0.8 hour $10 $8

Total
Variable Standard cost per unit $78

Fixed
overhead cost per quarter used cash
$50,000

Manufacturing Depreciation per quarter
$10,000

Selling and administrative costs are paid in month incurred unless it is an
noncash expense

Variable cost per unit $6

Fixed selling and administrative
cost per quarter used cash
$25,000

Selling and administrative depreciation per quarter $5,000

Additional information:
All cash payments except purchases are made quarterly as incurred.

Portion of sales collected

Collected in the quarter of sale 75%

Subsequent quarter 24%

Bad
debts
1%

Portion of purchases paid

Paid
in the quarter of purchases
70%

Subsequent quarter 30%

Unit selling price $150

Sales forecast

Quarter First Second Third Fourth

Unit
sales 3,400 2,500
3,000 4,100

Required: Prepare and answer the following. Make sure you use cell referencing

1. A sales budget for
each quarter and the year.

2. A production
budget for finished goods of units each quarter and the year.

3. A purchases budget
for raw material of kits each quarter and the year.

4. A manufacturing
cost budget for each quarter and the year.

5. A selling and
administrative expense budget for each quarter and the year.

6. A cash budget for
each quarter and the year.

7. A pro-forma
contribution income statement for each quarter and the year.

Hint: You will need
to compute Variable Cost of Goods Sold for each quarter, which is unit sold
times total Variable Standard cost per unit.

8. Using your
information from #7, compute the Breakeven in dollars for the year. Make sure you are using cell
references.

Hint: Compute the
annual contribution margin ratio.

9. What if the
company is able to lower the fixed Manufacturing overhead costs that uses cash
per quarter from $50,000 to $45,000.
Which budgets will change and what will be the new annual income?

You should only have to change the fixed manufacturing
overhead costs that uses cash on this worksheet and all the appropriate budgets
will change on the solution worksheet if you have

set up your cell references correctly. Please make sure you return the Fixed
manufacturing overhead costs that uses cash back to the original number before
you submit your solution.

Budget #1
Computer Accessories

Sales Budget

For the year ending December 31, 2017

First
Quarter Second Quarter Third Quarter Fourth Quarter Annual Total

Units 3,400

Sales
price $150

Sales
Budget $5,10,000

Budget #2
Computer Accessories

Production
Budget

For the year ending December 31, 2017

First
Quarter Second Quarter Third Quarter Fourth Quarter Annual Total

Unit
sales 3,400

Desired Ending Inventory

Total
Requirements

Less
Beginning Inventory

Budgeted Production in units

Budget #3
Computer Accessories

Purchases Budget

For the year ending December 31, 2017

First Quarter Second Quarter Third Quarter Fourth Quarter Annual Total

Budgeted production in units

Desired ending inventory of kits

Total
Requirements

Less
Beginning Inventory Kits

Purchase requirements in kits

Cost
per kit

Total
purchases of kits in dollars

Budget #4
Computer Accessories

Manufacturing
Cost Budget

For the year ending December 31, 2017

First
Quarter Second Quarter Third Quarter Fourth Quarter Annual Total

Direct
Materials:

Budgeted production in units

Cost
per kit

Direct
material cost

Direct
labor:

Budgeted production in units

Direct
labor cost per unit

Direct
labor cost

Variable Manufacturing
Overhead:

Budgeted production in units

Variable Overhead cost per unit

Variable Manufacturing Overhead

Fixed
Manufacturing Overhead:

Total
Manufacturing Costs

Budget #5
Computer Accessories

Selling and Administrative Expenses Budget

For the year
ending December 31, 2017

First
Quarter Second Quarter Third Quarter Fourth Quarter Annual Total

Budgets Sales Units from Budget
#1 3,400

Budgets Sales Dollars from Budget
#1

Variable selling and admin. expenses:

Bad
Debts

Variable selling and admin. expenses

Total
variable selling and admin. Expenses

Fixed
Selling and administrative Expenses

Total
selling and administrative expenses

Budget #6
Computer Accessories

Cash Budget

For the year ending December 31, 2017

First
Quarter Second Quarter Third Quarter Fourth Quarter Annual Total

Cash
Balance, Beginning

Collection on sales:

Collected in current quarter

Collected
in subsequent quarter

Total
collection on sales

Cash
available for operations

Cash
Disbursements:

Purchases:

Paid
in Current quarter

Paid
in subsequent quarter

Direct
labor

Variable Manufacturing Overhead

Fixed
Manufacturing Overhead

Variable selling and administrative expenses

Fixed
selling and administrative expenses

Total
Disbursements

Cash
Balance, Ending

Budget #7
Hint: You will need to compute
Variable Cost of Goods Sold for each quarter, which is unit sold times total
Variable Standard cost per unit.

Computer Accessories

Pro Forma Contribution Margin

For the year ending December 31, 2017

First
Quarter Second Quarter Third Quarter Fourth Quarter Annual Total

Sales
Revenue

Less
Variable Costs:

Cost
of Goods sold

Selling and Administrative expenses

Total
Variable costs

Contribution margin

Less
Fixed Costs:

Manufacturing Overhead

Selling and Administrative Expenses

Total
Fixed Costs

Net
Income

8. Using your
information from #7, compute the Breakeven in dollars for the year. Make sure you are using cell
references.

Hint: Compute the
annual contribution margin ratio.

Contribution margin ratio

Breakeven in Dollars

9. What if the
company is able to lower the fixed Manufacturing overhead costs that uses cash
per quarter from $50,000 to $45,000.
Which budgets will change and what will be the new annual income?

You should only have to change the fixed manufacturing
overhead costs that uses cash on this worksheet and all the appropriate budgets
will change on the solution worksheet if you have

set up your cell references correctly. Please make sure you return the Fixed
manufacturing overhead costs that uses cash back to the original number before
you submit your solution.

The following budgets would change:

New net income: