calculate and explain in detail: 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, 20XX. 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 .5 pts for each cell that has a number typed in the cell. 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 $10,000 Accounts receivables (previous fourth quarter's sales) $61,200 Raw materials 653 Kits Finished Goods 510 Units Accounts payable(purchases made in previous fourth quarter) $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 18% Finished goods as a portion of the following quarter's sales 14% Manufacturing costs other than raw materials are paid in quarter 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 $44 $44.00 Direct labor hours at rate 0.6 hour $18 $10.80 Variable overhead/labor hour 0.6 hour $4 $2.40 Total Variable Standard cost per unit $57.20 Fixed overhead cost per quarter used cash $18,000 Manufacturing Depreciation per quarter $2,000 Selling and administrative costs are paid in quarter incurred unless it is an noncash expense Variable cost per unit $2 Fixed selling and administrative cost per quarter used cash $23,100 Selling and administrative depreciation per quarter $2,200 Portion of sales collected Collected in the quarter of sale 67% Subsequent quarter 32% Bad debts 1% Portion of purchases paid Paid in the quarter of purchases 66% Subsequent quarter 34% Unit selling price $120 Sales forecast Quarter First Second Third Fourth Unit sales 3,000 2,500 2,700 3,200 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 $18,000 to $14,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, 20XX First Quarter Second Quarter Third Quarter Fourth Quarter Annual Total Units 3,000 2,500 2,700 3,200 11,400 Sales price $120 $120 $120 $120 $480 Sales Budget $360,000 $300,000 $324,000 $384,000 $1,368,000 Budget #2 Computer Accessories Production Budget For the year ending December 31, 20XX First Quarter Second Quarter Third Quarter Fourth Quarter Annual Total Unit sales 3,000 2,500 2,700 3,200 11,400 Desired Ending Inventory 500 Total Requirements Less Beginning Inventory Budgeted Production in units Budget #3 Computer Accessories Purchases Budget For the year ending December 31, 20XX 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, 20XX 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, 20XX First Quarter Second Quarter Third Quarter Fourth Quarter Annual Total Budgets Sales Units from Budget #1 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, 20XX 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, 20XX 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 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 including a discussion of cost behavior. Contribution margin ratio Breakeven in Dollars Margin of Safety in Sales Dollars Comment on your results. #9. What if the company is able to lower the fixed Manufacturing overhead costs that uses cash per quarter from $18,000 to $14,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 annual net income