Solution
Akash answered on
Nov 15 2021
OverviewFinalProject
Name: Type your name here
Final Project-Due the last day of the term. No projects will be accepted after the term is over.
Type in your name here:
Upload to Final Project link.
Save your file using your first initial, last name, and name of project.
There are 4 different parts to the final project. Make sure you click on the applicable part for specific requirements.
Final Project Objectives:
1. Develop operating and cash budgets for quarter and annual basis.
2. Analyze the
eakeven of company.
3. Analyze how changes in cost impact the budgets.
4. Prepare a flexible budget and performance report.
5. Formulate and interpret the variable cost variances.
6. Develop a summary of article on cu
ent management applications.
7. Design a segmented income statement.
8. Develop a recommend to management on segment analysis.
Grading Ru
ic for Final Project:
Part 1-Must use cell referencin to earn credit
#1 2
#2 2
#3 2
#4 3
#5 2
#6 3
#7 2
#8 2
#9 2
Total points possible 20
Part 2--Must use cell reference to earn credit
Each Variance calculation worth -.75 each 5.25
Each U or F designation-.75 each 5.25
Comments 1
Total points possible 11.5
Part 3--Must use cell reference to earn credit
Each number in revised cost control report-.3 pt. each 6
U or F variance designation-1 pt. each 6
.75 pt. for calculation of revenue variances and .75pt. for designation 4.5
Comments 1
Total points possible 17.5
Part 4
Article summary-3 points 3
Segmented Income statement .5 for each calculation and design of segmented income statement 5
Response to each questions 1 p. each 3
Total points possible 11
Total points for Final project 60
Part 1
Part 1 relates to Module 22 make sure you take time to Review 22-4, which also has a video in MBC that walks you through the review.
I have budget support material in the Module 22 folder in course resources.
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, 2019.
You have obtained the following information:
I have a template set up on the Part1BudgetSolution 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 co
ect 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 $20,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 incu
ed 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 $40,000
Manufacturing Depreciation per quarter $5,000
Selling and administrative costs are paid in month incu
ed 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 incu
ed.
Portion of sales collected
Collected in the quarter of sale 70%
Subsequent quarter 28%
Bad debts 2%
Portion of purchases paid
Paid in the quarter of purchases 65%
Subsequent quarter 35%
Unit selling price $110
Sales forecast
Quarter First Second Third Fourth
Unit sales 3,200 2,600 2,900 4,000
Required: Prepare and answer the following. Make sure you use cell referencing
Hint: Please note that the annual total, does not necessarily mean that you should add across, for example, review exhibit 22.13 since desired ending inventory
is the inventory at the end of the period and beginning inventory is the beginning inventory at the beginning of the period.
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 annual contribution margin, the
eakeven in dollars for the year, and the margin of safety in dollars for the year.
Make sure you are using cell references. Comment on your results.
9. What if the company is able to lower the fixed Manufacturing overhead costs that uses cash per quarter from $40,000 to $35,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 co
ectly. Please make sure you return the Fixed manufacturing overhead costs that uses cash back to the original number before you submit your solution.
Part1BudgetSolution
Budget #1 Computer Accessories
Sales Budget
For the year ending December 31, 2019
First Quarter Second Quarter Third Quarter Fourth Quarter Annual Total
Units 3,200 2,600 2,900 4,000 12,700
Sales price $110 $110 $110 $110 $110
Sales Budget $352,000 $286,000 $319,000 $440,000 $1,397,000
Budget #2 Computer Accessories
Production Budget
For the year ending December 31, 2019
First Quarter Second Quarter Third Quarter Fourth Quarter Annual Total
Unit sales 3,200 2,600 2,900 4,000 12,700
Desired Ending Inventory 520.0 580.0 800.0 500 500
Total Requirements 3,720 3,180 3,700 4,500
Less Beginning Inventory $ (653.00) $ (520.00) $ (580.00) $ (800.00) $ (653.00)
Budgeted Production in units 3,067 2,660 3,120 3,700 12,547
Budget #3 Computer Accessories
Purchases Budget
For the year ending December 31, 2019
First Quarter Second Quarter Third Quarter Fourth Quarter Annual Total
Budgeted production in units 3,067 2,660 3,120 3,700 12,547
Desired ending inventory of kits 520.0 580.0 800.0 500.0 500.0
Total Requirements 3,587 3,240 3,920 4,200 13,047
Less Beginning Inventory Kits $ (653.00) $ (520.00) $ (580.00) $ (800.00) $ (653.00)
Purchase requirements in kits 2,934 2,720 3,340 3,400 12,394
Cost per kit $50 $50 $50 $50
Total purchases of kits in dollars $ 146,700 $ 136,000 $ 167,000 $ 170,000 $ 619,700
Budget #4 Computer Accessories
Manufacturing Cost Budget
For the year ending December 31, 2019
First Quarter Second Quarter Third Quarter Fourth Quarter Annual Total
Direct Materials:
Budgeted production in units 3,067 2,660 3,120 3,700 12,547
Cost per kit $50 $50 $50 $50 $50
Direct material cost $ 153,350 $ 133,000 $ 156,000 $ 185,000 $ 627,350
Direct labor:
Budgeted production in units 3,067 2,660 3,120 3,700 12,547
Direct labor cost per...