Calculation of Free Cash Flow (FCF)

You are a Financial Analyst at Orion Tech Solutions, a growing software and hardware company. Management wants to know whether the business is generating enough actual cash to fund a possible acquisition next year.

Looking only at Net Income is not enough. A company may show a profit but still have less cash because it has to buy equipment, maintain inventory, and wait for customers to pay.

Therefore, you will calculate Unlevered Free Cash Flow (FCFF). This shows how much cash the business generates from its operations after paying taxes, investing in equipment, and funding working capital.

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

Calculation of Free Cash Flow (FCF)

Open a new Excel or Google Sheets workbook. We will start by setting up the raw data and calculating the core profitability and working capital metrics.

Add the Title and Tax Rate

• In A1, type: Orion Tech Solutions - FCF Model (Make it Bold).

• In A3, type: Corporate Tax Rate.

• In B3, enter: 25%.

Why do we need the tax rate?

We need the tax rate to calculate how much tax the company would pay on its operating profit.

Here, the tax rate is 25%.

1

Enter Income Statement Data 

2

Starting from Row 5, enter the following:

Here is the data formatted as a table for your Excel model:

Understand the following Concepts:

Revenue:
Money earned by the company from selling its products or services.

COGS:
The direct cost of producing or purchasing those products.

Operating Expenses:
Expenses required to run the business, such as salaries, rent, technology costs, etc.

Depreciation:
An accounting expense that spreads the cost of an asset over its useful life. It reduces profit but does not represent a current cash payment.

CapEx = Capital Expenditure

It means money a company spends to buy, build, or improve long-term assets that will be used for more than one year.

Examples of CapEx

For a company like Orion Tech Solutions:

• Buying computers and servers

• Buying machinery

• Purchasing or improving an office/building

• Buying vehicles

• Upgrading production equipment

Enter Working Capital Data

3

Leave Row 11 blank. Starting from Row 12, enter:

What do these items mean?

Accounts Receivable:
Money customers owe the company.

Example: You sell ₹50,000 worth of products today, but the customer will pay next month. The ₹50,000 becomes Accounts Receivable.

Inventory:
Products or materials currently held by the company.

Accounts Payable:
Money the company owes its suppliers.

Example: A supplier gives you equipment today but allows you to pay after 30 days. The amount you still owe is Accounts Payable.

Money the company owes its suppliers.

Example: A supplier gives you equipment today but allows you to pay after 30 days. The amount you still owe is Accounts Payable.

Calculate EBIT (Operating Profit)

4

Now we calculate how much profit the business generates from its operations. Leave Row 16 blank.

• In A17, type: 1. PROFITABILITY CALCULATIONS (Make it Bold).

• In A18, type: Operating Income (EBIT).

• In B18, enter: =B7-B8-B9-B10. (Result: ₹3,50,000)

• In C18, enter: =C7-C8-C9-C10. (Result: ₹4,30,000)

  • Formula Note: EBIT = Revenue − COGS − Operating Expenses − Depreciation.

Why do we use this formula?

We start with Revenue and subtract all operating costs:

Revenue − COGS − Operating Expenses − Depreciation = EBIT

For Year 2:

₹12,00,000 − ₹4,80,000 − ₹2,40,000 − ₹50,000 = ₹4,30,000

Why is EBIT important?

EBIT tells us how profitable the core business operations are before considering how the business is financed.

Calculate Taxes on EBIT

5

• In A19, type: Less: Taxes on EBIT.

• In B19, enter: =B18*$B3. (Result: ₹87,500)

• In C19, enter: =C18*$B$3. (Result: ₹1,07,500)

  • Excel Note: The $B$3 locks the tax rate cell so it doesn't shift when copied.

Why do we calculate tax on EBIT?

Unlevered Free Cash Flow (FCFF) measures the cash generated by the business operations, without considering loans or interest payments.

That's why we start with EBIT (profit before interest and tax).

EBIT → Tax on EBIT → NOPAT

For example:

EBIT ₹4,30,000 × 25% tax = ₹1,07,500 tax

NOPAT = ₹4,30,000 − ₹1,07,500 = ₹3,22,500

We calculate tax on EBIT to find the after-tax operating profit, without letting the company's debt or interest affect the calculation.

Calculate NOPAT (Net Operating Profit After Tax)

6

• In A20, type: NOPAT.

• In B20, enter: =B18-B19. (Result: ₹2,62,500)

• In C20, enter: =C18-C19. (Result: ₹3,22,500)

  • Concept Note: NOPAT tells us how much operating profit remains after tax, before considering interest or debt financing.

• In A20, type: NOPAT.

• In B20, enter: =B18-B19. (Result: ₹2,62,500)

• In C20, enter: =C18-C19. (Result: ₹3,22,500)

  • Concept Note: NOPAT tells us how much operating profit remains after tax, before considering interest or debt financing.

Calculate Net Working Capital (NWC)

7

Working capital tells us how much money is tied up in the company's day-to-day operations. Leave Row 21 blank.

• In A22, type: 2. NET WORKING CAPITAL (NWC) (Make it Bold).

• Calculate Current Operating Assets:

  • In A23, type: Current Operating Assets.

  • In B23, enter: =B13+B14. (Result: ₹1,80,000)

Why do we add these?

Both Accounts Receivable and Inventory represent money that is currently tied up in the business.

• In C23, enter: =C13+C14. (Result: ₹2,50,000)

