{"text_format":"3","workbook":"xlsx","n_sheets":2,"sheets":["Disc. Earnings","Div. Disc. Model"],"not_rendered":{"macros":1},"read":"whole","date_system":"1900"}

{"sheet":"Disc. Earnings","index":0,"n_rows":37,"n_cols":7,"rows_kept":37}
[{"$nulls":4},"TSCO  3/18/2014"]
[]
[]
[{"$nulls":4},"Initial Earnings",328.2,"Millions $"]
[null,{"$f":"(F4*(1+F6))/(1+F8)","v":{"v":344.66860714285707,"d":"344.67"}},{"$f":" getformula(B5)","v":"<-- =(F4*(1+F6))/(1+F8)"}]
[null,{"$f":"(F4*(1+F6)^2)/(1+F8)^2","v":{"v":361.9635854655611,"d":"361.96"}},{"$f":" getformula(B6)","v":"<-- =(F4*(1+F6)^2)/(1+F8)^2"},null,"First stage earnings growth rate",0.1762,"Percent decimal"]
[null,{"$f":"(F4*(1+F6)^3)/(1+F8)^3","v":{"v":380.12640109338645,"d":"380.13"}},"<-- =(F4*(1+F6)^3)/(1+F8)^3"]
[null,{"$f":"(F4*(1+F6)^4)/(1+F8)^4","v":{"v":399.20060086253676,"d":"399.20"}},"<-- =(F4*(1+F6)^4)/(1+F8)^4",null,"First stage discount rate",0.12,"Percent decimal"]
[null,{"$f":"(F4*(1+F6)^5)/(1+F8)^5","v":{"v":419.2319167272462,"d":"419.23"}},"<-- =(F4*(1+F6)^5)/(1+F8)^5"]
[null,{"$f":"(F4*(1+F6)^6)/(1+F8)^6","v":{"v":440.26837540588105,"d":"440.27"}},"<-- =(F4*(1+F6)^6)/(1+F8)^6",null,"n length, in years of first stage",10,"Years"]
[null,{"$f":"(F4*(1+F6)^7)/(1+F8)^7","v":{"v":462.3604135289261,"d":"462.36"}},"<-- =(F4*(1+F6)^7)/(1+F8)^7"]
[null,{"$f":"(F4*(1+F6)^8)/(1+F8)^8","v":{"v":485.5609985649311,"d":"485.56"}},"<-- =(F4*(1+F6)^8)/(1+F8)^8"]
[null,{"$f":"(F4*(1+F6)^9)/(1+F8)^9","v":{"v":509.9257558143498,"d":"509.93"}},"<-- =(F4*(1+F6)^9)/(1+F8)^9"]
[null,{"$f":"(F4*(1+F6)^10)/(1+F8)^10","v":{"v":535.5131017757484,"d":"535.51"}},"<-- =(F4*(1+F6)^10)/(1+F8)^10",null," Second stage earnings growth",0.06,"Percent decimal"]
[]
["Discounted Value",{"$f":"(SUM(B5:B14))/1000","v":{"v":4.338819756381424,"d":"$4.34"}},{"$f":" getformula(B16)","v":"<-- =(SUM(B5:B14))/1000"},null,"Second stage discount rate",0.12,"Percent decimal"]
[]
[{"$nulls":4},"Long Term Debt",0.012,"Billions $"]
[]
["Continuing Value",{"$f":"(((F4*(1+F6)^F10)*(1+F14))/(F16-F14)/(1+F8)^F10)/1000","v":{"v":9.46073146470489,"d":"$9.46"}},{"$f":" getformula(B20)","v":"<-- =(((F4*(1+F6)^F10)*(1+F14))/(F16-F14)/(1+F8)^F10)/1000"},null,"Shares Outstanding",139.7,"Millions of shares"]
[]
[]
["Intrinsic Value",{"$f":"B16+B20-F18","v":{"v":13.787551221086314,"d":"$13.79"}},{"$f":" getformula(B23)","v":"<-- =B16+B20-F18"},null," "]
[]
[]
["Intrinsic Stock Price",{"$f":"(B23/F20)*1000","v":{"v":98.6939958560223,"d":"$98.69"}},{"$f":" getformula(B26)","v":"<-- =(B23/F20)*1000"}]
[]
[]
[]
[null,null,"Sample program - use at your discretion. I assume no liability or make any promises with regard to this program or is use."]
[]
[null,null,"Notice to users: Be careful when switching between millions and billions"]
[]
[null,null,"All values in column F can be changed. However:"]
[null,null,"Length of first stage should be left at 10 years"]
[null,null,"Interest rate in first stage is whatever you wish it to be. "]
[null,null,"Generally, you want to leave stage 2 earnings growth rate and discount rate alone."]

