I only need help on Part II Question 6 on the Instructions sheet (Highlighted in red).

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. PARTI: 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 PART II: DEVELOPING OUTPUTS On the Orders sheet, complete the following tasks... 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. 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. Instructions Orders Item List Sched1 Sched2 Discount Ship States + F18 fx =(INDEX(Ship!B4:08,VLOOKUP(F5,States!B2:152,3,FALSE),MATCH(F6,Ship!B3:D3,0))) A B D E F G H Fishing Division Order Form 1 Quantity 20 Volume (Cf) $/unit Total 0.5 $ 79.99 $ 799.90 0.5 $ 30.95 $ 0.5 $ 99.99 $ 2.25 $ 169.95 $ 11,471.63 0.5 $ 26.99 $ 0.05 $ 1.99 $ 7.76 30 78 4.3 2 Customer Name: 3 4 Ship to State 5 Ship Method 6 Order Type 7 8 Item# Description 9 204 Proline Spin Reel 10 205|Proline Underspin Reel 11 203 Ultra-Lite Spin Reel 12 217 Waders Paints 13 216 1 lb Fish Line 14 218 Soft Bait Bulk - Worms 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Total Order Discount Shipping $ 12,279.29 4% #N/A Grand Total #N/A Data Input Instructions Orders Item List Sched1 Sched2 Discount Ship States + Ready EO A2 fx 201 B E F. G Price Schedule 1 1 1 1 1 1 1 1 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Item# Description 201|Proline Cast Reel 202 Supra Cast Reel 203 Ultra-Lite Spin Reel 204 Proline Spin Reel 205 Proline Underspin Reel 206 Proline Lite Stick Cast Rod 207 Cyclo Stick Cast Rod 208 Cyclo Stick Spinning Rod 209 Aberdeen Hook Set 210 Baitholder Hook Set 211 Wide Gap Treble Hook Set 212 Trailer Hook Set 213 Mono-filament line spool 214 Florocarbon line spool 215 1/4 lb Fish Line 216 1 lb Fish Line 217 Waders Paints 218 Soft Bait Bulk - Worms D Ship Volume (CF) 0.50 0.50 0.50 0.50 0.50 0.60 0.60 0.60 0.35 0.35 0.35 0.35 0.45 0.45 0.45 0.50 2.25 0.05 1 1 1 1 1 1 1 1 2 Instructions Orders Item List Sched1 Sched2 Discount Ship States B4 fo 99.99 A B D E F G H 1 Item# $/unit 201 $ 35.90 202 $ 51.20 203 $ 99.99 204 $ 79.99 205 $ 30.95 206 $ 40.50 207 $ 48.99 208 $ 160.99 209 $ 6.50 210 $ 7.50 211 $ 4.29 212 $ 2.15 213 $ 214 $ 16.99 215 $ 6.00 216 $ 26.99 217 $ 169.95 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Instructions Orders Item List Sched1 Sched2 Discount Ship States T29 fx C D E F G B Unit Pricing - Soft Bait Packages # of Packages $/unit 0 $ 1.99 100 $ 1.69 200 $ 1.39 400 $ 1.29 800 $ 1.00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Instructions Orders Item List Sched1 Sched2 Discount Ship States Q26 fx B $ 0 0% C D E $ 3,000 $5,000 $ 10,000 $ 2% 3% 4% F. 25,000 6% A 1 Total Order Value 2 Discount 3 4 5 Instructions Orders Item List Sched1 Sched2 Discount Ship States + G3 fx A B C D E F G 1 Boat 2 3 4 5 6 7 8 9 Ship Table 1 - Regular Customer Orders $/CF Region Truck Rail Air 1 $ 2.25 $ 2.20 $ 30.00 2 $ 2.50 $ 2.40 32.00 3 $ 3.00 $ 2.80 $ 34.00 4 $ 3.50 $ 3.20 $ 35.00 5 $ 4.00 $ 3.50 $ 36.00 Regular Expedited 10 11 12 13 14 15 16 17 18 Ship Table 2 - Expedited Customer Orders $/CF Region Truck Rail Air 1 $ 2.48 2.42 $ 27.00 2 $ 2.75 2.64 $ 30.00 3 $ 3.30 3.08 $ 32.00 4 $ 3.85 3.52 $ 33.00 5 $ 4.40 3.85 $ 34.00 A A A A A Instructions Orders Item List Sched1 Sched2 Discount Ship States A1 Name E F G H B c D Abbreviation Region Region# AL SE 2 AK W 10 5 AZ CI SW 4 AR SE 2 CA W 5 W 5 5 CT NE 1 DE NE 1 DC Region Numbers SE 2 w 5 SW 4 NE 1 MW 3 NE 1 FL GA HI ID IL IN 18 IA KS no KY LA LA ME ME MD 2 2 5 5 3 3 . 3 A 1 Name 2 ALABAMA 3 ALASKA 4 ARIZONA 5 ARKANSAS 6 CALIFORNIA 7 COLORADO 8 CONNECTICUT 9 DELAWARE AWARE DISTRICT OF 4 COLUMBIA 10 11 FLORIDA 12 GEORGIA 13 HAWAII 14 IDAHO 15 ILLINOIS 16 INDIANA 17 IOWA 18 KANSAS 19 KENTUCKY 20 LOUISIANA 21 MAINE 22 MARYLAND 23 MASSACHUSETTS 24 MICHIGAN 25 MINNESOTA 26 MISSISSIPPI 27 MISSOURI 28 MONTANA 20 - 29 NEBRASKA 30 NEVADA 31 NEW HAMPSHIRE 31 32 NEW JERSEY 33 NEW MEXICO 34 NEW YORK 35 NORTH CAROLINA 36 NORTH DAKOTA 37 OHIO 38 OKLAHOMA 39 OREGON SUS SE SE W W MW MW MW MW SE SE NE NE NE MW MW 3 2 2 1 1 1 3 MA MI MI MN MS MS MO MT NE NV NH SE MW 3 2 2 3 5 5 3 3 5 5 1 NJ NM 1 4 w W MW W w NIC NE ME NE SW ME NE SE MW MW MW SW W 1 2 3 3 4 5 40 PENNSYLVANIA NE Me #1 RHODE ISLAND NE SE NY NC ND OH OK OR PA RI SC SD TN TX UT VT VA VA WA WV WI 1 1 2 2 3 2 4 5 1 MW SE SW W NE SE W SE MW w 2 12 SOUTH CAROLINA 13 SOUTH DAKOTA 14 TENNESSEE 25 TEXAS 46 UTAH 27 VERMONT 48 VIRGINIA 79 WASHINGTON 50 WEST VIRGINIA 31 WISCONSIN 52 WYOMING 53 54 55 56 57 58 59 30 5 2 3 5 Instructions Orders Item List Sched1 Sched2 Discount Shir... 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. PARTI: 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 PART II: DEVELOPING OUTPUTS On the Orders sheet, complete the following tasks... 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. 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. Instructions Orders Item List Sched1 Sched2 Discount Ship States + F18 fx =(INDEX(Ship!B4:08,VLOOKUP(F5,States!B2:152,3,FALSE),MATCH(F6,Ship!B3:D3,0))) A B D E F G H Fishing Division Order Form 1 Quantity 20 Volume (Cf) $/unit Total 0.5 $ 79.99 $ 799.90 0.5 $ 30.95 $ 0.5 $ 99.99 $ 2.25 $ 169.95 $ 11,471.63 0.5 $ 26.99 $ 0.05 $ 1.99 $ 7.76 30 78 4.3 2 Customer Name: 3 4 Ship to State 5 Ship Method 6 Order Type 7 8 Item# Description 9 204 Proline Spin Reel 10 205|Proline Underspin Reel 11 203 Ultra-Lite Spin Reel 12 217 Waders Paints 13 216 1 lb Fish Line 14 218 Soft Bait Bulk - Worms 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Total Order Discount Shipping $ 12,279.29 4% #N/A Grand Total #N/A Data Input Instructions Orders Item List Sched1 Sched2 Discount Ship States + Ready EO A2 fx 201 B E F. G Price Schedule 1 1 1 1 1 1 1 1 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Item# Description 201|Proline Cast Reel 202 Supra Cast Reel 203 Ultra-Lite Spin Reel 204 Proline Spin Reel 205 Proline Underspin Reel 206 Proline Lite Stick Cast Rod 207 Cyclo Stick Cast Rod 208 Cyclo Stick Spinning Rod 209 Aberdeen Hook Set 210 Baitholder Hook Set 211 Wide Gap Treble Hook Set 212 Trailer Hook Set 213 Mono-filament line spool 214 Florocarbon line spool 215 1/4 lb Fish Line 216 1 lb Fish Line 217 Waders Paints 218 Soft Bait Bulk - Worms D Ship Volume (CF) 0.50 0.50 0.50 0.50 0.50 0.60 0.60 0.60 0.35 0.35 0.35 0.35 0.45 0.45 0.45 0.50 2.25 0.05 1 1 1 1 1 1 1 1 2 Instructions Orders Item List Sched1 Sched2 Discount Ship States B4 fo 99.99 A B D E F G H 1 Item# $/unit 201 $ 35.90 202 $ 51.20 203 $ 99.99 204 $ 79.99 205 $ 30.95 206 $ 40.50 207 $ 48.99 208 $ 160.99 209 $ 6.50 210 $ 7.50 211 $ 4.29 212 $ 2.15 213 $ 214 $ 16.99 215 $ 6.00 216 $ 26.99 217 $ 169.95 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Instructions Orders Item List Sched1 Sched2 Discount Ship States T29 fx C D E F G B Unit Pricing - Soft Bait Packages # of Packages $/unit 0 $ 1.99 100 $ 1.69 200 $ 1.39 400 $ 1.29 800 $ 1.00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 Instructions Orders Item List Sched1 Sched2 Discount Ship States Q26 fx B $ 0 0% C D E $ 3,000 $5,000 $ 10,000 $ 2% 3% 4% F. 25,000 6% A 1 Total Order Value 2 Discount 3 4 5 Instructions Orders Item List Sched1 Sched2 Discount Ship States + G3 fx A B C D E F G 1 Boat 2 3 4 5 6 7 8 9 Ship Table 1 - Regular Customer Orders $/CF Region Truck Rail Air 1 $ 2.25 $ 2.20 $ 30.00 2 $ 2.50 $ 2.40 32.00 3 $ 3.00 $ 2.80 $ 34.00 4 $ 3.50 $ 3.20 $ 35.00 5 $ 4.00 $ 3.50 $ 36.00 Regular Expedited 10 11 12 13 14 15 16 17 18 Ship Table 2 - Expedited Customer Orders $/CF Region Truck Rail Air 1 $ 2.48 2.42 $ 27.00 2 $ 2.75 2.64 $ 30.00 3 $ 3.30 3.08 $ 32.00 4 $ 3.85 3.52 $ 33.00 5 $ 4.40 3.85 $ 34.00 A A A A A Instructions Orders Item List Sched1 Sched2 Discount Ship States A1 Name E F G H B c D Abbreviation Region Region# AL SE 2 AK W 10 5 AZ CI SW 4 AR SE 2 CA W 5 W 5 5 CT NE 1 DE NE 1 DC Region Numbers SE 2 w 5 SW 4 NE 1 MW 3 NE 1 FL GA HI ID IL IN 18 IA KS no KY LA LA ME ME MD 2 2 5 5 3 3 . 3 A 1 Name 2 ALABAMA 3 ALASKA 4 ARIZONA 5 ARKANSAS 6 CALIFORNIA 7 COLORADO 8 CONNECTICUT 9 DELAWARE AWARE DISTRICT OF 4 COLUMBIA 10 11 FLORIDA 12 GEORGIA 13 HAWAII 14 IDAHO 15 ILLINOIS 16 INDIANA 17 IOWA 18 KANSAS 19 KENTUCKY 20 LOUISIANA 21 MAINE 22 MARYLAND 23 MASSACHUSETTS 24 MICHIGAN 25 MINNESOTA 26 MISSISSIPPI 27 MISSOURI 28 MONTANA 20 - 29 NEBRASKA 30 NEVADA 31 NEW HAMPSHIRE 31 32 NEW JERSEY 33 NEW MEXICO 34 NEW YORK 35 NORTH CAROLINA 36 NORTH DAKOTA 37 OHIO 38 OKLAHOMA 39 OREGON SUS SE SE W W MW MW MW MW SE SE NE NE NE MW MW 3 2 2 1 1 1 3 MA MI MI MN MS MS MO MT NE NV NH SE MW 3 2 2 3 5 5 3 3 5 5 1 NJ NM 1 4 w W MW W w NIC NE ME NE SW ME NE SE MW MW MW SW W 1 2 3 3 4 5 40 PENNSYLVANIA NE Me #1 RHODE ISLAND NE SE NY NC ND OH OK OR PA RI SC SD TN TX UT VT VA VA WA WV WI 1 1 2 2 3 2 4 5 1 MW SE SW W NE SE W SE MW w 2 12 SOUTH CAROLINA 13 SOUTH DAKOTA 14 TENNESSEE 25 TEXAS 46 UTAH 27 VERMONT 48 VIRGINIA 79 WASHINGTON 50 WEST VIRGINIA 31 WISCONSIN 52 WYOMING 53 54 55 56 57 58 59 30 5 2 3 5 Instructions Orders Item List Sched1 Sched2 Discount Shir