Spreadsheet Assignment Mastering Excel 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 VLOOKUP (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 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 Unit.
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 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.