Build a 3-Statement Financial Model [Free Course]

Показать описание

We'll go step-by-step to create a 3-Statement Model that links the income statement, balance sheet and cash flow statement.

Timestamps
00:00 Course Introduction and Model Structure
03:53 Before We Begin: The Premium Package
05:56 Historical Income Statement
12:22 Projected Income Statement
21:06 Historical Balance Sheet Assets
26:10 Projected Balance Sheet Assets
29:36 PP&E Roll-Forward Schedule (Depreciation, Capital Expenditures)
39:28 Historical and Projected Balance Sheet Liabilities
47:05 Historical and Projected Balance Sheet Equity
1:00:20 Cash Flow Statement (Excluding Financing)
1:10:25 Cash Flow from Financing (Debt Schedule)
1:18:02 Revolver and Interest ("Finishing Touches")

Рекомендации по теме

Комментарии

This is priceless ! A simple guide to building a 3 statement! Well explained and easy to build. Thanks!!

CUbiQ-pgvf

Finally ! a simple guide to building a 3 statement! thank you guys

GamalFJ

it is a fruitful experience to watch and learn from your presentation. well-put and easy to understand. thanks for the great presentation.

yudaaritonang

Thank you so much! I love everything that you guys do. It's simply the best platform out there.

fbvwvlu

8:20
11:50
23:40 alt+w+s
28:15 assumption for prepaid expenses
31:50 the comp will disclose what their dep was you have to read it in their footnote
32:05 simple assumption for depreciation
56:05 Other Comprehensive Income - BS

닝닝닝

How about a review for a bank stock financial forecasting model?

mburujames

Thank you for the video.I understand that this video really explain how to build financial model from scratch.I mean this is what really happened in the real life

rizalukman

Incredible content! Thank you so much for sharing

qikong

56:00 treasury stock is a contra accoutn
Ppe : beg +capex -depreciation
Intangibles: beg + purchase of intangibles - amortization
Use retained earnings as a plug for assets = liabilities + equity
57:29 common stock and apic, treasury stock, retained earnings “schedules” - dividends from cash flow statement? 58:58
Use revenue growth as projection for most BS line items except inventory
Below current liabilities - hold constant

CFS: 1:04:11 start with net income then depreciation and amortization then copy and paste items from Bs starting with current assets and liabilities (make sure you cross off each one).
Assets:
Previous - current
Liabilities
Current - prior

Equity items 1:14:53
Link directly to schedules for certain items eg issuances of common stock instead of common stock and apic and dividends instead of retained earnings
Remember revolver

You get total change in cash. Add this to previous cash in balance sheet to fill in cash forecast blank 1:19:58 here we just hold revolver constant
It should balance at this stage
1:21:52 activate revolver in case cash shortfall
1:23:38 debt schedule. Previous cash balance + all of cash items except revolver = total cash flow before any revolver borrowing

Revolver forecast = previous revolver balance + total revolver borrowing needed

1:27:00 -min formula
1:28:43 last step - interest. Interest rate x previous cash. Interest rate x prev revolver & long term debt. Make sure interest expense is negative on I/S

dbsk

wow where have you been all my life😀. I been trying to learn this own my own and give up due to learning curve! thank you!!

JerryR

