Build a 3-Statement Financial Model [Free Course]
ฝัง
- เผยแพร่เมื่อ 18 มิ.ย. 2024
- Build a 3-Statement Model completely from scratch in 90 minutes. Get the Complete Excel Model for this course at www.wallstreetprep.com/free-c...
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")
Incredible content! Thank you so much for sharing
Great video, and happy about all the formatting on the go, as I learned a lot from it as well. Thank you!
Finally ! a simple guide to building a 3 statement! thank you guys
Thank you so much for very clear explanation and sample
Simply amazing for US CPAs like me-Thank you so much-
Excellent delivery!
Very helpful course!
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!!
A-M-A-Z-I-N-G! Thank you!
thank you soooooooo much
AmAzing amazing tysm!!!!
thank you my dear , good job 😃
Icredible content thank you so much for sharing
THANK YOU NOW I CAN GET IT
SAW IT NOW. CHERS
Marvelous
How about a review for a bank stock financial forecasting model?
brilliant little tutorial - helped a lot.
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
Easy to understand. Thanks man
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?
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
Whats the drag formula shortcut you use? Kind regards!
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?
Is there a free template available?
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
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.
😊
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.
👍
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
share the excel file.
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.
Hop me in
my balance did not balance...
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
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.