Synthesis Project $1$

Creating an order form in Excel

Skills Required $($all are covered in Modules $1-3)$:

$$ General spreadsheet layout and formatting

$$ VLOOKUP

$$ IF function

$$ Conditional formatting

$$ Simple arithmetic formulas

$$ WORKDAY function

$$ IFERROR function $($optional$)$

For this assignment, you are to use your newly acquired Excel skills to create a spreadsheet$-$

based version of the order form used by mail$-$order $($yes$,$ mail$-$order, not online!$)$ outdoor

retailer QDH Outdoor Company. Refer to the accompanying PDF version of the order form,

which is used by QDH sales personnel to take phone orders. It has been annotated to help you

create the new spreadsheet form.

Your task is to improve upon the current order form by using Excel to automatically fill in some

of the fields, perform calculations, and remind sales personnel to tell the customers specific

things while they are on the phone. Most of this is explained in the annotations on the PDF

version of the form. Below are more details about what must be included in the Excel form.

The layout and format of the order form is up to you. It is intended to be filled out on a

computer, although it may be printed afterwards as well. It should include header information

$($QDH Outdoor Company$$ and address$)$ at the top of the OrderForm you create in Excel. Also,

set QDH Outdoor Company and address as a Print Title in case the form is printed, and make

sure an appropriate portion of the OrderForm worksheet is set as the Print Area.

Choose a layout that makes it easy to use and attractive.

Hint: Your layout can affect how easy $($or hard!$)$ it is to create formulas for the functions

required for this assignment.

Make sure that all formatting of dates, numbers, currencies, and percentages is appropriate!

You must use the Excel start file provided $($Synth$1$StartFile$).$ That workbook contains three

worksheets:

$$ A Documentation sheet $$ enter your name, etc.

$$ The Tables sheet that contains the tables you will use in your lookup functions.

$$ An OrderForm sheet where you will create your order form. You may want to copy the

tables from the Tables sheet into this sheet so they are easier to access. Copy them into

a place that does not interfere with your order form design.

The order form must include a place to enter all the information included in the paper $($PDF$)$

form.

$1.$ For Payment Type, the salesperson can just enter an $$X$$ to indicate if Cash, Check, or

Bill To is the method. For the credit card option, the user should enter a credit card

number.

$2.$ The shipping method option should be entered by the salesperson.

$3.$ The form must allow a salesperson to enter a quantity and a product number. Once

those two fields have been entered, the Product Name, Unit Price, Discount $\%,$ and Item

Total will be automatically filled in using Excel formulas that you create. The correct

values for Product Name and Unit Price come from the tables provided.

a$.$ To calculate the discount, use the following logic:

i$.$ If the quantity ordered for an item is less than $5,$ no discount is applied to

that item$$s total.

ii$.$ If the quantity ordered for an item is $5$ or more, but less than $10,$ a $5\%$

discount is applied to that item$$s total.

iii. If the quantity ordered for an item is $10$ or more, a $7\mathrm{.}5\%$ discount is

applied to that item$$s total.

b$.$ Your form should provide rows for each least five products to be entered.

$4.$ Subtotal is a sum of all the Item Totals $($use a formula$).$

$5.$ Sales Tax is calculated by Excel using the State Sales Tax data provided. You can assume

state is entered as $2-$letter abbreviations.

$6.$ Shipping is calculated by a formula you create based on what was entered in the Ship

Via field $($as an option number$)$ and the Shipping table provided.

$7.$ Total Due is a sum of Subtotal, Sales Tax, and Shipping $($use a formula$)..$

$8.$ Apply conditional formatting to the cell containing Total Due using the following rules:

a$.$ If Total Due is less than $$100,$ no special formatting applies.

b$.$ If Total Due is greater than $$100$ but less than $$500,$ format the cell with light

yellow fill.

c$.$ If Total Due is greater than $$500,$ format the cell with a light blue fill.

$9.$ The form must also include two messages for the salesperson to tell the customer.

Make sure the messages are formatted in ways that are very visible to the salesperson.

a$.$ The first message is based on the total amount of the order.

i$.$ If the order amount is over $$250,$ tell the customer, $$Thank you for your

business! Because your order was over $$250,$ you can receive free one$-$

day shipping on your next order.$$

ii$.$ If the order amount is less than $$250,$ tell the customer, $$Thank you for

your business! We hope to hear from you again soon.$$

b$.$ The second message to display is the date when delivery should be expected,

based on the shipping method chosen.

$10.$ Enter the Test Data shown below and conduct the test variations indicated. If your

formulas do not generate the results shown below, figure out what is going awry with

your formulas and correct them.