You are a financial analyst at a firm that has been returning cash to shareholders through stock repurchases. You want to determine how the level of repurchases will vary as a function of the sales growth the firm achieves over the next 5 years. To do so, build out a pro-forma of the firm reflecting the assumptions noted in the template spreadsheet. Assume the debt balance will remain constant.

1. (25 points) Complete the five-year forecasted pro-forma financial statement for the firm. Please assume that any excess cash will go to repurchases, so use Treasury Stock as the plug in the model. What is the average level of repurchases over the five-year period that the firm can manage based on the assumed sales growth rate?

2. (10 points) You want to forecast the levels of repurchases if sales growth differs from your baseline assumption. To determine this, create an Excel data table that shows the amount of average repurchases over the five-year period as a function of the sales growth rate. Consider sales growth ranging from 0% to 10% in increments of 1%. At approximately what sales growth rate is the firm unable to sustain average repurchases of at least $5,500 (on average over the next 5 years)?

6% 65% Notes: Other Current Assets/Sales does not indude cash Depreciation and interest are calculated based on average balance sheet values. Annual sales growth COGS as % of sales SGA as of sales Cash as of sales Other Current Assets/Sales Current Uabilities/Sales Depreciation rate of avg-gross fixed assets) Net fixed assets * of sales) Interest rate on debt Interest paid on cash and mark sec. Tax rate Dividend Payout Ratio 21% 2% 17% 14% 2% 23% 5% 1% 21% 40% Forecast 1 Forecast 2 Forecast 3 Forecast 4 Forecast 5 0 105,000 $ 168,900 $ 122 260) $ 112,350 $ (73,034) $ (23,596) $ 119,102 $ (77.416) $ 125,011) $ 126,248 $ (82,051) $ (26,512) $ 133,823 (86,985) (28,103) Year Income statement Sales COGS SGA Interest payment on debt interest earned on Cash and Mrk Sec. Depredation Prolit Before tax Taxes Profit after tax Dividends Retained earnings $ $ $ $ $ $ $ $ $ $ $ 100,000 $ 165,000) $ (21,000 $ (500) 20 (800) 12.720 (2,671) 10,049 14.020) 6.029 $ $ 2.000 17.000 Balance Sheet Cash and Marketable Sec. Other Current Assets Fixed Assets At cost Deprecation Net Fixed Assets Total Assets $ $ $ $ 40,000 (17.000) 23,000 42,000 $ $ 14,000 10,000 Current liabilities Debt Stock Pald in Capital Accumulated Retained Earrings Treasury Stock Total liabilities and equity $ $ $ $ 7.000 17.000 16,000) 42,000 Repurchases Average Repurchases from Year 15 #N/A #N/A Data Table Sales Growth 0.00% 100% 2.00% 3.00% 4.00% 5.00% 6.00% 7.00% 8.00% 9.00 10.00% 6% 65% Notes: Other Current Assets/Sales does not indude cash Depreciation and interest are calculated based on average balance sheet values. Annual sales growth COGS as % of sales SGA as of sales Cash as of sales Other Current Assets/Sales Current Uabilities/Sales Depreciation rate of avg-gross fixed assets) Net fixed assets * of sales) Interest rate on debt Interest paid on cash and mark sec. Tax rate Dividend Payout Ratio 21% 2% 17% 14% 2% 23% 5% 1% 21% 40% Forecast 1 Forecast 2 Forecast 3 Forecast 4 Forecast 5 0 105,000 $ 168,900 $ 122 260) $ 112,350 $ (73,034) $ (23,596) $ 119,102 $ (77.416) $ 125,011) $ 126,248 $ (82,051) $ (26,512) $ 133,823 (86,985) (28,103) Year Income statement Sales COGS SGA Interest payment on debt interest earned on Cash and Mrk Sec. Depredation Prolit Before tax Taxes Profit after tax Dividends Retained earnings $ $ $ $ $ $ $ $ $ $ $ 100,000 $ 165,000) $ (21,000 $ (500) 20 (800) 12.720 (2,671) 10,049 14.020) 6.029 $ $ 2.000 17.000 Balance Sheet Cash and Marketable Sec. Other Current Assets Fixed Assets At cost Deprecation Net Fixed Assets Total Assets $ $ $ $ 40,000 (17.000) 23,000 42,000 $ $ 14,000 10,000 Current liabilities Debt Stock Pald in Capital Accumulated Retained Earrings Treasury Stock Total liabilities and equity $ $ $ $ 7.000 17.000 16,000) 42,000 Repurchases Average Repurchases from Year 15 #N/A #N/A Data Table Sales Growth 0.00% 100% 2.00% 3.00% 4.00% 5.00% 6.00% 7.00% 8.00% 9.00 10.00%