Close all workbooks that are currently open. In a new workbook create a table query by selecting the following items from the Sales Analysis folder:

	Region
	Year
	Country Name
	Sales Revenue (SUM)

Place Region on the page axis and Year, Country Name, and Sales Revenue SUM (in that order) on the top axis.

Hint: To create a new workbook, click File > New.

Note: If you do not see the column headings on your query, select Edit > Worksheet Properties from the menu bar. Click the Table Format tab, select Show column headings, and click OK.

You can sort the results in a table by using any single column. Standard sort sequences are available: low to high or high to low.

For example, right-click Year and select the Sort Low to High option from the shortcut menu.

Year now appears sorted in ascending sequence.

To organize and make the results easier to read or analyze, you can group sort the results by any column that includes repeated values.

For example, right-click Country Name and select the Group Sort option from the shortcut menu.

Drag Country Name to the left of Year.

Select Tools > Sort from the menu bar.

The Edit Worksheet window appears with the Sort tab automatically selected.

You can view all sorts that you have applied to the table already and modify them, if necessary. For example, you can delete a sort, change the order of the columns by which the table rows are sorted, or hide a column from the table by selecting the Hidden check box. You can also add new sorts and format data in the sorted columns.

Select the sort applied to Country Name (click anywhere in that row) and click Format. The Format Data window appears with the Breaks tab automatically selected.

Change the Line thickness to 4 and the color to light gray. This inserts a light gray line with a thickness of 4 after each change in Country Name.

Click OK twice to close the Format Data and the Edit Worksheet windows.

Double-click the worksheet name tab, Sheet 1. The Worksheet Properties window appears with the General tab automatically selected. Enter Annual Revenue by Country in the Name text field.

Select the Table Format tab. Deselect the Show vertical gridlines and Show horizontal gridlines check boxes.

Click OK to close the Worksheet Properties window.

Select File > Save from the menu bar. Save the new workbook to the database as Revenue Analysis Reports.

Create a new crosstab query in the Revenue Analysis Reports workbook by selecting the following items from the Sales Analysis folder:

	Region
	Year
	Prod Category
	Sales Revenue (SUM)

Hint: Click Edit > Add Worksheet.

Place Region and Prod Category (nested in that order) on the side axis of the crosstab. Place Sales Revenue SUM (Data Point) and Year (nested in that order) on the top axis of the crosstab.

Click Finish.

Note: If the crosstab labels are not visible, right-click any item heading within the query. From the shortcut menu select Format > Crosstab. Select Show item labels from the Worksheet Properties window, Crosstab Format tab and click OK.

Items in a crosstab are automatically group sorted in the order in which items are nested on the top and side axes of the crosstab. By default, item headings are sorted low to high. To change the default sort order of Region, for example, do the following:

Right-click the Region item heading and select Sort High to Low from the shortcut menu.

The row headings for Region are sorted alphabetically high to low (descending sequence).

Alternatively, select Tools > Sort from the menu bar. The Sort Crosstab dialog box appears. In the Item to Sort area, click Region and select High to Low from the Default sort on row headings drop-down list.

Data points in a crosstab can be sorted in the same way by selecting a single column or row, using the Sort Crosstab dialog box shown in this screenshot. You can define the sort on any item of the side or top axes.

Note: Sorts defined on data points take precedence over default sorts defined on column or row headings.

Select Edit > Worksheet Properties from the menu bar and name the new worksheet Regional Annual Revenue by Category.

Select the Crosstab Format tab and select <blank> from the Show null values as drop-down list.

4.

Click OK to close the Worksheet Properties window.

Open the Annual Revenue by Country worksheet. Press and hold down the Ctrl key (to select multiple items) and click the Country Name and the Year column headings.

Click the Background Color icon on the Formatting toolbar and select medium-yellow from the color palette.

Your changes appear.

Click the Sales Revenue SUM column heading to highlight the data in the corresponding column. Click the Number Format icon on the Formatting toolbar.

Select the Currency Format icon from the drop-down list.

Your changes appear.

Right-click the Sales Revenue SUM column heading and select Edit Heading from the shortcut menu.

