FINANCIAL MODEL TEMPLATE \begin{tabular}{|c|c|c|c|c|c|c|} \hline \\ \hline Sales growth & 8% & & & & & \\ \hline Current assets/Sales & 15% & & & & & \\ \hline Current liabilities/Sales & 7% & & & & & \\ \hline Net fixed assets/Sales & 72% & & & & & \\ \hline Costs of goods sold/Sales & 58% & & & & & \\ \hline Depreciation rate ( % of Average Fixed Assets) & 8% & & & & & \\ \hline Interest rate on debt & 7.00% & & & & & \\ \hline Interest paid on cash and marketable securities & 5.00% & & & & & \\ \hline Tax rate & 25% & & & & & \\ \hline Dividend payout ratio & 70% & & & & & \\ \hline Cash/Sales & 10% & & & & & \\ \hline Debt & Plug & & & & & \\ \hline Stock (paid in capital) & Constant & & & & & \\ \hline WACC & 16% & & & & & \\ \hline Terminal Growth & 5% & & & & & \\ \hline Year & 0 & 1 & 2 & 3 & 4 & 5 \\ \hline \multicolumn{7}{|l|}{ Income statement } \\ \hline Sales & $1.000.00 & & & & & \\ \hline Costs of goods sold & ($500.00) & & & & & \\ \hline Interest payments on debt & ($32.00) & & & & & \\ \hline Interest earned on cash and marketable securities & $6.40 & & & & & \\ \hline Depreciation & ($100.00) & & & & & \\ \hline Profit before tax & $374.40 & & & & & \\ \hline Taxes & ($149.76) & & & & & \\ \hline Profit after tax & $224.64 & & & & & \\ \hline Dividends & ($134.78) & & & & & \\ \hline Retained earnings & $89.86 & & & & & \\ \hline \multicolumn{7}{|l|}{ Balance sheet } \\ \hline Cash and marketable securities & $80.00 & & & & & \\ \hline Current assets & $150.00 & & & & & \\ \hline \multicolumn{7}{|l|}{ Fixed assets } \\ \hline At cost & $1,070.00 & & & & & \\ \hline Depreciation & ($300.00) & & & & & \\ \hline Net fixed assets & $770.00 & & & & & \\ \hline Total assets & $1,000.00 & & & & & \\ \hline Current liabilities & $80.00 & & & & & \\ \hline Debt & $320.00 & & & & & \\ \hline Stock & $450.00 & & & & & \\ \hline Accumulated retained earnings & $150.00 & & & & & \\ \hline Total liabilities and equity & $1,000.00 & & & & & \\ \hline & & & & & & \\ \hline Free Cash Flows & & & & & & \\ \hline \end{tabular} 1. Use the " Ch.7 Handout Template_2023" to estimate the following: [Total 22 points] a. The Income Statement and Balance Sheet in years 1-5. [11 points] b. FCFE in years 15. [6 points] c. The terminal value in year 5 . [2 points] d. Enterprise Value, Firm Value, and Equity Value [ 3 points] 2. Perform a Monte Carlo analysis creating 1,000 iterations of the equity value of the firm in Question 1. [Total 12 points] a. Specifically, assume the Sales Growth Rate is normally distributed with a mean 8% and a standard deviation of 6%. Also assume the Cost of Goods Sold is normally distributed with a mean of 58% and a standard deviation of 15\%. (Suggestion: Make a copy the spreadsheet from question 1 and do the Monte Carlo analysis in this new spreadsheet.) [9 points] b. Calculate the following summary statistics for the distribution of equity values: mean, median, standard deviation, 5th percentile and 95th percentile. [3 points]