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)?

 

Annual sales growth COGS as % of sales SGA as % of sales Cash as % of sales Other Current Assets/Sales Current Liabilities/Sales Depreciation rate (% of avg- gross fixed assets) Net fixed assets (% of sales) Interest rate on debt Interest paid on cash and mrk sec. Tax rate Dividend Payout Ratio Year Income statement Sales COGS SGA Interest payment on debt Interest earned on Cash and Mrk Sec. Depreciation Profit Before tax Taxes Profit after tax Dividends Retained earnings Balance Sheet Cash and Marketable Sec. Other Current Assets Fixed Assets At cost Depreciation Net Fixed Assets Total Assets Current liabilities Debt Stock Paid in Capital Accumulated Retained Earnings Treasury Stock Total liabilities and equity Repurchases Average Repurchases from Year 1-5 Data Table Sales Growth $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 0 6% 65% 21% 2% 17% 14% 2% 23% 5% 1% 21% 40% 100,000 $ (65,000) $ (21,000) $ (500) 20 (800) 12,720 (2,671) 10,049 (4,020) 6,029 2,000 17,000 40,000 (17,000) 23,000 42,000 14,000 10,000 7,000 17,000 (6,000) 42,000 0.00% 1.00% 2.00% 3.00% 4.00% 5.00% 6.00% 7.00% 8.00% 9.00% 10.00% Forecast 1 #N/A Notes: Other Current Assets/Sales does not include cash Depreciation and interest are calculated based on average balance sheet values. 106,000 $ (68,900) $ (22,260) $ Forecast 2 112,360 $ (73,034) $ (23,596) $ Forecast 3 119,102 $ (77,416) S (25,011) $ Forecast 4 126,248 $ (82,061) $ (26,512) $ Forecast 5 #N/A 133,823 (86,985) (28,103)