Enter Sales Revenue in the Heading text field.

Click OK.

Open the Regional Annual Revenue by Category worksheet. Remove the Available Items and Selected Items Panes. (Hint: Click View and deselect both items.)

Click the tile located at the top-left corner of the data grid to select all the data values as shown in the screenshot).

Select the Left justified and Currency Format icons from the Formatting toolbar.

Select Middle East in the Region column of the side axis.

Click the down-arrow icon next to the New Condition icon on the Standard toolbar and select the Not Equals icon from the drop-down list.

Middle East region data point values are filtered from the crosstab.

Save the workbook but do not close it.

Click the Conditional Format icon on the Formatting toolbar.

The New Conditional Format dialog box appears. Select Sales Revenue SUM from the Item drop-down list.

Select greater than (>) from the Condition drop-down list and enter 2500000 (2,500,000) in the Value text field.

Click Format. The Format dialog box appears.

In the Color grouping, select medium-blue from the Background color palette.

Click OK twice to close both the Format and New Conditional Format dialog boxes.

All the cells with Sales Revenue SUM values exceeding $2,500,000 are highlighted in medium blue.

Select Format > Conditional Formats from the menu bar.

The Conditional Formats dialog box appears.

Click New Stoplight Format to open the New Stoplight Format dialog box. Enter 250000 (250,000) in the Unacceptable text field and 1000000 (1,000,000) in the Desirable text field.

Click OK. An alert message appears, warning you that an overlap exists between the ranges in the previously defined conditional format and the new stoplight format.

Click OK to close the alert dialog box.

Deselect the check box that corresponds to the Sales Revenue SUM > 2500000 conditional format to deactivate it.

Click OK to close the Conditional Formats dialog box. The data is highlighted accordingly, as shown in the screenshot.

Save the workbook but do not close it.

Open the Annual Revenue by Country worksheet in the Revenue Analysis Reports workbook. From the Add Worksheet icon drop-down list on the Standard toolbar, select Duplicate as Table.

The Duplicate as Table window appears with the Table Layout tab automatically selected. Drag Year to the Page Items area and Region to the top axis of the table, to the left of Country Name.

Select the Sort tab. Use the Column drop-down list to replace Country Name with Region.

Click Format. The Format Data window appears with the Breaks tab automatically selected.

Select the Format tab and select medium-yellow from the Background color palette.

Click OK to close the Format Data window.

Click Properties at the bottom of the Duplicate as Table page. The Worksheet Properties window appears. Enter Regional Revenue by Country in the Name text field.

Click OK twice to close the Worksheet Properties and Duplicate as Table windows. The new table worksheet, Regional Revenue by Country, is added to the Revenue Analysis Reports workbook.

Click the Sales Revenue column heading.

Click the down-arrow icon next to the New Total icon on the Standard toolbar and select Sum from the drop-down list.

A subtotal is inserted after each change in region, and a grand total (for the year shown on the page axis) is added at the bottom of the table.

Right-click the grand total for Sales Revenue SUM and select Edit Total from the shortcut menu.

The Edit Total window appears. Deselect Generate label automatically and enter Grand total for the year in the What label do you want to be shown? text field.

Click Format Data. The Format Data window appears with the Format tab automatically selected. Select Bold and Underline from the Style selections.

Click OK twice to close the Format Data and Edit Totals windows. (Note: You may have to resize the Sales Revenue column.)

Right-click the subtotal for any Sales Revenue SUM and select Edit Total from the shortcut menu.

The Edit Total window appears. Deselect Generate label automatically and enter Subtotal for followed by a space in the What label do you want to be shown? text field.

Click the down-arrow icon to the right of this field and select Insert Value from the drop-down list.

Click Format Data. The Format Data window appears with the Format tab automatically selected. Select Italic and Underline from the Style selections.

Click OK twice to close the Format Data and Edit Totals windows.

Save the workbook, but do not close it.

Select Edit > Add Worksheet from the menu bar. Select the Crosstab option and deselect the Title, Page Items, Graph, and Text Area check boxes.

Click Next. From the Sales Analysis folder select the following items:

	Region (Europe, Americas)
	Year
	Quarter
	Sales Revenue (SUM)

