ASK WALL ST. TRAINING EXPERT Do you have a question about financial modeling, valuation, analysis, Excel or anything finance related? If so, Ask the WST Expert!   Ask WST Expert is now WST Topic Forums.

Valuation Questions Financial Modeling Questions M&A and LBO Modeling Questions * Excel Questions Show All Questions / Hide All Questions

VALUATION QUESTIONS Why is minority interest NOT included in M&A analysis? Full Question: I believe you characterized minority interest as a quasi-financing for earnings (which seems similar to interest-bearing debt). Therefore, it would seem that I should include minority interest in my enterprise value calculation. WST Expert Response: Minority Interest: when you buy a company, you don't pay for MI because you aren't buying out the minority shareholders. For standalone Enterprise Value Value calculations, you include it because 100% of the subsidiary is on your books. The Enterprise Value includes the effect of 100% of the consolidated earnings. Please elaborate why cash is deducted from enterprise value. Full Question: Please elaborate why cash is deducted from enterprise value. I always have performed this calculation because I presume the acquiror will use the cash to finance the deal. WST Expert Response: Cash is deducted from Enterprise Value and has nothing to do with M&A and form of consideration an acquiror uses (which could be 100% stock). cash is the direct opposite of debt so therefore, one could use excess cash to pay down debt and hence, deducted from Enterprise Value. How do you factor net operating losses (NOLs) in valuation? Full Question: How do you factor net operating losses (NOLs) in valuation? I try to determine the NOL present value and add it to the enterprise value. This approach is very speculative, so I try to exclude NOLs from my valuation analysis unless I feel fairly confident the company/acquiror could use these assets. WST Expert Response: NOLs are trickier - they are not typically factored directly as an asset with value in the sense of equity, debt and enterprise value, but rather, decreases the cash taxes paid, increasing free cash flow and thereby increasing overall value. Of course, NOLs can be valued separately by modeling out future cash flows with and without NOLs, then taking the differences in PV. However, in an M&A context, it gets trickier because if there's a change of control, you are limited to the amount of NOLs you may use each year, roughly estimated as the risk-free rate times beginning NOL balance. So if you had $100 NOL and the 10-yr treasury is 5%, you can only use $5 NOL each year until depleted (20 yrs in this case). A tax attorney should be consulted!! Either way, I don't think I would advise adding NOLs to enterprise value as they are related to operations and not financing. However, if they are significant, then perhaps you would increase purchase price since that means higher expected future cash flows. In general, you just take the balance sheet as is. The same idea applies to accounts receivable - you wouldn't add that to enterprise value. Of course, there are exceptions, as in K-Mart's case when it was bought for it's real estate. Follow-on Question: My company (a TLC operator) has a huge amount of tax losses carried forward expected to be used in the forthcoming 4 - 5 years. In my DCF valuation, I have discounted in the Unlevered Free Cash Flows, the income taxes at 33% (ires rate for Italy) as if the Co. has no NOLs to offsed the taxable income base. I computed the savings for NOLs, equal to the PV of the tax shields on the next 4 -5 years EBT, as a surplus assets (adding it to the Enterprise Value). The issue is: which actualisation rate should I use to discount the tax shields? WACC or K(e)? I would exclude the K(d). Response: The way we would account for NOLs in a DCF analysis is to reduce the "cash taxes" paid, thereby increasing Free Cash Flow to Firm and thus, increasing the PV of FCFF in the 4-5 year projection period. If there are significant NOLs still remaining after that, we would treat the PV of the tax shields as if it were cash, so it would affect the Net Debt calculation and thus, Equity Value, but not Enterprise Value. In short, the question of which discount rate to use (WACC or Ke) is not required. Logic: if you have two companies that are exactly the same and generates the exact same future cash flows, except one has NOLs and one doesn't, the overall value of the firm is still the same - only the equity value is affected => meaning that if you were absolutely certain the NOLs would be utilized, it is like having cash in your pocket and so, Equity Value is increased (source of funds) but not Enterprise Value. Why aren't lease payments considered "future debt obligations"? Full Question: What is the rationale for not placing lease payments in the category of "future debt obligations" on your analysis sheet? WST Expert Response: Operating leases are off-balance sheet and are a result of an operating related decision and not a financing decision. The expense related to an operating lease would go under COGS or SG&A and not in interest expense (although, yes, there is an associated imputed interest expense, but ignoring that since it's an accounting recognition rule as opposed to an economic decision). When determining current debt balance, as financial analysts, we primarily care about forms of capital (equity, debt, preferred, minority interest, etc) and so, we want to look at items related to how one financed the business (a financing decision), as opposed to operating related items (an operating decision). The same applies to capital leases as well. Capital leases are the same thing as operating leases except for the accounting treatment and so we also ignore capital leases in the context of current debt balance. Note that some companies, such as Wal-Mart, list capital leases as a separate item on the Balance Sheet and some other companies include capital leases as a part of debt (per debt footnote), depending on the overall materiality of the amount and mgmt preference. The same applies for capital leases - don't forget, the difference between an operating and capital lease is one dictated by the FASB: if you are a SNOB, you must be classified as a capital lease, on the books. Acronym for SNOB: S:Seventy-five percent (75%) of useful life N:Ninety percent (90%) of FMV (fair market value) O:Ownership (transfer of ownership at lease termination B:Bargain Price (bargain price to buy asset at least termination, ie: $1) For the WACC, should I use YTM or coupon for cost of debt? Full Question: For the WACC, should I use YTM or Coupon rate for the before tax cost of debt? I guess coupon rate, because the YTM can change if the bond is putable,callable,exchangeable,convertible etc. WST Expert Response: For WACC, you are supposed to use YTM, but for non-distressed, run-rate firms, we generally end up using coupon rate. For option-embedded bonds (putable, callable, exchangeable, convertible etc) technically neither YTM or coupon works since you must then incorporate TOTAL expected return on the capital, including capital gains as well! Keep in mind that cost of debt is technically supposed to be "incremental, marginal" borrowing rate, which cannot be physically observed until the next tranche of debt is actually issued, so YTM is the best proxy for that. What do I do for beta of a company if there is no beta? Full Question: If there is no beta for a company, then can I regress the company's excess return (to its sector market index) to the sector market return? Should I use 1 year or 5 year (1 whole business cycle) data? I know that Beta instability can be a problem. WST Expert Response: If there is no beta, use publicly traded competitors' beta! This is covered in our Corporate Valuation and Valuation (Trading Comps) classes. If a company has no beta, what are you regressing when you say regress the company's excess return to market? If a company has returns, then by definition there's a beta! For private companies or to use comps' beta, first calculate the average (or median) of the levered, observable market betas of publicly traded comps and then using the Hermada beta equation, unlever the beta and then re-lever the beta using your company or your target capital structure weights. The Hermada "all-equity" beta equation is: Unlevered beta = [levered beta / [1+(1-tax rate) x (debt to equity ratio + preferred to equity ratio)] - assumes preferred is tax deductible. If preferred is not tax deductible, isolate preferred outside of the ratio as follows: Unlevered beta = [levered beta / [1+(1-tax rate) x (debt to equity ratio) + preferred to equity ratio]. Please clarify if any value is gained by buying back stock. Full Question: It has been said that holding cash isn't necessarily bad for a company. My belief is that the company can repurchase shares, therefore increasing their debt/equity ratios. Higher debt = higher tax shield so the value of the company is greater. Now, theory says, that's not true because if the markets are efficient and the stock is fairly priced, than buying the stock is a zero NPV project. No value creation. Both arguments make logical sense, but I can't seem to reconcile the two. WST Expert Response: In general, if markets are efficient and an asset is fairly priced, then there shouldn't be any excess returns at all and everything is a zero NPV project in theory. I usually say there are three reasons why a stock price should go up upon a share repurchase: (i) Financial / Mathematical: reduces shares outstanding, increases EPS, constant PE, stock price up; (ii) Behavioral Finance: signaling effect, mgmt thinks stock undervalued, buy low, sell high; and (iii) Economic: reduced supply, increased demand. But in each of those three cases, it's the market's inefficient response! Conceptually, the lack of value creation makes sense. The answer somewhat lies in the debt / equity ratio. To better illustrate this, recall the fundamental relationship between enterprise value, equity value and ultimately, stock price. The total enterprise value of the firm will not change depending on how you alter your mix of capital structure (our Corporate Valuation and Corporate Finance class). What are you doing is altering your capital structure by buying back shares. Equity value is simply the "residual" of enterprise value after Net Debt. So if you increase your debt or reduce your cash to buy back shares, by definition, your equity value will decrease. Again, that's because our assumption is that enterprise value doesn't change. This decrease in equity value is perfectly offset by the reduction in share prices. Take a look at the following short example and you'll see why this works: Scenario: Buy back $30 worth of stock using excess cash or by borrowing more debt: Exhibit A – Valuation Status Quo Use Cash Use Debt TEV 1,000.0 1,000.0 1,000.0 Total Enterprise Value (constant) Debt 200.0 200.0 230.0 Use Cash: debt constant, Use Debt: borrow Cash 50.0 20.0 50.0 Use Cash: use cash, Use Debt: cash constant Eq. Val 850.0 820.0 820.0 Equity Value: TEV - Debt + Cash S/Out 100.0 96.5 96.5 30/8.5 = 3.53; 100- 3.53 = 96.47 Price 8.5 8.5 8.5 EV / S/Out You mentioned the effect of the tax shield on interest expense as another possible explanation. Whether you increase debt or decrease cash to buy back shares, either way, your Pre-Tax Income goes up because of the extra interest expense or reduce interest income. Net Income therefore goes down, resulting in lower EPS. The question becomes, do these changes exactly offset each other? The answer is yes, they do, and so the EPS also stays the same. Assuming a constant PE ratio, there should also be no change to stock price. See below example, building off above example: Exhibit B – Income Statement @ 10% interest Status Quo Use Cash Use Debt EBIT 100.0 100.0 100.0 Interest Expense 20.0 20.0 23.0 (10% of debt balance on top) Interest Income 5.0 2.0 5.0 (10% of cash balance on top) EBT 85.0 82.0 82.0 Taxes 34.0 32.8 32.8 40% tax rate Net Income 51.0 49.2 49.2 S/Out 100.0 96.5 96.5 EPS 0.51 0.51 0.51 Estimated kE/"ROE" 6.0% 6.0% 6.0% Net Income / Eq Val Estimated kD 6.0% 6.0% After-tax cost of debt and cash Now, where the potential arbitrage actually does come into play is with the differential between the "cost of equity" and the "cost of debt". If the opportunity cost of the debt or cash used to fund the share repurchase is lower than the cost of equity, then buying back stock will have a small increase in the stock price. As a quick "back-of-the-envelope" test, take a look at Exhibit C in which the cost of debt and cash is below the Status Quo cost of equity. In Exhibit B, the selected 10% cost of debt and cash happened to equal the rough cost of equity and so there was no value created in EPS. However, in Exhibit C, the cost of debt and cash were selected as less than the Status Quo cost of equity, resulting in accretion to EPS. The reverse would be true as well. Exhibit C – Income Statement @ 6% and 3% Status Quo Use Cash Use Debt EBIT 100.0 100.0 100.0 Interest Expense 12.0 12.0 13.8 6% rate of debt balance on top Interest Income 1.5 0.6 1.5 3% rate of cash balance on top EBT 89.5 88.6 87.7 EBIT - Interest Expense + Interest Income Taxes 35.8 35.4 35.1 40% tax rate Net Income 53.7 53.2 52.6 EBT - Taxes S/Out 100.0 96.5 96.5 From on top EPS 0.537 0.551 0.545 Net Income / S/Out Estimated kE/"ROE" 6.3% 6.5% 6.4% Net Income / Eq Val Estimated kD 3.6% 1.8% After-tax cost of debt and cash Please clarify if any value is gained by buying back stock. (Follow-on Question) Full Question: So I understand your example. But my understanding is that M&M proposition II says that in a world with taxes, increasing debt means increasing the value of the firm or Enterprise Value. In the examples you provided me, the enterprise value of the firm stayed constant. I'm saying, if we change the debt/equity levels through share repurchase...enterprise value should go up. So how is that not a positive NPV project? The only way I can reconcile this is to think that the potential value creation from leverage is priced into the stock, so it's zero NPV. However, I don't know if that's the right idea. WST Expert Response: You have to remember the core lessons from my valuation class! If nothing else has happened to the company, why should there be a change to the value of the company (enterprise value)? If the "core, recurring profitability from core operations" has not changed, there is no change to TEV. Capital structure changes do not change this as capital structure is a "function" of debt and equity mix, meaning that equity value is a derived number. So if you just altering the capital structure mix, the core value (TEV) stays the same. Your reference to M&M II seems to be tackling it the other way around - that equity value is the beginning point and u add debt to get TEV which is what we do to calc the current mkt observable TEV but again, that TEV is supposedly fixed if there are no changes to profitability. This is the same concept of why we always use FCFF and never FCFE because calculating an equity-based number doesn't tell us anything abt the value of the company, but just the equity. Then, once u know TEV, you boil down to derive equity value. Free Cash Flow to Firm vs. Free Cash Flow to Equity Full Question: When calculating free cash flow to equity, we adjust the capex+working capital+depreciation numbers so that it represents only the equity portion. That makes sense because the equity shouldn't be responsible for all the cash outflows of the firm. However, when I think about the physical cash that's left for equity holders, it's after all capex + working capital etc. is paid out. Again, I can't reconcile the two arguments. Why do we take a percentage of capex + working capital items? WST Expert Response: In general, free cash flow to equity is a useless number. Free cash flow to firm is by far the superior method over free cash flow to equity. Free cash flow to firm takes into the account the capital structure differences between two companies, again per the enterprise value and equity value relationship. Free cash flow to equity starts with Net Income, having taken out interest expense. As we know this interest expense will significantly alter Net Income unproportionally and thus, this method should be avoided. However, if one must use free cash flow to equity, it doesn't make sense to me to adjust depr, capex, and wc to reflect just the equity portion. The first problem with that approach is what %age do you use to adjust? Either way, as you pointed out, the true "excess cash" left to equity holders is after the full depr, capex and wc has been taken into account. My response is that you shouldn't take a %age of depr, capex and wc. I mentioned that free cash flow to equity is generally useless. However, I'm sure there's an exception somewhere but I can't think of any right now. Even for the case of a company that is heavily valued on PE ratios for terminal value purposes, you still wouldn't take a %age. You would still calculate free cash flow to firm and then value the terminal value based on PE multiple (apply the PE multiple to Net Income to arrive at Equity Value, not Price per Share). The question here usually is, do I need to allocate my Net Debt between the free cash flow to firm and the terminal value (which now is on an Equity Value basis and not an Enterprise Value basis). To boil down to Stock Price, the answer is no, just add the PV of FCFF and the PV of TV, subtract out Net Debt to get implied Equity Value. Keep in mind that free cash flow to equity includes the net effect of debt issuance/repayments! Do you include the effect of pension liability in firm value? Full Question: Do you include the effect of pension liability in firm value (enterprise value or equity value)? WST Expert Response: Generally, in a standalone valuation context, such as a trading comps analysis, unfunded pension liabilities are not adjusted for. Trading comps attempt to quantify the current market valuation parameters. Unfunded pension liabilities are not considered part of the capital structure as it is not a form of capital in the equity value, net debt, preferred and minority interest summation to get to enterprise value. Unfunded pension liabilities would definitely be adjusted in an M&A context primarily as a purchase price allocation - when buying a company, the acquiror would want to make sure the pension is fully funded and so, would deduct unfunded pension liabilities from purchase price or insert a clause that the seller is responsible for paying future liabilities. However, in extreme cases of unfunded pension liabilities (such as large unfunded liability), one might decide to adjust for it under certain circumstances when calculating current market valuation. However, it really depends on the specific situation and contxt. In such a standalone situation, valuation multiples would not really be affected since unfunded pension liability typically does not hit the Income Statement, but instead through Other Comprehensive Income. Why do we use tax-effected EBIT instead of EBITDA for the DCF terminal value? Full Question: Why did we use the tax-effected EBIT instead of EBITDA when calculating the terminal value using the perpetuity growth rate? Is this the standard in the business? Also, if we have the growth rate, can we calculate the EBITDA multiple using the following formula: (1+growth rate)/(WACC-growth rate)? WST Expert Response: In a DCF, for the perpetuity growth rate method of calculating terminal value, CF*(1+g)/(r-g), what would be the proper term for "CF"? One should not use EBITDA since it is merely a proxy for cash flow and does not properly estimate Free Cash Flow to Firm. EBITDA rightfully is before the effects of capital structure and leverage in that it is pre-interest expense but you still have to pay taxes! Free Cash Flow to Firm might sound like the correct term to use for the "CF" at first glance, but if one is to use the perpetuity growth formula, which is a theorhetical concept, then following pure theory, Capex and depreciation net out over time and the changes in working capital are so negligible (by definition, very low growth) that the three terms cancel out, effectively, left with "Tax-Effected EBIT" as the correct term to use for "CF". In other language, "Tax-Effected EBIT" is also known as NOPAT (Net Operating Profit After Tax). Do note that there are other sources out there who disagree and argue that if one is to use "Tax-Effected EBIT" then the denominator should be simply g and not (r-g), although I don't agree with that. If you have the growth rate, the Implied EBITDA multiple would simply be the Terminal Value / Terminal Year EBITDA. How is the WACC - cost of debt affected by a tax benefit? Full Question: I have a question regarding cost of debt calculation. If a company has a tax benefit rather than the usual tax expense, then how is the after tax cost of debt calculated? WST Expert Response: Recall that WACC and therefore, the cost of debt is based on marginal, incremental borrowing rate. You are always trying to capture a "normalized" run-rate discount rate. The fact that you have NOL's (tax benefit) should be irrelevant because again, one is trying to capture normalized tax rate and therefore, NOL's should be effectively ignored for this calculation. The sole point being that the NOL's will run out. To properly reflect the benefit of NOL's in valuation purposes, figure out normalized, run-rate valuation and then add the NOL on a net PV basis. The rationale is that the discount rate reflects the risk of the company and shouldn't be distorted by NOLs until the very end. The other way to incorporate NOL's is through the construction of a detailed tax schedule reflecting both deferred tax assets and liabilities. The NOL's obviously shelter income and therefore result in increased profit and cash flow in your DCF analysis, but either way, the mat should work out the exact same. How do I incorporate maintenance capex into my free cash flow calculation? Full Question: I took your "Corporate Valuation Methodologies" class and was impressed with the quality of the class and hope to take more in the future. I am struggling to find a valuation procedure that I'm comfortable with. The denominator should be EV. The appropriate numerator, in my view, should be FCF. (whether this is levered or unlevered; I don't even mind if it is levered since I'm usually thinking about buying the equity). I'm trying to find out the likely (initial) return for a buyer of the business. Warren Buffett has stressed the point that FCF should take into account an allowance for maintenance capital expenditures (he refers to EBITDA as bullshit earnings since depreciation is the worst kind of expense, i.e., an expense you have to pay for up front with your initial capital outlay). When I get financial information from say, Yahoo Finance, I cannot find out what maintenance capital expenditures are (and this is not unusual even with analyst reports). I can find operating cash flow. My question relates to finding a proxy for free cash flow that is really available to the business owner and takes into account maintenance capital expenditures. Would using operating cash flow be appropriate (keeping in mind that it does not account for maintenance cap ex)? For example, if I go to Yahoo Finance and type in WMT and then click on key statistics, it gives me an EV of 240B, EBITDA of 24.5B, operating CF of 18.4B and levered FCF of 2.2B. When I'm trying to value the business I don't care about actual capital investments and expenditures, I'm only interested in FCF minus maintenance cap ex (for the purposes of valuation). In the absence of a guesstimate for maintenance cap ex, would operating CF be a good proxy or would you suggest another term that would involve more tweaking? WST Expert Response: I think you've mixed up too many academic theories and practical application. Usually, the capex number includes maintenance capex. this is the same total capex number from the cash flow from investing line as well as capex in the FCFF calculation. this makes buffett's point about FCF including an allowance for maintenance capex irrelevant as it should already be included. when looking at publicly traded companies' 10Ks, you will not usually find this number, but you could probably imply it based on the historical capex trend, although this isn't straightforward. To use EBITDA as a free cash flow to firm estimate would be wholly incorrect. EBITDA is a proxy for cash flow from operations, not to the entire firm. it just so happens that valuation metrics are applied based on EBITDA and not FCFF, which does correcly take into account the largest components of free cash. You mentioned you don't mind if FCF is levered or unlevered and here I would say that you do mind. levered FCF is basically FCFE (free cash flow to equity) which is a useless number since it is adjusting for capital structure and leverage incorrectly. two companies with the same operations and even capital structure would have a different FCFE due to different tax rate and interest rates, thus introducing even more unncessary noise. Operating Cash Flow means different things to different people and is always calculated differently. As mentioned earlier, FCFF takes into account maintenance capex, or rather, it should have already! ROE vs Revenue as stock return predictor Full Question: On the attached exhibit, chart one plots revenue vs. return, and chart two plots ROE vs. return. The questions are: What are the flaws in just using Revenue? My guess: Does not capture expenses or profit margins. What are the virtues of using ROE? My guess: Captures both margins and revenue growth. What are the pitfalls of using ROE? My guess: It is based on book value of equity so return is not what a new investor would get, companies that invest in cap-ex, etc. for growth will show low ROE initially so you’d miss these opportunities with a required ROE hurdle, doesn’t indicate level of risk used to generate ROE. What is the assurance that the value produced by a high ROE company will flow through to the owners? My guess: The stock price must appreciate and/or dividends must be paid or else the firm will be taken over. WST Expert Response: 1) Revenue - correct, it does not capture expenses or profit margins. the "retention" of the revenue is very important! the more of that dollar of revenue you keep, the more it is worth! 2) ROE is an important metric for banks because they employ leverage - they borrow deposits and use that to make loans, so the equity amount is very important for relative comparison purposes. Similar to banks, insurance companies are also important. 3) Pitfalls of ROE - excludes the "core" earnings power of the entire company, and only looks at equity. For a bank, the concept of Enterprise Value doesn't exist, and Equity Value is the key, but for other non-balance sheet companies, ROE is merely but one metric to look at. Hence, look at the holistic profitability and valuation picture. Do you need to adjust EBITDA for non-cash items? Full Question: When calculating a comp’s EBITDA for valuation-related purposes, do you adjust for all items of a non-cash nature – specifically stock-based compensation expense, LIFO inventory adjustments and pension/OPEB expenses? Or, do you have to pay respects to the fact that EBITDA is a mixed figure derived from both accrual and cash-based accounting? WST Expert Response: EBITDA - no adjustment for non-cash, remember it's a proxy for cash flow, not trying to really figure out cash flow. It's supposed to measure core ability to generate cash - if you want true cash flow, I would point to FCFF instead which incorporates Working Capital changes (and obviously CapEx). Remember to ask yourself, what is the whole point of EBITDA or other figures? In this case, EBITDA is used by the market as a major valuation multiple, for better or worse. There are plenty of debates out there on its shortfalls and everyone's opinions on a better measure, but the fact remains, that is what the market uses for measurement purposes. So in short, you do not have to pay respects to the fact that EBITDA is a mixed figure of accrual and cash-based accounting. Regarding pension expense and OPEB expense: in general, pension and other post-employment benefits have already been accrued for GAAP purposes in expenses and therefore, are not supposed to be added back to EBITDA because then you overstate the "core profitability" and "core cash flow generation". Note that this is different from IRS tax books in which pension expenses are not recognized based on accrual basis but when actually paid! For WACC, market or book value and what about regulated industries? Full Question: 1) Would you suggest for calculating the WACC (to be used as the discount rate) using the market value of the securities rather than book value in order to determine the weightings for the cost of debt and equity securities? (2) same question, but...If an industry was regulated (e.g., a state utility commission determines what ROE the company is allowed to earn), would that make a difference as to whether market or book value of equity should be used to determine the weightings of the cost of capital? WST Expert Response: 1) In theory, WACC should always be calculated using the market value of equity and debt to determine the weightings for each. However, it is common practice sometimes for ongoing, going-concern, run-rate, non-distressed companies to use market value of equity and book value of debt, the rationale being that the book and market value of debt are extremely close and similar. In either case, market of equity value of equity should always be used. 2) Just because an industry is regulated, that would not affect anything. In all cases, market value of equity is ALWAYS to be used, NEVER book value of equity. The fact that an industry is regulated, such as utilities, would affect the cost of equity (via CAPM) but doesn't affect the weightings. Maintenance capex vs capital expenditures revisited Full Question: Should capital expenditures that are not purely maintenance capex be reflected in the cash flows as an outflow of cash as long earnings from that investment are reflected in the EBIT? WST Expert Response: YES! CapEx that is not purely maintenance capex is to be reflected in the Free Cash Flow to Firm calculation! To do is would be remiss and excluding a significant portion of cash that doesn't below to the firm's stakeholders! Capital leases revisited x2 Full Question: Why do we subtract capital lease obligations from total debt? WST Expert Response: Capital leases are capital leases b/c the accountants said so. Else they would be operating leases and off-balance sheet. Operating leases are generally not added back to debt for valuation purposes as they are considered operating in nature and not capital structure in nature. See above question on operating leases for more information. Follow-up Question: I'm in the natural resources group and the analysts in my group and I got in a discussion today about capital leases and total debt. I think from the ratio exercise (where we inputed everything by hand), we excluded capital leases. However, some analysts say that capital leases are added to total debt (in D/E beta calculations), and that capital leases are added back to total debt in credit calculations. Which is correct? Response: Industry is important - they should not only be adding back minority interest but also subtracting out equity investments (opposite of minority interest). Rationale: match the numerator (valuation) with denominator (EBITDA). So that means, profitability on IS is increased by 100% of subsidiary, but their ownership of other companies is not consolidated. think it through... again, per our explanation of capital leases, generally, NOT added back. in the E&P sector, if capital leases are very large, then it's not necessarily incorrect to add back, however,then operating leases should be added back for consistency (which is usually not done). Cost Accounting vs Equity Accounting Full Question: What is the difference between Cost method Accounting Investment and Equity method Accounting Investment? WST Expert Response: Cost Accounting - Procedures used for rationally classifying, recording, and allocating current or predicted costs that relate to a certain product or production process. translation: expenditures expensed as incurred (recorded as current period cost, immediately) refers to: recognition of expenses in compiling financial statements (P&L) opposite: accrual accounting Equity Method of Accounting - Investors cost basis is adjusted up or down (in proportion to the % of stock ownership) as the investee's retained earnings fluctuation; used for long-term investments in equity securities of affiliate where holder can exert significant influence; 20% ownership or greater is arbitrarily presumed to have significant influence over the investee. translation: don't consolidate, minority interest expense (if majority shareholder) or equity income (if minority shareholder) refers to: primarily for JV's or investments in a company opposite: full consolidation cost vs equity method are really two completely separate issues What is the difference, if any, between book tax vs GAAP tax? Full Question: A company XYZ has a different book tax versus cash tax. How does that impact their FCF? And if they tell you that their book tax is 25% vs cash tax is 15%, how do you adjust for it in the FCF statements and analysis? Also, what is the difference, if any, between book tax vs GAAP tax? WST Expert Response: GAAP requires the accrual method be used when companies create their financial statements. What you see on a company’s reported financial statement is commonly referred to as book. The pre-tax taxable income that a company reports to the IRS generally does not equate to pre-tax book (financial statement) income, due to both temporary differences and permanent differences. The IRS calculates taxable income on a cash basis. The need for deferred tax accounting arises because companies often postpone (creating a deferred tax liability) or pre-pays taxes (creating a deferred tax asset) on profits pertaining to a particular period. The formal relationship (which may clarify the terms) is book income = taxable income plus/minus temporary differences ± permanent differences. Temporary or timing differences between book vs. taxable income are due to items of revenue or expense that are recognized in one period for taxes, but in a different period for books (over the ""long run"", they cause no difference between the two incomes). The most common examples of temporary difference arise from depreciating an asset by the straight method for book (which creates lower expenses) and the accelerated method for taxes (which creates higher expenses and thus less of a tax burden). Permanent differences are differences that never reverse. An example of a permanent difference is (non- taxable) interest revenue on municipal bonds. Temporary differences cause deferred taxes, while permanent differences cause a firm's effective income tax rate (book income tax expense ÷ pre-tax book income) to differ from the statutory tax rate. What drives deferred tax accounting is (the changes in) the deferred tax asset and liability accounts. Deferred tax liabilities are liabilities for taxes due in the future (future cash outflow for taxes payable) on income that has already been recognized for books. In effect, although you have already recognized the income on your books, the IRS lets you pay the taxes later (due to the timing difference). Deferred tax assets are reductions in future taxes payable, because you have already paid the taxes on book income to be recognized in the future (like a prepaid tax). Recognition of a deferred tax asset reduces future cash taxes payable and thus enhances FCF (although the asset has value only if the firm expects to pay taxes in the future). The way it will impact your financial statements in your exaple is as follows: you should see a deferred tax asset on your balance sheet. The asset should slowly be reduced as the firm offsets cash taxes paid (book or reported taxes will be unaffected). Under cash flow from operating activities section of your cash flow statement you should see a line item called deferred income taxes. This will effectively increase you CFO and thus FCF if you are calculating FCF as FCF – CapEx by the amount of you tax savings. The effect would be as you described - your tax cash rate (what you are actually paying) would be below your book rate (what you are reporting on your financial statments). Should companies ever use diluted shares to figure out WACC (instead of basic shares)? Full Question: Should companies ever use diluted shares to figure out WACC (instead of basic shares)? WST Expert Response: yes - since WACC uses market value of equity, and equity value is always calculated off diluted shares outstanding, it would be accurate to use diluted shares and not basic. How to value a company if we do or don't invest? Full Question: We are using DCF to value an existing equity investment.  The company is planning a capital increase two years down the road.  We may or may not participate in the capital increase.  My question is how should value our existing equity investment under these two scenarios (whether we will invest in the capital increase). WST Expert Response: The question of current valuation with all known facts is a separate and independent issue from the capital raise in the future. Meaning that they are two disparate, unlinked events. If it is known with certainty that a capital raise two yrs later is to be done (and more importantly, the %age to be given up with that 2nd capital raise), then model out the company as a going concern with all known facts. Ie - include the economics of the second raise and it doesn't matter who invests in that future round. Then calculate the value of the company and your returns based on your current ownership %age. WACC - preferred debt's tax deductibility and beta - unlevered vs levered equation Full Question: 1) Your Corporate Valuation Methodologies course states that Unlevered beta = (levered beta/ (1+(1-Tax rate)x(debt/equity+preferred/equity) - why the denomintors are equity not TEV? 2) The Company Profiles module says assume preferred stock is not tax-deductible which is different from Corporate Valuation module - so exactly, is preferred stock tax deductible or not? WST Expert Response: 1) Unlevered beta evaluates the relationship between debt and equity. Total Enterprise Value (TEV) is a valuation-based metric that is not meant for the levering/unlevering of betas. This is straight from the academic world, Hermada beta. 2) Most of the time, preferred debt is not tax deductible because the investor does not have to pay taxes on 70% of the income they earn from receiving preferred dividends under specific conditions. Therefore, in the Hermada beta equation, the Pref/Equity ratio is outside the (1-T)(D/E). However, in the specific example in our Corporate Valuation module, that company has a structure called TOPrS, in which case, the preferred IS tax deductible for the issuing company. TOPrS is Trust Originated Preferred Securities and other variations include CRESTS, HITS, etc, depending on the underwriting bank. How do convertible bonds affect total capitalization? Full Question: Assume we convert all the in-the-money convertible bonds.  Would this effect a firm's total capitalizaiton?  If, no, then do we simply move the amount of CB from debt to stockholder's equity?  Thanks. WST Expert Response: Yes, the TEV effectively increases as presumably, the post-conversion captial structure has more equity due to dilution. That's on the market value side. On the balance side side, correct, remove converts from debt as before and increase book value by the converted amt (shares * price /share). However, note that in a standalone model, once you assume conversion, APIC increases and no further adjustment in future years. But for trading comps, you have to constantly re-assess in-the-money or not. Mechanics of FCFF calculation Full Question: I have just started to learn how to calculate FCFF and have an obvious “newbie” question - Can the FCFF be calculated for the current quarter, or past year, without estimating forward? Simply put, is it logical and/or accepted industry practice to look at the current, or past FCFFs, or is it only used (useful) for estimating forward? And as a follow-up, if you can calculate the FCFF for the current quarter, or past year, how do you handle “Net Working Capital”? In my FCFF estimates for future years, I am using the current year’s working capital as a percent of revenue and applying this to my incremental revenue estimates for each year going out. If I were to apply the same approach to the current quarter, or past year, how would I do it? Do you simply take the incremental change in the “Working Capital” from the current quarter vs. previous quarter, or current year vs. previous year? Any input would be greatly appreciated. I hope my questions make sense to the experts, but as I said, I am a “freshman” at equity valuation. WST Expert Response: Yes, FCFF can be calculated for any time period. however, for purposes of DCF, you would want to calculate projected FCFF. Note that you don't necessarily have to have a complete financial model to do so - you can still estimate it by assuming growth rates on last known historical figures. There are several ways to estimate NWC: (i) % of revenue; (ii) % of YoY revenue change; (iii) flat assumption if fairly stable historically; (iv) change in 30/60 days expenses (COGS+SG&A) What is the difference between a special dividend of $100 and a share repurchase of $100? Full Question: As you know, a company can do a share repurchase, or a special dividend, when they're trying to return capital to shareholders on a standalone basis. Why is it that both a special dividend of $100 and a share repurchase of $100 of stock necessarily results in the same exact pro forma share price? This is all assuming that the company takes on the same amount of incremental debt to fund the share repurchase/dividend, and that the share repurchase occurs on a pro rata basis. WST Expert Response: Remember back to our share repurchase discussion. You are swapping out cash basically so same net effect to net equity value. Dividends get paid which reduces cash which is same as share repurchase. So the decrease in shares outstanding vs the interest cost is the same when you run the numbers. In theory the stock price falls by dividend amt which is the last key. Stock-Based Comp adjustment Full Question: So the short answer to the question is actually that when companies account for stock-based comp. it is a non-cash charge that should be added back in the CFO area. However, because stock-based comp. must be accounted for by the company as a potential benefit (b/c upon exercise the company receives an income tax benefit so they account in the asset side under deferred tax assets) they must account for that benefit on the other side of the equation as well. This happens through retained earnings (NI is reduced by the charge) and there is an add-back (roughly) in paid-in capital. Without the add-back in additional paid-in capital, the model will not balance. Also, when options are exercised, they provide cash which is accounted for through the CFF and occasionally a tax deduction through the CFO. That is what I have found so far. Please let me know if someone at your firm thinks I am right. WST Expert Response: Your asssessment is correct; however, if you never tell excel that stock-based comp is non-cash, IE, you embed within SG&A as it is most likely done as reported on 10Ks, then you don't have to make the adjustment. The incremental precision gained by modeling out stock-based comp and any option exercise is fairly minimal in our case, that we usually ignore it as it is immaterial. Why is there a holding company discount? Full Question: Why in equity valuation is given a discount to holding companies? For example, a company like GE. WST Expert Response: Short Answer: Corporate overhead allocation and inefficiencies. Longer Answer: Clearly, conglomerates exist under the context of "economies of scale" (increased purchasing power, lower cost of capital, etc) but there is a cost to coordinating all the activities. "Sum-of-the-parts" valuation many times indicates that the parts are greater than the whole due to inherent inefficiencies. From a market multiple perspective, different growth prospects and margins might also drag down a holding company's earnings. How do I treat Deferred Maintenance Revenues in TEV? Full Question: I have a question regarding how to treat Deferred Maintenance Revenues relating to maintenance fees earned by a company for software licenses in terms of calculating the Equity Value of a Company. Normally, the Equity Value = TEV + Cash less Debt. However, should the Deferred Maintanence Revenues which are not classified in the current liabilities section of the balance sheet also be subtracted from the TEV in order to calculate Equity Value? If so, what is the justification for doing so, and if not, then what is the justification for not including it in the calculation? WST Expert Response: Normally speaking, TEV focuses only on capital structure and "sources of funds". In other words, it focuses on valuation of entities. However, your question focuses on a "working capital", "day-to-day operations" of the company as opposed to capital structure. Thus, without knowing more about the specific industry or company in question, our initial gut feeling is to do nothing to your equation for TEV and Equity value, in other woreds, don't adjust either for the Deferred Maintenance Revenues. Follow-up Question: Thank you for your email. The deferred maintenance liabilities relates to software maintenance contracts. Normally the company receives cash upfront at the start of the 3 year maintenance contract and then has an obligation to perform on such contract over the 3 year period. These obligations are what the deferred maintenance liability account balance on the balance sheet are comprised of. Each month, this liability is reduced as the company recognizes a portion of the maintenance contract revenues into income. Much of the cash received from these contracts may have already been spent by the existing owner and therefore, the new owner of the business would still be liable to perform on these maintenance contracts even though the cash that was originally received for these contracts are no longer available to the new owner to cover the related expenses of performing on these contracts. In addition, when the original maintenance contract is just about to expire, the company will invoice customers about 30 to 90 days in advance of the termination of the original contract. The company will record the maintenance contract revenue on these renewal maintenance contracts upon invoicing and not when the maintenance contracts are accepted by the customer. I understand that normally TEV + cash - debt = Equity Value. However in this case, how would you treat the deferred maintenance liability which is shown below the current liabilities but above the stockholder's equity section on the balance sheet considering 1) the fact that some of the cash that has been collected on the maintenance contracts is no longer at the company and 2) that some of the deferred liability represents future obligations that are booked prematurely because the company invoices 30 to 90 days in advance of the termination of the original contract and will record the maintenance contract revenue and liability on these renewal maintenance contracts upon invoicing and not when the maintenance contracts are accepted by the customer. Thank you in advance for your help!! Response: This is definitelty akin to deferred revenue and unearned revenue (ie magazine subscriptions). Thus, not to be part of TEV and so the same answer - don't add or subtract. In the case of a merger, we would normally treat this as a closing adjustment due to working capital - that is, the buyer requires a minimum working capital of say, 60 days operating expenses. Any shortfall is a one-to-one deduct from price and any excess is a one-to-one increase to price. However, one could argue that if this is a significant materially large number, it wouldn't be unreasonable for the buyer to ask for the revenue for that since according to accrual accounting, the existing owner shouldn't have spent it. That's a separate negotiation for working capital, but again, in our opinion is not a TEV adjustment. Since TEV doesn't include working capital shortfalls, its not "officially" part of TEV although it does affect purchase price as stated above. As for revenue recognition and early invoicing - that's not GAAP and if it were publicly traded, the auditors would never let them get away with it, at least not today. Think back to dotcom bubble and all the restatements. Revenue recognition according to GAAP says "service performed" and "collected/collectible". If customer never agrees to renewal, then its not collectible. How to calculate CAPM for emerging markets? Full Question: Let’s say you’re valuing a company via DCF and so you need to figure out the target company’s cost of equity via CAPM. You’ve selected your comps, almost all of whom are US-based. But you’ve also got a british comp in there, who is a great comp. let’s further assume that the british comp does NOT have an ADR, so you can’t get a US-based beta for it. also, as you know, british comps trade based on a slightly different market, so their betas etc will be impacted by country-specific factors, investors, macroeconomic outlook etc. Clearly, cost of equity via CAPM assumes the US risk-free rate. What adjustment(s) if any, must we make to the British comp’s beta to make sure we are doing an apples-to-apples comparison? if your answer is that “it’s okay to mix apples and oranges in this case, because Britain isn’t very different from the US”, then let’s assume the company was based in Singapore or Russia – ie places with very different macroeconomic environments. Then, what would your answer be? WST Expert Response: easy answer - take our emerging mkts class! seriously though, great question with easy answer - make sure that when you grab the beta from bloomberg that you use S&P 500 as your index as opposed to the default which may be FTSE 100. rationale - as you noted, you DO want to capture country specific risk, although you won't capture currency risk b/c of the direct comparison to S&P. the rationale is that you want to make sure that you are consistent with CAPM which stipulates that you are trying to isolate risk above the risk free rate to a well diversified portfolio. the market risk premium shall continue to be from ibbotson as before, thus ensuring an apples-to-apples comparison. sometimes, there are other issues due to ethnocentricity or just plain ego in which for developed countries like West Europe, you would intentionally violate this and instead of using US Treasury and S&P, you would have to use country specific debt and the country-specific benchmark. note that CAPM requires "true risk-free rate" and the USA, being the only major country not ever taken over before, satisfies that requirement and not any other country. And no, that is not American ethnocentricity, talk to whomever came up with CAPM. Those converts seem in the money, but why aren't you adjusting for it in TEV? Full Question: My question is about COSTCO's convertibles. I was familiar with "if converted method" to measure the potential dilutive effects of potential dilution from CFA curriculum. The explanation given in the lecture was quite different. In the video, it was explained that whenever the face value of the outstanding convertibles is larger than the market value of the equivalent number of shares if converted, the holder of the convertible will not convert and there is no need for adjustment with respect to share dilution. Isn't this commensurate to comparing apples to oranges? Normally, one would compare present values to present values and future values to future values. Face value of the convertibles is their future value. Their PV is far below their face value and also the value of the shares if converted. In White and et al. (2003) the comparison of share price and the conversion price is given as a decision rule for deciding for the dilutive effects of convertible bonds and using option pricing models is suggested as one of the ways to separate debt and equity values of the convertible bonds. So the decision for the holder of the convertible is whether to exchange or not the PV of the convertibles for the equivalent number of COSTCO common shares. If this value is less than the total value of the shares,the convertibles are likely to be converted. So to sum it up, why is the future value (i.e., face value) of convertibles compared to the market value of shares? WST Expert Response: Per the instruction in the video, it clearly stipulates that the key question in the costco converts rests on the timing - how long away is the maturity of the converts? if it matures tomorrow, the holder wouldn't convert b/c they can get more by simply holding to maturity and receiving par value. as financial analysts in the generic sense, one cannot introduce subjectivity as to say when is "far away enough" that a rationale investor would convert. Again, to re-iterate our point, you mentioned PV of converts vs equivalent number of costco common shares - true statement, however, what costco shares suddenly dropped the next day? then it is suddenly out of the money and one wouldn't convert; thus, the timing of WHEN the PV is as of (in this case, years later) is critical - can one be absoultely sure that the price would go up in five years? ten years? most people would say yes, and we would disagree - case in point is WMT (wal-mart), the stock price has been stagnate and sideways for the past 5-7 years. Thus, it is our opinion that it is NOT guaranteed that a rationale investor would necessarily convert given the specific information supplied for Costco at that given time. Finally, pls note that the CFA is a great credential to have, but is still based on the textbook and not applied in real life. How come capital leases are excluded when you said include them? Full Question: When COSTCO's debt is being calculated, long-term debt and current portion of long-term debt from latest 10Q are simply added. However, long-term debt includes capital leases and there was no adjustment for that. Is there a special reason for not deducting the capital leases in case of COSTCO? WST Expert Response: Normally, per our instructions in the video, one would back out capital leases from the debt; however, because Costco's 10Q did not supply a full debt footnote in which we could have extracted such capital leases figures, we cannot just "make it up". One could argue to get it from Costco's 10K which might approximate the capital leases figure from 6 months prior (or 3 or 9 months prior depending on which 10Q), however, it is a worse sin to mix and match numbers from different time periods.

