{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Enterprise"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":61,"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":"Enterprise","index":0,"n_rows":79,"n_cols":8,"rows_kept":79,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Inputs",0,1,2,3,4]
["Horizon year ",4]
["WACC",0.11852681449024892]
["Perpetual growth in residual enterprise profit, after tax",0.02]
["Enterprise profit after tax: EPAT","NA",{"$f":"B18*11%","v":11},{"$f":"C18*11%","v":11.88},{"$f":"D18*11%","v":12.830400000000001},{"$f":"E18*11%","v":13.856832000000002}]
["Net enterprise assets [NEA]",100,{"$f":"B18*(1+8%)","v":108},{"$f":"C18*(1+8%)","v":116.64000000000001},{"$f":"D18*(1+8%)","v":125.97120000000002},{"$f":"E18*(1+8%)","v":136.04889600000004}]
[]
["Valuation computations",{"$f":"B13","v":0},1,2,3,4,5,6]
["Enterprise profit after tax",{"$f":"B17","v":"NA"},{"$f":"C17","v":11},11.88,12.830400000000001,13.856832000000002,{"$f":"G25+G24","v":15.029826543210845}]
["Net enterprise assets",100,108,116.64000000000001,125.97120000000002,136.04889600000004]
["Enterprise cash flows","NA",{"$f":"C21-(C22-B22)","v":3},{"$f":"D21-(D22-C22)","v":3.239999999999986},{"$f":"E21-(E22-D22)","v":3.4991999999999912},{"$f":"F21-(F22-E22)","v":3.7791359999999852}]
["Charge for enterprise capital",null,{"$f":"B22*$B$15","v":11.852681449024892},{"$f":"C22*$B$15","v":12.800895964946884},{"$f":"D22*$B$15","v":13.824967642142635},{"$f":"E22*$B$15","v":14.930965053514047},{"$f":"F22*$B$15","v":16.125442257795171}]
["Residual enterprise profit after tax","NA",{"$f":"C21-C24","v":-0.8526814490248924},{"$f":"D21-D24","v":-0.92089596494688308},{"$f":"E21-E24","v":-0.99456764214263416},{"$f":"F21-F24","v":-1.0741330535140445},{"$f":"F25*(1+$B$16)","v":-1.0956157145843255},{"$f":"G25*(1+$B$16)","v":-1.1175280288760121}]
["Change in residual enterprise profit after tax","NA","NA",{"$f":"D25-C25","v":-6.8214515921990682E-2},{"$f":"E25-D25","v":-7.3671677195751073E-2},{"$f":"F25-E25","v":-7.9565411371410377E-2},{"$f":"G25-F25","v":-2.1482661070280962E-2},{"$f":"H25-G25","v":-2.1912314291686608E-2}]
["Growth rates and returns",{"$f":"B20","v":0},{"$f":"C20","v":1},2,3,4,5,6]
["Enterprise profit after-tax growth rate",null,null,{"$f":"D21/C21-1","v":8.0000000000000071E-2},{"$f":"E21/D21-1","v":8.0000000000000071E-2},{"$f":"F21/E21-1","v":8.0000000000000071E-2},{"$f":"G21/F21-1","v":8.465098972195384E-2}]
["Net enterprise assets growth rate",null,null,{"$f":"D22/C22-1","v":8.0000000000000071E-2},8.0000000000000071E-2,8.0000000000000071E-2]
["Enterprise free cash flow growth rate",null,null,7.9999999999995408E-2,8.0000000000001847E-2,7.9999999999998517E-2]
["Residual enterprise profit after tax growth rate",null,null,{"$f":"IF(AND(C25=0,D25=0),0,D25/C25-1)","v":7.9999999999999183E-2},{"$f":"IF(AND(D25=0,E25=0),0,E25/D25-1)","v":8.0000000000000515E-2},{"$f":"IF(AND(E25=0,F25=0),0,F25/E25-1)","v":7.9999999999999627E-2},{"$f":"IF(AND(F25=0,G25=0),0,G25/F25-1)","v":2.0000000000000018E-2},{"$f":"IF(AND(G25=0,H25=0),0,H25/G25-1)","v":2.0000000000000018E-2}]
["Forward return on net enterprise assets: RNEA","NA",{"$f":"C21/B22","v":0.11},{"$f":"D21/C22","v":0.11},{"$f":"E21/D22","v":0.11},{"$f":"F21/E22","v":0.11},{"$f":"G21/F22","v":0.11047371191612493}]
["Intermediate computations: You may skip these"]
["Terminal value computation",{"$f":"B20","v":0},1,2,3,4,5,6]
["Book value of net enterprise assets at the end of the year",null,null,null,null,{"$f":"F22","v":136.04889600000004}]
["PV of residual enterprise profit after tax at the end of the year",null,null,null,null,{"$f":"G25/(B15-H31)","v":-11.119975006324369}]
["= Terminal enterprise value",null,null,null,null,{"$f":"SUM(F35:F36)","v":124.92892099367567}]
["Capitalized change in enterprise cash flows",null,0,{"$f":"(D23-C23)/$B$15","v":2.0248582654664236},{"$f":"(E23-D23)/$B$15","v":2.1868469267039092},{"$f":"(F23-E23)/$B$15","v":2.3617946808401236}]
["End of intermediate computation"]
[]
[]
["Valuation",{"$f":"B20","v":0}]
["Baseline: Discounted enterprise cash flows"]
["Anchor: None",0,{"$f":"B44/$B$47","v":0}]
["Until horizon: PV of enterprise free cash flows until horizon",{"$f":"NPV(B15,C23:F23)","v":10.186718526680039},{"$f":"B45/$B$47","v":0.11318524776089881}]
["Beyond the horizon: PV of horizon Value",{"$f":"F37/(1+B15)^B14","v":79.813689902864098},{"$f":"B46/$B$47","v":0.88681475223910122}]
["= Total enterprise value",{"$f":"SUM(B44:B46)","v":90.000408429544137}]
["Connecting to valuation anchors",{"$f":"B20","v":0}]
["I: Enterprise cash flow yield: ECF growth model"]
["Anchor: Capitalized forthcoming enterprise cash flows",{"$f":"C23/B15","v":25.310728318331773},{"$f":"B50/$B$53","v":0.28122903840093133}]
["Until horizon: PV of capitalized change in enterprise cash flows until the horizon",{"$f":"NPV(B15,C38:F38)","v":4.6900587176838382},{"$f":"B51/$B$53","v":5.211152704218449E-2}]
["Beyond the horizon: PV of capitalized change in enterprise cash flows beyond the horizon",{"$f":"B47-SUM(B50:B51)","v":59.999621393528528},{"$f":"B52/$B$53","v":0.66665943455688415}]
["= Total enterprise value",{"$f":"SUM(B50:B52)","v":90.000408429544137},{"$f":"SUM(C50:C52)","v":1}]
["II: EV to Net enterprise assets: Book value growth model [RIV]",null,"% of Value"]
["Anchor: Book value of net enterprise assets",{"$f":"B18","v":100},{"$f":"B55/$B$59","v":1.1111060687939427}]
["Until horizon: PV of residual change in net enterprise assets until horizon",{"$f":"NPV(B15,C25:F25)","v":-2.8953419713794233},{"$f":"B56/$B$59","v":-3.217032035633495E-2}]
["[Note: Residual change in net enterprise assets = Residual enterprise earnings]"]
["Beyond the horizon: PV of residual change in net enterprise assets beyond the horizon",{"$f":"F36/(1+B15)^B14","v":-7.1042495990764376},{"$f":"B58/$B$59","v":-7.8935748437607631E-2}]
["= Total Enterprise Value",{"$f":"B55+B56+B58","v":90.000408429544137}]
["III. EV to forward EPAT: Earnings growth model [AEG]"]
["Anchor: Capitalized enterprise profit after tax for the forthcoming year",{"$f":"C17/B15","v":92.806003833883167},{"$f":"B61/$B$64","v":1.0311731408034148}]
["Until horizon: PV of capitalized change in residual enterprise profit after tax",{"$f":"NPV(B15,D26:F26)/B15","v":-1.491043245482353},{"$f":"B62/$B$64","v":-1.6567071988896589E-2}]
["Beyond the horizon: PV of Horizon Value",{"$f":"(((G26/(B15-B16))/(1+B15)^(B14-1)))/B15","v":-1.314552158856674},{"$f":"B63/$B$64","v":-1.4606068814518293E-2}]
["= Total Enterprise Value",{"$f":"SUM(B61:B63)","v":90.000408429544137}]
[]
["Valuation ratios",{"$f":"B20","v":0}]
["Enterprise cash flow yield",{"$f":"C23/B47","v":3.333318206381828E-2}]
["EV/NEA ratio",{"$f":"B59/B18","v":0.90000408429544132}]
["Forward EV/EPAT ratio",{"$f":"B59/C17","v":8.1818553117767401}]
["RNEA or ROIC",{"$f":"C21/B22","v":0.11}]
["Check: EV/NEA = EV/EPAT * EPAT/NEA",{"$f":"B68-B69*B70","v":0}]
[]
["Reverse engineering: Inferring wacc OR growth",{"$f":"B20","v":0}]
["Current enterprise value",90]
["Difference = Current EV - Computed present value",{"$f":"B74-B47","v":-4.0842954413733423E-4}]
["Choose one of the following from the drop down box on the right and then CLICK INFER"]
["Infer wacc",null,2]
["Infer growth rate"]
[{"$f":"IF(C77=1,\"Inferred wacc\", \"Inferred growth rate\")&\" that sets EV = Present value \"","v":"Inferred growth rate that sets EV = Present value "},{"$f":"IF(C77=1,B15,B16)","v":0.02}]