Click Next. Place Region followed by Year on the side axis of the crosstab.

Place the Sales Revenue SUM data point and Quarter (nested in that order) on the top axis of the crosstab.

Click Finish.

Select the entire data grid and select Currency Format from the Formatting toolbar.

Name the new worksheet Quarterly Sales for Europe and America.

Select Tools > Totals from the menu bar.

The Edit Worksheet window appears with the Calculations tab of the Select Items page automatically selected.

On the bottom of the Edit Worksheet window, click New and select New Total from the drop-down list.

The New Total dialog box appears. Select the Grand total on right option.

Deselect Generate label automatically and enter Year Total in the What label do you want to be shown? text field.

Click OK to close the New Total dialog box. Do not close the Edit Worksheet window.

Click New and select New Total again. The New Total dialog box appears.

Select f(x) Average from the What kind of total do you want? drop-down list.

Select the Subtotal at each change in option, then select Region from the drop-down list.

Deselect Generate label automatically and enter Quarterly Average for followed by a space in the What label do you want to be shown? text field.

Click the down-arrow icon and select Insert Value from the drop-down list.

Click OK twice to close the New Total dialog box and the Edit Worksheet window. Year Total and Quarterly Average for the Americas and Europe appear on the crosstab.

Save the workbook, but do not close it.

Open the Quarterly Sales for Europe and America worksheet.

Select Edit > Duplicate Worksheet > As Crosstab from the menu bar. The Duplicate as Crosstab window appears with the Crosstab Layout tab automatically selected.

Select the Show Page Items check box and drag Region to the Page Items area.

Drag Quarter from the top axis of the crosstab to the side axis below Year.

Click Properties at the bottom of the Duplicate as Crosstab window. Enter Quarterly % Contributions in the Name text field.

Click OK twice to close the Worksheet Properties and Duplicate as Crosstab windows. The Quarterly % Contributions worksheet is added to the Revenue Analysis Reports workbook.

If the Selected Items Pane is not visible, click View > Selected Items Pane. In the Selected Items Pane, click each of the item totals that were copied to the new worksheet as shown in the screen capture.

Click the Remove from Worksheet icon on the Selected Items toolbar.

Click the New Percentage icon on the Standard toolbar.

The New Percentage dialog box appears.

Deselect Generate name automatically and enter % Contribution for Year Total in the What do you want to name this percentage? text field.

Select the Subtotal at each change in option and select Year from the drop-down list.

Deselect Show the percentage of the grand total for each subtotal.

Select Show subtotal and subtotal percentage (this check box should be selected by default) and enter Year Total in the Label text field.

Click OK to close the New Percentage dialog box. The % Contribution for Year Total column is added to the crosstab showing the percent contribution to the year total for each quarter.

Save the workbook.

In a new workbook, create a crosstab query by selecting the following items from the Profits Analysis folder:

	Prod Category
	Quantity Sold (SUM)
	Sales Revenue (AVG)
	Profit (SUM)

Place Prod Category on the side axis and Quantity Sold SUM, Sales Revenue AVG, and Profit SUM on the top axis of the crosstab as shown in the screen capture. Click Finish.

The new workbook appears.

Based on past performance, Peripherals and Accessories have contributed the most to the company's bottom line. To determine how well this category of products performed in each of the years from 1998 through 2001, continue with Step 2.

Click the drill icon next to Peripherals and Accessories and select Year from the Drill to Related list.

Though demand for these products peaked in the year 1999, overall profits declined from the prior year. You can surmise that profit for the year 1999 went down, while demand increased due to the decline in the average price per unit sold (Sales Revenue AVG). This is evidenced in the query; average price per unit sold in 1998 was approximately $130, whereas in 1999, it declined to approximately $90.

To investigate further, you decide to look into how individual products have performed over the years for which you have data.

Right-click in the data cell, Sales Revenue AVG, which shows the average price per unit sold for 1999, and select Drill from the shortcut menu.

The Drill dialog box appears.

From the Where do you want to drill to? drop-down list, select Drill to a Related Item.

Select Prod Name.

