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 quarterty 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 cel 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 behid excel is that managers can perform what f analysis just by changng the data, so you do not nood to retype the budgets if you have used cell references and formulas throughout Beginning-of-year balances $40,000 $61,200 Cash Accounts receivables (previous quarter's sales Raw materials Finished Goods Accounts 653 Kits 510 Units payable Desired end-of-year inventory balances Raw materials Finished goods 500 kits 270 units Desired end-of-quarter balances Raw materials as a portions of the following quarter's production Finished goods as a portion of the following quarter's sales 20% 15% Manufacturing costs other than raw materials are paid in month incurred unless it is an noncash expense Unit of nput Unit price Total cost per input per unit Variable Standard cost per unit Raw materials Direct labor hours at rate Variable overheadabor hour Total Variable Standard cost per unit ikit 0.8 hour 0.8 hour $10 78 Fixed overhead cost per quarter used cash $50,000 Manufacturing Depreclation per quarter $10,000 Seling and administrative costs are paid in month incurred unless it is an noncash expense Variable cost per unit Fixed seling and administrative cost per quarter used cash Selling and administrative depreciation per quarter S6 25,000 $5,000 Additional information: All cash payments except purchases are made quarterly as incurred. Portion of sales collected Collected in the quarter of sake Subsequent quarter Bad debts 70% 28% 2% Portion of purchases paid Paid in the quarter of purchases Subsequent quarter 65% 35% Unit selling price $120 Sales forecast 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 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 ond of the period and beginning inventory is the beginning inventory at the beginning of the period. 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 wil be the new annual income? You should only have to change the fxed 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 cel references correctly Please make sure you return the Fixed manufacturing overhead costs that uses cash back to the orginal number before you submit your solution. 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 quarterty 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 cel 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 behid excel is that managers can perform what f analysis just by changng the data, so you do not nood to retype the budgets if you have used cell references and formulas throughout Beginning-of-year balances $40,000 $61,200 Cash Accounts receivables (previous quarter's sales Raw materials Finished Goods Accounts 653 Kits 510 Units payable Desired end-of-year inventory balances Raw materials Finished goods 500 kits 270 units Desired end-of-quarter balances Raw materials as a portions of the following quarter's production Finished goods as a portion of the following quarter's sales 20% 15% Manufacturing costs other than raw materials are paid in month incurred unless it is an noncash expense Unit of nput Unit price Total cost per input per unit Variable Standard cost per unit Raw materials Direct labor hours at rate Variable overheadabor hour Total Variable Standard cost per unit ikit 0.8 hour 0.8 hour $10 78 Fixed overhead cost per quarter used cash $50,000 Manufacturing Depreclation per quarter $10,000 Seling and administrative costs are paid in month incurred unless it is an noncash expense Variable cost per unit Fixed seling and administrative cost per quarter used cash Selling and administrative depreciation per quarter S6 25,000 $5,000 Additional information: All cash payments except purchases are made quarterly as incurred. Portion of sales collected Collected in the quarter of sake Subsequent quarter Bad debts 70% 28% 2% Portion of purchases paid Paid in the quarter of purchases Subsequent quarter 65% 35% Unit selling price $120 Sales forecast 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 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 ond of the period and beginning inventory is the beginning inventory at the beginning of the period. 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 wil be the new annual income? You should only have to change the fxed 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 cel references correctly Please make sure you return the Fixed manufacturing overhead costs that uses cash back to the orginal number before you submit your solution