EXCEL：财政金融建模Problems 03.xlsVIP

Exercise 10--solution (中文) Exercise 10--solution Exercise 10--setup (中文) Exercise 10--setup Exercise 9 (中文) Exercise 9 Exercise 8 (中文) Exercise 8 Exercise 7 (中文) Exercise 7 Exercise 6 (中文) Exercise 6 Exercise 5 (中文) Exercise 5 Exercise 4 (中文) Exercise 4 Exercise 3 (中文) Exercise 3 Exercise 2 (中文) Exercise 2 Exercise 1 Model 1 for reference (中文) Model 1 for reference Sales growth Current assets/Sales Current liabilities/Sales Net fixed assets/Sales Costs of goods sold/Sales Depreciation rate Tax rate Dividend payout ratio Year Income statement Sales Costs of goods sold Depreciation Profit before tax Taxes Profit after tax Dividends Retained earnings Balance sheet Current assets Fixed assets At cost Depreciation Net fixed assets Total assets Current liabilities Debt Stock Accumulated retained earnings Total liabilities and equity Cash and marketable securities Total Interest rate on debt Interest payments on debt Interest paid on cash marketable securities FIRST FINANCIAL MODEL Free cash flow Add back depreciation Add back increase in current liabilities Subtract increase in fixed assets at cost Subtract increase in current assets Free cash flow calculation Add back after-tax interest on debt Valuing the firm FCF Terminal value Weighted average cost of capital Subtract out value of firms debt today Equity value -- =G59*(1+B3)/(B56-B3) -- =NPV(B56,C61:G61) Interest earned on cash marketable securities Valuing the firm--using half-year discounting -- =G72*(1+B3)/(B69-B3) Add in initial (year 0) cash and mkt. securities Enterprise value NPV of row 61 Cash and marketable securities as negative debt NPV of row 61 = enterprise value Net year 0 debt -- =-B37+B28 NPV of row 81 -- =NPV(B76,C81:G81)*(1+B76) growth -- =B87 WACC growth rate of sales Subtract after-tax interest on cash mkt. securities nmf Sales, general and administrative expenses -- Added SGA Data table: Value as function of SGA of SGA and sales growth Fixed assets at cost/Sales -- Increased fro