{"text_format":"1","workbook":"xlsx","n_sheets":12,"sheets":["Page 55","Page 56","Page 56, bottom","Page 57","Page 58, top","Caterpillar EV market","Page 58, bottom","Page 59","Page 65","Page 66","Page 67","Pages 68-69"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"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":"Page 55","index":0,"n_rows":19,"n_cols":5,"rows_kept":19,"cut":false,"hidden":false,"merged":[[0,0,0,4]]}
["XYZ CORP BALANCE SHEET"]
["Assets",null,null,"Liabilities and equity"]
["Short-term assets",null,null,"Short-term liabilities"]
["Cash",1000,null,"Accounts payable",1500]
["Marketable securities",1500,null,"Taxes payable",200]
["Inventories",1500,null,"Current portion of long-term debt",1000]
["Accounts receivable",3000,null,"Short-term debt",500]
[]
["Fixed assets",null,null,"Long-term debt",1500]
["Land",150,null,"Pension liabilities",800]
["Plant, property and equipment at cost",2500]
["Minus accumulated depreciation",-700,null,"Preferred stock",200]
["Net fixed assets",null,null,"Minority interest",100]
[]
[null,null,null,"Equity"]
["Goodwill",1000,null,"Stock at par",1000]
[null,null,null,"Accumulated retained earnings",3500]
[null,null,null,"Stock repurchases",{"$f":"-350","v":-350}]
["Total assets",{"$f":"SUM(B3:B16)","v":9950},null,"Total liabilities and equity",{"$f":"SUM(E3:E18)","v":9950}]

{"sheet":"Page 56","index":1,"n_rows":17,"n_cols":6,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["XYZ BALANCE SHEET\nOperational current liabilities moved to left side\nAll financial liabilities in one account on right side"]
["Assets",null,null,"Liabilities and equity"]
["Liquid assets (cash + marketable securities)",2500,null,"Financial debt"]
[null,null,null,"Current portion of long-term debt",1000]
["Current assets, operational",null,null,"Short-term debt",500]
["Inventories",1500,null,"Long-term debt",1500]
["Accounts receivable",3000,null,"Total financial debt",{"$f":"SUM(E4:E6)","v":3000}]
["Minus, current liabilities, operational"]
["Accounts payable",-1500]
["Taxes payable",-200,null,"Pension liabilities",800]
["Net working capital",{"$f":"SUM(B6:B10)","v":2800},{"$f":"getformula(B11)","v":"<--  =SUM(B6:B10)"}]
[null,null,null,"Preferred stock",200]
["Fixed assets",1950,null,"Minority interest",100]
[]
["Goodwill",1000,null,"Equity",4150]
[]
["Left-hand side of rewritten \nbalance sheet",{"$f":"B11+B13+B15+SUM(B3:B4)","v":8250},{"$f":"getformula(B17)","v":"<--  =B11+B13+B15+SUM(B3:B4)"},"Right-hand side of rewritten balance sheet",{"$f":"E7+E10+SUM(E12:E15)","v":8250},{"$f":"getformula(E17)","v":"<--  =E7+E10+SUM(E12:E15)"}]

{"sheet":"Page 56, bottom","index":2,"n_rows":14,"n_cols":6,"rows_kept":14,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["XYZ ENTERPRISE VALUE BALANCE SHEET"]
["Assets",null,null,"Liabilities and equity"]
["Net working capital",2800,null,"Total financial debt",3000]
[null,null,null,"Minus liquid assets",-2500]
["Fixed assets",1950,null,"Net debt",{"$f":"E3+E4","v":500}]
[]
["Goodwill",1000,null,"Pension liabilities",800]
[]
[null,null,null,"Preferred stock",200]
[null,null,null,"Minority interest",100]
[]
[null,null,null,"Equity",4150]
[]
["Enterprise value",{"$f":"B3+B5+B7","v":5750},{"$f":"getformula(B14)","v":"<--  =B3+B5+B7"},"Enterprise value",{"$f":"E5+E7+SUM(E9:E12)","v":5750},{"$f":"getformula(E14)","v":"<--  =E5+E7+SUM(E9:E12)"}]

{"sheet":"Page 57","index":3,"n_rows":20,"n_cols":5,"rows_kept":20,"cut":false,"hidden":false,"merged":[[0,0,0,4]]}
["CATERPILLAR CORP., BALANCE SHEET\n31 December 2011"]
["Current assets",null,null,"Current liabilities"]
["Cash and cash equivalents",3057000,null,"Accounts payable",16946000]
["Short-term investments",null,null,"Short-term debt ",9648000]
["Net receivables",19533000,null,"Other current liabilities",1967000]
["Inventory",14544000,null,"Total current liabilities",{"$f":"SUM(E3:E5)","v":28561000}]
["Other current assets",994000]
["Total current assets",{"$f":"SUM(B3:B7)","v":38128000},null,"Long-term debt",24944000]
[null,null,null,"Other liabilities",14539000]
["Long-term investments",13211000]
["Property, plant and equipment (net)",14395000,null,"Minority interest ",46000]
["Goodwill",7080000,null,"Total liabilities",{"$f":"SUM(E6:E11)","v":68090000}]
["Intangible assets",4368000]
["Other assets",2107000,null,"Stocks, options, warrants",473000]
["Deferred long-term asset charges",2157000,null,"Common stock",4273000]
[null,null,null,"Retained earnings",25219000]
[null,null,null,"Treasury stock",-10281000]
[null,null,null,"Other stockholder equity",-6328000]
[null,null,null,"Total equity",{"$f":"SUM(E14:E18)","v":13356000}]
["Total assets",{"$f":"SUM(B8:B15)","v":81446000},null,"Total equity and liabilities",{"$f":"E12+E19","v":81446000}]

{"sheet":"Page 58, top","index":4,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["CATERPILLAR CORP., 2011 ENTERPRISE VALUE BALANCE SHEET\nBook values"]
["Net working capital",{"$f":"19533000+14544000+994000-16946000-1967000","v":16158000},{"$f":"getformula(B2)","v":"<--  =19533000+14544000+994000-16946000-1967000"},"Net financial debt",{"$f":"9648000+24944000-3057000","v":31535000},{"$f":"getformula(E2)","v":"<--  =9648000+24944000-3057000"}]
["Long-term investments",13211000,null,"Other liabilities",14539000]
["Property, plant and equipment",14395000]
["Goodwill",7080000,null,"Minority interest ",46000]
["Intangible assets",4368000]
["Other assets",2107000,null,"Equity",13356000]
["Deferred long-term asset charges",2157000]
["Enterprise value",{"$f":"SUM(B2:B8)","v":59476000},{"$f":"getformula(B9)","v":"<--  =SUM(B2:B8)"},"Enterprise value",{"$f":"SUM(E2:E7)","v":59476000},{"$f":"getformula(E9)","v":"<--  =SUM(E2:E7)"}]

{"sheet":"Caterpillar EV market","index":5,"n_rows":10,"n_cols":6,"rows_kept":10,"cut":false,"hidden":false,"merged":[[0,0,0,5]]}
["CATERPILLAR CORP., 2011 ENTERPRISE VALUE BALANCE SHEET\nMarket Values"]
["Net working capital",{"$f":"19533000+14544000+994000-16946000-1967000","v":16158000},{"$f":"getformula(B2)","v":"<--  =19533000+14544000+994000-16946000-1967000"},"Net financial debt",{"$f":"9648000+24944000-3057000","v":31535000},{"$f":"getformula(E2)","v":"<--  =9648000+24944000-3057000"}]
["Long-term investments",13211000,null,"Other liabilities",14539000]
["Property, plant and equipment",14395000]
["Goodwill",7080000,null,"Minority interest ",46000]
["Intangible assets",4368000]
["Other assets",2107000,null,"Equity",{"$f":"'Page 58, bottom'!B4","v":56599878.341399997},"<-- Market cap"]
["Deferred long-term asset charges",2157000]
[]
["Enterprise value",{"$f":"SUM(B2:B8)","v":59476000},{"$f":"getformula(B10)","v":"<--  =SUM(B2:B8)"},"Enterprise value",{"$f":"SUM(E2:E7)","v":102719878.3414},{"$f":"getformula(E10)","v":"<--  =SUM(E2:E7)"}]

{"sheet":"Page 58, bottom","index":6,"n_rows":21,"n_cols":3,"rows_kept":21,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["CATERPILLAR VALUATION OF EQUITY AND FINANCIAL LIABILITIES:  EFFICIENT MARKETS APPROACH\nMost figures in thousand $"]
["Number of shares outstanding",{"$f":"624722719/1000","v":624722.71900000004},"<-- thousand shares"]
["Price per share",90.6,"<-- 30dec2011"]
["Equity value (\"Market Cap\")",{"$f":"B2*B3","v":56599878.341399997},{"$f":"getformula(B4)&\", thousand $\"","v":"<--  =B2*B3, thousand $"}]
[]
["Cash and cash equivalents",{"$f":"'Page 57'!B3","v":3057000}]
["Short-term debt and current portion of long-term debt",{"$f":"'Page 57'!E4","v":9648000}]
["Long-term debt",{"$f":"'Page 57'!E8","v":24944000}]
["Net debt",{"$f":"SUM(B7:B8)-B6","v":31535000},{"$f":"getformula(B9)","v":"<--  =SUM(B7:B8)-B6"}]
[]
["Other liabilities",{"$f":"'Page 58, top'!E3","v":14539000}]
[]
["Minority interest",{"$f":"'Page 57'!E11","v":46000}]
["Preferred stock",0]
[]
["Enterprise value:  Equity + Net debt \n+ Minority Interest + Preferred",{"$f":"SUM(B4,B9,B11,B13)","v":102719878.3414},{"$f":"getformula(B16)","v":"<--  =SUM(B4,B9,B11,B13)"}]
[]
["Shares outstanding"]
["Issued",814894624]
["Treasury stock (repurchased shares)",190171905]
["Outstanding shares",{"$f":"B19-B20","v":624722719},{"$f":"getformula(B21)","v":"<--  =B19-B20"}]

{"sheet":"Page 59","index":7,"n_rows":10,"n_cols":6,"rows_kept":10,"cut":false,"hidden":false,"merged":[[0,0,0,5],[2,1,7,1],[2,2,7,2]]}
["CATERPILLAR CORP., 2011 ENTERPRISE VALUE BALANCE SHEET\nRight-hand side revalued at market values\nLeft-hand side brought into balance with right-hand side by adjusting long-term assets"]
["Net working capital",{"$f":"19533000+14544000+994000-16946000-1967000","v":16158000},{"$f":"getformula(B2)","v":"<--  =19533000+14544000+994000-16946000-1967000"},"Net financial debt",{"$f":"9648000+24944000-3057000","v":31535000},{"$f":"getformula(E2)","v":"<--  =9648000+24944000-3057000"}]
["Long-term investments",{"$f":"E10-B2","v":86561878.341399997},{"$f":"getformula(B3)","v":"<--  =E10-B2"},"Other liabilities",14539000]
["Property, plant and equipment"]
["Goodwill",null,null,"Minority interest ",{"$f":"'Page 58, bottom'!B13","v":46000}]
["Intangible assets"]
["Other assets",null,null,"Equity",{"$f":"'Page 58, bottom'!B4","v":56599878.341399997},"<-- Market cap"]
["Deferred long-term asset charges"]
[]
["Enterprise value",{"$f":"SUM(B2:B8)","v":102719878.3414},{"$f":"getformula(B10)","v":"<--  =SUM(B2:B8)"},"Enterprise value",{"$f":"SUM(E2:E7)","v":102719878.3414},{"$f":"getformula(E10)","v":"<--  =SUM(E2:E7)"}]

{"sheet":"Page 65","index":8,"n_rows":35,"n_cols":7,"rows_kept":35,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["ABC CORPORATION\nConsolidated Statement of Cash Flows, 2008-2012"]
[null,2008,2009,2010,2011,2012]
["Operating Activities:"]
["Net earnings",479355,495597,534268,505856,520273]
["Adjustments to reconcile net earnings to net cash provided by operating activities"]
["Add back depreciation and amortization",41583,47647,46438,45839,46622]
["Changes in operating assets and liabilities:"]
["Subtract increase in accounts receivable",9387,25951,-12724,1685,-2153]
["Subtract increase in inventories",-37630,-22780,-16247,-15780,-5517]
["Subtract increase in prepaid expenses and other assets",-52191,13573,16255,14703,-2975]
["Add increase in accounts payable, accrued expenses, pensions, and other liabilities",29612,51172,6757,40541,60255]
["Net cash provided by operating activities",{"$f":"SUM(B4:B11)","v":470116},{"$f":"SUM(C4:C11)","v":611160},{"$f":"SUM(D4:D11)","v":574747},{"$f":"SUM(E4:E11)","v":592844},{"$f":"SUM(F4:F11)","v":616505},{"$f":"getformula(F12)","v":"<--  =SUM(F4:F11)"}]
[]
["Investing Activities:"]
["Short-term investments, net",-5000,-55000,50000,-10000,20000]
["Purchases of property, plant and equipment",-48944,-70326,-89947,-37044,-88426]
["Proceeds from dispositions of property, plant and equipment",197,6956,22942,6179,28693]
["Net cash used in investing activities",-53747,-118370,{"$f":"SUM(D15:D17)","v":-17005},{"$f":"SUM(E15:E17)","v":-40865},{"$f":"SUM(F15:F17)","v":-39733},{"$f":"getformula(F18)","v":"<--  =SUM(F15:F17)"}]
[]
["Financing Activities:"]
["Repayment of debt",0,0,-300000,0,-7095]
["Proceeds from revolving credit facility borrowings",1242431,0,0,0,250000]
["Proceeds from the issuance of stock",48286,114276,69375,68214,37855]
["Dividends paid",-332986,-344128,-361208,-367499,-378325]
["Stock repurchased",-150095,-200031,-200038,-200003,-597738]
["Net cash used in financing activities",{"$f":"SUM(B21:B25)","v":807636},{"$f":"SUM(C21:C25)","v":-429883},{"$f":"SUM(D21:D25)","v":-791871},{"$f":"SUM(E21:E25)","v":-499288},{"$f":"SUM(F21:F25)","v":-695303},{"$f":"getformula(F26)","v":"<--  =SUM(F21:F25)"}]
[]
["Changes in cash balances",{"$f":"B12+B18+B26","v":1224005},{"$f":"C12+C18+C26","v":62907},{"$f":"D12+D18+D26","v":-234129},{"$f":"E12+E18+E26","v":52691},{"$f":"F12+F18+F26","v":-118531},{"$f":"getformula(F28)","v":"<--  =F12+F18+F26"}]
[]
["Supplemental disclosure of cash flow information"]
["Cash paid during the period for"]
["Income taxes",255043,175972,314735,283618,305094]
["Interest",83553,83551,70351,57151,57910]
[]
["Income tax rate",{"$f":"B32/(B4+B32)","v":0.34728171917679512},{"$f":"C32/(C4+C32)","v":0.26203115390972481},{"$f":"D32/(D4+D32)","v":0.37071129312852841},{"$f":"E32/(E4+E32)","v":0.35924932296693746},{"$f":"F32/(F4+F32)","v":0.36964647241772447},{"$f":"getformula(F35)","v":"<--  =F32/(F4+F32)"}]

{"sheet":"Page 66","index":9,"n_rows":37,"n_cols":7,"rows_kept":37,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["ABC CORPORATION\nCSCF rewritten to Free Cash Flow (FCF)"]
[null,2008,2009,2010,2011,2012]
["Operating Activities:"]
["Net earnings",479355,495597,534268,505856,520273]
["Adjustments to reconcile net earnings to net cash provided by operating activities"]
["Add back depreciation and amortization",41583,47647,46438,45839,46622]
["Changes in operating assets and liabilities:"]
["Subtract increase in accounts receivable",9387,25951,-12724,1685,-2153]
["Subtract increase in inventories",-37630,-22780,-16247,-15780,-5517]
["Subtract increase in prepaid expenses and other assets",-52191,13573,16255,14703,-2975]
["Add increase in accounts payable, accrued expenses, pensions and other liabilities",29612,51172,6757,40541,60255]
["Net cash provided by operating activities",{"$f":"SUM(B4:B11)","v":470116},{"$f":"SUM(C4:C11)","v":611160},{"$f":"SUM(D4:D11)","v":574747},{"$f":"SUM(E4:E11)","v":592844},{"$f":"SUM(F4:F11)","v":616505},{"$f":"getformula(F12)","v":"<--  =SUM(F4:F11)"}]
[]
["Investing Activities:"]
["Short-term investments, net"]
["Purchases of property, plant and equipment",-48944,-70326,-89947,-37044,-88426]
["Proceeds from dispositions of property, plant and equipment",197,6956,22942,6179,28693]
["Net cash used in investing activities",-53747,-118370,{"$f":"SUM(D15:D17)","v":-67005},{"$f":"SUM(E15:E17)","v":-30865},{"$f":"SUM(F15:F17)","v":-59733},{"$f":"getformula(F18)","v":"<--  =SUM(F15:F17)"}]
[]
["Financing Activities:"]
["Repayment of debt"]
["Proceeds from revolving credit facility borrowings"]
["Proceeds from the issuance of stock"]
["Dividends paid"]
["Stock repurchased"]
["Net cash used in financing activities",null,null,null,null,null,{"$f":"getformula(F26)","v":"<--  "}]
[]
["Free cash flow before interest adjustment",{"$f":"B12+B18+B26","v":416369},{"$f":"C12+C18+C26","v":492790},{"$f":"D12+D18+D26","v":507742},{"$f":"E12+E18+E26","v":561979},{"$f":"F12+F18+F26","v":556772},{"$f":"getformula(F28)","v":"<--  =F12+F18+F26"}]
["Add back after-tax net interest",{"$f":"(1-B37)*B35","v":54536.570517621236},{"$f":"(1-C37)*C35","v":61658.035059688576},44271.089817114902,36619.541943116557,{"$f":"(1-F37)*F35","v":36503.772782289576},{"$f":"getformula(F29)","v":"<--  =(1-F37)*F35"}]
["Free cash flow (FCF)",{"$f":"B28+B29","v":470905.57051762124},{"$f":"C28+C29","v":554448.03505968861},552013.08981711487,598598.54194311658,593275.77278228954,{"$f":"getformula(F30)","v":"<--  =F28+F29"}]
[]
["Supplemental disclosure of cash flow information"]
["Cash paid during the period for"]
["Income taxes",255043,175972,314735,283618,305094]
["Interest",83553,83551,70351,57151,57910]
[]
["Income tax rate",{"$f":"B34/(B4+B34)","v":0.34728171917679512},{"$f":"C34/(C4+C34)","v":0.26203115390972481},{"$f":"D34/(D4+D34)","v":0.37071129312852841},{"$f":"E34/(E4+E34)","v":0.35924932296693746},{"$f":"F34/(F4+F34)","v":0.36964647241772447},{"$f":"getformula(F37)","v":"<--  =F34/(F4+F34)"}]

{"sheet":"Page 67","index":10,"n_rows":27,"n_cols":8,"rows_kept":27,"cut":false,"hidden":false,"merged":[[0,0,0,7],[18,0,19,0],[18,2,18,6]]}
["ABC CORP. VALUATION"]
["Free cash flow (FCF)\nyear ending 31 Dec. 2012",593275.77278228954,{"$f":"getformula(B2)","v":"<--  593275.77278229"}]
["Growth rate of FCF, years 1-5",0.08,"<-- Optimistic about short-term growth"]
["Long-term FCF growth rate",0.05,"<-- More pessimistic about long-term growth"]
["Weighted average cost of capital, WACC",0.107]
[]
["Year",2012,2013,2014,2015,2016,2017]
["FCF",null,{"$f":"B2*(1+B3)","v":640737.83460487274},{"$f":"C8*(1+$B$3)","v":691996.8613732626},{"$f":"D8*(1+$B$3)","v":747356.61028312368},{"$f":"E8*(1+$B$3)","v":807145.13910577365},{"$f":"F8*(1+$B$3)","v":871716.75023423554},{"$f":"getformula(G8)","v":"<--  =F8*(1+$B$3)"}]
["Terminal value",null,null,null,null,null,{"$f":"G8*(1+B4)/(B5-B4)","v":16057940.135893816},{"$f":"getformula(G9)","v":"<--  =G8*(1+B4)/(B5-B4)"}]
["Total",null,{"$f":"C8+C9","v":640737.83460487274},{"$f":"D8+D9","v":691996.8613732626},{"$f":"E8+E9","v":747356.61028312368},{"$f":"F8+F9","v":807145.13910577365},{"$f":"G8+G9","v":16929656.886128053},{"$f":"getformula(G10)","v":"<--  =G8+G9"}]
[]
["Enterprise value",{"$f":"NPV(B5,C10:G10)*(1+B5)^0.5","v":13063055.212536111},{"$f":"getformula(B12)","v":"<--  =NPV(B5,C10:G10)*(1+B5)^0.5"}]
["Add back initial cash and marketable securities",73697,"<-- From current balance sheet"]
["Subtract out 2012 financial liabilities",1379106,"<-- From current balance sheet"]
["Equity value",{"$f":"B12+B13-B14","v":11757646.212536111},{"$f":"getformula(B15)","v":"<--  =B12+B13-B14"}]
["Per share (1 million shares outstanding)",{"$f":"B15/1000000","v":11.757646212536111},{"$f":"getformula(B16)","v":"<--  =B15/1000000"}]
[]
["Data table:  Share value vs LT growth and WACC"]
[{"$f":"\"data table header, \"&CHAR(10)&gggetformula(B20)","v":{"$error":"#NAME?"}},null,"Long-term growth ↓"]
[null,{"$f":"IF(B5>B4,B16,\"nmf\")","v":11.757646212536111},0,0.03,0.06,0.09,0.12]
[null,0.06,13.103527042497896,24.951760756158212,"nmf","nmf","nmf"]
["WACC →",0.08,9.4842207042654536,14.478277881668323,34.454506591279781,"nmf","nmf"]
[null,0.1,7.3169753870629615,9.9932101507221436,16.683797059870102,63.517905423905759,"nmf"]
[null,0.12,5.8754245960578437,7.504007273305751,10.761172627801567,20.532668691289008,"nmf"]
[null,0.14000000000000001,4.8482987675736391,5.9218158148131161,7.8004706474822036,11.933511279354194,28.465673806842151]
[null,0.16,4.0799864818946832,4.8278637769461392,6.0244674490284691,8.2467314114670796,13.802391317563611]
[null,0.18,3.4840552542641023,4.0267378031279977,4.8407616264238413,6.197467998583579,8.9108807429030588]

{"sheet":"Pages 68-69","index":11,"n_rows":67,"n_cols":8,"rows_kept":67,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["PRO FORMA FINANCIAL MODEL"]
["Sales growth",0.1]
["Current assets/Sales",0.15]
["Current liabilities/Sales",0.08]
["Net fixed assets/Sales",0.77]
["Costs of goods sold/Sales",0.5]
["Depreciation rate",0.1]
["Interest rate on debt",0.1]
["Interest paid on cash and marketable securities",0.08]
["Tax rate",0.4]
["Dividend payout ratio",0.4]
[]
["Year",0,1,2,3,4,5]
["Income statement"]
["Sales",{"$f":"1000","v":1000},{"$f":"B15*(1+$B$2)","v":1100},{"$f":"C15*(1+$B$2)","v":1210},{"$f":"D15*(1+$B$2)","v":1331},{"$f":"E15*(1+$B$2)","v":1464.1000000000001},{"$f":"F15*(1+$B$2)","v":1610.5100000000002}]
["Costs of goods sold",{"$f":"-B15*$B$6","v":-500},-550,-605,-665.5,-732.05000000000007,-805.25500000000011]
["Interest payments on debt",{"$f":"-$B$8*B36","v":-32},{"$f":"-$B$8*(B36+C36)/2","v":-32},{"$f":"-$B$8*(C36+D36)/2","v":-32},{"$f":"-$B$8*(D36+E36)/2","v":-32},{"$f":"-$B$8*(E36+F36)/2","v":-32},{"$f":"-$B$8*(F36+G36)/2","v":-32}]
["Interest earned on cash and marketable securities",{"$f":"$B$9*B27","v":6.4},{"$f":"$B$9*(B27+C27)/2","v":8.9477956254272062},{"$f":"$B$9*(C27+D27)/2","v":14.276702863501569},{"$f":"$B$9*(D27+E27)/2","v":20.080913361163557},{"$f":"$B$9*(E27+F27)/2","v":26.376731259734132},{"$f":"$B$9*(F27+G27)/2","v":33.178068826054897}]
["Depreciation",-100,{"$f":"-$B$7*(C30+B30)/2","v":-116.68421052631579},{"$f":"-$B$7*(D30+C30)/2","v":-137.47728531855955},{"$f":"-$B$7*(E30+D30)/2","v":-161.31015745735533},{"$f":"-$B$7*(F30+E30)/2","v":-188.58791087391907},{"$f":"-$B$7*(G30+F30)/2","v":-219.76678043959475}]
["Profit before tax",{"$f":"SUM(B15:B19)","v":374.4},410.26358509911148,449.799417544942,492.27075590380821,537.8388203858151,586.66628838646022]
["Taxes",{"$f":"-B20*$B$10","v":-149.76},-164.10543403964459,-179.91976701797682,-196.90830236152328,-215.13552815432604,-234.66651535458411]
["Profit after tax",{"$f":"B21+B20","v":224.64},246.15815105946689,269.87965052696518,295.36245354228492,322.70329223148906,351.99977303187609]
["Dividends",{"$f":"-B22*$B$11","v":-89.855999999999995},{"$f":"-$B$11*C22","v":-98.463260423786764},{"$f":"-$B$11*D22","v":-107.95186021078608},{"$f":"-$B$11*E22","v":-118.14498141691398},{"$f":"-$B$11*F22","v":-129.08131689259562},{"$f":"-$B$11*G22","v":-140.79990921275044}]
["Retained earnings",{"$f":"B23+B22","v":134.78399999999999},147.69489063568011,161.92779031617908,177.21747212537093,193.62197533889344,211.19986381912565]
[]
["Balance sheet"]
["Cash and marketable securities",80,{"$f":"C39-C28-C32","v":143.69489063568017},{"$f":"D39-D28-D32","v":213.22268095185905},{"$f":"E39-E28-E32","v":288.80015307722988},{"$f":"F39-F28-F32","v":370.61812841612345},{"$f":"G39-G28-G32","v":458.83359223524894}]
["Current assets",{"$f":"$B$3*B15","v":150},{"$f":"C15*$B$3","v":165},{"$f":"D15*$B$3","v":181.5},{"$f":"E15*$B$3","v":199.65},{"$f":"F15*$B$3","v":219.61500000000001},{"$f":"G15*$B$3","v":241.57650000000001}]
["Fixed assets"]
["     At cost",{"$f":"B32-B31","v":1070},1263.6842105263158,1485.8614958448754,1740.341653302231,2031.4165641761501,2363.9190446157445]
["     Depreciation",-300,{"$f":"B31+C19","v":-416.68421052631578},{"$f":"C31+D19","v":-554.16149584487539},{"$f":"D31+E19","v":-715.47165330223072},{"$f":"E31+F19","v":-904.05956417614982},{"$f":"F31+G19","v":-1123.8263446157446}]
["     Net fixed assets",{"$f":"B15*$B$5","v":770},847,931.7,1024.8700000000001,1127.3570000000002,1240.0927000000001]
["Total assets",{"$f":"B27+B28+B32","v":1000},{"$f":"C32+C28+C27","v":1155.6948906356802},{"$f":"D32+D28+D27","v":1326.4226809518591},{"$f":"E32+E28+E27","v":1513.3201530772301},{"$f":"F32+F28+F27","v":1717.5901284161237},{"$f":"G32+G28+G27","v":1940.5027922352492}]
[]
["Current liabilities",{"$f":"B15*$B$4","v":80},88,96.8,106.48,117.12800000000001,128.84080000000003]
["Debt",{"$f":"B33-B35-B37-B38","v":320},{"$f":"B36","v":320},320,320,320,320]
["Stock",450,450,450,450,450,450]
["Accumulated retained earnings",150,{"$f":"B38+C24","v":297.69489063568011},{"$f":"C38+D24","v":459.62268095185919},{"$f":"D38+E24","v":636.84015307723007},{"$f":"E38+F24","v":830.46212841612351},{"$f":"F38+G24","v":1041.6619922352493}]
["Total liabilities and equity",{"$f":"SUM(B35:B38)","v":1000},1155.6948906356802,1326.4226809518591,1513.3201530772301,1717.5901284161237,1940.5027922352492]
[]
["Year",0,1,2,3,4,5]
["Free cash flow calculation"]
["Profit after tax",null,{"$f":"C22","v":246.15815105946689},{"$f":"D22","v":269.87965052696518},{"$f":"E22","v":295.36245354228492},{"$f":"F22","v":322.70329223148906},{"$f":"G22","v":351.99977303187609}]
["Add back depreciation",null,{"$f":"-C19","v":116.68421052631579},{"$f":"-D19","v":137.47728531855955},{"$f":"-E19","v":161.31015745735533},{"$f":"-F19","v":188.58791087391907},{"$f":"-G19","v":219.76678043959475}]
["Subtract increase in current assets",null,{"$f":"-(C28-B28)","v":-15},{"$f":"-(D28-C28)","v":-16.5},{"$f":"-(E28-D28)","v":-18.150000000000006},{"$f":"-(F28-E28)","v":-19.965000000000003},{"$f":"-(G28-F28)","v":-21.961500000000001}]
["Add back increase in current liabilities",null,{"$f":"C35-B35","v":8},{"$f":"D35-C35","v":8.7999999999999972},{"$f":"E35-D35","v":9.6800000000000068},{"$f":"F35-E35","v":10.64800000000001},{"$f":"G35-F35","v":11.712800000000016}]
["Subtract increase in fixed assets at cost",null,{"$f":"-(C30-B30)","v":-193.68421052631584},{"$f":"-(D30-C30)","v":-222.1772853185596},{"$f":"-(E30-D30)","v":-254.48015745735552},{"$f":"-(F30-E30)","v":-291.07491087391918},{"$f":"-(G30-F30)","v":-332.50248043959436}]
["Add back after-tax interest on debt",null,{"$f":"-(1-$B$10)*C17","v":19.2},19.2,19.2,19.2,19.2]
["Subtract after-tax interest on cash and mkt. securities",null,-5.3686773752563237,-8.5660217181009415,-12.048548016698133,-15.826038755840479,-19.906841295632937]
["Free cash flow",null,{"$f":"SUM(C43:C49)","v":175.98947368421051},{"$f":"SUM(D43:D49)","v":188.11362880886418},{"$f":"SUM(E43:E49)","v":200.87390552558662},{"$f":"SUM(F43:F49)","v":214.27325347564849},{"$f":"SUM(G43:G49)","v":228.30853173624354}]
[]
[]
["Valuing the firm"]
["Weighted average cost of capital",0.2]
["Long-term free cash flow growth rate",0.05]
[]
["Year",0,1,2,3,4,5]
["FCF",null,{"$f":"C50","v":175.98947368421051},{"$f":"D50","v":188.11362880886418},{"$f":"E50","v":200.87390552558662},{"$f":"F50","v":214.27325347564849},{"$f":"G50","v":228.30853173624354}]
["Terminal value",null,null,null,null,null,{"$f":"G58*(1+B55)/(B54-B55)","v":1598.1597221537045},{"$f":"getformula(G59)","v":"<--  =G58*(1+B55)/(B54-B55)"}]
["Total",null,{"$f":"C59+C58","v":175.98947368421051},{"$f":"D59+D58","v":188.11362880886418},{"$f":"E59+E58","v":200.87390552558662},{"$f":"F59+F58","v":214.27325347564849},{"$f":"G59+G58","v":1826.4682538899481}]
[]
[{"$f":"\"Enterprise value, present value of row \"&ROW(A60)","v":"Enterprise value, present value of row 60"},{"$f":"NPV(B54,C60:G60)*(1+B54)^0.5","v":1348.371777452516},{"$f":"getformula(B62)","v":"<--  =NPV(B54,C60:G60)*(1+B54)^0.5"}]
["Add in initial (year 0) cash and mkt. securities",{"$f":"B27","v":80},"<--  =B27"]
["Asset value in year 0",{"$f":"B63+B62","v":1428.371777452516},"<--  =B63+B62"]
["Subtract out value of firm's debt today",{"$f":"-B36","v":-320},"<--  =-B36"]
["Equity value",{"$f":"B64+B65","v":1108.371777452516},"<--  =B64+B65"]
["Share value (100 shares)",{"$f":"B66/100","v":11.08371777452516},"<--  =B66/100"]