{"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":78,"n_cols":9,"rows_kept":78,"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.2]
[null,"Current assets/Sales",0.15]
[null,"Current liabilities/Sales",0.08]
[null,"Net fixed assets/Sales",0.8]
[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.5]
[]
[null,"Year",0,1,2,3,4,5]
[null,"Income statement"]
[null,"Sales",1000,{"$f":"C18*(1+$C$5)","v":1200},{"$f":"D18*(1+$C$5)","v":1440},1728,2073.6,2488.3199999999997,{"$f":"getformula(H18)","v":"<--  =G18*(1+$C$5)"}]
[null,"Costs of goods sold",-500,{"$f":"-D18*$C$9","v":-600},{"$f":"-E18*$C$9","v":-720},-864,-1036.8,-1244.1599999999999,"<--  =-H18*$C$9"]
[null,"Interest payments on debt",-40,{"$f":"-$C$11*AVERAGE(C39:D39)","v":-40},{"$f":"-$C$11*AVERAGE(D39:E39)","v":-40},-41.141102791514115,-46.078840668375236,-54.557266993006536,"<--  =-$C$11*AVERAGE(G39:H39)"]
[null,"Interest earned on cash and marketable securities",6.4,{"$f":"$C$12*AVERAGE(C30:D30)","v":6.7504794374600445},{"$f":"$C$12*AVERAGE(D30:E30)","v":5.1004243549916284},1.5499449083396417,1.0609380524329026E-7,1.9011308404515149E-6,"<--  =$C$12*AVERAGE(G30:H30)"]
[null,"Depreciation",-100,{"$f":"-$C$10*AVERAGE(C33:D33)","v":-124.21052631578948},{"$f":"-$C$10*AVERAGE(D33:E33)","v":-155.81163434903047},-194.44443796471788,-241.59016827679343,-299.03418599014014,"<--  =-$C$10*AVERAGE(G33:H33)"]
[null,"Profit before tax",366.4,{"$f":"SUM(D18:D22)","v":442.53995312167058},{"$f":"SUM(E18:E22)","v":529.28879000596123},629.9644041521077,749.13099116092508,890.568548917984,"<--  =SUM(H18:H22)"]
[null,"Taxes",-146.56,{"$f":"-D23*$C$13","v":-177.01598124866825},{"$f":"-E23*$C$13","v":-211.7155160023845},-251.98576166084308,-299.65239646437004,-356.22741956719364,"<--  =-H23*$C$13"]
[null,"Profit after tax",219.83999999999997,{"$f":"D24+D23","v":265.52397187300232},{"$f":"E24+E23","v":317.57327400357673},377.97864249126462,449.47859469655504,534.34112935079042,"<--  =H24+H23"]
[null,"Dividends",-109.91999999999999,{"$f":"-D25*$C$14","v":-132.76198593650116},{"$f":"-E25*$C$14","v":-158.78663700178836},-188.98932124563231,-224.73929734827752,-267.17056467539521,"<--  =-H25*$C$14"]
[null,"Retained earnings",109.91999999999999,{"$f":"D25+D26","v":132.76198593650116},{"$f":"E25+E26","v":158.78663700178836},188.98932124563231,224.73929734827752,267.17056467539521,"<--  =H25+H26"]
[null,null,null,null,null,null,null,null,"<--  "]
[null,"Balance sheet",null,null,null,null,null,null,"<--  "]
[null,"Cash and marketable securities",80,{"$f":"D42-D35-D31","v":88.761985936501105},{"$f":"E42-E35-E31","v":38.7486229382896},-7.7708932622044813E-9,-1.5248892850650009E-6,-1.7252031170755799E-5,"<--  =H42-H35-H31"]
[null,"Current assets",200,{"$f":"D18*$C$6","v":180},{"$f":"E18*$C$6","v":216},259.2,311.03999999999996,373.24799999999993,"<--  =H18*$C$6"]
[null,"Fixed assets",null,null,null,null,null,null,"<--  "]
[null,"     At cost",1100,{"$f":"D35-D34","v":1384.2105263157896},{"$f":"E35-E34","v":1732.0221606648199},2156.8665986295377,2674.9367669063313,3305.7469528964712,"<--  =H35-H34"]
[null,"     Depreciation",-300,{"$f":"C34+D22","v":-424.21052631578948},{"$f":"D34+E22","v":-580.02216066481992},-774.46659862953777,-1016.0567669063312,-1315.0909528964712,"<--  =G34+H22"]
[null,"     Net fixed assets",800,{"$f":"D18*$C$8","v":960},{"$f":"E18*$C$8","v":1152},1382.4,1658.88,1990.6559999999999,"<--  =H18*$C$8"]
[null,"Total assets",1080,{"$f":"D35+D31+D30","v":1228.7619859365011},{"$f":"E35+E31+E30","v":1406.7486229382896},1641.5999999922292,1969.9199984751108,2363.9039827479687,"<--  =H35+H31+H30"]
[null,null,null,null,null,null,null,null,"<--  "]
[null,"Current liabilities",80,{"$f":"D18*$C$7","v":96},{"$f":"E18*$C$7","v":115.2},138.24,165.88800000000001,199.06559999999999,"<--  =H18*$C$7"]
[null,"Debt",400,{"$f":"MAX(D31+D35-D38-D40-D41,C39)","v":400},{"$f":"MAX(E31+E35-E38-E40-E41,D39)","v":400},422.82205583805307,498.75475906211193,592.39059957493964,{"$f":"getformula(H39)","v":"<--  =MAX(H31+H35-H38-H40-H41,G39)"}]
[null,"Stock",450,{"$f":"C40","v":450},{"$f":"D40","v":450},450,450,450,{"$f":"getformula(H40)","v":"<--  =G40"}]
[null,"Accumulated retained earnings",150,{"$f":"C41+D27","v":282.76198593650116},{"$f":"D41+E27","v":441.54862293828955},630.53794418392181,855.27724153219935,1122.4478062075946,"<--  =G41+H27"]
[null,"Total liabilities and equity",1080,{"$f":"SUM(D38:D41)","v":1228.7619859365011},{"$f":"SUM(E38:E41)","v":1406.7486229382896},1641.6000000219749,1969.9200005943112,2363.9040057825341,"<--  =SUM(H38:H41)"]
[null,null,null,null,null,null,null,null,"<--  "]
[null,null,null,null,null,null,null,null,"<--  "]
[null,"Year",0,1,2,3,4,5,"<--  5"]
[null,"Free cash flow calculation",null,null,null,null,null,null,"<--  "]
[null,"Profit after tax",null,{"$f":"D25","v":265.52397187300232},{"$f":"E25","v":317.57327400357673},377.97864249126462,449.47859469655504,534.34112935079042,"<--  =H25"]
[null,"Add back depreciation",null,{"$f":"-D22","v":124.21052631578948},{"$f":"-E22","v":155.81163434903047},194.44443796471788,241.59016827679343,299.03418599014014,"<--  =-H22"]
[null,"Subtract increase in current assets",null,{"$f":"-(D31-C31)","v":20},{"$f":"-(E31-D31)","v":-36},-43.199999999999989,-51.839999999999975,-62.20799999999997,"<--  =-(H31-G31)"]
[null,"Add back increase in current liabilities",null,{"$f":"(D38-C38)","v":16},{"$f":"(E38-D38)","v":19.200000000000003},23.040000000000006,27.647999999999996,33.177599999999984,"<--  =(H38-G38)"]
[null,"Subtract increase in fixed assets at cost",null,{"$f":"-(D33-C33)","v":-284.21052631578959},{"$f":"-(E33-D33)","v":-347.81163434903033},-424.84443796471783,-518.07016827679354,-630.81018599013987,"<--  =-(H33-G33)"]
[null,"Add back after-tax interest on debt",null,{"$f":"-D20*(1-$C$13)","v":24},{"$f":"-E20*(1-$C$13)","v":24},24.684661674908469,27.647304401025142,32.734360195803923,"<--  =-H20*(1-$C$13)"]
[null,"Subtract after-tax interest on cash and mkt. securities",null,{"$f":"-D21*(1-$C$13)","v":-4.0502876624760269},-3.060254612994977,-0.92996694500378496,-6.3656283145974156E-8,-1.140678504270909E-6,"<--  =-H21*(1-$C$13)"]
[null,"Free cash flow",null,{"$f":"SUM(D47:D53)","v":161.47368421052619},{"$f":"SUM(E47:E53)","v":129.71301939058191},151.17333722116922,176.45389903392382,206.26908840591614,"<--  =SUM(H47:H53)"]
[null,"Use our EBIT formula",null,{"$f":"(D18+D19+D22)*(1-$C$13)+D48+SUM(D49:D51)","v":161.47368421052619},{"$f":"(E18+E19+E22)*(1-$C$13)+E48+SUM(E49:E51)","v":129.71301939058191},151.17333722116939,176.45389903392379,206.26908840591614,"<--  =(H18+H19+H22)*(1-$C$13)+H48+SUM(H49:H51)"]
[null,"CONSOLIDATED STATEMENT OF CASH FLOWS:  RECONCILING THE CASH BALANCES",null,null,null,null,null,null,"<--  "]
[null,"     Cash flow from operating activities",null,null,null,null,null,null,"<--  "]
[null,"Profit after tax",null,{"$f":"D25","v":265.52397187300232},{"$f":"E25","v":317.57327400357673},377.97864249126462,449.47859469655504,534.34112935079042,"<--  =H25"]
[null,"Add back depreciation",null,{"$f":"-D22","v":124.21052631578948},{"$f":"-E22","v":155.81163434903047},194.44443796471788,241.59016827679343,299.03418599014014,"<--  =-H22"]
[null,"Adjust for changes in net working capital:",null,null,null,null,null,null,"<--  "]
[null,"     Subtract increase in current assets",null,{"$f":"-(D31-C31)","v":20},{"$f":"-(E31-D31)","v":-36},-43.199999999999989,-51.839999999999975,-62.20799999999997,"<--  =-(H31-G31)"]
[null,"     Add back increase in current liabilities",null,{"$f":"(D38-C38)","v":16},{"$f":"(E38-D38)","v":19.200000000000003},23.040000000000006,27.647999999999996,33.177599999999984,"<--  =(H38-G38)"]
[null,"Net cash from operating activities",null,{"$f":"SUM(D58:D62)","v":425.7344981887918},{"$f":"SUM(E58:E62)","v":456.58490835260721},552.26308045598239,666.87676297334849,804.34491534093058,"<--  =SUM(H58:H62)"]
[null,null,null,null,null,null,null,null,"<--  "]
[null,"     Cash flow from investing activities",null,null,null,null,null,null,"<--  "]
[null,"Aquisitions of fixed assets--capital expenditures",null,{"$f":"-(D33-C33)","v":-284.21052631578959},{"$f":"-(E33-D33)","v":-347.81163434903033},-424.84443796471783,-518.07016827679354,-630.81018599013987,"<--  =-(H33-G33)"]
[null,"Purchases of investment securities",null,0,0,0,0,0,"<--  0"]
[null,"Proceeds from sales of investment securities",null,0,0,0,0,0,"<--  0"]
[null,"Net cash used in investing activities",null,{"$f":"SUM(D66:D68)","v":-284.21052631578959},{"$f":"SUM(E66:E68)","v":-347.81163434903033},-424.84443796471783,-518.07016827679354,-630.81018599013987,"<--  =SUM(H66:H68)"]
[null,null,null,null,null,null,null,null,"<--  "]
[null,"     Cash flow from financing activities",null,null,null,null,null,null,"<--  "]
[null,"Net proceeds from borrowing activities",null,{"$f":"D39-C39","v":0},{"$f":"E39-D39","v":0},22.822055838053075,75.932703224058855,93.635840512827713,"<--  =H39-G39"]
[null,"Net proceeds from stock issues, repurchases",null,{"$f":"D40-C40","v":0},0,0,0,0,"<--  =H40-G40"]
[null,"Dividends paid",null,{"$f":"D26","v":-132.76198593650116},{"$f":"E26","v":-158.78663700178836},-188.98932124563231,-224.73929734827752,-267.17056467539521,"<--  =H26"]
[null,"Net cash from financing activities",null,{"$f":"SUM(D72:D74)","v":-132.76198593650116},{"$f":"SUM(E72:E74)","v":-158.78663700178836},-166.16726540757924,-148.80659412421866,-173.5347241625675,"<--  =SUM(H72:H74)"]
[null,null,null,null,null,null,null,null,"<--  "]
[null,"Net increase in cash and cash equivalents",null,{"$f":"D75+D69+D63","v":8.7619859365010484},{"$f":"E75+E69+E63","v":-50.013362998211448},-38.748622916314616,5.7233626193919918E-7,5.188223212826415E-6,"<--  =H75+H69+H63"]
[null,"Check:  changes in cash and mkt. securities",null,{"$f":"D30-C30","v":8.7619859365011052},{"$f":"E30-D30","v":-50.013362998211505},-38.748622946060493,-1.5171183918027964E-6,-1.5727141885690799E-5,"<--  =H30-G30"]