{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Contents","Cash Flow Model from ROIC","Amazon Stats","Valuation from Driver Formula","Scenario Name"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":2,"defined_names":10,"comments":0,"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":"Contents","index":0,"n_rows":15,"n_cols":7,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
[null,null,"Go to Charts by Pressing Shift and Enter on Menu Item "]
[null,null,null,null,null,null,"Instructions at www.edbodmer.com\t"]
[null,null,"Sheet Tab","Description","Author",null,"Questions: e-mail edwardbodmer@gmail.com"]
[null,1,"Contents"]
[null,2,"Cash Flow Model from ROIC"]
[null,3,"Amazon Stats"]
[null,4,"Valuation from Driver Formula"]
[null,5,"2004-2014 Financial Statements"]
[]
[null," File Name: ",null,{"$f":"File_name()","v":"C:\\Users\\Monika Lewenski\\Courses\\Chapter 1. Models and Analysis\\B. Project Finance Models and Exercises\\D. Resolving Circular Refereces for Funding, Scultping and DSRA\\Current Circular Reference Template1.xlsm"}]
[]
[null," Last Save Date ",null,{"$f":"lastsaved()","v":"10/5/2018 8:41:13 PM"}]
[null," Last Save By",null,{"$f":"last_save_by()","v":"Elvis Presley"}]
[]
[null," Colour Codes "]

{"sheet":"Cash Flow Model from ROIC","index":1,"n_rows":160,"n_cols":56,"rows_kept":160,"cut":false,"hidden":false,"merged":[]}
["Contents"]
[null,"Period",null,null,null,2017,2017,2018,2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029,2030,2031,2032,2033,2034,2035,2036,2037]
[null,"Holding Period",null,null,19,{"$f":"F2+E3","v":2036},{"$f":"G2<=$F$3","v":true},{"$f":"H2<=$F$3","v":true},true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,false]
[null,"Terminal Period",null,null,{"$f":"E3","v":19},{"$f":"F3","v":2036},{"$f":"G2=$F$4","v":false},{"$f":"H2=$F$4","v":false},false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,false,true,false]
[]
[null,"Results: Enterprise Value",null,null,{"$f":"E141","v":-664327488.87995958},null,"EV",{"$f":"E6","v":-664327488.87995958},null,"Terminal EV",{"$f":"E137","v":0}]
[null,"Less: EV to Equity Bridge",null,null,{"$f":"E32","v":18739210.799999952},null,"EBITDA",{"$f":"LOOKUP(G2,2:2,83:83)","v":13069958.11799451},null,"Terminal EBITA",{"$f":"LOOKUP(F3,2:2,83:83)","v":1001961322.8654548}]
[null,"Equity Value",null,null,{"$f":"E6-E7","v":-683066699.67995954},null,"EV/EBITDA",{"$f":"H6/H7","v":-50.828585897710269},null,"EV/EBITDA",{"$f":"K6/K7","v":0}]
[null,"Equity Value per Share",null,null,{"$f":"E8/E33","v":-1400.4729972525517}]
[]
["Assumptions",null,null,null,2009,{"$f":"F2","v":2017},2018,2030,{"$f":"F3+1","v":2037}]
[null,null,null,null,null,null,null,null,"Terminal"]
[null,"ROIC Projected",null,null,null,{"$f":"E30","v":7.9366200141418264E-2},0.15,0.18,0.1]
[null,"Cost of Capital",null,null,null,7.0000000000000007E-2,{"$f":"F14","v":7.0000000000000007E-2},{"$f":"G14","v":7.0000000000000007E-2},{"$f":"H14","v":7.0000000000000007E-2}]
[null,"Growth",null,null,null,0.35,0.3,0.25,0.04]
[]
[null,"Partial Year Factor",null,null,0.5]
[null,"Depreciation Life",null,null,10,null,null,null,null,null,"Instructions at www.edbodmer.com\t"]
[null,"Tax Rate",null,null,{"$f":"'Amazon Stats'!E15","v":0.26000954426151279},null,null,null,null,null,"Questions: e-mail edwardbodmer@gmail.com"]
[null,"Working Capital/Opening Plant Net",null,null,0.5]
[null,"Terminal EV/EBITDA",null,null,15]
[]
[null,null,null,null,"Amount","Pct"]
[null,"Opening Net Plant",null,null,{"$f":"'Amazon Stats'!J23+'Amazon Stats'!J25","v":65589999},{"$f":"E24/E26","v":1.2206917522192775}]
[null,"Opening WC and Other",null,null,{"$f":"'Amazon Stats'!J24","v":-11858171.222222224},{"$f":"E25/E24","v":-0.18079236778494576}]
[null,"Opening Invested Capital",null,null,{"$f":"SUM(E24:E25)","v":53731827.777777776},{"$f":"E26/E26","v":1}]
[]
[null,"Historic Year NOPAT ",null,null,{"$f":"'Amazon Stats'!J20","v":3280377.6902887137}]
[null,"Intial Year NOPAT Forecast",null,null,{"$f":"'Valuation from Driver Formula'!D8","v":4264490.9973753281}]
[null,"Initial Year ROIC (Opening Balance)",null,null,{"$f":"E29/E26","v":7.9366200141418264E-2}]
[]
[null,"EV to Equity Bridge",null,null,{"$f":"'Amazon Stats'!E19","v":18739210.799999952}]
[null,"Shares in Valuation Year",null,null,{"$f":"'Amazon Stats'!E20","v":487740}]
[]
["Stable Depreciation Analysis"]
[null,null,null,null,null,null,null,"Data Table with Alternative Growth"]
[null,null,null,null,null,null,null,null,{"$f":"G2","v":2017},{"$f":"H11","v":2030},{"$f":"I11","v":2037}]
[null,"Growth",null,null,{"$f":"G15","v":0.3},null,null,null,{"$f":"G15","v":0.3},{"$f":"H15","v":0.25},{"$f":"I15","v":0.04}]
[null,"Stable Cap Exp/Depreciation",null,null,{"$f":"LOOKUP(E18+1,43:43,49:49)","v":3.2346343959338255},null,null,{"$f":"E39","v":3.2346343959338255},3.2346343959338255,2.8007256240041785,1.2329094433013656]
[null,"Depreciation to Net Plant",null,null,{"$f":"LOOKUP(E18+1,43:43,57:57)","v":0.13425014872494787},null,null,{"$f":"E40","v":0.13425014872494787},0.13425014872494787,0.13883292194403732,0.1717405676344492]
[]
[]
[null,null,null,null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21,21]
[null,"Opening Balance",null,null,null,null,{"$f":"F47","v":0},{"$f":"G47","v":1},2.2999999999999998,3.99,6.1870000000000012,9.0431000000000026,12.756030000000004,17.582839000000007,23.85769070000001,32.014997910000012,42.619497283000015,55.405346467900024,72.026950408270039,93.635035530751054,121.72554618997637,158.2432100469693,205.71617306106009,267.43102497937809,347.66033247319154,451.95843221514906,587.54596187969389,{"$f":"AA47","v":763.80975044360207},{"$f":"AB47","v":992.95267557668285},{"$f":"AC47","v":1290.8384782496878},{"$f":"AD47","v":1678.0900217245942},{"$f":"AE47","v":2181.5170282419726},{"$f":"AF47","v":2835.9721367145648},{"$f":"AG47","v":3686.7637777289342},{"$f":"AH47","v":4792.7929110476143},{"$f":"AI47","v":6230.6307843618988},{"$f":"AJ47","v":8099.8200196704684},{"$f":"AK47","v":10529.766025571609},{"$f":"AL47","v":13688.695833243093},{"$f":"AM47","v":17795.304583216021},{"$f":"AN47","v":23133.895958180827},{"$f":"AO47","v":30074.064745635078},{"$f":"AP47","v":39096.284169325598},{"$f":"AQ47","v":50825.169420123282},{"$f":"AR47","v":66072.720246160272},{"$f":"AS47","v":85894.536320008352},{"$f":"AT47","v":111662.89721601087},{"$f":"AU47","v":145161.76638081414},{"$f":"AV47","v":188710.29629505839},{"$f":"AW47","v":245323.38518357594},{"$f":"AX47","v":318920.40073864872},{"$f":"AY47","v":414596.52096024336},{"$f":"AZ47","v":538975.4772483164},{"$f":"BA47","v":700668.12042281136},{"$f":"BB47","v":910868.55654965481},{"$f":"BC47","v":1184129.1235145512}]
[null,"Add: Capital Expenditures",null,null,1,null,{"$f":"E45","v":1},{"$f":"G45*(1+$E$38)","v":1.3},1.6900000000000002,2.1970000000000005,2.856100000000001,3.7129300000000014,4.8268090000000017,6.2748517000000028,8.1573072100000044,10.604499373000007,13.785849184900009,17.921603940370012,23.298085122481016,30.287510659225322,39.373763856992923,51.185893014090802,66.541660918318044,86.504159193813464,112.45540695195751,146.19202903754478,190.04963774880824,{"$f":"AA45*(1+$E$38)","v":247.06452907345073},321.18388779548593,417.53905413413173,542.80077037437127,705.64100148668263,917.33330193268739,1192.5332925124937,1550.2932802662419,2015.3812643461147,2619.9956436499492,3405.9943367449341,4427.7926377684144,5756.1304290989392,7482.969557828621,9727.8604251772067,12646.21855273037,16440.084118549483,21372.109354114327,27783.742160348625,36118.864808453218,46954.524250989183,61040.881526285943,79353.14598417173,103159.08977942326,134106.81671325024,174338.86172722533,226640.52024539295,294632.67631901085,383022.47921471414]
[null,"Less: Retirements",null,null,null,null,{"$f":"IF(G43>=$E$18,OFFSET(G45,0,-$E$18),0)","v":0},0,0,0,0,0,0,0,0,0,1,1.3,1.6900000000000002,2.1970000000000005,2.856100000000001,3.7129300000000014,4.8268090000000017,6.2748517000000028,8.1573072100000044,10.604499373000007,13.785849184900009,{"$f":"IF(AB43>=$E$18,OFFSET(AB45,0,-$E$18),0)","v":17.921603940370012},23.298085122481016,30.287510659225322,39.373763856992923,51.185893014090802,66.541660918318044,86.504159193813464,112.45540695195751,146.19202903754478,190.04963774880824,247.06452907345073,321.18388779548593,417.53905413413173,542.80077037437127,705.64100148668263,917.33330193268739,1192.5332925124937,1550.2932802662419,2015.3812643461147,2619.9956436499492,3405.9943367449341,4427.7926377684144,5756.1304290989392,7482.969557828621,9727.8604251772067,12646.21855273037,16440.084118549483,21372.109354114327,27783.742160348625]
[null,"Closing Balance",null,null,null,0,{"$f":"G44+G45-G46","v":1},{"$f":"H44+H45-H46","v":2.2999999999999998},3.99,6.1870000000000012,9.0431000000000026,12.756030000000004,17.582839000000007,23.85769070000001,32.014997910000012,42.619497283000015,55.405346467900024,72.026950408270039,93.635035530751054,121.72554618997637,158.2432100469693,205.71617306106009,267.43102497937809,347.66033247319154,451.95843221514906,587.54596187969389,763.80975044360207,{"$f":"AB44+AB45-AB46","v":992.95267557668285},1290.8384782496878,1678.0900217245942,2181.5170282419726,2835.9721367145648,3686.7637777289342,4792.7929110476143,6230.6307843618988,8099.8200196704684,10529.766025571609,13688.695833243093,17795.304583216021,23133.895958180827,30074.064745635078,39096.284169325598,50825.169420123282,66072.720246160272,85894.536320008352,111662.89721601087,145161.76638081414,188710.29629505839,245323.38518357594,318920.40073864872,414596.52096024336,538975.4772483164,700668.12042281136,910868.55654965481,1184129.1235145512,1539367.8605689167]
[null,"Depreciation",null,null,{"$f":"1/E18","v":0.1},null,{"$f":"G44*$E$48","v":0},0.1,0.22999999999999998,0.39900000000000002,0.61870000000000014,0.90431000000000028,1.2756030000000005,1.7582839000000008,2.3857690700000012,3.2014997910000016,4.2619497283000021,5.540534646790003,7.2026950408270043,9.3635035530751054,12.172554618997637,15.82432100469693,20.57161730610601,26.743102497937812,34.766033247319157,45.195843221514906,58.754596187969391,{"$f":"AB44*$E$48","v":76.380975044360213},99.295267557668296,129.08384782496879,167.80900217245943,218.15170282419729,283.59721367145647,368.67637777289343,479.27929110476146,623.06307843618993,809.98200196704693,1052.976602557161,1368.8695833243094,1779.5304583216021,2313.3895958180829,3007.4064745635078,3909.62841693256,5082.5169420123284,6607.2720246160279,8589.4536320008356,11166.289721601088,14516.176638081415,18871.02962950584,24532.338518357596,31892.040073864875,41459.652096024336,53897.547724831646,70066.812042281133,91086.85565496549,118412.91235145513]
[null,"Cap Exp to Depreciation",null,null,null,null,{"$f":"IF(G48,G45/G48)","v":false},{"$f":"IF(H48,H45/H48)","v":13},7.3478260869565233,5.5062656641604022,4.6162922256343952,4.105815483628402,3.7839429665812951,3.568736368455629,3.4191520514598674,3.3123536046484161,3.2346343959338255,3.2346343959338255,3.2346343959338251,3.2346343959338255,3.234634395933826,3.2346343959338255,3.2346343959338255,3.234634395933826,3.234634395933826,3.2346343959338264,3.234634395933826,{"$f":"IF(AB48,AB45/AB48)","v":3.234634395933826},3.2346343959338251,3.2346343959338251,3.2346343959338251,3.2346343959338246,3.2346343959338246,3.2346343959338246,3.2346343959338251,3.2346343959338251,3.2346343959338251,3.2346343959338255,3.2346343959338251,3.2346343959338255,3.2346343959338255,3.2346343959338251,3.2346343959338255,3.2346343959338255,3.2346343959338251,3.2346343959338255,3.2346343959338255,3.2346343959338251,3.2346343959338255,3.2346343959338251,3.2346343959338251,3.2346343959338255,3.2346343959338255,3.234634395933826,3.2346343959338255,3.234634395933826]
[]
[null,"Accum Depr"]
[null,"Opening Balance",null,null,null,null,{"$f":"F55","v":0},{"$f":"G55","v":0},0.1,0.32999999999999996,0.72899999999999998,1.3477000000000001,2.2520100000000003,3.5276130000000006,5.2858969000000009,7.6716659700000021,10.873165761000003,14.135115489300006,18.375650136090009,23.88834517691701,31.05484872999212,40.371303348989755,52.482694353686682,68.227502659792691,88.695753457730504,115.30447949504966,149.89582334356456,{"$f":"AA55","v":194.86457034663394},{"$f":"AB55","v":253.32394145062415},{"$f":"AC55","v":329.3211238858114},{"$f":"AD55","v":428.11746105155481},{"$f":"AE55","v":556.55269936702132},{"$f":"AF55","v":723.51850917712784},{"$f":"AG55","v":940.57406193026623},{"$f":"AH55","v":1222.7462805093462},{"$f":"AI55","v":1589.5701646621503},{"$f":"AJ55","v":2066.4412140607956},{"$f":"AK55","v":2686.3735782790341},{"$f":"AL55","v":3492.2856517627447},{"$f":"AM55","v":4539.9713472915682},{"$f":"AN55","v":5901.9627514790382},{"$f":"AO55","v":7672.5515769227495},{"$f":"AP55","v":9974.317049999574},{"$f":"AQ55","v":12966.612164999447},{"$f":"AR55","v":16856.595814499284},{"$f":"AS55","v":21913.574558849068},{"$f":"AT55","v":28487.646926503789},{"$f":"AU55","v":37033.941004454929},{"$f":"AV55","v":48144.123305791407},{"$f":"AW55","v":62587.36029752883},{"$f":"AX55","v":81363.56838678749},{"$f":"AY55","v":105772.63890282375},{"$f":"AZ55","v":137504.43057367089},{"$f":"BA55","v":178755.75974577217},{"$f":"BB55","v":232382.48766950384},{"$f":"BC55","v":302097.23397035501}]
[null,"Add: Depreciation Expense",null,null,null,null,{"$f":"G48","v":0},{"$f":"H48","v":0.1},0.22999999999999998,0.39900000000000002,0.61870000000000014,0.90431000000000028,1.2756030000000005,1.7582839000000008,2.3857690700000012,3.2014997910000016,4.2619497283000021,5.540534646790003,7.2026950408270043,9.3635035530751054,12.172554618997637,15.82432100469693,20.57161730610601,26.743102497937812,34.766033247319157,45.195843221514906,58.754596187969391,{"$f":"AB48","v":76.380975044360213},99.295267557668296,129.08384782496879,167.80900217245943,218.15170282419729,283.59721367145647,368.67637777289343,479.27929110476146,623.06307843618993,809.98200196704693,1052.976602557161,1368.8695833243094,1779.5304583216021,2313.3895958180829,3007.4064745635078,3909.62841693256,5082.5169420123284,6607.2720246160279,8589.4536320008356,11166.289721601088,14516.176638081415,18871.02962950584,24532.338518357596,31892.040073864875,41459.652096024336,53897.547724831646,70066.812042281133,91086.85565496549,118412.91235145513]
[null,"Less: Retirements",null,null,null,null,{"$f":"G46","v":0},{"$f":"H46","v":0},0,0,0,0,0,0,0,0,1,1.3,1.6900000000000002,2.1970000000000005,2.856100000000001,3.7129300000000014,4.8268090000000017,6.2748517000000028,8.1573072100000044,10.604499373000007,13.785849184900009,{"$f":"AB46","v":17.921603940370012},23.298085122481016,30.287510659225322,39.373763856992923,51.185893014090802,66.541660918318044,86.504159193813464,112.45540695195751,146.19202903754478,190.04963774880824,247.06452907345073,321.18388779548593,417.53905413413173,542.80077037437127,705.64100148668263,917.33330193268739,1192.5332925124937,1550.2932802662419,2015.3812643461147,2619.9956436499492,3405.9943367449341,4427.7926377684144,5756.1304290989392,7482.969557828621,9727.8604251772067,12646.21855273037,16440.084118549483,21372.109354114327,27783.742160348625]
[null,"Closing Balance",null,null,null,null,{"$f":"G52+G53-G54","v":0},{"$f":"H52+H53-H54","v":0.1},0.32999999999999996,0.72899999999999998,1.3477000000000001,2.2520100000000003,3.5276130000000006,5.2858969000000009,7.6716659700000021,10.873165761000003,14.135115489300006,18.375650136090009,23.88834517691701,31.05484872999212,40.371303348989755,52.482694353686682,68.227502659792691,88.695753457730504,115.30447949504966,149.89582334356456,194.86457034663394,{"$f":"AB52+AB53-AB54","v":253.32394145062415},329.3211238858114,428.11746105155481,556.55269936702132,723.51850917712784,940.57406193026623,1222.7462805093462,1589.5701646621503,2066.4412140607956,2686.3735782790341,3492.2856517627447,4539.9713472915682,5901.9627514790382,7672.5515769227495,9974.317049999574,12966.612164999447,16856.595814499284,21913.574558849068,28487.646926503789,37033.941004454929,48144.123305791407,62587.36029752883,81363.56838678749,105772.63890282375,137504.43057367089,178755.75974577217,232382.48766950384,302097.23397035501,392726.40416146151]
[null,"Net Plant",null,null,null,null,{"$f":"G44-G52","v":0},{"$f":"H44-H52","v":1},2.1999999999999997,3.66,5.4580000000000011,7.6954000000000029,10.504020000000004,14.055226000000006,18.571793800000009,24.343331940000009,31.746331522000013,41.270230978600019,53.651300272180031,69.74669035383404,90.670697459984254,117.87190669797954,153.23347870737342,199.2035223195854,258.96457901546103,336.65395272009937,437.65013853612936,{"$f":"AB44-AB52","v":568.9451800969681},739.6287341260587,961.51735436387639,1249.9725606730394,1624.9643288749512,2112.4536275374371,2746.1897157986677,3570.0466305382679,4641.0606196997487,6033.3788056096728,7843.3924472925755,10196.410181480349,13255.333235924452,17231.933206701789,22401.513168712328,29121.967119326022,37858.557255123837,49216.124431660988,63980.961761159284,83175.250289507079,108127.82537635921,140566.17298926698,182736.02488604712,237556.83235186123,308823.88205741963,401471.04667464551,521912.36067703919,678486.06888015103,882031.88954419619]
[null,"Depreciation to Net Plant",null,null,null,null,{"$f":"IF(G56,G48/G56)","v":false},{"$f":"IF(H56,H48/H56)","v":0.1},0.10454545454545455,0.10901639344262296,0.11335654085745694,0.1175130597499805,0.12143950601769608,0.12509823036641318,0.12846196203190668,0.13151444505998058,0.13425014872494787,0.13425014872494787,0.13425014872494787,0.13425014872494784,0.13425014872494784,0.13425014872494787,0.13425014872494784,0.13425014872494787,0.13425014872494787,0.13425014872494787,0.13425014872494784,{"$f":"IF(AB56,AB48/AB56)","v":0.13425014872494787},0.13425014872494787,0.13425014872494784,0.13425014872494784,0.13425014872494787,0.13425014872494784,0.13425014872494787,0.13425014872494787,0.13425014872494787,0.13425014872494787,0.13425014872494784,0.13425014872494787,0.13425014872494787,0.13425014872494787,0.13425014872494784,0.13425014872494787,0.13425014872494784,0.13425014872494787,0.13425014872494787,0.13425014872494787,0.13425014872494787,0.13425014872494784,0.13425014872494784,0.13425014872494787,0.13425014872494784,0.13425014872494787,0.13425014872494784,0.13425014872494787,0.13425014872494787]
[]
[null,"Investment Balance"]
[null,null,"Growth Rate Change"]
[null,null,"Cap Exp to Depreciation (Interpolate)",null,null,null,{"$f":"interpolate_look_up(G2,37:37,39:39)","v":3.2346343959338255},{"$f":"interpolate_look_up(H2,37:37,39:39)","v":3.1989931145900314},3.1637445517981155,3.1288843803325923,3.0944083206481272,3.0603121403541693,3.0265916536953665,2.9932427210377104,2.9602612483603417,2.9276431867529547,2.8953845319187406,2.8634813236828087,2.831929645506019,2.8007256240041785,2.4909495874914445,2.2154365262502198,1.9703967621386071,1.752459776772592,1.5586278500947954,1.3862348267787743,1.2329094433013656]
[null,null,"Net Plant Depreciation Rate (Interpolate)",null,null,null,{"$f":"interpolate_look_up(G2,37:37,40:40)","v":0.13425014872494787},{"$f":"interpolate_look_up(H2,37:37,40:40)","v":0.1345972343143976},0.13494521724666256,0.13529409984170118,0.13564388442546974,0.13599457332993808,0.13634616889310494,0.13669867345901371,0.13705208937776792,0.13740641900554704,0.13776166470462201,0.13811782884337126,0.13847491379629623,0.13883292194403732,0.14311648735713089,0.14753221834299582,0.15208419275196036,0.15677661425277092,0.16161381621460666,0.16660026570887004,0.1717405676344492]
[]
["NOPAT and Free Cash Flow"]
[null,"Plant Balance"]
[null,null,"Opening Balance",null,null,null,{"$f":"F69","v":65589999},{"$f":"G69","v":85266998.700000018},{"$f":"H69","v":110504187.82327749},142769971.74940228,183891363.66274622,236133737.92006946,302296349.94493538,385826275.15477461,490953763.15176725,622852370.0659287,787827618.28225517,993538334.22049069,1249255225.2348294,1566161668.2061663,1957702085.2577076,2375435514.2981186,2801389220.5608568,3214823867.5341926,3594070518.6891541,3918550316.6950531,4170696587.2655253]
[null,null,"Add: Capital Expenditures",null,null,null,{"$f":"G68*G61","v":28482466.820619188},36713891.32658682,47177795.558602974,60437326.725604407,77186113.1368348,98275518.962109193,124746874.39520058,157869427.99628279,199184845.94198897,250559161.95620361,314243160.13307643,392942248.60953265,489896952.59530252,608975217.68535149,697912874.7742244,776406977.21787488,839481665.16637683,883255852.52597117,905331250.2756635,904977794.52544487,883105662.81866288]
[null,null,"Less: Depreciation",null,null,null,{"$f":"G66*G62","v":8805467.1206191815},11476702.203309339,14912011.632478183,19315934.81226049,24943738.879511572,32112906.937243268,41216949.185361311,52741939.999290109,67286239.027827501,85583913.739877045,108532444.19484097,137225357.59519392,172990509.62396562,217434800.63380995,280179445.73381352,350453270.9551366,426047018.19304132,504009201.37100923,580851452.26976502,652831523.95497274,716277799.32804143]
[null,null,"Closing Plant Balance",null,null,{"$f":"E24","v":65589999},{"$f":"G66+G67-G68","v":85266998.700000018},{"$f":"H66+H67-H68","v":110504187.82327749},142769971.74940228,183891363.66274622,236133737.92006946,302296349.94493538,385826275.15477461,490953763.15176725,622852370.0659287,787827618.28225517,993538334.22049069,1249255225.2348294,1566161668.2061663,1957702085.2577076,2375435514.2981186,2801389220.5608568,3214823867.5341926,3594070518.6891541,3918550316.6950531,4170696587.2655253,4337524450.7561474]
[]
[null,null,"WC and Other as Pct of Plant",null,{"$f":"F25","v":-0.18079236778494576},null,{"$f":"$E$71","v":-0.18079236778494576},{"$f":"$E$71","v":-0.18079236778494576},-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576,-0.18079236778494576]
[null,null,"WC and Other Balance Closing",null,null,{"$f":"E25","v":-11858171.222222224},{"$f":"G71*G69","v":-15415622.588888895},-19978313.766722709,-25811721.241164252,-33246155.051790424,-42691177.592479192,-54652872.879291423,-69754445.838877693,-88760693.313137457,-112606954.76468451,-142433220.51562333,-179624147.92883331,-225855810.13792053,-283150076.32901341,-353937595.41126668,-429460611.15040725,-506469790.27242094,-581215619.02306342,-649780519.05988038,-708443990.03974771,-754030111.32432699,-784191315.77730024]
[null,null,"Change in WC and Other",null,null,null,{"$f":"G72-F72","v":-3557451.3666666709},{"$f":"H72-G72","v":-4562691.1778338142},-5833407.4744415432,-7434433.8106261715,-9445022.540688768,-11961695.286812231,-15101572.95958627,-19006247.474259764,-23846261.451547056,-29826265.750938818,-37190927.413209975,-46231662.209087223,-57294266.191092879,-70787519.082253277,-75523015.73914057,-77009179.122013688,-74745828.750642478,-68564900.036816955,-58663470.979867339,-45586121.284579277,-30161204.452973247]
[]
[null,null,"Invested Capital Including WC and Other",null,null,{"$f":"E26","v":53731827.777777776},{"$f":"G66+F72","v":53731827.777777776},{"$f":"H66+G72","v":69851376.111111119},90525874.056554779,116958250.50823803,150645208.6109558,193442560.32759026,247643477.06564397,316071829.31589693,402193069.83862978,510245415.3012442,645394397.76663184,813914186.29165745,1023399415.0969089,1283011591.8771529,1603764489.8464408,1945974903.1477113,2294919430.2884359,2633608248.5111294,2944289999.6292739,3210106326.6553054,3416666475.9411983]
[]
[null,null,"ROIC on Opening Investment",null,null,null,{"$f":"interpolate_look_up(G2,11:11,13:13)","v":7.9366200141418264E-2},0.15,0.15229642057495968,0.15462799813296668,0.15699527090881585,0.15939878537739166,0.1618390963798198,0.16431676725154981,0.16683236995239864,0.1693864851985851,0.17197970259678602,0.17461262078024406,0.17728584754695997,0.18,0.16550266995287607,0.15217296534183686,0.13991684477066929,0.12864784100515489,0.11828645094459014,0.10875957472544207,0.1]
[]
[null,null,"NOPAT",null,null,null,{"$f":"G77*G75","v":4264490.9973753281},{"$f":"H77*H75","v":10477706.416666668},13786766.588232897,18085020.141222879,23650585.336992085,30834509.1565107,40078396.55266045,51935901.212471813,67098823.019609191,86428677.486570105,110994736.58553717,142119689.15860611,181434232.68451861,230942086.53788751,265427305.04519817,296124771.49278098,321097885.68885988,338808015.22432423,348269614.60779542,349129798.91048205,341666647.59411985]
[null,null,"Tax Rate",null,{"$f":"E19","v":0.26000954426151279},null,{"$f":"$E$80","v":0.26000954426151279},{"$f":"$E$80","v":0.26000954426151279},0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279,0.26000954426151279]
[null,null,"EBIT =NOPAT/(1-t)",null,null,null,{"$f":"G79/(1-G80)","v":5762900},{"$f":"H79/(1-H80)","v":14159245.346225778},18631005.955981065,24439531.619599875,31960662.67285778,41668793.046443865,54160693.887144089,70184555.503004983,90675254.659395128,116797016.52410774,149994821.85857102,192056111.07075042,245184557.82440186,312087925.9279291,358690173.61894226,400173771.42690003,433921658.31167954,457854574.47042948,470640684.4940092,471803110.70642322,461717641.01085234]
[null,null,"Add: Depreciation",null,null,null,{"$f":"G68","v":8805467.1206191815},11476702.203309339,14912011.632478183,19315934.81226049,24943738.879511572,32112906.937243268,41216949.185361311,52741939.999290109,67286239.027827501,85583913.739877045,108532444.19484097,137225357.59519392,172990509.62396562,217434800.63380995,280179445.73381352,350453270.9551366,426047018.19304132,504009201.37100923,580851452.26976502,652831523.95497274,716277799.32804143]
[null,null,"EBITDA",null,null,null,{"$f":"G79+G82","v":13069958.11799451},21954408.619976006,28698778.220711082,37400954.953483373,48594324.216503657,62947416.093753964,81295345.738021761,104677841.21176192,134385062.04743668,172012591.22644717,219527180.78037813,279345046.75380003,354424742.3084842,448376887.1716975,545606750.77901173,646578042.44791758,747144903.88190126,842817216.59533346,929121066.87756038,1001961322.8654548,1057944446.9221613]
[null,null,"Less: Cap Exp",null,null,null,{"$f":"G67","v":28482466.820619188},36713891.32658682,47177795.558602974,60437326.725604407,77186113.1368348,98275518.962109193,124746874.39520058,157869427.99628279,199184845.94198897,250559161.95620361,314243160.13307643,392942248.60953265,489896952.59530252,608975217.68535149,697912874.7742244,776406977.21787488,839481665.16637683,883255852.52597117,905331250.2756635,904977794.52544487,883105662.81866288]
[null,null,"Less: Change in WC",null,null,null,{"$f":"G73","v":-3557451.3666666709},{"$f":"H73","v":-4562691.1778338142},-5833407.4744415432,-7434433.8106261715,-9445022.540688768,-11961695.286812231,-15101572.95958627,-19006247.474259764,-23846261.451547056,-29826265.750938818,-37190927.413209975,-46231662.209087223,-57294266.191092879,-70787519.082253277,-75523015.73914057,-77009179.122013688,-74745828.750642478,-68564900.036816955,-58663470.979867339,-45586121.284579277,-30161204.452973247]
[null,null,"Less: Taxes on EBIT",null,null,null,{"$f":"G81*G80","v":1498409.0026246721},{"$f":"H81*H80","v":3681538.9295591102},4844239.3677481674,6354511.4783769948,8310077.335865695,10834283.889933163,14082297.334483637,18248654.290533174,23576431.639785942,30368339.037537631,39000085.27303385,49936421.912144296,63750325.139883257,81145839.390041605,93262868.573744074,104048999.93411906,112823772.62281968,119046559.24610524,122371069.88621376,122673311.79594117,120050993.41673249]
[null,null,"FCF",null,null,null,{"$f":"G83-G84-G85-G86","v":-13353466.33858268},{"$f":"H83-H84-H85-H86","v":-13878330.458336109},-17489849.231198516,-21956449.439871859,-27456843.71550807,-34200691.47147616,-42432253.032076187,-52433993.600794271,-64529954.082791179,-79088644.016355261,-96525137.212522179,-117301961.5587897,-141928269.2356087,-170956650.82144231,-170045976.82981616,-156868755.58206266,-130414705.15665276,-90920295.139926001,-39917782.304449543,19896337.828648031,84948995.139739215]
[]
[null,null,"FCF Growth",null,null,null,null,{"$f":"H87/G87-1","v":3.9305458706023089E-2},{"$f":"I87/H87-1","v":0.26022717816847507},0.25538243066760069,0.25051383151447792,0.2456162778884543,0.2406840682582474,0.23571080614449991,0.2306892847813462,0.22561134810177386,0.22046772217464139,0.21524780949570244,0.2099394362171576,0.20452853925559333,-5.3269292961132253E-3,-7.749210827223163E-2,-0.16863810978325133,-0.30283709163998407,-0.56095850499587341,-1.4984329459212025,3.269579450818541]
[null,null,"NOPAT Growth",null,null,null,null,{"$f":"H79/G79-1","v":1.4569653032719252},0.3158191344531831,0.31176661514372572,0.30774448423660261,0.30375247450143128,0.29979032094298486,0.29585776078220505,0.29195453343738609,0.28808038050551033,0.28423504574374991,0.28041827505111261,0.27662981645025497,0.27286942006944259,0.1493240968949725,0.11565300880538842,8.4333080512609904E-2,5.5154924167346353E-2,2.7926137984683352E-2,2.4698804219693837E-3,-2.1376437472974796E-2]
[null,null,"Investment Growth",null,null,null,null,{"$f":"H69/G69-1","v":0.29597839150022143},0.29198697860867928,0.28802549590415594,0.28409368018573677,0.28019127045395686,0.27631800789210503,0.2724736358476898,0.2686578998140563,0.2648705474121642,0.2611113283725166,0.25737999451724103,0.25367629974232542,0.24999999999999978,0.21337946778833894,0.17931604697279968,0.14758200821896628,0.11796809616380255,9.0281978697581255E-2,6.4346824767363309E-2,4.0000000000000258E-2]
[null,null,"Cap Exp Growth",null,null,null,null,{"$f":"H67/G67-1","v":0.28899970489946081},0.28501212630758621,0.28105448781579478,0.27712652624879808,0.27322798063282838,0.26935859217693481,0.26551810425445455,0.26170626238462713,0.25792281421437546,0.25416750950022915,0.25044010009041573,0.24674033990708377,0.24306798492869586,0.14604478886171313,0.11246977277649939,8.1239208043337729E-2,5.2144304248644691E-2,2.4993208577741344E-2,-3.9041593903998972E-4,-2.4168694347082131E-2]
[null,null,"Working Capital Growth",null,null,null,null,{"$f":"H72/G72-1","v":0.29597839150022143},{"$f":"I72/H72-1","v":0.29198697860867906},0.28802549590415594,0.28409368018573677,0.28019127045395664,0.27631800789210526,0.2724736358476898,0.2686578998140563,0.26487054741216443,0.26111132837251638,0.25737999451724103,0.25367629974232542,0.24999999999999978,0.21337946778833916,0.17931604697279968,0.14758200821896605,0.11796809616380277,9.0281978697581255E-2,6.4346824767363309E-2,4.0000000000000258E-2]
[]
[null,"Valuation"]
[null,null,"Period",null,null,null,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21]
[null,null,"PV Factor End of Year",null,{"$f":"F14","v":7.0000000000000007E-2},null,{"$f":"1/(1+$E$97)^G96","v":0.93457943925233644},{"$f":"1/(1+$E$97)^H96","v":0.87343872827321156},0.81629787689085187,0.7628952120475252,0.71298617948366838,0.66634222381651254,0.62274974188459109,0.5820091045650384,0.54393374258414806,0.5083492921347178,0.47509279638758667,0.44401195924073528,0.41496444788853759,0.3878172410173249,0.36244601964235967,0.33873459779659787,0.31657439046411018,0.29586391632159825,0.27650833301083949,0.2584190028138687,0.24151308674193336]
[null,null,"PV of Cash Flow Over Explicit Period",null,{"$f":"SUMPRODUCT(G97:AA97*G87:AA87*G3:AA3)","v":-642229624.17633426}]
[null,null,"PV of Explicit Cash Flow with Half Year Adj",null,{"$f":"E98*(1+E97)^part_yr_fac","v":-664327488.87995958}]
[]
[null,null,"EV to Equity Bridge (Net Debt)",null,{"$f":"E32","v":18739210.799999952}]
[null,null,"Shares at Valuation Date",null,{"$f":"E33","v":487740}]
[]
[null,"Vauation from Constant Growth Method (Gordon's Method)"]
[null,null,"Terminal Period from Line 2",null,{"$f":"F3","v":2036}]
[null,null,"Terminal WACC",null,{"$f":"LOOKUP(E105+1,11:11,14:14)","v":7.0000000000000007E-2},{"$f":"_xlfn.FORMULATEXT(E106)","v":"=LOOKUP(E105+1,11:11,14:14)"}]
[null,null,"Terminal Growth Rate",null,{"$f":"LOOKUP(E105+1,11:11,15:15)","v":0.04},{"$f":"_xlfn.FORMULATEXT(E107)","v":"=LOOKUP(E105+1,11:11,15:15)"}]
[null,null,"Terminal PV Factor",null,{"$f":"LOOKUP(E105,2:2,97:97)","v":0.2584190028138687}]
[null,null,"Terminal Free Cash Flow",null,{"$f":"LOOKUP(E105+1,2:2,87:87)","v":84948995.139739215},"Terminal + 1 CF"]
[null,null,"Terminal Value without in Terminal Period",null,null,"Terminal CF/(WACC-g)"]
[null,null,"PV of Terminal Value",null,null,"PV Factor x Terminal Value"]
[null,null,"PV of Terminal Value adjusted for 1/2 year",null,null,"PV Terminal x (1+WACC)^partial_year "]
[]
[null,null,"Total Enterprise Value Including Explicit",null,null,"PV of Terminal Value + PV Explicit Above"]
[null,null,"Less: EV to Equity Bridge",null,{"$f":"E101","v":18739210.799999952}]
[null,null,"Total Equity Value",null,null,"EV - EV to Equity Bridge"]
[null,null,"Shares",null,{"$f":"E102","v":487740}]
[null,null,"Equity Valuation",null,null,"Equity Value/Shares"]
[]
[null,"Value Driver with EV/EBITDA Input"]
[null,null,"Terminal Period",null,{"$f":"E105","v":2036}]
[null,null,"Terminal EBITDA",null,{"$f":"LOOKUP(E121,2:2,83:83)","v":1001961322.8654548}]
[null,null,"Terminal EV/EBITDA",null,{"$f":"E21","v":15}]
[null,null,"Terminal Value",null,null,"EV/EBITDA Ratio x EBITDA"]
[null,null,"PV of Terminal Value",null,null,"Multiply by PV Factor Above"]
[null,null,"PV Adjusted",null,null,"PV Terminal x (1+WACC)^partial_year "]
[null,null,"Less: EV to Equity Bridge",null,{"$f":"E115","v":18739210.799999952}]
[null,null,"Total Equity Value",null,null,"EV - EV to Equity Bridge"]
[null,null,"Shares",null,{"$f":"E117","v":487740}]
[null,null,"Equity Valuation",null,null,"Equity Value/Shares"]
[]
[]
[null,"Value Driver with Terminal Value with Book Value"]
[null,null,"BV Terminal",null,{"$f":"SUMIF(2:2,F3+1,75:75)","v":3416666475.9411983}]
[null,null,"Terminal ROIC",null,{"$f":"LOOKUP(F3+1,11:11,13:13)","v":0.1}]
[null,null,"Terminal Growth",null,{"$f":"LOOKUP(F3+1,11:11,15:15)","v":0.04}]
[null,null,"Value Driver Formula ",null,null,"Value = NOPAT x (1-g/ROIC)/(WACC-g)",null,null,null,"NOPAT= Book Value of Invested Capital x ROIC"]
[null,null,"PV Factor",null,{"$f":"LOOKUP(F3,2:2,97:97)","v":0.2584190028138687}]
[null,null,"PV of Terminal EV with Partial Year Factor",null,null,"Value x PV Factor x (1+WACC) ^partial_year"]
[]
[null,null,"Total Enterprise Value with Explicit CF",null,null,"PV of Terminal Value + PV Explicit Above"]
[null,null,"Less: EV to Equity Bridge",null,{"$f":"E127","v":18739210.799999952}]
[null,null,"Total Equity Value",null,null,"EV - EV to Equity Bridge"]
[null,null,"Shares",null,{"$f":"E129","v":487740}]
[null,null,"Equity Valuation",null,null,"Equity Value/Shares"]
[]
[]
[null,"Value Driver with Terminal NOPAT"]
[null,null,"NOPAT in Terminal Year",null,{"$f":"LOOKUP(F3,2:2,79:79)","v":349129798.91048205}]
[null,null,"Terminal Growth",null,{"$f":"E136","v":0.04}]
[null,null,"NOPAT with Growth",null,{"$f":"E149*(1+E150)","v":363094990.86690134}]
[null,null,"Value Driver Formula from NOPAT",null,null,"Value = NOPAT x (1-g/ROIC)/(WACC-g)"]
[null,null,"PV Factor",null,{"$f":"E138","v":0.2584190028138687}]
[null,null,"PV of Terminal Value",null,null,"Value x PV Factor x (1+WACC) ^partial_year"]
[]
[null,null,"Total Enterprise Value",null,null,"PV of Terminal Value + PV Explicit Above"]
[null,null,"Less: EV to Equity Bridge",null,{"$f":"E142","v":18739210.799999952}]
[null,null,"Total Equity Value",null,null,"EV - EV to Equity Bridge"]
[null,null,"Shares",null,{"$f":"E144","v":487740}]
[null,null,"Equity Valuation",null,null,"Equity Value/Shares"]

{"sheet":"Amazon Stats","index":2,"n_rows":26,"n_cols":14,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
["Contents","Amazon Stats","Amazon"]
[null,null,null,null,"1 Year","5 Year",null,"Yahoo",null,"EPS"]
[null,null,"Expected Growth",null,0.46200000000000002,0.45929999999999999,null,"Current Quarters Estimate:",null,3.05,null,null,null,"Instructions at www.edbodmer.com\t"]
[null,null,"Past Growth",null,null,0.66120000000000001,null,"Current Year's Estimate:",null,17.170000000000002,null,null,null,"Questions: e-mail edwardbodmer@gmail.com"]
[null,null,null,null,null,null,null,"Next Fiscal Year Estimate:",null,25.4]
[null,null,"Dividend Yield",null,"N/A"]
[null,null,"Dividend Payout",null,0,null,null,"Market Watch"]
[null,null,"Historic ROE",null,0.12909999999999999,null,null,"Fiscal year is January-December. All values USD millions."]
[null,null,"Forward ROE",null,0.35304759254105206,null,null,null,null,"Revenue","EPS"]
[null,null,"Stock Price",null,2016,null,null,"Current Qtr. (Sep 2018)",null,57040000,3.11]
[null,null,null,null,null,null,null,"Next Qtr. (Dec 2018)",null,73800000,5.99]
[null,null,null,null,null,null,null,"Current Year (2018)",null,234900000,17.350000000000001]
[null,null,"ROIC Reported",null,6.0599999999999994E-2,null,null,"Next Year (2019)",null,286480000,25.37]
[null,null,"Depreciation Rate",null,0.15939915414904476]
[null,null,"Tax Rate",null,0.26000954426151279,null,null,"Growth Rate - 2019",null,0.21958280119199669,0.46224783861671459]
[null,null,"EBITDA",null,20630000]
[null,null,"Enterprise Value",null,995380032,null,null,"Historic",null,"2017-12-31"]
[null,null,null,null,null,null,null,"EBITDA",null,26065649.291266561]
[null,null,"Net Debt",null,18739210.799999952,null,null,"Operating Income",null,4433000]
[null,null,"Shares",null,487740,null,null,"NOPAT",null,3280377.6902887137]
[null,null,"Net Debt to Mkt Cap",null,1.9188221087230548E-2]
[null,null,null,null,null,null,null,"Invested Capital",null,53731827.777777776]
[null,null,"Invested Capital/Share",null,110.16489887599495,null,null,"Net Plant",null,48870000]
[null,null,null,null,null,null,null,"Working Capital",null,-11858171.222222224]
[null,null,null,null,null,null,null,"Goodwill",null,16719999]
[null,null,null,null,null,null,null,"ROIC",null,6.1050923185706346E-2]

{"sheet":"Valuation from Driver Formula","index":3,"n_rows":20,"n_cols":6,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Contents","Valuation from Driver Formula"]
[]
[null,null,"Growth",0.05]
[null,null,"ROIC",0.3]
[null,null,"WACC",7.0000000000000007E-2]
[]
[null,null,"NOPAT (2017)",{"$f":"'Amazon Stats'!J20","v":3280377.6902887137}]
[null,null,"NOPAT Estimated",{"$f":"D7*1.3","v":4264490.9973753281}]
[null,null,"Invested Capital (EOY 2009)",{"$f":"'Amazon Stats'!J22","v":53731827.777777776}]
[]
[null,null,"Total Invested Capital",{"$f":"D9","v":53731827.777777776}]
[null,null,"Less: Equity"]
[null,null,"EV to Equity Bridge",{"$f":"'Amazon Stats'!E19","v":18739210.799999952}]
[]
[null,null,"Enterprise Value",{"$f":"D7*(1-D3/D4)/(D5-D3)","v":136682403.76202968},null,"NOPAT x (1-g/ROIC)/(WACC-g)"]
[null,null,"Less: EV to Equity Bridge",{"$f":"D13","v":18739210.799999952}]
[null,null,"Equity Value",{"$f":"D15-D16","v":117943192.96202973}]
[]
[null,null,"Number of Shares",{"$f":"'Amazon Stats'!E20","v":487740}]
[null,null,"Equity Value per Share",{"$f":"D17/D19","v":241.81570706120007}]

{"sheet":"Scenario Name","index":4,"n_rows":7,"n_cols":15,"rows_kept":7,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,"No","Name","Equity Value Growth Method","Equity Value Multiple Method","Equity Value Value Driver Method","Equity Value Value Driver Method BV","WACC","Explicit Period","2018 Growth","2030 Growth","Terminal Growth","2018 ROIC","2030 ROIC","Terminal ROIC"]