Sensitivity Analysis Using Data Tables

You are working as a Financial Analyst at Orion Tech Solutions. Management wants to understand how changes in selling price and sales volume growth can affect the company's Year 3 Gross Profit.

Instead of changing the assumptions one by one, you will use an Excel Two-Way Data Table. This allows you to test different combinations of Price and Growth and see the resulting Gross Profit quickly.

Topic : Financial Modelling (Excel-based)

1) Financial statement structure

2) Three-statement model overview

3) Assumptions and drivers

4) Free cash flow calculation

5) Sensitivity and scenario analysis

Previous Lab File required for this lab : Development of Financial Assumptions & Drivers

Lab File for Sensitivity Analysis : Sensitivity Analysis using Data Tables

In this task, you will create a Two-Way Data Table to test different combinations of Price and Volume Growth.

Create the Data Table

Go to the Assumptions sheet.

In the empty area below your existing tables, enter the following:

Your table should look like this:

Here:

• Prices are placed across the top: ₹110, ₹125, ₹140.

• Growth rates are placed down the left side: 10%, 15%, 20%.

• The formula in B13 connects the table to Year 3 Gross Profit.

Select the Complete Table

2

Highlight the entire range:

B13:E16

Make sure your selection includes:

• The Gross Profit formula

• All three prices

• All three growth rates

• The empty cells in the middle

Create the Two-Way Data Table

3

On the Excel ribbon:

1. Click Data.

2. Select What-If Analysis.

3. Click Data Table.

A Data Table window will appear.

Enter the Input Cells

4

In the Row input cell box:

• Click D6 on the Assumptions sheet.

• This is the Price assumption.

In the Column input cell box:

• Click D5 on the Assumptions sheet.

• This is the Growth assumption.

Click OK.

Excel will automatically calculate the Gross Profit for all nine combinations.

Expected Data Table

Your completed table should show approximately:

Now use the completed Data Table to understand how Price and Growth affect Year 3 Gross Profit.

Identify the Worst-Case Scenario

1

Find the lowest Gross Profit in the table.

Find the highest Gross Profit in the table.

• Price: ₹140

• Growth: 20%

• Gross Profit: ₹11,45,760

This is the best-case scenario among the assumptions tested.

• Price: ₹110

• Growth: 10%

• Gross Profit: ₹8,25,220

This is the worst-case scenario among the assumptions tested.

Identify the Best-Case Scenario

2

Compare the Impact of Price and Growth

3

Compare the changes in Gross Profit when Price and Growth change.

The analysis shows that increasing the selling price produces a larger change in Gross Profit than the same type of increase in sales volume growth within the scenarios tested.

Compare the changes in Gross Profit when Price and Growth change.

The analysis shows that increasing the selling price produces a larger change in Gross Profit than the same type of increase in sales volume growth within the scenarios tested.

Business Recommendation

4

Based on the sensitivity analysis, management should give strong attention to price optimization.

A higher selling price can have a significant positive effect on Gross Profit. However, management should also consider customer demand and competition before increasing prices.

Key Takeaway

Sensitivity analysis helps management understand how changes in important assumptions can affect financial results. A Two-Way Data Table makes it easy to compare several scenarios at the same time instead of changing assumptions manually