Build a Dynamic 3 Statement Financial Model From Scratch

Summary

In this instructional video, Kenji guides viewers through creating a dynamic three-statement financial model linking the balance sheet, income statement, and cash flow statement. He details the process from organizing the income statement with key assumptions to building out the other components and ensuring the model's interconnectivity. The model serves as a foundation for financial analysis and valuation methods like discounted cash flow and M&A modeling. Kenji also offers a free downloadable file and touches on formatting and formula techniques in Excel.

Takeaways

• 📊 The video provides a step-by-step guide to creating a dynamic three-statement financial model that links the balance sheet, income statement, and cash flow statement.
• 🔗 The model begins with organizing and building an income statement based on a set of assumptions, which is essential for financial analysis and serves as a foundation for valuation work like discounted cash flow or M&A models.
• 📈 The script demonstrates how to format and calculate the income statement, including adjusting for items like revenue, discounts, and cost of goods sold (COGS).
• 🏢 It explains the importance of distinguishing between historical and estimated figures in financial statements, particularly when dealing with balance sheets as they represent a snapshot at a specific time.
• 💻 The video script includes Excel shortcuts and functions like `EDATE`, `ALT`, and `CTRL` combinations for efficient data manipulation and formatting within the financial model.
• 🔑 Key financial concepts such as EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization) are introduced, highlighting their significance in profitability measurement and company comparison.
• 🛠️ The script covers the creation of a depreciation schedule, which is crucial for allocating the cost of tangible assets over their useful life, affecting the income statement and balance sheet.
• 🔄 The importance of linking the three financial statements is emphasized, ensuring that changes in one statement are reflected in the others to maintain accuracy and consistency.
• 💡 The video mentions the use of assumptions for accounts receivable, payable, and deferred revenue, which are dynamically calculated as percentages of revenue or COGS, affecting the balance sheet.
• 📉 The script details the process of forecasting future financial figures based on historical data and percentages, which helps in projecting the company's financial health.
• 🌐 The video concludes with the creation of a cash flow statement, which is vital for understanding the company's liquidity and is used to reconcile the balance sheet's cash figure.

Q & A

What is the purpose of creating a dynamic three-statement model?

A dynamic three-statement model is used for regular financial analysis and as the foundation for valuation work such as discounted cash flow, LBO, or M&A models.

What are the three main financial statements that the video focuses on linking together?

The video focuses on linking the balance sheet, the income statement, and the cash flow statement.

What is the first step in building the financial model as described in the video?

The first step is to organize and build the income statement using a set of assumptions.

How does the video suggest formatting the income statement in Excel?

The video suggests using Excel shortcuts like Alt + H6 for indents, Ctrl + B for bold, and Alt + HB for borders to format the income statement.

What is the significance of using 'EDATE' function in the income statement?

The 'EDATE' function is used to automatically calculate the end date for each year, which is useful for a statement that covers a range of periods like a full year.

How are the assumptions for the financial model represented in the Excel file?

Assumptions are represented in blue and are hardcoded, meaning they are typed in directly without being part of a formula.

What is the reason for making certain assumptions a percentage of revenue?

Making certain assumptions a percentage of revenue allows for dynamic calculations that adjust based on the revenue, ensuring the model remains proportional and accurate.

Can you explain the meaning of EBITDA as mentioned in the video?

EBITDA stands for Earnings Before Interest, Taxes, Depreciation, and Amortization. It is a measure of a company's profitability that allows for comparison between companies by omitting the effects of taxes and other non-cash expenses.

What is the role of the 'Fixed Assets Schedule' in the financial model?

The 'Fixed Assets Schedule' helps in calculating depreciation and amortization, which are then linked back to the income statement to account for the cost allocation of assets over their useful life.

How does the video suggest handling the interest expense in the income statement?

The interest expense is handled by linking it to the balance sheet where the interest payment is calculated based on the interest rate and the debt amount.

What is the final step to ensure the accuracy of the three-statement model?

The final step is to create a cash flow statement and then use it to fill in the missing cash balance in the balance sheet. The accuracy is confirmed when the balance check shows all zeros.