{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["Pages 128,131","Pages 135-138","Pages 139-142, 144, 145","Page 143","Page 143, bottom","Page 146","Page 147","Page 149","Pages 151-152","Pages 153-154","Page 154","Page 155","Page 156"],"not_rendered":{"charts":4,"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":"Pages 128,131","index":0,"n_rows":39,"n_cols":4,"rows_kept":39,"cut":false,"hidden":false,"merged":[[0,0,0,3]]}
["SETTING UP THE FINANCIAL STATEMENT 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]
["Income statement"]
["Sales",{"$f":"1000","v":1000},{"$f":"B15*(1+$B$2)","v":1100},{"$f":"getformula(C15)","v":"<-- =B15*(1+$B$2)"}]
["Costs of goods sold",{"$f":"-B15*$B$6","v":-500},{"$f":"-C15*$B$6","v":-550},{"$f":"getformula(C16)","v":"<-- =-C15*$B$6"}]
["Interest payments on debt",{"$f":"-$B$8*B36","v":-32},{"$f":"-$B$8*(B36+C36)/2","v":-32},"<-- =-$B$8*(B36+C36)/2"]
["Interest earned on cash and marketable securities",{"$f":"$B$9*B27","v":6.4},{"$f":"$B$9*(B27+C27)/2","v":8.9477956254272062},"<-- =$B$9*(B27+C27)/2"]
["Depreciation",-100,{"$f":"-$B$7*(C30+B30)/2","v":-116.68421052631579},"<-- =-$B$7*(C30+B30)/2"]
["Profit before tax",{"$f":"SUM(B15:B19)","v":374.4},{"$f":"SUM(C15:C19)","v":410.26358509911148},"<-- =SUM(C15:C19)"]
["Taxes",{"$f":"-B20*$B$10","v":-149.76},{"$f":"-C20*$B$10","v":-164.10543403964459},"<-- =-C20*$B$10"]
["Profit after tax",{"$f":"B21+B20","v":224.64},{"$f":"C21+C20","v":246.15815105946689},"<-- =C21+C20"]
["Dividends",{"$f":"-B22*$B$11","v":-89.855999999999995},{"$f":"-$B$11*C22","v":-98.463260423786764},"<-- =-$B$11*C22"]
["Retained earnings",{"$f":"B23+B22","v":134.78399999999999},{"$f":"C23+C22","v":147.69489063568011},"<-- =C23+C22"]
[]
["Balance sheet"]
["Cash and marketable securities",80,{"$f":"C39-C28-C32","v":143.69489063568017},{"$f":"getformula(C27)","v":"<-- =C39-C28-C32"}]
["Current assets",{"$f":"$B$3*B15","v":150},{"$f":"C15*$B$3","v":165},{"$f":"getformula(C28)","v":"<-- =C15*$B$3"}]
["Fixed assets"]
["     At cost",{"$f":"B32-B31","v":1070},{"$f":"C32-C31","v":1263.6842105263158},{"$f":"getformula(C30)","v":"<-- =C32-C31"}]
["     Depreciation",-300,{"$f":"B31+C19","v":-416.68421052631578},{"$f":"getformula(C31)","v":"<-- =B31+C19"}]
["     Net fixed assets",{"$f":"B15*$B$5","v":770},{"$f":"C15*$B$5","v":847},{"$f":"getformula(C32)","v":"<-- =C15*$B$5"}]
["Total assets",{"$f":"B27+B28+B32","v":1000},{"$f":"C32+C28+C27","v":1155.6948906356802},{"$f":"getformula(C33)","v":"<-- =C32+C28+C27"}]
[]
["Current liabilities",{"$f":"B15*$B$4","v":80},{"$f":"C15*$B$4","v":88},{"$f":"getformula(C35)","v":"<-- =C15*$B$4"}]
["Debt",{"$f":"B33-B35-B37-B38","v":320},{"$f":"B36","v":320},{"$f":"getformula(C36)","v":"<-- =B36"}]
["Stock",450,{"$f":"B37","v":450},{"$f":"getformula(C37)","v":"<-- =B37"}]
["Accumulated retained earnings",150,{"$f":"B38+C24","v":297.69489063568011},{"$f":"getformula(C38)","v":"<-- =B38+C24"}]
["Total liabilities and equity",{"$f":"SUM(B35:B38)","v":1000},{"$f":"SUM(C35:C38)","v":1155.6948906356802},{"$f":"getformula(C39)","v":"<-- =SUM(C35:C38)"}]

{"sheet":"Pages 135-138","index":1,"n_rows":75,"n_cols":8,"rows_kept":75,"cut":false,"hidden":false,"merged":[[0,0,0,6],[52,0,52,7]]}
["FIRST 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},{"$f":"getformula(G15)","v":"<-- =F15*(1+$B$2)"}]
["Costs of goods sold",{"$f":"-B15*$B$6","v":-500},-550,-605,-665.5,-732.05000000000007,-805.25500000000011,{"$f":"getformula(G16)","v":"<-- =-G15*$B$6"}]
["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},"<-- =-$B$8*(F36+G36)/2"]
["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},"<-- =$B$9*(F27+G27)/2"]
["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},"<-- =-$B$7*(G30+F30)/2"]
["Profit before tax",{"$f":"SUM(B15:B19)","v":374.4},410.26358509911148,449.799417544942,492.27075590380821,537.8388203858151,586.66628838646022,"<-- =SUM(G15:G19)"]
["Taxes",{"$f":"-B20*$B$10","v":-149.76},-164.10543403964459,-179.91976701797682,-196.90830236152328,-215.13552815432604,-234.66651535458411,"<-- =-G20*$B$10"]
["Profit after tax",{"$f":"B21+B20","v":224.64},246.15815105946689,269.87965052696518,295.36245354228492,322.70329223148906,351.99977303187609,"<-- =G21+G20"]
["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},"<-- =-$B$11*G22"]
["Retained earnings",{"$f":"B23+B22","v":134.78399999999999},147.69489063568011,161.92779031617908,177.21747212537093,193.62197533889344,211.19986381912565,"<-- =G23+G22"]
[]
["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},"<-- =G39-G28-G32"]
["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},"<-- =G15*$B$3"]
["Fixed assets"]
["     At cost",{"$f":"B32-B31","v":1070},1263.6842105263158,1485.8614958448754,1740.341653302231,2031.4165641761501,2363.9190446157445,"<-- =G32-G31"]
["     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},"<-- =F31+G19"]
["     Net fixed assets",{"$f":"B15*$B$5","v":770},847,931.7,1024.8700000000001,1127.3570000000002,1240.0927000000001,"<-- =G15*$B$5"]
["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},"<-- =G32+G28+G27"]
[]
["Current liabilities",{"$f":"B15*$B$4","v":80},88,96.8,106.48,117.12800000000001,128.84080000000003,"<-- =G15*$B$4"]
["Debt",{"$f":"B33-B35-B37-B38","v":320},{"$f":"B36","v":320},320,320,320,320,"<-- =F36"]
["Stock",450,450,450,450,450,450,"<-- =F37"]
["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},"<-- =F38+G24"]
["Total liabilities and equity",{"$f":"SUM(B35:B38)","v":1000},1155.6948906356802,1326.4226809518591,1513.3201530772301,1717.5901284161237,1940.5027922352492,"<-- =SUM(G35:G38)"]
[]
["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},"<-- =G22"]
["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},"<-- =-G19"]
["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},"<-- =-(G28-F28)"]
["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},"<-- =G35-F35"]
["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},"<-- =-(G30-F30)"]
["Add back after-tax interest on debt",null,{"$f":"-(1-$B$10)*C17","v":19.2},19.2,19.2,19.2,19.2,"<-- =-(1-$B$10)*G17"]
["Subtract after-tax interest on cash and+A14 mkt. securities",null,-5.3686773752563237,-8.5660217181009415,-12.048548016698133,-15.826038755840479,-19.906841295632937,"<-- =-(1-$B$10)*G18"]
["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},"<-- =SUM(G43:G49)"]
[]
[]
["CONSOLIDATED STATEMENT OF CASH FLOWS:  RECONCILING THE CASH BALANCES"]
["     Cash flow from operating activities"]
["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},"<-- =G22"]
["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},"<-- =-G19"]
["Adjust for changes in net working capital:"]
["     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},"<-- =-(G28-F28)"]
["     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},"<-- =G35-F35"]
["Net cash from operating activities",null,{"$f":"SUM(C55:C59)","v":355.84236158578267},{"$f":"SUM(D55:D59)","v":399.65693584552474},{"$f":"SUM(E55:E59)","v":448.20261099964029},{"$f":"SUM(F55:F59)","v":501.97420310540815},{"$f":"SUM(G55:G59)","v":561.51785347147086},"<-- =SUM(G55:G59)"]
[]
["     Cash flow from investing activities"]
["Aquisitions of fixed assets—capital expenditures",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},"<-- =-(G30-F30)"]
["Purchases of investment securities",null,0,0,0,0,0,"<-- Not in our model"]
["Proceeds from sales of investment securities",null,0,0,0,0,0,"<-- Not in our model"]
["Net cash used in investing activities",null,{"$f":"SUM(C63:C65)","v":-193.68421052631584},{"$f":"SUM(D63:D65)","v":-222.1772853185596},{"$f":"SUM(E63:E65)","v":-254.48015745735552},{"$f":"SUM(F63:F65)","v":-291.07491087391918},{"$f":"SUM(G63:G65)","v":-332.50248043959436},"<-- =SUM(G63:G65)"]
[]
["     Cash flow from financing activities"]
["Net proceeds from borrowing activities",null,{"$f":"C36-B36","v":0},0,0,0,0,"<-- =G36-F36"]
["Net proceeds from stock issues, repurchases",null,0,0,0,0,0,"<-- =G37-F37"]
["Dividends paid",null,{"$f":"C23","v":-98.463260423786764},{"$f":"D23","v":-107.95186021078608},{"$f":"E23","v":-118.14498141691398},{"$f":"F23","v":-129.08131689259562},{"$f":"G23","v":-140.79990921275044},"<-- =G23"]
["Net cash from financing activities",null,{"$f":"SUM(C69:C71)","v":-98.463260423786764},{"$f":"SUM(D69:D71)","v":-107.95186021078608},{"$f":"SUM(E69:E71)","v":-118.14498141691398},{"$f":"SUM(F69:F71)","v":-129.08131689259562},{"$f":"SUM(G69:G71)","v":-140.79990921275044},"<-- =SUM(G69:G71)"]
[]
["Net increase in cash and cash equivalents",null,{"$f":"C72+C66+C60","v":63.694890635680053},{"$f":"D72+D66+D60","v":69.52779031617905},{"$f":"E72+E66+E60","v":75.577472125370775},{"$f":"F72+F66+F60","v":81.817975338893348},{"$f":"G72+G66+G60","v":88.215463819126057},"<-- =G72+G66+G60"]
["Check:  changes in cash and mkt. securities",null,{"$f":"C27-B27","v":63.694890635680167},{"$f":"D27-C27","v":69.52779031617888},{"$f":"E27-D27","v":75.577472125370832},{"$f":"F27-E27","v":81.817975338893575},{"$f":"G27-F27","v":88.215463819125489},"<-- =G27-F27"]

{"sheet":"Pages 139-142, 144, 145","index":2,"n_rows":119,"n_cols":11,"rows_kept":119,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["FIRST 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,"<-- real growth  2% + inflation 3%?"]
[]
["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)","v":1230.8893973567979},{"$f":"getformula(B62)","v":"<-- =NPV(B54,C60:G60)"}]
["Add in initial (year 0) cash and mkt. securities",{"$f":"B27","v":80},{"$f":"getformula(B63)","v":"<-- =B27"}]
["Asset value in year 0",{"$f":"B63+B62","v":1310.8893973567979},{"$f":"getformula(B64)","v":"<-- =B63+B62"}]
["Subtract out value of firm's debt today",{"$f":"-B36","v":-320},{"$f":"getformula(B65)","v":"<-- =-B36"}]
["Equity value",{"$f":"B64+B65","v":990.88939735679787},{"$f":"getformula(B66)","v":"<-- =B64+B65"}]
[]
["Cash and marketable securities as negative debt"]
[{"$f":"\"NPV of row \"&ROW(A60)&\" = enterprise value\"","v":"NPV of row 60 = enterprise value"},{"$f":"B62","v":1230.8893973567979},{"$f":"getformula(B69)","v":"<-- =B62"}]
["Net year 0 debt:  debt minus cash",{"$f":"-B36+B27","v":-240},{"$f":"getformula(B70)","v":"<-- =-B36+B27"}]
["Equity value",{"$f":"B69+B70","v":990.88939735679787},{"$f":"getformula(B71)","v":"<-- =B69+B70"}]
[]
[]
["Valuing the firm—using mid-year discounting"]
["Weighted average cost of capital",{"$f":"B54","v":0.2}]
["Long-term free cash flow growth rate",0.05]
[]
["Year",0,1,2,3,4,5]
["FCF",null,{"$f":"C58","v":175.98947368421051},{"$f":"D58","v":188.11362880886418},{"$f":"E58","v":200.87390552558662},{"$f":"F58","v":214.27325347564849},{"$f":"G58","v":228.30853173624354}]
["Terminal value",null,null,null,null,null,{"$f":"G79*(1+B76)/(B75-B76)","v":1598.1597221537045},{"$f":"getformula(G80)","v":"<-- =G79*(1+B76)/(B75-B76)"}]
["Total",null,{"$f":"C80+C79","v":175.98947368421051},{"$f":"D80+D79","v":188.11362880886418},{"$f":"E80+E79","v":200.87390552558662},{"$f":"F80+F79","v":214.27325347564849},{"$f":"G80+G79","v":1826.4682538899481}]
[]
[{"$f":"\"Enterprise value, NPV of row \"&ROW(A81)","v":"Enterprise value, NPV of row 81"},{"$f":"NPV(B75,C81:G81)*(1+B75)^0.5","v":1348.371777452516},{"$f":"getformula(B83)","v":"<-- =NPV(B75,C81:G81)*(1+B75)^0.5"}]
["Add in initial (year 0) cash and mkt. securities",{"$f":"B27","v":80},{"$f":"getformula(B84)","v":"<-- =B27"}]
["Asset value in year 0",{"$f":"B84+B83","v":1428.371777452516},{"$f":"getformula(B85)","v":"<-- =B84+B83"}]
["Subtract out value of firm's debt today",{"$f":"B65","v":-320},{"$f":"getformula(B86)","v":"<-- =B65"}]
["Equity value",{"$f":"B85+B86","v":1108.371777452516},{"$f":"getformula(B87)","v":"<-- =B85+B86"}]
[]
[]
[{"$f":"\"Data table:  The effect of sales growth (cell \"&CHAR(COLUMN(B2)+64)&ROW(B2)&\") on equity valuation\"","v":"Data table:  The effect of sales growth (cell B2) on equity valuation"}]
[null,"Growth",{"$f":"B87","v":1108.371777452516},{"$f":"getformula(C91)&\" , data table header\"","v":"<-- =B87 , data table header"}]
[null,0,1093.078879295015]
[null,0.02,1104.9406971058056]
[null,0.04,1112.916085109207]
[null,0.06,1116.5068023902504]
[null,0.08,1115.179632948104]
[null,0.1,1108.3718882655728]
[null,0.12,1095.489519963987]
[null,0.14000000000000001,1075.9063566217387]
[null,0.16,1048.9633405928257]
[]
[]
[]
[]
[]
[]
[null,null,"WACC ¯"]
[null,{"$f":"IF(B75<=B76,\"nmf\",B87)","v":1108.371777452516},0.1,0.12,0.14000000000000001,0.16,0.18,0.2,0.22,0.24,0.26]
[null,0,2038.1232391188132,1660.038286713241,1390.5233561253415,1188.8205362645583,1032.2905563431227,907.35304529133737,805.36851734081779,720.57940413013011,649.00141081969855]
["Long-term FCF growth rate --> ",0.02,2446.9956004757996,1915.9603990064097,1562.3442109531318,1310.0759551120079,1121.1232074714453,974.35928934506501,857.11014101049204,761.31202136449406,681.5903811718141]
[null,0.04,3128.4495317808901,2299.8435672239257,1802.893407700145,1471.7498469079621,1235.3366160649675,1058.1170944122221,920.34990327342757,810.19116204573038,720.10461886067833]
[null,0.06,4491.3573943910706,2939.6488475864526,2163.7172028206651,1698.0932954222985,1387.6211608563301,1165.8057009271381,999.3996061020971,869.93233398946381,766.32170408731531]
[null,0.08,8580.0809822216142,4219.2594083115055,2765.0901946881995,2037.6084681938023,1600.8195235642381,1309.3905096136925,1101.0349383103862,944.60879891913055,822.8092526976493]
[null,0.1,"nmf",8058.0910904866669,3967.8361784232675,2603.4670894796418,1920.6170676260999,1510.4092417748691,1236.5487145881052,1040.621396685845,893.41868846056695]
[null,0.12,"nmf","nmf",7576.0741296284659,3735.1843320513199,2453.6129743958704,1811.9373400166337,1426.2680013769113,1168.6381937081305,984.20224872717517]
[null,0.14000000000000001,"nmf","nmf","nmf",7130.3360597663632,3519.6047879354119,2314.4841704195755,1710.8469315601214,1347.8617095393311,1105.2469957493197]
[null,0.16,"nmf","nmf","nmf","nmf",6717.5802285540312,3319.577831225457,2185.1451485321372,1616.6969832861312,1274.7096415803214]
[]
[null,"Note:  Data tables are discussed in Chapter 31."]

{"sheet":"Page 143","index":3,"n_rows":39,"n_cols":8,"rows_kept":39,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["FIXED ASSETS AT COST IS A FUNCTION OF SALES"]
["Sales growth",0.1]
["Current assets/Sales",0.15]
["Current liabilities/Sales",0.08]
["Fixed assets at cost/Sales",1.07]
["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":12.353246753246754},{"$f":"$B$9*(C27+D27)/2","v":25.066426041490978},{"$f":"$B$9*(D27+E27)/2","v":39.479821607638435},{"$f":"$B$9*(E27+F27)/2","v":55.775987823446052},{"$f":"$B$9*(F27+G27)/2","v":74.156100714390888}]
["Depreciation",-100,{"$f":"-$B$7*(C30+B30)/2","v":-112.35000000000001},{"$f":"-$B$7*(D30+C30)/2","v":-123.58499999999999},{"$f":"-$B$7*(E30+D30)/2","v":-135.9435},{"$f":"-$B$7*(F30+E30)/2","v":-149.53785000000002},{"$f":"-$B$7*(G30+F30)/2","v":-164.49163500000006},{"$f":"getformula(G19)","v":"<-- =-$B$7*(G30+F30)/2"}]
["Profit before tax",{"$f":"SUM(B15:B19)","v":374.4},418.00324675324669,474.48142604149103,537.03632160763846,606.28813782344605,682.91946571439087]
["Taxes",{"$f":"-B20*$B$10","v":-149.76},-167.2012987012987,-189.79257041659642,-214.81452864305538,-242.51525512937843,-273.16778628575634]
["Profit after tax",{"$f":"B21+B20","v":224.64},250.80194805194799,284.68885562489459,322.22179296458307,363.77288269406762,409.75167942863453]
["Dividends",{"$f":"-B22*$B$11","v":-89.855999999999995},{"$f":"-$B$11*C22","v":-100.3207792207792},{"$f":"-$B$11*D22","v":-113.87554224995785},{"$f":"-$B$11*E22","v":-128.88871718583323},{"$f":"-$B$11*F22","v":-145.50915307762705},{"$f":"-$B$11*G22","v":-163.90067177145383}]
["Retained earnings",{"$f":"B23+B22","v":134.78399999999999},150.48116883116879,170.81331337493674,193.33307577874984,218.26372961644057,245.8510076571807]
[]
["Balance sheet"]
["Cash and marketable securities",80,{"$f":"C39-C28-C32","v":228.83116883116884},{"$f":"D39-D28-D32","v":397.82948220610558},{"$f":"E39-E28-E32","v":589.16605798485534},{"$f":"F39-F28-F32","v":805.23363760129598},{"$f":"G39-G28-G32","v":1048.6688802584763}]
["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",1070,{"$f":"$B$5*C15","v":1177},{"$f":"$B$5*D15","v":1294.7},1424.17,1566.5870000000002,1723.2457000000004,{"$f":"getformula(G30)","v":"<-- =$B$5*G15"}]
["     Depreciation",-300,{"$f":"B31+C19","v":-412.35},{"$f":"C31+D19","v":-535.93500000000006},{"$f":"D31+E19","v":-671.87850000000003},{"$f":"E31+F19","v":-821.41635000000008},{"$f":"F31+G19","v":-985.90798500000017},{"$f":"getformula(G31)","v":"<-- =F31+G19"}]
["     Net fixed assets",{"$f":"B30+B31","v":770},{"$f":"C30+C31","v":764.65},{"$f":"D30+D31","v":758.76499999999999},752.29150000000004,745.17065000000014,737.33771500000023,{"$f":"getformula(G32)","v":"<-- =G30+G31"}]
["Total assets",{"$f":"B27+B28+B32","v":1000},{"$f":"C32+C28+C27","v":1158.4811688311688},{"$f":"D32+D28+D27","v":1338.0944822061056},{"$f":"E32+E28+E27","v":1541.1075579848552},{"$f":"F32+F28+F27","v":1770.0192876012961},{"$f":"G32+G28+G27","v":2027.5830952584765}]
[]
["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":300.48116883116882},{"$f":"C38+D24","v":471.29448220610556},{"$f":"D38+E24","v":664.62755798485546},{"$f":"E38+F24","v":882.89128760129597},{"$f":"F38+G24","v":1128.7422952584766}]
["Total liabilities and equity",{"$f":"SUM(B35:B38)","v":1000},1158.4811688311688,1338.0944822061056,1541.1075579848555,1770.0192876012961,2027.5830952584765]

{"sheet":"Page 143, bottom","index":4,"n_rows":50,"n_cols":8,"rows_kept":50,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["NET FIXED ASSETS CONSTANT\nDepreciation reinvested into fixed assets"]
["Sales growth",0.1]
["Current assets/Sales",0.15]
["Current liabilities/Sales",0.08]
["Net fixed assets","Constant"]
["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":12.13200615174299},{"$f":"$B$9*(C27+D27)/2","v":24.36545434747466},{"$f":"$B$9*(D27+E27)/2","v":38.21954037972538},{"$f":"$B$9*(E27+F27)/2","v":53.866017907042192},{"$f":"$B$9*(F27+G27)/2","v":71.493897064086127}]
["Depreciation",-100,{"$f":"-$B$7*(C30+B30)/2","v":-112.63157894736842},{"$f":"-$B$7*(D30+C30)/2","v":-124.48753462603878},{"$f":"-$B$7*(E30+D30)/2","v":-137.59148563930603},{"$f":"-$B$7*(F30+E30)/2","v":-152.07479991712771},{"$f":"-$B$7*(G30+F30)/2","v":-168.08267359261487},{"$f":"getformula(G19)","v":"<-- =-$B$7*(G30+F30)/2"}]
["Profit before tax",{"$f":"SUM(B15:B19)","v":374.4},417.50042720437455,472.87791972143583,534.12805474041932,601.84121798991453,676.66622347147131]
["Taxes",{"$f":"-B20*$B$10","v":-149.76},-167.00017088174982,-189.15116788857435,-213.65122189616773,-240.73648719596582,-270.66648938858856]
["Profit after tax",{"$f":"B21+B20","v":224.64},250.50025632262472,283.72675183286151,320.47683284425159,361.1047307939487,405.99973408288275]
["Dividends",{"$f":"-B22*$B$11","v":-89.855999999999995},{"$f":"-$B$11*C22","v":-100.2001025290499},{"$f":"-$B$11*D22","v":-113.49070073314461},{"$f":"-$B$11*E22","v":-128.19073313770065},{"$f":"-$B$11*F22","v":-144.44189231757949},{"$f":"-$B$11*G22","v":-162.39989363315311}]
["Retained earnings",{"$f":"B23+B22","v":134.78399999999999},150.30015379357482,170.23605109971692,192.28609970655094,216.66283847636922,243.59984044972964]
[]
["Balance sheet"]
["Cash and marketable securities",80,{"$f":"C39-C28-C32","v":223.30015379357474},{"$f":"D39-D28-D32","v":385.83620489329178},{"$f":"E39-E28-E32","v":569.65230459984264},{"$f":"F39-F28-F32","v":776.99814307621205},{"$f":"G39-G28-G32","v":1010.3492835259412}]
["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",1070,{"$f":"C32-C31","v":1182.6315789473683},{"$f":"D32-D31","v":1307.1191135734073},1444.7105992127133,1596.7853991298412,1764.868072722456,{"$f":"getformula(G30)","v":"<-- =G32-G31"}]
["     Depreciation",-300,{"$f":"B31+C19","v":-412.63157894736844},{"$f":"C31+D19","v":-537.11911357340728},{"$f":"D31+E19","v":-674.7105992127133},{"$f":"E31+F19","v":-826.78539912984104},{"$f":"F31+G19","v":-994.86807272245596},{"$f":"getformula(G31)","v":"<-- =F31+G19"}]
["     Net fixed assets",{"$f":"B30+B31","v":770},{"$f":"B32","v":770},{"$f":"C32","v":770},770,770,770,{"$f":"getformula(G32)","v":"<-- =F32"}]
["Total assets",{"$f":"B27+B28+B32","v":1000},{"$f":"C32+C28+C27","v":1158.3001537935747},{"$f":"D32+D28+D27","v":1337.3362048932918},{"$f":"E32+E28+E27","v":1539.3023045998425},{"$f":"F32+F28+F27","v":1766.6131430762121},{"$f":"G32+G28+G27","v":2021.9257835259414}]
[]
["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":300.30015379357485},{"$f":"C38+D24","v":470.53620489329177},{"$f":"D38+E24","v":662.82230459984271},{"$f":"E38+F24","v":879.4851430762119},{"$f":"F38+G24","v":1123.0849835259414}]
["Total liabilities and equity",{"$f":"SUM(B35:B38)","v":1000},1158.3001537935747,1337.3362048932918,1539.3023045998427,1766.6131430762121,2021.9257835259414]
[]
["Year",0,1,2,3,4,5]
["Free cash flow calculation"]
["Profit after tax",null,{"$f":"C22","v":250.50025632262472},{"$f":"D22","v":283.72675183286151},{"$f":"E22","v":320.47683284425159},{"$f":"F22","v":361.1047307939487},{"$f":"G22","v":405.99973408288275}]
["Add back depreciation",null,{"$f":"-C19","v":112.63157894736842},{"$f":"-D19","v":124.48753462603878},{"$f":"-E19","v":137.59148563930603},{"$f":"-F19","v":152.07479991712771},{"$f":"-G19","v":168.08267359261487},{"$f":"getformula(G44)","v":"<-- =-G19"}]
["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":-112.63157894736833},{"$f":"-(D30-C30)","v":-124.48753462603895},{"$f":"-(E30-D30)","v":-137.59148563930603},{"$f":"-(F30-E30)","v":-152.07479991712785},{"$f":"-(G30-F30)","v":-168.08267359261481},{"$f":"getformula(G47)","v":"<-- =-(G30-F30)"}]
["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,-7.2792036910457938,-14.619272608484795,-22.931724227835229,-32.319610744225315,-42.896338238451676]
["Free cash flow",null,{"$f":"SUM(C43:C49)","v":255.42105263157902},{"$f":"SUM(D43:D49)","v":280.60747922437656},{"$f":"SUM(E43:E49)","v":308.27510861641639},{"$f":"SUM(F43:F49)","v":338.6681200497232},{"$f":"SUM(G43:G49)","v":372.05469584443114}]

{"sheet":"Page 146","index":5,"n_rows":50,"n_cols":7,"rows_kept":50,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["NEGATIVE CASH BALANCES:  ILLUSTRATION"]
["Sales growth",0.2,"<-- Increased from 10%"]
["Current assets/Sales",0.2,"<-- Increased from 15%"]
["Current liabilities/Sales",0.08]
["Net fixed assets/Sales",0.8,"<-- Increased from 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.5,"<-- Increased from 40%"]
[]
["Year",0,1,2,3,4,5]
["Income statement"]
["Sales",{"$f":"1000","v":1000},{"$f":"B15*(1+$B$2)","v":1200},{"$f":"C15*(1+$B$2)","v":1440},{"$f":"D15*(1+$B$2)","v":1728},{"$f":"E15*(1+$B$2)","v":2073.6},{"$f":"F15*(1+$B$2)","v":2488.3199999999997}]
["Costs of goods sold",{"$f":"-B15*$B$6","v":-500},-600,-720,-864,-1036.8,-1244.1599999999999]
["Interest payments on debt",{"$f":"-$B$8*B36","v":-40},{"$f":"-$B$8*(B36+C36)/2","v":-40},{"$f":"-$B$8*(C36+D36)/2","v":-40},{"$f":"-$B$8*(D36+E36)/2","v":-40},{"$f":"-$B$8*(E36+F36)/2","v":-40},{"$f":"-$B$8*(F36+G36)/2","v":-40}]
["Interest earned on cash and marketable securities",{"$f":"$B$9*B27","v":6.4},{"$f":"$B$9*(B27+C27)/2","v":4.3213296398891909},{"$f":"$B$9*(C27+D27)/2","v":-0.30271288705463123},{"$f":"$B$9*(D27+E27)/2","v":-5.9633788557330538},{"$f":"$B$9*(E27+F27)/2","v":-12.888901494838056},{"$f":"$B$9*(F27+G27)/2","v":-21.355583566780698}]
["Depreciation",-100,{"$f":"-$B$7*(C30+B30)/2","v":-124.21052631578948},{"$f":"-$B$7*(D30+C30)/2","v":-155.81163434903047},{"$f":"-$B$7*(E30+D30)/2","v":-194.44443796471788},{"$f":"-$B$7*(F30+E30)/2","v":-241.59016827679343},{"$f":"-$B$7*(G30+F30)/2","v":-299.03418599014014}]
["Profit before tax",{"$f":"SUM(B15:B19)","v":366.4},440.11080332409972,523.88565276391489,623.59218317954912,742.32093022836852,883.77023044307896]
["Taxes",{"$f":"-B20*$B$10","v":-146.56},-176.0443213296399,-209.55426110556596,-249.43687327181965,-296.9283720913474,-353.50809217723162]
["Profit after tax",{"$f":"B21+B20","v":219.83999999999997},264.06648199445982,314.33139165834893,374.15530990772947,445.39255813702113,530.2621382658474]
["Dividends",{"$f":"-B22*$B$11","v":-109.91999999999999},{"$f":"-$B$11*C22","v":-132.03324099722991},{"$f":"-$B$11*D22","v":-157.16569582917447},{"$f":"-$B$11*E22","v":-187.07765495386474},{"$f":"-$B$11*F22","v":-222.69627906851056},{"$f":"-$B$11*G22","v":-265.1310691329237}]
["Retained earnings",{"$f":"B23+B22","v":109.91999999999999},132.03324099722991,157.16569582917447,187.07765495386474,222.69627906851056,265.1310691329237]
[]
["Balance sheet"]
["Cash and marketable securities",80,{"$f":"C39-C28-C32","v":28.033240997229768},{"$f":"D39-D28-D32","v":-35.601063173595549},{"$f":"E39-E28-E32","v":-113.48340821973079},{"$f":"F39-F28-F32","v":-208.7391291512206},{"$f":"G39-G28-G32","v":-325.15046001829683}]
["Current assets",{"$f":"$B$3*B15","v":200},{"$f":"C15*$B$3","v":240},{"$f":"D15*$B$3","v":288},{"$f":"E15*$B$3","v":345.6},{"$f":"F15*$B$3","v":414.72},{"$f":"G15*$B$3","v":497.66399999999999}]
["Fixed assets"]
["     At cost",{"$f":"B32-B31","v":1100},1384.2105263157896,1732.0221606648199,2156.8665986295377,2674.9367669063313,3305.7469528964712]
["     Depreciation",-300,{"$f":"B31+C19","v":-424.21052631578948},{"$f":"C31-$B$7*(D30+C30)/2","v":-580.02216066481992},{"$f":"D31-$B$7*(E30+D30)/2","v":-774.46659862953777},{"$f":"E31-$B$7*(F30+E30)/2","v":-1016.0567669063312},{"$f":"F31-$B$7*(G30+F30)/2","v":-1315.0909528964712}]
["     Net fixed assets",{"$f":"B15*$B$5","v":800},960,1152,1382.4,1658.88,1990.6559999999999]
["Total assets",{"$f":"B27+B28+B32","v":1080},{"$f":"C32+C28+C27","v":1228.0332409972298},{"$f":"D32+D28+D27","v":1404.3989368264045},{"$f":"E32+E28+E27","v":1614.5165917802692},{"$f":"F32+F28+F27","v":1864.8608708487798},{"$f":"G32+G28+G27","v":2163.1695399817027}]
[]
["Current liabilities",{"$f":"B15*$B$4","v":80},96,115.2,138.24,165.88800000000001,199.06559999999999]
["Debt",{"$f":"B33-B35-B37-B38","v":400},{"$f":"B36","v":400},400,400,400,400]
["Stock",450,450,450,450,450,450]
["Accumulated retained earnings",150,{"$f":"B38+C24","v":282.03324099722988},{"$f":"C38+D24","v":439.19893682640435},{"$f":"D38+E24","v":626.27659178026909},{"$f":"E38+F24","v":848.97287084877962},{"$f":"F38+G24","v":1114.1039399817032}]
["Total liabilities and equity",{"$f":"SUM(B35:B38)","v":1080},1228.0332409972298,1404.3989368264045,1614.5165917802692,1864.8608708487795,2163.1695399817031]
[]
["Year",0,1,2,3,4,5]
["Free cash flow calculation"]
["Profit after tax",null,{"$f":"C22","v":264.06648199445982},{"$f":"D22","v":314.33139165834893},{"$f":"E22","v":374.15530990772947},{"$f":"F22","v":445.39255813702113},{"$f":"G22","v":530.2621382658474}]
["Add back depreciation",null,{"$f":"-C19","v":124.21052631578948},{"$f":"-D19","v":155.81163434903047},{"$f":"-E19","v":194.44443796471788},{"$f":"-F19","v":241.59016827679343},{"$f":"-G19","v":299.03418599014014}]
["Subtract increase in current assets",null,{"$f":"-(C28-B28)","v":-40},{"$f":"-(D28-C28)","v":-48},{"$f":"-(E28-D28)","v":-57.600000000000023},{"$f":"-(F28-E28)","v":-69.12},{"$f":"-(G28-F28)","v":-82.94399999999996}]
["Add back increase in current liabilities",null,{"$f":"C35-B35","v":16},{"$f":"D35-C35","v":19.200000000000003},{"$f":"E35-D35","v":23.040000000000006},{"$f":"F35-E35","v":27.647999999999996},{"$f":"G35-F35","v":33.177599999999984}]
["Subtract increase in fixed assets at cost",null,{"$f":"-(C30-B30)","v":-284.21052631578959},{"$f":"-(D30-C30)","v":-347.81163434903033},{"$f":"-(E30-D30)","v":-424.84443796471783},{"$f":"-(F30-E30)","v":-518.07016827679354},{"$f":"-(G30-F30)","v":-630.81018599013987}]
["Add back after-tax interest on debt",null,{"$f":"-(1-$B$10)*C17","v":24},24,24,24,24]
["Subtract after-tax interest on cash and mkt. securities",null,-2.5927977839335146,0.18162773223277873,3.5780273134398324,7.733340896902833,12.813350140068419]
["Free cash flow",null,{"$f":"SUM(C43:C49)","v":101.47368421052619},{"$f":"SUM(D43:D49)","v":117.71301939058182},{"$f":"SUM(E43:E49)","v":136.77333722116927},{"$f":"SUM(F43:F49)","v":159.1738990339239},{"$f":"SUM(G43:G49)","v":185.53308840591612}]

{"sheet":"Page 147","index":6,"n_rows":50,"n_cols":8,"rows_kept":50,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["NO NEGATIVE CASH BALANCES"]
["Sales growth",0.2,"<-- Increased from 10%"]
["Current assets/Sales",0.2,"<-- Increased from 15%"]
["Current liabilities/Sales",0.08]
["Net fixed assets/Sales",0.8,"<-- Increased from 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.5,"<-- Increased from 40%"]
[]
["Year",0,1,2,3,4,5]
["Income statement"]
["Sales",{"$f":"1000","v":1000},{"$f":"B15*(1+$B$2)","v":1200},{"$f":"C15*(1+$B$2)","v":1440},{"$f":"D15*(1+$B$2)","v":1728},{"$f":"E15*(1+$B$2)","v":2073.6},{"$f":"F15*(1+$B$2)","v":2488.3199999999997}]
["Costs of goods sold",{"$f":"-B15*$B$6","v":-500},-600,-720,-864,-1036.8,-1244.1599999999999]
["Interest payments on debt",{"$f":"-$B$8*B36","v":-40},{"$f":"-$B$8*(B36+C36)/2","v":-40},{"$f":"-$B$8*(C36+D36)/2","v":-41.785474640381352},{"$f":"-$B$8*(D36+E36)/2","v":-47.487934873333465},{"$f":"-$B$8*(E36+F36)/2","v":-56.217637553356511},{"$f":"-$B$8*(F36+G36)/2","v":-66.934606528589697}]
["Interest earned on cash and marketable securities",{"$f":"$B$9*B27","v":6.4},{"$f":"$B$9*(B27+C27)/2","v":4.3213296398891909},{"$f":"$B$9*(C27+D27)/2","v":1.1213296398891908},{"$f":"$B$9*(D27+E27)/2","v":0},{"$f":"$B$9*(E27+F27)/2","v":0},{"$f":"$B$9*(F27+G27)/2","v":0}]
["Depreciation",-100,{"$f":"-$B$7*(C30+B30)/2","v":-124.21052631578948},{"$f":"-$B$7*(D30+C30)/2","v":-155.81163434903047},{"$f":"-$B$7*(E30+D30)/2","v":-194.44443796471788},{"$f":"-$B$7*(F30+E30)/2","v":-241.59016827679343},{"$f":"-$B$7*(G30+F30)/2","v":-299.03418599014014}]
["Profit before tax",{"$f":"SUM(B15:B19)","v":366.4},440.11080332409972,523.52422065047733,622.06762716194874,738.99219416984999,878.19120748127]
["Taxes",{"$f":"-B20*$B$10","v":-146.56},-176.0443213296399,-209.40968826019093,-248.82705086477949,-295.59687766794002,-351.27648299250802]
["Profit after tax",{"$f":"B21+B20","v":219.83999999999997},264.06648199445982,314.1145323902864,373.24057629716924,443.39531650190997,526.91472448876198]
["Dividends",{"$f":"-B22*$B$11","v":-109.91999999999999},{"$f":"-$B$11*C22","v":-132.03324099722991},{"$f":"-$B$11*D22","v":-157.0572661951432},{"$f":"-$B$11*E22","v":-186.62028814858462},{"$f":"-$B$11*F22","v":-221.69765825095499},{"$f":"-$B$11*G22","v":-263.45736224438099}]
["Retained earnings",{"$f":"B23+B22","v":109.91999999999999},132.03324099722991,157.0572661951432,186.62028814858462,221.69765825095499,263.45736224438099]
[]
["Balance sheet"]
["Cash and marketable securities",80,{"$f":"C39-C28-C32","v":28.033240997229768},{"$f":"D39-D28-D32","v":0},{"$f":"E39-E28-E32","v":0},{"$f":"F39-F28-F32","v":0},{"$f":"G39-G28-G32","v":0},{"$f":"getformula(G27)","v":"<-- =G39-G28-G32"}]
["Current assets",{"$f":"$B$3*B15","v":200},{"$f":"C15*$B$3","v":240},{"$f":"D15*$B$3","v":288},{"$f":"E15*$B$3","v":345.6},{"$f":"F15*$B$3","v":414.72},{"$f":"G15*$B$3","v":497.66399999999999}]
["Fixed assets"]
["     At cost",{"$f":"B32-B31","v":1100},1384.2105263157896,1732.0221606648199,2156.8665986295377,2674.9367669063313,3305.7469528964712]
["     Depreciation",-300,{"$f":"B31+C19","v":-424.21052631578948},{"$f":"C31+D19","v":-580.02216066481992},{"$f":"D31+E19","v":-774.46659862953777},{"$f":"E31+F19","v":-1016.0567669063312},{"$f":"F31+G19","v":-1315.0909528964712}]
["     Net fixed assets",{"$f":"B15*$B$5","v":800},960,1152,1382.4,1658.88,1990.6559999999999]
["Total assets",{"$f":"B27+B28+B32","v":1080},{"$f":"C32+C28+C27","v":1228.0332409972298},{"$f":"D32+D28+D27","v":1440},{"$f":"E32+E28+E27","v":1728},{"$f":"F32+F28+F27","v":2073.6000000000004},{"$f":"G32+G28+G27","v":2488.3199999999997}]
[]
["Current liabilities",{"$f":"B15*$B$4","v":80},96,115.2,138.24,165.88800000000001,199.06559999999999]
["Debt",{"$f":"B33-B35-B37-B38","v":400},{"$f":"MAX(C28+C32-C35-C37-C38,B36)","v":400},{"$f":"MAX(D28+D32-D35-D37-D38,C36)","v":435.70949280762687},{"$f":"MAX(E28+E32-E35-E37-E38,D36)","v":514.04920465904229},{"$f":"MAX(F28+F32-F35-F37-F38,E36)","v":610.30354640808775},{"$f":"MAX(G28+G32-G35-G37-G38,F36)","v":728.38858416370613},{"$f":"getformula(G36)","v":"<-- =MAX(G28+G32-G35-G37-G38,F36)"}]
["Stock",450,{"$f":"B37","v":450},{"$f":"C37","v":450},{"$f":"D37","v":450},{"$f":"E37","v":450},{"$f":"F37","v":450}]
["Accumulated retained earnings",150,{"$f":"B38+C24","v":282.03324099722988},{"$f":"C38+D24","v":439.09050719237308},{"$f":"D38+E24","v":625.7107953409577},{"$f":"E38+F24","v":847.40845359191269},{"$f":"F38+G24","v":1110.8658158362937}]
["Total liabilities and equity",{"$f":"SUM(B35:B38)","v":1080},1228.0332409972298,1440,1728,2073.6000000000004,2488.3199999999997]
[]
["Year",0,1,2,3,4,5]
["Free cash flow calculation"]
["Profit after tax",null,{"$f":"C22","v":264.06648199445982},{"$f":"D22","v":314.1145323902864},{"$f":"E22","v":373.24057629716924},{"$f":"F22","v":443.39531650190997},{"$f":"G22","v":526.91472448876198}]
["Add back depreciation",null,{"$f":"-C19","v":124.21052631578948},{"$f":"-D19","v":155.81163434903047},{"$f":"-E19","v":194.44443796471788},{"$f":"-F19","v":241.59016827679343},{"$f":"-G19","v":299.03418599014014}]
["Subtract increase in current assets",null,{"$f":"-(C28-B28)","v":-40},{"$f":"-(D28-C28)","v":-48},{"$f":"-(E28-D28)","v":-57.600000000000023},{"$f":"-(F28-E28)","v":-69.12},{"$f":"-(G28-F28)","v":-82.94399999999996}]
["Add back increase in current liabilities",null,{"$f":"C35-B35","v":16},{"$f":"D35-C35","v":19.200000000000003},{"$f":"E35-D35","v":23.040000000000006},{"$f":"F35-E35","v":27.647999999999996},{"$f":"G35-F35","v":33.177599999999984}]
["Subtract increase in fixed assets at cost",null,{"$f":"-(C30-B30)","v":-284.21052631578959},{"$f":"-(D30-C30)","v":-347.81163434903033},{"$f":"-(E30-D30)","v":-424.84443796471783},{"$f":"-(F30-E30)","v":-518.07016827679354},{"$f":"-(G30-F30)","v":-630.81018599013987}]
["Add back after-tax interest on debt",null,{"$f":"-(1-$B$10)*C17","v":24},25.071284784228812,28.492760924000077,33.730582532013905,40.160763917153815]
["Subtract after-tax interest on cash and mkt. securities",null,-2.5927977839335146,-0.67279778393351442,0,0,0]
["Free cash flow",null,{"$f":"SUM(C43:C49)","v":101.47368421052619},{"$f":"SUM(D43:D49)","v":117.71301939058179},{"$f":"SUM(E43:E49)","v":136.77333722116927},{"$f":"SUM(F43:F49)","v":159.17389903392376},{"$f":"SUM(G43:G49)","v":185.53308840591609}]

{"sheet":"Page 149","index":7,"n_rows":52,"n_cols":8,"rows_kept":52,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["TARGET DEBT/EQUITY RATIO\nCash is fixed, ratio of debt/equity changes in each year"]
["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 & marketable securities",0.08]
["Tax rate",0.4]
["Dividend payout ratio",0.6]
[]
["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":-30.342857142857145},{"$f":"-$B$8*(C36+D36)/2","v":-28.555449735449738},{"$f":"-$B$8*(D36+E36)/2","v":-28.036131054131058},{"$f":"-$B$8*(E36+F36)/2","v":-28.937123076923083},{"$f":"-$B$8*(F36+G36)/2","v":-31.646220000000007}]
["Interest earned on cash & marketable securities",{"$f":"$B$9*B27","v":6.4},{"$f":"$B$9*(B27+C27)/2","v":6.4},{"$f":"$B$9*(C27+D27)/2","v":6.4},{"$f":"$B$9*(D27+E27)/2","v":6.4},{"$f":"$B$9*(E27+F27)/2","v":6.4},{"$f":"$B$9*(F27+G27)/2","v":6.4}]
["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},409.37293233082704,445.36726494599066,482.55371148851361,520.92496604915789,560.24199956040536]
["Taxes",{"$f":"-B20*$B$10","v":-149.76},-163.74917293233082,-178.14690597839626,-193.02148459540547,-208.36998641966318,-224.09679982416216]
["Profit after tax",{"$f":"B21+B20","v":224.64},245.62375939849622,267.22035896759439,289.53222689310815,312.55497962949471,336.14519973624317]
["Dividends",{"$f":"-B22*$B$11","v":-134.78399999999999},{"$f":"-$B$11*C22","v":-147.37425563909773},{"$f":"-$B$11*D22","v":-160.33221538055662},{"$f":"-$B$11*E22","v":-173.71933613586489},{"$f":"-$B$11*F22","v":-187.53298777769683},{"$f":"-$B$11*G22","v":-201.6871198417459}]
["Retained earnings",{"$f":"B23+B22","v":89.855999999999995},98.249503759398493,106.88814358703777,115.81289075724325,125.02199185179788,134.45807989449727]
[]
["Balance sheet"]
["Cash and marketable securities",80,{"$f":"B27","v":80},{"$f":"C27","v":80},{"$f":"D27","v":80},{"$f":"E27","v":80},{"$f":"F27","v":80}]
["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-$B$7*(C30+B30)/2","v":-416.68421052631578},{"$f":"C31-$B$7*(D30+C30)/2","v":-554.16149584487539},{"$f":"D31-$B$7*(E30+D30)/2","v":-715.47165330223072},{"$f":"E31-$B$7*(F30+E30)/2","v":-904.05956417614982},{"$f":"F31-$B$7*(G30+F30)/2","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":1092},{"$f":"D32+D28+D27","v":1193.2},{"$f":"E32+E28+E27","v":1304.5200000000002},{"$f":"F32+F28+F27","v":1426.9720000000002},{"$f":"G32+G28+G27","v":1561.6692000000003}]
[]
["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":"C41*(C37+C38)","v":286.85714285714283},{"$f":"D41*(D37+D38)","v":284.25185185185188},{"$f":"E41*(E37+E38)","v":276.47076923076924},{"$f":"F41*(F37+F38)","v":302.27169230769238},{"$f":"G41*(G37+G38)","v":330.65270769230773},{"$f":"getformula(G36)","v":"<-- =G41*(G37+G38)"}]
["Stock",450,{"$f":"C33-C35-C36-C38","v":468.89335338345859},{"$f":"D33-D35-D36-D38","v":457.010500801712},{"$f":"E33-E35-E36-E38","v":450.61869266555141},{"$f":"F33-F35-F36-F38","v":411.59977773683056},{"$f":"G33-G35-G36-G38","v":371.7450824577179},{"$f":"getformula(G37)","v":"<-- =G33-G35-G36-G38"}]
["Accumulated retained earnings",150,{"$f":"B38+C24","v":248.24950375939849},{"$f":"C38+D24","v":355.13764734643627},{"$f":"D38+E24","v":470.95053810367949},{"$f":"E38+F24","v":595.9725299554774},{"$f":"F38+G24","v":730.43060984997464}]
["Total liabilities and equity",{"$f":"SUM(B35:B38)","v":1000},1092,1193.2000000000003,1304.5200000000002,1426.9720000000002,1561.6692000000003]
[]
["Target debt/equity ratio",{"$f":"B36/(B37+B38)","v":0.53333333333333333},0.4,0.35,0.3,{"$f":"E41","v":0.3},{"$f":"F41","v":0.3}]
[]
["Year",0,1,2,3,4,5]
["Free cash flow calculation"]
["Profit after tax",null,{"$f":"C22","v":245.62375939849622},{"$f":"D22","v":267.22035896759439},{"$f":"E22","v":289.53222689310815},{"$f":"F22","v":312.55497962949471},{"$f":"G22","v":336.14519973624317}]
["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":18.205714285714286},17.133269841269843,16.821678632478633,17.362273846153848,18.987732000000005]
["Subtract after-tax interest on cash & mkt. securities",null,-3.84,-3.84,-3.84,-3.84,-3.84]
["Free cash flow",null,{"$f":"SUM(C45:C51)","v":175.98947368421048},{"$f":"SUM(D45:D51)","v":188.11362880886421},{"$f":"SUM(E45:E51)","v":200.87390552558662},{"$f":"SUM(F45:F51)","v":214.27325347564846},{"$f":"SUM(G45:G51)","v":228.30853173624357}]

{"sheet":"Pages 151-152","index":8,"n_rows":72,"n_cols":8,"rows_kept":72,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["PROJECT FINANCE\nNo dividends, debt repayment schedule fixed, net fixed assets constant"]
["Sales growth",0.15]
["Current assets/Sales",0.15]
["Current liabilities/Sales",0.08]
["Costs of goods sold/Sales",0.45]
["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,"<-- No dividends until all the debt is paid off"]
[]
["Year",0,1,2,3,4,5]
["Income statement"]
["Sales",null,1150,{"$f":"C14*(1+$B$2)","v":1322.5},{"$f":"D14*(1+$B$2)","v":1520.8749999999998},{"$f":"E14*(1+$B$2)","v":1749.0062499999997},{"$f":"F14*(1+$B$2)","v":2011.3571874999996}]
["Costs of goods sold",null,{"$f":"-C14*$B$5","v":-517.5},{"$f":"-D14*$B$5","v":-595.125},{"$f":"-E14*$B$5","v":-684.39374999999995},{"$f":"-F14*$B$5","v":-787.05281249999985},{"$f":"-G14*$B$5","v":-905.11073437499977}]
["Interest payments on debt",null,{"$f":"-$B$7*(B35+C35)/2","v":-90},{"$f":"-$B$7*(C35+D35)/2","v":-70},{"$f":"-$B$7*(D35+E35)/2","v":-50},{"$f":"-$B$7*(E35+F35)/2","v":-30},{"$f":"-$B$7*(F35+G35)/2","v":-10}]
["Interest earned on cash and marketable securities",null,{"$f":"$B$8*(B26+C26)/2","v":0.76574633304573581},{"$f":"$B$8*(C26+D26)/2","v":3.3207019789604786},{"$f":"$B$8*(D26+E26)/2","v":9.4853309058247213},{"$f":"$B$8*(E26+F26)/2","v":21.277477890096144},{"$f":"$B$8*(F26+G26)/2","v":39.681741998288707}]
["Depreciation",null,{"$f":"-$B$6*(C29+B29)/2","v":-210.52631578947367},{"$f":"-$B$6*(D29+C29)/2","v":-232.6869806094183},{"$f":"-$B$6*(E29+D29)/2","v":-257.18034698935708},{"$f":"-$B$6*(F29+E29)/2","v":-284.25196246192098},{"$f":"-$B$6*(G29+F29)/2","v":-314.173221668439}]
["Profit before tax",null,{"$f":"SUM(C14:C18)","v":332.7394305435721},{"$f":"SUM(D14:D18)","v":428.00872136954223},{"$f":"SUM(E14:E18)","v":538.78623391646749},{"$f":"SUM(F14:F18)","v":668.97895292817498},{"$f":"SUM(G14:G18)","v":821.75497345484951}]
["Taxes",null,{"$f":"-C19*$B$9","v":-133.09577221742884},{"$f":"-D19*$B$9","v":-171.2034885478169},{"$f":"-E19*$B$9","v":-215.51449356658702},{"$f":"-F19*$B$9","v":-267.59158117126998},{"$f":"-G19*$B$9","v":-328.70198938193982}]
["Profit after tax",null,{"$f":"C20+C19","v":199.64365832614325},{"$f":"D20+D19","v":256.80523282172533},{"$f":"E20+E19","v":323.27174034988047},{"$f":"F20+F19","v":401.387371756905},{"$f":"G20+G19","v":493.05298407290968}]
["Dividends",null,{"$f":"-$B$10*C21","v":0},{"$f":"-$B$10*D21","v":0},{"$f":"-$B$10*E21","v":0},{"$f":"-$B$10*F21","v":0},{"$f":"-$B$10*G21","v":0}]
["Retained earnings",null,{"$f":"C22+C21","v":199.64365832614325},{"$f":"D22+D21","v":256.80523282172533},{"$f":"E22+E21","v":323.27174034988047},{"$f":"F22+F21","v":401.387371756905},{"$f":"G22+G21","v":493.05298407290968}]
[]
["Balance sheet"]
["Cash and marketable securities",0,{"$f":"C38-C27-C31","v":19.143658326143395},{"$f":"D38-D27-D31","v":63.873891147868562},{"$f":"E38-E27-E31","v":173.25938149774947},{"$f":"F38-F27-F31","v":358.6775657546541},{"$f":"G38-G27-G31","v":633.3659842025636},{"$f":"getformula(G26)","v":"<-- =G38-G27-G31"}]
["Current assets",200,{"$f":"C14*$B$3","v":172.5},{"$f":"D14*$B$3","v":198.375},{"$f":"E14*$B$3","v":228.13124999999997},{"$f":"F14*$B$3","v":262.35093749999993},{"$f":"G14*$B$3","v":301.70357812499992}]
["Fixed assets"]
["     At cost",2000,{"$f":"C31-C30","v":2210.5263157894738},{"$f":"D31-D30","v":2443.213296398892},{"$f":"E31-E30","v":2700.3936433882491},{"$f":"F31-F30","v":2984.6456058501699},{"$f":"G31-G30","v":3298.8188275186094}]
["     Depreciation",0,{"$f":"B30-$B$6*(C29+B29)/2","v":-210.52631578947367},{"$f":"C30-$B$6*(D29+C29)/2","v":-443.213296398892},{"$f":"D30-$B$6*(E29+D29)/2","v":-700.39364338824907},{"$f":"E30-$B$6*(F29+E29)/2","v":-984.64560585017011},{"$f":"F30-$B$6*(G29+F29)/2","v":-1298.8188275186092},{"$f":"getformula(G30)","v":"<-- =F30-$B$6*(G29+F29)/2"}]
["     Net fixed assets",{"$f":"B29-B30","v":2000},{"$f":"B31","v":2000},{"$f":"C31","v":2000},{"$f":"D31","v":2000},{"$f":"E31","v":2000},{"$f":"F31","v":2000},"<-- NFA don't change"]
["Total assets",{"$f":"B26+B27+B31","v":2200},{"$f":"C31+C27+C26","v":2191.6436583261434},{"$f":"D31+D27+D26","v":2262.2488911478686},{"$f":"E31+E27+E26","v":2401.3906314977494},{"$f":"F31+F27+F26","v":2621.028503254654},{"$f":"G31+G27+G26","v":2935.0695623275633}]
[]
["Current liabilities",100,{"$f":"C14*$B$4","v":92},{"$f":"D14*$B$4","v":105.8},{"$f":"E14*$B$4","v":121.66999999999999},{"$f":"F14*$B$4","v":139.92049999999998},{"$f":"G14*$B$4","v":160.90857499999996}]
["Debt",{"$f":"B32-B34-B36-B37","v":1000},{"$f":"B35-$B$35/5","v":800},{"$f":"C35-$B$35/5","v":600},{"$f":"D35-$B$35/5","v":400},{"$f":"E35-$B$35/5","v":200},{"$f":"F35-$B$35/5","v":0},{"$f":"getformula(G35)","v":"<-- =F35-$B$35/5"}]
["Stock",1100,{"$f":"B36","v":1100},{"$f":"C36","v":1100},{"$f":"D36","v":1100},{"$f":"E36","v":1100},{"$f":"F36","v":1100}]
["Accumulated retained earnings",0,{"$f":"B37+C23","v":199.64365832614325},{"$f":"C37+D23","v":456.44889114786861},{"$f":"D37+E23","v":779.72063149774908},{"$f":"E37+F23","v":1181.108003254654},{"$f":"F37+G23","v":1674.1609873275638}]
["Total liabilities and equity",{"$f":"SUM(B34:B37)","v":2200},2191.6436583261434,2262.2488911478686,2401.3906314977494,2621.028503254654,2935.0695623275637]
[]
[]
["FREE CASH FLOW CALCULATION"]
["Year",0,1,2,3,4,5]
["Profit after tax",null,{"$f":"C21","v":199.64365832614325},{"$f":"D21","v":256.80523282172533},{"$f":"E21","v":323.27174034988047},{"$f":"F21","v":401.387371756905},{"$f":"G21","v":493.05298407290968}]
["Add back depreciation",null,{"$f":"-C18","v":210.52631578947367},{"$f":"-D18","v":232.6869806094183},{"$f":"-E18","v":257.18034698935708},{"$f":"-F18","v":284.25196246192098},{"$f":"-G18","v":314.173221668439}]
["Subtract increase in current assets",null,{"$f":"-(C27-B27)","v":27.5},{"$f":"-(D27-C27)","v":-25.875},{"$f":"-(E27-D27)","v":-29.756249999999966},{"$f":"-(F27-E27)","v":-34.219687499999964},{"$f":"-(G27-F27)","v":-39.352640624999992}]
["Add back increase in current liabilities",null,{"$f":"C34-B34","v":-8},{"$f":"D34-C34","v":13.799999999999997},{"$f":"E34-D34","v":15.86999999999999},{"$f":"F34-E34","v":18.250499999999988},{"$f":"G34-F34","v":20.988074999999981}]
["Subtract increase in fixed assets at cost",null,{"$f":"-(C29-B29)","v":-210.52631578947376},{"$f":"-(D29-C29)","v":-232.68698060941824},{"$f":"-(E29-D29)","v":-257.18034698935708},{"$f":"-(F29-E29)","v":-284.25196246192081},{"$f":"-(G29-F29)","v":-314.17322166843951}]
["Add back after-tax interest on debt",null,{"$f":"-C16*(1-$B$9)","v":54},42,30,18,6]
["Subtract after-tax interest on cash and mkt. securities",null,-0.45944779982744144,-1.9924211873762872,-5.6911985434948322,-12.766486734057686,-23.809045198973223]
["Free cash flow",null,{"$f":"SUM(C43:C49)","v":272.68421052631572},{"$f":"SUM(D43:D49)","v":284.7378116343491},{"$f":"SUM(E43:E49)","v":333.69429180638576},{"$f":"SUM(F43:F49)","v":390.65169752284748},{"$f":"SUM(G43:G49)","v":456.87937324893585}]
[]
[]
[]
[]
[]
["RETURN ON EQUITY (ROE)"]
["Year",0,1,2,3,4,5]
["Equity cash flow",{"$f":"-B36","v":-1100},{"$f":"C22","v":0},{"$f":"D22","v":0},{"$f":"E22","v":0},{"$f":"F22","v":0},{"$f":"G22+G36+G37","v":2774.1609873275638},"<-- =G23+G37+G38"]
["RETURN ON EQUITY (ROE)",{"$f":"IRR(B58:G58)","v":0.20322762667997751},"<-- =IRR(B59:G59)"]
[]
[]
["Data table: ROE as a function of initial",null,{"$f":"B59","v":0.20322762667997751},"<-- =B60&\" , table header\""]
["equity investment",2000,0.1458139510999541]
[null,1800,0.15430558052240095]
[null,1600,0.16458053050499055]
[null,1400,0.17728010551682427]
[null,1200,0.19340373895191187]
[null,1000,0.2146080377879156]
[null,800,0.2438780449255884]
[null,600,0.28728224539655245]
[null,400,0.35978951870035902]
[null,200,0.51539259550046568]

{"sheet":"Pages 153-154","index":9,"n_rows":72,"n_cols":8,"rows_kept":72,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["PROJECT FINANCE\nWith these parameters the project cannot pay off its debt"]
["Sales growth",0.15]
["Current assets/Sales",0.15]
["Current liabilities/Sales",0.08]
["Costs of goods sold/Sales",0.55000000000000004]
["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,"<-- No dividends until all the debt is paid off"]
[]
["Year",0,1,2,3,4,5]
["Income statement"]
["Sales",null,1150,{"$f":"C14*(1+$B$2)","v":1322.5},{"$f":"D14*(1+$B$2)","v":1520.8749999999998},{"$f":"E14*(1+$B$2)","v":1749.0062499999997},{"$f":"F14*(1+$B$2)","v":2011.3571874999996}]
["Costs of goods sold",null,{"$f":"-C14*$B$5","v":-632.5},{"$f":"-D14*$B$5","v":-727.37500000000011},{"$f":"-E14*$B$5","v":-836.48124999999993},{"$f":"-F14*$B$5","v":-961.95343749999995},{"$f":"-G14*$B$5","v":-1106.2464531249998}]
["Interest payments on debt",null,{"$f":"-$B$7*(B35+C35)/2","v":-90},{"$f":"-$B$7*(C35+D35)/2","v":-70},{"$f":"-$B$7*(D35+E35)/2","v":-50},{"$f":"-$B$7*(E35+F35)/2","v":-30},{"$f":"-$B$7*(F35+G35)/2","v":-10}]
["Interest earned on cash and marketable securities",null,{"$f":"$B$8*(B26+C26)/2","v":-2.06212252120371},{"$f":"$B$8*(C26+D26)/2","v":-5.7261604504792016},{"$f":"$B$8*(D26+E26)/2","v":-6.9983650959760419},{"$f":"$B$8*(E26+F26)/2","v":-4.0575841015380503},{"$f":"$B$8*(F26+G26)/2","v":3.8538950790183844}]
["Depreciation",null,{"$f":"-$B$6*(C29+B29)/2","v":-210.52631578947367},{"$f":"-$B$6*(D29+C29)/2","v":-232.6869806094183},{"$f":"-$B$6*(E29+D29)/2","v":-257.18034698935708},{"$f":"-$B$6*(F29+E29)/2","v":-284.25196246192098},{"$f":"-$B$6*(G29+F29)/2","v":-314.173221668439}]
["Profit before tax",null,{"$f":"SUM(C14:C18)","v":214.91156168932261},{"$f":"SUM(D14:D18)","v":286.71185894010233},{"$f":"SUM(E14:E18)","v":370.21503791466671},{"$f":"SUM(F14:F18)","v":468.74326593654069},{"$f":"SUM(G14:G18)","v":584.79140778557917}]
["Taxes",null,{"$f":"-C19*$B$9","v":-85.964624675729056},{"$f":"-D19*$B$9","v":-114.68474357604094},{"$f":"-E19*$B$9","v":-148.08601516586668},{"$f":"-F19*$B$9","v":-187.4973063746163},{"$f":"-G19*$B$9","v":-233.91656311423168}]
["Profit after tax",null,{"$f":"C20+C19","v":128.94693701359355},{"$f":"D20+D19","v":172.02711536406139},{"$f":"E20+E19","v":222.12902274880003},{"$f":"F20+F19","v":281.24595956192439},{"$f":"G20+G19","v":350.87484467134749}]
["Dividends",null,{"$f":"-$B$10*C21","v":0},{"$f":"-$B$10*D21","v":0},{"$f":"-$B$10*E21","v":0},{"$f":"-$B$10*F21","v":0},{"$f":"-$B$10*G21","v":0}]
["Retained earnings",null,{"$f":"C22+C21","v":128.94693701359355},{"$f":"D22+D21","v":172.02711536406139},{"$f":"E22+E21","v":222.12902274880003},{"$f":"F22+F21","v":281.24595956192439},{"$f":"G22+G21","v":350.87484467134749}]
[]
["Balance sheet"]
["Cash and marketable securities",0,{"$f":"C38-C27-C31","v":-51.55306298640653},{"$f":"D38-D27-D31","v":-91.600947622345302},{"$f":"E38-E27-E31","v":-83.358174873544613},{"$f":"F38-F27-F31","v":-18.081402811620592},{"$f":"G38-G27-G31","v":114.42887623472689},{"$f":"getformula(G26)&\", the plug\"","v":"<-- =G38-G27-G31, the plug"}]
["Current assets",200,{"$f":"C14*$B$3","v":172.5},{"$f":"D14*$B$3","v":198.375},{"$f":"E14*$B$3","v":228.13124999999997},{"$f":"F14*$B$3","v":262.35093749999993},{"$f":"G14*$B$3","v":301.70357812499992}]
["Fixed assets"]
["     At cost",2000,{"$f":"C31-C30","v":2210.5263157894738},{"$f":"D31-D30","v":2443.213296398892},{"$f":"E31-E30","v":2700.3936433882491},{"$f":"F31-F30","v":2984.6456058501699},{"$f":"G31-G30","v":3298.8188275186094}]
["     Depreciation",0,{"$f":"B30-$B$6*(C29+B29)/2","v":-210.52631578947367},{"$f":"C30-$B$6*(D29+C29)/2","v":-443.213296398892},{"$f":"D30-$B$6*(E29+D29)/2","v":-700.39364338824907},{"$f":"E30-$B$6*(F29+E29)/2","v":-984.64560585017011},{"$f":"F30-$B$6*(G29+F29)/2","v":-1298.8188275186092}]
["     Net fixed assets",{"$f":"B29-B30","v":2000},{"$f":"B31","v":2000},{"$f":"C31","v":2000},{"$f":"D31","v":2000},{"$f":"E31","v":2000},{"$f":"F31","v":2000},"<-- NFA don't change"]
["Total assets",{"$f":"B26+B27+B31","v":2200},{"$f":"C31+C27+C26","v":2120.9469370135935},{"$f":"D31+D27+D26","v":2106.7740523776547},{"$f":"E31+E27+E26","v":2144.7730751264553},{"$f":"F31+F27+F26","v":2244.2695346883793},{"$f":"G31+G27+G26","v":2416.1324543597266}]
[]
["Current liabilities",100,{"$f":"C14*$B$4","v":92},{"$f":"D14*$B$4","v":105.8},{"$f":"E14*$B$4","v":121.66999999999999},{"$f":"F14*$B$4","v":139.92049999999998},{"$f":"G14*$B$4","v":160.90857499999996}]
["Debt",{"$f":"B32-B34-B36-B37","v":1000},{"$f":"B35-$B$35/5","v":800},{"$f":"C35-$B$35/5","v":600},{"$f":"D35-$B$35/5","v":400},{"$f":"E35-$B$35/5","v":200},{"$f":"F35-$B$35/5","v":0}]
["Stock",1100,{"$f":"B36","v":1100},{"$f":"C36","v":1100},{"$f":"D36","v":1100},{"$f":"E36","v":1100},{"$f":"F36","v":1100}]
["Accumulated retained earnings",0,{"$f":"B37+C23","v":128.94693701359355},{"$f":"C37+D23","v":300.97405237765497},{"$f":"D37+E23","v":523.103075126455},{"$f":"E37+F23","v":804.34903468837933},{"$f":"F37+G23","v":1155.2238793597269}]
["Total liabilities and equity",{"$f":"SUM(B34:B37)","v":2200},2120.9469370135935,2106.7740523776547,2144.7730751264553,2244.2695346883793,2416.1324543597266]
[]
[]
["FREE CASH FLOW CALCULATION"]
["Year",0,1,2,3,4,5]
["Profit after tax",null,{"$f":"C21","v":128.94693701359355},{"$f":"D21","v":172.02711536406139},{"$f":"E21","v":222.12902274880003},{"$f":"F21","v":281.24595956192439},{"$f":"G21","v":350.87484467134749}]
["Add back depreciation",null,{"$f":"-C18","v":210.52631578947367},{"$f":"-D18","v":232.6869806094183},{"$f":"-E18","v":257.18034698935708},{"$f":"-F18","v":284.25196246192098},{"$f":"-G18","v":314.173221668439}]
["Subtract increase in current assets",null,{"$f":"-(C27-B27)","v":27.5},{"$f":"-(D27-C27)","v":-25.875},{"$f":"-(E27-D27)","v":-29.756249999999966},{"$f":"-(F27-E27)","v":-34.219687499999964},{"$f":"-(G27-F27)","v":-39.352640624999992}]
["Add back increase in current liabilities",null,{"$f":"C34-B34","v":-8},{"$f":"D34-C34","v":13.799999999999997},{"$f":"E34-D34","v":15.86999999999999},{"$f":"F34-E34","v":18.250499999999988},{"$f":"G34-F34","v":20.988074999999981}]
["Subtract increase in fixed assets at cost",null,{"$f":"-(C29-B29)","v":-210.52631578947376},{"$f":"-(D29-C29)","v":-232.68698060941824},{"$f":"-(E29-D29)","v":-257.18034698935708},{"$f":"-(F29-E29)","v":-284.25196246192081},{"$f":"-(G29-F29)","v":-314.17322166843951}]
["Add back after-tax interest on debt",null,{"$f":"-C16","v":90},70,50,30,10]
["Subtract after-tax interest on cash and mkt. securities",null,2.06212252120371,5.7261604504792016,6.9983650959760419,4.0575841015380503,-3.8538950790183844]
["Free cash flow",null,{"$f":"SUM(C43:C49)","v":240.50905953479719},{"$f":"SUM(D43:D49)","v":235.67827581454065},{"$f":"SUM(E43:E49)","v":265.24113784477612},{"$f":"SUM(F43:F49)","v":299.33435616346264},{"$f":"SUM(G43:G49)","v":338.65638396732862}]
[]
[]
[]
[]
[]
["RETURN ON EQUITY (ROE)"]
["Year",0,1,2,3,4,5]
["Equity cash flow",{"$f":"-B36","v":-1100},{"$f":"C22","v":0},{"$f":"D22","v":0},{"$f":"E22","v":0},{"$f":"F22","v":0},{"$f":"G22+G36+G37","v":2255.2238793597271},{"$f":"getformula(G58)","v":"<-- =G22+G36+G37"}]
["RETURN ON EQUITY (ROE)",{"$f":"IRR(B58:G58)","v":0.15440817944868046},{"$f":"getformula(B59)","v":"<-- =IRR(B58:G58)"}]
[]
[]
["Data table: ROE as a function of initial",null,{"$f":"B59","v":0.15440817944868046},"<-- =B60"]
["equity investment",2000,0.11398803498572541]
[null,1800,0.1198286814070213]
[null,1600,0.12696226083116735]
[null,1400,0.13587648353515269]
[null,1200,0.14734315703532386]
[null,1000,0.16266413698046778]
[null,800,0.18423264486363489]
[null,600,0.21702704295320707]
[null,400,0.27365444210694623]
[null,200,0.40096329673066644]

{"sheet":"Page 154","index":10,"n_rows":72,"n_cols":8,"rows_kept":72,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["PROJECT FINANCE\nWith these parameters the project cannot pay off its debt"]
["Sales growth",0.15]
["Current assets/Sales",0.15]
["Current liabilities/Sales",0.08]
["Costs of goods sold/Sales",0.55000000000000004]
["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,"<-- No dividends until all the debt is paid off"]
[]
["Year",0,1,2,3,4,5]
["Income statement"]
["Sales",null,1150,{"$f":"C14*(1+$B$2)","v":1322.5},{"$f":"D14*(1+$B$2)","v":1520.8749999999998},{"$f":"E14*(1+$B$2)","v":1749.0062499999997},{"$f":"F14*(1+$B$2)","v":2011.3571874999996}]
["Costs of goods sold",null,{"$f":"-C14*$B$5","v":-632.5},{"$f":"-D14*$B$5","v":-727.37500000000011},{"$f":"-E14*$B$5","v":-836.48124999999993},{"$f":"-F14*$B$5","v":-961.95343749999995},{"$f":"-G14*$B$5","v":-1106.2464531249998}]
["Interest payments on debt",null,{"$f":"-$B$7*(B35+C35)/2","v":-135},{"$f":"-$B$7*(C35+D35)/2","v":-105},{"$f":"-$B$7*(D35+E35)/2","v":-75},{"$f":"-$B$7*(E35+F35)/2","v":-45},{"$f":"-$B$7*(F35+G35)/2","v":-15}]
["Interest earned on cash and marketable securities",null,{"$f":"$B$8*(B26+C26)/2","v":-7.2670405522001698},{"$f":"$B$8*(C26+D26)/2","v":-21.350992458018837},{"$f":"$B$8*(D26+E26)/2","v":-33.063762437891292},{"$f":"$B$8*(E26+F26)/2","v":-40.585213281999351},{"$f":"$B$8*(F26+G26)/2","v":-43.158696536778692}]
["Depreciation",null,{"$f":"-$B$6*(C29+B29)/2","v":-210.52631578947367},{"$f":"-$B$6*(D29+C29)/2","v":-232.6869806094183},{"$f":"-$B$6*(E29+D29)/2","v":-257.18034698935708},{"$f":"-$B$6*(F29+E29)/2","v":-284.25196246192098},{"$f":"-$B$6*(G29+F29)/2","v":-314.173221668439}]
["Profit before tax",null,{"$f":"SUM(C14:C18)","v":164.70664365832616},{"$f":"SUM(D14:D18)","v":236.08702693256276},{"$f":"SUM(E14:E18)","v":319.14964057275142},{"$f":"SUM(F14:F18)","v":417.21563675607939},{"$f":"SUM(G14:G18)","v":532.77881616978198}]
["Taxes",null,{"$f":"-C19*$B$9","v":-65.88265746333046},{"$f":"-D19*$B$9","v":-94.434810773025106},{"$f":"-E19*$B$9","v":-127.65985622910057},{"$f":"-F19*$B$9","v":-166.88625470243176},{"$f":"-G19*$B$9","v":-213.1115264679128}]
["Profit after tax",null,{"$f":"C20+C19","v":98.823986194995697},{"$f":"D20+D19","v":141.65221615953766},{"$f":"E20+E19","v":191.48978434365085},{"$f":"F20+F19","v":250.32938205364763},{"$f":"G20+G19","v":319.66728970186921}]
["Dividends",null,{"$f":"-$B$10*C21","v":0},{"$f":"-$B$10*D21","v":0},{"$f":"-$B$10*E21","v":0},{"$f":"-$B$10*F21","v":0},{"$f":"-$B$10*G21","v":0}]
["Retained earnings",null,{"$f":"C22+C21","v":98.823986194995697},{"$f":"D22+D21","v":141.65221615953766},{"$f":"E22+E21","v":191.48978434365085},{"$f":"F22+F21","v":250.32938205364763},{"$f":"G22+G21","v":319.66728970186921}]
[]
["Balance sheet"]
["Cash and marketable securities",0,{"$f":"C38-C27-C31","v":-181.67601380500423},{"$f":"D38-D27-D31","v":-352.09879764546668},{"$f":"E38-E27-E31","v":-474.49526330181561},{"$f":"F38-F27-F31","v":-540.13506874816812},{"$f":"G38-G27-G31","v":-538.8323446712991},{"$f":"getformula(G26)&\", the plug\"","v":"<-- =G38-G27-G31, the plug"}]
["Current assets",200,{"$f":"C14*$B$3","v":172.5},{"$f":"D14*$B$3","v":198.375},{"$f":"E14*$B$3","v":228.13124999999997},{"$f":"F14*$B$3","v":262.35093749999993},{"$f":"G14*$B$3","v":301.70357812499992}]
["Fixed assets"]
["     At cost",2000,{"$f":"C31-C30","v":2210.5263157894738},{"$f":"D31-D30","v":2443.213296398892},{"$f":"E31-E30","v":2700.3936433882491},{"$f":"F31-F30","v":2984.6456058501699},{"$f":"G31-G30","v":3298.8188275186094}]
["     Depreciation",0,{"$f":"B30-$B$6*(C29+B29)/2","v":-210.52631578947367},{"$f":"C30-$B$6*(D29+C29)/2","v":-443.213296398892},{"$f":"D30-$B$6*(E29+D29)/2","v":-700.39364338824907},{"$f":"E30-$B$6*(F29+E29)/2","v":-984.64560585017011},{"$f":"F30-$B$6*(G29+F29)/2","v":-1298.8188275186092}]
["     Net fixed assets",{"$f":"B29-B30","v":2000},{"$f":"B31","v":2000},{"$f":"C31","v":2000},{"$f":"D31","v":2000},{"$f":"E31","v":2000},{"$f":"F31","v":2000},"<-- NFA don't change"]
["Total assets",{"$f":"B26+B27+B31","v":2200},{"$f":"C31+C27+C26","v":1990.8239861949958},{"$f":"D31+D27+D26","v":1846.2762023545333},{"$f":"E31+E27+E26","v":1753.6359866981843},{"$f":"F31+F27+F26","v":1722.2158687518317},{"$f":"G31+G27+G26","v":1762.8712334537006}]
[]
["Current liabilities",100,{"$f":"C14*$B$4","v":92},{"$f":"D14*$B$4","v":105.8},{"$f":"E14*$B$4","v":121.66999999999999},{"$f":"F14*$B$4","v":139.92049999999998},{"$f":"G14*$B$4","v":160.90857499999996}]
["Debt",1500,{"$f":"B35-$B$35/5","v":1200},{"$f":"C35-$B$35/5","v":900},{"$f":"D35-$B$35/5","v":600},{"$f":"E35-$B$35/5","v":300},{"$f":"F35-$B$35/5","v":0},{"$f":"getformula(G35)","v":"<-- =F35-$B$35/5"}]
["Stock",{"$f":"B32-B34-B35-B37","v":600},{"$f":"B36","v":600},{"$f":"C36","v":600},{"$f":"D36","v":600},{"$f":"E36","v":600},{"$f":"F36","v":600}]
["Accumulated retained earnings",0,{"$f":"B37+C23","v":98.823986194995697},{"$f":"C37+D23","v":240.47620235453337},{"$f":"D37+E23","v":431.96598669818422},{"$f":"E37+F23","v":682.2953687518318},{"$f":"F37+G23","v":1001.962658453701}]
["Total liabilities and equity",{"$f":"SUM(B34:B37)","v":2200},1990.8239861949958,1846.2762023545333,1753.6359866981843,1722.2158687518317,1762.8712334537008]
[]
[]
["FREE CASH FLOW CALCULATION"]
["Year",0,1,2,3,4,5]
["Profit after tax",null,{"$f":"C21","v":98.823986194995697},{"$f":"D21","v":141.65221615953766},{"$f":"E21","v":191.48978434365085},{"$f":"F21","v":250.32938205364763},{"$f":"G21","v":319.66728970186921}]
["Add back depreciation",null,{"$f":"-C18","v":210.52631578947367},{"$f":"-D18","v":232.6869806094183},{"$f":"-E18","v":257.18034698935708},{"$f":"-F18","v":284.25196246192098},{"$f":"-G18","v":314.173221668439}]
["Subtract increase in current assets",null,{"$f":"-(C27-B27)","v":27.5},{"$f":"-(D27-C27)","v":-25.875},{"$f":"-(E27-D27)","v":-29.756249999999966},{"$f":"-(F27-E27)","v":-34.219687499999964},{"$f":"-(G27-F27)","v":-39.352640624999992}]
["Add back increase in current liabilities",null,{"$f":"C34-B34","v":-8},{"$f":"D34-C34","v":13.799999999999997},{"$f":"E34-D34","v":15.86999999999999},{"$f":"F34-E34","v":18.250499999999988},{"$f":"G34-F34","v":20.988074999999981}]
["Subtract increase in fixed assets at cost",null,{"$f":"-(C29-B29)","v":-210.52631578947376},{"$f":"-(D29-C29)","v":-232.68698060941824},{"$f":"-(E29-D29)","v":-257.18034698935708},{"$f":"-(F29-E29)","v":-284.25196246192081},{"$f":"-(G29-F29)","v":-314.17322166843951}]
["Add back after-tax interest on debt",null,{"$f":"-C16","v":135},105,75,45,15]
["Subtract after-tax interest on cash and mkt. securities",null,7.2670405522001698,21.350992458018837,33.063762437891292,40.585213281999351,43.158696536778692]
["Free cash flow",null,{"$f":"SUM(C43:C49)","v":260.59102674719577},{"$f":"SUM(D43:D49)","v":255.92820861755655},{"$f":"SUM(E43:E49)","v":285.66729678154218},{"$f":"SUM(F43:F49)","v":319.94540783564719},{"$f":"SUM(G43:G49)","v":359.46142061364725}]
[]
[]
[]
[]
[]
["RETURN ON EQUITY (ROE)"]
["Year",0,1,2,3,4,5]
["Equity cash flow",{"$f":"-B36","v":-600},{"$f":"C22","v":0},{"$f":"D22","v":0},{"$f":"E22","v":0},{"$f":"F22","v":0},{"$f":"G22+G36+G37","v":1601.9626584537009},{"$f":"getformula(G58)","v":"<-- =G22+G36+G37"}]
["RETURN ON EQUITY (ROE)",{"$f":"IRR(B58:G58)","v":0.21702704028396513},{"$f":"getformula(B59)","v":"<-- =IRR(B58:G58)"}]
[]
[]
["Data table: ROE as a function of initial",null,{"$f":"B59","v":0.21702704028396513},{"$f":"getformula(C62)&\" , data table header\"","v":"<-- =B59 , data table header"}]
["equity investment",2000,0.10801423794014564]
[null,1800,0.11426147538255393]
[null,1600,0.12187858983688837]
[null,1400,0.13137770671980875]
[null,1200,0.14356680586784032]
[null,1000,0.15980393592409148]
[null,800,0.18257525492573246]
[null,600,0.21702704060898093]
[null,400,0.27611538429194105]
[null,200,0.40764971959833263]

{"sheet":"Page 155","index":11,"n_rows":74,"n_cols":8,"rows_kept":74,"cut":false,"hidden":false,"merged":[[0,0,0,6]]}
["COMPUTING THE ROE IN THE FIRST 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-$B$7*(C30+B30)/2","v":-416.68421052631578},{"$f":"C31-$B$7*(D30+C30)/2","v":-554.16149584487539},{"$f":"D31-$B$7*(E30+D30)/2","v":-715.47165330223072},{"$f":"E31-$B$7*(F30+E30)/2","v":-904.05956417614982},{"$f":"F31-$B$7*(G30+F30)/2","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 (mid-year discounting)"]
["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, NPV of row \"&ROW(A60)","v":"Enterprise value, NPV 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},{"$f":"getformula(B63)","v":"<-- =B27"}]
["Asset value, year 0",{"$f":"B63+B62","v":1428.371777452516},{"$f":"getformula(B64)","v":"<-- =B63+B62"}]
["Subtract out value of firm's debt today",{"$f":"-B36","v":-320},{"$f":"getformula(B65)","v":"<-- =-B36"}]
["Equity value",{"$f":"B64+B65","v":1108.371777452516},{"$f":"getformula(B66)","v":"<-- =B64+B65"}]
[]
[]
["RETURN ON EQUITY (ROE)"]
["Year",0,1,2,3,4,5]
["Projected dividends",{"$f":"-B66","v":-1108.371777452516},{"$f":"-C23","v":98.463260423786764},{"$f":"-D23","v":107.95186021078608},{"$f":"-E23","v":118.14498141691398},{"$f":"-F23","v":129.08131689259562},{"$f":"-G23","v":140.79990921275044}]
["Anticipated equity value, year 5",null,null,null,null,null,{"$f":"G59+G27-G36","v":1736.9933143889534},"<-- Terminal value + year 5 cash - year 5 debt"]
["Equity cash flow",{"$f":"SUM(B71:B72)","v":-1108.371777452516},98.463260423786764,107.95186021078608,118.14498141691398,129.08131689259562,1877.7932236017039,{"$f":"getformula(G73)","v":"<-- =SUM(G71:G72)"}]
["RETURN ON EQUITY (ROE)",{"$f":"IRR(B73:G73)","v":0.18292793925657369},{"$f":"getformula(B74)","v":"<-- =IRR(B73:G73)"}]

{"sheet":"Page 156","index":12,"n_rows":57,"n_cols":8,"rows_kept":57,"cut":false,"hidden":false,"merged":[[0,0,0,7]]}
["MODELING TAX LOSS CARRYFORWARDS"]
["Sales growth",0.11]
["Current assets/Sales",0.15]
["Current liabilities/Sales",0.08]
["Net fixed assets/Sales",0.45]
["Fixed costs",440]
["Costs of goods sold/Sales",0.55000000000000004]
["Depreciation rate",0.1]
["Interest rate on debt",0.1]
["Interest paid on cash and marketable securities",0.08]
["Initial tax-loss carryforward",-100]
["Tax rate",0.4]
["Dividend payout ratio",0.4]
[]
["Year",0,1,2,3,4,5]
["Income statement"]
["Sales",1000,{"$f":"B17*(1+$B$2)","v":1110},{"$f":"C17*(1+$B$2)","v":1232.1000000000001},{"$f":"D17*(1+$B$2)","v":1367.6310000000003},{"$f":"E17*(1+$B$2)","v":1518.0704100000005},{"$f":"F17*(1+$B$2)","v":1685.0581551000007}]
["Fixed costs",null,{"$f":"-$B$6","v":-440},-440,-440,-440,-440,{"$f":"getformula(G18)","v":"<-- =-$B$6"}]
["Costs of goods sold",null,{"$f":"-C17*$B$7","v":-610.5},-677.65500000000009,-752.19705000000022,-834.93872550000037,-926.78198530500049]
["Interest payments on debt",null,{"$f":"-$B$9*(B43+C43)/2","v":3},{"$f":"-$B$9*(C43+D43)/2","v":9},{"$f":"-$B$9*(D43+E43)/2","v":15},{"$f":"-$B$9*(E43+F43)/2","v":21},{"$f":"-$B$9*(F43+G43)/2","v":27}]
["Interest earned on cash and marketable securities",null,{"$f":"$B$10*AVERAGE(B34:C34)","v":0.98947368421052495},{"$f":"$B$10*AVERAGE(C34:D34)","v":-8.2662385558007472},-13.693986540335882,-16.099145115532153,-16.853264212671665,{"$f":"getformula(G21)","v":"<-- =$B$10*AVERAGE(F34:G34)"}]
["Depreciation",null,{"$f":"-$B$8*(C37+B37)/2","v":-81.552631578947384},{"$f":"-$B$8*(D37+C37)/2","v":-95.634224376731311},{"$f":"-$B$8*(E37+D37)/2","v":-111.80277167954515},{"$f":"-$B$8*(F37+E37)/2","v":-130.34446788265518},{"$f":"-$B$8*(G37+F37)/2","v":-151.58294975425048}]
["Profit before tax",null,{"$f":"SUM(C17:C22)","v":-18.063157894736861},19.544537067467985,64.937191780119065,117.68807150181277,176.83995582807808]
["Taxes",null,{"$f":"-MAX($B$12*C30,0)","v":0},{"$f":"-MAX($B$12*D30,0)","v":0},0,-33.642656981865187,-70.735982331231227,{"$f":"getformula(G24)","v":"<-- =-MAX($B$12*G30,0)"}]
["Profit after tax",null,{"$f":"C24+C23","v":-18.063157894736861},19.544537067467985,{"$f":"E24+E23","v":64.937191780119065},84.045414519947585,106.10397349684685]
["Dividends",null,{"$f":"IF(C25<0,0,$B$13*C25)","v":0},7.8178148269871945,{"$f":"IF(E25<0,0,$B$13*E25)","v":25.974876712047628},{"$f":"IF(F25<0,0,$B$13*F25)","v":33.618165807979032},{"$f":"IF(G25<0,0,$B$13*G25)","v":42.441589398738742},{"$f":"getformula(G26)","v":"<-- =IF(G25<0,0,$B$13*G25)"}]
["Retained earnings",null,{"$f":"C26+C25","v":-18.063157894736861},27.36235189445518,{"$f":"E26+E25","v":90.912068492166696},117.66358032792661,148.54556289558559]
[]
["Tax loss carryforward from previous years",null,{"$f":"B11","v":-100},{"$f":"MIN(C29+C23,0)","v":-118.06315789473686},{"$f":"MIN(D29+D23,0)","v":-98.518620827268876},{"$f":"MIN(E29+E23,0)","v":-33.581429047149811},0,{"$f":"getformula(G29)","v":"<-- =MIN(F29+F23,0)"}]
["Taxable profit",{"$f":"MAX(B25+B29,0)","v":0},{"$f":"MAX(C23+C29,0)","v":0},{"$f":"MAX(D23+D29,0)","v":0},0,84.106642454662961,176.83995582807808,{"$f":"getformula(G30)","v":"<-- =MAX(G23+G29,0)"}]
["Effective tax rate",null,{"$f":"-C24/C23","v":0},{"$f":"-D24/D23","v":0},0,0.28586293030850612,0.39999999999999997,{"$f":"getformula(G31)","v":"<-- =-G24/G23"}]
[]
["Balance sheet"]
["Cash and marketable securities",80,{"$f":"C46-C35-C39","v":-55.263157894736878},{"$f":"D46-D35-D39","v":-151.39280600028178},-190.95685750811526,-211.52177038018851,-209.80983493660312,"<-- Plug"]
["Current assets",{"$f":"$B$3*B17","v":150},{"$f":"C17*$B$3","v":166.5},{"$f":"D17*$B$3","v":184.81500000000003},{"$f":"E17*$B$3","v":205.14465000000004},{"$f":"F17*$B$3","v":227.71056150000007},{"$f":"G17*$B$3","v":252.7587232650001}]
["Fixed assets"]
["     At cost",{"$f":"B39-B38","v":750},881.0526315789474,1031.6318559556787,1204.4235776352241,1402.4657800178793,1629.19321506713]
["     Depreciation",-300,{"$f":"B38+C22","v":-381.5526315789474},{"$f":"C38+D22","v":-477.18685595567871},{"$f":"D38+E22","v":-588.98962763522388},{"$f":"E38+F22","v":-719.33409551787906},{"$f":"F38+G22","v":-870.9170452721296}]
["     Net fixed assets",{"$f":"B17*$B$5","v":450},499.5,554.44500000000005,615.43395000000021,683.13168450000023,758.27616979500033]
["Total assets",{"$f":"B34+B35+B39","v":680},{"$f":"C39+C35+C34","v":610.73684210526312},{"$f":"D39+D35+D34","v":587.86719399971832},{"$f":"E39+E35+E34","v":629.62174249188502},{"$f":"F39+F35+F34","v":699.32047561981176},{"$f":"G39+G35+G34","v":801.22505812339728}]
[]
["Current liabilities",{"$f":"B17*$B$4","v":80},88.8,98.568000000000012,109.41048000000002,121.44563280000004,134.80465240800007]
["Debt",{"$f":"B40-B42-B44-B45","v":0},{"$f":"B43-60","v":-60},{"$f":"C43-60","v":-120},-180,-240,-300,"<-- Decreases by 60 per year"]
["Stock",450,{"$f":"B44","v":450},450,450,450,450]
["Accumulated retained earnings",150,{"$f":"B45+C27","v":131.93684210526314},{"$f":"C45+D27","v":159.29919399971831},{"$f":"D45+E27","v":250.21126249188501},367.87484281981165,516.42040571539724,{"$f":"getformula(G45)","v":"<-- =F45+G27"}]
["Total liabilities and equity",{"$f":"SUM(B42:B45)","v":680},610.73684210526312,587.86719399971832,629.62174249188502,699.32047561981176,801.22505812339728]
[]
["Year",0,1,2,3,4,5]
["Free cash flow calculation"]
["Profit after tax",null,{"$f":"C25","v":-18.063157894736861},{"$f":"D25","v":19.544537067467985},{"$f":"E25","v":64.937191780119065},{"$f":"F25","v":84.045414519947585},{"$f":"G25","v":106.10397349684685}]
["Add back depreciation",null,{"$f":"-C22","v":81.552631578947384},{"$f":"-D22","v":95.634224376731311},{"$f":"-E22","v":111.80277167954515},{"$f":"-F22","v":130.34446788265518},{"$f":"-G22","v":151.58294975425048}]
["Subtract increase in current assets",null,{"$f":"-(C35-B35)","v":-16.5},{"$f":"-(D35-C35)","v":-18.315000000000026},{"$f":"-(E35-D35)","v":-20.329650000000015},{"$f":"-(F35-E35)","v":-22.565911500000027},{"$f":"-(G35-F35)","v":-25.048161765000032}]
["Add back increase in current liabilities",null,{"$f":"C42-B42","v":8.7999999999999972},{"$f":"D42-C42","v":9.7680000000000149},{"$f":"E42-D42","v":10.842480000000009},{"$f":"F42-E42","v":12.03515280000002},{"$f":"G42-F42","v":13.359019608000025}]
["Subtract increase in fixed assets at cost",null,{"$f":"-(C37-B37)","v":-131.0526315789474},{"$f":"-(D37-C37)","v":-150.5792243767313},{"$f":"-(E37-D37)","v":-172.79172167954539},{"$f":"-(F37-E37)","v":-198.0422023826552},{"$f":"-(G37-F37)","v":-226.72743504925074}]
["Add back after-tax interest on debt",null,{"$f":"-(1-$C$31)*C20","v":-3},{"$f":"-(1-$C$31)*D20","v":-9},-15,-21,-27,{"$f":"getformula(G55)","v":"<-- =-(1-$C$31)*G20"}]
["Subtract after-tax interest on cash and mkt. securities",null,{"$f":"-(1-$C$31)*C21","v":-0.98947368421052495},{"$f":"-(1-$C$31)*D21","v":8.2662385558007472},13.693986540335882,16.099145115532153,16.853264212671665,{"$f":"getformula(G56)","v":"<-- =-(1-$C$31)*G21"}]
["Free cash flow",null,{"$f":"SUM(C50:C56)","v":-79.252631578947401},{"$f":"SUM(D50:D56)","v":-44.681224376731272},{"$f":"SUM(E50:E56)","v":-6.8449416795453182},{"$f":"SUM(F50:F56)","v":0.91606643547972055},{"$f":"SUM(G50:G56)","v":9.1236102575182407}]