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. 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. A B D F G Fishing Division Order Form Description Quantity Volume (cf) $/unit Total 2 Customer Name: 3 4 Ship to State 5 Ship Method 5 Order Type 7 3 Item# 2 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 Total Order Discount Shipping Grand Total 1 WN 1 Item# Description 2 201 Proline Cast Reel 202 Supra Cast Reel 4 203 Ultra-Lite Spin Reel 5 204 Proline Spin Reel 6 | 205|Proline Underspin Reel 7 206 Proline Lite Stick Cast Rod 8 207 Cyclo Stick Cast Rod 9 208 Cyclo Stick Spinning Rod 10 209 Aberdeen Hook Set 11 210 Baitholder Hook Set 12 211 Wide Gap Treble Hook Set 13 212 Trailer Hook Set 14 213 Mono-filament line spool 214 Florocarbon line spool 16 215 1/4 lb Fish Line 17 216 1 lb Fish Line 18 217 Waders Paints 19 218 Soft Bait Bulk - Worms 20 21 Price Schedule 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 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 15 A B B 1 Item# $/unit 2 201 $ 35.90 3 202 $ 51.20 4 203 $ 99.99 5 204 $ 79.99 6 205 $ 30.95 7 206 $ 40.50 8 207 $ 48.99 9 208 $ 160.99 10 209 $ 6.50 11 210 $ 7.50 12 211 $ 4.29 13 212 $ 2.15 14 213 $ 8.00 15 214 $ 16.99 16 215 $ 6.00 17 216 $ 26.99 18 217 $ 169.95 19 20 21 22 G $ $ A 1 Total Order Value 2 Discount 3 4 B 0 0% C D E $ 3,000 $ 5,000 $10,000 $ 2% 3% 4% F 25,000 6% A B D 3 . 5 5 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 3 0 1 2 3 4 5 6 7 8 9 0 Ship Table 2 - Expedited Customer Orders $ICF 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 D SE MW OWN B MS MO MT NE NV NH NJ W MW 3 5 1 W NE NE SW 1 4 4 NM NY NC 1 N NE SE MW MW 3 3 4 5 A 26 MISSISSIPPI 27 MISSOURI 28 MONTANA 29 NEBRASKA 30 NEVADA 31 NEW HAMPSHIRE 32 NEW JERSEY 33 NEW MEXICO 34 NEW YORK 35 NORTH CAROLINA 36 NORTH DAKOTA 37 OHIO 38 OKLAHOMA 39 OREGON 40 PENNSYLVANIA 41 RHODE ISLAND 42 SOUTH CAROLINA 43 SOUTH DAKOTA 44 TENNESSEE 45 TEXAS 46 UTAH 47 VERMONT 48 VIRGINIA 49 WASHINGTON 50 WEST VIRGINIA 51 WISCONSIN 52 WYOMING 53 54 ND OH OK OR PA RI SC SD TN TX SW W NE NE 1 1 SE 2 3 MW SE 2 SW 4 5 UT VT VA W NE 1 2 2 SE W SE 5 WA W WI WY MW | 3 W 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. 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. A B D F G Fishing Division Order Form Description Quantity Volume (cf) $/unit Total 2 Customer Name: 3 4 Ship to State 5 Ship Method 5 Order Type 7 3 Item# 2 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 Total Order Discount Shipping Grand Total 1 WN 1 Item# Description 2 201 Proline Cast Reel 202 Supra Cast Reel 4 203 Ultra-Lite Spin Reel 5 204 Proline Spin Reel 6 | 205|Proline Underspin Reel 7 206 Proline Lite Stick Cast Rod 8 207 Cyclo Stick Cast Rod 9 208 Cyclo Stick Spinning Rod 10 209 Aberdeen Hook Set 11 210 Baitholder Hook Set 12 211 Wide Gap Treble Hook Set 13 212 Trailer Hook Set 14 213 Mono-filament line spool 214 Florocarbon line spool 16 215 1/4 lb Fish Line 17 216 1 lb Fish Line 18 217 Waders Paints 19 218 Soft Bait Bulk - Worms 20 21 Price Schedule 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 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 15 A B B 1 Item# $/unit 2 201 $ 35.90 3 202 $ 51.20 4 203 $ 99.99 5 204 $ 79.99 6 205 $ 30.95 7 206 $ 40.50 8 207 $ 48.99 9 208 $ 160.99 10 209 $ 6.50 11 210 $ 7.50 12 211 $ 4.29 13 212 $ 2.15 14 213 $ 8.00 15 214 $ 16.99 16 215 $ 6.00 17 216 $ 26.99 18 217 $ 169.95 19 20 21 22 G $ $ A 1 Total Order Value 2 Discount 3 4 B 0 0% C D E $ 3,000 $ 5,000 $10,000 $ 2% 3% 4% F 25,000 6% A B D 3 . 5 5 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 3 0 1 2 3 4 5 6 7 8 9 0 Ship Table 2 - Expedited Customer Orders $ICF 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 D SE MW OWN B MS MO MT NE NV NH NJ W MW 3 5 1 W NE NE SW 1 4 4 NM NY NC 1 N NE SE MW MW 3 3 4 5 A 26 MISSISSIPPI 27 MISSOURI 28 MONTANA 29 NEBRASKA 30 NEVADA 31 NEW HAMPSHIRE 32 NEW JERSEY 33 NEW MEXICO 34 NEW YORK 35 NORTH CAROLINA 36 NORTH DAKOTA 37 OHIO 38 OKLAHOMA 39 OREGON 40 PENNSYLVANIA 41 RHODE ISLAND 42 SOUTH CAROLINA 43 SOUTH DAKOTA 44 TENNESSEE 45 TEXAS 46 UTAH 47 VERMONT 48 VIRGINIA 49 WASHINGTON 50 WEST VIRGINIA 51 WISCONSIN 52 WYOMING 53 54 ND OH OK OR PA RI SC SD TN TX SW W NE NE 1 1 SE 2 3 MW SE 2 SW 4 5 UT VT VA W NE 1 2 2 SE W SE 5 WA W WI WY MW | 3 W