Development of Financial Assumptions & Drivers

You are a Financial Analyst at Orion Tech Solutions. The management team wants you to prepare a 3-year financial forecast.

A good financial model should not have numbers hardcoded inside formulas. Instead, important business assumptions such as sales growth, price, cost percentage, and CapEx percentage should be kept separately in an Assumptions Sheet.

This makes the model easy to change.

For example, if management asks:

“What happens if we increase our Year 3 selling price from ₹110 to ₹125?”

You should only change one cell in the Assumptions Sheet. The entire financial model should automatically update.

In this lab, you will create an Assumptions Sheet and use those assumptions to build a dynamic 3-year financial forecast.

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

You are a Financial Analyst at Orion Tech Solutions. The management team wants you to prepare a 3-year financial forecast.

A good financial model should not have numbers hardcoded inside formulas. Instead, important business assumptions such as sales growth, price, cost percentage, and CapEx percentage should be kept separately in an Assumptions Sheet.

This makes the model easy to change.

For example, if management asks:

“What happens if we increase our Year 3 selling price from ₹110 to ₹125?”

You should only change one cell in the Assumptions Sheet. The entire financial model should automatically update.

In this lab, you will create an Assumptions Sheet and use those assumptions to build a dynamic 3-year financial forecast.

Pre-Lab Preparation

Lab File

Development of Financial Assumptions & Drivers

The Assumptions Sheet will act as the control center of your financial model.

Open a new Excel workbook and rename the first sheet:

Assumptions

Set Up the Timeline

In A1, type:

ORION TECH - DRIVERS & ASSUMPTIONS

Make it Bold.

In Row 3, enter:

Enter Revenue Drivers

2

Revenue mainly depends on two things:

• Number of units sold

• Price charged per unit

In A4, type:

REVENUE DRIVERS

Make it Bold.

Now enter the following:

What does this mean?

• Year 1 is the base year, so volume growth is 0%.

• In Year 2, units sold are expected to increase by 10%.

• In Year 3, units sold are expected to increase by 15%.

• The price per unit increases from ₹100 → ₹105 → ₹110

Enter Cost Drivers

3

Costs can be linked to revenue. This allows costs to increase or decrease automatically as revenue changes.

In A8, type:

COST DRIVERS

Make it Bold.

Now enter:

Understand the Assumption

The company expects its COGS to improve from 40% of revenue in Year 1 to 38% in Years 2 and 3.

A lower COGS percentage means the company expects to become more cost-efficient.

Enter CapEx Drivers

4

CapEx means Capital Expenditure. It is money spent on assets such as equipment, machinery, technology, or other long-term assets.

In A11, type:

CAPEX DRIVERS

Make it Bold.

Now enter:

What does this mean?

The company expects to spend:

• 10% of revenue on CapEx in Year 1.

• 8% of revenue in Years 2 and 3.

Now you will create a second worksheet to hold your calculations.

Model

This sheet will contain the actual financial calculations.

Set Up the Model Headers

1

In A1, type:

ORION TECH - 3-YEAR FORECAST

Make it Bold.

In Row 3, enter:

Calculate Units Sold

2

Units Sold is the starting point for calculating revenue.

In A3, type:

Units Sold

What does this formula mean?

Let's break it down:

B3 = Previous year's units sold
Assumptions!C5 = Year 2 volume growth rate

So the formula means:

Take Year 1 Units Sold and increase them by the Year 2 growth rate.

Calculation:

10,000 × (1 + 10%) = 11,000

Therefore, Year 2 Units Sold = 11,000.

Take Year 2 Units Sold and increase them by the Year 3 growth rate.

Calculation:

11,000 × (1 + 15%) = 12,650

Therefore, Year 3 Units Sold = 12,650

Calculate Revenue

3

Revenue is calculated using:

Revenue = Units Sold × Price per Unit

In A4, type:

Revenue

Year 1

In B4, enter:

=B3*Assumptions!B6

Result: ₹10,00,000

Explanation

B3 = Year 1 Units Sold
Assumptions!B6 = Year 1 Price per Unit

Therefore:

10,000 × ₹100 = ₹10,00,000

Year 1 Revenue = ₹10,00,000

Year 2

In C5, enter:

=C3*Assumptions!C6

Result: ₹11,55,000

