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

Transcribed Image Text:

Annual sales growth 6% Notes: COGS as % of sales 65% Other Current Assets/Sales does not indude cash SGA as % of sales Cash as % of sales Other Current Assets/Sales Current Liabilities/Sales Deprediation rate (k of avg. gross fied assets) Net fixed assets (% of sales) Interest rate on debt Interest paid on cash and mrk sec. Тах гate Dividend Payout Ratio 21% Deprediation and interest are calculated based on average balance sheet values. 17% 14% 23% 21% 40% Forecast Forecast Forecast Forecast Forecast Year 1 4 Income statement Sales COGS 100,000 $ 133,823 (35,985) (28,103) 106,000 $ 112,360 $ 119,102 $ 126,248 $ (65,000) $ (21,000) $ (73,034) $ (23,596) $ (68,900) $ (77,416) S (82,061) $ SGA (22 260) $ (25,011) $ (26,512) $ Interest payment on debt (s00 Interest earned on Cash and Mrk Sec. 20 Depredation (300) Profit Before tax Tawes 12,720 (2,671) Profit after tax Dividends 10,049 (4,020) Retained earnings 6,029 Balance Sheet Cash and Marketable Sec. $ 2,000 Other Current Assets 17,000 Fived Assets At cost 40,000 Depreciation (17,000) Net Flxed Assets 23,000 Total Assets 42,000 Current liabilities 14,000 Debt 10,000 Stock Paid in Capital 7,000 Accumulated Retained Earnings $ 17,000 Treasury Stock Total liabilities and equity (6,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% Annual sales growth 6% Notes: COGS as % of sales 65% Other Current Assets/Sales does not indude cash SGA as % of sales Cash as % of sales Other Current Assets/Sales Current Liabilities/Sales Deprediation rate (k of avg. gross fied assets) Net fixed assets (% of sales) Interest rate on debt Interest paid on cash and mrk sec. Тах гate Dividend Payout Ratio 21% Deprediation and interest are calculated based on average balance sheet values. 17% 14% 23% 21% 40% Forecast Forecast Forecast Forecast Forecast Year 1 4 Income statement Sales COGS 100,000 $ 133,823 (35,985) (28,103) 106,000 $ 112,360 $ 119,102 $ 126,248 $ (65,000) $ (21,000) $ (73,034) $ (23,596) $ (68,900) $ (77,416) S (82,061) $ SGA (22 260) $ (25,011) $ (26,512) $ Interest payment on debt (s00 Interest earned on Cash and Mrk Sec. 20 Depredation (300) Profit Before tax Tawes 12,720 (2,671) Profit after tax Dividends 10,049 (4,020) Retained earnings 6,029 Balance Sheet Cash and Marketable Sec. $ 2,000 Other Current Assets 17,000 Fived Assets At cost 40,000 Depreciation (17,000) Net Flxed Assets 23,000 Total Assets 42,000 Current liabilities 14,000 Debt 10,000 Stock Paid in Capital 7,000 Accumulated Retained Earnings $ 17,000 Treasury Stock Total liabilities and equity (6,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%

Answer rating: 100% (QA)

Assumptions Annual sales growth 6 COGS as of sales 65 SGA as of sales 21 Cash as of sales 2 Other Current AssetsSales 17 Current LiabilitiesSales 14 Depreciation rate of avg gross fixed assets 2 Net f... View the full answer