Reference no: EM132419406

INSTRUCTIONS -

Visualizing Data - Using the Draft created in Activity 3.2 during Week3, continue developing your report

Step 1: Conditional Format Main Data Set

Create a three color scale Conditional format for the currency figures using all dollar amounts in the data table.

Step 2: Add four insights to your Summary worksheet

On your summary sheet include (4 different insights):

- Which industry has the highest average rates in the most recent two years?

- Which industry has the lowest average rates in the most recent two years?

- Are at least two industries similar in pay? If so, which industries?

- Describe at least one additional insight you had from your analysis.

Attached is a screenshot of a sample Summary sheet to this point. Your Industries may differ.

Step 3: Create the Pivot Table 1

Go back to main data set

Create a pivot table for all months for each industry.

All values should be changed to "Average" and be in a currency format with two decimals.

Apply a three color scale conditional format for each industry's column individually.

Step 4: Add two insights for Pivot Table 1 to Summary Sheet

On your summary sheet, describe at least one insight you found from applying the conditional format on the comparisons for each industry by month.

Are there any months that are consistently high or consistently low among the industries?

Step 5: Create Pivot Table 2

Go back to the main data set.

Create a second pivot table for all years for each industry.

Place this pivot table below your first pivot table (use "Existing sheet" option).

Create a three-color scale conditional format for each industry individually.

Step 6: Add insights for Pivot Table 2 to Summary Sheet

On your summary sheet, describe at least one insight you found from applying the conditional format on the comparisons of individual industries for all years.

Step 7: Format the document for printing

Apply "Repeat Row1 at Top" to the main data set (see Week3 Tutorial if you need help on this task).

Format the workbook for printing. Ensure data does not break across pages horizontally.

Apply worksheet design guidelines (Note: Page Layout view will not be completed on the main data set due to Freeze Panes; however, it should be applied to both your pivot table worksheet and summary worksheet).

Step 8: Save to PDF and Submit both files

A sample of the final Assignment appearance is shown below.

You will submit: One Excel workbook with 3 worksheets and One PDF file with 4-5 pages.

Attachment:- Assignment Files.rar