Calculation:

11,000 × ₹105 = ₹11,55,000

Year 2 Revenue = ₹11,55,000

Year 3

In D5, enter:

=D3*Assumptions!D6

Result: ₹13,91,500

Calculation:

12,650 × ₹110 = ₹13,91,500

Year 3 Revenue = ₹13,91,500

Calculate Cost of Goods Sold (COGS)

4

COGS is calculated as a percentage of revenue.

COGS = Revenue × COGS Margin

In A5, type:

Cost of Goods Sold

Year 1

In B5, enter:

=B4*Assumptions!B9

Result: ₹4,00,000

Calculation:

₹10,00,000 × 40% = ₹4,00,000

Year 2

In C5, enter:

=C4*Assumptions!C9

Result: ₹4,38,900

Calculation:

₹11,55,000 × 38% = ₹4,38,900

Year 3

In D5, enter:

=D4*Assumptions!D9

Result: ₹5,28,770

Calculation:

₹13,91,500 × 38% = ₹5,28,770

Because the COGS percentage is stored in the Assumptions Sheet, changing the assumption will automatically change COGS.

Calculate Gross Profit

5

Gross Profit shows how much money remains after deducting COGS from revenue.

Gross Profit = Revenue − COGS

In A6, type:

Gross Profit

Year 1

In B6, enter:

=B4-B5

Result: ₹6,00,000

Calculation:

₹10,00,000 − ₹4,00,000 = ₹6,00,000

Year 2

In C6, enter:

=C4-C5

Result: ₹7,16,100

Calculation:

₹11,55,000 − ₹4,38,900 = ₹7,16,100

Year 3

In D6, enter:

=D4-D5

Result: ₹8,62,730

Calculation:

₹13,91,500 − ₹5,28,770 = ₹8,62,730

Calculate Capital Expenditures (CapEx)

6

CapEx is calculated as a percentage of revenue.

CapEx = Revenue × CapEx %

In A8, type:

Capital Expenditures (CapEx)

Year 1

In B8, enter:

=B4*Assumptions!B12

Result: ₹1,00,000

Calculation:

₹10,00,000 × 10% = ₹1,00,000

Year 2

In C8, enter:

=C4*Assumptions!C12

Result: ₹92,400

Calculation:

₹11,55,000 × 8% = ₹92,400

Year 3

In D8, enter:

=D4*Assumptions!D12

Result: ₹1,11,320

Calculation:

₹13,91,500 × 8% = ₹1,11,320

As revenue increases, CapEx also changes automatically because it is linked to revenue.

Check Your Model

Before moving to the scenario analysis, check that your Model Sheet shows the following:

If your numbers match, your model is working correctly.

Now let's test why using assumptions is useful.

Suppose inflation increases and the company decides that it must increase its Year 3 price per unit from ₹110 to ₹125.

Scenario :

Go to the Assumptions sheet

Find: D6 = 110

Change it to: D6 = 125

You do not need to change anything in the Model sheet.

1

Observe the Model

2

Go back to the Model sheet.

The Year 3 results will automatically change.

Year 3 Revenue

Old calculation: 12,650 × ₹110 = ₹13,91,500

New calculation: 12,650 × ₹125 = ₹15,81,250

New Year 3 Revenue = ₹15,81,250

Year 3 COGS

COGS remains 38% of revenue:

₹15,81,250 × 38% = ₹6,00,875

Year 3 Gross Profit

₹15,81,250 − ₹6,00,875 = ₹9,80,375

New Year 3 Gross Profit = ₹9,80,375

Year 3 CapEx

₹15,81,250 × 8% = ₹1,26,500

New Year 3 CapEx = ₹1,26,500

Final Model Results

After completing the original assumptions, your Model sheet should show:

Here is the final model result formatted as a table, showing the updated numbers after you successfully changed the Year 3 price assumption to ₹125:

After changing Assumptions from ₹110 to ₹125, only the Year 3 price assumption changes, but the linked Year 3 financial results automatically update.

Key Business Lesson

A good financial model keeps assumptions separate from calculations.

Instead of changing many formulas, management can change one assumption and immediately see its impact on the business.

For example:

Change Price

↓

Revenue changes

↓

COGS changes

↓

Gross Profit changes

↓

CapEx changes