SynthesisProject1 Creating an order form in Excel Skills Required (all are covered in Modules 1-3): General spreadsheet layout and formatting VLOOKUP Simple IF function Conditional formatting Simple arithmetic formulas WORKDAY function IFERROR function For this assignment, you are using 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. 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. It can resemble the pdf example. 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 (SynthesisProject1StartFile). That workbook contains four worksheets: The log sheet where your edits will be recorded make sure you enable macros! A Documentation worksheet enter your name, etc. The Tables worksheet that contains the tables you will use in your lookup functions. An OrderForm worksheet where you will create your order form. You may want to copy the tables from the Tables sheet into this worksheet 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. 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. The shipping method option should be entered by the salesperson. 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 should 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, create a formula that uses the following logic: If the quantity ordered for an item is less than 5, no discount is applied to that items total. If the quantity ordered for an item is 5 or more, but less than 10, a 5% discount is applied to that items total. If the quantity ordered for an item is 10 or more, a 7.5% discount is applied to that items total. Subtotal is the sum of all the Item Totals (use a formula). Sales Tax is calculated by your Excel formula using the State Sales Tax data provided. 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. Total Due is the sum of Subtotal, Sales Tax, and Shipping (use a formula). Apply conditional formatting to the cell containing Total Due using the following rules: If Total Due is less than $100, no special formatting applies. If Total Due is greater than $100 but less than $500, format the cell with light yellow fill. If Total Due is greater than $500, format the cell with a light blue fill. The form must also include two messages for the salesperson to tell the customer. You create these dynamic messages with formulas in your OrderForm worksheet. 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. 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. Submit your file as it is after completing the Test Variations. Finally, create a comprehensive Documentation worksheet for your workbook. Please refer to the Sample Documentation and Spreadsheet Standards in D2L/Brightspace Content Tips and Help Videos How to Document Excel Workbooks. MAIL ORDER FORM QDH Outdoor Company 123 Cedar Ave Mayberry, NC Phone: 444 545-6767 Order # preprinted on form ORDER NUMBER: 1Test Data Test your order form by entering the following test data and checking your forms formulas against the results showAfter you have verified that you obtain these outputs in your order form, for all the right reasons, you may proceed to the T Show transcribed data MAIL ORDER FORM QDH Outdoor Company 123 Cedar Ave Mayberry, NC Phone: 444 545-6767 Order # preprinted on form ORDER NUMBER: 11356 DATE: TO: Customer Name Street Address City State, Zip Phone Check marks for each of these fields ("x" in a cell will be fine for spreadsheet form) PAYMENT TYPE: Cash Check_ Bill to Credit Card Number Salesperson writes credit card number here Last name of salesperson Shipping method (entered as option number) determines shipping charge. See the table provided. SALESPERSON SHIP VIA QUANTITY PRODUCT NAME PRODUCT NUMBER UNIT PRICE DISCOUNT % ITEM TOTAL SUBTOTAL Enter quantity and product number and Excel should fill out the rest (prod name, price, discount, item total) SALES TAX SHIPPING TOTAL DUE Above fields should be calculated in Excel Test Data Test your order form by entering the following test data and checking your form's formulas against the results shown here. Enter these data: Date: 9/15/2021 Customer Name: Gladys Knight Street Address: 123 Forth City, State, Zip: Elway, CO 80384 Phone: 719-555-1212 Payment Type: Bill to Salesperson: Elbert Ship Via Option: 2 Quantity 12 9 5 1 Product Number sg8128 led7991 fa8401 bb7117 You should obtain the following results: Unit Price Product Name Polarized Snow Goggles LED Rechargeable Kit First Aid Kit (Large) Blister Patches $ $ $ $ 39.45 21.94 21.73 9.95 Discount % Item Total 7.5% $ 437.90 5.0% $ 187.59 5.0% $ 103.22 0.0% $ Subtotal $ 738.65 9.95 Sales Tax $ 21.42 Shipping $ 15.95 Total Due $ 776.02 The messages should display: Thank you for your business! Because your order was over $250, you can receive free one-day shipping on your next order. Your delivery should arrive on 9/20/2021. After you have verified that you obtain these outputs in your order form, for all the right reasons, you may proceed to the Test Variations. Test Variations: Change the state to IN. The Sales Tax should display $51.71. Finally, enter the following values and after you check that you again get the results shown, save your workbook with these values in it. Return the State to CO. Change the order date to 9/25/21. Change the Ship Via Option to 1. Enter the following quantities and Product Numbers Quantity Product Number 1 5g8128 2 led7991 1 fa8401 2 bb7117 These should display Unit Price $ 39.45 $ 21.94 $ 21.73 $ 9.95 Discount% Item Total 0.0% $ 39.45 0.0% $ 43.88 0.0% $ 21.73 0.0% $ 19.90 Subtotal $ 124.96 Sales Tax $ 3.62 Shipping $ 12.95 Total Due $ 141.53 The messages should display: Thank you for your business! We hope to hear from you again soon. Your delivery should arrive on 10/1/2021. Save the file with your last name and "SynProji" in the title (e.g. RamirezSynProj1) and submit to Brightspace