Open Week # 2 – Chapter 5 – Exercises – Level 1.pdf and save it to do the exercises
Make W2-Exercise 1: Level 1 – Order Form for Golf Balls.
Golf Balls – 23FallB
| Pricing Information for Golf Balls | ||||||||||
| TheZone Equipment Division | ||||||||||
| Unit Pricing Golf Balls | Shipping Costs | Shipping Discount | ||||||||
| Qty | $/each | Method | $/each | Total Price | $0 | $500 | $1,000 | |||
| Rail | Shipping Discount A | 0% | 15% | 20% | ||||||
| Truck | Shipping Discount B | 0% | 10% | 15% | ||||||
| Ship | ||||||||||
| Customer | ||||||||||
| Order# | Cust ID | Shipping | Quantity | Unit Price | Total Price | Unit Shipping | Total Shipping | Shipping Discount | Grand Total | |
| 101 | RG339 | Truck | 354 | |||||||
| 102 | HM394 | Customer | 47 | |||||||
| 103 | XA843 | Boat | 1900 | |||||||
| 104 | PK423 | Truck | 103 | |||||||
| 105 | PK880 | Customer | 360 | |||||||
| 106 | EN737 | Rail | 135 |
,
1
WEEK # 2 – EXERCISES CHAPTER # 5
W2-Exercise 1: Level 1 – Order Form for Golf Balls In addition to creating an order form for tennis balls, Vijay also needs to create an order form for golf balls. TheZone sells golf balls in unit packages of a dozen balls ($/each represents the price per package).The shipping charges for golf balls are slightly different from those for tennis balls. Table 5.6 shows the unit prices and shipping charges for golf balls.
Vijay has already created a Golf workbook containing lookup tables and an order form in a worksheet named Golf Balls. See Figure 5.11. In these steps, your task is to complete the order form, using the appropriate lookup functions to calculate the total prices, total shipping charges, and grand totals. Complete the following:
2
WEEK # 2 – EXERCISES CHAPTER # 5
1. Open the workbook named Golf.xlsx located in the Chapter 5 folder, and then save the file as W2-1-Golf-Orders –
YourName.xlsx.
2. In the Golf Balls worksheet, complete the Unit Pricing lookup table to include the units and corresponding prices that TheZone charges for golf balls.
3. Complete the Shipping Costs table to list the appropriate unit shipping charges.
4. In cell E14, use the appropriate lookup function to calculate the unit price for this order based on the quantity ordered. Write the formula so that it can be copied down the column, and then copy the formula into cells E15:E19.
5. In cell F14, calculate the total price for the order (excluding shipping). Write the formula so that it can be copied
down the column, and then copy the formula into cells F15:F19.
6. In cell G14, calculate the unit shipping charge for the order based on the shipping method. Write the formula so that it can be copied down the column, and then copy the formula into cells G15:G19. Correct data entry errors, as necessary.
7. In cell H14, calculate the total shipping cost for the order. Write the formula so that it can be copied down the
column, and then copy the formula into cells H15:H19.
8. In cell I14, calculate the shipping discount for the order, using Shipping Discount B, based on the total price (column F). The discounted value will be the corresponding percentage times the total shipping cost previously calculated. Write the formula so that it can be copied down the column, and then copy the formula into cells I15:I19.
9. In cell J14, calculate the grand total for the order. Write the formula so that it can be copied down the column, and
then copy the formula into cells J15:J19.
10. Add your name and Current Date at the end of the workbook.
11. Save and close the W2-1-Golf-Orders -YourName.xlsx workbook.
