Question 1.

PIVOT TABLE/CHART

Project Scenario: "Apex Corp. Sales Data Analysis"

The project involves analyzing a raw dataset containing sales transactions with fields
such as: Order Date, Region, Sales Rep, Item, Units Sold, Unit Cost, and Total
Sales.
Order Date Region Sales Rep Item Units Sold Unit Cost Total Sales

01-01-2024 East Smith Binder 50 $10.00 $500.00

01-05-2024 Central Jones Pencil 100 $1.20 $120.00

01-08-2024 West Williams Desk 10 $250.00 $2,500.00

01-12-2024 Central Jones Pen 25 $5.75 $143.75

1/15/2024 East Smith Pencil 75 $1.20 $90.00

1/20/2024 West Williams Binder 40 $10.00 $400.00

1/23/2024 Central Jones Desk 5 $250.00 $1,250.00

1/28/2024 East Smith Pen 60 $5.75 $345.00

02-01-2024 Central Jones Pencil 30 $1.20 $36.00

02-04-2024 West Williams Pen 35 $5.75 $201.25

02-07-2024 East Smith Desk 8 $250.00 $2,000.00

02-10-2024 Central Jones Binder 20 $10.00 $200.00

2/15/2024 West Williams Pencil 150 $1.20 $180.00

2/18/2024 East Smith Pen 20 $5.75 $115.00

2/22/2024 Central Jones Pen 40 $5.75 $230.00

2/25/2024 West Williams Binder 60 $10.00 $600.00

03-01-2024 East Smith Pencil 90 $1.20 $108.00

03-05-2024 Central Jones Desk 12 $250.00 $3,000.00

03-08-2024 West Williams Pen 50 $5.75 $287.50

03-12-2024 East Smith Binder 30 $10.00 $300.00

Use Pivot Tables and Pivot Charts to answer the following questions:

1. Total Sales by Region: Create a Pivot Table to display the total sales for
each Region. Which region has the highest sales?

Answer 1. -
Step 1- Select the full table (including headers). Insert → PivotTable → New
Worksheet.

Step 2- Drag Region to Rows, Total Sales to Values.

Step 3- Values → Value Field Settings → Sum.

Final Output-

As per the data and pivot table, the central region has the highest sales.

2. Product Performance: Create a Pivot Table to show the average units

sold for each Item category. Which item has the highest average units sold
per order?

Answer 2.-

Step 1- Create a PivotTable from the dataset. (same as Step 1 of sub question 1
of Q1)
Step 2- Drag Item to Rows, Units Sold to Values.

Step 3- Click Value Field Settings → choose Average (not Sum).

 Step 4- The Pivot will show average units sold per Item.

The highest average unit sold per order item is Pencil.

3. Sales Representative Performance: Summarize the data to show the total

sales made by each Sales Rep. Sort the results to easily identify the top and
bottom performers.

Answer 3. -
Step 1- Pivot: Drag Sales Rep to Rows, Total Sales to Values (Sum).
Step 2- Sort Pivot descending to rank reps.

Final Output- Highest Sales Rep: Jones, Lowest Sales Rep: Smith

4. Sales Trends Over Time: Group the Order Date field by months and
years to analyze monthly and yearly sales trends. When were sales highest?

Answer 4. –

Step 1- Convert the Order Date column to Date type.

 Step 2- Insert → PivotTable. Place Order Date in Rows, Total Sales in Values.

The highest sales month is January 2024.

5. Interactive Filtering: Insert Slicers for Region and Item to dynamically filter
any of the created Pivot Tables and Charts.

Answer 5. -

Step 1- Click anywhere in a PivotTable.

Step 2- PivotTable Analyze → Insert Slicer.

Step 3- Check Region and Item → OK.

 Step 4- Use the slicers to filter all connected PivotTables/Charts

6. Visual Representation: Create a Pivot Chart (e.g., a clustered column chart

or a line chart) based on the sales trend data (Question 4) to visually highlight
seasonal patterns or growth.

Answer 6. -
Step 1- Create Pivot grouped by Month (see Q4).
Step 2- PivotChart → choose Line Chart.

Step 3- Format axes, add title “Monthly Sales — Apex Corp”.

 7. Targeted Insight: Use a filter or a slicer to specifically analyze the
performance of the Sales Rep "Smith" and present a summary of his sales
across all regions and items.
Answer 7. -
 In any Pivot with Sales Rep in Rows, click the Sales Rep filter and choose
Smith.
 Or use a Slicer for Sales Rep and click Smith.

 The Pivot will then display Smith’s totals across Items, Regions, Months.
Question 2. What if Analysis
1. Affordability and Goal Seeking (Goal Seek)

 Question: The individual has a retirement goal to accumulate a Future

Value of $200,000 in 20 years. Assuming the initial deposit and interest rate
remain the same, what must their annual payment (PMT) be to reach this
specific target?

 Excel Task: Use Goal Seek to find the required annual payment needed to
