Get Your Exam Data

Remember to use Get Data in Excel!

Click HERE to open the exam data in a new web browser. Go to File > Create a Copy > Download a Copy. Use the downloaded copy as your data source for this assessment.

Data Preparation

There is no associated marks for these steps. However, these steps are essentials for you to obtain the correct answers in the subsequent questions.

Data Preparation Step 1

Refer to the "Business Travel" column. Identify all blank cells and enter "Non-Travel" in all blank cells.

Data Preparation Step 2

Refer to the "Business Travel" column. Replace the "_" symbol with a space.

Data Preparation Step 3

Change the number format of "Monthly Income" to "Currency" or "Fixed Decimal Places".

Data Preparation Step 4

Change the number format of "Percent Salary Hike" to "Percentage". Make sure to have 2 decimal places.

Data Preparation Step 5

Based on the information in "Monthly Income" and "Percent Salary Hike", add a new column showing "New Monthly Income". Use an appropriate formula to get the result.

Simple Analysis

In this section you will be tested on creating a simple Pivot Table analysis using COUNT, SUM, MIN, AVERAGE, and MAX functions.
For all the answers involving currency/accounting/percentage data, be sure to get an answer of 2 decimal places.

Intermediate Analysis

In this section you will be tested on creating multi-levels Pivot Table analysis. You need to combine the usage of 'Rows' and 'Columns' field in the Pivot Table Fields panel in order to obtain the answers.
For all the answers involving currency/accounting/percentage data, be sure to get an answer of 2 decimal places.

Advance Analysis

In this section you will be tested on creating custom grouping and calculated field in Pivot Table.
For all the answers involving currency/accounting/percentage data, be sure to get an answer of 2 decimal places.