apter 5 cel ATC 5-7 Spreadsheet Assignment Mastering Excel Beasley Company makes three types of exercise machines. Data have been accumulated for four possi- ble overhead drivers. Data for these four possible drivers are shown in rows 4 to 7 of the following spreadsheet. 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. A 1 Chapter 5-Mastering Excel 3 Possible Drivers 4 1 Unit 5 2 Setup B 3 Machine hour 7 4 Labor hour 8 D E G K Name Standard Delure Grand Tota 10,000 15,000 20,000 45,000 100 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 815 11 Overhead Rate 12 13 14 15 1 Overhead cost Units $500,000 45,000= $ 11.11 per unit 10 17 Cost Standard 18 Direct Materials $12,000 Deluxe $36,000 Grand $ 80,000 Total $128,000 19 Direct Labor 800 20 Allocated Overhead 21 Total 111.111 $123,911 4,000 166.667 3,200 8,000 $206,687 222.222 $305,422 500.000 $630,000 22 23 Cost per unit $ 12.30 $ 13.78 $ 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.