​

• Calculate Current Operating Liabilities:

• Calculate Current Operating Liabilities:

  • In A24, type: Current Operating Liabilities.

  • In B24, enter: =B15. (Result: ₹60,000)

  • In C24, enter: =C15. (Result: ₹75,000)

Why does Accounts Payable reduce NWC?

Accounts Payable is money the company owes suppliers but has not paid yet.

So the company is temporarily holding onto that cash.

• Calculate NWC:

  • In A25, type: Net Working Capital (NWC).

  • In B25, enter: =B23-B24. (Result: ₹1,20,000)

  • In C25, enter: =C23-C24. (Result: ₹1,75,000)

Why is NWC important?

Think of NWC as cash tied up in running the business.

For example:

• Customers owe you money → cash is tied up.

• You have inventory sitting in your warehouse → cash is tied up.

• Suppliers have given you time to pay → this reduces the amount of cash tied up.

Calculate Change in NWC

Now compare Year 2 with Year 1.

Year 1 NWC:

₹1,20,000

Year 2 NWC:

₹1,75,000

Therefore:

Change in NWC = ₹1,75,000 − ₹1,20,000 = ₹55,000

What does the ₹55,000 mean?

It means the company has ₹55,000 more cash tied up in working capital than it had in Year 1.

Therefore, this ₹55,000 is treated as a cash outflow.

Increase in NWC → Cash decreases

Decrease in NWC → Cash increases

This is one of the most important concepts in the lab.

Now we bring everything together to calculate the final Free Cash Flow for Year 2. Leave Row 26 blank.

Link NOPAT

1

• In A27, type: 3. FREE CASH FLOW DERIVATION (Make it Bold). In C27, type: Year 2.

• In A28, type: NOPAT.

• In C28, enter: =C20. (Result: ₹3,22,500)

Why do we start with NOPAT?

NOPAT represents the after-tax operating profit generated by the business.

So it is the starting point for calculating the cash generated by operations.

Add Back Depreciation

2

• In A29, type: Add: Depreciation.

• In C29, enter: =C10. (Result: ₹50,000)

Why do we add depreciation?

Depreciation reduced EBIT and therefore reduced NOPAT.

But depreciation is a non-cash expense.

The company did not actually pay ₹50,000 in cash this year just because depreciation was recorded.

Therefore, we add it back.

Subtract CapEx

3

• In A30, type: Less: CapEx.

• In C30, enter: =-C11. (Result: -₹1,20,000)

Why do we subtract CapEx?

Unlike depreciation, CapEx involves actual cash leaving the company.

The company spent ₹1,20,000 on equipment and other long-term assets.

Therefore, we must subtract it.

Subtract Change in NWC

4

• In A31, type: Less: Change in NWC.

• In C31, enter: =-(C25-B25). (Result: -₹55,000)

Why is there a minus sign?

NWC increased from:

₹1,20,000 → ₹1,75,000

The increase is:

₹55,000

That means an additional ₹55,000 of cash became tied up in the business.

Therefore, it reduces Free Cash Flow.

The formula:

=-(C25-B25)

means:

− (Year 2 NWC − Year 1 NWC)

or:

− (₹1,75,000 − ₹1,20,000) = −₹55,000

Calculate Final Free Cash Flow

5

• In A32, type: UNLEVERED FREE CASH FLOW.

• In C32, enter: =SUM(C28:C31). (Result: ₹1,97,500)

​

What does ₹1,97,500 mean?

It means that after:

• Paying operating taxes

• Adding back non-cash depreciation

• Buying necessary equipment

• Funding additional working capital

the company generated ₹1,97,500 of cash that is free from the core business operations.

This is the cash management could potentially use for things such as an acquisition, debt repayment, dividends, or other investments.

Dynamic Scenario Testing

Now let's see how a simple business decision can improve Free Cash Flow.

Scenario

The Supply Chain Manager negotiates better payment terms with suppliers.

The suppliers agree to allow Orion Tech Solutions to pay later.

Change Accounts Payable

1

Go to:

Cell C15

Current value:

₹75,000

Change it to:

₹95,000

Do not change anything else.

Excel will automatically recalculate the formulas

Check the New NWC

2

Current operating assets remain:

₹2,50,000

But Accounts Payable increases to:

₹95,000

Therefore:

NWC = ₹2,50,000 − ₹95,000

NWC = ₹1,55,000

Previously, NWC was:

₹1,75,000

So NWC has decreased by:

₹20,000

Check the Change in NWC

3

Previously:

Change in NWC = ₹55,000

Now:

₹1,55,000 − ₹1,20,000 = ₹35,000

Therefore, the cash tied up in working capital has fallen from ₹55,000 to ₹35,000.

Check the New Free Cash Flow

4

The new calculation becomes:

What changed?

Free Cash Flow increased from:

₹1,97,500 → ₹2,17,500

Increase:

₹20,000

Why?

The company did not sell more products.

It did not increase its profit.

It simply negotiated better payment terms with suppliers.

Because the company can pay suppliers later, more cash remains in the company's bank account today.

What changed?

Free Cash Flow increased from:

₹1,97,500 → ₹2,17,500

Increase:

₹20,000

Why?

The company did not sell more products.

It did not increase its profit.

It simply negotiated better payment terms with suppliers.

Because the company can pay suppliers later, more cash remains in the company's bank account today.