Two exercises in attachment
>Orders
:
to State abbreviation 1 0
1 0.50 1 0.50 1 0.50 1 0.50 1 1 0.60 1 0.60 1 1 0.35 ide Gap Treble Hook Set
1 0.35 1 0.35 1 1 0.45 1 0.45 1 0.50 1 2 $/unit Discount Ship $ 3.50 Region Truck Rail Air $ 30.00 $ 32.00 $ 3.85 $ 34.00 Region ABA AL 2 SKA
W 5 SE 2 IZONA
4 W 5 AR SE 2 SW 4 LIF N CA W 5 1 LORADO
CO W 5 3 IC CT NE 1 LA RE
DE NE 1 CT OF COLUMBIA
NE 1 OR A
FL SE 2 SE 2 W 5 ID W 5 L OIS
IL MW 3 IANA
IN MW 3 IA MW 3 MW 3 KY SE 2 LA SE 2 NE 1 NE 1 MA NE 1 CHIGAN
MI MW 3 MW 3 SE 2 MW 3 W 5 NE MW 3 DA
W 5 NE 1 NE 1 SW 4 NE 1 SE 2 ND MW 3 IO
OH MW 3 LAHOMA
OK SW 4 OR W 5 NE 1 RI NE 1 SE 2 MW 3 SE 2 SW 4 UT W 5 NE 1 VA SE 2 WA W 5 SE 2 SCONSIN
WI MW 3 OMING
WY W 5 Customer Name:
Preferred 1 2=Description 3=Quantity 4=Volume 5= $/Unit 6=Total
Item# Description Quantity Volume (cf) $/unit Total
Total Order 7=Discount
Shipping 8=Shipping
Grand Total 9=Grand Total
Discounts =Discount!$B$1:$F$2 1=Item#
2=Description
3=Quantity
4=Volume
5= $/Unit
6=Total 9=Grand Total Sum of Total Order + Discount + Shipping
Freight Customer Type (regular, preferred) Find the Discount ==> =- HLOOKUP(F16,Discounts,2,TRUE) * F16 ==> – Discount * Total Order
Find the Discount ==> =INDEX((Ship1,Ship2),VLOOKUP(F5,States,3,FALSE),MATCH(F6,Ship!B3:D3,0),MATCH(F4,{“regular”,”preferred”},0))*D16 ==> – Discount * Total Order
Select the Column in the Table 2
=IF(ISBLANK(A9), “”, C9* E9)
Setup the following Name Defined:
Choose the FALSE for Sched1 and TRUE for Sched2 ==> VLOOKUP(A9, ItemList, 3, FALSE)>1
VLOOKUP(IF(VLOOKUP(A9, ItemList, 3, FALSE) = 1, A9, C9),CHOOSE(VLOOKUP(A9, ItemList, 3, FALSE), Sched1,Sched2),2,VLOOKUP(A9, ItemList, 3, FALSE)>1)
Choose the Price Schedule Table ==> CHOOSE(VLOOKUP(A9, ItemList, 3, FALSE), Sched1,Sched2)
IF(VLOOKUP(A9, ItemList, 3, FALSE) = 1, A9, C9) ==> Sched 1 use Item# and Sched2 use Quantity
Find the Price Schedule ==> VLOOKUP(A9, ItemList, 3, FALSE)
Fishing Division Order Form
=IF(ISBLANK(A9),””,VLOOKUP(IF(VLOOKUP(A9, ItemList, 3, FALSE) = 1, A9, C9),CHOOSE(VLOOKUP(A9, ItemList, 3, FALSE), Sched1,Sched2),2,VLOOKUP(A9, ItemList, 3, FALSE)>1))
=IF(ISBLANK(A9),” “,VLOOKUP(A9,ItemList,2,FALSE))
Enter Item #
Enter Quantity
=IF(ISBLANK(A9), “”, VLOOKUP(A9,ItemList,4,FALSE)*C9) >Golf2
weight number
1 2 2 9.00
.00
$ 399.00 $ 119.99 $ 675.00 Handling 0
Description $ 5,000 Shipping NA $ 0.20 Region 1 2 3 4 $ 0.11 NA NA $ 0.14 $ 0.18 Region 1 2 3 4 $ 0.10 NA $ 0.11 NA $ 0.16 $ 0.14 $ 0.16
1 WEEK # 2 – EXERCISES CHAPTER # 5
Exercise 2: Level 2 – Order 2 Form for Golf Balls Now that Vijay has completed the order form for tennis products, he needs to work on the order form for golf equipment. As with
tennis products, Vijay must include shipping charges and a discount for orders according to their total amount. In addition, he must
add a handling charge because most golf equipment must be packed by hand. TheZone calculates handling costs for golf equipment as
shown in Table 5.9. Vijay has updated the Golf workbook and renamed it Golf2. He consolidated the order information on a worksheet named Orders, and
added worksheets for pricing information, handling charges (including a maximum fee), discounts, and shipping charges. Figure 5.25
shows the Orders worksheet with some order-related data already entered. In these steps, you need to complete the Orders worksheet using lookup functions to display the product description, and to calculate
the order total and shipping, handling, and discount charges.
2 WEEK # 2 – EXERCISES CHAPTER # 5 Complete the following:
1. Open the workbook named Golf2.xlsx located in the assignment, and then save the file as
W2-2-Golf-Orders2 -YourName.xlsx. Questions 3 through 9.
3. In cell C8 of the Orders worksheet, write a formula that displays the product description for the first item in the order. Copy the
formula into cells C9:C12.
4. In cell D8 of the Orders worksheet, calculate the total value of the item (price multiplied by quantity). Copy the formula into cells
D9:D12.
5. In cell D15 of the Orders worksheet, calculate the total cost of the order.
6. In cell D16 of the Orders worksheet, calculate the total shipping charge for this order based on four variables: the ship to region, the
customer type (standard, preferred, or most preferred), the total weight, and the method of shipping.
7. In cell D17 of the Orders worksheet, calculate the handling cost. Be certain to account for the maximum handling charge. Handling
fees are based on the total order value excluding shipping and discounts.
8. In cell D18 of the Orders worksheet, calculate the discount. Be certain to write the formula so that the discount is deducted from the
total amount when all values are added. Discounts are again based on the total order value excluding shipping and handling fees.
9. In cell D20 of the Orders worksheet, calculate the grand total for the order.
10. Save and close the W2-2-Golf-Orders2 -YourName.xlsx workbook. 3 WEEK # 2 – EXERCISES CHAPTER # 5 Exercise 3: Level 3 – Fishing Order contains the worksheets described in Table 5.14.
In these steps, you will complete the order form, creating the formulas so that new items can be added easily to the item list. You’ll
need to design the order form so that it works as follows:
• Prices for fixed-price items are listed in the Sched1 worksheet. Prices for variable-priced soft bait packages are listed in the
Sched2 worksheet. A price schedule code (1 for fixed priced, 2 for soft bait variable priced) is included in the Item List
worksheet.
• Shipping costs are based on the freight customer type, destination region, shipping method, and total shipping volume.
Users enter the freight customer type text and state abbreviation. You need to use this information to retrieve the freight
customer type number and region number.
Calculate the shipping volume based on the volumes listed by item number in the Item List worksheet.
• Discounts are calculated as a percentage of the total order value as listed in the Discounts worksheet. Orders of less than
$3,000 receive no discount, orders of at least $3,000 but less than $5,000 receive a 2% discount, orders of at least $5,000 but
less than $10,000 receive a 3% discount, and orders of at least $10,000 but less than $25,000 received a 4% discount. Orders
of $25,000 or more receive a 6% discount as you complete the Orders worksheet, select functions that are flexible enough to
allow for additional items or up to 23 pricing schedules.
Use range names to make the form easy to use and troubleshoot.
If the item number field is blank, be certain your form displays a blank cell for the resulting unit price and total.
Test that your workbook calculates the correct values.
Where appropriate, formulas should work when copied down the column or across the row.
Format your values appropriately.
4 WEEK # 2 – EXERCISES CHAPTER # 5 1. Open the workbook named Fishing.xlsx located in the assignment, and then save the file as
W2-3-Fishing-Orders-YourName.xlsx. • Orders: Item #201 (150 items), Item #209 (315 items), Item #218 (500 items)
• Shipped by truck to California (CA) to a preferred customer
3. In cell B9 of the Orders worksheet, write a formula that enters the item description.
Copy the formula to cells B10:B14.
4. In cell D9 of the Orders worksheet, write a formula that calculates the total volume of the first line item (quantity multiplied by
volume per item).
Copy the formula to cells D10:D14.
5. In cell E9 of the Orders worksheet, write a formula that calculates the unit price.
Copy the formula to cells E10:E14.
6. In cell F9 of the Orders worksheet, write a formula that calculates the total value of this line item.
Copy the formula to cells F10:F14.
7. In cell F16 of the Orders worksheet, write a formula that calculates the total for all items, excluding discounts and shipping.
8. In cell D16 of the Orders worksheet, write a formula that calculates the total shipping volume of this order.
9. In cell F17 of the Orders worksheet, write a formula that calculates the discount, if any, on this order.
10. In cell F18 of the Orders worksheet, write a formula that calculates the shipping costs directly from the state and ship method
(Truck, Rail, or Air).
TROUBLESHOOTING: In order to complete this step so that the cost of shipping automatically updates correctly based on
the shipping parameters, you should calculate the value manually for several different options and compare it against the
resulting values on your worksheet. Using range names will help simplify the formulas.
If incorrect values result, break down the formula to test each argument as a separate formula to determine where the
discrepancies can be found.
11. In cell F20 of the Orders worksheet, write a formula that calculates the grand total of this order.
12. Test the formulas by entering different order values, and then repeat Step 2.
13. Save and close the W2-3-Fishing-Orders-YourName.xlsx workbook.
Turnitin Report Formatting Title Page Citation Outline
2
Fishing Division Order Form
Customer
Name
Freight Customer Type (regular, preferred)
Ship
Ship Method (truck,rail,air,boat)
Item#
Description
Quantity
Volume (cf)
$/unit
Total
Total Order
Discount
Shipping
Grand Total
Item List
Item# Description
Price Schedule
Ship Volume (CF)
2
0
1
Proline Cast Reel
0.
5
202
Supra Cast Reel
20
3
Ultra-Lite Spin Reel
20
4
Proline Spin Reel
205
Proline Underspin Reel
206
Proline Lite Stick Cast Rod
0.60
207
Cyclo Stick Cast Rod
208
Cyclo Stick Spinning Rod
209
Aberdeen Hook Set
0.35
210
Baitholder Hook Set
211
W
212
Trailer Hook Set
213
Mono-filament line spool
0.45
214
Florocarbon line spool
215
1/4 lb Fish Line
216
1 lb Fish Line
217
Waders Paints
2.25
218
Soft Bait Bulk – Worms
0.05
Sched1
Item# $/unit
201
$ 39.99
202
$ 50.99
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
$ 8.00
214
$ 16.99
215
$ 6.00
216
$ 26.99
217
$ 169.95
Sched2
Unit Pricing – Soft Bait Packages
# of Packages
0
$ 1.99
100
$ 1.69
200
$ 1.39
400
$ 1.29
800
$ 1.00
Total Order Value
$ 0 0
$ 3,000
$ 5,000
$ 10,000
$ 25,000
Discount
0%
2%
3%
4%
6%
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
$ 36.00
Ship Table 2 – Preferred Customer Orders $/CF
1
$ 2.48
$ 2.42
$ 27.00
2
$ 2.75
$ 2.64
3
$ 3.30
$ 3.08
4
$ 3.85
$ 3.52
$ 33.00
5
$ 4.40
States
Name
Abbreviation
Region#
AL
MA
SE
Region Numbers
A
LA
AK
AR
AZ
SW
AR
KANSAS
CA
OR
IA
NE
CO
MW
CONNE
CT
UT
DE
WA
DIST
RI
DC
FL
ID
GEORGIA
GA
HAWAII
HI
IDAHO
IL
IN
I
ND
IOWA
KANSAS
KS
KENTUC
KY
LOUISIANA
MAINE
ME
MARYLAND
MD
MASSACHUSETTS
MI
MINNESOTA
MN
MISSISSIPPI
MS
MISSOURI
MO
MONTANA
MT
NEBRASKA
NE
VA
NV
NEW HAMPSHIRE
NH
NEW JERSEY
NJ
NEW MEXICO
NM
NEW YORK
NY
NORTH CAROLINA
NC
NORTH DAKOTA
OH
OK
OREGON
PENNSYLVANIA
PA
RHODE ISLAND
SOUTH CAROLINA
SC
SOUTH DAKOTA
SD
TENNESSEE
TN
TEXAS
TX
UTAH
VERMONT
VT
VIRGINIA
WASHINGTON
WEST VIRGINIA
WV
WI
WY
CA
Truck
Discount
ItemList =’Item List’!$A$2:$D$19
Sched1 =Sched1!$A$2:$B$18
Sched2 =Sched2!$A$3:$B$7
Ship1 =Ship!$B$4:$D$8
Ship2 =Ship!$B$12:$D$16
States =States!$B$2:$D$52
7=Discount
8=Shipping
Ship to State abbreviation
Ship Method (truck,rail,air,boat)
2
Golf Products Order Form
Shipping
1
7
5
Region
Shipping method
3
Customer type
Item#
Quantity
Description
Total
1
10
7 2
4
8
15
5
20
Total Order
Shipping
Handling
Discount
Grand Total
FNU-A
Costs
Pricing Information for Golf Equipment
TheZone Equipment Division
Item# Description
Price/item
1
Titanium Driver – Men
$ 325.00
2
Fusion Driver – Women
$ 545.00
3
Titanium Driver – Women
$ 300.00
4
Fairway Woods – Men
$ 1
9
5
Hybrid Woods – Men
$ 59.95
6
Fusion Fairway – Women
$ 500
7
Stainless Steel Fairway – Women
$ 200.00
8
Irons w/Steel Shafts – Men
$ 525.00
9
Hybrid Irons – Men
$ 399.00
10
Irons w/Steel Shafts – Women
$ 375.00
11
Hybrid Irons – Women
12
Steel Putter – Men
$ 119.99
13
Steel Putter – Women
14
Forged Wedges – Men
$ 105.00
15
Package – Men
$ 675.00
16
Package – Women
Handling charges:
Total order value
$ –
$ 2,500
$ 5,000
$ 7,500
$ 10,000
$ 12,500
Handling charge (% of order value)
0.007
0.006
0.005
0.004
0.003
0.002
Maximum Handling Fee
$ 30.00
Discounts
Discount
Total Order Value
$ – $ –
Less than $5,000, no discount
$ 125
At least $5,000 but less than $10,000, $150 discount
$ 500 $ 10,000
At least $10,000 but less than $50,000, $400 discount
$ 1,000
$ 50,000
$50,000 or more, $1000 discount
Shipping Method – Standard
Region 1 2 3 4
1
$ 0.11
$ 0.12
$ 1.50
NA
2
$ 0.19
$ 0.13
$ 2.00
3
$ 0.25
$ 0.14
$ 2.10
$ 0.20
4
$ 0.29
$ 0.17
$ 2.25
5
$ 0.46
$ 0.41
$ 3.50
$ 0.35
Shipping Method – Preferred
1
$ 0.10
$ 1.35
2 $ 0.17 $ 0.12
$ 1.80
3
$ 0.23
$ 1.89
$ 0.18
4
$ 0.26
$ 0.15
$ 2.03
5
$ 0.42
$ 0.37
$ 3.15
$ 0.32
Shipping Method – Most Preferred
1
$ 0.09
$ 1.22
2
$ 0.16
$ 1.62
3 $ 0.20 $ 0.12
$ 1.70
4
$ 0.24
$ 1.82
5 $ 0.37
$ 0.33
$ 2.84
$ 0.28
2. Examine the contents of each worksheet, and name the ranges listed in Table 5.10. Use these range names as appropriate in
LEVEL
Vijay has been asked to develop an order form for fishing equipment. He has already created a workbook named Fishing.xlsx, which
Complete the following:
2. Use the following test data:
Order an Essay Now & Get These Features For Free:
