Required Construct a spreadsheet that will allocate overhead and calculate unit cost for each of these alternative drivers. A screen capture of the spreadsheet and data follows. 1 Chapter 5-Mastering Excel Possible Drivers 3 1 Unit 5 2 Setup 6 3 Machine hour 7 4 Labor hour 8 D E G H J K Name: Standard Deluxe Grand Total 10,000 100 15,000 20,000 45,000 500 400 1,000 20,000 45,000 60,000 125,000 4,000 9,000 16,000 29.000 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 Overhead Rate: 12 13 14 15 Overhead cost Units $500,000 45,000 = $ 11.11 per unit 16 17 Cost Standard Deluxe 18 Direct Materials $ 12,000 $ 36,000 Grand $ 80,000 Total $128,000 19 Direct Labor 20 Allocated Overhead 21 Total 800 111,111 $123,911 4,000 166,667 $206,667 3,200 222.222 $305,422 8,000 500,000 $636,000 22 23 Cost per unit $ 12.39 $ 13.78 1245 $ 15,27 Source: Microsoft Excel 2010 Spreadsheet Tips 1. This spreadsheet uses a function called vertical lookup. This function can pull the appropriate val- ues from a table. The form of this function is =VLOOKUP (value, table, column#). In this exam- ple, the table is in cells B4 to K7. Three examples of the use of VLOOKUP follow. 2. Cell C15 is =VLOOKUP (B15, B4:K7, 2). This function operates by using the one (1) in cell B15 to look up a value in the table. Notice that the table is defined as B4:K7 and that the function is looking up the value in the second column, which is Units. 3. Cell E15 is =VLOOKUP (B15, B4:K7, 10). In this case, the function is looking up the value in the tenth column, which is 45,000. Be sure to count empty columns. 4. Cell E20 is VLOOKUP (B15, B4:K7, 4)*G15. In this case, the function is looking up the value in the fourth column, which is $10,000. Be sure to count empty columns. 5. Cells 115, G20, and 120 also use the VLOOKUP function. 6. After completing the spreadsheet, you can change the value in cell B15 (1-4) to see the effect of choosing a different driver for overhead.