For all the files and resources, go to: mergersandinquisitions.com/3-statement-model/ The Table of Contents is in the video description (click "more" under the date), but I'll paste it here as well: 0:00 Introduction 2:35 What is a 3-Statement Modeling Test? 5:54 Part 1: Inputting the Historical Financial Statements 15:31 Balance Sheet Entry 24:14 Cash Flow Statement Entry 35:11 Part 2: Income Statement Projections 50:12 Part 3: Balance Sheet Projections 57:51 Part 4: Cash Flow Statement Projections 1:07:12 Part 5: Linking the Statements 1:10:59 Part 6: Debt and Stock Repurchases 1:19:16 Part 7: Model Checks, Review, and Final Comments 1:22:35 Recap and Summary
As a Student of Management Accounting and interested into Finance, I love how you use REAL FINANCIAL DATA from companies. Most don't do these in their tutorials. Please, keep doing more of this real case studies. It helps me down in the long run. You just earned a new subscriber. Much Love.
HOLYYYY this is fking beautiful. Dont listen to any negativity, this video is top quality. PLEASE do MORE for different type of companies: service based, tech etc. -> or build on top of this model in the situation you have multiple days to do research. How do you go out to build out your own assumptions? To improve the model? Thanks for the video❤
Thanks. We have many different examples of 3-statement models in the full courses (breakingintowallstreet.com/breaking-into-wall-street-courses/). There may be another example in this channel eventually, but we are now aiming for more concise videos, so I don't know the exact form it will take.
This is a real-life case study, completed under actual time pressure, to simulate what an actual exercise would be like in an interview. This setup means that there will inevitably be mistakes and oversights that we have to return to and fix at the end. You can, of course, find perfectly scripted tutorials and lessons that get everything right from the start - but the goal here was to complete something with mistakes along the way and see what we could return to and fix by the end. If you can get even 50% correct in a real-life case study, you are doing pretty well...
@@financialmodeling thank you for your answer, I understandt the purpose of the mistakes anyways now I have learn to be careful with the dates before putting the data thanks again !
No. You should consolidate and simplify aggressively, as we did here. Aim for 5 items on each side of the Balance Sheet; 10 at most. Focus on the big picture, not tiny details.
You could, but we prefer to link it to revenue or to CapEx so that there's more of a link between the company's growth and its D&A. But in a simple enough model, it's all about the same anyway.
We consolidated the various line items within Shareholders' Equity so that there's just a single line item to make the modeling/linking/checking process easier.
Totally makes sense, thanks! Appreciate the timely response. I have a technical coming up to create a three statement model w/ debt schedule, amortization schedule, working capital schedule, etc. on a public company.... any ideas on how to how to get good practice for this? @@financialmodeling
I don't know what you mean by all hard-coded numbers, so would need to see a screenshot or video clip of your file to suggest a fix. But for something like this, you should probably just follow along from scratch with your own version of the file anyway and compare it to the finished one at the end.
I watched tons of fake company Three statement models, but nothing compare to the real companies case study, . Many don't dare to do this, but you did and it was very great. Just subscribed!!
It was replaced years ago, sorry. We have many examples of 3-statement models from templates vs. blank sheets in the courses and occasionally post free samples here.
Preparing for a 3-statement exam, this is super helpful! Only thing I'm not sure I understood is why do we also subtract Pensions in addition to Acquisitions from Other Assets when linking it to the CFS?
Each line item on the CFS must be reflected once and only once on the BS. We don't have a separate BS account for Pension Assets and do not know where else to link it, so we choose to link it to "Other Assets." We need to link it somewhere because the Balance Sheet will not balance otherwise.
That’s great Brian, but you have to admit you knew about this Case/material beforehand. You can’t possibly spend 15 seconds, ONLY, and then go to page 42/56 (like by “accident”) and just yeh be able to workout and use the growth rate… that’s the issue, in reality it takes much longer. It’s all well and good when you are already aware of where to look for it…. (What to look for should be knowledge part assumptions based of course, that’s correct ;) )
Thanks, but I'll push back on you here because that's not quite accurate. The version you see here is the "second run" of this model, but the pre-existing knowledge did not make much a difference in terms of finishing within the time limit. With the first version I did off-screen, I actually gave myself 90 minutes to build the model and finished in that amount of time, including finding the information and growth rates. It is not difficult to quickly flip through a presentation or search for the key terms once you know how to do it (in almost any industry). The difference is that this RU-vid version takes *more time* to complete the same tasks because I am stopping to explain things along the way. If you remove the explanation times, intro, outro, etc., this is actually closer to a 60-minute tutorial. I agree that you're probably not going to finish this entire case in 60 minutes if you don't know something going into it, but it is 100% plausible to finish it in 90 minutes if you are proficient in Excel and do not have to stop to explain things along the way. Maybe it won't be quite as good as the version here, but you would still be able to finish and pass the case study under a 90-minute limit.
You make a favor for people that want to break into Investment Banking. I'm the president of the Financial Club of Mackenzie Presbyterian University, one of the Brazilian Ivy League universities, and I'm using your video do prepare the students to break into Investment Banking. Thank you so much! Hugs from Brazil.
"And then we want to add 'Net Transfers to UTC' whatever that is" Man I can't tell you how much of a relief it is to hear there are actually things you [also] don't know - obscure individual company financial statements or otherwise 😝
All financial statements have unique/strange/one-time items, so no one can possibly know everything. But in a time-pressured case study, you just have to decide what's important / worth looking up vs. what's not and move on quickly rather than dwelling on something.
I'm curious about the I/S Drivers. The case states to "use something more than a simple percentage growth rate", I'm assuming that means we can't use the guidance on page 17 from the Investor Presentation PDF where it mentions the Medium Term Outlook? In that case, I'm curious how you would figure out other ways to come up with a back-ended calculation that arrives at a similar growth rate in another case study? I know here we took data from the New Equipment Market Size to extrapolate Product Revenue, and Service Units to extrapolate Service Revenue. I'm assuming we would just have to look for similar data points that you can derive revenue from?
Correct, you cannot just use a simple revenue estimate or percentage growth rate from the investor presentation. If you did not have the market size and share data, you would have to look for Units Sold or Total Customers or something else that you could link to Revenue and make a projection from there. But usually if there's an instruction like that, the presentation or other materials have market size or units sold estimates.
I have an Interview tommorow, I need to prepare FInancial Modeling, I have watched this video twice. Lets see how it goes, I will come back and let you know If I get selected, But thank you so much for the Detail explanation, tips and tricks
Hello. I am new to financial modeling and enjoyed this tutorial so much. Can you build a playlist for financial modeling for renewable energy project? It would be really helpful. TIA
57:25 when I put this into my formula bar, I get 383 for FY 22, but for some reason I get (124) for FY 23, (124) for FY 24, (119) for FY 25 and (112) for FY 26. I have redone the same part over and over again, checked the numbers which I have the exact same as yours but this keeps happening. Any advice? Edit: nevermind I found out I had an anchor on another part that was messing with the numbers for this part.
The change in equity on the balance sheet does not equal the net income, and at that point I froze. For example FY20 change in equity is 217m and the net income is 906m. I can see it in the changes in equity statement now I have time to look through, but it threw me when I did the test. Did anyone else see that and what do you think is the best way to deal with it? I think you have to ignore the fact there's a bunch of stuff going through Equity that we don't have the time to understand and account for, ignore the fact there's no reconciliation between the BS & P&L and plough on. Which is what M&I did. Excellent video. Well done chief. (I've been accounting for 25 years.)
Thanks. The point of this exercise is that when you only have 90 minutes to finish, you need to make decisions quickly, make rough estimates, and not overthink things. So, yes, there are potential issues on these statements, and we could spend a lot of time trying to fix them... or we could ignore them, move on, and finish the meaningful parts, i.e., the company's cash flow and ability to issue dividends, repurchase stock/debt, and make acquisitions. In real life, the Balance Sheet and Cash Flow Statement never match up exactly if you look at any public company's financial statements because accounts are grouped slightly different internally vs. the public presentation... so it's not worth worrying about.
My questions about this... It appears when I model it similarly, that Capex is increasing at 1%/year while D&A is increasing at 2% isn't this an issue? because say if revenue is 100 per year, D&A = -2% per year, and capex is 1% per year wouldn't fixed assets decline over time?
This is an issue that you should think about it in a more complex model or in a situation where you have more time, such as days or weeks, and you have to make much longer-term forecasts. In a 90-minute speed test where you have to finish and produce output as quickly as possible, it's not worth thinking about as long as the D&A and CapEx numbers are reasonable in the forecast period (i.e., CapEx stays ahead of D&A if the company as growing).
Could I please understand more on why we are projecting contract liabilities using expenses instead of maybe revenue since contract liabilities is basically revenue that was received early? Not really sure the relation to expenses besides the fact that you would incur expenses to perform the work required to fulfill the contractual obligations? :O
In a time-pressured case study, you have to get used to making decisions quickly and not looking into the details of every single line item as long as the end result is similar. Yes, maybe contract liabilities should trend with revenue, but if we took the time to look into it and then changed the assumption, would it affect the output at all? Would we get a higher score on this case study? The answer is no - it's a small detail that no one would pay attention to. It would matter only if this specific assumption made a huge impact on the company's cash flows or flipped them from positive to negative (for example).
It doesn't. Any time a new lease is signed or changed, the ROU Asset and Liability both change by about the same amount, offsetting each other. Only cash lease payments affect Net Income and cash flow - actually signing/cancelling/changing leases should all be offsetting changes.
When consolidating balance sheet liabilities, how would you handle something like earnout liabilities? I would imagine this is something you have on its own because it wouldn't grow over time as a % of anything. I have also seen liabilities like accrued claims which I would assume would be ok to group with other liabilities. Second question would be where are these grouped on the consolidated CFS?
In a quick/simple model, just keep Earnouts constant or consolidate them with "Other Liabilities" or something similar. Earnout Liabilities do actually change over time based on the likelihood of payout and the eventual payout, but it's too complicated to factor into a 60 or 90-minute exercise. You can just link them to an "Other" or "Other Changes" line item on the CFS.
question: why is the sum of cost of services, cost of products and op expenses labeled as Operating Income? Shouldn't it be just Total COGS (op expenses not included). Thank you in advance
It's not the sum of Cost of Services, Cost of Products, and Operating Expenses. Look at the formula again. It's Revenue minus all of those, but since each one has a negative sign, we write it as a + in the formula since adding a negative is the same as subtracting a positive.
sorry lol i see that you already corrected it at 13:11 time stamp. I actually stopped mid pre-tax income computation and was able to see the continuation just now. Thanks for the response 🙏🏼
I wouldn't say it's "common," but for lateral interviews (i.e., being hired from another bank or a Big 4 or other professional services firm), yes, modeling tests and case studies come up. They are less common in entry-level interviews.
On the Assets side of the Balance Sheet, when taking an existing item and linking it to something on the CFS, you always subtract the CFS line item because on the CFS, negatives represent cash outflows and, therefore, asset increases, while positives represent cash inflows and, therefore, asset reductions.
sorry if I have missed the explanation somewhere. the working change is -32. You would think that would added to NI as decline in WC means you got more cash. Would appreciate the clarification.
If you're referring to the FY 19 number, the -32 there represents *the cash flow impact of the Change in Working Capital,* which has the opposite sign as the Change in Working Capital itself. If Working Capital, a net asset, goes up, cash flow goes down, so this Change in WC is shown as a negative on the CFS. If Working Capital goes down, cash flow goes up, so the opposite happens, and it's a positive on the CFS.
@@financialmodeling if I may ask, I noticed You didnt project Other Assets in WC, Why?, I did it, and now the balance is off, how can I make the balance sheet checks?
@@7-72-73 We follow what the company does in its own calculations for Working Capital on the statements. We can answer questions in this free channel but do not offer model reviews or fixes at this time. Happy to look at your model if you have signed up for a course or coaching service on the site.
Why are the net working capital figures positive rather than negative? Even just looking at CA and CL for each year the CL increase more than CA, can someone please explain. When I do the formula on the excel sheet I also get negative figures for NWC
If you look at row 108 of the "Complete" file, you will see all the calculations. Current Assets do not increase by more than Current Liabilities. Your formula in F108 should be: =E78-F78+E79-F79+E81-F81+SUM(F86:F88)-SUM(E86:E88)+SUM(F90:F91)-SUM(E90:E91) On the Assets side, take the old number and subtract the new number; on the L&E side, do the opposite. In FY 22, Current Assets increases by $625, and Current Liabilities increases by $1,008.
Is this because it is being reflected on the cash flow statement? And if WC liabilities are increasing more than assets the total cash would be increasing? I'm looking at the formula and it looks like an inverse of total WC of the company
@@johndean1990 As stated many times previously in this channel, yes, when Working Capital increases, it's a cash outflow on the CFS and vice versa. The definitions online and on Investopedia, etc., are wrong because they don't present the Change in WC from a cash flow perspective.
Hi thanks for the great video. Would you say that we should aggressively simplify and consolidate like this while making models generally? If you really focus on building an accurate model it could take 2-3 days.
Yes. More complexity does not make the model better. In fact, more complexity often means less accuracy because it's more difficult to update the model as new results and assumptions are factored in. It's rarely worth the effort to spend days or weeks on a very complex version of a model unless you specifically have to do it by client request.
@@financialmodeling In that case, would you say it is most effective to spend most time on the key assumptions? e.g. units, revenue/unit, opEx, capEx, etc? for the remaining ones, unless something idiosyncratic makes the line item important, just find the most efficient way to assume and project?
