Part 2 Computer Accessories assembles a computer networking

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 $50,000 $61,200 Cash Accounts receivables (previous quarter\'s sales) Raw materials Finished Goods Accounts payable 653 Kits 510 Units $33,255 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%

Solution

Budget #1 Computer Accessories Sales Budget For the year ending December 31, 2017 First Second Third Fourth Total Quarter Quarter Quarter Quarter Units 3400 2500 3000 4100 13000 Sales price($) 150 150 150 150 150 Sales Budget 510000 375000 450000 615000 1950000 Budget #2: Computer Accessories Production Budget For the year ending December 31, 2017 First Second Third Fourth Total Quarter Quarter Quarter Quarter Sales units 3400 2500 3000 4100 13000 Desired ending inventory of units 375 450 615 270 270 (15% of following qrtr sales) Units required 3775 2950 3615 4370 13270 Less: Beginning inventory 510 375 450 615 510 Budgeted production in units 3265 2575 3165 3755 12760 Budget #3: Computer Accessories Purchase Budget For the year ending December 31, 2017 First Second Third Fourth Total Quarter Quarter Quarter Quarter Budgeted production in units 3265 2575 3165 3755 12760 Desired ending inventory of kits 515 633 751 500 500 (20% of following qrtr production) Kits required 3780 3208 3916 4255 13260 Less: Beginning inventory 653 515 633 751 653 Budgeted production in units 3127 2693 3283 3504 12607 Cost per kit 50 50 50 50 50 Total purchase of kits in dollars 156350 134650 164150 175200 630350 Budget #4: Computer Accessories Manufacturing cost Budget For the year ending December 31, 2017 First Second Third Fourth Total Quarter Quarter Quarter Quarter Direct materials: Budgeted production units 3265 2575 3165 3755 12760 Cost per kit 50 50 50 50 50 Direct material cost 163250 128750 158250 187750 638000 Direct labor: Budgeted production units 3265 2575 3165 3755 12760 Budgeted labor cost per unit 20 20 20 20 20 Direct labor cost 65300 51500 63300 75100 255200 Variable manufacturing cost: Budgeted production units 3265 2575 3165 3755 12760 Variable overhead cost per unit 8 8 8 8 8 Total variable overhead cost 26120 20600 25320 30040 102080 Fixed manufacturing overhead Cash 50000 50000 50000 50000 200000 Depreciation 10000 10000 10000 10000 40000 Total fixed manufacturing overhead 60000 60000 60000 60000 240000 Total manufacturing overhead 86120 80600 85320 90040 342080 Total manufacturing cost 314670 260850 306870 352890 1235280 Budget #5: Computer Accessories Selling and administrative expenses budget For the year ending December 31, 2017 First Second Third Fourth Total Quarter Quarter Quarter Quarter Budgeted sales units from budget#1 3400 2500 3000 4100 13000 Budgeted sales dollars from budget#1 510000 375000 450000 615000 1950000 Variable selling and administration expenses: Bad debts (1% of sales dollars) 5100 3750 4500 6150 19500 Variable cost (@$6 per unit of sales) 20400 15000 18000 24600 78000 Total variable selling and administrative exp. 25500 18750 22500 30750 97500 Fixed selling and administration expenses: Cash expenses 25000 25000 25000 25000 100000 Depreciation 5000 5000 5000 5000 20000 Total fixed selling and administrative exp. 30000 30000 30000 30000 120000 Total selling and administrative expenses 55500 48750 52500 60750 217500 Budget #6 : Computer Accessories Cash Budget For the year ending December 31, 2017 First Second Third Fourth Annual Quarter Quarter Quarter Quarter Budget Cash balance , beginning 50000 164180 264570 355150 50000 Collection on sales: Collection in current quarter (75%) 382500 281250 337500 461250 1462500 Collection in subsequent quarter (24%) 61200 122400 90000 108000 381600 Total collections on sales 443700 403650 427500 569250 1844100 Cash available for disbursements 493700 567830 692070 924400 1894100 Cash disbursements:    Purchases:      Paid in the quarter (70%) 109445 94255 114905 122640 441245      Paid in subsequent quarter (30%) 33255 46905 40395 49245 169800    Direct labor 65300 51500 63300 75100 255200    Variable manufacturing overhead 26120 20600 25320 30040 102080    Fixed manufacturing overhead 50000 50000 50000 50000 200000    Variable selling and administrative expenses 20400 15000 18000 24600 78000    Fixed selling and administrative expenses 25000 25000 25000 25000 100000 Total Disbursements 329520 303260 336920 376625 1346325 Cash balance , ending 164180 264570 355150 547775 547775 Budget #6 : Computer Accessories {proforma Contribution Margin For the year ending December 31, 2017 First Second Third Fourth Annual Quarter Quarter Quarter Quarter Budget Sales revenue 510000 375000 450000 615000 1950000 Less: Variable costs:             Cost of goods sold * 265200 195000 234000 319800 1014000             Selling and administrative expenses 25500 18750 22500 30750 97500             Total variable costs 290700 213750 256500 350550 1111500 Contribution margin 219300 161250 193500 264450 838500 Less: Fixed costs:             Cost of goods sold 49470 65850 72870 33090 221280             Selling and administrative expenses 30000 30000 30000 30000 120000             Total fixed costs 79470 95850 102870 63090 341280 Net income 139830 65400 90630 201360 497220 Variable cost of goods sold Sales units 3400 2500 3000 4100 13000 Total standard variable cost per unit 78 78 78 78 78 Cost of goods sold * 265200 195000 234000 319800 1014000