{"text_format":"3","workbook":"xls","n_sheets":1,"sheets":["NewFCFF2Stage"],"not_rendered":{"comments":50},"read":"whole","date_system":"1904","formulas":"cached_only"}

{"sheet":"NewFCFF2Stage","index":0,"n_rows":159,"n_cols":14,"rows_kept":159,"flags":["formulas_cached_only"]}
[null,"Two-Stage FCFF Discount Model"]
[]
[null,"This model is designed to value a firm, with two stages of growth, an initial"]
[null,"period of higher growth and a subsequent period of stable growth."]
[]
[null,"Assumptions"]
[null,"1. The firm is expected to grow at a higher growth rate in the first period."]
[null,"2. The growth rate will drop at the end of the first period to the stable growth rate."]
[]
[null,"The user has to define the following inputs:"]
[null,"1. Length of high growth period"]
[null,"2. Expected growth rate in earnings during the high growth period."]
[null,"3. Capital Spending, Depreciation and Working Capital needs during the high growth period."]
[null,"4. Expected growth rate in earnings during the stable growth period."]
[null,"5. Inputs for the cost of capital. (Cost of equity, Cost of debt, Weights on debt and equity)"]
[]
[]
[]
[null,"Inputs to the model"]
[null,"Current EBIT =",null,{"v":5186,"d":"$5,186.00"},"(in currency)"]
[null,"Current Interest Expense =",null,{"v":118,"d":"$118.00"},"(in currency)"]
[null,"Current Capital Spending",null,{"v":2152,"d":"$2,152.00"},"(in currency)"]
[null,"Current Depreciation & Amort'n =",null,{"v":1228,"d":"$1,228.00"},"(in currency)"]
[null,"Tax Rate on Income =",null,{"v":0.2849,"d":"28.49%"},"(in percent)"]
[null,"Current Revenues =",null,{"v":16701,"d":"$16,701.00"},"( in currency)"]
[null,"Current Non-cash Working Capital =",null,{"v":3755,"d":"$3,755.00"},"(in currency)"]
[null,"Chg. Working Capital =",null,{"v":499,"d":"$499.00"},"(in currency)"]
[null,"Book Value of Debt =",null,{"v":1479,"d":"$1,479.00"},"( in currency)"]
[null,"Book Value of Equity =",null,{"v":12941,"d":"$12,941.00"},"(in currency)"]
[]
[null,"Weights on Debt and Equity"]
[null,"Is the firm publicly traded ?",{"$nulls":3},"Yes","( Yes or No)"]
[]
[null,"If yes, enter the market price per share =",{"$nulls":3},{"v":125.5,"d":"$125.50"},"(in currency)"]
[null,"      & Number of shares outstanding  =",{"$nulls":3},993.57,"(in #)"]
[null,"      & Market Value of Debt =",{"$nulls":3},{"v":1822,"d":"$1,822.00"},"( in currency)"]
[]
[null,"If no, do you want to use the book value debt ratio ?",{"$nulls":4},"(Yes or No)"]
[null,"If no, enter the debt to capital ratio to be used =",{"$nulls":4},"(in percent)"]
[]
[null,"Enter length of extraordinary growth period =",null,null,5,"(in years)"]
[]
[null,"Do you want to change the debt ratio in the stable growth period?",{"$nulls":3},"No"]
[null,"If yes, enter the debt ratio for the stable growth period ="]
[]
[null,"Costs of Components"]
[null,"Do you want to enter cost of equity directly?",null,null,"No","(Yes or No)"]
[null,"If yes, enter the cost of equity =",{"$nulls":3},"(in percent)"]
[null,"If no, enter the inputs to the cost of equity"]
[null,"Beta of the stock =",null,0.8]
[null,"Riskfree rate=",null,{"v":0.053,"d":"5.30%"},"(in percent)"]
[null,"Risk Premium=",null,{"v":0.055,"d":"5.50%"},"(in percent)"]
[]
[null,"Enter the cost of debt for cost of capital calculation",null,null,{"v":0.055,"d":"5.50%"},"( in percent)"]
[]
[null,"Earnings Inputs"]
[null,"Do you want to use the historical growth rate?",null,null,"No","(Yes or No)"]
[null,"If yes, enter EBIT  from five years ago =",null,null,{"v":800,"d":"$800.00"},"(in currency)"]
[]
[null,"Do you have an outside estimate of growth ?",null,null,"Yes","(Yes or No)"]
[null,"If yes, enter the estimated growth:",null,null,{"v":0.125,"d":"12.50%"},"(in percent)"]
[]
[null,"Do you want to calculate the growth rate from fundamentals?",{"$nulls":3},"Yes","(Yes or No)"]
[null,"The following will be the inputs to the fundamental growth formulation:"]
[null,"ROC =",{"v":0.2571781276005548,"d":"25.72%"}]
[null,"Reinv. Rate =",{"v":0.3837122017190414,"d":"38.37%"}]
[null,"Do you want to change any of these inputs for the high growth period?",{"$nulls":3},"No","(Yes or No)"]
[null,"If yes, specify the values for these inputs (Please enter all variables)"]
[null,"ROC =",{"v":0.1,"d":"10.00%"}]
[null,"Reinv. Rate =",{"v":1,"d":"100.00%"}]
[]
[null,"Specify weights to be assigned to each of these growth rates:"]
[null,"Historical Growth Rate  =",null,null,{"v":0,"d":"0.00%"},"(in percent)"]
[null,"Outside Prediction of Growth =",null,null,{"v":0,"d":"0.00%"},"(in percent)"]
[null,"Fundamental Estimate of Growth =",null,null,{"v":1,"d":"100.00%"},"(in percent)"]
[]
[null,"Enter growth rate in stable growth period?",null,null,{"v":0.06,"d":"6.00%"},"(in percent)"]
[]
[null,"Beta"]
[null,"Will the beta to change in the stable period?",null,null,"No","(Yes or No)"]
[null,"If yes, enter the beta for stable period =",null,null,{"v":1,"d":"1.00"}]
[]
[null,"Will the cost of debt change in the stable period?",null,null,"No","(Yes or No)"]
[null,"If yes, enter the new cost of debt  =",{"$nulls":3},"( in percent)"]
[]
[null,"Capital Spending, Depreciation & Working Capital"]
[null,"Do you want all these items to grow at the same rate as earnings ?",{"$nulls":3},"Yes","(Yes or No)"]
[null,"If not, enter the growth rates for each of the following items:"]
[null,null,"Capital Spending","Depreciation","Revenues"]
[null,"High Growth",{"v":0.06,"d":"6%"},{"v":0.06,"d":"6%"},{"v":0.06,"d":"6%"},"(in percent)"]
[null,"Stable Growth","Do not enter","Do not enter",{"v":0.06,"d":"6%"},"(in percent)"]
[]
[null,"Do you want to keep the current fraction of working capital to revenues?",{"$nulls":3},"Yes","(Yes or No)"]
[null,"Specify working capital as a percent of revenues:",{"$nulls":3},"(in percent)"]
[]
[null,"Capital Spending and Depreciation in Stable Growth"]
[null,"Is capital spending to be offset by depreciation in stable period?",{"$nulls":3},"No","(Yes or No)"]
[null,"If no, do you want to enter capital expenditure as % of depreciation",{"$nulls":3},{"v":1.2,"d":"120%"},"(in percent)"]
[]
[]
[null,"Before reviewing the output, check to see if any warnings appear on the next page."]
[null,"Warnings"]
[null," "]
[null," "]
[]
[null,"Capital Spending exceeds depreciation by a significant factor"]
[null," "]
[null," "]
[]
[null,"Output from the program"]
[null,"Cost of Equity =",null,{"v":0.097,"d":"9.70%"}]
[null,"Equity/(Debt+Equity ) =",null,{"v":0.9855985496111194,"d":"98.56%"}]
[null,"After-tax Cost of debt =",null,{"v":0.039330500000000004,"d":"3.93%"}]
[null,"Debt/(Debt +Equity) =",null,{"v":0.014401450388880632,"d":"1.44%"}]
[null,"Cost of Capital =",null,{"v":0.09616947555679844,"d":"9.62%"}]
[]
[]
[null,"Current EBIT * (1 - tax rate) =",null,null,{"v":3708.5086000000006,"d":"$3,708.51"}]
[null," - (Capital Spending - Depreciation)",null,null,{"v":924,"d":"$924.00"}]
[null," - Change in Working Capital",null,null,{"v":499,"d":"$499.00"}]
[null,"Current FCFF",null,null,{"v":2285.5086000000006,"d":"$2,285.51"}]
[]
[null,"Growth Rate in Earnings per share"]
[{"$nulls":3},"Growth Rate","Weight"]
[null,"Historical Growth =",null,{"v":0.453277350548531,"d":"45.33%"},{"v":0,"d":"0.00%"}]
[null,"Outside Estimates =",null,{"v":0.125,"d":"12.50%"},{"v":0,"d":"0.00%"}]
[null,"Fundamental Growth =",null,{"v":0.09868238557558946,"d":"9.87%"},{"v":1,"d":"100.00%"}]
[null,"Weighted Average",null,{"v":0.09868238557558946,"d":"9.87%"}]
[]
[null,"Growth Rate in capital spending, depreciation and working capital"]
[{"$nulls":3},"High Growth","Stable Growth"]
[null,"Growth rate in capital spending =",null,{"v":0.09868238557558946,"d":"9.87%"},"Do not enter"]
[null,"Growth rate in depreciation =",null,{"v":0.09868238557558946,"d":"9.87%"},"Do not enter"]
[null,"Growth rate in revenues =",null,{"v":0.09868238557558946,"d":"9.87%"},{"v":0.06,"d":"6.00%"}]
[]
[null,"Working Capital as percent of revenues =",null,null,{"v":0.22483683611759775,"d":"22.48%"}," (in percent)"]
[null,"The FCFE for the high growth phase are shown below (upto 10 years)"]
[{"$nulls":3},1,2,3,4,5," "," "," "," "," ","Terminal Year"]
[null,"EBIT * (1 - tax rate)",null,{"v":4074.47307557559,"d":"$4,074.47"},{"v":4476.551798636898,"d":"$4,476.55"},{"v":4918.308609279083,"d":"$4,918.31"},{"v":5403.659035839702,"d":"$5,403.66"},{"v":5936.905000333452,"d":"$5,936.91"}," "," "," "," "," ",{"v":6293.119300353461,"d":"$6,293.12"}]
[null," - (CapEx-Depreciation)",null,{"v":1015.1825242718444,"d":"$1,015.18"},{"v":1115.363157561639,"d":"$1,115.36"},{"v":1225.4298547329436,"d":"$1,225.43"},{"v":1346.3581961535385,"d":"$1,346.36"},{"v":1479.220034789217,"d":"$1,479.22"}," "," "," "," "," ",{"v":416.76864391437823,"d":"$416.77"}]
[null," -Chg. Working Capital",null,{"v":370.55235783633816,"d":"$370.55"},{"v":407.11934848828776,"d":"$407.12"},{"v":447.2948570110917,"d":"$447.29"},{"v":491.4349805566382,"d":"$491.43"},{"v":539.9309567932604,"d":"$539.93"}," "," "," "," "," ",{"v":360.67995004113766,"d":"$360.68"}]
[null,"Free Cashflow to Firm",null,{"v":2688.7381934674077,"d":"$2,688.74"},{"v":2954.0692925869707,"d":"$2,954.07"},{"v":3245.5838975350475,"d":"$3,245.58"},{"v":3565.8658591295252,"d":"$3,565.87"},{"v":3917.754008750975,"d":"$3,917.75"}," "," "," "," "," ",{"v":5515.670706397945,"d":"$5,515.67"}]
[null,"Present Value",null,{"v":2452.8489922615886,"d":"$2,452.85"},{"v":2458.4720176647575,"d":"$2,458.47"},{"v":2464.1079335535564,"d":"$2,464.11"},{"v":2469.756769478735,"d":"$2,469.76"},{"v":2475.418555058786,"d":"$2,475.42"}," "," "," "," "," "]
[]
[null,"Growth Rate in Stable Phase =",null,null,{"v":0.06,"d":"6.00%"}]
[null,"FCFF in Stable Phase =",null,null,{"v":5515.670706397945,"d":"$5,515.67"}]
[null,"Cost of Equity in Stable Phase =",null,null,{"v":0.097,"d":"9.70%"}]
[null,"Equity/ (Equity + Debt) =",null,null,{"v":0.9855985496111194,"d":"98.56%"}]
[null,"AT Cost of Debt in Stable Phase =",null,null,{"v":0.039330500000000004,"d":"3.93%"}]
[null,"Debt/ (Equity + Debt)  =",null,null,{"v":0.014401450388880632,"d":"1.44%"}]
[null,"Cost of Capital in Stable Phase =",null,null,{"v":0.09616947555679844,"d":"9.62%"}]
[null,"Value at the end of growth phase =",null,null,{"v":152495.18057668422,"d":"$152,495.18"}]
[]
[null,"Present Value of FCFF in high growth phase =",{"$nulls":3},{"v":12320.604268017425,"d":"$12,320.60"}]
[null,"Present Value of Terminal Value of Firm =",{"$nulls":3},{"v":96353.52263398288,"d":"$96,353.52"}]
[null,"Value of the firm =",{"$nulls":3},{"v":108674.12690200031,"d":"$108,674.13"}]
[null,"Market Value of outstanding debt =",{"$nulls":3},{"v":1822,"d":"$1,822.00"}]
[null,"Market Value of Equity =",{"$nulls":3},{"v":106852.12690200031,"d":"$106,852.13"}]
[null,"Market Value of Equity/share =",{"$nulls":3},{"v":107.54363245870981,"d":"$107.54"}]