Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Sales Order Project Create an Excel workbook and name it Sales Order Form (with your name at the end). Create a sheet named Product List

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

  1. 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.
  1. Create a sheet named Order Form.
  2. 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.
  1. 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.
  2. Enter 10 different products for the order.
  3. Create a formula to calculate a total for all products ordered.
  4. Create a formula to calculate sales tax on the total using a 3% tax rate entered in another cell in the worksheet.
  5. Create a formula to calculate a single shipping charge. Include both the shipping fee and additional charge for the delivery type in one cell.
  6. Create a formula to calculate the total bill.
  7. 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.
  8. 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).
  9. Create an area to enter the Customer name, shipping address, shipping city, shipping state, shipping zip and shipping phone.
  10. Create an area to enter the billing address, billing city, billing state, billing zip and billing phone.
  11. 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.
  12. Create a button to run the macro.
  13. Label the button, Copy Shipping Address to Billing Address.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Auditing And Assurance Theory And Practice

Authors: Clifford Gomez

1st Edition

8120345665, 978-8120345669

More Books

Students also viewed these Accounting questions

Question

6. Identify characteristics of whiteness.

Answered: 1 week ago

Question

9. Explain the relationship between identity and communication.

Answered: 1 week ago