Sign up  |  Log in

How did you learn how to do three statement modeling?

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.

We’re gonna win so much, you may even get tired of winning. And you’ll say, 'Please, please. It’s too much winning. We can’t take it anymore. Mr. President, it’s too much.' And I’ll say, 'No, it isn’t!' We have to keep winning!

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.

numi wrote:

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.

We’re gonna win so much, you may even get tired of winning. And you’ll say, 'Please, please. It’s too much winning. We can’t take it anymore. Mr. President, it’s too much.' And I’ll say, 'No, it isn’t!' We have to keep winning!

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.

udemy has cheap cources, but prob is garbage

"You want a quote? Haven’t I written enough already???"

RIP

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.

I tend to follow this process.

1.  Input financial statements for BS, IS, CF

2.  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.

3.  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.

4.  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?)

5.  Intrinics/DCF valuation

6.  Relative comparable valuation

7.  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.

You want a quote?  Haven’t I written enough already???

^ 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.

Thank you CFAvsMBA, going to use that outline while building this thing.

We’re gonna win so much, you may even get tired of winning. And you’ll say, 'Please, please. It’s too much winning. We can’t take it anymore. Mr. President, it’s too much.' And I’ll say, 'No, it isn’t!' We have to keep winning!

I chummed that up in a hurry, so don’t hesitate to ask detailed questions if/when they arise.  Important thing is your learn by doing it. 

cvm a true legend bsd

"You want a quote? Haven’t I written enough already???"

RIP

All

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.

CFAvsMBA wrote:

^ 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. 

This is hilarious, almost considering using this in my sig.

Great input, dude.

Thanks for the offer to send your stuff.

Would be interested in seeing how you do things. Is there a PM function here, or should we just exchange emails ?

- Fran: You know, in Tibet, if they want something, do you know what they do? They give something away.
- Bernard: They do, do they? That must be why they're such a dominant global power.

There is a pm function, or hit me up at cfavsmba at g mail

^respekt

"You want a quote? Haven’t I written enough already???"

RIP

Since I was already in the industry what I basically did was…

1. Download a sell side model in a couple of industries. Previously looked at these a lot.

2. 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.

3. 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. 

4. 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. 

CFAvsMBA wrote:

There is a pm function, or hit me up at cfavsmba at g mail

You ever used a tornado diagram to visualize senstivity?  I’ve been using it lately and not as analytical people seem to grasp it pretty quickly

If you quote my above reply, I think you’ll see a 3-statement cheat sheet.

[/quote]

Respect.  Model sent to those requested.  Let me know what you think.  Remember, I am human, they are not perfect, I’d be interested in your feedback or insight.

^ Thank you again for sharing your knowledge and work.

I like the model a lot, seemed to go into detail on things that really matter, (i.e. global rev growth), awesome stuff.

I’ve spent the last few days on mine and made a stupid mistake, I started from historical BS and IS and built everything out, including the historical cash flow statement, without including many line items. So the ending cash numbers on the historical don’t tie. But all my future IS, BS, CF balance/tie perfectly, so progress is being made!

We’re gonna win so much, you may even get tired of winning. And you’ll say, 'Please, please. It’s too much winning. We can’t take it anymore. Mr. President, it’s too much.' And I’ll say, 'No, it isn’t!' We have to keep winning!

^ Nice work.  I couldn’t get the forecasted balance sheet to tie out so I had to cheat in one of the equity accounts to ensure A= L+E. 

By not following what you have been taught in the CFA curriculum in FRA/Equity- please donot start arguing over how much we have all learnt from the CFA curriculum.

Its a pity when a L3 candidate comes to me and i have first make him/her go thru a painful process of “unlearning” all the ‘useful stuff” he/she has learnt so far and teach how stuff is done in real life..

Sign up for courses on BIWS (as somebody above suggested- you will get as close to the real stuff without working at a shop)..I owe BIWS to whatever i accounting/FS modeling&analysis i know today and apply in my role at one of BB banks where i work..ok may be like 30% to my grad school as well! 

Two questions - I tried self-teaching this to myself before doing the L3 prep and had some modest success but wasn’t sure how to approach these.

1. How much do you worry about making BS/CF/IS balance historically?  WD40 had decent statements, but every other company I looked at would change various metrics retrospectively almost every year/quarter, without comment.  This drove me nuts.

