1. Do a sensitivity analysis using your discounted cash flow model. Recalculate the price per share for Robertson for the following scenarios:

a. Estimate the margin rates (COGS/Sales and SG&A/Sales) that will result in a price of $50 (the minimum price Simmons is willing to accept you can do this using trial and error).

b. Cost of goods sold/sales and SG&A/Sales remains at 69% and 22%, respectively (i.e., there are no-cost improvements) (no changes to your original assumption of growth rates) [show this analysis in your spreadsheet]

Key assumptions: \begin{tabular}{|c|c|} \hline Annual growth rate in sales (20032006)= & 6.0% from text \\ \hline growth rate after 2006= & 0.0% \\ \hline Income tax rate = & 400 iftron ten \\ \hline Net working capital/Sales = & 43.4% frem Exibits 1 and 2 \\ \hline PP\&E / Sales = & 34.4% from Exhbits 1 and 2 \\ \hline Depreciation/PP\&E ratio = & 11.1% from fxhibit: 1 and 2 \\ \hline WACC = & 9.93% onn catculations \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline \multirow{2}{*}{ Pro-formas for Robertson Tool Co. } & \multicolumn{4}{|c|}{ Actuol } & \multicolumn{6}{|c|}{ Forecast } \\ \hline & \multicolumn{2}{|c|}{2002} & \multicolumn{2}{|c|}{2003} & \multicolumn{2}{|c|}{2004} & \multicolumn{2}{|c|}{2005} & \multicolumn{2}{|c|}{2006} \\ \hline Sales & s & 55.3 & & 58.6 & & 62.1 & & 65.9 & & 69.8 \\ \hline COGS & $ & 37.9 & & 39.9 & & 41.6 & & 43.5 & & 45,4 \\ \hline SG\&A & $ & 12.3 & & 12.3 & & 12.4 & & 12.5 & & 13.3 \\ \hline Depreciation & s & 2.1 & & 2.2 & & 2.4 & & 2.5 & & 2.7 \\ \hline Operating profit (EBIT) & $ & 3.0 & $ & 4.2 & $ & 5.7 & s & 7.4 & $ & 8.5 \\ \hline Tax40% & 5 & 1.2 & 5 & 1.7 & 5 & 2.3 & s & 3.0 & $ & 3.4 \\ \hline EBIAT & S & 1.8 & $ & 2.5 & S & 3.4 & s & 4.4 & s & 5.1 \\ \hline COGS/Sales & & 69% & & 68% & & 67% & & 66% & & 65% \\ \hline SG\&A/Sales & & 22% & & 21% & & 20% & & 19% & & 19% \\ \hline Net plant \& equipment (begin) & & & $ & 19.0 & $ & 20.8 & $ & 21.9 & $ & 23.0 \\ \hline Capital expenditures & & & $ & 4.0 & $ & 3.5 & $ & 3.6 & $ & 3.8 \\ \hline Depreciation expense & & & $ & 2.2 & $ & 2.4 & $ & 2.5 & $ & 2.7 \\ \hline Net plant \& equipment (end) & $ & 19.00 & $ & 20.8 & 5 & 21.9 & $ & 23.0 & $ & 24.2 \\ \hline \end{tabular} Cash flow projections and enterprise value \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|} \hline Depreciation & & & 2.2 & & 2.4 & & 2.5 & & 2.7 \\ \hline Cash flow from operations & & & 4.8 & & 5.8 & & 6.9 & & 7.8 \\ \hline NWC & 24.0 & & 25.4 & & 27.0 & & 28.6 & & 30.3 \\ \hline Increase in NWC & & & 1.4 & & 1.5 & & 1.6 & & 1.7 \\ \hline Capital expenditures & & $ & 4.0 & $ & 3.5 & $ & 3.6 & $ & 3.8 \\ \hline Free cash flow & & $ & (0.68) & $ & 0.76 & $ & 1.71 & $ & 2.25 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline Total cash flow & & $ & (0.68)$ & 0.76 & $ & 1.71 & $ & 2.25 \\ \hline Discounted cash flows & & $ & (0.68)$ & 0.69 & $ & 1.42 & $ & 1.69 \\ \hline \multicolumn{2}{|c|}{ Enterprise value (Present value of cash flows) = } & \multicolumn{2}{|r|}{$18.77} & & & & & \\ \hline Price/share & & \multicolumn{2}{|r|}{$32.13} & & & & & \\ \hline WACC & 9.9% & \multicolumn{2}{|r|}{$32.13} & \multicolumn{2}{|l|}{8.9%} & \multicolumn{2}{|l|}{9.5%} & \multirow[t]{2}{*}{9.9%} \\ \hline v & 37.7 & \multicolumn{2}{|r|}{0.0%} & & & \multicolumn{2}{|l|}{9.5%} & \\ \hline D/V & 31.8% & & 0.5% & & & & & \\ \hlineE/N & 68.2% & & 1.0% & & & & & \\ \hline Times Interst Earned & 2.25 & & 1.5% & & & & & \\ \hline \end{tabular}