Building an Integrated Three-Statement Financial Model

You are working as a Financial Analyst at Nexus Dynamics Ltd.

Management wants to know what the company's financial position could look like next year. They have given you the current year's financial information and some assumptions for the next year.

Your job is to build a Three-Statement Financial Model in Excel.

You will build and connect:

1. Income Statement – shows revenue, expenses, and profit.

2. Cash Flow Statement – shows how cash moves in and out of the business.

3. Balance Sheet – shows the company's assets, liabilities, and equity.

The most important part of this lab is to link the three statements together using Excel formulas.

At the end, your Balance Sheet should balance, meaning:

Total Assets − Total Liabilities & Equity = 0

The most important part of this lab is to link the three statements together using Excel formulas.

At the end, your Balance Sheet should balance, meaning:

Total Assets − Total Liabilities & Equity = 0

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

Lab File : Building of Three-Statement Financial Model 

A. Lets first Set Up the Assumptions

Create the Model Title

Open a blank Excel workbook.

In A1, type:

Nexus Dynamics - Financial Model

Create the Assumptions Table

Enter the following:

These are the assumptions that will drive your Year 1 forecast.

B: Enter the Year 0 Balance Sheet

       Starting in Row 13, create the Balance Sheet.

Calculate Year 0 Total Assets

In B18, enter:

=SUM(B14:B17)

You should get:

₹5,90,000

Calculate Year 0 Total Liabilities & Equity

In B23, enter:

=SUM(B19:B22)

You should also get:

₹5,90,000

Calculate the Balance Check

In B24, enter:

=B18-B23

 Expected result:

0

This means:

Assets = Liabilities + Equity

Your Year 0 Balance Sheet is balanced.

C: Build the Income Statement

       Starting in Row 26, create the Income Statement.

Starting in Row 26, create the Year 1 Income Statement.

 Calculate Year 0 (Column B)

1) Enter Year 0 Revenue: In cell B27, verify or enter 500000.

2) Calculate COGS: In cell B28, enter =B27*$B$3. (Result: ₹2,00,000)

3) Calculate Gross Profit: In cell B29, enter =B27-B28. (Result: ₹3,00,000)

4) Calculate Operating Expenses: In cell B30, enter =B27*$B$4. (Result: ₹1,00,000)

5) Calculate Depreciation: In cell B31, enter =$B$8. (Result: ₹50,000)

6) Calculate Operating Income (EBIT): In cell B32, enter =B29-B30-B31. (Result: ₹1,50,000)

7) Calculate Interest Expense: In cell B33, enter =$B$20*$B$6. (Result: ₹20,000. Note: This links Long-Term Debt from row 20 to the Interest Rate in B6).

8) Calculate Profit Before Tax (EBT): In cell B34, enter =B32-B33. (Result: ₹1,30,000)

9) Calculate Tax: In cell B35, enter =B34*$B$5. (Result: ₹32,500)

10) Calculate Net Income: In cell B36, enter =B34-B35. (Result: ₹97,500)

Forecast Year 1 (Column C)

1) Forecast Year 1 Revenue: In cell C27, enter =B27*(1+$B$2). (Result: ₹5,50,000)

2) Calculate COGS: In cell C28, enter =C27*$B$3. (Result: ₹2,20,000)

3) Calculate Gross Profit: In cell C29, enter =C27-C28. (Result: ₹3,30,000)

4) Calculate Operating Expenses: In cell C30, enter =C27*$B$4. (Result: ₹1,10,000)

5) Calculate Depreciation: In cell C31, enter =$B$8. (Result: ₹50,000)

6) Calculate Operating Income (EBIT): In cell C32, enter =C29-C30-C31. (Result: ₹1,70,000)

7) Calculate Interest Expense: In cell C33, enter =$B$20*$B$6. (Result: ₹20,000)

8) Calculate Profit Before Tax (EBT): In cell C34, enter =C32-C33. (Result: ₹1,50,000)

9) Calculate Tax: In cell C35, enter =C34*$B$5. (Result: ₹37,500)

10) Calculate Net Income: In cell C36, enter =C34-C35. (Result: ₹1,12,500)

D: Build the Cash Flow Statement

       Starting in Row 38, create the Cash Flow Statement using the Indirect Method.

E : Calculate Year 1 Cash Flow (Column C)​

• Link Net Income (Starting Point):

  • In cell C39, enter =C36. (Result: ₹1,12,500)

  • Why: We start with the bottom-line profit from the Income Statement as our base cash generator.

