Formatting Cells D26 through I26
Hi,
So in comparing my unformatted workbook to the already completed formatted workboook, I noticed that cells D26 through I26 multiplied profit by the mutiple in C12 rather than C26. Should I use C12 instead when creating the scenario analysis?

Questions on the slide (various aspects) - 6 questions
1. pro forma vs reported numbers: Why take pro forma instead of reported numbers? When you are projecting the consolidated company post M&A, which financials to use? 2. organic vs inorganic growth: When you are trying to project organic vs inorganic growth and % of growth in the combined company... 1. pro forma vs reported numbers: Why take pro forma instead of reported numbers? When you are projecting the consolidated company post M&A, which financials to use?

2. organic vs inorganic growth: When you are trying to project organic vs inorganic growth and % of growth in the combined company from acquisition vs from organic (acquiror), is it the right way to do this: use historical financials (3-5 years) of target & acquiror and going forward, except that going forward you include synergies? Is there a need to use pro forma 2004-2005 numbers from SHLD in this case and how do you use given pro forma numbers when carrying out this inorganic vs organic growth analysis?

3. Adjustments on pro forma questions: Gain from sale of business:
a. $317m gain from sale of business that occurred at pretax level but was offset by MI expense post tax. This MI expense occurs post tax below net income right? All MI income / expense usually occurs after net income to common shareholders (so post tax), is this right?

4. Adjustments on pro forma questions: Bankruptcy ($40m expense on page 9/43 on top of PDF – is this expense equal to the gain that you will add after reversing?):
a. I agree they are below EBIT but they are in between EBIT and earning before tax, so they are pretax. The slides state these numbers are post tax, how do you arrive at post tax conclusion?

b. Is gain adjustment from bankruptcy $40m from page 21/43 on the cashflow statement equal to the bankruptcy expense $40m on page 9/43. This cashflow statement is from successor company meaning GAAP right (so post M&A consolidation and not pro forma)?

5. Adjustments on pro forma questions: other income adjustments ($21m gain created from $48m other income):
a. I agree they are below EBIT but they are in between EBIT and earning before tax, so they are pretax. The slides state these numbers are post tax, how do you arrive at post tax conclusion?

b. $23m vs $21m gain from asset sale (not sure which number to use): Where is $21m gain from? On page 33/43 It was stated $21m gain from other income from $45m cure payment to Kmart? However, on notes 12 page 32/43, gain on sale of investment in total is $23m instead of $21m. Which is the right number to use $23m or $21m gain? The total other income pro forma is $48m vs reported $38m and the breakdown of other income is only given $38m (which seem to be reported, instead of pro forma) so how do you know which gain from investment sale number to use out of the $48m since the breakdown of other income pro forma is not given?

6. Estimates for pro forma diluted EPS: you used 2 methods using pro forma net income before and after accounting changes. Whichever is the method, you always take the higher diluted EPS number to be conservative correct? Hence there is no preference for either method. Read More

Re: Excel Formula of running a scenario analysis
I'm now at the end of the DCF model and I'm about to run a scenario analysis with changes of some variables, e.g. dividend payout ratio and beta. So if I want to set up a table with different combinations of these two variables, is there any excel formula that I can use to fill in the intrisic value... I'm now at the end of the DCF model and I'm about to run a scenario analysis with changes of some variables, e.g. dividend payout ratio and beta. So if I want to set up a table with different combinations of these two variables, is there any excel formula that I can use to fill in the intrisic value (stock price) in the table without changing the variables each time and filling in the stock prices manually? Thank you. Read More

Re: Valuation Summary - Private Company Differences
For a private company, you would start with Equity Value. Typically we would use the anticipated Transaction Equity Value which is justified from your standalone valuation for a private company. You would simply not have the share price or share price premium. However, do note that despite being... For a private company, you would start with Equity Value. Typically we would use the anticipated Transaction Equity Value which is justified from your standalone valuation for a private company. You would simply not have the share price or share price premium.

However, do note that despite being private, there is ALWAYS a number of shares and one can indeed value the shares since it would just be Equity Value divided by FDO (fully diluted shares outstanding). Recall, step one in any process is build financial model, then value the company and then the actual analysis at hand. Read More

Re: Exit Transaction Fees & IRR/MOIC
Yes, because in theory the IRR is a cash-on-cash calculation. Your entry equity that the sponsor puts in includes the effect of fees and therefore so should your exit. Clearly, both of these would reduce final IRR. However, do note that it's not typically common practice in an LBO model to incorpora... Yes, because in theory the IRR is a cash-on-cash calculation. Your entry equity that the sponsor puts in includes the effect of fees and therefore so should your exit. Clearly, both of these would reduce final IRR. However, do note that it's not typically common practice in an LBO model to incorporate such transaction fees at exit - although the PE shop is definitely including it in their returns analysis to their LPs. Read More