2. Do you input the historical statements exactly as reported, or just pick and choose the important accounts and group stuff to save space?

Oh and one more for the BIWS guys-

3. I’m thinking about doing the O&G module, is it worth doing their financial modeling course first or would I be fine diving straight into the oil one?  I’m cheap and would rather not buy two programs.

^I dont even bother to enter historical CF statement, or balance historical statements. I’ve never seen this done and don’t see the point of it, unless you’re just trying to practice - but if you’re trying to practice that way, prepare to battle quirky audit reclassifications until you go crazy and give up.

You need to input historical financials exactly in the same format as you would be forecasting, and that is rarely presented in a manner that follows US GAAP or IFRS rules. If you look at the models done by the company’s FP&A team, you will find that they follow completely different formatting from the financial reporting statements - that’s how management runs their business and thinks of their operations, which means it is the best way to forecast. This is of course very industry-specific, but as an example I like to see division EBITDA, corporate overhead, depreciation broken out from COGS and SG&A…. so put yourself in management’s shoes and restate the historical P&L and BS from ‘financial reporting’ to ‘operating basis’ by filling in the blanks from the notes to the filings.

I have to disagree with some of the bros here about balance sheet forecasting - critically important when you have leverage and are assessing the risks of the cash flows. Even if you don’t care about long-term solvency and liquidity metrics for the company (I don’t see why you wouldn’t), putting a quick DCF where you project some CAPEX and working capital requirement as % of revenue most often doesn’t pass the smell test for me. What if the company has large cash interest and debt amortization payments, so their cash flow from operations can’t support the growth levels in your DCF without maxing out their revolver? What if they have a large chunk of maintenance CAPEX that doesn’t vary with sales? Project your balance sheets!!

CFAvsMBA wrote:

There is a pm function, or hit me up at cfavsmba at g mail

I sent you an email.

Regarding this forum:

Is there a way for me to link attachments? I’d love to post an Excel file (my own novice version of a 3 statement model) and see what others think about it.

In corporate finance now, looking to get my CFA and possibly an MBA.

Mobius Strip wrote:

^I dont even bother to enter historical CF statement, or balance historical statements. I’ve never seen this done and don’t see the point of it, unless you’re just trying to practice - but if you’re trying to practice that way, prepare to battle quirky audit reclassifications until you go crazy and give up.

You need to input historical financials exactly in the same format as you would be forecasting, and that is rarely presented in a manner that follows US GAAP or IFRS rules. If you look at the models done by the company’s FP&A team, you will find that they follow completely different formatting from the financial reporting statements - that’s how management runs their business and thinks of their operations, which means it is the best way to forecast. This is of course very industry-specific, but as an example I like to see division EBITDA, corporate overhead, depreciation broken out from COGS and SG&A…. so put yourself in management’s shoes and restate the historical P&L and BS from ‘financial reporting’ to ‘operating basis’ by filling in the blanks from the notes to the filings.

I have to disagree with some of the bros here about balance sheet forecasting - critically important when you have leverage and are assessing the risks of the cash flows. Even if you don’t care about long-term solvency and liquidity metrics for the company (I don’t see why you wouldn’t), putting a quick DCF where you project some CAPEX and working capital requirement as % of revenue most often doesn’t pass the smell test for me. What if the company has large cash interest and debt amortization payments, so their cash flow from operations can’t support the growth levels in your DCF without maxing out their revolver? What if they have a large chunk of maintenance CAPEX that doesn’t vary with sales? Project your balance sheets!!

Respect.

If you are not forecasting CAPEX and NWC as a % of sales, then what metric do you use?  Management guidance is seldom given beyond a year.

Sent it out to those who requested. 

I use receivables, inventory and payables turnover metrics to forecast WC. Very often there are justifiable reasons to adjust them going forward, compared to latest historical data (for example, retailer with seasonal sales coming off a bad quarter may have excess inventory levels and low turnover ratio). COGS can have a significant fixed component depending on the industry and they are the driver for inventory turnover, so unless you force your gross margin to be flat - you will get different answer compared to WC as % of sales.

For CAPEX, I typically try to break out the fixed maintenance portion (again, this tends to be industry-specific) and separate growth CAPEX which might be better tied to sales. But growth CAPEX investment typically precedes increase in sales and the timing difference may be substantial for some businesses, so that’s something to consider when modeling.