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 breakeven 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 correctly. Please make sure you return the Fixed manufacturing overhead costs that uses cash back to the original number before you submit your solution.

Computer Accessories assembles a computer networling device from kits of imported components You have been asked to develop a quarlerly and annual operaling budget anda pro-forma incoTe statements for te year ending December 31, 2019 You have oblained the following information Thave 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 thisworksheet to reference the data that is being inputted onto the budgets on the budget worksheet If you type in amy mbers in the solution, I will taike 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 cel efeences and fomulas in your budgets Your grade will be based on accuracy of your solution and comect usage of excel The budget worksheet has formadlexd budgets for you io complete. The beauty behind excel is that managers can perform whatif analysis just by changing the data, so you do not need to retype the budgels if you have used cel references and formulas throughout Beginning-af-year balances Cash $20,000 $61,200 53 Kts Accounts receivables (pevious quater's sales) Raw malerials Finished Goods 510 Unis Accounts payable $33,255 Desired end-af-year inventory balances Raw malerials 500 kits Finished goods 270 unls Desired end-ofquarter balances Raw malerials as a portions of the following quarter's production Finished goods as a portion of the folowing quartert's sales 20 % 15 % Manufactuing costs other than raw materials ae paid in month incured unless it is an noncash expense Tolal Unit Unit of price per cost per pul S50 Variable Standad cost per uni rput unit $50 Raw malerials 1 kt Direct labor hous atrale 08 hour $25 $10 $20 Variable oveteadabor hour 0.8 hou Total Variable Standard cost pr unt $78 FiREd ovtad cost per qunler $40,000 used cash Manulatuing Deprecialion per quarter 5000 Seling and administralive costs are paid in month incumed unlessst is an noncarsh expese Variable cost per unit Fxed selling and adrministralve Cost per qurter used cash Seng and adminisirudive dercialin per ter $25,000 $5,000 Additional information All cash payments except purchases areade quarterly as incurred. Portion of sales collected Collected in the quarter of sale Subsequent quter 70% 28% Bad debts 2% Portion of purchases paid Paid in the quater of prcherses Subseuent quater 35% Unil slling price $110 Sales forecast Qurter Unil sales Frst Second Third Fouth 3,200 2600 2,900 4,000 Requiredt Prepare and answer the following. Make sure you use cell referencing Hint Please note that the anmal total, doesnot necessarily mean that you should add aress, for example, review exhiit 22.13 since desired ening inventory is the inventory athe end of the period and beginning inventory is the beginning inveiony at the beginning of the period 1. Asaes budget for each quarterand the year 2 Aproduction budget for finished gonds of units each qarter and the year 3 Apuchases budget for raw meterial of kils each quater and the year 4. Amanufacturing cost budget for each quater and the year 5 Aseling and administrafive expense budget for each quarter and the year 6. Acash budget lor each quarter and the year 7. Apro-forrma contribution income statement for each quarter and the year Hint: You will need to compute Variable Cost af Goods Sold for each quarter, which is unit sold times trial Variable Standard cost per unit. 8 Using your information from #7, compute the annual contribulion magin, the breakeven in dollars for the year, and the margin of safety in dollars for the year Make sure you are using cel references. Comment on your resulls. . Whatif the company is able to lower the fixed Manfactuing overhead costs that usescash per quarter from $40,000 to$35,000. Which budgets wil change and what wil be the new annual inco You should only have to change the foxed manufactung overhead costs that uses cash on this worksheet and al the appropriate budgets wil change on the soluion worksheet if you have set up your cel relerences comectly. Please make sure you retun the Fxed manufactung ovehead costs tha uses cash back to the original nurmber before you subrmil your solution Computer Accessories assembles a computer networling device from kits of imported components You have been asked to develop a quarlerly and annual operaling budget anda pro-forma incoTe statements for te year ending December 31, 2019 You have oblained the following information Thave 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 thisworksheet to reference the data that is being inputted onto the budgets on the budget worksheet If you type in amy mbers in the solution, I will taike 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 cel efeences and fomulas in your budgets Your grade will be based on accuracy of your solution and comect usage of excel The budget worksheet has formadlexd budgets for you io complete. The beauty behind excel is that managers can perform whatif analysis just by changing the data, so you do not need to retype the budgels if you have used cel references and formulas throughout Beginning-af-year balances Cash $20,000 $61,200 53 Kts Accounts receivables (pevious quater's sales) Raw malerials Finished Goods 510 Unis Accounts payable $33,255 Desired end-af-year inventory balances Raw malerials 500 kits Finished goods 270 unls Desired end-ofquarter balances Raw malerials as a portions of the following quarter's production Finished goods as a portion of the folowing quartert's sales 20 % 15 % Manufactuing costs other than raw materials ae paid in month incured unless it is an noncash expense Tolal Unit Unit of price per cost per pul S50 Variable Standad cost per uni rput unit $50 Raw malerials 1 kt Direct labor hous atrale 08 hour $25 $10 $20 Variable oveteadabor hour 0.8 hou Total Variable Standard cost pr unt $78 FiREd ovtad cost per qunler $40,000 used cash Manulatuing Deprecialion per quarter 5000 Seling and administralive costs are paid in month incumed unlessst is an noncarsh expese Variable cost per unit Fxed selling and adrministralve Cost per qurter used cash Seng and adminisirudive dercialin per ter $25,000 $5,000 Additional information All cash payments except purchases areade quarterly as incurred. Portion of sales collected Collected in the quarter of sale Subsequent quter 70% 28% Bad debts 2% Portion of purchases paid Paid in the quater of prcherses Subseuent quater 35% Unil slling price $110 Sales forecast Qurter Unil sales Frst Second Third Fouth 3,200 2600 2,900 4,000 Requiredt Prepare and answer the following. Make sure you use cell referencing Hint Please note that the anmal total, doesnot necessarily mean that you should add aress, for example, review exhiit 22.13 since desired ening inventory is the inventory athe end of the period and beginning inventory is the beginning inveiony at the beginning of the period 1. Asaes budget for each quarterand the year 2 Aproduction budget for finished gonds of units each qarter and the year 3 Apuchases budget for raw meterial of kils each quater and the year 4. Amanufacturing cost budget for each quater and the year 5 Aseling and administrafive expense budget for each quarter and the year 6. Acash budget lor each quarter and the year 7. Apro-forrma contribution income statement for each quarter and the year Hint: You will need to compute Variable Cost af Goods Sold for each quarter, which is unit sold times trial Variable Standard cost per unit. 8 Using your information from #7, compute the annual contribulion magin, the breakeven in dollars for the year, and the margin of safety in dollars for the year Make sure you are using cel references. Comment on your resulls. . Whatif the company is able to lower the fixed Manfactuing overhead costs that usescash per quarter from $40,000 to$35,000. Which budgets wil change and what wil be the new annual inco You should only have to change the foxed manufactung overhead costs that uses cash on this worksheet and al the appropriate budgets wil change on the soluion worksheet if you have set up your cel relerences comectly. Please make sure you retun the Fxed manufactung ovehead costs tha uses cash back to the original nurmber before you subrmil your solution