Click OK. Prod Name is added to the worksheet.

Observe that there was no significant change in the average price per unit sold (Sales Revenue AVG) for individual products in the Peripherals and Accessories category in each of the four years for which you have data. Also, observe that demand (Quantity Sold SUM) for the two most expensive products (17" LCD w/ built-in HDTV Tuner and 18" Flat Panel Graphics Monitor) in the category, fell to almost half in 1999 compared to the demand from the year before.

Though more products were sold from the Peripherals and Accessories category in 1999, the fact that demand for the more expensive products fell to almost half compared to 1998, explains both the lower average price per unit sold ($90) in 1999 and the decrease in the overall profit for the year.

Name the worksheet Peripherals and Accessories by Year and save the workbook as Product Demand Analysis.

Create a crosstab query in the Product Demand Analysis workbook by selecting the following items from the Profits Analysis folder:

	Quantity Sold (SUM)
	Region
	Year

Place Region on the side axis of the crosstab and Quantity Sold SUM and Year on the top axis. Name the worksheet Demand by Region.

Click the drill icon next to 1999 on the top axis and select Quarter from the drop-down list.

The demand for each quarter in 1999 is added to the worksheet.

Click the drill icon next to Region and select Country Name from the drop-down list.

Country Name is added to the worksheet.

To collapse the drilled results back to the Region level, click the drill icon next to Region and select Collapse from the drop-down list.

Click the drill icon next to 1999 and select Collapse from the drop-down list.

The query should look like this screen capture:

Save the workbook, but do not close it.

In the Demand by Region worksheet of the Product Demand Analysis workbook, right-click the Region heading and select Drill from the shortcut menu.

By default, the Drill Up/Down option is selected from the Where do you want to drill to? drop-down list within the Drill dialog box. All items in the predefined hierarchy on which you can drill are shown.

Select Country Name from the list.

Click Show Advanced. The Drill dialog box expands to reveal additional drill options.

Select the Replace current item option.

Click OK. Country Name replaces Region on the worksheet.

Rename the worksheet Demand by Country and save your changes.

In the Demand by Country worksheet of the Product Demand Analysis workbook, double-click the data cell that displays the number of products sold to India in 1999.

The Drill dialog box appears with Drill to Detail and Show component rows of selected value automatically selected.

Click Advanced. Select the Place drill results in a new sheet check box and select the Table option.

Click OK. A new worksheet is added to the Product Demand Analysis workbook.

The three rows returned by the query display specific detailed information for the three products that were sold to India in 1999.

Name the new worksheet Products Sold to India in 1999 and save your changes.

Create a new table query in the Product Demand Analysis workbook by selecting the following items from the Profits Analysis folder:

	Prod Category
	Sales Revenue (SUM)
	Quantity Sold (SUM)

Place all the items on the top axis of the table. Name the worksheet Sales Results by Category.

In the same workbook, create another table query by selecting the following items from the Profits Analysis folder:

	Prod Category
	Prod Name
	Quantity Sold (SUM)
	Sales Revenue (SUM)

Place all items on the top axis of the table and group sort the results by Prod Category.

Name the worksheet Sales Results by Product.

Open the Sales Results by Category worksheet. Right-click anywhere in the Prod Category column of the table and select Manage Links from the shortcut menu.

The Manage Links dialog box appears with Prod Category automatically selected in the Item list.

Click New Link. The New Link dialog box appears.

Enter Sales by Product Name in the What would you like to name this link? text field.

Select Worksheet from the Where do you want to link to? drop-down list and select the Sales Results by Product worksheet from the Destination Worksheet drop-down list.

Note: Product Demand Analysis (the current workbook) is automatically selected as the destination workbook. To link to a worksheet in a different workbook, click Browse and make your selection.

Click OK to close the New Link dialog box. The Sales by Product link is added to the Links area of the Manage Links dialog box.

Click OK to close the Manage Links dialog box. A drill icon is added next to all the cell values in the Prod Category column.

Click the drill icon next to any of the product category names in the Prod Category column. The name of the link that you just defined appears in the pop-up menu.

Click the link name to display the results in the destination worksheet.

Save the changes to your workbook.