• Add Depreciation (ADDED):

  • In cell C40, enter =C31. (Result: ₹50,000)

  • Why it is added (+): Depreciation was subtracted earlier to calculate Net Income, but it is a "non-cash" expense (no actual money left the bank). We add it back to find our true cash.

• Calculate Change in Accounts Rec. (SUBTRACTED):

  • In cell C41, enter =B15-$B$9. (Result: -₹5,000)

  • Why it is subtracted (-): Accounts Receivable increased from ₹50,000 to ₹55,000. An increase in an asset means customers owe you more money that you haven't collected yet. Because you don't have that cash in hand, it is a negative adjustment.

• Link Net Income (Starting Point):

  • In cell C39, enter =C36. (Result: ₹1,12,500)

  • Why: We start with the bottom-line profit from the Income Statement as our base cash generator.

• Add Depreciation (ADDED):

  • In cell C40, enter =C31. (Result: ₹50,000)

  • Why it is added (+): Depreciation was subtracted earlier to calculate Net Income, but it is a "non-cash" expense (no actual money left the bank). We add it back to find our true cash.

• Calculate Change in Accounts Rec. (SUBTRACTED):

  • In cell C41, enter =B15-$B$9. (Result: -₹5,000)

  • Why it is subtracted (-): Accounts Receivable increased from ₹50,000 to ₹55,000. An increase in an asset means customers owe you more money that you haven't collected yet. Because you don't have that cash in hand, it is a negative adjustment.

• Calculate Change in Inventory (SUBTRACTED):

  • In cell C42, enter =B16-$B$10. (Result: -₹4,000)

  • Why it is subtracted (-): Inventory increased from ₹40,000 to ₹44,000. An increase in an asset means you spent money to buy more goods. Buying goods uses cash, so it is a negative adjustment.

• Calculate Change in Accounts Pay. (ADDED):

  • In cell C43, enter =$B$11-B19. (Result: ₹3,000)

  • Why it is added (+): Accounts Payable increased from ₹30,000 to ₹33,000. An increase in a liability means you delayed paying your bills. Holding onto that money preserves your cash, so it is a positive adjustment.

• Calculate Cash from Operations (CFO):

  • In cell C44, enter =SUM(C39:C43). (Result: ₹1,56,500)

  • Why: This sums the Net Income and all the working capital adjustments (+ and -) to show exactly how much cash the core business generated.

• Calculate Capital Expenditures (SUBTRACTED):

  • In cell C45, enter =-$B$7. (Result: -₹80,000)

  • Why it is subtracted (-): Capital Expenditures (CapEx) represent buying physical assets like property or equipment. Purchasing assets is a major cash outflow, so it must be negative.

• Calculate Cash from Financing (CFF):

  • In cell C46, enter 0. (Result: ₹0)

  • Why: The company did not issue new stock or take on new debt in this scenario, so there is zero cash movement here.

• Calculate Net Change in Cash:

  • In cell C47, enter =SUM(C44:C46). (Result: ₹76,500)

  • Why: This adds the totals from Operations, Investing (CapEx), and Financing to show the final net amount of cash added to the company's bank account this year.

Why is there no Year 0 in the Cash Flow Statement?

In financial modeling, you will notice that the Balance Sheet and Income Statement have a "Year 0" column, but the Cash Flow Statement only starts at "Year 1". Here is the accounting rule behind this:

• The Balance Sheet is a "Snapshot": Year 0 on the Balance Sheet just shows us the exact balances in the bank on the very last day of that year.

• The Cash Flow Statement is a "Video": It measures the movement or change in cash over a full 12-month period.

To calculate the cash movement for Year 0, a financial analyst would need to see the Balance Sheet from Year -1 (the year before). Because we need to calculate the Change in Accounts Receivable, Inventory, and Accounts Payable, we always need a starting point to compare against.

Since management only gave us the ending balances for Year 0, we do not have the historical data to look backwards. Therefore, we can only look forward and calculate the cash flows that occur during Year 1 (which bridges the gap between the Year 0 Balance Sheet and the Year 1 Balance Sheet).

F: Complete the Year 1 Balance Sheet (Column C)

This is the most critical part of the model where all three statements merge.

• Calculate Cash (ADDED):

  • In cell C14, enter =B14+C47. (Result: ₹1,76,500)

  • Why it is added (+): We take last year's ending bank balance (Year 0 Cash) and add the final Net Change in Cash from the bottom of our Cash Flow Statement to get the exact cash we have today.

