Please read the Review Problem 1 instructions and explain how to which functions to use and how to use them to answer the questions to the corresponding excel screen shot

Item Price quoted Hors d'oeuvre Premium $ $ $ Salad Vegetable Vegetable Starch Pasta Entre Steak Entre Non-alcoholic Drinks Champagne Toast Description Shrimp Cocktail, Fried Goat Cheese, Bruschetta Caesar Baby Carrots Green Beans Red Potatoes Fettuccine Alfredo Ribeye Tea and Softdrinks Asti Bottle Beer Domestic & House White and Red Total for party 2.95 $ (265.50) 1.95 $ (175.50) 2.00 $ (180.00) 2.00 $ (180.00) 2.50 $ (225.00) 7.95 $ (715.50) 13.95 (1,255.50) 1.95 $ (175.50) 3.95 (355.50) $ $ $ 12.95 $ Beer and Wine (1,165.50) $ $ $ $ Budget for Event Food $ Amount Left $ Subtotal 7,000 Service Fee 2,087.80 Mandatory Gratuity Grand Total Deposit Required $ (4,693.50) 720.00 (938.70) (4,912.20) (982.44) $ $ Total Due at Event $ A (3,929.76) Review Problem 1 Practice adding Data Validation to Premiere's catering service price quote application: 1. On the Review1 Drop-Downs worksheet, create two named ranges: Service (A4:A10) and Item (C4:C19). 2. Select the Review1 worksheet. Find and fix the one data entry error. 3. Cell B29 is not calculating correctly. Fix the formula. 4. Set cell B10 to allow only future dates. 5. Set cell B11 to allow only events with 10 or more guests. Add a meaningful Input Message. 6. In cell B12, create a drop-down list of the service types listed on the Review1 Drop-Downs worksheet. Challenge: Use the INDIRECT function to accomplish this. 7. Set cells A15:A26 to contain a drop-down list populated by the Item named range on the Review1 Drop- Downs worksheet. 8. Set an appropriate Data Validation rule so that the entries made in cells C15:C26 do not allow cell B29 to become negative. 9. Set cell B28 to allow only decimal values greater than 0. Add a meaningful Error Alert. 10. Add a note to cell C28 that says the following: The service fee includes table preparation, buffet setup, cake table setup, and servers. 11. Unlock all the cells that have a light green background, and then protect the worksheet without a password. 12. Hide the Review1 Drop-Downs worksheet. Item Price quoted Hors d'oeuvre Premium $ $ $ Salad Vegetable Vegetable Starch Pasta Entre Steak Entre Non-alcoholic Drinks Champagne Toast Description Shrimp Cocktail, Fried Goat Cheese, Bruschetta Caesar Baby Carrots Green Beans Red Potatoes Fettuccine Alfredo Ribeye Tea and Softdrinks Asti Bottle Beer Domestic & House White and Red Total for party 2.95 $ (265.50) 1.95 $ (175.50) 2.00 $ (180.00) 2.00 $ (180.00) 2.50 $ (225.00) 7.95 $ (715.50) 13.95 (1,255.50) 1.95 $ (175.50) 3.95 (355.50) $ $ $ 12.95 $ Beer and Wine (1,165.50) $ $ $ $ Budget for Event Food $ Amount Left $ Subtotal 7,000 Service Fee 2,087.80 Mandatory Gratuity Grand Total Deposit Required $ (4,693.50) 720.00 (938.70) (4,912.20) (982.44) $ $ Total Due at Event $ A (3,929.76) Review Problem 1 Practice adding Data Validation to Premiere's catering service price quote application: 1. On the Review1 Drop-Downs worksheet, create two named ranges: Service (A4:A10) and Item (C4:C19). 2. Select the Review1 worksheet. Find and fix the one data entry error. 3. Cell B29 is not calculating correctly. Fix the formula. 4. Set cell B10 to allow only future dates. 5. Set cell B11 to allow only events with 10 or more guests. Add a meaningful Input Message. 6. In cell B12, create a drop-down list of the service types listed on the Review1 Drop-Downs worksheet. Challenge: Use the INDIRECT function to accomplish this. 7. Set cells A15:A26 to contain a drop-down list populated by the Item named range on the Review1 Drop- Downs worksheet. 8. Set an appropriate Data Validation rule so that the entries made in cells C15:C26 do not allow cell B29 to become negative. 9. Set cell B28 to allow only decimal values greater than 0. Add a meaningful Error Alert. 10. Add a note to cell C28 that says the following: The service fee includes table preparation, buffet setup, cake table setup, and servers. 11. Unlock all the cells that have a light green background, and then protect the worksheet without a password. 12. Hide the Review1 Drop-Downs worksheet