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.
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
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
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
The revolver is usually a Current Liab, not a NC Laib But what made me cringe, especially given the fact this is a 3 statement model, is starting 20:20 him just plugging in random numbers for the RE section of equity. Literally one of the most fundamental parts of the 3 statements is how IS connects to BS through Equity and specifically RE is previous years plus NET INCOME of this year for new RE amount. You cant just randomly plug something in. You're missing the entire point of the exercise.
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!!😸
In the BS looking at the assets, COGS means higher inventory as you had to purchase those goods, hence an increase(growth) in COGS translates to higher inventory. Vice versa, a decrease in COGS (all else equal) means less inventory
By the way, the cost of financing ( interest paid minus interest earned) shouldn’t this appear under the section of financing part in cash flow statement ? It should also be added back under the section of operating cash part. And these two parts offset each other, but they should be presented in the work process
Under US GAAP, the interest received and interest paid both fall under cash flow from operations. So this is included in the CFO (already imbedded in the net income). Under IFRS, they can be either (i) operating or (ii) investing (for interest received) / financing (for interest paid). In this case, you would have to account for it accordingly. Gotta read the footnotes on the company's financial statements if it is not a US company and reports its statements under IFRS guidelines.
Where would we find the purchase of intangibles? What happens if its a services business and there isn't much cpaex but it's more so periodic to replace current infrastructure? could net PPE go negative if the company made on of purchase for equipment and depreciated them over five years and five year later the same equipment is in place and there isn't an offsetting spending in capex?
why at the last step for revolver input on the B/S do we need to add last year's revolver balance of $5, since I assume the calculation for Total Revolver Borrowing Needed would be the whole value for Projection Year 1?
Not necessarily, Depreciation is buried within COGS and Operating expenses. It is only shown separately on the footnotes. Some modellers on the other hand like to break down each line iten to components
Sorry for this basic question but what's the use of doing +1 or -1 when doing the forecasting numbers? Like for forecasting COGS, you did +1 t the beginning of the calculations
Just some advice: Get to the point. Don't waste our time showing you formatting and talking in tangents. People are going to lose their attention and patience. And what about the analysis? What does all the numbers mean in the end of the day?
Finally ! a simple guide to building a 3 statement! thank you guys
This is priceless ! A simple guide to building a 3 statement! Well explained and easy to build. Thanks!!
Thank you so much! I love everything that you guys do. It's simply the best platform out there.
it is a fruitful experience to watch and learn from your presentation. well-put and easy to understand. thanks for the great presentation.
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.
😊
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!!
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?
Incredible content! Thank you so much for sharing
ty
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
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
The revolver is usually a Current Liab, not a NC Laib
But what made me cringe, especially given the fact this is a 3 statement model, is starting 20:20 him just plugging in random numbers for the RE section of equity. Literally one of the most fundamental parts of the 3 statements is how IS connects to BS through Equity and specifically RE is previous years plus NET INCOME of this year for new RE amount. You cant just randomly plug something in. You're missing the entire point of the exercise.
@@AJSalkan thanks for the feedback and agree. not sure i learned that much from this. exercise tbh.
Take it easy on your keyboard big dawg
Exactly 😂😂
hahaha nah sometimes (probably more) you're under time crunch so you gotta move quick
Please don't be rude
Great video, and happy about all the formatting on the go, as I learned a lot from it as well. Thank you!
Excellent delivery!
Gold!
Great video. Only thing is that the link to the complete model doesn't work anymore.
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!!😸
In the BS looking at the assets, COGS means higher inventory as you had to purchase those goods, hence an increase(growth) in COGS translates to higher inventory. Vice versa, a decrease in COGS (all else equal) means less inventory
Thank you so much for very clear explanation and sample
Simply amazing for US CPAs like me-Thank you so much-
A-M-A-Z-I-N-G! Thank you!
thank you my dear , good job 😃
Icredible content thank you so much for sharing
can you take the same gm% for future years because you cant have the same gm% every year?
THANK YOU NOW I CAN GET IT
How to practically forecast revenue growth assumptions for income statement using historical data sir
By the way, the cost of financing ( interest paid minus interest earned) shouldn’t this appear under the section of financing part in cash flow statement ? It should also be added back under the section of operating cash part. And these two parts offset each other, but they should be presented in the work process
Under US GAAP, the interest received and interest paid both fall under cash flow from operations. So this is included in the CFO (already imbedded in the net income).
Under IFRS, they can be either (i) operating or (ii) investing (for interest received) / financing (for interest paid).
In this case, you would have to account for it accordingly. Gotta read the footnotes on the company's financial statements if it is not a US company and reports its statements under IFRS guidelines.
thank you soooooooo much
Very helpful course!
SAW IT NOW. CHERS
Where would we find the purchase of intangibles? What happens if its a services business and there isn't much cpaex but it's more so periodic to replace current infrastructure? could net PPE go negative if the company made on of purchase for equipment and depreciated them over five years and five year later the same equipment is in place and there isn't an offsetting spending in capex?
AmAzing amazing tysm!!!!
my balance did not balance...
Whats the drag formula shortcut you use? Kind regards!
Is there a free template available?
why at the last step for revolver input on the B/S do we need to add last year's revolver balance of $5, since I assume the calculation for Total Revolver Borrowing Needed would be the whole value for Projection Year 1?
Easy to understand. Thanks man
Marvelous
brilliant little tutorial - helped a lot.
DONT WE HAVE TO FIND DEPRECIATION ON THE INCOME STATEMENT?
Not necessarily, Depreciation is buried within COGS and Operating expenses. It is only shown separately on the footnotes.
Some modellers on the other hand like to break down each line iten to components
@@theurbanpandit7151 depreciation is not in COGS, it’s an operating expense.
share the excel file.
Sorry for this basic question but what's the use of doing +1 or -1 when doing the forecasting numbers? Like for forecasting COGS, you did +1 t the beginning of the calculations
the -1 is to turn the negative to a positive number, +1 is for adding the growth rate and to then multiply it to find the forecasts
I am also confused, did figure it out why?
ohh nvm i understand now, the reason why he added 1 on cogs % was to get to the gross profit percentage
Hop me in
👍
Great tutorial, but it seems to me you like to show off your excel (shortcut) skills.
Just some advice: Get to the point. Don't waste our time showing you formatting and talking in tangents. People are going to lose their attention and patience. And what about the analysis? What does all the numbers mean in the end of the day?
it was not help full for beginners because sir you are just showing your work not about the help of people of beginner level