• Update Accounts Receivable (LINKED):

  • In cell C15, enter =$B$9. (Result: ₹55,000)

  • Why: We are linking directly to management's forecast assumption. This sets the new total for money customers owe us.

• Update Inventory (LINKED):

  • In cell C16, enter =$B$10. (Result: ₹44,000)

  • Why: We link directly to the forecast assumption to set the new value of unsold goods sitting in the warehouse.

• Calculate Net PPE (ADDED & SUBTRACTED):

  • In cell C17, enter =B17+$B$7-C31. (Result: ₹4,30,000)

  • Why it is added (+): We start with last year's property and equipment, then add any new equipment we bought this year (Capital Expenditures).

  • Why it is subtracted (-): We must subtract the Depreciation (from the Income Statement), which represents the value those physical assets lost over the year due to wear and tear.

• Calculate Total Assets (SUM):

  • In cell C18, enter =SUM(C14:C17). (Result: ₹7,05,500)

  • Why: This adds up everything of value the company currently owns.

• Update Accounts Payable (LINKED):

  • In cell C19, enter =$B$11. (Result: ₹33,000)

  • Why: Linked to the forecast assumption. This is the new total of unpaid bills we owe to our suppliers.

• Carry Forward Common Stock (EQUAL):

  • In cell C21, enter =B21. (Result: ₹2,60,000)

• Update Retained Earnings (ADDED):

  • In cell C22, enter =B22+C36. (Result: ₹2,12,500)

  • Why it is added (+): Retained Earnings acts like the company's long-term savings account. We take last year's savings and add the new Net Income profit we generated this year (from the very bottom of the Income Statement).

• Calculate Total Liab & Equity (SUM):

  • In cell C23, enter =SUM(C19:C22). (Result: ₹7,05,500)

  • Why: This adds up everything the company owes to others (Liabilities) plus the total value belonging to the owners (Equity).

• Create the Balance Check (SUBTRACTED):

  • In cell C24, enter =C18-C23.

  • Why it is subtracted (-): The universal rule of accounting is Total Assets = Total Liabilities + Equity. By subtracting the liabilities and equity from the assets, we ensure the difference is exactly 0.

If C24 = 0, your three-statement model is successfully integrated!

Here is the rewritten section for Tasks 6, 7, and 8, updated with the precise cell numbers that match your verified Excel model.

Think of the financial model like a connected chain. Complete these concepts for your final understanding:

• Income Statement → Balance Sheet: Net Income (from cell C36) directly increases Retained Earnings on the Balance Sheet (in cell C22).

• Income Statement → Cash Flow Statement & Balance Sheet: Depreciation (from cell C31) is added back in the Cash Flow Statement (cell C40) because it does not use actual cash. It is also subtracted from the Net PPE on the Balance Sheet (cell C17) to show the loss of asset value.

• Cash Flow Statement → Balance Sheet: The Net Change in Cash (from cell C47) is added to the beginning cash balance to update the ending Cash balance on the Balance Sheet (cell C14).

Test Whether Your Model Is Dynamic

Now test whether your formulas are correctly linked.

1) Go to cell B2 (Revenue Growth Rate).

2) Change the value from 10% to 25%.

3) at your model. You should see automatic changes in the following cells:

• Revenue (C27)

• Net Income (C36)

• Net Change in Cash (C47)

• Ending Cash (C14)

• Retained Earnings (C22)

• Total Assets (C18)

4) But the most important thing is: Your Balance Check (C24) should still exactly equal 0.

5) (Change cell B2 back to 10% after testing).

Troubleshooting Challenge

Suppose you accidentally make a mistake and forget to add Net Income to Retained

Earnings on your Balance Sheet.

Instead of typing the correct formula: =B22+C36

You accidentally just carry forward the old balance: =B22

What happens?

• Your Year 1 Net Income is ₹1,12,500 (in cell C36).

• Because you forgot to add this profit to Retained Earnings, your Total Equity is understated by exactly ₹1,12,500.

• Therefore, your Balance Check (cell C24) will show ₹1,12,500. The Balance Sheet will not balance because your Assets are now higher than your Liabilities & Equity by that exact amount.

Lesson: The Balance Check (Assets minus Liabilities & Equity) is your best friend. It instantly helps you find missing links and mistakes in your financial model!