{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Objectives of Exercise","Basic Exercise Formulas","Basic Exercise Completed","Minimum Cash","Sheet2","Sheet3"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Objectives of Exercise","index":0,"n_rows":45,"n_cols":14,"rows_kept":45,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Demonstrate Fundamental Principles of Corporate Model",null,null,null,null,null,null,null,null,"Subject","Excel Exercise File","Video","Page Reference"]
[null,null,"Use of historic balance sheet",null,null,null,null,null,null,null,"Overview of Corporate Model Issues","Building Basic Corporate Model"]
[null,null,"Cash balance and debt balance schedules before the financial statements",null,null,null,null,null,null,null,"Exercise Part 1: Depreciation, Debt and P&L","Building Basic Corporate Model"]
[null,null,"Cash flow waterfall in cash flow statement",null,null,null,null,null,null,null,"Exercise Part 2: Cash Flow and Balance Sheet","Building Basic Corporate Model"]
[null,null,"Structuring of model with modules",null,null,null,null,null,null,null,"WC and Fixed Debt Exercise Overview","WC Corporate Exercise"]
[null,null,"Balance Sheet at end of model as output",null,null,null,null,null,null,null,"WC and Fixed Debt Exercise Exercise","WC Corporate Exercise"]
[null,null,"Introduction to corporate model issues",null,null,null,null,null,null,null,"Model with History - Overview","Corporate Model with History"]
[null,null,null,null,null,null,null,null,null,null,"Model with History - Switches","Corporate Model with History"]
[null,"Depreciation and Cash Flow",null,null,null,null,null,null,null,null,"Model with History - Assumptions","Corporate Model with History"]
[null,null,"Calculation of EBITDA and Capital Expenditures, Given",null,null,null,null,null,null,null,"Model with History - Working","Corporate Model with History"]
[null,null,"Introduction to Alternative Depreciation Methods, Use Net Plant",null,null,null,null,null,null,null,"Model with History - Debt and Depreciation","Corporate Model with History"]
[null,null,"Connection of Cash flow and Debt Schedule",null,null,null,null,null,null,null,"Model with History - P&L and Balance Sheet","Corporate Model with History"]
[null,null,"Use of Opening Balance to avoid circularity",null,null,null,null,null,null,null,"Model with Industry Capacity","Model with Industry Capacity"]
[null,null,"Listing of Assumptions in the Debt Schedule and the Depreciation Schedule",null,null,null,null,null,null,null,"Model with Working Analysis Overview","Model with Working Analysis"]
[null,null,"Filling in numbers on profit and loss",null,null,null,null,null,null,null,"Model with Working Analysis","Model with Working Analysis"]
[null,null,null,null,null,null,null,null,null,null,"WC and Debt Exercise","Model with WC and Debt"]
[null,"Cash Flow and Balance Sheet",null,null,null,null,null,null,null,null,"Cash Flow Plug Exercise","Cash Flow Plug"]
[null,null,"Subtotals in cash flow",null,null,null,null,null,null,null,"Minimum Cash Balance","Minimum Cash Balance"]
[null,null,"Use of MAX for capping at zero"]
[null,null,"Use of MIN for testing balance"]
[null,null,"Use of MIN and MAX"]
[null,null,"Finding closing balances for balance sheet"]
[null,null,"Computing Equity balance"]
[null,null,"Testing Balance sheet"]
[]
[null,"Cash Flow Plug Exercise"]
[null,null,"Demonnstrates connection of cash flow and balance sheet"]
[null,null,"Assumes that only cash balance or debt balance"]
[null,null,"Simple balance sheet"]
[]
[null,"Model with History"]
[]
[null,"Model with Industry Capacity Analysis"]
[]
[null,"WC and Debt Exercise"]
[null,null,"Another Corporate Expercise"]
[null,null,"Indude Different Debt Issues in the Financing Analysis"]
[null,null,"Minimum Cash Balance"]
[null,null,"Working Capital"]
[null,null,"Includes cash flow and balance sheet"]
[null,null,"Includes structuring"]
[null,null,"Minority Interest"]
[]
[null,"Model with Working Analysis"]

{"sheet":"Basic Exercise Formulas","index":1,"n_rows":110,"n_cols":13,"rows_kept":110,"cut":false,"hidden":false,"merged":[]}
[]
["Historic Financial Statements"]
[]
[null,"Historic Balance Sheet"]
[null,null,"Assets"]
[null,null,null,"Cash",null,20]
[null,null,null,"Gross Plant",null,600]
[null,null,null,"Accumulated Deprecation ",null,200,null,null,"Shift CNTL R to Copy to Right"]
[null,null,null,"Net Plant",null,{"$f":"F7-F8","v":400}]
[null,null,null,"Total Assets",null,{"$f":"F9+F6","v":420},null,null,"Shift CNTL S to colour inputs"]
[]
[null,null,"Liabilites and Captial"]
[null,null,null,"Debt",null,200]
[null,null,null,"Equity",null,{"$f":"F10-F13","v":220}]
[null,null,null,"Total Liabilities and Capital",null,{"$f":"SUM(F13:F14)","v":420}]
[]
["Assumptions"]
[null,"Operating"]
[null,null,"EBITDA",null,null,null,70,75,80,90,100,110,160]
[null,null,"Capital Expenditures",null,null,null,20,45,70,95,120,145,170]
[]
[null,"Tax and Depreciation"]
[null,null,"Net Plant Depreciation Rate",null,null,null,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,"Tax Rate",null,null,null,0.3,0.3,0.3,0.3,0.3,0.3,0.3]
[]
[null,"Financing"]
[null,null,"Interest Rate on Cash Balance",null,null,null,0.02,0.02,0.02,0.02,0.02,0.02,0.02]
[null,null,"Interest Rate on Debt Balance",null,null,null,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2]
[null,null,"Dividend Payout Ratio",null,null,null,0.6,0.6,0.6,0.6,0.6,0.6,0.6]
[]
["Depreciation Balance"]
[null,"Opening Balance of Net Plant"]
[null,"Add: Capital Expenditures"]
[null,"Less: Depreciation from below"]
[null,"Closing Balance"]
[]
[null,"Depreciation Rate"]
[null,"Depreciation Expense on Opening Balance"]
[]
["Debt and Cash Balance"]
[null,"Debt Balance"]
[null,null,"Opening Balance"]
[null,null,"Add: New Debt Issued from cash flow"]
[null,null,"Less: Debt Repaid"]
[null,null,"Closing Balance"]
[]
[null,null,"Interest Rate"]
[null,null,"Interest Expense on Opening Balance"]
[]
[null,"Cash Balance"]
[null,null,"Opening Balance"]
[null,null,"Add: New Cash Generated"]
[null,null,"Less:  Reduced Cash Balance from Positive Cash Flow"]
[null,null,"Closing Balance"]
[]
[null,null,"Interest Rate"]
[null,null,"Interest Income"]
[]
[null,null,"Min Cash Requirement"]
[null,null,"Min Cash for Funding"]
[]
["Profit and Loss Statement"]
[null,"EBITDA"]
[null,"Less: Depreciation"]
[null,"EBIT "]
[null,"Less: Interest Expense"]
[null,"Add: Interest Income"]
[null,"Earnings"]
[]
["Cash Flow Statement"]
[null,"EBITDA"]
[null,"Less: Captial Expenditures"]
[null,"Cash From Operations"]
[null,"Less: Interest Expense"]
[null,"Add: Interest Income"]
[null,"Less: Dividends"]
[null,"Net Cash Flow"]
[null,"Add: Reduced Cash Balance when Cash is Negative"]
[null,"Less: Reduced Debt Balance when Cash is Postitive"]
[null,"Cash Flow after Reductions"]
[null,"Less: Added Cash Balance when Cash is Postive"]
[null,"Add: Added Debt Balance when Cash is Negative"]
[null,"Final Cash Flow (Should be zero)"]
[]
[null,"Test"]
[]
["Balance Sheet"]
[null,"Equity Balance"]
[null,null,"Opening Balance"]
[null,null,"Add: Earnings"]
[null,null,"Less: Dividends"]
[null,null,"Closing Balance"]
[]
[null,"Assets"]
[null,null,"Cash"]
[null,null,"Net Plant"]
[null,null,"Total Assets"]
[]
[null,"Liabilites and Captial"]
[null,null,"Debt"]
[null,null,"Equity"]
[null,null,"Total Liabilities and Capital"]
[]
[null,null,"Difference"]
[null,null,"Test"]
[]
[null,null,null,"ROIC"]
[null,null,null,"ROE"]
[null,null,null,"Debt/Capital"]
[null,null,null,"Target"]

{"sheet":"Basic Exercise Completed","index":2,"n_rows":108,"n_cols":13,"rows_kept":108,"cut":false,"hidden":false,"merged":[]}
[]
["Assumptions"]
[]
[null,"Historic Balance Sheet"]
[null,null,"Assets"]
[null,null,null,"Cash",null,20]
[null,null,null,"Gross Plant",null,600]
[null,null,null,"Accumulated Deprecation ",null,200]
[null,null,null,"Net Plant",null,{"$f":"F7-F8","v":400}]
[null,null,null,"Total Assets",null,{"$f":"F9+F6","v":420},null,null,"Shift CNTL S to colour inputs"]
[]
[null,null,"Liabilites and Captial"]
[null,null,null,"Debt",null,200]
[null,null,null,"Equity",null,{"$f":"F10-F13","v":220}]
[null,null,null,"Total Liabilities and Capital",null,{"$f":"SUM(F13:F14)","v":420}]
[]
[null,"Operating"]
[null,null,"EBITDA",null,null,null,100,130,160,190,220,250,280]
[null,null,"Capital Expenditures",null,null,null,20,45,70,95,120,145,170]
[]
[null,"Tax and Depreciation"]
[null,null,"Depreciation Rate",null,null,null,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,"Tax Rate",null,null,null,0.3,0.3,0.3,0.3,0.3,0.3,0.3]
[]
[null,"Financing"]
[null,null,"Interest Rate on Cash Balance",null,null,null,0.02,0.02,0.02,0.02,0.02,0.02,0.02]
[null,null,"Interest Rate on Debt Balance",null,null,null,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2]
[null,null,"Dividend Payout Ratio",null,null,null,0.6,0.6,0.6,0.6,0.6,0.6,0.6]
[]
["Depreciation Balance"]
[null,"Opening Balance",null,null,null,null,{"$f":"F34","v":600},{"$f":"G34","v":620},665,735,830,950,1095]
[null,"Add: Capital Expenditures",null,null,null,null,{"$f":"G19","v":20},{"$f":"H19","v":45},70,95,120,145,170]
[null,"Less: Retirements"]
[null,"Closing Balance",null,null,null,{"$f":"F7","v":600},{"$f":"G31+G32-G33","v":620},{"$f":"H31+H32-H33","v":665},735,830,950,1095,1265]
[]
[null,"Depreciation Rate",null,null,null,null,{"$f":"G22","v":0.05},{"$f":"H22","v":0.05},0.05,0.05,0.05,0.05,0.05]
[null,"Depreciation Expense",null,null,null,null,{"$f":"AVERAGE(G34,G31)*G36","v":30.5},{"$f":"AVERAGE(H34,H31)*H36","v":32.125},35,39.125,44.5,51.125,59]
[]
[null,"Accumulated Deprecation",null,null,null,{"$f":"F8","v":200},{"$f":"F39+G37","v":230.5},{"$f":"G39+H37","v":262.625},297.625,336.75,381.25,432.375,491.375]
[]
[null,"Net Plant",null,null,null,{"$f":"F34-F39","v":400},{"$f":"G34-G39","v":389.5},402.375,437.375,493.25,568.75,662.625,773.625]
[]
[]
["Debt and Cash Balance"]
[null,"Debt Balance"]
[null,null,"Opening Balance",null,null,null,{"$f":"F49","v":200},{"$f":"G49","v":167.14},145.38491999999997,134.29569775999994,133.42097729727993,133.42097729727993,140.26855832484222]
[null,null,"Add: New Debt Issued",null,null,null,{"$f":"G83","v":0},{"$f":"H83","v":0},0,0,0,6.8475810275622706,26.527519633095579]
[null,null,"Less: Debt Repaid",null,null,null,{"$f":"G80","v":32.860000000000007},{"$f":"H80","v":21.755080000000007},11.089222240000012,0.87472046272000625,0,0,0]
[null,null,"Closing Balance",null,null,{"$f":"F13","v":200},{"$f":"G46+G47-G48","v":167.14},{"$f":"H46+H47-H48","v":145.38491999999997},134.29569775999994,133.42097729727993,133.42097729727993,140.26855832484222,166.7960779579378]
[]
[null,null,"Interest Rate",null,null,null,{"$f":"G27","v":7.0000000000000007E-2},{"$f":"H27","v":7.0000000000000007E-2},7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2]
[null,null,"Interest Expense",null,null,null,{"$f":"G51*G46","v":14.000000000000002},{"$f":"H51*H46","v":11.6998},10.176944399999998,9.4006988431999972,9.3394684108095962,9.3394684108095962,9.8187990827389555]
[]
[null,"Cash Balance"]
[null,null,"Opening Balance",null,null,null,{"$f":"F58","v":20},{"$f":"G58","v":20},20,20,20,11.124212635676159,0]
[null,null,"Add: New Cash Generated",null,null,null,{"$f":"G82","v":0},{"$f":"H82","v":0},0,0,0,0,0]
[null,null,"Less:  Cash Used",null,null,null,{"$f":"G79","v":0},{"$f":"H79","v":0},0,0,8.8757873643238412,11.124212635676159,0]
[null,null,"Closing Balance",null,null,{"$f":"F6","v":20},{"$f":"G55+G56-G57","v":20},{"$f":"H55+H56-H57","v":20},20,20,11.124212635676159,0,0]
[]
[null,null,"Interest Rate",null,null,null,{"$f":"G26","v":0.02},{"$f":"H26","v":0.02},0.02,0.02,0.02,0.02,0.02]
[null,null,"Interest Income",null,null,null,{"$f":"G60*G55","v":0.4},{"$f":"H60*H55","v":0.4},0.4,0.4,0.4,0.22248425271352318,0]
[]
["Profit and Loss Statement"]
[null,"EBITDA",null,null,null,null,{"$f":"G18","v":100},{"$f":"H18","v":130},160,190,220,250,280]
[null,"Less: Depreciation",null,null,null,null,{"$f":"G37","v":30.5},{"$f":"H37","v":32.125},35,39.125,44.5,51.125,59]
[null,"EBIT ",null,null,null,null,{"$f":"G64-G65","v":69.5},{"$f":"H64-H65","v":97.875},125,150.875,175.5,198.875,221]
[null,"Less: Interest Expense",null,null,null,null,{"$f":"G52","v":14.000000000000002},{"$f":"H52","v":11.6998},10.176944399999998,9.4006988431999972,9.3394684108095962,9.3394684108095962,9.8187990827389555]
[null,"Add: Interest Income",null,null,null,null,{"$f":"G61","v":0.4},{"$f":"H61","v":0.4},0.4,0.4,0.4,0.22248425271352318,0]
[null,"Earnings",null,null,null,null,{"$f":"G66-G67+G68","v":55.9},{"$f":"H66-H67+H68","v":86.575200000000009},115.22305560000001,141.87430115680002,166.56053158919042,189.75801584190393,211.18120091726104]
[]
["Cash Flow Statement"]
[null,"EBITDA",null,null,null,null,{"$f":"G64","v":100},{"$f":"H64","v":130},160,190,220,250,280]
[null,"Less: Captial Expenditures",null,null,null,null,{"$f":"G19","v":20},{"$f":"H19","v":45},70,95,120,145,170]
[null,"Cash From Operations",null,null,null,null,{"$f":"G72-G73","v":80},{"$f":"H72-H73","v":85},90,95,100,105,110]
[null,"Less: Interest Expense",null,null,null,null,{"$f":"G67","v":14.000000000000002},{"$f":"H67","v":11.6998},10.176944399999998,9.4006988431999972,9.3394684108095962,9.3394684108095962,9.8187990827389555]
[null,"Add: Interest Income",null,null,null,null,{"$f":"G68","v":0.4},{"$f":"H68","v":0.4},0.4,0.4,0.4,0.22248425271352318,0]
[null,"Less: Dividends",null,null,null,null,{"$f":"MAX(G69*G28,0)","v":33.54},{"$f":"MAX(H69*H28,0)","v":51.945120000000003},69.133833359999997,85.124580694080009,99.936318953514245,113.85480950514236,126.70872055035662]
[null,"Net Cash Flow",null,null,null,null,{"$f":"G74-G75+G76-G77","v":32.860000000000007},{"$f":"H74-H75+H76-H77","v":21.755080000000007},11.089222240000012,0.87472046272000625,-8.8757873643238412,-17.971793663238429,-26.527519633095579]
[null,"Add: Reduced Cash Balance when Cash is Negative",null,null,null,null,{"$f":"MIN(MAX(-G78,0),G55)","v":0},{"$f":"MIN(MAX(-H78,0),H55)","v":0},0,0,8.8757873643238412,11.124212635676159,0]
[null,"Less: Reduced Debt Balance when Cash is Postitive",null,null,null,null,{"$f":"MIN(MAX(G78,0),G46)","v":32.860000000000007},{"$f":"MIN(MAX(H78,0),H46)","v":21.755080000000007},11.089222240000012,0.87472046272000625,0,0,0]
[null,"Cash Flow after Reductions",null,null,null,null,{"$f":"G78+G79-G80","v":0},{"$f":"H78+H79-H80","v":0},0,0,0,-6.8475810275622706,-26.527519633095579]
[null,"Less: Added Cash Balance when Cash is Postive",null,null,null,null,{"$f":"MAX(G81,0)","v":0},{"$f":"MAX(H81,0)","v":0},0,0,0,0,0]
[null,"Add: Added Debt Balance when Cash is Negative",null,null,null,null,{"$f":"MAX(-G81,0)","v":0},{"$f":"MAX(-H81,0)","v":0},0,0,0,6.8475810275622706,26.527519633095579]
[null,"Final Cash Flow (Should be zero)",null,null,null,null,{"$f":"G81-G82+G83","v":0},{"$f":"H81-H82+H83","v":0},0,0,0,0,0]
[]
[null,"Test",null,null,{"$f":"AND(G86:M86)","v":true},null,{"$f":"G84=0","v":true},{"$f":"H84=0","v":true},true,true,true,true,true]
[]
["Balance Sheet"]
[null,"Equity Balance"]
[null,null,"Opening Balance",null,null,null,{"$f":"F93","v":220},{"$f":"G93","v":242.35999999999999},276.99008000000003,323.07930224000006,379.82902270272007,446.45323533839621,522.35644167515773]
[null,null,"Add: Earnings",null,null,null,{"$f":"G69","v":55.9},{"$f":"H69","v":86.575200000000009},115.22305560000001,141.87430115680002,166.56053158919042,189.75801584190393,211.18120091726104]
[null,null,"Less: Dividends",null,null,null,{"$f":"G77","v":33.54},{"$f":"H77","v":51.945120000000003},69.133833359999997,85.124580694080009,99.936318953514245,113.85480950514236,126.70872055035662]
[null,null,"Closing Balance",null,null,{"$f":"F14","v":220},{"$f":"G90+G91-G92","v":242.35999999999999},{"$f":"H90+H91-H92","v":276.99008000000003},323.07930224000006,379.82902270272007,446.45323533839621,522.35644167515773,606.82892204206212]
[]
[null,"Assets"]
[null,null,"Cash",null,null,{"$f":"F58","v":20},{"$f":"G58","v":20},20,20,20,11.124212635676159,0,0]
[null,null,"Gross Plant",null,null,{"$f":"F34","v":600},{"$f":"G34","v":620},665,735,830,950,1095,1265]
[null,null,"Accumulated Deprecation ",null,null,{"$f":"F39","v":200},{"$f":"G39","v":230.5},262.625,297.625,336.75,381.25,432.375,491.375]
[null,null,"Net Plant",null,null,{"$f":"F97-F98","v":400},{"$f":"G97-G98","v":389.5},402.375,437.375,493.25,568.75,662.625,773.625]
[null,null,"Total Assets",null,null,{"$f":"F96+F99","v":420},{"$f":"G96+G99","v":409.5},422.375,457.375,513.25,579.87421263567614,662.625,773.625]
[]
[null,"Liabilites and Captial"]
[null,null,"Debt",null,null,{"$f":"F49","v":200},{"$f":"G49","v":167.14},145.38491999999997,134.29569775999994,133.42097729727993,133.42097729727993,140.26855832484222,166.7960779579378]
[null,null,"Equity",null,null,{"$f":"F93","v":220},{"$f":"G93","v":242.35999999999999},276.99008000000003,323.07930224000006,379.82902270272007,446.45323533839621,522.35644167515773,606.82892204206212]
[null,null,"Total Liabilities and Capital",null,null,{"$f":"F103+F104","v":420},{"$f":"G103+G104","v":409.5},422.375,457.375,513.25,579.87421263567614,662.625,773.62499999999989]
[]
[null,null,"Difference",null,null,{"$f":"F105-F100","v":0},{"$f":"G105-G100","v":0},0,0,0,0,0,0]
[null,null,"Test",null,{"$f":"AND(F108:M108)","v":true},{"$f":"F107=0","v":true},{"$f":"G107=0","v":true},true,true,true,true,true,true]

{"sheet":"Minimum Cash","index":3,"n_rows":114,"n_cols":13,"rows_kept":114,"cut":false,"hidden":false,"merged":[]}
[]
["Assumptions"]
[]
[null,"Historic Balance Sheet"]
[null,null,"Assets",null,null,"History"]
[null,null,null,"Cash",null,20]
[null,null,null,"Gross Plant",null,600]
[null,null,null,"Accumulated Deprecation ",null,200]
[null,null,null,"Net Plant",null,{"$f":"F7-F8","v":400}]
[null,null,null,"Total Assets",null,{"$f":"F9+F6","v":420},null,null,"Shift CNTL S to colour inputs"]
[]
[null,null,"Liabilites and Captial"]
[null,null,null,"Debt",null,200]
[null,null,null,"Equity",null,{"$f":"F10-F13","v":220}]
[null,null,null,"Total Liabilities and Capital",null,{"$f":"SUM(F13:F14)","v":420}]
[]
[null,"Operating"]
[null,null,"EBITDA",null,null,null,100,130,160,190,220,250,280]
[null,null,"Capital Expenditures",null,null,null,20,45,70,95,120,145,170]
[]
[null,"Tax and Depreciation"]
[null,null,"Depreciation Rate",null,null,null,0.05,0.05,0.05,0.05,0.05,0.05,0.05]
[null,null,"Tax Rate",null,null,null,0.3,0.3,0.3,0.3,0.3,0.3,0.3]
[]
[null,"Financing"]
[null,null,"Interest Rate on Cash Balance",null,null,null,0.02,0.02,0.02,0.02,0.02,0.02,0.02]
[null,null,"Interest Rate on Debt Balance",null,null,null,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2]
[null,null,"Dividend Payout Ratio",null,null,null,0.6,0.6,0.6,0.6,0.6,0.6,0.6]
[null,null,"Minimum Cash Balance",null,null,null,{"$f":"G18*5%","v":5},{"$f":"H18*5%","v":6.5},8,9.5,11,12.5,14]
[]
["Depreciation Balance"]
[null,"Opening Balance",null,null,null,null,{"$f":"F35","v":600},{"$f":"G35","v":620},665,735,830,950,1095]
[null,"Add: Capital Expenditures",null,null,null,null,{"$f":"G19","v":20},{"$f":"H19","v":45},70,95,120,145,170]
[null,"Less: Retirements"]
[null,"Closing Balance",null,null,null,{"$f":"F7","v":600},{"$f":"G32+G33-G34","v":620},{"$f":"H32+H33-H34","v":665},735,830,950,1095,1265]
[]
[null,"Depreciation Rate",null,null,null,null,{"$f":"G22","v":0.05},{"$f":"H22","v":0.05},0.05,0.05,0.05,0.05,0.05]
[null,"Depreciation Expense",null,null,null,null,{"$f":"AVERAGE(G35,G32)*G37","v":30.5},{"$f":"AVERAGE(H35,H32)*H37","v":32.125},35,39.125,44.5,51.125,59]
[]
[null,"Accumulated Deprecation",null,null,null,{"$f":"F8","v":200},{"$f":"F40+G38","v":230.5},{"$f":"G40+H38","v":262.625},297.625,336.75,381.25,432.375,491.375]
[]
[null,"Net Plant",null,null,null,{"$f":"F35-F40","v":400},{"$f":"G35-G40","v":389.5},402.375,437.375,493.25,568.75,662.625,773.625]
[]
[]
["Debt and Cash Balance"]
[null,"Debt Balance"]
[null,null,"Opening Balance",null,null,null,{"$f":"F50","v":200},{"$f":"G50","v":167.14},145.38491999999997,134.29569775999994,133.42097729727993,133.42097729727993,152.76855832484222]
[null,null,"Add: New Debt Issued",null,null,null,{"$f":"G89","v":0},{"$f":"H89","v":0},0,0,0,19.347581027562271,28.277519633095579]
[null,null,"Less: Debt Repaid",null,null,null,{"$f":"G86","v":32.860000000000007},{"$f":"H86","v":21.755080000000007},11.089222240000012,0.87472046272000625,0,0,0]
[null,null,"Closing Balance",null,null,{"$f":"F13","v":200},{"$f":"G47+G48-G49","v":167.14},{"$f":"H47+H48-H49","v":145.38491999999997},134.29569775999994,133.42097729727993,133.42097729727993,152.76855832484222,181.0460779579378]
[]
[null,null,"Interest Rate",null,null,null,{"$f":"G27","v":7.0000000000000007E-2},{"$f":"H27","v":7.0000000000000007E-2},7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2,7.0000000000000007E-2]
[null,null,"Interest Expense",null,null,null,{"$f":"G52*G47","v":14.000000000000002},{"$f":"H52*H47","v":11.6998},10.176944399999998,9.4006988431999972,9.3394684108095962,9.3394684108095962,10.693799082738956]
[]
[null,"Cash Balance"]
[null,null,"Opening Balance",null,null,null,{"$f":"F59","v":20},{"$f":"G59","v":20},20,20,20,11.124212635676159,12.5]
[null,null,"Add: New Cash Generated",null,null,null,{"$f":"G88+G83","v":0},{"$f":"H88+H83","v":0},0,0,0,1.3757873643238412,1.5]
[null,null,"Less:  Cash Used",null,null,null,{"$f":"G85","v":0},{"$f":"H85","v":0},0,0,8.8757873643238412,0,0]
[null,null,"Closing Balance",null,null,{"$f":"F6","v":20},{"$f":"G56+G57-G58","v":20},{"$f":"H56+H57-H58","v":20},20,20,11.124212635676159,12.5,14]
[]
[null,null,"Minimum Cash Balance",null,null,null,{"$f":"G29","v":5},{"$f":"H29","v":6.5},8,9.5,11,12.5,14]
[null,null,"Surplus Cash Aviable for Negative Cash Flow",null,null,null,{"$f":"MAX(G56-G61,0)","v":15},{"$f":"MAX(H56-H61,0)","v":13.5},12,10.5,9,0,0]
[null,null,"Required Additional Cash",null,null,null,{"$f":"MAX(G61-G56,0)","v":0},{"$f":"MAX(H61-H56,0)","v":0},0,0,0,1.3757873643238412,1.5]
[]
[null,null,"Interest Rate",null,null,null,{"$f":"G26","v":0.02},{"$f":"H26","v":0.02},0.02,0.02,0.02,0.02,0.02]
[null,null,"Interest Income",null,null,null,{"$f":"G65*G56","v":0.4},{"$f":"H65*H56","v":0.4},0.4,0.4,0.4,0.22248425271352318,0.25]
[]
["Profit and Loss Statement"]
[null,"EBITDA",null,null,null,null,{"$f":"G18","v":100},{"$f":"H18","v":130},160,190,220,250,280]
[null,"Less: Depreciation",null,null,null,null,{"$f":"G38","v":30.5},{"$f":"H38","v":32.125},35,39.125,44.5,51.125,59]
[null,"EBIT ",null,null,null,null,{"$f":"G69-G70","v":69.5},{"$f":"H69-H70","v":97.875},125,150.875,175.5,198.875,221]
[null,"Less: Interest Expense",null,null,null,null,{"$f":"G53","v":14.000000000000002},{"$f":"H53","v":11.6998},10.176944399999998,9.4006988431999972,9.3394684108095962,9.3394684108095962,10.693799082738956]
[null,"Add: Interest Income",null,null,null,null,{"$f":"G66","v":0.4},{"$f":"H66","v":0.4},0.4,0.4,0.4,0.22248425271352318,0.25]
[null,"Earnings",null,null,null,null,{"$f":"G71-G72+G73","v":55.9},{"$f":"H71-H72+H73","v":86.575200000000009},115.22305560000001,141.87430115680002,166.56053158919042,189.75801584190393,210.55620091726104]
[]
["Cash Flow Statement"]
[null,"EBITDA",null,null,null,null,{"$f":"G69","v":100},{"$f":"H69","v":130},160,190,220,250,280]
[null,"Less: Captial Expenditures",null,null,null,null,{"$f":"G19","v":20},{"$f":"H19","v":45},70,95,120,145,170]
[null,"Cash From Operations",null,null,null,null,{"$f":"G77-G78","v":80},{"$f":"H77-H78","v":85},90,95,100,105,110]
[null,"Less: Interest Expense",null,null,null,null,{"$f":"G72","v":14.000000000000002},{"$f":"H72","v":11.6998},10.176944399999998,9.4006988431999972,9.3394684108095962,9.3394684108095962,10.693799082738956]
[null,"Add: Interest Income",null,null,null,null,{"$f":"G73","v":0.4},0.4,0.4,0.4,0.4,0.22248425271352318,0.25]
[null,"Less: Dividends",null,null,null,null,{"$f":"MAX(G74*G28,0)","v":33.54},{"$f":"MAX(H74*H28,0)","v":51.945120000000003},69.133833359999997,85.124580694080009,99.936318953514245,113.85480950514236,126.33372055035662]
[null,"Less: Added Cash to Fund Minimum",null,null,null,null,{"$f":"G63","v":0},{"$f":"H63","v":0},0,0,0,1.3757873643238412,1.5]
[null,"Net Cash Flow",null,null,null,null,{"$f":"G79-G80+G81-G82-G83","v":32.860000000000007},{"$f":"H79-H80+H81-H82-H83","v":21.755080000000007},11.089222240000012,0.87472046272000625,-8.8757873643238412,-19.347581027562271,-28.277519633095579]
[null,"Add: Reduced Cash Balance when Cash is Negative",null,null,null,null,{"$f":"MIN(MAX(-G84,0),G62)","v":0},{"$f":"MIN(MAX(-H84,0),H62)","v":0},0,0,8.8757873643238412,0,0]
[null,"Less: Reduced Debt Balance when Cash is Postitive",null,null,null,null,{"$f":"MIN(MAX(G84,0),G47)","v":32.860000000000007},{"$f":"MIN(MAX(H84,0),H47)","v":21.755080000000007},11.089222240000012,0.87472046272000625,0,0,0]
[null,"Cash Flow after Reductions",null,null,null,null,{"$f":"G84+G85-G86","v":0},{"$f":"H84+H85-H86","v":0},0,0,0,-19.347581027562271,-28.277519633095579]
[null,"Less: Added Cash Balance when Cash is Postive",null,null,null,null,{"$f":"MAX(G87,0)","v":0},{"$f":"MAX(H87,0)","v":0},0,0,0,0,0]
[null,"Add: Added Debt Balance when Cash is Negative",null,null,null,null,{"$f":"MAX(-G87,0)","v":0},{"$f":"MAX(-H87,0)","v":0},0,0,0,19.347581027562271,28.277519633095579]
[null,"Final Cash Flow (Should be zero)",null,null,null,null,{"$f":"G87-G88+G89","v":0},{"$f":"H87-H88+H89","v":0},0,0,0,0,0]
[]
[null,"Test",null,null,{"$f":"AND(G92:M92)","v":true},null,{"$f":"G90=0","v":true},{"$f":"H90=0","v":true},true,true,true,true,true]
[]
["Balance Sheet"]
[null,"Equity Balance"]
[null,null,"Opening Balance",null,null,null,{"$f":"F99","v":220},{"$f":"G99","v":242.35999999999999},276.99008000000003,323.07930224000006,379.82902270272007,446.45323533839621,522.35644167515773]
[null,null,"Add: Earnings",null,null,null,{"$f":"G74","v":55.9},{"$f":"H74","v":86.575200000000009},115.22305560000001,141.87430115680002,166.56053158919042,189.75801584190393,210.55620091726104]
[null,null,"Less: Dividends",null,null,null,{"$f":"G82","v":33.54},{"$f":"H82","v":51.945120000000003},69.133833359999997,85.124580694080009,99.936318953514245,113.85480950514236,126.33372055035662]
[null,null,"Closing Balance",null,null,{"$f":"F14","v":220},{"$f":"G96+G97-G98","v":242.35999999999999},{"$f":"H96+H97-H98","v":276.99008000000003},323.07930224000006,379.82902270272007,446.45323533839621,522.35644167515773,606.57892204206212]
[]
[null,"Assets"]
[null,null,"Cash",null,null,{"$f":"F59","v":20},{"$f":"G59","v":20},20,20,20,11.124212635676159,12.5,14]
[null,null,"Gross Plant",null,null,{"$f":"F35","v":600},{"$f":"G35","v":620},665,735,830,950,1095,1265]
[null,null,"Accumulated Deprecation ",null,null,{"$f":"F40","v":200},{"$f":"G40","v":230.5},262.625,297.625,336.75,381.25,432.375,491.375]
[null,null,"Net Plant",null,null,{"$f":"F103-F104","v":400},{"$f":"G103-G104","v":389.5},402.375,437.375,493.25,568.75,662.625,773.625]
[null,null,"Total Assets",null,null,{"$f":"F102+F105","v":420},{"$f":"G102+G105","v":409.5},422.375,457.375,513.25,579.87421263567614,675.125,787.625]
[]
[null,"Liabilites and Captial"]
[null,null,"Debt",null,null,{"$f":"F50","v":200},{"$f":"G50","v":167.14},145.38491999999997,134.29569775999994,133.42097729727993,133.42097729727993,152.76855832484222,181.0460779579378]
[null,null,"Equity",null,null,{"$f":"F99","v":220},{"$f":"G99","v":242.35999999999999},276.99008000000003,323.07930224000006,379.82902270272007,446.45323533839621,522.35644167515773,606.57892204206212]
[null,null,"Total Liabilities and Capital",null,null,{"$f":"F109+F110","v":420},{"$f":"G109+G110","v":409.5},422.375,457.375,513.25,579.87421263567614,675.125,787.62499999999989]
[]
[null,null,"Difference",null,null,{"$f":"F111-F106","v":0},{"$f":"G111-G106","v":0},0,0,0,0,0,0]
[null,null,"Test",null,{"$f":"AND(F114:M114)","v":true},{"$f":"F113=0","v":true},{"$f":"G113=0","v":true},true,true,true,true,true,true]

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

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