Case 3 - Bobby's Popcorn (19 marks) Bobby Nolan established Bobby's Popcom in Hawthom, NV in 2016. He ships flavoured popcom in standard flavours such as plain and Kettle com and gourmet flavours such as grape and orange. Customers place their orders via the company's website and receive delivery in one to three days, depending on the shipping option they choose. Bobby wants to create a professional looking invoice he can use for each customer transaction. Bobby created the 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. 1. Open the Popcom worksheet. 2. In the 'Shipping Cost Table, assign the defined name Shipping Cost to the data in the range N4:08 3. In the Item column (B16:B26), use data validation to create a list of the items in the Product Pricing table. The input message should read Select from Product Pricing Table and the Error message should read "You made a mistake!". 4. In the first Price cell (G16), use the appropriate function to look up the price of the ordered item 5. Modify the formula in G16 by combining an IFERROR function with the existing function to display either the price of the item or a blank cell if an error value occurs. Copy this formula to the bottom of the invoice. 6. In the Total column, enter a formula to calculate the total charge for that row. Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. Copy this formula to the bottom of the invoice. 7. In the Subtotal cell, insert a formula to calculate the total charge for items ordered. Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. 8. In the sales tax cell, enter a formula that will display the sales tax so if the customer's state is NV, then calculate 6.85% of the subtotal. If the state is something other than NV, the sales tax will be zero. 9. In the Shipping cell, use the IF and vlookup functions to determine the shipping cost. If the subtotal is 0, the shipping cost should be 0. 10. In the Total Due cell, enter the correct formula. 11. Enter the following data into the worksheet: Gourmet (2) 15 Quantity: 2 Item 2: Plain Tin 1g Quantity: Item 1: 1 12. Save your workbook. 1 A 00 B C D E F G H J K L M N 1 2 Bobby's Popcorn Product Pricing 1024 South Main Hawthorne, Nevada 89145 (775) 555-1212 Shipping Cost Table Order Amount ($) Shipping Cost 5 $ 4.00 15 $ 8.50 25 $ 12.50 50 $ 16.00 100 $ Date Sold: 2020-07-07 Sold to: Lauri Bradford 3226 South Street Hawthorne NV 89415 Item Flavour Qty Price Total 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Item Price Standard (1 lg $15.00 Gourmet (019 #### Standard (2) 19 #### Gourmet (21g #### Standard (3) 19 #1 Gourmet (3] 1g Standard (1 2a Gourmet (1129 Standard 1212 Gourmet 122 Standard (312 Gourmet (329 Standard (139 Gourmet (139 Standard (213 Gourmet ( 239 Standard 1313 Gourmet (33 #### Plain tin la $5.00 Fancy tin ng $7.00 Plain tin 2a $ 8.00 Fancy tin 2 g $10.00 Plain tin 39 $ 11,00 Fancy tin 3 g $13.00 Notes: Subtotal Sales Tax Shipping Total Due Standard Popcorns Natural Buttered White Cheddar Cheesy Cheddar Kettlecorn Cajun Sour Cream 'n Chive Salt 'n Vinegar Caramel Gourmet Popcorns Too Hot to Handle Chipotle BBQ Nacho Cheese Cinnamon & Brown Sugar Grape Orange Key Lime Island Coconut Cookies & Cream Case 3 - Bobby's Popcorn (19 marks) Bobby Nolan established Bobby's Popcom in Hawthom, NV in 2016. He ships flavoured popcom in standard flavours such as plain and Kettle com and gourmet flavours such as grape and orange. Customers place their orders via the company's website and receive delivery in one to three days, depending on the shipping option they choose. Bobby wants to create a professional looking invoice he can use for each customer transaction. Bobby created the 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. 1. Open the Popcom worksheet. 2. In the 'Shipping Cost Table, assign the defined name Shipping Cost to the data in the range N4:08 3. In the Item column (B16:B26), use data validation to create a list of the items in the Product Pricing table. The input message should read Select from Product Pricing Table and the Error message should read "You made a mistake!". 4. In the first Price cell (G16), use the appropriate function to look up the price of the ordered item 5. Modify the formula in G16 by combining an IFERROR function with the existing function to display either the price of the item or a blank cell if an error value occurs. Copy this formula to the bottom of the invoice. 6. In the Total column, enter a formula to calculate the total charge for that row. Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. Copy this formula to the bottom of the invoice. 7. In the Subtotal cell, insert a formula to calculate the total charge for items ordered. Use the IFERROR function to display either the total charge or a blank cell if an error value occurs. 8. In the sales tax cell, enter a formula that will display the sales tax so if the customer's state is NV, then calculate 6.85% of the subtotal. If the state is something other than NV, the sales tax will be zero. 9. In the Shipping cell, use the IF and vlookup functions to determine the shipping cost. If the subtotal is 0, the shipping cost should be 0. 10. In the Total Due cell, enter the correct formula. 11. Enter the following data into the worksheet: Gourmet (2) 15 Quantity: 2 Item 2: Plain Tin 1g Quantity: Item 1: 1 12. Save your workbook. 1 A 00 B C D E F G H J K L M N 1 2 Bobby's Popcorn Product Pricing 1024 South Main Hawthorne, Nevada 89145 (775) 555-1212 Shipping Cost Table Order Amount ($) Shipping Cost 5 $ 4.00 15 $ 8.50 25 $ 12.50 50 $ 16.00 100 $ Date Sold: 2020-07-07 Sold to: Lauri Bradford 3226 South Street Hawthorne NV 89415 Item Flavour Qty Price Total 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Item Price Standard (1 lg $15.00 Gourmet (019 #### Standard (2) 19 #### Gourmet (21g #### Standard (3) 19 #1 Gourmet (3] 1g Standard (1 2a Gourmet (1129 Standard 1212 Gourmet 122 Standard (312 Gourmet (329 Standard (139 Gourmet (139 Standard (213 Gourmet ( 239 Standard 1313 Gourmet (33 #### Plain tin la $5.00 Fancy tin ng $7.00 Plain tin 2a $ 8.00 Fancy tin 2 g $10.00 Plain tin 39 $ 11,00 Fancy tin 3 g $13.00 Notes: Subtotal Sales Tax Shipping Total Due Standard Popcorns Natural Buttered White Cheddar Cheesy Cheddar Kettlecorn Cajun Sour Cream 'n Chive Salt 'n Vinegar Caramel Gourmet Popcorns Too Hot to Handle Chipotle BBQ Nacho Cheese Cinnamon & Brown Sugar Grape Orange Key Lime Island Coconut Cookies & Cream