{"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]
[null,"Costs of goods sold",-500]
[null,"Interest payments on debt",-40]
[null,"Interest earned on cash and marketable securities",6.4]
[null,"Depreciation",-100]
[null,"Profit before tax",366.4]
[null,"Taxes",-146.56]
[null,"Profit after tax",219.83999999999997]
[null,"Dividends",-109.91999999999999]
[null,"Retained earnings",109.91999999999999]
[]
[null,"Balance sheet"]
[null,"Cash and marketable securities",80,null,null,null,null,null,"***Use Cash as the plug, i.e. it is used to make the balance sheet balance"]
[null,"Current assets",200]
[null,"Fixed assets"]
[null,"     At cost",1100]
[null,"     Depreciation",-300]
[null,"     Net fixed assets",800]
[null,"Total assets",1080]
[]
[null,"Current liabilities",80]
[null,"Debt",400]
[null,"Stock",450]
[null,"Accumulated retained earnings",150]
[null,"Total liabilities and equity",1080]
[]
[]
[null,"Year",0,1,2,3,4,5]
[null,"Free cash flow calculation"]
[null,"Profit after tax"]
[null,"Add back depreciation"]
[null,"Subtract increase in current assets"]
[null,"Add back increase in current liabilities"]
[null,"Subtract increase in fixed assets at cost"]
[null,"Add back after-tax interest on debt"]
[null,"Subtract after-tax interest on cash and mkt. securities"]
[null,"Free cash flow"]
[null,"Use our EBIT formula"]
[null,"CONSOLIDATED STATEMENT OF CASH FLOWS:  RECONCILING THE CASH BALANCES"]
[null,"     Cash flow from operating activities"]
[null,"Profit after tax"]
[null,"Add back depreciation"]
[null,"Adjust for changes in net working capital:"]
[null,"     Subtract increase in current assets"]
[null,"     Add back increase in current liabilities"]
[null,"Net cash from operating activities"]
[]
[null,"     Cash flow from investing activities"]
[null,"Aquisitions of fixed assets--capital expenditures"]
[null,"Purchases of investment securities"]
[null,"Proceeds from sales of investment securities"]
[null,"Net cash used in investing activities"]
[]
[null,"     Cash flow from financing activities"]
[null,"Net proceeds from borrowing activities"]
[null,"Net proceeds from stock issues, repurchases"]
[null,"Dividends paid"]
[null,"Net cash from financing activities"]
[]
[null,"Net increase in cash and cash equivalents"]
[null,"Check:  changes in cash and mkt. securities"]