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!
^ 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.
-
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.
-
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-
- 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!!
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.
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.
Model has been sent out! Drop feedback in here so we all can discuss.
Inventory and AP generally follow COGS, not sales. If margins change, then this won’t be as accurate. Not to mention the DSO and DPO can also change depending on company policy and strategy going forward.
CapEx will generally follow depreciation, if you’re going the dirty way of estimating CapEx using a % of rev, instead of a depreciation table. Forecast net PP&E as a percentage of revenues (this will be an assumption that capital turnover is constant), then forecast depreciation, typically as a percentage of net PP&E, and finally calculate capital expenditures by summing the increase in net PP&E plus depreciation. Other than that, you should value CapEx and depreciation seperately, although the difference between both methods is usually insignificant for the effort needed.
SGA usually follows the gross profit margin, or more generally, revenue. COGS is not always a constant common size, it’s better to break down revenue and cogs on a line item basis, like for example a mixture of price and volume for every good or service.
The WACC also has the cost of equity as part of the equation, so finding out the after-tax Kd is not sufficient. Usually, the Ke is the more difficult variable to estimate.
Which industry and company will be easiest for someone learning to model? I’m assuming a manufacturing or retail business will be way easier to learn with, as compared to a conglomerate or a financial services company. Is there an even easier industry to model?
Additionally, a firm with a simple debt structure will be way simpler for a beginner. Any suggestions for THE SIMPLEST public company for a greenhorn to get his hands dirty?
Any suggestions will be verrryyyy appreciated!
My man Warren B said invest in what you know. Pick a company of a product you admire and it will it exponentially easier to churn through the pages of the 10k.
This.
The first company I ever attempted to model wasn’t one I found very interesting and after a few hours I gave up and moved onto something I was more interested in.
My second attempt (and all companies I have analysed thereafter) was successfully finished because it was something I was interested in, understood the company and industry, and was something I really wanted to invest in after doing some analysis.
CFAvsMBA (via Warren B) gives really sound advice here.
I went through Wall Streep Prep a few years ago and am using their model as a template, and I still can’t get my own balance sheets to balance after building two 3 statement models now. Anyone interested in taking a gander at one of my models? I’ve got no one to ask at work for advice on this. email me at my throw away account if interested: xmotox at gmail. thanks.
BUMP.
CvM, I had a look at your model. Still interested in discussing it here ?
Let’s hear it. Hold nothing back.