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

{"sheet":"NewFCFE2Stage","index":0,"n_rows":146,"n_cols":14,"rows_kept":146,"flags":["formulas_cached_only"]}
[null,"Two-Stage FCFE Discount Model"]
[]
[null,"This model is designed to value the equity in 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,"3. The free cashflow to equity is the correct measure of expected cashflows to stockholders."]
[]
[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 equity."]
[]
[]
[]
[null,"Inputs to the model"]
[null,"Current Earnings per share =",null,{"v":4,"d":"$4.00"},"(in currency)"]
[null,"Current Dividends per share =",null,{"v":0.25,"d":"$0.25"},"(in currency)"]
[null,"Current Capital Spending/sh =",null,{"v":3.7,"d":"$3.70"},"(in currency)"]
[null,"Current Depreciation / share =",null,{"v":1.7,"d":"$1.70"},"(in currency)"]
[null,"Current Revenues/ share =",null,{"v":20,"d":"$20.00"}]
[null,"Working Capital/ share =",null,{"v":8,"d":"$8.00"},"(in currency)"]
[null,"Chg. Working Capital/share =",null,{"v":1,"d":"$1.00"}]
[]
[null,"Enter length of extraordinary growth period =",null,null,5,"(in years)"]
[]
[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,1.3]
[null,"Riskfree rate=",null,{"v":0.07,"d":"7.00%"},"(in percent)"]
[null,"Risk Premium=",null,{"v":0.055,"d":"5.50%"},"(in percent)"]
[]
[null,"Earnings Inputs"]
[null,"Do you want to use the historical growth rate?",null,null,"Yes","(Yes or No)"]
[null,"If yes, enter EPS from five years ago =",null,null,{"v":0.49,"d":"$0.49"},"(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.19,"d":"19.00%"},"(in percent)"]
[]
[null,"Do you want to calculate the growth rate from fundamentals?",{"$nulls":3},"Yes","(Yes or No)"]
[null,"If yes, enter the following inputs:"]
[null,"Net Income Currently =",null,{"v":1077,"d":"$1,077.00"},null,"(in currency)"]
[null,"Interest Expense Currently =",null,{"v":53.85,"d":"$53.85"},null,"(in currency)"]
[null,"Book Value of Debt =",null,{"v":600,"d":"$600.00"},null,"(in currency)"]
[null,"Book Value of Equity =",null,{"v":5445,"d":"$5,445.00"},null,"(in currency)"]
[null,"Tax Rate on Income=",null,{"v":0.36,"d":"36.00%"},null,"(in percent)"]
[null,"The following will be the inputs to the fundamental growth formulation:"]
[null,"ROC =",{"v":0.1838650124069479,"d":"18.39%"},"D/E =",{"v":0.11019283746556474,"d":"11.02%"},"(in percent)"]
[null,"Retention =",{"v":0.9375,"d":"93.75%"},"Interest Rate=",{"v":0.08975,"d":"8.98%"},"(in percent)"]
[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.1838650124069479,"d":"18.39%"},"D/E =",{"v":0.11019283746556474,"d":"11.02%"},"(in percent)"]
[null,"Retention =",{"v":0.9375,"d":"93.75%"},"Interest Rate=",{"v":0.08975,"d":"8.98%"},"(in percent)"]
[]
[null,"Specify weights to be assigned to each of these growth rates:"]
[null,"Historical Growth Rate  =",null,null,{"v":0.1,"d":"10.00%"},"(in percent)"]
[null,"Outside Prediction of Growth =",null,null,{"v":0.4,"d":"40.00%"},"(in percent)"]
[null,"Fundamental Estimate of Growth =",null,null,{"v":0.5,"d":"50.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,"Yes","(Yes or No)"]
[null,"If yes, enter the beta for stable period =",null,null,1.1]
[]
[null,"Capital Spending, Depreciation & Working Capital"]
[null,"Do you want all these items to grow at the same rate as earnings ?",{"$nulls":3},"No","(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.2,"d":"20%"},{"v":0.2,"d":"20%"},{"v":0.18,"d":"18%"},"(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:",null,null,{"v":0.4,"d":"40%"},"(in percent)"]
[]
[null,"Do you want to use the current debt ratio as your desired mix?",{"$nulls":3},"Yes","(Yes or No)"]
[null,"If no, enter the following inputs for financing mix,"]
[null,"Desired debt financing proportion - Capital Spending",{"$nulls":3},"(in percent)"]
[null,"Desired debt financing proportion - Working Capital ",{"$nulls":3},"(in percent)"]
[]
[null,"Capital spending and Depreciation during Stable Growth"]
[null,"Is capital spending to be offset by depreciation in stable period?",{"$nulls":3},"No","(Yes or No)"]
[null,"If no, enter capital expenditures as % of depreciation in stable growth",{"$nulls":3},{"v":1.5,"d":"150%"},"(in percent)"]
[]
[null,"Before reviewing the output, check to see if any warnings appear on the next page."]
[null,"Warnings"]
[null," "]
[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.14150000000000001,"d":"14.15%"}]
[]
[null,"Proportion of Debt: Capital Spending (DR)=",null,null,{"v":0.09925558312655088,"d":"9.93%"}]
[null,"Proportion of Debt: Working Capital (DR)=",null,null,{"v":0.09925558312655088,"d":"9.93%"}]
[]
[null,"Current Earnings per share=",null,null,{"v":4,"d":"$4.00"}]
[null,"(Capital Spending - Depreciation)*(1-DR)",null,null,{"v":1.8014888337468982,"d":"$1.80"}]
[null,"Change in Working Capital * (1-DR)",null,null,{"v":0.9007444168734491,"d":"$0.90"}]
[null,"Current FCFE",null,null,{"v":1.2977667493796528,"d":"$1.30"}]
[]
[null,"Growth Rate in Earnings per share"]
[{"$nulls":3},"Growth Rate","Weight"]
[null,"Historical Growth =",null,{"v":0.5218532716010147,"d":"52.19%"},{"v":0.1,"d":"10.00%"}]
[null,"Outside Estimates =",null,{"v":0.19,"d":"19.00%"},{"v":0.4,"d":"40.00%"}]
[null,"Fundamental Growth =",null,{"v":0.18543388429752067,"d":"18.54%"},{"v":0.5,"d":"50.00%"}]
[null,"Weighted Average",null,{"v":0.2209022693088618,"d":"22.09%"}]
[]
[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.2,"d":"20.00%"},"Do not enter"]
[null,"Growth rate in depreciation =",null,{"v":0.2,"d":"20.00%"},"Do not enter"]
[null,"Growth rate in revenues =",null,{"v":0.18,"d":"18.00%"},{"v":0.06,"d":"6.00%"}]
[]
[null,"Working Capital as percent of revenues =",null,null,{"v":0.4,"d":"40.00%"}," (in percent)"]
[null,"The FCFE for the high growth phase are shown below (upto 6 years)"]
[{"$nulls":3},1,2,3,4,5," "," "," "," "," ","Terminal Year"]
[null,"Earnings",null,{"v":4.883609077235447,"d":"$4.88"},{"v":5.962409404814114,"d":"$5.96"},{"v":7.279519172886052,"d":"$7.28"},{"v":8.88758147765395,"d":"$8.89"},{"v":10.850868394735114,"d":"$10.85"}," "," "," "," "," ",{"v":11.50192049841922,"d":"$11.50"}]
[null," - (CapEx-Depreciation)*(1-DR)",null,{"v":2.1617866004962782,"d":"$2.16"},{"v":2.5941439205955334,"d":"$2.59"},{"v":3.1129727047146405,"d":"$3.11"},{"v":3.7355672456575677,"d":"$3.74"},{"v":4.482680694789083,"d":"$4.48"}," "," "," "," "," ",{"v":2.019447653002481,"d":"$2.02"}]
[null," -Chg. Working Capital*(1-DR)",null,{"v":1.2970719602977663,"d":"$1.30"},{"v":1.5305449131513642,"d":"$1.53"},{"v":1.80604299751861,"d":"$1.81"},{"v":2.1311307370719583,"d":"$2.13"},{"v":2.51473426974491,"d":"$2.51"}," "," "," "," "," ",{"v":0.9891288127663337,"d":"$0.99"}]
[null,"Free Cashflow to Equity",null,{"v":1.4247505164414027,"d":"$1.42"},{"v":1.8377205710672162,"d":"$1.84"},{"v":2.360503470652801,"d":"$2.36"},{"v":3.020883494924423,"d":"$3.02"},{"v":3.8534534302011205,"d":"$3.85"}," "," "," "," "," ",{"v":8.493344032650407,"d":"$8.49"}]
[null,"Present Value",null,{"v":1.248138866790541,"d":"$1.25"},{"v":1.4103524103807255,"d":"$1.41"},{"v":1.5869998870052877,"d":"$1.59"},{"v":1.779222709081908,"d":"$1.78"},{"v":1.9882479149312287,"d":"$1.99"}," "," "," "," "," "]
[]
[null,"Growth Rate in Stable Phase =",null,null,{"v":0.06,"d":"6.00%"}]
[null,"FCFE in Stable Phase =",null,null,{"v":8.493344032650407,"d":"$8.49"}]
[null,"Cost of Equity in Stable Phase =",null,null,{"v":0.1305,"d":"13.05%"}]
[null,"Price at the end of growth phase =",null,null,{"v":120.47296500213342,"d":"$120.47"}]
[]
[null,"Present Value of FCFE in high growth phase =",{"$nulls":3},{"v":8.01296178818969,"d":"$8.01"}]
[null,"Present Value of Terminal Price =",{"$nulls":3},{"v":62.15985889274729,"d":"$62.16"}]
[null,"Value of the stock =",{"$nulls":3},{"v":70.17282068093698,"d":"$70.17"}]
[]
[]
[null,"Estimating the value of growth"]
[null,"Value of assets in place =",null,null,{"v":9.944572792181246,"d":"$9.94"}]
[null,"Value of stable growth =",null,null,{"v":9.567948546009278,"d":"$9.57"}]
[null,"Value of extraordinary growth =",null,null,{"v":50.660299342746455,"d":"$50.66"}]
[null,"Value of the stock =",null,null,{"v":70.17282068093698,"d":"$70.17"}]