Accounting

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. 2. Examine the contents of each worksheet, and name the ranges listed in Table 5.10. Use these range names as appropriate in

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. LEVEL

3

WEEK # 2 - EXERCISES CHAPTER # 5

Exercise 3: Level 3 – Fishing Order Vijay has been asked to develop an order form for fishing equipment. He has already created a workbook named Fishing.xlsx, which

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

Complete the following:

1. Open the workbook named Fishing.xlsx located in the assignment, and then save the file as

W2-3-Fishing-Orders-YourName.xlsx. 2. Use the following test data:

• 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.