Sales Order Project

1. Create an Excel workbook and name it Sales Order Form (with your name at the end).
2. Create a sheet named Product List and enter the following items:

ProductId	ProductName	ProductPrice
EX001	Onion BBQ Sauce With Bacon (9 Oz)	11.15
EX002	Old Fashion Maple Hot Sauce (11 Oz)	12.75
EX003	Ginger Honey Sauce (9 Oz)	8.75
EX004	Praline Maple Glaze (9 Oz)	13.55
EX005	Sweet & Spicy Balsamic Garlic Sauce (8 Oz)	9.25
EX006	Sweet, Sour & Smokey Mustard Sauce 14.5 Oz	11.55
EX007	Roquefort Dijon (10 Oz)	9.75
EX008	Ginger Birch Sauce (4.75 Oz)	8.55
EX009	Spicy Maple BBQ Sauce (10.25 Oz)	11.15
EX010	Sesame Sauce (11 Oz)	9.75
EX011	Merlot 'n Chocolate Sauce (10 Oz)	12.15
EX012	Harvest Honey Truffle Sauce (8.5 Oz)	12.95
EX013	Brown Ginger Mustard (9 Oz)	5.95
EX014	Maine Maple Champagne Sauce (9 Oz)	8.75
EX015	Cracked Pepper Lemon & Thyme Sauce (8.4 Oz)	9.75
EX016	Hickory Almond Sauce (9 oz)	6.75

3. Create a sheet named Delivery Calculator.

• On the delivery calculator sheet use the following information to create a lookup table. Merchandise subtotal $0, shipping fee $5.95, merchandise subtotal $25, shipping fee $7.95, merchandise subtotal $50, shipping fee $9.95, merchandise subtotal $75, shipping fee $11.95, merchandise subtotal $100, shipping fee $13.95, merchandise subtotal $125, shipping fee $0
• On the delivery calculator sheet use the following information to create a second lookup table. Additional Delivery charges are as follows: Standard shipping is no additional charge and takes 5 days. Priority shipping is 9.95 and takes 3 days. Express shipping is 12.95 and takes 2 days. Next day delivery is 19.95 and takes 1 day.

4. Create a sheet named Order Form.
5. On the Order form, create an area to enter the Date of Order, Type of Delivery, Days to Deliver and Delivery Date.

• The date of order should display the current date in the format that includes the day of the week.
• The type of delivery can be Standard, Priority, Express or Next day. Add a data validation rule to enter the type of delivery.
• The Days to Deliver should fill in automatically after the user selects the type of delivery.
• Calculate the delivery date. There are no deliveries on Sundays. Use a function to determine if the delivery date is a Sunday. If the delivery date is a Sunday schedule delivery for the next day.

6. Create an area for a customer order. Allow space for ten products. Include product id, product name, price, quantity and charge. For the Product ID only allow entries from the product list. Name and price should fill in automatically after the product ID is entered. Use a formula to display nothing if any of the formulas return an error.
7. Enter 10 different products for the order.
8. Create a formula to calculate a total for all products ordered.
9. Create a formula to calculate sales tax on the total using a 3% tax rate entered in another cell in the worksheet.
10. Create a formula to calculate a single shipping charge. Include both the shipping fee and additional charge for the delivery type in one cell.
11. Create a formula to calculate the total bill.
12. Delete the product ID and quantity in your last 3 items ordered so that you have only 7 products ordered. Verify that no errors display in the blank rows.
13. Create an area to enter the Credit card type (MasterCard or Visa) Credit Card Number (must be 16 characters), Expiration date (must be past the date of order), Security Code (must be 3 characters).
14. Create an area to enter the Customer name, shipping address, shipping city, shipping state, shipping zip and shipping phone.
15. Create an area to enter the billing address, billing city, billing state, billing zip and billing phone.
16. Often times the customer shipping address will be the same as the billing address. To avoid tying the same address twice, record a macro to copy the data from the from the customer shipping address, shipping city, shipping state, shipping zip and shipping phone to the billing address, billing city, billing state, billing zip and billing phone.
17. Create a button to run the macro.
18. Label the button, Copy Shipping Address to Billing Address.

Upload to Blackboard.