Quick background, I’m attempting to do a financial model and valuation for a company I’m following to use as an example of my abilities for networking and recruiting purposes. Before starting my model, I look at a sell-side report for this company and noticed that their model has numbers that are quite a bit different from the company’s actual releases. In fact, the equity research team’s report has a balance sheet that is half the size of what is actually reported by the company. Obviously the analyst disregarded some line items that were listed by the company, but I’m just wondering how common this is and if I am expected to do this as well. I just assumed I woul enter the company’s past info exactly, then make assumptions for the future and base my valuation off of that. Now I’m getting confused about what I’m even supposed to do. Also, should past info be linked through the IS, BS, and CF, or should the numbers just be hard coded and then link them in the future? I’m getting a case of paralysis by analysis here, kind of frustrating.
Maybe the analysts are using proportionate consolidation of subsidiaries?
If it is for valuation, there is something called ‘normalizing the financial statements’ which involves the analyst to adjust the financial statements to reflect the true economic reality…which includes disregarding the income/loss from discontinued operations, ignoring the infrequent and/or unusual times etc
Yes, consolidaton as well as interpretation (normalizing for one off events etc.) can have a big influence on a balancesheet and P/L. Especially nowadays with major write-offs and impairments. Check the equity to find out whether you are on the “right level” of consolidation.
Yeah, it looks like that is what the sell-side firm did, they took out extraordinary items and did not count income from discontinued operations. Still, it just seems weird to me to put together a model showing that the firm’s balance sheet is half of what it actually is - I’m nervious the companies I send this too will look up the company’s 10-K and wonder why my numbers are completely different. So basically, when you’re doing a model for valuation purposes (I’m sending this to buy-side IM firms), you adjust past info too? I always thought you included every line item, but on projections going forward you could just zero out the items that were extraordinary.
It sounds like you’ve picked an unusually complicated company here SP. Maybe just pick another more straight forward one. How long is the sell-side report you have? Often short notes from the sell side will just contain the key lines from their model and not all their workings. I’d try to get an initiation report on the stock if possible and get reports from several different brokers to see if all their numbers are consistent. Do you have easy access to sell-side reports? By the way hard coding of historic data is fine. There’s no problem with that. It really depends on the type of company you are looking at as to how many adjustments are needed to make past numbers comparable to forecasts. Often you will see a diluted adjusted EPS number which will try to adjust for ‘one-offs’ and funnies. Sometimes analysts will tend to exclude things like restructuring charges however which occur very frequently. In this case the analyst is overstating the long-run average earnings of the company. It is subjective as to what is extraordinary and what is not. That is why sometimes you can find two analysts’ reports on the same stock with different historic numbers.
- Are you sure the company hasn’t restated its balance sheet as the result of an acquisition, spin-off, ect? 2. Sometimes the company breaks out its shareholders equity section in great detail. Frequently sell-side analysts don’t bother to break out the shareholdr’s equity section on a go forward basis because in most cases there is no value in doing so.
Soda Popinski Wrote: ------------------------------------------------------- > Yeah, it looks like that is what the sell-side > firm did, they took out extraordinary items and > did not count income from discontinued operations. > Still, it just seems weird to me to put together > a model showing that the firm’s balance sheet is > half of what it actually is - I’m nervious the > companies I send this too will look up the > company’s 10-K and wonder why my numbers are > completely different. > > So basically, when you’re doing a model for > valuation purposes (I’m sending this to buy-side > IM firms), you adjust past info too? I always > thought you included every line item, but on > projections going forward you could just zero out > the items that were extraordinary. What items are excluded from the B/S ? Discontinued operations is usually one line and unless the Company is selling half of it’s business, wouldn’t account for why you’re B/S is half of what the 10-k reports. Are you sure the model you’re copying hasn’t modeled out working capital rather than the entire B/S ?
My experience working on the sell side is that it’s pretty common to group together line items on the balance sheet, for example 4 or 5 line items may just be grouped into Other on the analyst’s balance sheet. In part this is so all the models for the companies the analyst covers, say 20 or 30, are relatively consistent. Also during earnings season an analyst may have only 30 minutes or so to update the model so it has to be done quickly. Of course the totals typically are the same as the company’s total assets and liabilities. Normalizing for discontinuted ops etc. is a bit more unusual IMO. I have only seen the totals differ if the analyst is doing a balance sheet pro forma for a pending acquisition or divestiture. On the Income Statement its pretty common to report both GAAP EPS and normalized EPS. I agree that maybe you should just pick a simpler company for your analysis.
The company I am valuing here is Altria, and they have had some restatements due to the spinoffs of PMI and Kraft. After spending a few hours today pouring throught the old releases I have noticed that there were big chunks of discontinued ops that this analyst didn’t include. Doing the modeling has been fairly complicated, but I really am high on company and have crafted an investment thesis I am confident in, so I am going to try and see it through. Hopefully I don’t get stuck at this point, I have spent probably 20 hours on it so far. For what I’m trying to do, should I keep all the line items as they are reported by the company, or should I dump line items such as one-time items, other non-operating items, and anything to do with discontinued ops? Thanks for the input guys.
It hurts to say this but after you put 30-40 hours into this project you’ll be lucky if someone looks at it for more than 5 minutes. I’d focus on creating a functional, easy to follow model accompanied by a well thought out 1-2 page writeup about your assumptions etc. Keeping it to the point might be hard with a huge conglomerate like Altria, but if you like the company so much then go for it I guess.
Soda Popinski Wrote: ------------------------------------------------------- > For what I’m trying to do, should I keep all the > line items as they are reported by the company, or > should I dump line items such as one-time items, > other non-operating items, and anything to do with > discontinued ops? I’d dump them but make sure you tell them why you did so… ie better approximation of future cashflow.
Yeah Altria is probably quite a complicated beast to look at. In retrospect KO or MSFT or something along those lines might have been a better starting point. Still, now that you’ve so much work done you might as well see this true. I agree with others about keeping it short and sweet. How many worksheets are in your model currently? Are you doing a DCF? You don’t want to go overboard here, but at the same time I understand you want people to appreciate the effort you’ve gone to. Have you mentioned peers in your analysis? Always good when discussing valuations to include some commentary on similar companies.
Altria is a big messy pig to model up with the spinoff’s of Kraft and PMI.
I have 5 sheets in my model - working model (IS, BS, and CF on one sheet), assumptions, working capital schedule, depreciation schedule, and DCF. I thought about doing some other valuation techniques and a peer analysis, but to be honest, I was burned out as it took me probably 20+ hours to do this valuation due to the complexity of Altria’s finances, including their restatements after the spinoffs, and the fact that I lost about 5 hours of work due to my model not saving properly a couple days ago. I put a lot of work in it and hopefully that will be evident to the contacts I’m sending it to. Thanks for the help guys. EDIT: Also, does anyone know how to change just one specific page in a word doc to landscape view? My financial statements from excel are too long to fit properly on normal page layout and right now I’m just emailing out my actual model. I’d prefer to just paste the statements on my word doc, change it to a PDF, then send it out like that.
Hi Soda Popinski In MS word, go to insert, page break. Then go to page set-up, landscape, apply to this point forward. For me, I like to keep the model with as few worksheets as possible. This makes it much easier to run data tables and scenario analysis since these functions requires inputs to be on the same sheets.