Beasley 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 these alternative drivers. A screen capture of the spreadsheet and data follows.

Spreadsheet Tips
1. This spreadsheet uses a function called vertical lookup. This function can pull the appropriate values from a table. The form of this function is 5VLOOKUP (value, table, column#). In this example, the table is in cells B4 to K7. Three examples of the use of VLOOKUP follow.
2. Cell C15 is 5VLOOKUP (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 5VLOOKUP (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 5VLOOKUP (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 I15, G20, and I20 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.

Transcribed Image Text:

Deluxe 15,000 Possible Drivers 1 Unit 2 Setup 3 Machine hour 4 Labor hour Total 45,000 1,000 125,000 29,000 Grand 20,000 Standard 10,000 100 20,000 4,000 500 400 45,000 9,000 60,000 16,000 Determine the effect of different cost drivers on overhead allocated and the resulting cost per unit. To change the cost driver, insert the appropriate number (1-4) from the list above in cel B15. Ovarhead Rate: Overhead cost Units $500,000 45,000 $ 11.11 per urit = Cost Direct Materials Standard Deluxe Grand $ 80,000 3,200 222,222 $ 305,422 Total $ 128,000 8,000 500,000 $ 636,000 $ 36,000 4,000 166 667 $ 206,667 $ 12,000 Direct Labor 800 Allocated Overhead 111,111 $ 123,911 Total $ 12.39 $ 13.78 $ 1527 Cost per unit