GRINKLE "ONLY"

ACADEMIC MISCONDUCT:

Each individual student is expected to create the files submitted, develop the formulas, and directly input all data (formulas, inputs, format, tables, etc.) during this semester into the file he/she submits for credit. In addition, each student is responsible to maintain his/her file and related notes in a reasonably secure manner and not knowingly allow any other student access to his/her files and related notes (formulas, calculations, data, etc.). Be sure to review the academic Misconduct Policy in the course syllabus.

REQUIREMENTS:

Create a new Excel workbook and save in usual format replacing the HW number with PRJ01.

In this project, you will be creating an invoice for a sports supply store that sells sporting goods to elementary, middle and high schools. You have been asked to create a billing/invoice system that will expedite the process of creating invoices for clients. Your invoice will have two sections: 1) Company and Customer Information and 2) Order Information.

1) Company and Customer Information Section:

You are to design the layout of the top portion of the invoice. However, the following items must be included:

a) Your Company Name, Address, Phone Number and Website (you can make up this information)

b) Invoice Date: use a function to calculate the current date

c) Invoice Number: (user input)

d) Due Date: Use a formula to calculate the due date as 30 days from the invoice date

e) A section for the customer information. Label this area ‘Bill To:’ It must contain the following information:

· A user input for the Customer ID (see attached sheet for all customer information). Use a drop down list for this item.

· Customer Name

· Customer Street Address

· Customer City, State and Zip

· Customer Phone

(Use VLookup for Name thru Phone above)

· Tax Exempt Form on File – use a drop down where the user can select YES or NO

· Shipping Zone: Use a VLookup to display the shipping zone as 1, 2, 3, or 4 (see attached)

2) Order Information: The order section of the invoice is described below. Refer to template shown for required layout. See attached sheet for all product information.

a) Product #: User input. Use a drop down list

b) Description: Use a V-Lookup to display the description of the product ID that was entered.

c) Quantity: User Input. Customers cannot order in quantities higher than 50, for any one item. Use a function to display an appropriate error message if a user enters a quantity more than 50 or use data validation to limit the quantity that can be entered.

d) Unit Price: Use a V-Lookup to display the unit price of the product ID that was entered.

e) Amount: This cell should use a formula to calculate the total amount due for each product type ordered.

f) Subtotal: Calculate the total amount due before tax and shipping

g) Tax: The tax should be calculated as 6% of the subtotal. However, if the customer has a tax exempt form on file, then the tax will be zero and you should show this as ‘TAX EXEMPT’ in the tax output cell. The 6% may change in the future. If the user left the tax exempt question blank, then the output for the tax cell should say ‘Enter Yes or No for Tax Exempt Status’.

h) Shipping: Calculate the shipping based on the following information. Refer to the attached document regarding which Zone each state is in. Use Index and Match. The shipping charges may change in the future as may the pricing differential between the various tiers. The set-up for your Index and Match has been provided on the attached sheet.

i. Tier 1: Orders of $0-$99.99: Zone 1 - $10, Zone 2 - $8, Zone 3 - $12.50, Zone 4 - $15 (these prices may change in the future.)

ii. Tier 2: $100-$199.99: $2.50 less than Tier 1 prices (use a formula to calculate this so the $2.50 can change in the future).

iii. Tier 3: $200-249.99: $4.00 less than Tier 1 prices.

iv. In addition, if the customer orders $250 or more worth of product (before tax and shipping), then shipping is free. Free shipping should be shown with the word ‘FREE’ in the shipping cost output cell.

v. If the customer lives in Alaska or Hawaii, additional handling charges of $15 should be added to the standard shipping fee (and should apply even if shipping is free).

Use the layout below for the order section of your invoice:

3) Additional Project Requirements:

a. Invoice must be set to fit to one page.

b. Include a header with your name.

c. Invoice should display a professional design and appearance. Format cells appropriately following course guidelines.

d. Create your invoice in such a manner that no errors will appear anywhere on the invoice (including #NA and #VALUE! Errors).

e. No numbers should be shown in your formulas – use cell references. Link to assumptions where applicable.

f. When no product information has been entered into the invoice, the subtotal through total areas should be blank, not zero. Once at least one product has been entered into the invoice, the subtotal through total should show outputs in those cells.

g. Add any additional touches that you feel will add value to your invoice. You can find many sample invoices by doing a web search on the internet.

Customer Information: (Place this information on a separate worksheet. Adjust the layout as necessary.)

Product Information: (Place this information on a separate worksheet. Adjust the layout as necessary.)

Shipping Pricing: (Place this on a separate worksheet. Do not change the layout. You can add rows to this if you feel necessary.)

Shipping Zones by State: (Place this on a separate worksheet. Adjust the layout as necessary.)