FINANCIAL MODELING QUESTIONS I have a circular reference problem in my model due to interest. Help! Full Question: I have created an interest tab where I would like to derive my interest expense and debt amortization schedule. I would like both of these to feed into the income statement and balance sheet in financials tab. In the debt amortization schedule I would like to derive the cash flow sweep from the cash flow statement. There is a circular reference problem, and I can sort of see why this is happening, but I am wondering how you would go about creating the model to simplify this. WST Expert Response: In general, circular references should be avoided whenever possible; however, you will legitly need a circ when calculating interest expense off average balance. (see our website and click on FREE RESOURCES for an exhibit on circular reference and Excel iterations). The workaround is to calculate off beginning balance and have a toggle to use average balance (since avg balance gives more specific numbers). Once you are on beginning balance, you shouldn't have circular references and if you do, then something is still mis-coded! How do I model out zero-coupon accretion / PIKs? Full Question: I have one question on modeling out a company with discount notes that accrete over the forecast period. I have already separated the different debt tranches. But I find that every time I include the accretion for the notes and then add back the after-tax non-cash interest expense to the cash flow, the balance sheet becomes unbalanced. Do you have any suggestions? Any help you could provide would be most appreciated. Thanks. WST Expert Response: To answer your question on the best practices of treating discounted notes that accrete: you do have the right approach to a discount bond, which would also apply in the case of PIKs: 1) Increase Interest Expense on the Income Statement (via the Interest Schedule) by the amount of the accretion. 2) Increase the Debt Balance for that particular tranche on the Debt Schedule. You can do this by adding a new line called "Interest Accretion" or "PIK". Either way, it increases the ending balance for that debt tranche. Repayments and borrowings should be kept separate for transparency. 3) Since this is a non-cash interest, you would increase CFF by the amount of the interest accreted or the PIK. This positive cash flow typically goes to CFF since it's considered a financing decision and not under CFO. If you incorporate all three, then you should be balancing and all set! How come my accretion/dilution model doesn't foot with reported numbers? Full Question: Why is it that in attempting to replicate a merger analysis of an announced deal, an accretion/dilution merger model does not foot to companies' presentation materials even after including all relevant data items? WST Expert Response: Remember, an accretion/dilution model is a back-of-the-envelope analysis that encapsulates the *major* components and drivers of value in a deal. Therefore, it would be nearly impossible to replicate a real deal's numbers since the bankers would be building a super complex, fully integrated merger model (well they should be anyway!). Such a model incorporates the true cash flow projections of debt paydowns, balance sheet synergies (completely unrelated to announced synergies) and a multitude of additional assumptions. The accretion/dilution model typically would serve as a quick and dirty analysis to determine if it's feasible to pursue down the path of additional and further complication of blowing out the model for much more precision. Circular references clarified Full Question: My model calculated, but I had a question about the final (key) point surrounding the iterations. I understand that we entered a choose function which will alternate using the average cash balances (2) or the beginning value (1). I also understand how to set up and run the iterations – my questions were: 1) how the iterations make the model equate such that the diffs are $0. Is it changing anything besides the cash value (I know the choose function was directed towards the type of cash bal to use but I wanted to confirm that nothing else was being modified when going through the iterations 2) when to use the beginning (1) verse the average (2) cash balances. I thought I heard you say that it should be set to the beginning balance initially and than turned to 2 such that it uses the avg balance for the iterations and than turned back to 1 once the iterations are complete and there are no differences remaining in the model… WST Expert Response: Your question can be summarized as follows: What is a circular reference, how do you avoid circular references in a financial model and once you have a circular reference, what does the Iterations option in Excel do? The logic runs as follows: In general, there is NO good reason to have a circular reference ("circ")- a logic that relies on itself to calcuate itself. The only legitimate reason to have a circ is in the case of getting a better, more precise financial model based on average debt and cash balances (see our FREE RESOURCES section and click on Circular Reference for more information). I need to note that for certain complex calculations and situations, there are a handful of reasons why a circ is required. However, aside from those very limited reasons (certain return calculations, certain share repurchase estimates, self-referencing cells for souped up sensitivity analyses, etc), circs should be avoided at almost all costs. Incorporating a circ for the purposes of calculating average balance is fine; however, once you do so, if a new circ is created (again, circs are bad and should be avoided), you will never tell that the new, bad, circ is there. Hence, my strong recommendation to build a simple choose statement switch that toggles between Beginning Balance (no circ since based off last year's figures) and Average Balance (yes circs, since requires this year's figure to calculate this year's figure). You should update and modify the financial model under the Beginning Balance scenario - this way, if a circ is accidentally created, you will either be warned by Excel or it will show in the Status Bar, indicating you have a circ. Unless you are on Average Balance, you should not have a circ! Then, when you are ready to finalize your model (or updates finished), then switch the toggle to Average Balance for a more precise calculation. Whenever you toggle back to Beginning Balance, no circs should exist. If there is still a circ, you have a "Fatal Error" in Excel. Whenever you have a legitimate circular reference, you need to tell Excel what to do - that is, turn on the Iterations option so Excel can calculate the numbers for you until there is no change in any number on the model. For more information on what Iterations in Excel does, see our FREE RESOURCES section and click on Excel Iterations Logic. How do I convert an annual projection model into a quarterly tracking model? Full Question: In my new job, where I follow the retail industry as a high-yield analyst, I have been told that I might want to prepare quarterly forecasts in order to gauge how a company is doing relative to my annual forecast. How do you suggest amending the forecast we prepared in class to prepare quarterly forecasts? Or do you even believe that quarterly forecasts are even the way to go, given that most sales are back loaded in the fourth quarter? If not, how would you suggest I gauge quarterly company performance against my annual forecast? WST Expert Response: If you are actively tracking an industry as a research analyst, it is absolutely critical to maintain a "tracking model" in which you update quarterly results. your model must be flexible enough to handle the periodic updates from the company. regardless of the cyclicality of the business (which in the retail space, is obviously cyclical), you need a quarterly model. To expand the projection model from an annual model to a quarterly model, it really isn't difficult: - increase the number of periods on the right by selecting the last full year and Ctrl+R to however many additional periods you want - change the labels from annual to Q1, Q2, Q3, Q4 and add a new row on top to indicate the Years - decide if you want to put Full Year right after Q4 or summarized to the right of your model, really depends on your preference. obviously Full Year will be sum of the applicable four quarters - instead of sequential Year-over-Year growth rates, use Quarter-over-Quarter growth rates - change the Balance Sheet ratios from 365 days to the correct number of days in the quarter. use date arithmetic in Excel to automatically calculate the number of days in a row that is white to avoid printing and then reference the number of days to that row - update historical inputs with quarterly instead of annual figures - don't forget to update the labels to the correct quarter - since you are specifically following high-yield, your ratios will be off the full year results. it may not be a bad idea to have an annualized ratio each quarter, although this is less meaningful due to cyclicality Additional Note: Unfortunately, while quarterly results can easily be converted to annual, quarterly results cannot be imputed from your annual totals. In order to build a quarterly projection model you need to input historical quarterly results (we would suggest at least 3 years) and based on your knowledge of the company, historical evidence of seasonality, etc., you have to forecast future quarters based on Q/Q growth rates, margins, etc. How do I account for stub periods in an LBO model? Full Question: If an LBO transaction does not happen at the end of the Pro Forma fiscal year (ie, 2004) but, say, at the end of Q2 2005, how can I account for this? WST Expert Response: In general, financial models are good for a particular point in time, as these are not tracking models like quarterly Equity Research models but rather, transaction models. To adjust the model from end of 2004 to Q2 2005, you would have to build in a stub period. The stub period would reflect the 2nd half of the year's earnings instead of full year. Likewise, the balance sheet would be updated for Q2 2005 numbers instead of year end 2004. How does one correctly calculate Days Payable Outstanding? Full Question: I took your advanced financial modeling class yesterday and have a question for you regarding the DPO (Days Payable Outstanding) formula. The formula we used was AP * (365/total expenses). I thought DPO used COGS to calculate. How come we’re using total expenses instead? Is it something specific to the way WMT does business ? Or am I just totally off? WST Expert Response: You are absolutely correct that the traditional formula for DPO uses COGS. However, that's straight from the textbooks and assumes that AP is trade payables. The vast majority of financials in 10Ks are presented with AP inclusive of trade payables as well as other acccrued expenses and liabilities and thus, COGS + SG&A might be more appropriate in such circumstances, as with WMT. As caveated in class, it wouldn't be unreasonable to back out D&A from COGS+SG&A sinec D&A doesn't result in/contribute to AP. Therefore, I would treat this is the general rule, not the exception. If you plow thru WMT's financials, you'll see an accrued liabilities line on the balance sheet that at first blush might run contrary to what I just said, but upon further inspection, that accrued liabilities is actually deferred, unearned SAM'S CLUB membership fees as well as hedging instruments with unrealized gains. How do I simplify sensitivity analysis in my financial model? Full Question: we are constantly setting up models and running different case scenarios based on variable inputs we create in our models. Often times we need to summarize these cases onto one table and because the models are complex and we change more than one variable at a time it sometimes means cutting and pasting the result--this however usually creates a fixed summary table of the results---and that's a problem because there is always a change that needs to be made to at least one of the cases and now we have a fixed summary table. I was thinking that a pivot table might be helpful but then again it may not flow with all the variable input changes we make---because I am a little rusty on pivot tables but I recall they only work with fix data. So I was looking at your Advance Excel for Data Analysis and Macros class and thinking this could help---or perhaps anther class you teach might better address this problem---or maybe there is no easy way to address this problem. Are there any classes you are instructing right now that would address this issue? WST Expert Response: Regarding your question on the sensitivity - this is actually something that is covered in detail in our Advanced Excel for Data Analysis class! You basically want to create an inputs page of scenarios that list all the different variables. Then using the choose function, you set the case and the actual projection model grabs the correct scenario that you specified. Then you run a data table (sensitivity analysis) on the case number to view whatever output you want (whether it's profit or returns, etc). You definitely would not need a pivot table for what you are describing. Does it make sense to forecast working capital off future quarters' sales? Full Question: When forecasting working capital levels on a quarterly basis, can I assume that the best approach, given the seasonality of retail stores, is to base my inventory/payables forecasts on that same quarter’s sales? Alternatively, would it be better to base my current quarter inventory/payables levels on future forecasted quarterly sales, given the lead-time needed to build inventory before increased seasonal sales. WST Expert Response: Your logic isn't a bad one - to base days outstanding off future forecasted quarterly sales. However, you will find that if you do that, you will create circular references. Any difference between using same quarter vs future quarter sales will be minimal unless you are a huge growth company. The additional savings in finer precision wouldn't be worth the trade-off and would be easily overshadowed via a cahnge in growth rates. Why don't we amortize debt out month by month instead of annually? Full Question: Why don't you amortize the debt out, month-by-month, to get the estimated interest and principal paydown (as opposed to using a simple interest rate times the beginning or average balance)? WST Expert Response: You could - but since we built an annual model, we use annual numbers. If you want to get extremely technical and precise, you would model that out, but since this isn't an LBO where you need extremely precise numbers, the additional precision gained isn't worth it, since it would only translate to a small difference in interest expense. But you have the right idea. What if discretionary debt sweep is positive? Full Question: In calculating Discretionary Sweep for WMT, the formula had: -Min(Beginning Balance Revolver, Cash Flow before Discretionary Debt Repayment). In this case, Cash Flow before Discretionary Debt Repayment was negative and hence Discretionary Sweep became a positive number. I just completed another analysis and Discretionary Debt Repayment is positive. In that case, will the formula be: -Max(Beginning Balance Revolver, Cash Flow before Discretionary Debt Repayment)? WST Expert Response: No, the formula stays the same. Never changes. The excess cash that is built is naturally captured through the cash flow statement. You do not want to pay down revolver past negative and no need to borrow either! How do I account for Discontinued Operations on the financial statements? Full Question: How do you typically account for Discontinued Operations in the BS, IS and CF? With regards to the BS: I am looking at the 3Q2006 results of WMT and reported the affect on the Cash & Equivalents and Current Assets of Discontined Operations, but have not detailed the affects on the Liablilies and Shareholders EQ and I am having a hard time balancing the BS. With regards to the IS: Mgmt has not indicated how much the discontinued operations accounts for in revenue. WST Expert Response: As you know, typically, we would ignore the effect of Discontinued Operations in our projections. However, for recently announced Discontinued Operations such as WMT's Germany operations, you would need the segment breakdown of the assets and liabilities or they'd have to disclose it to you. In short, you can't really do much, you'd have to make estimates based on prior period. You would have to make guestimates based on press releases and prior historical data. How to project minority interest? Full Question: I'm working on a company called Central European Media Enterprises and on the balance sheet there is minority interest. I know in the video we did not project that account because we did not have enough information. However, I do have quite a bit of information on the ownership interests of CME, should I project Minority Interest and if so, how? WST Expert Response: For a quick and dirty minority interest estimate on the balance sheet, you would take the proportionate share of what CME does NOT own and recognize that as the MI liability on the balance sheet. In other words, you would build a separate standalone model for the subsidiary (which is 100% consolidated into the parent company, or CME) and then the minority interest liability (whatever %age not owned by CME) is recognized as a liability, effectively, somewhat akin to the Equity portion. Follow-up Question: Should net income always be net of minority interest? And should we caluclate earning/share based on earning net or minority interest? Response: This would be guided by Accounting financial statements (ie FASB/IFRS), meaning there is a clear black and white line, no gray line, as with many finance questions. Net Income is ALWAYS after the effects of Minority Interest. EPS is to be calculated off Net Income to Common Shareholders (so after Preferred Dividends, if any). The rationale is that Net Income is after everything in the company has been accounted for already and is truly what is "net" or left over to shareholders. How should I input the scheduled lease payment in the debt sweep? Full Question: How should I input the scheduled lease payment in the page of debt sweep of your spreadsheet? Should I input the scheduled payment of each year and leave interest rate zero? WST Expert Response: We would decrease capital lease on the balance sheet and put that in the cash flow statement just like working capital. In theory you have to account for imputed interest and depreciation. Modeling out working capital Full Question: For change in working capital, you use the change in operating expenses. Normally, and what I’ve been taught, is that you use current assets minus current liabilities. Can you explain to me why you would use the change in operating expenses? WST Expert Response: Normally with a derivation of the full balance sheet, yes, assets less laibilities. But 30/60 days expenses is not an uncommon estimate for cash flow based business (services firms) since you need to maintain that to pay salary, etc. How do I model out pension expenses on the cash flow statement? Full Question: I am building an IFRS full model on a UK company at the moment and need to ask soemthing regarding the Cash Flow statement. Would you include payments to the Company Pension Fund as a negative item in calculations for the Cash Flow from Operations ? It seems many of the sell side reports remove this item thus flattering the Free Cash number at the end but the company itself does not in their consolidated accounts. WST Expert Response: Recall that pension expense is technically included in Compensation (usually part of SG&A). That does not necessarily mean they physically paid cash for that expense in that time period (matching principle of expenses, subset of accrual accounting). Therefore, the contribution to pension would be a working capital figure. Thus, if you were to include payments to Company Pension Fund as a negative item in CFO, that would imply that they paid MORE than the accrual expense amount recognized on the Income Statement. However, keep in mind that the CFO figures are partly derived also from the BS figures - that is, if there is an actuarial change in pension assets, then that change is reflected in CFO and possibly further distorting the true cash changes to pension. In short, you need to figure out exactly what is going on with the company and then model that out as close to reality as you can. Follow-up Question: Thanks. The company have an item in the working capital section of the CF statement "payment to Pension Fund" of negative GBP100m. Response: Is the CF statement direct or indirect method? if direct method, then that means they paid GBP100, if indirect that means they paid GBP100 MORE THAN whatever was recognized as an expense, exclusive of adjustments.