{"sheet":"Div. Disc. Model","index":1,"n_rows":27,"n_cols":8,"rows_kept":27}
[]
[]
[null,null,"Pepsi example - 2 Statge Dividend Discount Model"]
[]
[]
[{"$nulls":6},"Initial Dividend",{"v":1.12,"d":"$1.12"}]
[{"$nulls":4},{"$f":"(H6*(1+H8))/(1+H10)","v":1.149217391304348},{"$f":"getformula(E7)","v":"<-- =(H6*(1+H8))/(1+H10)"}]
[{"$nulls":4},{"$f":"(H6*(1+H8)^2)/(1+H10)^2","v":1.179196975425331},{"$f":"getformula(E8)","v":"<-- =(H6*(1+H8)^2)/(1+H10)^2"},"First stage dividend growth rate",0.18]
[{"$nulls":4},{"$f":"(H6*(1+H8)^3)/(1+H10)^3","v":1.2099586356538181},"<-- =(H6*(1+H8)^3)/(1+H10)^3"]
[{"$nulls":4},{"$f":"(H6*(1+H8)^4)/(1+H10)^4","v":1.241522773975222},"<-- =(H6*(1+H8)^4)/(1+H10)^4","First stage discount rate",0.15]
[{"$nulls":4},{"$f":"(H6*(1+H8)^5)/(1+H10)^5","v":1.2739103246006622},"<-- =(H6*(1+H8)^5)/(1+H10)^5"]
[{"$nulls":4},{"$f":"(H6*(1+H8)^6)/(1+H10)^6","v":1.3071427678511145},"<-- =(H6*(1+H8)^6)/(1+H10)^6","n length, in years of first stage",10]
[{"$nulls":4},{"$f":"(H6*(1+H8)^7)/(1+H10)^7","v":1.3412421444037526},"<-- =(H6*(1+H8)^7)/(1+H10)^7"]
[{"$nulls":4},{"$f":"(H6*(1+H8)^8)/(1+H10)^8","v":1.3762310699099374},"<-- =(H6*(1+H8)^8)/(1+H10)^8","n length, in years of first stage",5]
[{"$nulls":4},{"$f":"(H6*(1+H8)^9)/(1+H10)^9","v":1.4121327499945446},"<-- =(H6*(1+H8)^9)/(1+H10)^9"]
[{"$nulls":4},{"$f":"(H6*(1+H8)^10)/(1+H10)^10","v":1.4489709956465762},"<-- =(H6*(1+H8)^10)/(1+H10)^10"," Second stage dividend growth rate ",0.085]
[{"$nulls":5},"<-- "]
[{"$nulls":3},"10 Year Value",{"$f":"SUM(E7:E16)","v":{"v":12.939525828765307,"d":"$12.94"}},"<-- =SUM(E7:E16)"," Second stage discount rate ",0.105]
[{"$nulls":5},"<-- "]
[{"$nulls":3},"5 Year Value",{"$f":"SUM(E7:E11)","v":{"v":6.053806100959381,"d":"$6.05"}},"<-- =SUM(E7:E11)"]
[{"$nulls":5},"<-- "]
[{"$nulls":3},"Continuing Value 10 year",{"$f":"((H6*(1+H8)^H12)*(1+H16))/(H18-H16)/(1+H10)^H12","v":{"v":78.60667651382678,"d":"$78.61"}},"<-- =((H6*(1+H8)^H12)*(1+H16))/(H18-H16)/(1+H10)^H12"]
[{"$nulls":3},"Continuing Value 5 year",{"$f":"((H6*(1+H8)^H14)*(1+H16))/(H18-H16)/(1+H10)^H14","v":{"v":69.10963510958597,"d":"$69.11"}},"<-- =((H6*(1+H8)^H14)*(1+H16))/(H18-H16)/(1+H10)^H14"]
[{"$nulls":5},"<-- "]
[{"$nulls":3},"Intrinsic Value of Shares 10 year first period",{"$f":"E18+E22","v":{"v":91.5462023425921,"d":"$91.55"}},"<-- =E18+E22"]
[{"$nulls":5},"<-- "]
[{"$nulls":3},"Intrinsic Value of Shares 5 year first period",{"$f":"E20+E23","v":{"v":75.16344121054534,"d":"$75.16"}},"<-- =E20+E23"]