How to Create and Use 3-Way Financial Models

Introduction

For small business owners, maintaining financial stability and driving growth are top priorities. A 3-way financial model is an essential tool that integrates the income statement, balance sheet, and cash flow statement, providing a comprehensive view of your business's financial health. This article guides you through the creation and utilisation of 3-way financial models to enhance your financial planning and decision-making.

What is a 3-Way Financial Model?

A 3-way financial model combines three critical financial statements: the income statement, the balance sheet, and the cash flow statement. This integration allows you to see how changes in one area affect the others, providing a holistic view of your business's financial status.

Unlike a standalone profit forecast, a 3-way model ensures that your balance sheet always balances and your cash position is always visible - meaning you can see the cash impact of every revenue and expense assumption in real time.

Steps to Create a 3-Way Financial Model

1. Income Statement

Start with the income statement, which details your revenue, expenses, and profits over a specific period. Key components include:

Revenue: Total income from sales and other sources.
Cost of Goods Sold (COGS): Direct costs of producing goods or services.
Gross Profit: Revenue minus COGS.
Operating Expenses: Indirect costs such as rent, utilities, and salaries.
Net Profit: Gross profit minus operating expenses and taxes.

2. Balance Sheet

Next, prepare the balance sheet, which shows your business's financial position at a specific point in time. It includes:

Assets: What your business owns (e.g., cash, inventory, equipment).
Liabilities: What your business owes (e.g., loans, accounts payable).
Equity: Owner's investment plus retained earnings.

3. Cash Flow Statement

Finally, create the cash flow statement, which tracks the flow of cash in and out of your business. It includes:

Operating Activities: Cash generated or used in business operations.
Investing Activities: Cash spent on or received from investments (e.g., purchasing equipment).
Financing Activities: Cash received from or paid to investors and creditors (e.g., loan repayments).

Integrating the Three Statements

Once you have these three statements, integrate them to reflect how changes in one statement affect the others. The key linkages are:

Net profit from the income statement flows into retained earnings on the balance sheet and is the starting point for the cash flow statement (operating activities)
Changes in working capital (debtors, creditors, inventory) on the balance sheet drive the operating cash flow adjustments
Capital expenditure and loan movements appear in both the cash flow statement and the balance sheet
Closing cash from the cash flow statement becomes opening cash for the next period's balance sheet

In Excel, set up a circular reference for interest on debt and cash balances (a small iterative calculation is normal for 3-way models). Enable iterative calculation in Excel: File → Options → Formulas → Enable iterative calculation with max iterations of 100.

Worked Example: A 3-Way Model for a Growing Retail Business

Consider a retail business planning its next 12 months. The business has:

$500K in existing cash
$200K in trade debtors and $150K in trade creditors
A $300K equipment purchase planned in month 3 (funded by a $200K loan and $100K from cash)
Monthly revenue of $100K, growing at 2% per month
Operating expenses of $75K per month, with $10K of that being depreciation (non-cash)

Running the 3-way model reveals:

Month 1-2: Cash position is healthy - operating cash flow of ~$35K/month covers all needs
Month 3: The equipment purchase drops cash from $570K to $470K (after the $100K cash contribution plus the $200K loan draw)
Month 4-6: New loan repayments ($4.2K/month) reduce monthly net cash flow, but the equipment enables a capacity increase that lifts revenue by 5% from month 5 onward
Month 12: Cash position is $615K - the investment has paid for itself and generated a net cash surplus

Note: The above figures are illustrative. Actual results depend on specific revenue growth rates, cost structures, and financing terms.

Using 3-Way Financial Models

1. Financial Planning

Use the model to create detailed financial plans. Project future income, expenses, and cash flows to determine your business's financial trajectory. Adjust your plans based on different scenarios to prepare for various outcomes.

2. Performance Monitoring

Regularly compare actual financial performance against your model's projections. Identify variances and understand their causes to refine your forecasts and improve accuracy.

3. Decision-Making

Leverage the model to make informed business decisions. Whether you're considering a new investment, expanding operations, or cutting costs, the model provides insights into the financial implications of your choices.

4. Risk Management

Identify potential financial risks by simulating different scenarios. Understand the impact of adverse conditions (e.g., a sales drop) on your cash flow and overall financial health. Develop strategies to mitigate these risks.

Common Mistakes in 3-Way Models

Forgetting to link retained earnings: Net profit must flow to the balance sheet equity section, or the model won't balance
Double-counting depreciation: Depreciation is already included in operating expenses on the P&L - don't subtract it again in cash flow without adding it back (D&A add-back)
Ignoring working capital timing: A sale today doesn't mean cash today. If your payment terms are 30 days, model the delay between revenue recognition and cash receipt
Circular reference errors: Interest on debt and cash requires iterative calculation. If you haven't enabled it, the model won't converge to a correct result

Q & A

What is the primary benefit of a 3-way financial model?

The primary benefit is its ability to provide a comprehensive view of your business's financial health by integrating the income statement, balance sheet, and cash flow statement. This helps in making informed financial decisions.

How often should a 3-way financial model be updated?

It should be updated regularly, ideally monthly or quarterly, to reflect the latest financial data and business performance. Regular updates ensure that the model remains accurate and useful for planning and decision-making.

Can a 3-way financial model help in securing financing?

Yes, a well-prepared 3-way financial model can demonstrate your business's financial stability and growth potential to lenders and investors, making it easier to secure financing.

How does scenario planning work in a 3-way financial model?

Scenario planning involves creating different financial projections based on various assumptions (e.g., best-case, worst-case scenarios). This helps you understand the potential impact of different conditions on your business's financial health.

What tools can be used to create 3-way financial models?

Financial modeling software like Excel, Google Sheets, and specialised financial planning tools can be used to create 3-way financial models.

How do I handle seasonality in a 3-way model?

Build monthly revenue drivers based on historical seasonal patterns rather than using a flat growth rate. For example, if Q2 traditionally generates 35% of annual revenue, model that explicitly. Your working capital and cash flow will change dramatically by quarter as a result.

Conclusion

By following these guidelines, small business owners can effectively create and use 3-way financial models to enhance their financial stability and drive growth. For more advanced financial modelling techniques and business planning tools, visit ExcelWiz.com.au.