Case Problem 1 Data File needed for this Case Problem: Popcorn.xlsx Ricky's Popcom Ricky Nolan established Ricky's Popcom in Hawthorne, Nevada, in 2000. He ships flavored popcorn in standard flavors such as plain and kettle corn and gourmet flavors such as grape and orange. Customers place their orders via the company website and then receive their popcorn in one to three days, depending on the shipping option they choose. Ricky wants to create a professional-looking invoice he can use for each customer transaction. Ricky used Excel to create the invoice layout and wants you to add formulas to calculate the price per item, sales tax shipping and invoice total based on existing tables for pricing and shipping. Complete the following: 1. Open the Popcorn workbook located in the Excel8 > Case folder included with your Data Files, and then save the workbook as Rickys Popcorn in the location specified by your instructor 2. In the Documentation worksheet, enter your name and the date. 3. In the Pricing and Shipping worksheet, assign the defined name Shipping Cost to the data in the range D3: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 Customer Invoice worksheet in the Item column (range B16:326), use data validation to create a list of the items in the Product Pricing table in the Pricing and Shipping worksheet. (Hint: Select the entire range before setting the validation rule.) 5. In the Flavor column (range E16:E26), use data validation to create an input message indicating that the popcorn flavor should be entered. The flavors are located below the invoice. 6. In the Price cell (cell G16), use a VLOOKUP function to retrieve the price of the ordered item listed in the Product Pricing table in the Pricing and Shipping worksheet. (Hint: Use the defined name ProductPrice that was assigned to the Product Pricing table.) When no item is selected, this cell will display an error message. 7. Modify the formula in the cell G16 by combining the IFERROR function with the VLOOKUP function to display either the price or a blank cell if an error value occurs. Copy the formula down the range G16:G26. 8. In the Total column (range H16:H26), enter a formula to calculate the total charge for that row (Qty x Price). Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. 9. In the Subtotal cell (cell H27), 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. 10. In the Sales Tax cell (cell H28), enter a formula with an IF function so if the customer's state (cell C12) is NV, then calculate 6.85 percent of the subtotal (cell H27); otherwise, use 0 for the sales tax. (Hint: The defined name State is assigned to cell C12, and the defined name Sub_Total is assigned to cell H27. Note that the defined name "Sub_Total" is intentionally not spelled as "Subtotal," which is the name of an Excel function.) 11. In the Shipping cell (cell H29), enter a formula that nests the VLOOKUP function in an IF function to look up the shipping cost from the Shipping Cost table in the Pricing and Shipping worksheet based on the subtotal in cell H27. If the subtotal is o, the shipping cost should display 0. (Hint: Use the defined name you created for the Shipping Cost table data.) 12. In the Total Due cell (cell H30), calculate the invoice total by entering a formula that adds the values in the Subtotal, Sales Tax, and Shipping cells. a 13. Test the worksheet by using the following order data: Sold to: Laurl Bradford Street: 3226 South Street City, State Zip: Hawthorne, NV 89415 Date: 12/1/2017 Item 1: Gourmet (2) 18 Flavor: Nacho Cheese Quantity 2 Item 2: Plain Tin 18 Quantity: 1 14. Save the workbook, and then close it. A B D E F G H I 1 2 3 4 Ricky's Popcorn 1024 South Main Hawthorne, Nevada 89145 (775) 555-1212 5 Date Sold: 6 7 8 Sold to: Item Flavor Qty Price Total 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Notes: Subtotal Sales Tax