EXCEL

Spaziani Company makes three types of exercise machines. Data have been accumulated for four possible overhead drivers. Data for these four possible drivers are shown in rows 3 to 7 of the following spreadsheet Required Construct a spreadsheet that will allocate overhead and calculate unit cost for each of those alternative drivers. A screen capture and data follows. Your spreadsheet should look like the one below. DO E3 Excel2 Example Microsoft Excel File Home Insert Page Layout Formulas Data Review View Acrobat Verdana 10 General A Insert - BIU AA EE $ - % * Delete Paste Styles Sort & Find & A Format Filter - Select - Clipboard Font Alignment Number Cells Editing B16 A A B D E F G H 1 1 Chapter 6 - Mastering Excel Name: 2 3 Possible Drivers Standard Deluxe Grand Total 4 1 Unit 10,000 15,000 20,000 45,000 5 2 Setup 100 500 400 1,000 6 3 Machine hour 20,000 45,000 60,000 125,000 7 4 Labor hour 4,000 9,000 16,000 29,000 B 9 Determine the effect of different cost drivers on overhead allocated and the resulting cost per unit. 10 To change the cost driver, insert the appropriate number (1-4) from the list above in cell B15. 11 12 Overhead Rate: 13 14 Overhead cost $500,000 15 Unit 45,000 = 11.11 per Unit 16 17 Cost Standard Deluxe Grand Total 18 Direct Materials $ 12,000 $ 36,000 $ 80,000 $ 128,000 19 Direct Labor 800 4,000 3,200 20 8,000 Allocated Overhead 111,111 166,667 222,222 500,000 21 Total $ 123,911 $ 20.667 $ 305,422 22 $636,000 23 Cost per unit $ 12.39 $ 13.78 $ 15.27 24 25 26 27 28 29 Sheet1 Ready 100%