make cell B9 equal $200,000 by changing cell B5.

Answer 1. –

Step 1- Build the sheet [In B5, put any starting number (e.g., 4000). In B9, enter a
FV formula that depends on B5: =FV(B2, B3, -B5, -B4)].

Step 2- Find B5 with Goal Seek

Data → What-If Analysis → Goal Seek:

 Set cell: B7

 To value: 200000

 By changing cell: B5
Step 3- Click OK → Excel will set B5 → result will be = 4370.44 .

2. Risk and Return Scenarios (Scenario Manager)

 Question: The projected interest rate of 8% is uncertain. The student needs

to evaluate the outcome under different market conditions. Use the Scenario
Manager to compare the final Future Value for three distinct scenarios:

o "Bull Market" (Optimistic): Interest Rate = 10%; Years = 25; Annual

Payment = $3,000

o "Base Case" (Expected): Use the original data (8% rate; 20 years;
$2,000 payment; $10,000 PV)

o "Bear Market" (Pessimistic): Interest Rate = 4%; Years = 20; Annual

Payment = $1,500

 Excel Task: Create a Scenario Summary report showing the projected

Future Value for each of the three scenarios in a single table, allowing for
easy comparison.
 Answer 2. -

Step 1: Set Up Your Calculation Sheet

Step 2: Open Scenario Manager

 Go to Data tab

 In the Forecast group → click What-If Analysis

 Select Scenario Manager

Step 3: Add the 3 Scenarios

Scenario 1 — Bull Market

 Click Add

 Scenario name: Bull Market

 Changing cells: B3, B4, B5

 Enter values: B3 = 3000, B4 = 10%, B5 = 25

Click OK
Scenario 2 — Base Case

 Click Add

 Name: Base Case

 Values: B3 = 2000, B4 = 8%, B5 = 20

Click OK

Scenario 3 — Bear Market

 Click Add

 Name: Bear Market

 Values: B3 = 1500, B4 = 4%, B5 = 20

Click OK
Step 4: Generate the Scenario Summary

1. In Scenario Manager, click Summary

2. Select: Scenario Summary

3. Result cell = B6 (Future Value)

Click OK

3. Sensitivity to Key Variables (Data Tables)

 Question: How sensitive is the final Future Value to variations in the interest
rate and the number of years invested?

 Excel Task: Create a Two-Variable Data Table that shows the

calculated Future Value across a range of:

o Years (in rows): 10, 15, 20, 25, 30

o Annual Interest Rates (in columns): 4%, 5%, 6%, 7%, 8%, 9%, 10%
  Analysis: Analyse the completed table to identify the combinations of
variables that have the most significant impact on wealth accumulation over
time.

Answer 3. –

Step 1: Create the Table Layout

Step 2: Link the Output Cell

Step 3: Apply Data Table

Select the entire block: D5: K10

Now apply the Data Table:

1. Go to Data → What-If Analysis → Data Table

2. Input: Row Input Cell = B2 (Interest Rate), Column Input Cell = B3 (Years)

3. Click OK
Sensitivity (Interpretation):

1. Impact of Years (Rows):

 FV increases dramatically as years increase, especially after 20+ years.

 Compounding has an exponential effect → longer periods = biggest growth

driver.

2. Impact of interest rate (Columns):

 Small increases in interest rate (e.g., 7% → 8%) create large jumps in FV.

 The effect becomes stronger at longer durations.

3. Highest Sensitivity Region:

The most sensitive FV values occur where:

 Years are high (25–30)

 Interest rates are high (8–10%)

These combinations produce the largest future values.

4. Lowest Sensitivity Region:

 Small years (10–15)

 Low interest (4–6%)

Here, FV grows slowly and compounding is less significant.

Question 3. Correlation, Regression, and Scatter Plot

Enter the following data into an Excel spreadsheet, with "Hours Studied" in Column
A and "Exam Score" in Column B:

Hours Studied (X) Exam Score (Y)

2 60
3 75
4 70
5 85
6 80
7 90
8 95

1. Calculate the correlation coefficient (r): Use the CORREL function in Excel
(e.g., =CORREL(A2:A8, B2:B8)) to find the correlation coefficient between
Hours Studied and Exam Score.

Answer 1.
Use Excel: =CORREL(A2:A8, B2:B8)

2. Interpret the correlation:

o What is the value of the correlation coefficient?
o Does this value indicate a positive, negative, or no correlation?
o Describe the strength of the relationship (weak, moderate, strong).
o Does this correlation imply that studying causes higher scores? Explain
your reasoning.

Answer 2.
a) The correlation coefficient (r) is:
r = 0.9283
b) Because the value is greater than 0, it indicates a positive correlation
This means that as the Number of Hours Studied increases, the Exam Score also
increases.
c) Interpretation scale:
 0.0 – 0.3 → Weak
 0.3 – 0.6 → Moderate
 0.6 – 0.8 → Strong
 0.8 – 1.0 → Very strong
