Answered step by step
Verified Expert Solution
Question
1 Approved Answer
PLEASE use Excel to solve this!!!!! or I will rate you zero A1 4 x V fx Pricing Information for Golf Equipment A B C
PLEASE use Excel to solve this!!!!! or I will rate you zero
A1 4 x V fx Pricing Information for Golf Equipment A B C D E F G 1 Pricing Information for Golf Equipment TheZone Equipment Division 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Item# Description 1 Titanium Driver - Men 2 Fusion Driver - Women 3 Titanium Driver - Women 4 Fairway Woods - Men 5 Hybrid Woods - Men 6 Fusion Fairway - Women 7 Stainless Steel Fairway - Women 8 Irons w/Steel Shafts - Men 9 Hybrid Irons - Men 10 Irons w/Steel Shafts - Women 11 Hybrid Irons - Women 12 Steel Putter - Men 13 Steel Putter - Women 14 Forged Wedges - Men 15 Package - Men 16 Package - Women Price/item $ 325.00 $ 545.00 $ 300.00 $ 199.00 $ 59.95 $ 500.00 $ 200.00 $ 525.00 $ 399.00 $ 375.00 $ 399.00 $ 119.99 $ 119.99 $ 105.00 $ 675.00 $ 675.00 A1 4 x fx Handling charges: A B C D E F G H $ $ 2,500 $ 5,000 $ 7,500 $ 0.006 0.005 0.004 10,000 $ 12,500 0.003 0.002 0.007 1 Handling charges: 2 Total order value 3 Handling charge (% of order value) 4. 5 6 Maximum Handling Fee 7 8 9 10 $ 30.00 B22 x fx A D E 1 B Golf Products Order Form Shipping weight Region number Shipping Method 175 1 3 2 3 4 5 6 7 8 9 Quantity Description Total Item# 1 7 4 10 2 8 2 20 10 15 5 Total Order Shipping Handling Discount 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Grand Total B16 fx A B C D E 1 2 3 Discount Total Order Value Description $ $ Less than $5,000, no discount $ 150 $ 5,000 At least $5,000 but less than $10,000, $150 discount $ 400 $ 10,000 At least $10,000 but less than $50,000, $400 discount $ 1,000 $ 50,000 $50,000 or more, $1000 discount 4 5 6 7 8 O 1 1 1 B2 fx 1 A B c E F G I 1 1 2. 3 4 4 Region 1 $ 2 $ 3 $ 4 $ 5 $ Shipping Method 1 2 3 0.09 $ 0.10 $ 1.22 0.16 $ 0.11 $ 1.62 0.20 0.12 $ 1.70 $ 0.24 $ 0.14 $ 1.82 $ 0.37 $ 0.33 $ 2.84 $ 5 NA NA 0.16 0.16 0.28 6 Alealeal 7 8 9 10 11 Complete the following: 1. Open the workbook named Module 2 - Golf.xlsx and then save the file as GolfOrders your last name).xlsx. 2. Examine the contents of each worksheet, and name the ranges listed in the table below. Use these range names as appropriate in Questions 3 through 9. Range Name ItemPrice. Handling Fee Disc TotOrdervol Worksheet Costs Handling Discounts Discounts Cells A5:C20 B2:G3 A2:A5 B2:35 3. In cell C7 of the Orders worksheet, write a formula that displays the product description for the first item in the order. Copy the formula into cells C8:011. 4. In cell D7 of the Orders worksheet, calculate the total value of the item (price multiplied by quantity). Copy the formula into cells 08:011. 5. In cell D14 of the Orders worksheet, calculate the total cost of the order. 6. In cell D15 of the Orders worksheet, calculate the total shipping charge for this order based on three variables: the ship-to region, the total weight, and the method of shipping. a. HINT: This will require a HLOOKUP and cells C2, C3, and C4 to retrieve the answer. For the rewnymber argument of the HLOOKUP, use C3 + 1 7. In cell D16 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. a. HINT: The HLOOKUP will need to have the fourth argument be True for the approximate match. 8. In cell D17 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 (should be a negative number). Discounts are based on the total order value excluding shipping and handling fees. 9. In cell D19 of the Orders worksheet, calculate the grand total for the order. 10. Save and close the GolfOrders.xlsx workbookStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started