Posted: April 25th, 2025

excel 3

 
Open Week # 2 – Chapter 5 – Exercises – Level 1 and save it to do the exercises

Make W2-Exercise 1: Level 1 – Order Form for Golf Balls.

Golf Balls – 23FallB

ping Costs

Discount

$/each

A

0%

15%

Ship

Shipping

Total Price

Shipping Discount

Truck

Customer

Truck
103

Customer

Rail

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.