Saroosh_Ahmed 2/9/00 1:44 pm
Sdmorens:
Lets see if I can help. I am not an I-Banker, but have worked with them in the recent past. Mainly, the sensitivity analysis that is around the valuation. What happens to the valuation if you change the discount rate or the FCF/Earnings/EBITDA perpetual or terminal rate (depending on how you have built the model). Also, you may have to make assumptions on sensitivities for the main drivers of the model (revenues, revenues per unit, customers, etc). The key is presenting it in a format that everyone can understand.
Example: With cash flows of Year 1: -500 Year 2: -500 Year 3: -500 Year 4: +5000 The NPV is $1717 at a 15% discount Rate
To do a sensitivity around the discount rate and the payoff, you need to keep one constant and change the other. So, keeping cash flows constant, by changing the discount rate in the range of .12 to .17, we get values between $1976 and $1563. Similarly, keeping discount rates constant for each, changing payoff to a range of 2500 to 7500 would change NPV to $230 and $2898. The best way to present the results is through a table: $2500 $3000 $3500 and so on ------------------------------ 12%| xx $705 xx xx 13%| xx xx xx xx 14%| xx xx xx xx and so on (Yahoo messes up with the formatting here) The best way to do it is through excel (Data menu; select Table) Hope this helps Good luck... Saroosh
wayne_yang.geo 2/8/00 11:25 pm
Hi, sdmorens, In a nutshell, sensitivity analysis is simply analyzing how deeply impacted a company's financial performance would be by changes in various key drivers. We get to such analysis through financial modeling.
Look again at the restaurant company example I outlined earlier here on the message board. (The message is archived on the web site: http://www.financeprofessional.com/financepro/finmod.html.) In it, I mentioned that key revenue drivers for a restaurant company might be such things as daily traffic count and average ticket price (average spent per person visiting the restaurant.) If I were going to do sensitivity analysis on such a company, I would drop and raise daily traffic count and average ticket price within certain bands, say, with the input/budgeted projections of the company, or using as a guideline what I know industry analysts are expecting for the restaurant industry. Doing so would allow me to see if a change in one driver has a deeper impact on the company than another. Obviously, sensitivity analysis can also be done on the company's margins (i.e. looking at the impact of rises in the price of labor, fluctuations in packaging costs, and so on.) Best Regards, Wayne http://www.financeprofessional.com
sdmorens 2/8/00 5:30 pm
I am beginning as an I-banking analyst this summer so don't fault me if this is a rather basic question. Anyway, today I was asked for the different valuation methods I was familiar with and was asked if I new sensitivity analysis. I think sensitivity analysis refers to estimating cash flows and determining what effects these cash flows would have on the value of the firm determined when discounting cash flows. However, I really am not sure and would like it if someone could give a brief explanation of sensitivity analysis or tell me where I might find additional info. Thank you. Saroosh
Saroosh_Ahmed 01/19/2000 10:31 am EST
What I have observed (and anyone may correct me if I am wrong), is that most Investment Bankers/Eq. Research analysts depend a lot on margins. That may be an easy (and probably pretty reliable) way to look at a company, but it may not necessarily be the most accurate. If you are modelling from an investment perspective, you could plug margins for the model; however, if you are modelling for your own business / department, you want to be sure you have all the drivers identified and make sure all your assumptions are realistic.
For instance, in the example Wayne gave, if you have a restuarant in an area that is undergoing depression/stagnant growth, you would not use the same growth rate as a restuarant in a developing and growing market.
As for L-II CFA, I am in the same boat as you are. The way I am approaching it is taking part in a study group - this really helps because you have a bunch of people with different skill sets who can solve most of the problems and give valuable inputs from their area of expertise and backgrounds.
wayne_yang.geo 01/19/2000 09:52 am EST
Hi, Connie, For sales projections, we ultimately like to get under the hood of the company to determine the key drivers of the company's revenues. I like to approximate as closely as possible the true ebb and flow of the company's cash flows, and of course, this will depend on the kind of company I am analyzing.
Let's take for instance a restaurant company. Analysts in this sector like to look at same store sales, which as you know excludes the additional revenue from new restaurants that the company has built during the period to focus on how revenues have grown (or lagged) at the restaurants the company at the beginning of the period.
The primary drivers for revenues might be average traffic count (the number of people that pass through the doors of my restaurant) and average ticket price (the average amount that the typical person will spend at my restaurant during a visit). I will multiply these numbers accordingly by the number of restaurants currently in the system and the number of days in the period in which I am looking (say, 365 if I am looking at a year). To project revenues, I would look at the financials and press releases (or talk directly to the company to get its projections for openings and closings) to get a sense of how many restaurants the company is opening or closing during a year. (Obviously, this will also help me in my projections of capital expenditures.) There is a ramp up period for new restaurants, so I will not be giving full credit to the revenues of such a restaurant during its first year.
Similar exercises are done to derive the company's margins: for personnel costs, for instance, you would look at the number of people needed to run a given restaurant, from minimum wage (and here you could project how minimum wage will rise) people to managers; for overhead you would look at the number of district managers needed then administrative people and so on.
As you can tell, these models can get as detailed as you have time or patience for. The financial departments of various companies often will have highly detailed models because they are privy to all the necessary information and because they want to have as much detail as possible to gauge their performance. As an investment banker though, IMHO there is a point of diminishing returns where adding detailed driver after detailed driver does not necessarily allow you to much better approximate the performance of the company. I hope this gives at least a fuzzy (probably rambling) answer to what you were asking. Best Regards, Wayne
connietc 01/18/2000 05:57 pm EST
First, for the projections of sales, besides on the basis of the company's past sales growth rate and company's porjection, and industry average, what other methods do you use to project sales? (like some operating ratio?) Secondly, does anyone have any suggestions as regards to how to prepare for CFA Level II? BTW, modeler2000, are you at I-Bank? It seems that you know this stuff. :)
numbacruncha2 01/13/2000 10:48 pm EST
With respect to tieing everything to sales, that's basically what I do as well, mostly because of time restraints. If ample time were available, I think I would check out the comparable Days' Receivables and Days' Payables, etc.
Also, wouldn't you have sales growth converge to the industry (or comparables) average? I basically assume that in my DCF when choosing growth rates for the terminal value.
modeler2000 01/13/2000 05:16 pm EST
let me ask or run this by everyone...
in a corporate finance seminar packet i have, it stated that one should always watch this when making assumptions for modeling: ensure that the growth rates of assets, liabilities, EPS, etc. all eventually converge on the true or sustainable growth rate of a company known as "g". "g", as known in fundamental equity analysis, is simply ROE (in the case of no dividends) or Net income minus dividends, and then the quantity divided by shareholder's equity - basically "net ROE". implying that "g" is basically the growth rate of equity since it should be the only thing that increases shareholder's equity by increasing retained earnings. from school, people will recognize "g" as part of that basic equity valuation equation for constant growth: Price = D / (k - g) should a company have its other growth rates be below this, then unfortunately it is not fulfilling its potential, but as a consolation it does have has some room to go up and improve in performance and perhaps in value. should it be exceeding "g", then it runs the risk of growing too fast, or being unable to sustain the same levels of growth in the near future.
i did know about "g" (what it is and all) and used it for finding some realistic comps, but, honestly, i did not think about using it as a benchmark growth rate until i read the packet again. in real life practice, does anyone use it as a benchmark growth rate? i don't, yet...
for the way i model, it seems reasonable; since i keep everything as a fixed % of sales. so long as i make sales growth converge to "g", then mathematically, everything else's growth rate will converge to "g" also - by the distributive property of multiplication. but that's also what has made me question if the way i model is too simplistic.
should everything not be dependent on only sales? is there another widely practiced method that actually makes it possible for EPS, assets, and liabilities to have differing growth rates, making for perhaps a messier, but maybe more realistic model... that's why i asked if its better to have debt, some other liabilities a % of assets? --------------------------------------------------
* for ShaneSr: if you tie projected A/R, A/P, Inventory, etc. to their respective turnover ratios from your base year, that is actually the mathematical equivalent to making them a fixed % of sales from the base year. oh, the evils of mathematical identities :) --------------------------------------------------
one final and repeat question for this board, for some differing opinions:
how do you properly model PP&E so your CAPEX is not so deranged? is it better to have "PP&E net" as a fixed % of sales, or or "PP&E at cost" as a fixed % of sales (with the "PP&E net" as an additional plug)? i've been doing it with "PP&E net" as fixed % of sales, "Accumulated Depreciation" as what accounting says it should be (the previous year's AD plus that year's "Depreciation" (a % of sales too), and "PP&E at cost" as the sum of the year's "Accumulated Depreciation" and "PP&E net" - with Excel set up to be able to solve for circular references of course.
numbacruncha2 01/12/2000 08:27 pm EST
To calculate FCF, I almost always use the following formula: EBIT Less: Taxes (to tax-effect the EBIT) Plus: Goodwill & Deprecitian Plus: Other non-cash charges Less: Capital Expenditures Less: Changes in Net Working Capital
It's important to tax-effect the EBIT because a discounted cash flow analysis (which I assume this is for) provides a value on the company regardless of its financing decisions (ie, it removes the tax benefit of interest expense from FCF).
For CapEx, if I can't get a realiable figure, then I use a multiple of depreciation. So a a multiple greater than 1.0 implies business growth -- sometimes 1.0xdepr is used for over-simplification and neatness.
If you can break Fixed Assets out of Net PP&E, then you can hold FA as a constant % of Revenue and calculate the change in FA as the CapEx. (So, in essence, i agree with your first suggestion....)
I just got my MBA in May so I'm pretty new at this as well. I anxious to hear how others tackle your problem. --nc2
modeler2000 01/12/2000 01:07 pm EST
ShaneSr,
thanks for replying to my monster post. FCF is estimated by one the two methods listed below, which to my understanding (from school) and practice are standard accounting/finance methods? (1) --------------- Net Income + Depreciation & Amortization + [any other non-cash charges taken] +/- changes in NWC (excluding cash) - CAPEX --------------- yields FCF or (2) --------------- Sales - COGS - all CASH charges (including taxes) +/- changes in NWC (excluding cash) - CAPEX --------------- yields FCF
on a case by case basis, debt service payments and/or dividends will be taken out to get true FCF to equity. however, like i said, it's a case by case basis. you bring up a good point about how important CAPEX is. so, then my question is, "what is the proper way to model PP&E, since it affects CAPEX so much?"
(1) should i break out "PP&E at cost" and "Acccumulated Depreciation", making "PP&E net" be a fixed % of sales and therefore, "PP&E at cost" as the simple sum of "PP&E net" and "Accumulated Depreciation"? this is exactly how the textbook i picked up does it. CAPEX is merely "PP&E at cost" for the period minus "PP&E at cost" for the previous period.
or (2) if i'm not given "Accumulated Depreciation" and, therefore, cannot break it out as easily, do i make "PP&E net" as a % of sales, and get CAPEX by doing "PP&E net" for the period, add depreciation and subtract "PP&E net" for the previous period to get CAPEX? am i calculating CAPEX incorrectly in this case?
the two methods yield very different CAPEX figures... i am still a relative newbie at this stuff, and any help would be appreciated. let me know what you think.
ShaneSr 01/12/2000 12:10 am EST
Hey modeler,
Ouch! Our company uses a proprietary model that forecasts income, balance sheet and cash flow statements that are tied together. There is a depreciation, capex, debt, tax rates for all 50 states, flooring, intang., etc. modules that all tie into the statements, so I don't have to deal with those issues. (It's about a 4 meg excel model.) I'll give your post a shot though . . .
(1) I'd forecast A/R based on Days Sales Outstanding and A/P based on DPO A/P, cash as a % of growth of sales (like 50% of growth), prepaids and accruals as a % (accruals could be a % of cost of sales if your accruals are typically payroll for a services company). Look at the other line items on a case by case basis.
(2) The cash flow statement in our model distributes all free cash flow, so your prof. model probably comes pretty close to what ours does (equity is a plug).
(3) If the statements aren't tied together, I'd leave equity a plug either way.
(4) I think it is imperative to estimate capital expenditures by themselves because of the significant impact they can have on FCF. How are you estimating free cash flows?
modeler2000 01/11/2000 07:03 pm EST
I have a good number of questions about financial modeling:
(1) when forecasting a balance sheet, is the consensus to make all assets AND liabilities as a percentage of sales? what do people generally make as their "plug" or cell that is a formula in order to make the balance sheet balance?
(2) i took a second look at a model a prof of mine made in school, and he made debt and some liabilities a PERCENTAGE OF TOTAL ASSETS, and shareholder's equity the plug - with no regard for how net income after dividends would affect retained earnings and, therefore, shareholder's equity. does anyone else do that?
(3) what do people leave as the plug in their model so that the balance sheet portion of their model balances if they have a debt schedule? if they don't have a debt schedule?
(4) if you do the following: - set up a schedule for PP&E (i.e. break out "accumulated depreciation" and then "PP&E at cost") - summing them to get "PP&E net"; - forecast "PP&E at cost" as a fixed percentage of sales; - keep depreciation always as a fixed percentage of sales, and add it to "accumulated depreciation" every year; - and get CAPEX by just subtracting the current year's "PP&E at cost" from the previous year's
you get a different level of CAPEX if you do the following: - forecast only "PP&E net" as a fixed percentage of sales, plus do not account for "accumulated depreciation" at all; - use T-account/algebraic methods to get a CAPEX figure, backsolving with the current year-end's "PP&E net", last year's "PP&E net" and the current year-end's "depreciation";
the different levels of CAPEX in turn make your FCF levels differ and valuations differ too. is one wrong? are they both wrong?
(5) is it safe to just have net income after dividends just go straight into retained earnings and be the only thing that will affect shareholder's equity? what about the rest of the balance sheet?
sorry for the lengthy post, but these things are really bugging me. the valuation differences these
methods of modeling make are quite significant. if question (4) was just too much to follow, i
will glady e-mail the simple model i made for my own sensitivity analysis to anyone's address
for illustration. please, any feedback would be great. i picked up a book called Financial
Modeling by Simon Benninga, and it shows one way, but i'm not sure if it's the best or only way
to model...