M&A and LBO MODELING QUESTIONS * Why does an LBO set the floor valuation? Full Question: Why does an LBO set the floor valuation? WST Expert Response: LBOs set the floor valuation because in theory, anybody could buy another firm (in an M&A transaction) and make it highly levered but in such a deal, there would be 'synergies' and as such, if you do anything 'value added' on top of an LBO, one should extract additional value above and beyond a 'simple' re-engineering of the capital structure. * How do you determine reasonable debt covenants for an LBO deal? Full Question: How do you determine reasonable debt covenants for an LBO deal WST Expert Response: Debt covenants are determined by the Company’s industry. In the past few years, LBO’s have increasingly been structured with covenant-lite debt, meaning that they have fewer requirements and restrictions than traditional debt structure. However, as debt markets substantially decline (as in 2007 Q3), investors have lost their appetite for covenant-lite debt. Before modeling a potential LBO transaction, discuss with debt capital markets what the appropriate covenants for a deal are and the maximum debt to EBITDA ratio appropriate for the particular industry. * What is the rationale for using PIK instruments in a LBO transaction? Full Question: What is the rationale for using PIK instruments in a LBO transaction? WST Expert Response: In an LBO transaction, if the Company has insufficient cash they may have the ability to utilize a PIK instrument (payment-in-kind). Essentially, PIKs defer cash interest payments to the future. The downside to PIKs is that interest accretes and compounds over time, which increases the overall interest the Company pays on its debt. PIKs may also involve an “equity kicker” for the lender. Instead of deferring the interest payments, the Company can issue warrants, which effectively dilute existing shareholders. * What is the rationale for using different debt securities to finance an LBO? Full Question: What is the rationale for using senior discount notes vs senior notes vs term loan vs credit facility vs cash vs PIK preferred stock WST Expert Response: Term loans (aka bank debt) are less expensive for a Company because they require a lower interest rate. However, they require regular mandatory paydowns, which decreases the Company’s available cash. Senior notes and senior discount notes are advantageous in that you defer paying them back until maturity. However, the interest rate on both of these instruments is higher than bank debt. * How do you properly amortize debt items / paydown of debt? Full Question: How do you determine the amortization of debt items / paydown of debt for individual debt items? WST Expert Response: Amortization of financing fees is determined by the number of years to maturity/paydown of the particular debt instrument. Amortization is calculated according to the matching principle of accounting, i.e. a 6 year term loan is amortized over a 6 year period while 7 year senior notes are amortized over a 7 year period. * What balances the LBO model in the Sources & Uses of Funds? Full Question: What balances the LBO model in the Sources & Uses of Funds? WST Expert Response: Two methods are used to balance the LBO model. The first and preferred method is by using the amount of equity in the sources section of the model as a variable. This allows you to maximize the amount of debt used in the transaction, thus increasing the rate of return for the entity providing equity in the transaction. The second method is to set a specific amount of equity contribution from the private investor and use the revolver to balance the model. This latter method is less ideal since you may not be fully maximizing the debt capacity which is the point of an LBO to begin with (lever up with debt). * How do you treat NOLs in a M&A deal? Full Question: What is the proper treatment of NOL carryforwards in a M&A deal for the acquiring company? WST Expert Response: This is perhaps best illustrated through an example – Company ABC is acquiring Company XYZ. Company XYZ has $50 million of NOL carryforwards. The calculation for the annual NOL allowance that Company ABC will be able to reduce Pre-Tax Income by following any 'change of ownership' of Company XYZ (per Section 382 of IRS tax code) is as follows: Fair market value of target equity value multiplied by the 'tax-exempt rate' (a rate published monthly by the IRS and is also known as long term tax-exempt rate). The advantage for Company ABC to acquire Company XYZ with an NOL carryforward is that the NOL will shelter a greater percentage of ABC’s Pre-Tax Income and lower the effective tax rate of NewCo in the future. If the NOL is significant however, the present value of the NOL will errode some of the tax savings. FMV of Target Equity is Purchase Price (including premium) since that is the new value of the target once acquired. However, for LBOs, is the amount of Net Equity, thus, it is private equity sponsor equity contribution. * How to you treat of amortization of intangibles and taxation? Full Question: What is the correct treatment of amortization of intangibles and taxation in a M&A deal? WST Expert Response: In a plain vanilla stock deal, the Acquiror is not able to amortize the goodwill (excess of equity purchase price over book value). In an asset purchase, the Acquiror is able to depreciate/amortize the newly acquired assets which lowers the Acquiror’s Taxable Income. In a 338(h)(10) election, a Stock deal is treated like an Asset deal for tax purposes, thus, not requiring amortization of goodwill (FASB 142 / IFRS 3) but DOES receive a tax deduction, as the goodwill is amortized for tax purposes, lowering effective tax rate. * Explain the difference between Stock vs Asset deals. Full Question: What are the different treatment of Stock vs Asset sales and how does it affect the M&A model? WST Expert Response: In a stock sale, the acquiror purchases the stock of the Company, thus purchasing the entire company (all of the assets, liabilities and future contingencies). In an asset sale, the buyer only purchases certain assets and liabilities of the target company. Thus, in an asset sale, the acquiror is not responsible for liabilities incurred in the past but in a stock sale the acquiror is responsible for all liabilities, past and present, since they own the entire company. In a stock sale, the seller is taxed at the capital gains rate and the buyer does not experience a step-up in tax basis. In an asset sale, the seller is potentially double-taxed for both the appreciation on the assets and capital gains on the distribution of proceeds. Also, the buyer receives a step-up in tax basis and can depreciate/amortize the newly acquired assets. * Explain 338(h)10 elections and the impact on a M&A deal. Full Question: Explain 338(h)10 elections and the impact on a M&A deal. WST Expert Response: In a 338(h)(10) election, the transaction is a stock acquisition from a legal perspective but is treated like an asset sale for tax purposes. Thus, the acquiror can depreciate/amortize the full purchase price and experience a step-up in tax basis. The acquiror can also utilize any NOLs to shelter against any gains on the sale. Under certain scenarios, however, (Target has been an S-Corp for more than 10 years or is a subsidiary of a company) the seller does not experience double taxation. A number of conditions must be met in order to qualify for a 338(h)(10) election. One condition required is that the acquirer must purchase 80% or more of the target company’s stock. * How do you build in divestitures / future acquisitions / stock buybacks? Full Question: How do you build in divestitures / future acquisitions / stock buybacks in a M&A model? WST Expert Response: The following are the implications involved in each of the 3 scenarios: (i) Divestiture – Depending on whether it is a stock or asset sale will determine what the tax implications are to determine the net proceeds received by the selling company. The net proceeds will impact the financial statements in different ways depending on the consideration received. For example, there will be an extraordinary item in the seller’s P&L to show the one-time sale which will drop to the bottom line. Depending on whether cash or stock is received will determine how the cash flow statement and balance sheet will be impacted. (ii) Future Acquisitions – Similar implications as a divestiture. In a future acquisition you would combine both companies P&Ls and Balance Sheets on a pro forma basis. (iii) Stock buyback – To build a stock buyback into the model you will need to assume a stock price in the future that stock will be bought at based on an assumed P/E ratio. Then you need to decide what percentage of shares outstanding you want to buy back. A stock buyback affects a Company’s P&L in the following ways – increase in interest expense (if debt is borrowed to buy back stock), increase in interest income (if cash is used to buyback stock), increase or decrease in taxes depending on your interest adjustment, decrease in net income and decrease in shares outstanding. The buyback will be accretive if the incremental decrease in shares outstanding is larger in proportion than the decrease in net income on a pro forma basis. The balance sheet implications are the following – decrease cash (if cash used to buyback stock), increase debt (if debt used to buyback stock) and decrease shareholder’s equity. * How do you maximize earnings accretion? Full Question: What are different ways to structure a transaction in view of maximizing earnings accretion? WST Expert Response: Aside from the favorite answer - SYNERGIES, there are several issues to consider when structuring a transaction to maximize earnings accretion. The first question is to whether to pay with stock, cash or a combination of the two. The next question is what are the relative P/E ratios of the Acquiror and the Target and what is the implied P/E of debt for the Acquiror? If the Acquiror’s Price-to-Earnings (P/E) ratio is 40x, the Target’s P/E ratio is 20x, and the Acquiror’s implied P/E of debt is 25x it would most likely make sense to structure the transaction largely with equity consideration because the Target will contribute more Net Income relative to its purchase price. However, if the Acquiror’s P/E ratio is 25x or lower, the transaction would make more sense with a large debt (cash) component and less stock to maximize earnings accretion. In most situations, other than when the Acquiror has a substantially larger P/E than the Target, the transaction will be structured with a large percentage of debt consideration to maximize earnings accretion. However, there are limitations as to how much incremental debt the Acquiror can put on its balance sheet. The logic lies in the fact that everything, including capital, has an opportunity cost. Unfortunately, it boils down to a simpel P/E game. For more detailed information and explanation, refer to our Merger Modeling Basics course or online video. * How can a Company with little cash do an all-cash deal? Full Question: What is the feasibility of all-cash transaction when the Company has little cash on Balance Sheet (determine borrowing capability of Company)? WST Expert Response: If Company XYZ has minimal cash on its balance sheet but is looking to make an all-cash acquisition they can borrow the cash by taking on debt. The amount of debt that they are able to borrow depends on how much debt they currently have on the balance sheet and at what point will the Company max out its total debt borrowing. Maximum debt capacities of different companies are usually determined by the specific industry’s typical debt requirements. Certain industries with high, steady cash flows can take on a higher level of debt and thus have a higher Debt/EBITDA ratio than cyclical industries with uncertain cash flows. This is not to be confused with LBOs. LBO Deal Structuring and Capital Lease Treatment Discussion Thread Full Question: LBO Deal Structuring and Capital Lease Treatment Discussion Thread WST Expert Response: QUESTION: RE: LBO modeling: when a company you’re analyzing in order to do an LBO has capital leases on its balance sheet, what’s the proper way to reflect that in the sources and uses? That is: let’s assume they have $20mm of capital leases on the balance sheet right now (IE pre transaction). If we’re going to keep their capital leases outstanding post-LBO, should our LBO sources have a line item called “Assume capital leases : $20mm”, so that our sources and uses balance? and then, post-transaction, we’ll have a Capital leases line item on the balance sheet, as well as in our debt schedule. RESPONSE: Are they buying out the cap leases? If not, then it doesn't come into the picture at all. QUESTION: Let's assume they're leaving the cap leases in place. So are you saying that the sources and uses will not show the cap leases at all?!?! If that's the case, then when I calculate the LBO entry multiple, I'll have to add cap leases to the financing sources, in order to accurately calculate the LBO's TEV/EBITDA multiple, right? RESPONSE: Remember for standalone valuation, cap leases not included. QUESTION: Okay - but lets assume the MD wants to show cap leases as part of the entry LBO multiple. Would the correct way to model it be: show the cap leases in the uses as "Capital Leases assumed" and show them in the sources as "capital leases assumed"? RESPONSE: No keep it simple. Just say Debt/EBITDA of 6x (or whatever). Then in debt capacity say: Debt + Cap leases = Total debt Then total x multiple is new capacity. No need to have cap leases in S&U QUESTION: Good stuff. To be clear: you're saying "Look, Sources and Uses are just about what are you buying, and what are you using to buy it. TEV multiples are a totally different concept, in which you say: how much debt am I raising + debt already outstanding on the company + equity paid - cash on the balance sheet right after the transaction occurs = TEV, then divide that by 2007E EBITDA, and there's your entry multiple." RESPONSE: Yes, correct. However you asked abt leverage - so, Debt/EBITDA and the question is, debt includes cap leases not part of S&U. QUESTION: Understood. And, the TEV for an LBO should be total debt + total preferred + minority interest - cash PRO FORMA FOR TRANSACTION, right? RESPONSE: Well, supposedly its always "excess cash" but rarely is that additional adjustment made. So do whatever ur vp/md wants on that - minor point anyways. The key is not multiples but S&U. QUESTION: Btw, I noticed that the JCP LBO model we did in training does not have an Asset Writeup assumption cell. I thought that goodwill = equity purchase price + M&A fees - (asset writeup + tangible net book value), no? RESPONSE: Correct - we did FMV step up in super-advanced merger not JCP. Why? Bc once LBO, its not technically GAAP necessary. When it IPOs later, whole new financials anwyay. QUESTION: Understood, but doesn't the asset writeup generate incremental depreciation expense, which is tax deductible? IE wouldn't we want to model that incremental depreciation out, to calculate a lower income tax bill? RESPONSE: GAAP FMV writeup is independent of tax writeoff. Hence a large deferred tax liability is created to match the difference. The explanation is much longer. So the short answer is, nope! Longer answer - this could be a good continuing education topic for a luncheon discussion. QUESTION: 1. is amoritzation of intangibles tax deductible? 2. is interest expense on capital leases tax deductible? 3. if a company has capital leases, but does not show interest expense in its internal company model, where else could they be showing the interest expense on capital leases? RESPONSE: 1) amortization of IDENTIFIABLE intangibles is usually tax deductible 2) both interest expense and the depreciation on capital leases are tax deductible (legit expense!) 3) the capital lease expense could be burIEd within interest expense (IE debt) or depreciation. In an M&A, how do you treat liabilities on the balance sheet (as debt)? Full Question: In determining a total transaction value for an acquisition (Including the stock/cash offer, plus the assumption of long term liabilities and working capital), would all liabilities from the balance sheet be included? WST Expert Response: The answer is no - when we look at transaction value, we are trying to gauge financial value not operating related items. Long term liabilities and working capital and other non-financial securities are to be excluded. Deferred income taxes also fall in that category since that's not a capital structure decision, but rather operating related. ARO - that's trickier and really depends on the relative size vs. the balance sheet. Generally speaking, not included either; however, if significant impact, then yes - as to what that threshold is, well that's a judgement call then, but basically based on materiality. Follow-up Question: I think I remember from your course that they buyer’s cost for the acquisition would be the price they pay in terms of cash/stock/other, plus the absorption of any liabilities that must be absorbed (in the short term?) for the target to continue as on ongoing entity. Would this include the working capital deficit/surplus (including cash)? And also long-term debt? I assume deferred taxes would not because that has to do with timing/accounting. ARO is an accrual item and in theory the asset could be sold and retirement costs avoided? So possibly this would not be included in the total cost to the buyer? Response: When you look at "purchase price", working capital deficit/surplus as you described (absorption of net liabilities) is not in the calculation. However, it is typically a "purchase price adjustment" as term sheets will normally have a "minimum working capital requirement" stipulated clearly. For large public-public deals, it's not really an issue unless distressed situation. Long Term Debt would definitely be a part of purchase price (Enterprise Value) because you are basically acquiring the funds needed to run the business (Equity and Debt). ARO by definition technically cannot be sold since the asset has already been retired and either off the books or has zero value (rather no one would buy it). ARO is amortized and included in expenses and thus, reduces future earnings and cash flow so it's sort of embedded within the price because of the lower future earnings. On the flip side, for the sake of completeness, an ARO liability could be viewed as debt but again, that's a core business related activi3:28 PM 1/22/2008ty as opposed to capital structure. Insurance Company LBO Question Full Question: I am looking at the potential LBO of a small insurance company and have a question about how to model the transaction for purchase account purposes under GAAP and statutory accounting. Here are the assumptions: The firm has $106.7 MM of statutory assets, $52.4 liabilities, and $54.3 of statutory surplus at close. We buy the firm for $242 MM. THe consideration is comprised of a 38 MM senior loan and the remainder in preferred stock. This will have an holdco/opco structure where the debt will be held at the holdco level. How do I calculate the statutory surplus pro-forma for the transaction? I know this is a pretty specific question, but I can't seem to figure it out. I know on the GAAP balance sheet for the combined firm under the pro forma capital structure we will put up a large goodwill figure, but since goodwill is not a recognized asset on the statutory balance sheet, I don't know how to handle the purchase price consideration. Any help would be much appreciated. WST Expert Response: Typically, if holdco raises new capital inform of debt + equity, it would invest that capital directly downstream into insco. So, the total proceeds are treated as a contribution to statutory surplus at the subsidiary level. If it's an acquisition and there's no new cash created at holdco, then there's no effect on statutory surplus. It stays the same. Goodwill is not an admitted asset for stat purposes. In other words, all the purchase accounting is done at GAAP level on holdco and stat co is unaffected. Note - don't take this as gospel truth, but our belief is that there is no effect on the statutory balance sheet of the operating insurance company. Goodwill etc. is a GAAP construct - statutory accounting does not allow it as an admitted asset, so unless there is some revaluation of loss reserves or UPR or whatever, the stat balance sheets (and surplus) would be unchanged. Also, if you care about their Best ratings at the InsCo level at all, then it will be tough to finance the deal entirely with debt and preferreds. How to calculate / estimate transaction costs for an LBO? Full Question: I am doing a LBO analysis of a small retailing company. The transaction EV is about $350 million and new equity is $100 (my number). What is the appropriate transaction cost for this analysis? You mentioned that the M&A fee was around 1-2% of TEV. But I am not sure about the legal, debt financing and tender costs for this small transaction. Since all the cash on the B/S is used to finance the deal, what cash balance should I use in the pro-forma analysis? Does it matter if I don't have any cash balance at all? In your JC Penney example, you had 750 for cash balance in the pro-forma analysis. I don't know how you got this number. Thank you for your help. WST Expert Response: Debt financing fees range anywhere between 25bps/50bps for revolver facilities to as high as 1-2% for more junior debt. The amt of financing fees would be dependent on the specific tranches of debt raised. Likewise, tender costs for existing debt that is refinanced would vary based on the PV of future interest expense that the bondholder would no longer receive (map out future cash flows for each bond and PV back at coupon rate/YTM). Legal fees are fairly static (not really all that variable unless extremely complicated of a deal) - probably $500K to $1MM (but don't take that as the word). the $750 is an estimated number not derived from anywhere. it is partially based on historical cash balance and funds required for retailers to run their business. Does a 338(h)(10) election apply for a foreign (non-US) company? Full Question: I had a question re the 338(h)(10): is that something that only can be elected if both target and acquirer are US domiciled companies, or can a foreign buyer and/or foreign target do a 338(h)(10), as well? WST Expert Response: If a foreign corporation is a "qualifying foreign purchasing corporation" (see requirements under Treasury Regulation 1.338-2) and the target is a "qualifying foreign target", a Section 338 election can be made. Of course, it's only applicable to the extent that the Parent and/or target are subject to U.S. tax. In terms of the treatment of the purchase in the jurisdiction of the foreign parent or the foreign target, you'd have to look at the tax law of that particular jurisdiction to see if it has a provision comparable to Section 338. Also, to answer a previous training class question, FMV step-up is typically NOT tax deductible. The explanation is actually quite longer but that's the short answer. So yes, u get screwed on taxes AND its worse - you report lower profits bc of FMW step-up. How to set up Sources & Uses for <100% LBO? Full Question: What is the proper way to set up sources and uses for an LBO where the sponsor is acquiring less than 100% of the target? I have typically included lines for roll-over equity in both the S&U in order to get the value of the total equity (rollover and purchased) from the sources to equal the equity value as calculated from an assumed entry EV/EBITDA multiple and the current net cash balance ([Assumed entry multiple]*EBITDA=EV+cash-debt=Equity Value). Is this best practice? WST Expert Response: Yes and no. If less than 100% (assuming greater than 50&) then sources and uses should match true in and outflow of $$ by "grossing it up" the way you described, you are effectively creating a "fake" transaction. You are not technically incorrect (hence the "yes") but we wouldn't consider that a best practice (the "no" part). Instead we wold have the %age of equity value flow thru s&u as it is actually happening. In your irr you adjust for the buyer/investor's %age ownership. Our Complex LBO online course covers this very neatly. Private Company M&A and LBO adjustments Full Question: In the Core Merger Modelling Topics module, the models you build concern public listed companies either as acquirors or as targets. What are the effects on a model when the target is a private (not listed company) or a Business Unit that a corporation wants to dispose. For example let's assume the target is a 100% subsidiary of a large corporation. The large corporation wants to dispose this subsidiary and it is the perfect LBO candidate. How are we going to treat this transaction? In any calculation concerning Equity are we going to use the number of shares with their nominal value? i.e the target has 100k shares with a nominal value of $5. Will the premium be calculated on the nominal value? What will happen in the case where a large corporation wants to divest from a B.U which has no legal entity form. How do we treat such transaction from a model point of view, when we have no equity. It woulD be great if you could explain one LBO and one merger example. Thank you in advance for your prompt response. WST Expert Response: The ramifications for a private company vs a public company are very similar - the major difference is that there is no EPS and Shares Outstanding. Thus, to "fudge it", you could assume that there is one Share Outstanding or, better yet, to be more precise, you would use Net Income => so instead of P/E, you would use Equity Value / Net Income per the courses. In addition, for an LBO of a private company or a subsidiary (treated the same), the biggest change would be to drive the value of the offer price to be based on TEV/EBITDA as opposed to percent premium over current stock price (which doesn't exist). Everything else stays the same => you would still have TEV and Equity Value, just not Stock Price. Thus, there is no concept of "number of shares" or "nominal value"; you simply use TEV as the starting point and work the opposite direction on the capital structure. For a large corporation wishing to divest a business unit with no legal entity, the parent company would have to first create a legal entity or structure it as an asset sale. There is never "no equity" - there is always a cost basis somewhere (both GAAP equivalent of Shareholders' Equity and tax basis for tax treatment). How do you calculate leveraged-adjusted exchange ratios in M&A stock deals? Full Question: I would like to know how to correctly estimate exchange ratios in case of all-stock-transactions based on leverage-adjusted EBITDA, TEV, Equity Value, and Earnings contribution analysis. Think an adjustment needs to be made to account for the difference in capital structure of both companies. WST Expert Response: Traditionally these are used purely to evaluate stock vs stock, so it's not been practice to leverage-adjust. however, you could by simply using TEV, EBITDA, Equity Value, etc for both a contribution and exchange ratio. Exchange Ratios are most helpful over a time period rather than static, for comparison purposes.

