We don’t do this type of modeling at our valuation firm but I decided to take it upon myself to learn on my own (seems like it’s something important to know)
I read every walk through and downloaded every free template I can find and check nearly every single cell. Some of these are beast of models, 500+ rows, with tons of schedules. Still haven’t built my first model yet. Tons of time spent but I need some feedback.
How is this learned in practice? Do people start with a base template their firm uses? How much time is given to an analyst to construct one of these, an average one? I think I will have enough knowledge and confidence to build one soon (I will be using Macabacus’s step by step model for guidence) but if anything I said raises red flags or ya’ll have any advice, I’d love to hear it.
Sign up for Breaking Into Wall Street. Best bang for the buck. Other good ones are Wall Street Prep and Training the Street. I’ve used them all and BIWS is the best value.
The job offer I’m waiting on says they will pay up to a certain amount a year for education expenses and if that’s covered, I’m in. If not, I’m probably still in. I’ve watched every single video they have on youtube. They have a Dell LBO model on there that is awesome.
The Excel and Fundamentals (i.e. basic BIWS product) is $247. Hopefully your job offer comes through and they cover the price, but otherwise, that seems to be a nominal price to pay to develop a skill that’s basically table stakes for getting a corporate finance job these days.
Important thing is to learn by doing. Get a self study guide to help you, but become content making your own modifications and adjustements. Modeling is an art, not a hard mathematical science.
If I can strip all of my personal information out of a recent model I did for a job interivew, I’ll send it to you. It’s a 3 statement model of a public company flowing into both relative and intrinic valutions. Despite pumping 30 hours in 2 days to whip that model up, I didn’t get the job. I’m a little bitter, because the guy who got the job I doubt had to go through the grinder like I did. It’s what I’ve seen happen when hacksaw MBAs go against Top 2 MBAs.
What I didn’t have time to do was build in sensitivity and scenario analysis which is a nice touch to future estimates.
Forecast Revenue based on industry, competitors, management guidance, analyst estimates (separate tab). Forecast CAPEX on this same tab. I like to forecast CAPEX as a % of revenue.
Link forecast to BS, IS, CF. Increase AR, Invent, AP by % increase in sales on BS, stick with common size on the others. PPE will follow the CAPEX forecast on the rev tab.
Stick with an common size COGS %, SGA %, and so forth on the IS going foreard. Improvements in margins is an aggresive forecast in my opinion. Don’t bother trying to forecast other income other expenses or extrodinary items. This gets to be GIGO in a hurry.
Find the WACC or a highwatermark cost of capital. If all else fails, look for the marginal cost of capital (ie if they had to issue more debt, what would the interest rate be?)
Intrinics/DCF valuation
Relative comparable valuation
Summary weighting with valuations coming to a happy medium. Scenario and sensitivity analysis (different permutations of growth rates, discount rates, for terminal value and the resulting valuation)
There you have it, the short and sweet. There is a lot of tinkering along the way to make it all work, but I tend to follow that process.
Great summary, CvM. That’s helpful to many, I’m sure.
Out of curiosity, how much tinkering do you do with balance sheet items? Do you always take a deep dive into whether the assets are being over/undervalued, or do you either trust those numbers, farm out that analysis, give a standard discount to those numbers, increase the discount rate to reflect that uncertainty? I can’t imagine how I’d know whether specialized factory machines cost $100 million or $150 million, for example.
^ honestly I don’t give a fk. A balance sheet is a statement in time and I’m confident there is plenty of window dressing in there. In my current role, if we are doing a net asset value of a business, we’ll steeply discount inventory to 20 cents on the dollar with PPE at 50 cents on the dollar. Those looking to sell us their business don’t like what this value comes out to be, but remember, an investment is only worth what someone will pay for it. The amounts mentioned above is what we typically get if we need to use a 3rd party liquidator to literally sell off assets.
If we are not doing a net asset value, we literally don’t care. We are looking for future cash flows to get an idea of what sort of pro forma earnings we can get if we assume the business.
The balance sheet is a historical statement of position, not a historical time period like the income statement.
Thanks for the breakdown CvM. I think i’ll also buy the BIWS pack after the exam. We do a little bit of modelling here but not much, a lot of it is just adjusting templates.
I’ll send out my model to anyone interested. It’s about a year dated, but it would certainly be a good template that is used in the real world. Drop your email below, or send it to me in the message, and I’ll send the model your way.
Give me a day to strip out my personal info first.
Since I was already in the industry what I basically did was…
Download a sell side model in a couple of industries. Previously looked at these a lot.
Go line by line to see how things link. It really helps if you actually uh… know how financial statements work in the first place. Don’t look at any of the ones that are driven by macros as they obviously don’t help.
Create own model and reference sell side models in that industry. There may be some cases in which what they do doesn’t make sense to you which means you’re either doing it wrong (most of the time if you’re learning) or you have potential alpha. A lot of the time I find embedded assumptions which make sense after talking with the company’s IR team.
Repeat above… a lot. Each of these takes a ton of time to do (as CvM indicated, probably at least 30 hours if you’re doing it correctly). I did two 3 statement models interviewing for my current job.
I think it’s a good exercise and something that you really have to know. However, on the buy side, expectations for how model intensive you should be vary by firm. I’ve never been asked to project out the full balance sheet for my two jobs. Actually knowing how the company works / risks / qualitative factors seem to matter a lot more.