You are interning in the treasury group of a company that recently experienced a large negative

shock in the price of their output good. Luckily, the firm still has access to debt markets, and

management wants to know how much debt the company may need to issue today in order to be

sure to have cash on hand for the next 5 years in the event output prices do not recover for five

years. Management is reluctant to consider dividend cuts, but they want the model to be able to

accommodate this possibility as well.

Assumptions

1. Any new debt will be borrowed over the coming year (that is, it will be included in the

year 1 forecasted debt balance), and should be sufficiently large to ensure positive cash

balances for the next five years. Debt will remain at this same new level from year 1

through year 5. Management wants the projected year 5 cash balance to equal the current

balance of $14. In the interim, they are fine with cash balances floating around. This

means that the plug in the model for year 5 is the debt balance, and the plug for years 1-4

is the cash balance.

2. The firm will not issue or repurchase stock.

3. The output price has declined by 45% relative to year 0 and is expected to remain

constant over the five year period. As a result, the number of units sold is expected to be

10% higher over the coming year compared to year 0 unit sales and will remain constant

over the five year period.

4. Unfortunately, input prices have not fallen. COGS and Inventory are both tied to units

sold. Costs are $3.50 per unit. The end-of-period inventory reflects 1/3 of the forecasted

cost of the next year?s unit sales (please assume year 6 unit sales are projected to be the

same as those from years 1-5).

5. The firm will not divest or invest in capital assets, so gross fixed assets will remain

constant.

6. The firm can take tax credits associated with any operating losses.

Build a one-page spreadsheet model to answer the questions listed below:

1. (25 points) Complete the five-year forecasted pro-forma financial statement for the firm

assuming dividends paid remain constant. How much new debt must be issued from year 0 to

year 1?

2. (15 points) If the firm cuts its dividend, it will do so in the first year (that is, it will remain at

the new level from year 1 through year 5). Incorporate a possible dividend cut into your model.

Specifically, create a data table and plot that shows the amount of new debt that must be issued

from year 0 to year 1 as a function of the magnitude of the dividend cut. Consider dividend cut

magnitudes (in % terms) of 0% to 25% in increments of 2.5%. At approximately what dividend

cut magnitude would the firm not need any new debt?

Assumptions Growth in units sold from year 0 to year 1 Unit price growth from year 0 to year 1 COGS (per unit sold) Other Current Assets/Sales Inventory as % of forecasted COGS next year Current Liabilities/Sales Depreciation rate (% of avg. gross fixed assets) Interest rate on debt Interest paid on cash and mrk sec. Tax rate Dividend Cut Percentage from year 0 to year 1 10% -45% $3.50 15% 33% 8% 10% 10% 8% 40% 0% Notes: Other Current assets/Sales does not include cash or inven Depreciation and interest are calculated based on average The firm can take any tax credits associated with loss Forecast 0 1 Year Forecast 2 Forecast 3 Forecast 4 Income statement Units Price 100.00 $7.00 Sales COGS Interest payment on debt Interest earned on Cash and Mrk Sec. Depreciation Profit Before tax Taxes Profit after tax Dividends $700 -$350 -$3 $4 -$70 $281 -$113 $169 -$63 Retained earnings $106 Balance Sheet Cash and Marketable Sec. Other Current Assets Inventory Fixed Assets At cost Depreciation Net Fixed Assets Total Assets $749 -$210 $539 $786 Current liabilities Debt Stock Accumulated Retained Earnings $56 $30 $200 $500 Total liabilities and equity $786 $14 $105 $128 110.00 $3.85 110.00 $3.85 110.00 $3.85 110.00 $3.85 ales does not include cash or inventory est are calculated based on average Balance Sheet items. y tax credits associated with losses. Forecast 5 110.00 $3.85