00:02 📊 A three-statement financial model is crucial for corporate finance, investment banking, and private equity roles. It helps understand the interaction between financial statements and requires both accounting and modeling skills.
00:45 🏛 This course simplifies building a financial statement model's architecture, making it applicable to various companies. It emphasizes learning from scratch and provides step-by-step instructions.
02:08 💹 Income statements typically include revenue, cost of goods sold, operating expenses, operating profit, interest income, interest expense, pre-tax profit, tax expense, and net income.
07:30 📊 Use consistent formatting for expenses as negatives to maintain simplicity and clarity in financial modeling.
12:23 📈 Project future revenue growth rates based on historical trends, and calculate gross profit margins to estimate cost of goods sold. Maintain uniform formatting for calculations.
15:18 💼 Project operating expenses based on historical percentages of revenue and ensure consistent formatting for expenses.
16:58 🏦 Interest income and interest expense projections depend on future debt and cash balances, impacting the balance sheet and cash flow statement.
19:08 🧾 Tax expense calculations should be based on historical tax rates as a percentage of pre-tax profit. Formatting and consistency are key.
21:16 📝 Building a three-statement model involves interconnecting the income statement, balance sheet, and cash flow statement to create a comprehensive financial analysis.
21:31 📊 The financial model will have fewer than 600 rows to keep it manageable.
21:59 💰 Balance sheet begins with cash, accounts receivable, and inventory.
22:54 🏢 Long-term assets include property, plant, equipment, and intangible assets.
23:22 📈 Total assets calculation combines current and non-current assets.
25:19 📈 Intangible assets and property plant equipment forecasted based on revenue and expenses.
26:18 💼 Working capital liabilities include accounts payable, accrued expenses, and other current liabilities.
27:45 💳 Forecasting working capital liabilities often tied to revenue growth.
29:28 🏗 Property plant equipment (PPE) forecasted using capital expenditures (CAPEX) and depreciation.
36:22 💡 Intangible assets forecasted similarly to PPE, tied to revenue and expenses.
43:22 🏦 Long-term debt and revolver are important liabilities to consider in the model.
44:19 📊 Understanding the structure of a balance sheet and its importance.
45:30 🤝 Relationship between different liabilities and equity in a balance sheet.
46:48 💡 Importance of maintaining balance and connectivity among various financial model components.
47:46 📈 Explanation of key equity line items: common stock, additional paid-in capital, treasury stock, other comprehensive income, and retained earnings.
52:12 📊 Constructing a roll-forward model for equity line items, such as common stock and additional paid-in capital.
54:49 📉 Understanding treasury stock as a negative value representing repurchased shares.
56:02 📊 Forecasting retained earnings: accumulation of net income minus dividends.
57:28 💰 Impact of dividends on retained earnings and cash flow statement.
59:08 📉 Importance and complexity of constructing a cash flow statement to reconcile net income with cash flows.
01:00:36 🔗 Utilizing "elevator drops" for quick navigation in complex financial models.
01:02:15 🌐 Exploring the reconciliation process in a cash flow statement: starting from net income, adding back depreciation and amortization, and considering changes in working capital.
01:04:19 ⚖ Reversing the impact of depreciation and amortization to convert net income into cash flow from operations.
01:06:01 📊 Incorporating changes in working capital to accurately represent cash flows from operations.
01:06:30 🧮 Understanding year-over-year change in working capital and its impact on cash flow.
01:07:24 📊 Concept of assets (e.g., inventories, prepaid expenses) going up as outflows and liabilities as sources of funds.
01:08:32 🌐 The total cash flow from operations reflects how much cash is generated and used in operations.
01:09:15 🛠 Building the cash flow statement for investing activities, including capital expenditures and intangible asset purchases.
01:09:56 💰 Analyzing cash flow, reinvestment, and dividend distribution to shareholders.
01:10:23 📈 Connecting cash flow statement, balance sheet, and financial model dynamics.
01:16:10 🔄 Dynamic cash management to prevent negative cash balance using revolver borrowing.
01:26:08 🔀 Using a "min" function to handle cash surplus and deficit situations for revolving credit line.
01:28:00 🔄 Building a dynamic revolver borrowing mechanism is crucial to prevent negative cash balances and ensure cash availability.
01:28:28 🧩 The core architecture of a three-statement financial model is in place, providing a framework for further customization.
01:28:55 🏗 The model's structure allows for additional details, such as interest income and expense, to be incorporated.
01:29:23 💰 Assumptions about interest rates for cash and debt are added to the model for more realistic financial projections.
01:30:25 🔄 Interest income is calculated based on the beginning cash balance, avoiding circular reference errors.
01:30:51 🔄 Interest expense is calculated similarly, considering two debt tranches and avoiding circularity.
01:31:50 🎉 The three-statement model is complete, providing a powerful tool for financial analysis and scenario testing.

casinosbroker

Great video, and happy about all the formatting on the go, as I learned a lot from it as well. Thank you!

TheLyutov

Thank you so much for very clear explanation and sample

hbtsg

Icredible content thank you so much for sharing

isaacneto

Your video is much much more importnat to me. Keep up the good works. Just want to ask you why we drive our inventory growth from cost of goods sold percentage growth as they are inversely proportional. When we sell more inventoy we would have less on hand. Just subscribed!!😸

godismyway