Data File needed for this Case Problem: Spirit.xlsx 

  

The Spirit Store Alice Meachen established The Spirit Store, which sells products to loyal alums of Central State College. Products offered by Alice on her website range from tee shirts and backpacks to mugs and blankets—and all feature the school’s logo. Alice has a large, steady base of clients who find these uniquely designed products a great reminder of their college days. To ensure the timely receipt of payments, Alice wants you to use Excel to create an invoice she can use for each customer transaction. Complete the following: 

  

1. Open the Sprit workbook located in the Excel8 ? Case1 folder included with your Data Files, and then save the workbook as Spirit Store. 

  

2. In the Documentation worksheet, enter your name and the date.

  

 3. In the Product Pricing and Shipping worksheet, assign the defined name ShippingCost to the data stored in the range D2:E7, which can be used for an approximate match lookup. (Hint: The lookup table includes only the values, not the descriptive labels.)

  

 4. In the Invoice worksheet, use data validation to make it easier to enter ordered items in the range C16:C36 by creating a list of the different items in the Product Pricing table in the Product Pricing and Shipping worksheet. (Hint: Select the entire range before setting the validation rule.) 

  

5. In the Per Unit column (the range G16:G36), use a VLOOKUP function to retrieve the per-unit price of each ordered product from the Product Pricing data in the range A3:B28 in the Product and Shipping worksheet. (Hint: Use the defined name ProductPrice that was assigned to the Product Pricing data.) 

  

6. Modify the formula in the Per Unit column by combining the IFERROR function with the VLOOKUP function to display either the per-unit price or a blank cell if an error value occurs. 

  

7. In the Total column (the range H16:H36), enter a formula to calculate the total charge for that row (Qty × Per Unit). Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. Format the column appropriately. 

  

8. In the Subtotal cell (cell H37), add a formula to sum the Total column. Use the IFERROR function to display either the subtotal or a blank cell if an error value occurs. Format this cell appropriately. 

  

9. In the Sales Tax cell (cell H38), enter a formula with nested IF functions to calculate 8.25 percent of the subtotal (cell H37) if the customer’s state (cell D12) is OH, or 8.75 percent if the state is MI; otherwise, use 0 percent for the sales tax. Format this cell appropriately. (Hint: The defined name Subtotl is assigned to cell H37. Note that the defined name “Subtotl” is intentionally not spelled as “Subtotal,” which is the name of an Excel function. The defined name State is assigned to cell D12.) 

  

10. In the Shipping cell (cell H39), enter a formula that looks up the shipping cost from the Shipping Cost table in the Product Pricing and Shipping worksheet based on the subtotal in cell H37. If the subtotal is 0, the shipping cost should display 0. Format this cell appropriately. (Hint: Use the defined name you created for the Shipping Cost table data.) 

  

11. In the Total Due cell (cell H40), calculate the invoice total by entering a formula that adds the values in the Subtotal, Sales Tax, and Shipping cells. Format this cell appropriately. 

  

12. Test the worksheet using the following data:  

  

      

  

13. Save the workbook, and then close it.                                                         

   
 

Sold to: Date: Items ordered: Ellen Farmer 222 Central Avenue Arlington, MI 60005 6/15/2016 Blanket, 2 Duffle Bag - Large, 1 Scarf, 2