Some modeling questions
Recently I came inches away from a sweet ER gig but was told the reason I didn't get it was I didn't have DCF/etc experience, so I'm now attempting to build one for my employer. I've got all our statements and whatever, and have been putting the data into a spreadsheet so I can play with it all and figure something out. Couple things that I'm not sure what to do with though...
- First of all, how much of the data presented in statements/annual reports do you typically include in the model? My excel sheet is literally 1000 rows after putting in all the income tax, PPE, intangibles, operating segments, share-based payments, etc etc etc. I'm fairly certain I don't need 90% of what they're presenting in these notes but I put it in anyway, for now.
- I know my employer's reports have basic condensed, non-IFRS income statements, then after the MDA they put in IFRS statements. When that happens do you typically find ways to reconcile them, or just stick with the IFRS? Problem here being that I can find some common links between the two, but then for example operating income is completely different from the non-IFRS compared to IFRS statements. And detail is lacking to really break out what's going on.
- Many of the quarterly statements only have selected notes, meaning I have no detail on say taxes (and many other things) until the annual statement comes out. Is this normal? Do you just make assumptions for the interim periods?
- What about data not adding up? For example I've got each of Q1-4 2012 data put in, plus the 2012 annual report. But, for example, CoS in the annual report is about 2.5MM different from the sum of the individual quarters. CoS is a pretty top level item so hard to fuck that up, plus there's no comments in any MDA or notes about it.
I've got the data in excel, now I'm just trying to figure out what I need/don't need, find errors in the numbers and link data whenever possible, basically get ready to really dig into valuation..
Thanks folks
Input all of it, then only display what you need - for example... if the company is a capital intensive steel manufacturer your going to need to show depreciation, though if your working on a capital light software developer, you can probably hide that row. Hide row in excel is Alt+A+G+G
Always use IFRS or GAAP if it is available, makes the companies easier to compare to others in the industry.
Yes, detail for Qs is lower.. though you should only use the most recent Q as inter-quarter fluctuations are pretty meaningless for valuation, just focus on inputing FY (fiscal year) data for the past 10 years if available.
Sometimes a company will restate from year to year or quarter to quarter. Always go with the most recent data... for example if the most recent 2013 Annual goes back 3 years, use those three years of data 2013, 2012, 2011 then start using the 2010 data from the 2012 Annual and so on...
Make sure to keep the valuation simple and justify your assumptions! Don't shoot yourself in the foot by trying to make a ridiculously complex model. If you get too granular (e.g., trying to project out employee expenses in SG&A) your valuation probably doesn't mean much.
PM me if you have questions - good luck!
Thanks e, that gives me a little clarity on some things. You can probably expect a PM or two in the future. Really wish I asked this before typing in 4 years of quarterly data though, damn.
You can usually find the financials already in excel on the company's website or on a third party site for free.
I don't know how standard this is, but the BB ER model that I've seen is set up like this:
1) QTR SGMT DATA (information about business segments) 2) QUARTERLY FINANCIAL DATA (Margins, growth rates, etc) 2) ANNUAL SGMT DATA (same as #1) 3) ANNUAL FINANCIAL DATA (same as #2) 4) INCOME STATEMENT 5) BALANCE SHEET 6) CASH FLOW 7) DCF MODEL 8) SENSITIVITY
I would include any data from the business segment parts of the 10q/K, as well as all of the quarterly and annual financial statements. Don't hide anything that is reported in these two sections. Use the SGMT data for revenue and expense build-ups.
Try to avoid the WSP / BIWS formatting.
If you want an example, I can't send you the BB model I have, but I can send you one I've done that is formatted a little bit differently.
Because many of the models actually used are formatted in pages similar to what I listed above, and are more professional looking than what BIWS uses. I also haven't seen many that use the green / blue / black text colors either. It probably doesn't matter for a modeling test though. They can always teach you their format. Try to get your hands on a model actually used in the industry so you can see how they are set up.
I would also caveat my post. My firm is buy side deep value and tends to be somewhat minimalist, so for model formatting go with ER3499.
I'm not familiar with this - what's ER3499?
attention to detail son, that is the user name of the person trying to help you.
At et iure praesentium consequatur. Iure dolorem tenetur quibusdam aspernatur quia recusandae dolor.
Praesentium totam et et soluta molestias voluptate occaecati. Dolores tempora ut tempora aliquam exercitationem. Ut provident laudantium voluptatibus dolores velit. Praesentium eum quia libero consequatur excepturi. Maxime praesentium eveniet sit esse et alias. Dolores quis molestiae et labore. Quas et eum molestiae et.
Illum et aut illum nobis harum praesentium delectus. Ipsa officiis dicta vero odio iure.
Cum voluptates impedit aut vero ratione hic. Tempore nam necessitatibus harum error praesentium delectus. Aut odit velit rem sapiente et at non. Necessitatibus ex non et error. Corrupti laudantium quam ut est quo suscipit consequatur.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...