FISHING ORDER PROCESSING The Orders sheet permits a salesperson to record customer orders. The input areas are highlighted in blue, output areas in green. Input areas should be set up for data validation so that only valid inputs may be provided. Once information has been filled in the areas highlighted in blue, the company would like areas marked in green to complete automatically. PART I: VALIDATING INPUTS Set up the Orders sheet to accept only valid inputs as specified... 1. Customer names may be any text up to 50 characters. 2. Ship to State should contain any of the State abbreviations available on the States sheet, column B. 3. Valid shipping methods are Truck, Rail, Air, Boat 4. Order types are Regular or Expedited 5. Valid item numbers are available in column A of the Item List sheet 6. Quantity is any number not exceeding 10,000 On the Orders sheet, complete the following tasks... PART II: DEVELOPING OUTPUTS 1. Obtain the item description from the Item List sheet, based on the item number selected 2. Obtain the volume in cubic feet (cf) corresponding to the item selected from the Item List sheet 3. The Item List sheet also contains the reference schedule from which the unit price for ordered items is to be determined. For items 201 to 217, this is currently the Sched1 sheet; for item 218, it is the Sched2 sheet. Determine which is the appropriate reference schedule based on the item number and then determine the unit price to be charged. For items on Sched1, notice that this is a flat rate; for those on Sched2, it is a function of the number of units ordered. 4. Order totals for the Volume and Total columns should be displayed in row 16. 5. In cell F17, calculate the Discount the customer should receive based on the Total Order amount and the discount rate table provided on the Discount sheet. 6. In F18, calculate shipping costs based on the total shipping volume, the Order Type, the Ship Method selected, the region, and the shipping rates provided on the Ship sheet. Note that regions correspond to the destinatioon state, as indicated on the States sheet. 7. Calculate the Grand Total owed in cell F20. Format all $ values as currency. 8. The Order Form accommodates up to 6 ordered items. If a customer orders fewer than 6 items, unused rows should appear blank; when an item number iand quantity s supplied though, the rest of the order row should populate completely. PART III: AUTOMATION 1. In lieu of the on-screen data entry, create a data input form. This form should minimally permit the user to enter customers' names. Controls that permit the user to enter additional data will earn bonus points. 2. Place a button on the Orders sheet that, when clicked, will bring up the order entry form