{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Pro_forma_1"],"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":"Pro_forma_1","index":0,"n_rows":79,"n_cols":9,"rows_kept":79,"cut":false,"hidden":false,"merged":[]}
[]
[null,"HW5 Lemonade Stand"]
[null,"Instructions:","Complete the following financial statements using the assumptions listed below to forecast year 1 through 5. Use cash as the plug to balance the balance sheet. Make sure you go to Excel options (Alt T O) and turn on enable iterative calculations under formulas. You will also need to calculate free cash flow to the firm (unlevered free cash flow)"]
[]
[null,"Sales growth",0.1]
[null,"Current assets/Sales",0.15]
[null,"Current liabilities/Sales",0.08]
[null,"Net fixed assets/Sales",0.77]
[null,"Costs of goods sold/Sales",0.5]
[null,"Depreciation rate",0.1]
[null,"Interest rate on debt",0.1]
[null,"Interest paid on cash and marketable securities",0.08]
[null,"Tax rate",0.4]
[null,"Dividend payout ratio",0.4]
[null,"Circuit Breaker","OFF"]
[]
[null,"Year",0,1,2,3,4,5]
[null,"Income statement"]
[null,"Sales",1000,{"$f":"C19*(1+$C$5)","v":1100},{"$f":"D19*(1+$C$5)","v":1210},1331,1464.1000000000001,1610.5100000000002]
[null,"Costs of goods sold",-500,{"$f":"-D19*$C$9","v":-550},{"$f":"-E19*$C$9","v":-605},-665.5,-732.05000000000007,-805.25500000000011]
[null,"Interest payments on debt",-32,{"$f":"-$C$11*AVERAGE(C40:D40)","v":-32},{"$f":"-$C$11*AVERAGE(D40:E40)","v":-32},-32,-32,-32]
[null,"Interest earned on cash and marketable securities",6.4,{"$f":"$C$12*AVERAGE(C31:D31)","v":8.94779562547712},{"$f":"$C$12*AVERAGE(D31:E31)","v":14.276702864508671},20.080913371332432,26.376731328589972,33.178069179500405]
[null,"Depreciation",-100,{"$f":"-$C$10*AVERAGE(C34:D34)","v":-116.68421052619237},{"$f":"-$C$10*AVERAGE(D34:E34)","v":-137.47728531606973},-161.31015743221519,-188.58791070368926,-219.76677956578416]
[null,"Profit before tax",374.4,{"$f":"SUM(D19:D23)","v":410.2635850992848},{"$f":"SUM(E19:E23)","v":449.79941754843901},492.27075593911729,537.83882062490079,586.66628961371634]
[null,"Taxes",-149.76,{"$f":"-D24*$C$13","v":-164.10543403971394},{"$f":"-E24*$C$13","v":-179.91976701937563},-196.90830237564694,-215.13552824996032,-234.66651584548654]
[null,"Profit after tax",224.64,{"$f":"D25+D24","v":246.15815105957086},{"$f":"E25+E24","v":269.87965052906338},295.36245356347035,322.70329237494047,351.99977376822983]
[null,"Dividends",-89.855999999999995,{"$f":"-D26*$C$14","v":-98.463260423828345},{"$f":"-E26*$C$14","v":-107.95186021162536},-118.14498142538815,-129.08131694997618,-140.79990950729194]
[null,"Retained earnings",134.78399999999999,{"$f":"D26+D27","v":147.69489063574252},{"$f":"E26+E27","v":161.92779031743802},177.2174721380822,193.62197542496429,211.19986426093789]
[]
[null,"Balance sheet"]
[null,"Cash and marketable securities",80,{"$f":"IF($C$15=\"OFF\",D43-D36-D32,0)","v":143.69489063692799},{"$f":"IF($C$15=\"OFF\",E43-E36-E32,0)","v":213.22268097578876},288.80015330752201,370.61812990722729,458.83359958028279,"***Use Cash as the plug, i.e. it is used to make the balance sheet balance"]
[null,"Current assets",150,{"$f":"D19*$C$6","v":165},{"$f":"E19*$C$6","v":181.5},199.65,219.61500000000001,241.57650000000001]
[null,"Fixed assets"]
[null,"     At cost",1070,{"$f":"IF($C$15=\"OFF\",D36-D35,0)","v":1263.6842105238477},{"$f":"IF($C$15=\"OFF\",E36-E35,0)","v":1485.8614957975469},1740.3416528467569,2031.4165612270283,2363.9190300886548]
[null,"     Depreciation",-300,{"$f":"C35+D23","v":-416.68421052619237},{"$f":"D35+E23","v":-554.16149584226207},-715.47165327447726,-904.05956397816658,-1123.8263435439508]
[null,"     Net fixed assets",770,{"$f":"D19*$C$8","v":847},{"$f":"E19*$C$8","v":931.7},1024.8700000000001,1127.3570000000002,1240.0927000000001]
[null,"Total assets",1000,{"$f":"D36+D32+D31","v":1155.694890636928},{"$f":"E36+E32+E31","v":1326.4226809757888},1513.3201533075221,1717.5901299072275,1940.502799580283]
[]
[null,"Current liabilities",80,{"$f":"D19*$C$7","v":88},{"$f":"E19*$C$7","v":96.8},106.48,117.12800000000001,128.84080000000003]
[null,"Debt",320,{"$f":"C40","v":320},{"$f":"D40","v":320},320,320,320]
[null,"Stock",450,{"$f":"C41","v":450},{"$f":"D41","v":450},450,450,450]
[null,"Accumulated retained earnings",150,{"$f":"C42+D28","v":297.69489063574252},{"$f":"D42+E28","v":459.62268095318052},636.84015309126266,830.46212851622693,1041.6619927771649]
[null,"Total liabilities and equity",1000,{"$f":"SUM(D39:D42)","v":1155.6948906357425},{"$f":"SUM(E39:E42)","v":1326.4226809531806},1513.3201530912627,1717.590128516227,1940.5027927771648]
[]
[]
[null,"Year",0,1,2,3,4,5]
[null,"Free cash flow calculation"]
[null,"Profit after tax",null,{"$f":"D26","v":246.15815105957086},{"$f":"E26","v":269.87965052906338},295.36245356347035,322.70329237494047,351.99977376822983]
[null,"Add back depreciation",null,{"$f":"-D23","v":116.68421052619237},{"$f":"-E23","v":137.47728531606973},161.31015743221519,188.58791070368926,219.76677956578416]
[null,"Subtract increase in current assets",null,{"$f":"-(D32-C32)","v":-15},{"$f":"-(E32-D32)","v":-16.5},-18.150000000000006,-19.965000000000003,-21.961500000000001]
[null,"Add back increase in current liabilities",null,{"$f":"(D39-C39)","v":8},{"$f":"(E39-D39)","v":8.7999999999999972},9.6800000000000068,10.64800000000001,11.712800000000016]
[null,"Subtract increase in fixed assets at cost",null,{"$f":"-(D34-C34)","v":-193.6842105238477},{"$f":"-(E34-D34)","v":-222.17728527369923},-254.48015704920999,-291.07490838027138,-332.50246886162654]
[null,"Add back after-tax interest on debt",null,{"$f":"-D21*(1-$C$13)","v":19.2},{"$f":"-E21*(1-$C$13)","v":19.2},19.2,19.2,19.2]
[null,"Subtract after-tax interest on cash and mkt. securities",null,{"$f":"-D22*(1-$C$13)","v":-5.3686773752862722},{"$f":"-E22*(1-$C$13)","v":-8.566021718705203},-12.048548022799459,-15.826038797153982,-19.906841507700243]
[null,"Free cash flow",null,{"$f":"SUM(D48:D54)","v":175.98947368662925},{"$f":"SUM(E48:E54)","v":188.11362885272865},200.87390592367612,214.27325590120441,228.30854296468723]
[null,"Use our EBIT formula",null,{"$f":"(D19+D20+D23)*(1-$C$13)+D49+SUM(D50:D52)","v":175.98947368662925},{"$f":"(E19+E20+E23)*(1-$C$13)+E49+SUM(E50:E52)","v":188.11362885272871},200.87390592367603,214.27325590120427,228.30854296468715]
[null,"CONSOLIDATED STATEMENT OF CASH FLOWS:  RECONCILING THE CASH BALANCES"]
[null,"     Cash flow from operating activities"]
[null,"Profit after tax",null,{"$f":"D26","v":246.15815105957086},{"$f":"E26","v":269.87965052906338},295.36245356347035,322.70329237494047,351.99977376822983]
[null,"Add back depreciation",null,{"$f":"-D23","v":116.68421052619237},{"$f":"-E23","v":137.47728531606973},161.31015743221519,188.58791070368926,219.76677956578416]
[null,"Adjust for changes in net working capital:"]
[null,"     Subtract increase in current assets",null,{"$f":"-(D32-C32)","v":-15},{"$f":"-(E32-D32)","v":-16.5},-18.150000000000006,-19.965000000000003,-21.961500000000001]
[null,"     Add back increase in current liabilities",null,{"$f":"(D39-C39)","v":8},{"$f":"(E39-D39)","v":8.7999999999999972},9.6800000000000068,10.64800000000001,11.712800000000016]
[null,"Net cash from operating activities",null,{"$f":"SUM(D59:D63)","v":355.84236158576323},{"$f":"SUM(E59:E63)","v":399.65693584513309},448.20261099568557,501.97420307862978,561.51785333401403]
[]
[null,"     Cash flow from investing activities"]
[null,"Aquisitions of fixed assets--capital expenditures",null,{"$f":"-(D34-C34)","v":-193.6842105238477},{"$f":"-(E34-D34)","v":-222.17728527369923},-254.48015704920999,-291.07490838027138,-332.50246886162654]
[null,"Purchases of investment securities",null,0,0,0,0,0]
[null,"Proceeds from sales of investment securities",null,0,0,0,0,0]
[null,"Net cash used in investing activities",null,{"$f":"SUM(D67:D69)","v":-193.6842105238477},{"$f":"SUM(E67:E69)","v":-222.17728527369923},-254.48015704920999,-291.07490838027138,-332.50246886162654]
[]
[null,"     Cash flow from financing activities"]
[null,"Net proceeds from borrowing activities",null,{"$f":"D40-C40","v":0},{"$f":"E40-D40","v":0},0,0,0]
[null,"Net proceeds from stock issues, repurchases",null,{"$f":"D41-C41","v":0},{"$f":"E41-D41","v":0},0,0,0]
[null,"Dividends paid",null,{"$f":"D27","v":-98.463260423828345},{"$f":"E27","v":-107.95186021162536},-118.14498142538815,-129.08131694997618,-140.79990950729194]
[null,"Net cash from financing activities",null,{"$f":"SUM(D73:D75)","v":-98.463260423828345},{"$f":"SUM(E73:E75)","v":-107.95186021162536},-118.14498142538815,-129.08131694997618,-140.79990950729194]
[]
[null,"Net increase in cash and cash equivalents",null,{"$f":"D76+D70+D64","v":63.694890638087202},{"$f":"E76+E70+E64","v":69.527790359808535},75.577472521087429,81.817977748382191,88.215474965095552]
[null,"Check:  changes in cash and mkt. securities",null,{"$f":"D31-C31","v":63.694890636927994},{"$f":"E31-D31","v":69.527790338860768},75.577472331733247,81.817976599705275,88.215469673055509]