Part 2 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 Accounts receivables (previous quarter's sales) Raw materials $50,000 $61,200 653 Kits Finished Goods 510 Units Accounts payable $33,255 Desired end-of-year inventory balances Raw materials Finished goods 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 500 kits 270 units 20% 15% Manufacturing costs other than raw materials are paid in month incurred unless it is an noncash expense Unit Total price per cost per Unit of Variable Standard cost per unit input input 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 Manufacturing Depreciation per quarter $50,000 $10,000 Selling and administrative costs are paid in month incurred unless it is an noncash expense Variable cost per unit Fixed selling and administrative cost per quarter used cash Selling and administrative depreciation per quarter $6 $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 sale Subsequent quarter Bad debts 75% 24% 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 Budget #5 Budget #6 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 Computer Accessories Selling and Administrative Expenses Budget For the year ending December 31, 2017 Second First Quarter Quarter 3,400 Third Fourth Quarter 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 Computer Accessories Cash Budget For the year ending December 31, 2017 Second First Quarter Quarter Third Fourth Quarter Quarter Annual Total