{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["4YrValue"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":65,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"4YrValue","index":0,"n_rows":96,"n_cols":12,"rows_kept":96,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,"_","___",null,"___"]
["Inputs",0,1,2,3,4,5,6,null,"Click here"]
["Horizon year ",4,null,null,null,null,null,null,null,1]
["Book value per share: b0",10,null,null,null,null,null,null,null,{"$f":"J13+1","v":2}]
["Cost of equity or discount rate: re",0.09,null,null,null,null,null,null,null,{"$f":"J14+1","v":3}]
["Perpetual growth in residual earnings: g",1.4916987079438632E-2,null,null,null,null,null,null,null,{"$f":"J15+1","v":4}]
["Earnings per share: et","NA",{"$f":"B14*15%","v":1.5},{"$f":"C17*(1+7%)","v":1.605},{"$f":"D17*(1+7%)","v":1.7173500000000002},{"$f":"E17*(1+7%)","v":1.8375645000000003},null,null,null,{"$f":"J16+1","v":5}]
["Dividend payout as % of earnings [payout]","NA",0.6,{"$f":"C18","v":0.6},{"$f":"D18","v":0.6},{"$f":"C18","v":0.6},{"$f":"F18","v":0.6},{"$f":"G18","v":0.6},null,{"$f":"J17+1","v":6}]
[]
["Financial statements",{"$f":"B12","v":0},{"$f":"C12","v":1},2,3,4,5,6]
["Earnings per share: et",{"$f":"B17","v":"NA"},{"$f":"C17","v":1.5},{"$f":"D17","v":1.605},{"$f":"E17","v":1.7173500000000002},{"$f":"F17","v":1.8375645000000003},{"$f":"G25+G24","v":1.9151128019575094},{"$f":"H25+H24","v":1.9956228364712727},null,{"$f":"J18+1","v":7},null,{"$f":"L24+1","v":15}]
["Dividends per share: dt",{"$f":"B18","v":"NA"},{"$f":"C21*C18","v":0.89999999999999991},0.96299999999999997,1.03041,1.1025387000000002,1.1490676811745055,1.1973737018827635,null,{"$f":"J21+1","v":8},null,{"$f":"L21+1","v":16}]
["Ending book value per share: bt",{"$f":"B14","v":10},{"$f":"B23+C17-C22","v":10.6},{"$f":"C23+D17-D22","v":11.242000000000001},{"$f":"D23+E17-E22","v":11.928940000000001},{"$f":"E23+F17-F22","v":12.663965800000001},{"$f":"F23+G21-G22","v":13.430010920783005},{"$f":"G23+H21-H22","v":14.228260055371514},null,{"$f":"J22+1","v":9},{"$f":"J23+1","v":10},{"$f":"L22+1","v":17}]
["Charge for equity: cost of equity*previous ending book value = re * bt-1",null,{"$f":"$B$15*B23","v":0.89999999999999991},0.95399999999999996,1.0117800000000001,1.0736046000000001,1.1397569220000001,1.2087009828704705,null,{"$f":"K23+1","v":11},null,{"$f":"L25+1","v":14}]
["Residual earnings per share: rest = et -  re * bt-1","NA",{"$f":"C21-($B$15*B23)","v":0.60000000000000009},{"$f":"D21-($B$15*C23)","v":0.65100000000000002},{"$f":"E21-($B$15*D23)","v":0.70557000000000003},{"$f":"F21-($B$15*E23)","v":0.76395990000000014},{"$f":"F25*(1+$B$16)","v":0.77535587995750932},{"$f":"G25*(1+$B$16)","v":0.78692185360080225},null,{"$f":"J24+1","v":12},null,{"$f":"J25+1","v":13}]
["Growth rates and returns",{"$f":"B12","v":0},1,2,3,4,5,6]
["Earnings growth rate",null,null,{"$f":"D21/C21-1","v":7.0000000000000062E-2},7.0000000000000062E-2,7.0000000000000062E-2,{"$f":"G21/F21-1","v":4.2201676162936907E-2},4.2039317178325319E-2,null,{"$f":"L23+1","v":18}]
["Dividends growth rate",null,null,7.0000000000000062E-2,7.0000000000000062E-2,7.0000000000000062E-2,4.2201676162936685E-2,4.2039317178325319E-2,null,{"$f":"J27+1","v":19}]
["Book value growth rate",null,{"$f":"C23/B23-1","v":6.0000000000000053E-2},6.0566037735849232E-2,6.1104785625333502E-2,6.1617025485919186E-2,6.0490144468251961E-2,5.9437712991968983E-2,null,{"$f":"J28+1","v":20}]
["Residual earnings growth rate",null,null,{"$f":"IF(AND(C25=0,D25=0),0,D25/C25-1)","v":8.4999999999999964E-2},{"$f":"IF(AND(D25=0,E25=0),0,E25/D25-1)","v":8.3824884792626841E-2},{"$f":"IF(AND(E25=0,F25=0),0,F25/E25-1)","v":8.275564437263494E-2},{"$f":"IF(AND(F25=0,G25=0),0,G25/F25-1)","v":1.491698707943856E-2},{"$f":"IF(AND(G25=0,H25=0),0,H25/G25-1)","v":1.491698707943856E-2},null,{"$f":"J29+1","v":21}]
["Forward return on equity = Forthcoming eps/current bps [ROE]","NA",{"$f":"C21/B23","v":0.15},0.15141509433962264,0.15276196406333392,0.1540425637147978,0.15122536117063023,0.1485942824799224,null,{"$f":"J30+1","v":22}]
["Intermediate computations: You may skip these"]
["Terminal value computation",{"$f":"B12","v":0},1,2,3,4,5,6]
["Book value of equity at the end of the year",null,null,null,null,{"$f":"F23","v":12.663965800000001},null,null,null,{"$f":"J31+1","v":23}]
["+ PV of residual earnings at the end of the year",null,null,null,null,{"$f":"G25/(B15-H30)","v":10.326648462786695},null,null,null,{"$f":"J34+1","v":24}]
["= Terminal value per share",null,null,null,null,{"$f":"SUM(F34:F35)","v":22.990614262786696},null,null,null,{"$f":"J35+1","v":25}]
["Alternate computation of terminal value: Theoretical, skip"]
["DPS for perpetual growth = EPS - perpetual growth rate in bt * bt",null,null,null,null,null,{"$f":"G21-B16*F23","v":1.7262045877444567},null,null,{"$f":"J36+1","v":26}]
["Terminal value = PV of future DPS growing at the perpetual growth rate = dt/(re- g) ",null,null,null,null,{"$f":"G38/(B15-B16)","v":22.990614262786707},null,null,null,{"$f":"J38+1","v":27}]
["Capitalized change in dividends: Change in dividends/re = (dt - dt-1)/re",null,0,{"$f":"(D22-C22)/$B$15","v":0.70000000000000062},{"$f":"(E22-D22)/$B$15","v":0.74900000000000089},{"$f":"(F22-E22)/$B$15","v":0.80143000000000175},{"$f":"(G22-F22)/$B$15","v":0.5169886797167258},{"$f":"(H22-G22)/$B$15","v":0.53673356342508849},null,{"$f":"J39+1","v":28}]
["End of intermediate computation"]
[]
[]
["Valuation",{"$f":"B12","v":0}]
["Baseline: Discounted dividends Model",null,"% of Value"]
["Anchor: None",0,{"$f":"B46/$B$49","v":0},null,null,null,null,null,null,{"$f":"J40+1","v":29}]
["+ Until horizon: PV of dividends until the horizon",{"$f":"NPV(B15,C22:F22)","v":3.2129576978549612},{"$f":"B47/$B$49","v":0.16476631398518785},null,null,null,null,null,null,{"$f":"J46+1","v":30}]
["+ Beyond the horizon: PV of terminal value",{"$f":"F36/(1+B15)^B13","v":16.287130761633183},{"$f":"B48/$B$49","v":0.83523368601481218},null,null,null,null,null,null,{"$f":"J47+1","v":31}]
["= Total equity value",{"$f":"SUM(B46:B48)","v":19.500088459488143},{"$f":"SUM(C47:C48)","v":1},null,null,null,null,null,null,{"$f":"J48+1","v":32}]
["Connecting to valuation anchors",{"$f":"B12","v":0}]
["I: Dividend yield: Dividend growth model"]
["Anchor: Capitalized forthcoming dividends",{"$f":"C22/B15","v":10},{"$f":"B52/$B$55","v":0.51281818648029298},null,null,null,null,null,null,{"$f":"J49+1","v":33}]
["+ Until horizon: PV of capitalized change in dividends until the horizon",{"$f":"NPV(B15,C40:F40)","v":1.7352946387563115},{"$f":"B53/$B$55","v":8.8989064965598677E-2},null,null,null,null,null,null,{"$f":"J52+1","v":34}]
["+ Beyond the horizon: PV of capitalized change in dividends beyond the horizon",{"$f":"B49-SUM(B52:B53)","v":7.7647938207318319},{"$f":"B54/$B$55","v":0.3981927485541083},null,null,null,null,null,null,{"$f":"J53+1","v":35}]
["= Total equity value",{"$f":"SUM(B52:B54)","v":19.500088459488143},{"$f":"SUM(C52:C54)","v":1},null,null,null,null,null,null,{"$f":"J54+1","v":36}]
["II: Price to book: Book value growth model [RIV]",null,"% of Value"]
["Anchor: Current book value",{"$f":"B14","v":10},{"$f":"B57/$B$61","v":0.51281818648029298},null,null,null,null,null,null,{"$f":"J55+1","v":37}]
["+ Until horizon: PV of residual change in book value for the first 2 years",{"$f":"NPV(B15,C25:F25)","v":2.1844303426423926},{"$f":"B58/$B$61","v":0.11202156068063968},null,null,null,null,null,null,{"$f":"J57+1","v":38}]
["[Note: Residual change in book value = residual earnings]"]
["+ Beyond the horizon: PV of residual change in book value beyond 2 years",{"$f":"F35/(1+B15)^B13","v":7.3156581168457508},{"$f":"B60/$B$61","v":0.37516025283906734},null,null,null,null,null,null,{"$f":"J58+1","v":39}]
["= Total equity value",{"$f":"B57+B58+B60","v":19.500088459488143},{"$f":"SUM(C57:C60)","v":1},null,null,null,null,null,null,{"$f":"J60+1","v":40}]
["Intermediate computations: You may skip these",{"$f":"B12","v":0},1,2,3,4,5,6]
["Change in earnings [et - et-1]",null,null,{"$f":"D21-C21","v":0.10499999999999998},{"$f":"E21-D21","v":0.11235000000000017},{"$f":"F21-E21","v":0.12021450000000011},{"$f":"G21-F21","v":7.7548301957509169E-2},{"$f":"H21-G21","v":8.0510034513763262E-2},null,{"$f":"J61+1","v":41}]
["Reinvestment at the end of the previous year: et-1 - dt-1",null,{"$f":"C21-C22","v":0.60000000000000009},0.64200000000000002,0.68694000000000011,0.73502580000000006,0.76604512078300391,0.79824913458850921,null,{"$f":"J63+1","v":42}]
["Change in earnings if reinvestment yields cost of equity [=re*(et-1 - dt-1)]",null,null,{"$f":"C64*$B$15","v":5.4000000000000006E-2},{"$f":"D64*$B$15","v":5.7779999999999998E-2},{"$f":"E64*$B$15","v":6.1824600000000007E-2},{"$f":"F64*$B$15","v":6.6152322E-2},{"$f":"G64*$B$15","v":6.894406087047035E-2},null,{"$f":"J64+1","v":43}]
["Residual change in earnings [=(et - et-1) - re*(et-1 - dt-1)]"]
["[i.e., actual change in earnings - change in earnings from normal return on reinvestment]",null,null,{"$f":"D63-D65","v":5.0999999999999976E-2},{"$f":"E63-E65","v":5.4570000000000174E-2},{"$f":"F63-F65","v":5.8389900000000106E-2},{"$f":"G63-G65","v":1.139597995750917E-2},{"$f":"H63-H65","v":1.1565973643292912E-2},null,{"$f":"J65+1","v":44}]
["Not used: Just for checking"]
["Change in residual earnings ['(et - re*bt-1)- (et-1 - re*bt-2)]",null,null,{"$f":"D25-C25","v":5.0999999999999934E-2},{"$f":"E25-D25","v":5.4570000000000007E-2},{"$f":"F25-E25","v":5.8389900000000106E-2},{"$f":"G25-F25","v":1.1395979957509184E-2},{"$f":"H25-G25","v":1.1565973643292926E-2},null,{"$f":"J67+1","v":45}]
["Check: Residual change in earnings = Change in residual earnings",{"$f":"B67-B69","v":0},0,0,1.6653345369377348E-16,0,-1.3877787807814457E-17,-1.3877787807814457E-17,null,{"$f":"J69+1","v":46}]
["Growth rate of residual change in earnings",null,null,null,null,null,{"$f":"IF(AND(F69=0,G69=0),0,G69/F69-1)","v":-0.80482960310757234},{"$f":"IF(AND(G69=0,H69=0),0,H69/G69-1)","v":1.491698707944189E-2},null,47]
["Value added each year: Capitalized forthcoming residual change in earnings",null,{"$f":"D67/$B$15","v":0.56666666666666643},{"$f":"E67/$B$15","v":0.60633333333333528},{"$f":"F67/$B$15","v":0.64877666666666789},{"$f":"G67/$B$15","v":0.12662199952787967},{"$f":"H67/$B$15","v":0.12851081825881014},null,null,48]
["PV of value added each year based on earnings forecasts until the horizon",{"$f":"NPV(B15,C72:E72)","v":1.5311909360073555},null,null,null,null,null,null,null,49]
["Capitalized value of value added per year beyond the horizon",null,0,0,{"$f":"F72/(B15-B16)","v":1.686426724269138},null,null,null,null,50]
["PV of value added each year based on earnings forecasts beyond the horizon",{"$f":"NPV(B15,C74:E74)","v":1.3022308568141234},null,null,null,null,null,null,null,51]
["End of intermediate computation"]
["III. Price to forward earnings: Earning growth Model [AEG]",null,"% of Value"]
["Anchor: Capitalized value of forthcoming earnings for Y1",{"$f":"C21/B15","v":16.666666666666668},{"$f":"B78/$B$81","v":0.85469697746715489},null,null,null,null,null,null,{"$f":"J75+1","v":52}]
["+ Until horizon: PV of value based on earnings forecasts until the horizon",{"$f":"B73","v":1.5311909360073555},{"$f":"B79/$B$81","v":7.8522255895835419E-2},null,null,null,null,null,null,{"$f":"J78+1","v":53}]
["+ Beyond the horizon: PV of value based on earnings forecasts beyond the horizon",{"$f":"E74/(1+B15)^(B13-1)","v":1.3022308568141236},{"$f":"B80/$B$81","v":6.6780766637009686E-2},null,null,null,null,null,null,{"$f":"J79+1","v":54}]
["= Total Equity Value",{"$f":"SUM(B78:B80)","v":19.500088459488147},{"$f":"SUM(C78:C80)","v":1},null,null,null,null,null,null,{"$f":"J80+1","v":55}]
[]
["Valuation ratios",{"$f":"B12","v":0}]
["Dividend yield = Forthcoming dividends/Current stock price",{"$f":"C22/B49","v":4.6153636783226364E-2},null,null,null,null,null,null,null,{"$f":"J81+1","v":56}]
["P/B ratio = Current stock price/current book value",{"$f":"B61/B14","v":1.9500088459488143},null,null,null,null,null,null,null,{"$f":"J84+1","v":57}]
["Forward P/E ratio = Current stock price/Forthcoming earnings",{"$f":"B61/C17","v":13.000058972992095},null,null,null,null,null,null,null,{"$f":"J85+1","v":58}]
["Forward ROE = Forthcoming earnings/Current book value",{"$f":"C31","v":0.15},null,null,null,null,null,null,null,{"$f":"J86+1","v":59}]
["Check: P/B - P/E * ROE = 0",{"$f":"B85-B86*B87","v":0},null,null,null,null,null,null,null,{"$f":"J87+1","v":60}]
[]
["Reverse engineering: Inferring cost of equity OR growth",{"$f":"B12","v":0}]
["Current stock price",{"$f":"C17*13","v":19.5},null,null,null,null,null,null,null,{"$f":"J88+1","v":61}]
["Difference = Current stock price - Computed present value",{"$f":"B91-B61","v":-8.8459488143399767E-5},null,null,null,null,null,null,null,{"$f":"J91+1","v":62}]
["Choose one of the following from the drop down box on the right and then CLICK INFER"]
["Infer cost of equity",null,2,null,null,null,null,null,null,{"$f":"J92+1","v":63}]
["Infer growth rate",null,null,null,null,null,null,null,null,{"$f":"J94+1","v":64}]
[{"$f":"IF(C94=1,\"Inferred cost of equity\", \"Inferred growth rate\")&\" that sets Price = Present value \"","v":"Inferred growth rate that sets Price = Present value "},{"$f":"IF(C94=1,B15,B16)","v":1.4916987079438632E-2},null,null,null,null,null,null,null,{"$f":"J95+1","v":65}]