Since r = 0.9283, it is:
A very strong positive relationship
This means the two variables move together closely.
d)No. Correlation does NOT prove causation.
Even though hours studied and exam scores move together strongly, it cannot be
concluded that studying causes higher scores because:
 Other factors may affect exam scores (difficulty, learning ability, teaching
quality, motivation).
 Correlation only measures how closely two variables move together—not why.
Therefore:
 Correlation ≠ Causation
 It only shows that students who study more tend to score higher in this
dataset.

3. Run a linear regression analysis: Use the Data Analysis ToolPak in Excel
to perform a regression analysis with "Exam Score" as the dependent variable
(Y) and "Hours Studied" as the independent variable (X).

o Note: If you don't see the "Data Analysis" button under the "Data" tab,
you will need to enable the Analysis ToolPak add-in in Excel Options.

Answer 3. –
Step 1- Go to Data → Data Analysis
Select Regression
Click OK
Step 2- Set the Input Ranges
Dependent Variable (Y Range): Select the exam scores: B1:B8
Independent Variable (X Range): Select hours studied: A1:A8
Check the box Labels (since you included headers).

Step 3- Choose Output Location

Select:
  Output Range (e.g., E1)
OR
 New Worksheet Ply
Then click OK.

4. Identify the regression equation: From the regression output summary,

locate the Intercept and the X Variable 1 Coefficient (slope) to write the linear
regression equation in the form Y = Intercept + Slope * X.

Answer 4. -
After performing the linear regression in Excel (using the Data Analysis ToolPak), we
obtain the following coefficients from the regression output:
 Intercept = 53.392857
 Slope (X Variable 1 Coefficient) = 5.178571
Using these values, the linear regression equation is:
Y= 53.392857 + 5.178571X
Interpretation of the equation:
 When X = Hours Studied, the model predicts the Exam Score (Y).
 This equation shows that for every 1 additional hour studied, the predicted
exam score increases by approximately 5.18 marks.
 5. Interpret the coefficients:

o What does the intercept value mean in the context of this data?
o What does the slope value mean? (i.e., for every additional hour
studied, how much does the exam score change?)

Answer 5. -
a) Interpretation of the Intercept
The intercept value from the regression output is: Intercept = 53.392857
Meaning:
In the context of this data, the intercept represents the predicted exam score when a
student studies 0 hours.
Interpretation:
If a student studies 0 hours, the model predicts that they would score approximately
53.39 marks.
Note: In real life, this value may not be perfectly realistic (because nobody takes an
exam without preparation), but statistically it helps form the regression line.
b) Interpretation of the Slope
The slope value from the regression output is: Slope = 5.178571
Meaning:
The slope shows how much the exam score changes for every additional hour
studied.
Interpretation:
 For every 1 extra hour studied, the exam score is predicted to increase by
approximately 5.18 marks.
 This means the relationship between hours studied and exam performance is
strongly positive: more study time → higher expected score.

6. Evaluate the model fit (R-squared):

o What is the R-squared value of the output

o Interpret this value. How much of the variation in exam scores is
explained by the hours studied?]

Answer 6. -
 a) From the regression output, the R-squared value is: R² = 0.8617
(approximately 86.17%)
b) Interpretation of the R-squared value

 R-squared tells us how well the independent variable (Hours Studied)

explains the variation in the dependent variable (Exam Score).
 Interpretation: An R² of 0.8617 means that approximately:
86.17% of the variation in exam scores is explained by the number of hours
studied:
 This indicates a strong model fit, meaning the regression line represents the
data very well.
 Only about 13.83% of the variation is due to other factors such as:
o student ability

o difficulty of the exam

o study quality

o motivation, etc.

7. Make a prediction: Use your regression equation to predict the exam score
for a student who studied for 4.5 hours.

Answer 7. - Prediction (for 4.5 hours)

Use the regression equation:

Y^ =53.392857 +5.178571× 4.5

Predicted score = 53.392857 + 23.30357 = 76.69643 = 76.70

So a student studying 4.5 hours is predicted to score = 76.7.

8. Create a scatter plot with a trendline:

o Generate a scatter plot of the data (Insert > Scatter Chart).

o Add a linear trendline to the plot.
o Display the equation and the R-squared value on the chart.
o Visually confirm that the equation on the chart matches the one you
found in the regression output.

Answer 8. -
Step 1- Select two columns (Hours and Score).
Step 2- Insert → Scatter Chart (Markers only).

Step 3- Click a data point → Add Trendline → Linear.

Step 4- In Trendline options: Check Display Equation on chart and Display R-

squared value on chart.
Step 5- Confirm that the printed equation matches the regression output (slope and
intercept shown on chart). The R² shown should match the R-squared from
Regression.