Excel spreadsheet
Sheet1 comparable and unlevered Beta (also multiples for relative valuation) TickerBetaDebt (Thousands)Equity (Thousands)Tax RateUnlevered BetaP/E ratioP/S ratioEV/EBITDA MacysM2.127.499.7121.18%1.3219.110.458.07 Saks Fifth AvenueSKS1.28 GapGPS1.83 Dillard'sDDS1.391.42 Khol's CorporationKSS2.186.618.7624.80%12.390.497 TargetTGT1.31.2 The TJX Comapines, IncTJX0.9612.8785.6225.74%34.42.0221.06 Average Median Assumptions & high-level inputs Historical YearsForecast Years Year 0Year 1Year 2Year 3Year 4Year 5 Growth of sales Michael Dimond: Sales growth is the foundation of forecasting. In an over-simplified model, everything would grow at the same rate as sales. I assume all non-specified items grow at same rate as sales. The items below are typical exceptions…. Cost of sales as % of revune Michael Dimond: If I expect my gross margin to change during my explicit forecast, this line allows me to manage that. If not, I would eliminate this line and let cost of sales grow at the same rate as sales. Look for evidence of past performance to reveal management behavior. Selling, general & admin expenses as % of revune Effective tax rate Growth of PP&E, net Growth of Dept & Amort Expense Michael Dimond: SG&A mostly represents overhead costs. This line allows me to manage SG&A independent of the sales growth. In bad economic times, this is frequently the first line management tries to squeeze to manage costs. In good economic times, some companies see this line bloat a little. Look for patterns that provide evidence of how management has managed SG&A in the past. I would only let SG&A grow at the same rate as sales if there is strong evidence that this is how management controls this line in their P&L. Growth of cash divends paid Michael Dimond: This line will drive your tax expense on the forecast income statement: Tax Expense = Tax Rate times Pre-tax Profit Insert rows as needed for other assumptions… Terminal Growth Rate (after explicit forecast period)-----> How fast do you think cash flows will grow after the five-year explicit forecast? You might want to benchmark this against things like long-term inflation & GDP expectations -----> Rf (Risk-free Rate)% Market Risk-Premium (MRP)%<--- most="" of="" damodaran's="" writing="" indicates="" 4.5%="" to="" 5.5%="" to="" be="" pretty="" normal.="" adjusted="" close="" on="" filing="" date="" shares="" outstanding="" on="" date="" of="" interest="" mv="" equity="" (millions)="" -="" 0="" market="" value="" of="" non-operating="" assets="" -="" 0="" tax="" rate="" 0.00%="" costs="" of="" capital="" unlevered="" cost="" of="" capital="" (ku)="" unlevered="" beta="" ku="" (unlevered="" cost="" of="" capital)="" unlevered="" cost="" of="" capital="" is="" computed="" using="" a="" capm="" variation:="" ku="Rf" +="" unlevered_beta="" *="" (mrp)="" cost="" of="" debt="" (kd)="" and="" market="" value="" of="" debt="" yield="" on="" bonds="" yield="" estimated="" from="" tie="" (times="" interest="" earned="" ratio)="" yield="" estimated="" from="" altman="" z-score="" cost="" of="" debt=""><--- choose="" the="" best="" of="" the="" three="" figures="" above="" market="" value="" of="" debt=""><--- sum="" of="" the="" pv="" of="" balance="" sheet="" debt="" &="" pv="" of="" operating="" lease="" expenses="" balance="" sheet="" debt="" pv="" of="" balance="" sheet="" debt="" pmt="" fv="" n="" operating="" leases="" pv="" of="" operating="" lease="" expenses="" expected="" op.="" lease="" payments="" --=""> Levered Beta and Cost of Equity (Ke) D/E Ratio (based on market values) Levered Beta ("Bottom-up Beta") Ke (Cost of Equity … Common Equity)CAPM: Ke = Rf + Levered_Beta * (MRP) Weighted Average Cost of Capital (WACC) Value MV Debt MV Equity MV Pfd Stock - MV of Total Capital Wd =MV Debt / MV Total Capital We =MV Equity / MV Total Capital Wpfd = MV Pfd Stock / MV Total Capital WACC (Weighted Average Cost of Capital)WACC (Weighted Average Cost of Capital) Reality Check: Do the rates make sense? Do they adequately reflect the uncertainty of the cash flows we are valuing? RememberRf< kd="">< wacc="">< ku="">< ke %%%%% % p/e multiples valuation benchmark p/e benchmark trailing p/e benchmark forward p/e earnings available to common shareholders earnings available to common shareholders (for year 0) forecast earnings available to common shareholders equity intrinsic value equity intrinsic value equity intrinsic value shares shares shares implied price per share implied price per share error:#value! implied price per share upside/downside error:#value!0.0% p/s multiples valuation benchmark p/s benchmark p/s sales revenue sales revenue equity intrinsic value equity intrinsic value shares shares implied price per share implied price per share upside/downside -18.1% ev/ebitda multiples valuation benchmark ev/ebitda benchmark ev/ebitda ebitda ebitda enterprise value enterprise value market value of non-operating assets market value of non-operating assets market value of debt market value of debt equity intrinsic value equity intrinsic value shares shares implied price per share implied price per share upside/downside 22.6% equity cash flow model: discounting equity cash flows at cost of equity ke (cost of equity) ke (cost of equity) 12345 equity cash flow (fcfe) equity cash flow (fcfe) terminal value terminal value pv pv equity value of operations equity value of operations market value of non-operating assets market value of non-operating assets equity intrinsic value equity intrinsic value shares shares implied price per share implied price per share upside/downside -16.8% corporate valuation model: discounting free cash flows at wacc wacc (weighted average cost of capital) wacc (weighted average cost of capital) 12345 free cash flow (fcff) free cash flow (fcff) terminal value terminal value pv pv value of operations value of operations market value of non-operating assets market value of non-operating assets market value of debt market value of debt equity intrinsic value equity intrinsic value shares shares implied price per share implied price per share -7.1% adjusted present value model: discounting unlevered cash flows at unlevered cost of capital ku (unlevered cost of capital) ku (unlevered cost of capital) 12345 fcff fcff terminal value terminal value pv pv unlevered value of operations unlevered value of operations interest tax savings interest tax savings terminal value terminal value pv pv -0 value of tax shield value of tax shield market value of non-operating assets market value of non-operating assets market value of debt market value of debt equity intrinsic value equity intrinsic value shares shares implied price per share implied price per share cash flow computations year ended… figures in… opinc tax rate (t) nopat = opinc x (1-t) depreciation & amortization expense ocf = nopat + dep & amort exp δnowc δgfa fcff = ocf - δnowc - δgfa interest expense, net fcff δdebt int(1-t) pfdcf fcfe = fcff + δdebt - int(1-t) - pfdcf fcff int(t) ucf = fcff + int(t) financial statements & analysis consolidated statement of operations - usd ($) shares in millions, $ in millions income statement sales other revenue total revenue cost of sales selling, general and administrative expenses depreciation and amortization (exculsive of depreciation included in cost of sales) gain on sale cost of goods sold gross profit operating income net intrest expense net other (income)/expense earnings from continuing operations before income tax provisions from income taxes net earnings from continuing operations discounted operations, net of tax net earnings basic earnings per share contiuning operations discounted operations net earnings per share diluted earnings per share contiuning operations discounted operations net earnings per share weighted avergae common shares outstanding basic earnings per share diluted consolidated statement of financial position - usd ($) $ in millions assets cash and cash equivalents accounts receivable inventories assets of discontinued operations accounts and other receivables other current assets total current assets property and equipment land buildings and improvements fixtures and equipment computer hardware and software construction-in-progress accumulated depreciation property and equipment, net noncurrent assets of discontinued operations operationg lease assets other noncurrent assets total assets liabilities and shareholders' investment accounts payable accrued and other current liabilities current portion of long-term debt and other borrowings liabilities of discontinued operations total current liabilities long-term debt and other borrowings noncurrent operating lease liabilities deferred income taxes noncurrent liabilities of discontinued operations other noncurrent liabilities total noncurrent liabilities total liabilities shareholders' investment common stock additional paid-in capital retained earnings accumulated other comprehensive loss pension and other benefit liabilities currency translation adjustment and cash flow hedges total shareholders' investment total liabilities and shareholders' investment cash flows- operating activities operating activities net earnings earnings from discontinued operations, net of tax net earnings from continuing operations adjustments to reconcile net earnings to cash provided by operations: depreciation and amortization share-based compensation expense deferred income taxes gain on sale loss on debt extinguishment noncash losses / (gains) and other, net changes in operating accounts: inventory other assets accounts payable accrued and other liabilities cash provided by operating activities—continuing operations cash provided by operating activities—discontinued operations cash provided by operations investing activities expenditures for property and equipment proceeds from disposal of property and equipment proceeds from sale of businesses cash paid for acquisitions, net of cash assumed other investments cash required for investing activities financing activities additions to long-term debt reductions of long-term debt dividends paid repurchase of stock stock option exercises cash required for financing activities net increase / (decrease) in cash and cash equivalents cash and cash equivalents at beginning of period cash and cash equivalents at end of period common size income statement sales other revenue total revenue cost of sales selling, general and administrative expenses depreciation and amortization (exculsive of depreciation included in cost of sales) operating income net interest expense net other (income)/ expense earnings from continuing operations before income tax provisions from income taxes net earnings from continuing operations discontinued operations, net oof tax net earnings common size balance sheet assets cash and cash equivalents accounts receivable inventories assets of discontinued operations accounts and other receivables other current assets total current assets property and equipment land buildings and improvements fixtures and equipment computer hardware and software construction-in-progress accumulated depreciation property and equipment, net noncurrent assets of discontinued operations operationg lease assets other noncurrent assets total assets liabilities and shareholders' investment accounts payable accured and other current liabilities current portion of long-term debt and other borrowings liabilities of discontinued operations total current liabilities long-term debt and other borrowings noncurrent operating lease liabilities deferred income taxes noncurrent liabilities of discontinued operations other noncurrent liabilities total noncurrent liabilities shareholders' investment common stock additional paid-in capital retained earnings accumulated other comprehensive loss pension and other benefit liabilities currency translation adjustment and cash flow hedges total shareholders' investment total liabilities ad shareholders' investment growth of income statement sales other revenue total revenue cost of sales selling, general and administrative expenses depreciation and ke="" %="" %="" %="" %="" %="" %="" p/e="" multiples="" valuation="" benchmark="" p/e="" benchmark="" trailing="" p/e="" benchmark="" forward="" p/e="" earnings="" available="" to="" common="" shareholders="" earnings="" available="" to="" common="" shareholders="" (for="" year="" 0)="" forecast="" earnings="" available="" to="" common="" shareholders="" equity="" intrinsic="" value="" equity="" intrinsic="" value="" equity="" intrinsic="" value="" shares="" shares="" shares="" implied="" price="" per="" share="" implied="" price="" per="" share="" error:#value!="" implied="" price="" per="" share="" upside/downside="" error:#value!="" 0.0%="" p/s="" multiples="" valuation="" benchmark="" p/s="" benchmark="" p/s="" sales="" revenue="" sales="" revenue="" equity="" intrinsic="" value="" equity="" intrinsic="" value="" shares="" shares="" implied="" price="" per="" share="" implied="" price="" per="" share="" upside/downside="" -18.1%="" ev/ebitda="" multiples="" valuation="" benchmark="" ev/ebitda="" benchmark="" ev/ebitda="" ebitda="" ebitda="" enterprise="" value="" enterprise="" value="" market="" value="" of="" non-operating="" assets="" market="" value="" of="" non-operating="" assets="" market="" value="" of="" debt="" market="" value="" of="" debt="" equity="" intrinsic="" value="" equity="" intrinsic="" value="" shares="" shares="" implied="" price="" per="" share="" implied="" price="" per="" share="" upside/downside="" 22.6%="" equity="" cash="" flow="" model:="" discounting="" equity="" cash="" flows="" at="" cost="" of="" equity="" ke="" (cost="" of="" equity)="" ke="" (cost="" of="" equity)="" 1="" 2="" 3="" 4="" 5="" equity="" cash="" flow="" (fcfe)="" equity="" cash="" flow="" (fcfe)="" terminal="" value="" terminal="" value="" pv="" pv="" equity="" value="" of="" operations="" equity="" value="" of="" operations="" market="" value="" of="" non-operating="" assets="" market="" value="" of="" non-operating="" assets="" equity="" intrinsic="" value="" equity="" intrinsic="" value="" shares="" shares="" implied="" price="" per="" share="" implied="" price="" per="" share="" upside/downside="" -16.8%="" corporate="" valuation="" model:="" discounting="" free="" cash="" flows="" at="" wacc="" wacc="" (weighted="" average="" cost="" of="" capital)="" wacc="" (weighted="" average="" cost="" of="" capital)="" 1="" 2="" 3="" 4="" 5="" free="" cash="" flow="" (fcff)="" free="" cash="" flow="" (fcff)="" terminal="" value="" terminal="" value="" pv="" pv="" value="" of="" operations="" value="" of="" operations="" market="" value="" of="" non-operating="" assets="" market="" value="" of="" non-operating="" assets="" market="" value="" of="" debt="" market="" value="" of="" debt="" equity="" intrinsic="" value="" equity="" intrinsic="" value="" shares="" shares="" implied="" price="" per="" share="" implied="" price="" per="" share="" -7.1%="" adjusted="" present="" value="" model:="" discounting="" unlevered="" cash="" flows="" at="" unlevered="" cost="" of="" capital="" ku="" (unlevered="" cost="" of="" capital)="" ku="" (unlevered="" cost="" of="" capital)="" 1="" 2="" 3="" 4="" 5="" fcff="" fcff="" terminal="" value="" terminal="" value="" pv="" pv="" unlevered="" value="" of="" operations="" unlevered="" value="" of="" operations="" interest="" tax="" savings="" interest="" tax="" savings="" terminal="" value="" terminal="" value="" pv="" pv="" -0="" value="" of="" tax="" shield="" value="" of="" tax="" shield="" market="" value="" of="" non-operating="" assets="" market="" value="" of="" non-operating="" assets="" market="" value="" of="" debt="" market="" value="" of="" debt="" equity="" intrinsic="" value="" equity="" intrinsic="" value="" shares="" shares="" implied="" price="" per="" share="" implied="" price="" per="" share="" cash="" flow="" computations="" year="" ended…="" figures="" in…="" opinc="" tax="" rate="" (t)="" nopat="OpInc" x="" (1-t)="" depreciation="" &="" amortization="" expense="" ocf="NOPAT" +="" dep="" &="" amort="" exp="" δnowc="" δgfa="" fcff="OCF" -="" δnowc="" -="" δgfa="" interest="" expense,="" net="" fcff="" δdebt="" int(1-t)="" pfdcf="" fcfe="FCFF" +="" δdebt="" -="" int(1-t)="" -="" pfdcf="" fcff="" int(t)="" ucf="FCFF" +="" int(t)="" financial="" statements="" &="" analysis="" consolidated="" statement="" of="" operations="" -="" usd="" ($)="" shares="" in="" millions,="" $="" in="" millions="" income="" statement="" sales="" other="" revenue="" total="" revenue="" cost="" of="" sales="" selling,="" general="" and="" administrative="" expenses="" depreciation="" and="" amortization="" (exculsive="" of="" depreciation="" included="" in="" cost="" of="" sales)="" gain="" on="" sale="" cost="" of="" goods="" sold="" gross="" profit="" operating="" income="" net="" intrest="" expense="" net="" other="" (income)/expense="" earnings="" from="" continuing="" operations="" before="" income="" tax="" provisions="" from="" income="" taxes="" net="" earnings="" from="" continuing="" operations="" discounted="" operations,="" net="" of="" tax="" net="" earnings="" basic="" earnings="" per="" share="" contiuning="" operations="" discounted="" operations="" net="" earnings="" per="" share="" diluted="" earnings="" per="" share="" contiuning="" operations="" discounted="" operations="" net="" earnings="" per="" share="" weighted="" avergae="" common="" shares="" outstanding="" basic="" earnings="" per="" share="" diluted="" consolidated="" statement="" of="" financial="" position="" -="" usd="" ($)="" $="" in="" millions="" assets="" cash="" and="" cash="" equivalents="" accounts="" receivable="" inventories="" assets="" of="" discontinued="" operations="" accounts="" and="" other="" receivables="" other="" current="" assets="" total="" current="" assets="" property="" and="" equipment="" land="" buildings="" and="" improvements="" fixtures="" and="" equipment="" computer="" hardware="" and="" software="" construction-in-progress="" accumulated="" depreciation="" property="" and="" equipment,="" net="" noncurrent="" assets="" of="" discontinued="" operations="" operationg="" lease="" assets="" other="" noncurrent="" assets="" total="" assets="" liabilities="" and="" shareholders'="" investment="" accounts="" payable="" accrued="" and="" other="" current="" liabilities="" current="" portion="" of="" long-term="" debt="" and="" other="" borrowings="" liabilities="" of="" discontinued="" operations="" total="" current="" liabilities="" long-term="" debt="" and="" other="" borrowings="" noncurrent="" operating="" lease="" liabilities="" deferred="" income="" taxes="" noncurrent="" liabilities="" of="" discontinued="" operations="" other="" noncurrent="" liabilities="" total="" noncurrent="" liabilities="" total="" liabilities="" shareholders'="" investment="" common="" stock="" additional="" paid-in="" capital="" retained="" earnings="" accumulated="" other="" comprehensive="" loss="" pension="" and="" other="" benefit="" liabilities="" currency="" translation="" adjustment="" and="" cash="" flow="" hedges="" total="" shareholders'="" investment="" total="" liabilities="" and="" shareholders'="" investment="" cash="" flows-="" operating="" activities="" operating="" activities="" net="" earnings="" earnings="" from="" discontinued="" operations,="" net="" of="" tax="" net="" earnings="" from="" continuing="" operations="" adjustments="" to="" reconcile="" net="" earnings="" to="" cash="" provided="" by="" operations:="" depreciation="" and="" amortization="" share-based="" compensation="" expense="" deferred="" income="" taxes="" gain="" on="" sale="" loss="" on="" debt="" extinguishment="" noncash="" losses="" (gains)="" and="" other,="" net="" changes="" in="" operating="" accounts:="" inventory="" other="" assets="" accounts="" payable="" accrued="" and="" other="" liabilities="" cash="" provided="" by="" operating="" activities—continuing="" operations="" cash="" provided="" by="" operating="" activities—discontinued="" operations="" cash="" provided="" by="" operations="" investing="" activities="" expenditures="" for="" property="" and="" equipment="" proceeds="" from="" disposal="" of="" property="" and="" equipment="" proceeds="" from="" sale="" of="" businesses="" cash="" paid="" for="" acquisitions,="" net="" of="" cash="" assumed="" other="" investments="" cash="" required="" for="" investing="" activities="" financing="" activities="" additions="" to="" long-term="" debt="" reductions="" of="" long-term="" debt="" dividends="" paid="" repurchase="" of="" stock="" stock="" option="" exercises="" cash="" required="" for="" financing="" activities="" net="" increase="" (decrease)="" in="" cash="" and="" cash="" equivalents="" cash="" and="" cash="" equivalents="" at="" beginning="" of="" period="" cash="" and="" cash="" equivalents="" at="" end="" of="" period="" common="" size="" income="" statement="" sales="" other="" revenue="" total="" revenue="" cost="" of="" sales="" selling,="" general="" and="" administrative="" expenses="" depreciation="" and="" amortization="" (exculsive="" of="" depreciation="" included="" in="" cost="" of="" sales)="" operating="" income="" net="" interest="" expense="" net="" other="" (income)/="" expense="" earnings="" from="" continuing="" operations="" before="" income="" tax="" provisions="" from="" income="" taxes="" net="" earnings="" from="" continuing="" operations="" discontinued="" operations,="" net="" oof="" tax="" net="" earnings="" common="" size="" balance="" sheet="" assets="" cash="" and="" cash="" equivalents="" accounts="" receivable="" inventories="" assets="" of="" discontinued="" operations="" accounts="" and="" other="" receivables="" other="" current="" assets="" total="" current="" assets="" property="" and="" equipment="" land="" buildings="" and="" improvements="" fixtures="" and="" equipment="" computer="" hardware="" and="" software="" construction-in-progress="" accumulated="" depreciation="" property="" and="" equipment,="" net="" noncurrent="" assets="" of="" discontinued="" operations="" operationg="" lease="" assets="" other="" noncurrent="" assets="" total="" assets="" liabilities="" and="" shareholders'="" investment="" accounts="" payable="" accured="" and="" other="" current="" liabilities="" current="" portion="" of="" long-term="" debt="" and="" other="" borrowings="" liabilities="" of="" discontinued="" operations="" total="" current="" liabilities="" long-term="" debt="" and="" other="" borrowings="" noncurrent="" operating="" lease="" liabilities="" deferred="" income="" taxes="" noncurrent="" liabilities="" of="" discontinued="" operations="" other="" noncurrent="" liabilities="" total="" noncurrent="" liabilities="" shareholders'="" investment="" common="" stock="" additional="" paid-in="" capital="" retained="" earnings="" accumulated="" other="" comprehensive="" loss="" pension="" and="" other="" benefit="" liabilities="" currency="" translation="" adjustment="" and="" cash="" flow="" hedges="" total="" shareholders'="" investment="" total="" liabilities="" ad="" shareholders'="" investment="" growth="" of="" income="" statement="" sales="" other="" revenue="" total="" revenue="" cost="" of="" sales="" selling,="" general="" and="" administrative="" expenses="" depreciation="">