Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

Case Problem 2

Data Files needed for this Case Problem: Sales1.xls, Works1.xls

Wizard Works
Andrew Howe manages orders for Wizard Works, an online seller of cus-

tom fireworks. Andrew has asked you to help him use Excel to develop some reports for an

upcoming meeting. Andrewâ€™s first project is to create a sales report for three different types

of Wizard Works products sold in four different regions. After you enter the sales data,

Andrew wants you to calculate the total, average, minimum, and maximum sales for each

product and for each region, and then for all products and all regions. You will also need to

calculate the percentage of sales for each product.

Apply

Using what you have

learned in Session 2.1,

create a workbook that

summarizes regional

sales information.

To complete this task:

1. Open the
Sales1
workbook located in the Tutorial.02\Cases folder included with

your Data Files, and then save the workbook as
Sales2
in the same folder.

2. Enter your name and the date in the Documentation sheet.

3. Switch to the Sales Summary sheet, and enter the sales data shown in Figure 2-34.

Figure 2-34

Units Sold

Region

Fountains

Firecrackers

Rockets

Region 1

1503

1380

814

Region 2

1081

1873

1103

Region 3

1773

2415

644

Region 4

2289

2103

1474

4. For each product, enter formulas to calculate the total sales for all regions, the aver-

age sales per region, and the maximum and minimum sales over all the regions.

5. For each product, enter a formula that uses absolute cell references to calculate the

percentage of units sold per region.

6. Summarize the sales for all three of these Wizard Works products by calculating the

total, average, maximum, and minimum units sold for all products in all regions.

7. Calculate the percent of units sold for all products in each region.

8. Print the Sales Summary worksheet in landscape orientation, and then save your

changes to the workbook.

Using what you

learned in Session 2.2,

create a worksheet that

determines shipping

costs and discounts for

customer orders.

(
Note:
The following steps are
optional
. You should attempt them only if you com-

pleted
Session 2.2
in the tutorial.)

Andrew also needs your help calculating the total costs of customer orders. You need to

compute the total cost of each order, which includes the shipping cost and special dis-

count offered by the store. Customers can choose one of two shipping options: Standard

shipping, which costs $8.95 or Express shipping, which costs $14.95. Wizard Works also

offers a 5% discount for orders that are more than $200 (not including the shipping cost).

You need to enter formulas that use the IF function to determine the shipping cost and dis-

count, if applicable, in order to calculate the total cost of each order.

9. Open the
Works1
workbook located in the Tutorial.02\Cases folder, and then save

the workbook as
Works2
in the same folder.

10. Enter your name and the date in the Documentation sheet, and then switch to the

Orders sheet.

11. In cell F7, calculate the cost of each product ordered, which is equal to the price of

the product multiplied by the quantity.