Microsoft Office Tutorials and References
In Depth Information
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.
Using what you have
learned in Session 2.1,
create a workbook that
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.
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
costs and discounts for
( 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
11. In cell F7, calculate the cost of each product ordered, which is equal to the price of
the product multiplied by the quantity.