EXCEL QUESTIONS How do I access the file location for auto-starting macro toolbar? Full Question: I do not understand how to save the macro toolbar file into the assigned folder (C:\Documents and Settings\<USER NAME>\Application Data\Microsoft\Excel\XLSTART) WST Expert Response: You may not see this directory because it is a hidden system folder that you need to unhide. Follow the directions for unhiding system folders - in Windows Explorer, go to TOOLS => FOLDER OPTIONS => VIEW => Hidden Files and folders => Show hidden files and folders. You should be able to locate that folder now. You can also access our FREE RESOURCES section for a detailed tutorial with screen captures. As an alternative, you can right click on the START MENU button your Windows taskbar and click on Explore and then scroll up to Application Data, etc. - a bit faster. How do I assign a shortcut key to a previously created macro? Full Question: How do I add the keystroke shortcut if I have already initially bypassed the prompting to do so when you recording the macro? WST Expert Response: To add a shortcut key to a macro, you can hit ALT+F8 to bring up the macro dialog box and then select your macro and click on Options and it should be straightfwd from there. Don't forget to save your macro file. How to I automate blue and black cells for inputs/outputs? Full Question: I was wondering how to build a macro for formulas and constants to be black and blue. WST Expert Response: You cannot automate the color changing as you type a number/formula without building a macro to specifically do so. What you can do is to have excel highlight the cells on the current worksheet and then change the colors blue (if you selected "constants") and black (if you selected "formulas"). This doesn't require the use of a macro, although you can build a macro so you don't have to keep hitting the various options. As you build additional analysis or worksheets in your model, you would do the same - follow the instructions on our Excel shortcut sheet under our FREE RESOURCES section or download and install our WST Macro Add-Ins. How do I get the file name automatically in a given cell? Full Question: Is there a formula or command that will state the file name you are currently working on in a cell? We wanted to have the title automatically update with the version we were working on. Thank you. WST Expert Response: Use this on a file that already has been saved (as opposed to a brand new file) Insert this in any cell: =CELL("filename") However, that gives the full path and tab name. to just get the file name without the full path or tab name, use this formula: =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1) To also remove the .xls extension (no full path nor tab name), use this formula: =MID(CELL("filename"),FIND("[",CELL("filename"),1)+1,FIND("]",CELL("filename"),1)-FIND("[",CELL("filename"),1)-5) What is the keyboard command for the right mouse click? Full Question: What is the keyboard command for the right mouse click? WST Expert Response: To right click, you can click on the right context menu button which usually is next to the right ALT key on a standard keyboard. It has a drop down menu with an arrow on it. Many laptops also have this key but may not be next to the right ALT key all the time. (IBM Thinkpads don't have this key). Alternatively, you may click on SHIFT+F10 which is a Windows command so should work on most Windows-based and Office applications. How do I shade every 3rd row instead of alternate rows? Full Question: I had a follow-up question to the auto row shading taught in the Advanced Excel for Data Analysis class - what would the formula be if I wanted every 3, (or 4 or 5) rows colored, rather than every other row? WST Expert Response: Fairly straight forward - the trick to all of this is simply figuring out the mathematical equation. Instead of this formula as instructed in class: =ROW()-EVEN(ROW()) Try this formula instead =MOD(ROW(),3)=0 MOD provides the remainder of a division equation and you are looking for every third row. This somewhat depends on what row number your data set begins. You can tweak the =0 to =1 or =2 depending on the starting row and exactly which row you want to shade. Once you play around with it, you'll see what I mean. Obviously to make it every 4 or 5 rows, you change the 3 in the formula to 4 or 5 as appropriate, etc. How do I create a long underline? Full Question: How do I create a long underline? WST Expert Response: Install our free macros add-in in our FREE RESOURCES section. Under WST => Borders menu, Ctrl + Shift + A, but you must have pre-formatted the cell using our custom formatting first! How do I select two or more non-adjacent cells and quicky get the sum? Full Question: Is there any way to highlight two or more NON-ADJACENT cells to make formatting changes to? Also, would like to highlight two non-adjacent cells to get a quick sum of the values. WST Expert Response: To highlight non-adjacent cells, use mouse - it's quicker. One could write a macro, but not worth it. When you have more than 1 cell (whether adjacent or not adjacent) selected, see the bottom right of your status bar. The sum should appear. How do I create a data table? - and limitations Full Question: What is the [=TABLE] function that I see and how do I re-create this "data table" WST Expert Response: Data tables in Excel allow you to quickly build sensitivity analysis by outputting 1 or 2 output cells by changing 1 or 2 input cells. For Data Table with One Input: - Select from E12 to G17 (entire range of cells, including the header cells) - Assuming input variables go down the COLUMN, enter C13 as COLUMN INPUT CELL (or whatever cell the input cells replace) - hit OK, you may need to hit F9 to refresh For Data Table with Two Inputs: - Select from H20 to M25 (entire range of cells, including the header cells) - Input ""a"" is across the ROW so D21 is ROW INPUT (same concept as above) - Input ""b"" is down the COLUMN so D22 is COLUMN INPUT (same concept as above) - hit OK, you may need to hit F9 to refresh If you get the same number in your data table, don't forget the Re-Calc step: Tools => Options => Calculation => set to Automatic not Automatic except tables! Follow-on Question: I have made a model on one worksheet, and have made a data table using that model on the same worksheet. Now, I want to make the same data table, but on a different worksheet. When I construct (not copy) the same data table on a different worksheet, I get an error that says “Input cell reference is not valid.” Note, when defining the input cell reference, I am changing worksheets to the one where my income statement is, and then selecting the appropriate cell. The only difference is the change in worksheets. Should it be possible to construct a data table using a model that is on a different worksheet? Response: One constraint to data tables, as you have discovered, is that the inputs to the data table and the data table itself must be on the same worksheet. The output cells of the data table can be from a different worksheet. Your work-around solution is to either put the data table on the same worksheet as your input tab and then create a separate output elsewhere or to have the inputs referenced from the same data table tab. Linking between files Full Question: Currently I am working on an independent workbook that is located on company's share drive, and I have a dependent workbook that is linked to the independent workbook, also on the share drive. Up till now that links has been working great however, I notice today that the link has been corrupted and somehow the dependent workbook has located another excel workbook in another location, on my C drive, rather than the orginal location on the share drive. The program is linking to a workbook in to a folder that doesn't exist on my c drive. I also notice that under the option where it ask "alt startup file location" is that same place where the new link is going to. Would someone please help me figure out how to fix the link problem. Thank you. WST Expert Response: short answer: don't link between files; this is a fatal error! medium length answer: go to EDIT => LINKS and click on Change Source (only later versions of Excel); be careful to check links are still valid in case independent source file was modified to add/delete rows/columns long answer: you probably opened the file from your email, so it got saved to a temporary folder; thus breaking the link. this is but one reason why links between files are very poor best practices, or rather, a best poor practice. Limitation on number or rows in excel 2003 Full Question: I have a question about the limitation of rows in excel 2003. I remember being informed that the limitation in excel 2003 was over 1 million rows or greater tahn 65,999 . When I tried to open a file with more than 66 thousands it seems I get an error. When I google the error it states that: "A Microsoft Office Excel worksheet contains 65,536 rows and 256 columns. You cannot increase the number of rows or columns beyond the maximum row and column limits." and the website that I obtained that info from is http://office.microsoft.com/en-us/excel/HA101375451033.aspx can someone clarify if there is a restriction to the number of rows in excel. Thank you. WST Expert Response: Thank you for your inquiry. in excel 2003 there are only 65,536 rows and 256 columns. you are thinking about excel 2007 in which there are over 1 million rows and 16,000+ columns. Stock Price Annotations in Excel charting Full Question: Helping someone work on a powerpoint project, and I was wondering if there is a better way (other than moving the arrow along the stock chart line) to link an arrow to a certain stock price on the graph.  I hope all is well, I have loaded your macros, and I have the shortcuts on my wall! WST Expert Response: Yes, take a look at the pv graph on the profile which we didn't get to. create a new series next to stock prices with just numbers filled in for the dates you want. Then chart type is line, formatted as no line, but with marker. Make the marker big enough and then manually point your arrow to the exact point, making sure to touch the line. Then when all done, remove marker. All set. There are ways to automate using custom developed macros and programs but short of that, that's the easiest, fastest and most accurate way. How do I group/ungroup cells? Full Question: I see in Wall Street sell-side models that "+"/"-" buttons ( near the row and column headings) and "1"/"2" buttons (in the upper left hand corner) are created to unhide/hide a bunch of columns/rows by simply clicking on the buttons. How do I create these buttons ? WST Expert Response: This is a result of "grouping cells" together. Please take a look at our excel shortcuts to group/ungroup (under FREE RESOURCES). Also, our macros use Ctrl+Shift+S to toggle expanding and collapsing to avoid using the mouse! Can you summarize the various ways to copy and paste efficiently in Excel? Full Question: The commands that move data/formats/formulas over such as Ctrl+R, highlighting and hitting Ctrl Enter, etc. I know some bring just the formula over and some bring the formula over as well as the formatting. Can you provide a summery of which provides which? WST Expert Response: 4:01 PM 1/22/2008 Copy / Paste => copies and pastes formulas/values, formatting and comments Ctrl + R / D => copies and pastes formulas/values and formatting don't forget about Ctrl + L and Ctrl + Shift + U with our macros Ctrl + Enter => copies and pastes formulas/values only Ctrl + ' => copies and pastes EXACT formula from cell on top don't forget about Ctrl + / and Ctrl + \ and Ctrl + | with our macros Is it possible to create a list of random numbers from -1 to 1 with a given standard deviation? Full Question: Is it possible to create a list of random numbers from -1 to 1 with a given standard deviation? WST Expert Response: To create a random number between -1 to 1, use the formula: =rand()*2-1 rand() generates a random number between 0 and 1 hence the need to modify the range. as for a given standard deviation, it is doubtful that can be done - that is to specify a standard deviation and have excel generate a list of numbers. what you could do is generate your list, use stdev() function and keep refreshing your list. then when you get close, manually add numbers to "fudge" it. don't forget to copy and paste as a value first else you lose your data. How do I move a row without the mouse? Full Question: I want a quick way to move entire row with no mouse.  I know to highlight and cut you would do Shift space bar -- then hit Ctrl + C. How do I put it in the proper place?  When I highlight the row in which i want my selection to go above, it simply copys over that row instead of inserting itself above it.  Is there an easier way than doing it via the mouse?  Thanks. WST Expert Response: After copying and going to where u want, u want to "insert copied cells". Thus right click using the right click button on the keyboard (next to alt on the bottom right) and hit "e". This inserts on top of your active cell. You must be on column A or highlight the row else excel says unequal size/shape. SUMIF problem Full Question: What am I doing wrong with this formula it keeps returning “0”? =SUMIF(A7:A21,"MONTH(3)",C7:C21) Notes: A7:A21 are dates in this format (3/5) Condition is to see if the Month is March (thus I use 3) C7:C21 is the sale amount to add if condition is true I also tried the following and it also returns 0 =SUMIF(A7:A21,"=” &MONTH(3)",C7:C21) WST Expert Response: Many things are wrong with the formula. Do this - in a column all the way to the right, say U, do =month(A7) then copy that down to A21. Then at bottom, do =sumif(U7:U21,3,C7:C21). To avoid using a brand new column, you need SUM+IF array. It's complicated. See Advanced Excel for Data Analysis video.

*M&A and LBO Modeling Questions with an * provided with the assistance of Ted Murphy of Needham & Co.

Show All Questions / Hide All Questions