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.
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.
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!!
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
"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.
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 UA-cam 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.
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.
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.
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.
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.
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 !
Hi Brian, I have a question about the structure you mentioned. Is this framework applicable to all companies, or is it specific to the one you're currently working with? Also, could there be variations in the three-statement model? I've noticed that some companies have categories that don't align with the 10-K structure you're using. I’d appreciate your insights, thank you so much!
All companies have the financial statements shown here, but some disclose unit or market-level data differently. So this general outline applies, but the specifics, such as the revenue and expense drivers, may differ. It's hard to say what you should do differently without seeing a specific example. And in some industries, such as commercial banks and insurance, you need extra schedules for points like regulatory capital.
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.
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.
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.
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).
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.
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.
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?
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 🙏🏼
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.
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 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.
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.
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.
![Working Capital and the Change in Working Capital in Valuation and Financial Modeling [REVISED]](/img/n.gif)
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.
Thanks for watching!
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.
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.
Thanks! Glad to hear it.
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!!
Thanks for watching!
This one video beats WSP's ENTIRE course. Definitely going to purchase the whole thing from you guys. Great video.
Thanks for watching!
This is pure gold. Any course i have taken can't compare to this information. Thank you for this amazing content
Thanks for watching!
this helped 1000x more than the Wall street prep program. thank you very much!
Thanks for watching!
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
Good luck!
I wish I can subscribe twice! Such a golden nugget! Great job.
Thanks for watching!
Great video as always! Really appreciate the effort!
Thanks for watching!
"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.
Great teaching and video Brian
Thanks for watching!
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 UA-cam 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.
Great content. Really well done. Balances accuracy, speed and function.
Thanks for watching!
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.
@@financialmodeling "Each line item on the CFS must be reflected once and only once on the BS" Gold! Makes sense on the rest then. Thank you!
lol, I've got a case study next week - perfect timing, thanks Brian!
Good luck!
Great Explanation! Thankyou for the notes and tips to model under pressure.
Thanks!
Thank you! This is great! Thinking on your feet, quick decision making, lovet😊
Thanks for watching!
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.
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.
Exceptional containt bro... Exceptional😊
Thanks for watching!
Thank you Brian, this is very helpful!
Thanks for watching!
Thanks Brian! I also liked the old video that builds using a template, is it taken down and any chance you can reupload the video and resources?
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.
@@financialmodeling No problem, appreciate all the resource anyway 🙌
Why on earth waiting the last moment to tell about the issue on balance sheet .Thanks for your answer and explication ! Great video !!
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 !
Thank you so much , very helpful!
Thanks for watching!
Hi Brian, I have a question about the structure you mentioned. Is this framework applicable to all companies, or is it specific to the one you're currently working with? Also, could there be variations in the three-statement model? I've noticed that some companies have categories that don't align with the 10-K structure you're using. I’d appreciate your insights, thank you so much!
All companies have the financial statements shown here, but some disclose unit or market-level data differently. So this general outline applies, but the specifics, such as the revenue and expense drivers, may differ. It's hard to say what you should do differently without seeing a specific example. And in some industries, such as commercial banks and insurance, you need extra schedules for points like regulatory capital.
@@financialmodeling thank you so much
Great Video! Thanks
Thanks for watching!
Thank you Brian. Powerful presentation. I got a question, is every single item in any statement needs to be projected?
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.
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.
Gather here all those that have interview.
Thanks for watching!
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
Thanks. We don't currently cover this topic but hope to add it in the future.
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.
Great Video man, appreciate it! Could I just check how do you quickly peek the cell path? The method I use is Alt + [ but doesnt seem as effective.
Ctrl + [ jumps to precedent cells. Ctrl + Shift + [ highlights both direct and indirect precedents.
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).
Very minor question - is the formatting with a ‘$’ sign on the first line item and total row convention?
Could I also ask if it is possible to tie D&A to assets?
Yes, it's a convention.
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.
How does movement in op leases impact net income to CFO? ROU Asset is just PV of future lease payments
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.
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?
@@muhammadkatif2720 Yes.
Hey, thank you for the video. It was really insightful. I have a question on how I can project the deferred tax liability?
Please see some of the coverage in the channel on deferred taxes and NOLs.
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 🙏🏼
hi, why do u subtract pension when finding other assets in the balance sheet during "linking the 3 statement model" at 1:08:39, thanks
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.
@@financialmodeling thanks for this, I understand now, have a good day!
Why is there no retained earnings line item in the Shareholders' Equity?
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
How to copy the data in the same format from a website to excel?
Try Data --> From Web in Excel. It won't work perfectly, but it usually saves some amount of time if the data is in a grid-like format.
Is it commonly asked in the industry to complete similar case studies in 90 minutes?
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.
The excel files for me have all numbers hardcoded in. How can I fix that?
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.
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.
@@financialmodeling Thank you!!
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.
Can you please send me the solved excel file
Please follow the links in the video description.
@@financialmodelingthe excel file which you solved in this video??
@@ursdipanshu Please follow the link in the pinned comment or video description.
10:10 resume
39:37
👋👋👋🙏🙏🙏
Thanks for watching!
goat shit
Thanks for watching!