{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Contents","Cash Flow Model from ROIC","Amazon Stats","Valuation from Driver Formula","Scenario Output","Sheet2"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":2,"defined_names":10,"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":"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":118,"n_cols":56,"rows_kept":118,"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":"E108","v":3007892895.8043976},null,"EV",{"$f":"E6","v":3007892895.8043976},null,"Terminal EV",{"$f":"E104","v":16314336037.663172}]
[null,"Less: EV to Equity Bridge",null,null,{"$f":"E31","v":18739210.799999952},null,"EBITDA",{"$f":"LOOKUP(G2,2:2,82:82)","v":13069958.11799451},null,"Terminal EBITA",{"$f":"LOOKUP(F3,2:2,82:82)","v":1451788058.247829}]
[null,"Equity Value",null,null,{"$f":"E6-E7","v":2989153685.0043974},null,"EV/EBITDA",{"$f":"H6/H7","v":230.13791388230837},null,"EV/EBITDA",{"$f":"K6/K7","v":11.237408893797511}]
[null,"Equity Value per Share",null,null,{"$f":"E8/E32","v":6128.5801554196851}]
[]
["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":"E29","v":7.9366200141418264E-2},0.12,0.18,0.12]
[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,null,null,null,"Amount","Pct"]
[null,"Opening Net Plant",null,null,{"$f":"'Amazon Stats'!J23+'Amazon Stats'!J25","v":65589999},{"$f":"E23/E25","v":1.2206917522192775}]
[null,"Opening WC and Other",null,null,{"$f":"'Amazon Stats'!J24","v":-11858171.222222224},{"$f":"E24/E23","v":-0.18079236778494576}]
[null,"Opening Invested Capital",null,null,{"$f":"SUM(E23:E24)","v":53731827.777777776},{"$f":"E25/E25","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":"E28/E25","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}]
[null,"Growth",null,null,{"$f":"G15","v":0.3},null,null,null,{"$f":"G15","v":0.3},{"$f":"H15","v":0.25}]
[null,"Stable Cap Exp/Depreciation",null,null,{"$f":"LOOKUP(E18+1,42:42,48:48)","v":3.2346343959338255},null,null,{"$f":"E38","v":3.2346343959338255},3.2346343959338255,2.8007256240041785]
[null,"Depreciation to Net Plant",null,null,{"$f":"LOOKUP(E18+1,42:42,56:56)","v":0.13425014872494787},null,null,{"$f":"E39","v":0.13425014872494787},0.13425014872494787,0.13883292194403732]
[]
[]
[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":"F46","v":0},{"$f":"G46","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":"AA46","v":763.80975044360207},{"$f":"AB46","v":992.95267557668285},{"$f":"AC46","v":1290.8384782496878},{"$f":"AD46","v":1678.0900217245942},{"$f":"AE46","v":2181.5170282419726},{"$f":"AF46","v":2835.9721367145648},{"$f":"AG46","v":3686.7637777289342},{"$f":"AH46","v":4792.7929110476143},{"$f":"AI46","v":6230.6307843618988},{"$f":"AJ46","v":8099.8200196704684},{"$f":"AK46","v":10529.766025571609},{"$f":"AL46","v":13688.695833243093},{"$f":"AM46","v":17795.304583216021},{"$f":"AN46","v":23133.895958180827},{"$f":"AO46","v":30074.064745635078},{"$f":"AP46","v":39096.284169325598},{"$f":"AQ46","v":50825.169420123282},{"$f":"AR46","v":66072.720246160272},{"$f":"AS46","v":85894.536320008352},{"$f":"AT46","v":111662.89721601087},{"$f":"AU46","v":145161.76638081414},{"$f":"AV46","v":188710.29629505839},{"$f":"AW46","v":245323.38518357594},{"$f":"AX46","v":318920.40073864872},{"$f":"AY46","v":414596.52096024336},{"$f":"AZ46","v":538975.4772483164},{"$f":"BA46","v":700668.12042281136},{"$f":"BB46","v":910868.55654965481},{"$f":"BC46","v":1184129.1235145512}]
[null,"Add: Capital Expenditures",null,null,1,null,{"$f":"E44","v":1},{"$f":"G44*(1+$E$37)","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":"AA44*(1+$E$37)","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(G42>=$E$18,OFFSET(G44,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(AB42>=$E$18,OFFSET(AB44,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":"G43+G44-G45","v":1},{"$f":"H43+H44-H45","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":"AB43+AB44-AB45","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":"G43*$E$47","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":"AB43*$E$47","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(G47,G44/G47)","v":false},{"$f":"IF(H47,H44/H47)","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(AB47,AB44/AB47)","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":"F54","v":0},{"$f":"G54","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":"AA54","v":194.86457034663394},{"$f":"AB54","v":253.32394145062415},{"$f":"AC54","v":329.3211238858114},{"$f":"AD54","v":428.11746105155481},{"$f":"AE54","v":556.55269936702132},{"$f":"AF54","v":723.51850917712784},{"$f":"AG54","v":940.57406193026623},{"$f":"AH54","v":1222.7462805093462},{"$f":"AI54","v":1589.5701646621503},{"$f":"AJ54","v":2066.4412140607956},{"$f":"AK54","v":2686.3735782790341},{"$f":"AL54","v":3492.2856517627447},{"$f":"AM54","v":4539.9713472915682},{"$f":"AN54","v":5901.9627514790382},{"$f":"AO54","v":7672.5515769227495},{"$f":"AP54","v":9974.317049999574},{"$f":"AQ54","v":12966.612164999447},{"$f":"AR54","v":16856.595814499284},{"$f":"AS54","v":21913.574558849068},{"$f":"AT54","v":28487.646926503789},{"$f":"AU54","v":37033.941004454929},{"$f":"AV54","v":48144.123305791407},{"$f":"AW54","v":62587.36029752883},{"$f":"AX54","v":81363.56838678749},{"$f":"AY54","v":105772.63890282375},{"$f":"AZ54","v":137504.43057367089},{"$f":"BA54","v":178755.75974577217},{"$f":"BB54","v":232382.48766950384},{"$f":"BC54","v":302097.23397035501}]
[null,"Add: Depreciation Expense",null,null,null,null,{"$f":"G47","v":0},{"$f":"H47","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":"AB47","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":"G45","v":0},{"$f":"H45","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":"AB45","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":"G51+G52-G53","v":0},{"$f":"H51+H52-H53","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":"AB51+AB52-AB53","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":"G43-G51","v":0},{"$f":"H43-H51","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":"AB43-AB51","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(G55,G47/G55)","v":false},{"$f":"IF(H55,H47/H55)","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(AB55,AB47/AB55)","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,36:36,38:38)","v":3.2346343959338255},{"$f":"interpolate_look_up(H2,36:36,38:38)","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.8007256240041785,2.8007256240041785,2.8007256240041785,2.8007256240041785,2.8007256240041785,2.8007256240041785,2.8007256240041785]
[null,null,"Net Plant Depreciation Rate (Interpolate)",null,null,null,{"$f":"interpolate_look_up(G2,36:36,39:39)","v":0.13425014872494787},{"$f":"interpolate_look_up(H2,36:36,39:39)","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.13883292194403732,0.13883292194403732,0.13883292194403732,0.13883292194403732,0.13883292194403732,0.13883292194403732,0.13883292194403732]
[]
["NOPAT and Free Cash Flow"]
[null,"Plant Balance"]
[null,null,"Opening Balance",null,null,null,{"$f":"F68","v":65589999},{"$f":"G68","v":85266998.700000018},{"$f":"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,2447127606.5721345,3058909508.2151685,3823636885.2689605,4779546106.5862007,5974432633.2327509,7468040791.5409384]
[null,null,"Add: Capital Expenditures",null,null,null,{"$f":"G67*G60","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,761219022.10668933,951523777.6333617,1189404722.0417023,1486755902.5521276,1858444878.1901598,2323056097.7376995,2903820122.1721244]
[null,null,"Less: Depreciation",null,null,null,{"$f":"G65*G61","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,271793500.79226243,339741875.99032801,424677344.98791009,530846681.23488754,663558351.5436095,829447939.42951179,1036809924.2868898]
[null,null,"Closing Plant Balance",null,null,{"$f":"E23","v":65589999},{"$f":"G65+G66-G67","v":85266998.700000018},{"$f":"H65+H66-H67","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,2447127606.5721345,3058909508.2151685,3823636885.2689605,4779546106.5862007,5974432633.2327509,7468040791.5409384,9335050989.4261723]
[]
[null,null,"WC and Other as Pct of Plant",null,{"$f":"F24","v":-0.18079236778494576},null,{"$f":"$E$70","v":-0.18079236778494576},{"$f":"$E$70","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":"E24","v":-11858171.222222224},{"$f":"G70*G68","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,-442421994.26408339,-553027492.83010435,-691284366.03763032,-864105457.54703796,-1080131821.9337974,-1350164777.4172468,-1687705971.7715583]
[null,null,"Change in WC and Other",null,null,null,{"$f":"G71-F71","v":-3557451.3666666709},{"$f":"H71-G71","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,-88484398.852816701,-110605498.56602097,-138256873.20752597,-172821091.50940764,-216026364.3867594,-270032955.48344946,-337541194.35431147]
[]
[null,null,"Invested Capital Including WC and Other",null,null,{"$f":"E25","v":53731827.777777776},{"$f":"G65+F71","v":53731827.777777776},{"$f":"H65+G71","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,2004705612.3080511,2505882015.3850641,3132352519.2313299,3915440649.0391626,4894300811.298954,6117876014.1236916]
[]
[null,null,"ROIC on Opening Investment",null,null,null,{"$f":"interpolate_look_up(G2,11:11,13:13)","v":7.9366200141418264E-2},0.12,0.12412392997582998,0.12838958327203956,0.13280183036403859,0.13736570910639981,0.14208643048502503,0.14696938456699066,0.15202014665486649,0.15724448365253377,0.16264836064977206,0.16823794773313211,0.17401962703087193,0.18,0.16986997033838963,0.16031003790425208,0.15128811879856344,0.14277393473812633,0.13473891143922609,0.1271560827200458,0.12]
[]
[null,null,"NOPAT",null,null,null,{"$f":"G76*G74","v":4264490.9973753281},{"$f":"H76*H74","v":8382165.1333333338},11236427.25239661,15016221.04297949,20005959.439107358,26572374.470756959,35186777.689157516,46452882.233520284,61141449.460439458,80233276.86511679,104972340.76928958,136931252.33259061,178091584.51877657,230942086.53788751,272431426.31997746,321374432.69597054,379110176.03875917,447218294.15753949,527562210.85643363,622340118.81831706,734145121.69484293]
[null,null,"Tax Rate",null,{"$f":"E19","v":0.26000954426151279},null,{"$f":"$E$79","v":0.26000954426151279},{"$f":"$E$79","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":"G78/(1-G79)","v":5762900},{"$f":"H78/(1-H79)","v":11327396.276980622},15184556.996999385,20292452.323582705,27035429.016637843,35909077.292407192,47550312.867268294,62774974.830130436,82624646.015768155,108424745.53951713,141856344.16666967,185044619.52274442,240667407.44790655,312087925.9279291,368155324.4468531,434295375.30352193,512317656.39520192,604356840.94226551,712931101.69906604,841011007.63149869,992100798.05987382]
[null,null,"Add: Depreciation",null,null,null,{"$f":"G67","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,271793500.79226243,339741875.99032801,424677344.98791009,530846681.23488754,663558351.5436095,829447939.42951179,1036809924.2868898]
[null,null,"EBITDA",null,null,null,{"$f":"G78+G81","v":13069958.11799451},19858867.336642671,26148438.884874791,34332155.85523998,44949698.318618931,58685281.408000231,76403726.874518827,99194822.232810393,128427688.48826696,165817190.60499382,213504784.96413055,274156609.92778456,351082094.14274216,448376887.1716975,544224927.11223984,661116308.68629861,803787521.02666926,978064975.39242697,1191120562.400043,1451788058.247829,1770955045.9817328]
[null,null,"Less: Cap Exp",null,null,null,{"$f":"G66","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,761219022.10668933,951523777.6333617,1189404722.0417023,1486755902.5521276,1858444878.1901598,2323056097.7376995,2903820122.1721244]
[null,null,"Less: Change in WC",null,null,null,{"$f":"G72","v":-3557451.3666666709},{"$f":"H72","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,-88484398.852816701,-110605498.56602097,-138256873.20752597,-172821091.50940764,-216026364.3867594,-270032955.48344946,-337541194.35431147]
[null,null,"Less: Taxes on EBIT",null,null,null,{"$f":"G80*G79","v":1498409.0026246721},{"$f":"H80*H79","v":2945231.143647288},3948129.7446027752,5276231.2806032151,7029469.5775304846,9336702.8216502313,12363535.178110776,16322092.596610151,21483196.555328697,28191468.674400341,36884003.397380091,48113367.190153807,62575822.929129995,81145839.390041605,95723898.126875654,112920942.6075514,133207480.35644275,157138546.78472602,185368890.84263238,218670888.81318164,257955676.36503091]
[null,null,"FCF",null,null,null,{"$f":"G82-G83-G84-G85","v":-13353466.33858268},{"$f":"H82-H83-H84-H85","v":-15237563.955757622},-19144078.943889417,-23946968.340341471,-29820861.855057586,-36965245.088946961,-45605109.739206262,-55990450.88582278,-68394092.557503656,-83107174.274671316,-100431451.15311599,-120667343.66281468,-144096415.19059747,-170956650.82144231,-224233594.26850843,-292722912.98859352,-380567808.16394979,-493008382.43501902,-636666842.2459898,-819905972.81960273,-1053279558.201111]
[]
[null,null,"FCF Growth",null,null,null,null,{"$f":"H86/G86-1","v":0.14109427240859151},{"$f":"I86/H86-1","v":0.25637398467854777},0.25088119467795478,0.24528756338733926,0.23957668522842157,0.23372940256367247,0.22772319167754018,0.22153137678735102,0.21512211313860696,0.20845705595990416,0.20148959591201576,0.19416248685519699,0.18640460691070349,0.31164007478546041,0.3054373674181603,0.3000957262910926,0.29545477010664412,0.29139151570086264,0.28781007336143727,0.28463457166795814]
[null,null,"NOPAT Growth",null,null,null,null,{"$f":"H78/G78-1","v":0.96557224261754016},0.34051609263968552,0.33638751052089888,0.33228988717242625,0.32822295034816817,0.32418643007913173,0.32018005865408683,0.31620357060040383,0.31225670266502892,0.3083391937956439,0.30445078512196844,0.30059121993722915,0.29676024367978382,0.17965257179437222,0.17965257179437266,0.17965257179437266,0.17965257179437244,0.17965257179437244,0.17965257179437266,0.17965257179437222]
[null,null,"Investment Growth",null,null,null,null,{"$f":"H68/G68-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.25,0.25000000000000022,0.25,0.25,0.25,0.25,0.25]
[null,null,"Cap Exp Growth",null,null,null,null,{"$f":"H66/G66-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.25,0.25,0.25000000000000022,0.24999999999999978,0.25000000000000022,0.24999999999999978,0.25]
[null,null,"Working Capital Growth",null,null,null,null,{"$f":"H71/G71-1","v":0.29597839150022143},{"$f":"I71/H71-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.25,0.25000000000000022,0.24999999999999978,0.25,0.25,0.25,0.24999999999999978]
[]
[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$96)^G95","v":0.93457943925233644},{"$f":"1/(1+$E$96)^H95","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(G96:AA96*G86:AA86*G3:AA3)","v":-1308094632.9079413}]
[null,null,"PV of Explicit Cash Flow with Half Year Adj",null,{"$f":"E97*(1+E96)^part_yr_fac","v":-1353103609.6498823}]
[]
[null,"Value Driver with Terminal Value with Book Value"]
[null,null,"BV Terminal",null,{"$f":"SUMIF(2:2,F3+1,74:74)","v":6117876014.1236916}]
[null,null,"Terminal ROIC",null,{"$f":"LOOKUP(F3+1,11:11,13:13)","v":0.12}]
[null,null,"Terminal Growth",null,{"$f":"LOOKUP(F3+1,11:11,15:15)","v":0.04}]
[null,null,"Value Driver Formula from BV x ROIC",null,{"$f":"E101*E102*(1-E103/E102)/(E96-E103)","v":16314336037.663172}]
[null,null,"PV Factor",null,{"$f":"LOOKUP(F3,2:2,96:96)","v":0.2584190028138687}]
[null,null,"PV of Terminal EV",null,{"$f":"E104*E105*(1+E96)^part_yr_fac","v":4360996505.4542799}]
[]
[null,null,"Total Enterprise Value with Explicit CF",null,{"$f":"E106+E98","v":3007892895.8043976}]
[]
[null,"Value Driver with Terminal NOPAT"]
[null,null,"NOPAT in Terminal Year",null,{"$f":"LOOKUP(F3,2:2,78:78)","v":622340118.81831706}]
[null,null,"Terminal Growth",null,{"$f":"E103","v":0.04}]
[null,null,"NOPAT with Growth",null,{"$f":"E111*(1+E112)","v":647233723.57104981}]
[null,null,"Value Driver Formula from NOPAT",null,{"$f":"E113*(1-E112/E102)/(E96-E112)","v":14382971634.912214}]
[null,null,"PV Factor",null,{"$f":"E105","v":0.2584190028138687}]
[null,null,"PV of Terminal Value",null,{"$f":"E115*E114*(1+E96)^part_yr_fac","v":3844722144.5663352}]
[]
[null,null,"Total Enterprise Value",null,{"$f":"E116+E98","v":2491618534.9164529}]

{"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 Output","index":4,"n_rows":19,"n_cols":8,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
[null,null,null,"Row with Links",{"$f":"ROW(Q12)","v":12},null,null,"Instructions at www.edbodmer.com\t"]
[null,null,null,"Scenario Number Column",{"$f":"COLUMN(C:C)","v":3},null,null,"Questions: e-mail edwardbodmer@gmail.com"]
[null,null,null,"Scenario Name Column",{"$f":"COLUMN(D:D)","v":4}]
[null,null,null,"Base Address",{"$f":"ADDRESS(E1,E2)","v":"$C$12"},null,null,"Instructions:"]
[null,null,null,"Column Scenario Number",{"$f":"MID(E4,2,1)","v":"C"},null,null,"All you have to do is to copy this sheet into your model (with ALT, E, M)"]
[null,null,null,"Scenario Sheet Name","Scenario Output",null,null,"This file has to be open and available (like generic macros)"]
[null,null,null,null,{"$f":"MID(CELL(\"filename\"),FIND(\"]\",CELL(\"filename\"),1)+1,250)","v":"Summary Waterfall"},null,null,"When you open your model, this file should open as well"]
[null,null,null,null,null,null,null,"You may have to copy and paste special the sheet name"]
[]
[]
[null,null,"No","Name",{"$f":"'Cash Flow Model from ROIC'!B9","v":"Equity Value per Share"},{"$f":"'Cash Flow Model from ROIC'!G8","v":"EV/EBITDA"},"Terminal EV/EBITDA","Cost of Capital"]
[null,null,null,null,{"$f":"'Cash Flow Model from ROIC'!E9","v":6128.5801554196851},{"$f":"'Cash Flow Model from ROIC'!H8","v":230.13791388230837},{"$f":"'Cash Flow Model from ROIC'!K8","v":11.237408893797511},{"$f":"'Cash Flow Model from ROIC'!F14","v":7.0000000000000007E-2}]
[]
[null,null,1,"Base Case",1485.7901445452542,54.128151510165054,10.314407515609609,7.0000000000000007E-2]
[null,null,2,"Higher Cost of Capital",520.36025150758087,19.843562315205478,6.8762716770730741,0.09]
[null,null,3,"Lower Terminal ROIC",969.89629292855398,35.807599289400329,8.390879719291771,7.0000000000000007E-2]
[null,null,4,"Higher Terminal Growth",1778.9361774172298,64.538427164179438,13.05247956334275,7.0000000000000007E-2]
[null,null,5,"Higher Short Term ROIC",1312.3405816229142,47.968567004109509,9.8404246543383049,7.0000000000000007E-2]
[null,null,6,"Higher Short Term Growth",1312.3405816229142,47.968567004109509,9.8404246543383049,7.0000000000000007E-2]

{"sheet":"Sheet2","index":5,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}