C2-1. Open the Sauce workbook located in your Final Project folder and then save the workbook as Sauce Shoppe as a macro-enabled workbook in your Final Project folder.

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

Create a new query to the Sauce Shoppe Products Access database located in your Final Project folder and import the Product List table into cell A4 of the Product List worksheet.

Add the description Product list imported from the Product List table in the Sauce Shoppe Products database to the query properties for the product data. Have Excel refresh this data whenever the workbook is reopened.

Ensure the name of the Excel table containing the product data is Product List.

Change the table style to Orange Table Style Medium 6.

In the Order Form worksheet, in cell B26, enter a formula that returns the current date.

In cell B27, add a data validation to limit the possible type of delivery values in the cell to Standard, 3 Day, 2 Day, or Overnight based on the values in the range A14:A17 of the Delivery Calculator worksheet.

In cell B28, calculate the number of workdays to deliver the order based on the lookup table in the range A14:C17 of the Deliver Calculator worksheet and using the value of cell B27 as the lookup value. If the formula returns an error value, display no text in the cell.

In cell B29, calculate the estimated delivery day, assuming that deliveries are only made on the weekdays, using the value in cell B28 to estimate the number of workdays that have passed from the current date in cell B26. Have the estimated delivery date skip holidays using the list of holiday dates in the range A20:A75 of the Delivery Calculator worksheet. If the formula returns an error value, display no text in the cell.

In the range A32:A41, add data validation to limit the product ID of items ordered by the customer to the list of product IDs in the range A5:A70 of the Product List worksheet.

In the range B32:C41, use the Product List table as a lookup table to retrieve the name of the product ordered by the customer. Use the corresponding Product ID in column A as the lookup value. If the formula returns an error value, display no text in the cell.

In the range D32:D41, use the Product List table as a lookup table to retrieve the price of the product ordered by the customer. Use the corresponding value in column A as the lookup value. If no Product ID is entered in the corresponding cell in column A, display no text in the cell.

In the range F32:F41, calculate the charge for the item ordered by multiplying the price of the item by the quantity ordered. If the formula returns an error value, display no text in the cell.

In cell F43, calculate the subtotal of the charges for the items ordered by the customer.

In cell F44, calculate the sales tax on the order using the tax rate quoted in cell H44.

In cell F45, calculate the total cost of shipping and handling based on the following rules:

• Calculate the initial shipping charge by looking up the shipping fee in the table in the range A6:B10 of the Delivery Calculator worksheet using cell F43 as the lookup value.
• Any merchandise subtotal of $100 or greater receives free shipping when using Standard Delivery type (check Shipping Fee table).
• Add a surcharge based on the type of delivery by looking up the surcharge from the range A14:C17 on the Delivery Calculator worksheet and the type of delivery specified in cell B27.
• If the formula returns an error value, display no text in the cell.

In cell F47, calculate the total bill by adding the subtotal, sales tax, and shipping & handling fee. If the formula returns an error value, display no text in the cell.

Use data validation to limit the value in cell E26 to either VISA or MasterCard, which are the credit cards that The Sauce Shoppe accepts.

VISA and MasterCard credit card numbers are 16 digits. Add data validation to set the length of the text in cell E27 to 16 characters. Apply the Number format with no decimal places to the value in the cell.

Cell H27 is used for storing a three-digit security code. Add data validation to set the length of the text in the cell to three characters.

In cell E28, add data validation to verify that the expiration date entered in the cell is past the date specified in cell B26 (the date of the order).

 Record a macro named Copy Address that performs the following steps:

• Select and copy the range B8:B13.
• Paste the copied cells into the range B19:B24.
• Select cell B26.

Assign the Copy Address macro to the macro button, “Copy Shipping Address --->”

Unlock all of the cells in the worksheet where users enter data. All other cells should be locked.

Protect the Order Form worksheet from being changed. Do not specify a password for the sheet protection.

Enter the customer information listed below. Use the macro button to copy the shipping address into the billing address.

Customer: Jaime Kingsolver
Order No.: OR1284
Address 1: 414 Jefferson Lane
City: Newberry
State: South Carolina
ZIP: 29108
Phone: (803) 555-1021

Choose Standard shipping for the order.

Add the following items to the order and calculate the total bill:

3 of item FM008
1 of item EX012
1 of item HVM015

Enter the following VISA credit card number for the customer: 4123456789012340 with the security code 999, 3/1/2022 as the expiration date and Jaime Kingsolver as the cardholder's name.

Change the print settings so the order form will print on a single sheet of paper in portrait orientation.

Save the workbook.

Delete all of the customer and order specific information from the Order Form worksheet. Save the file as Order Form in Excel Macro-Enabled Template format in the Final Project folder. Do not have Excel clear the external data before saving the template.