Excel Lab #5: Forecasting / Projection Preparation

Assignment:

You will be populating and projecting 3 years of financial data for the target company.

Given Information:

Historical (Year 0) Financials

Growth/Ratio Assumptions

Income Statement Assumptions:

1. Sales in Year 1 is $682.5M. Compute the Sales growth in the first year. This growth rate increases by 10% per year, for each of the next two years.
2. COGS% of Sales is 60% in the first year. This ratio increases by 5% per year, for each of the next two years.
3. SG&A% of Sales is 20% and will remain constant.
4. The tax rate is fixed at 40%.

Balance Sheet Assumptions:

Asset Assumptions

1. Current assets% of Sales is 20% in the first year, 25% in the second year, and 30% in the third year. Note: Cash is a separate line item (you will need to compute this).
2. Investment growth/year is fixed at 10%.

Liabilities Assumptions

3. Current liabilities% of Sales is 15% in the first year. This ratio increases by 5% per year, for each of the next two years.
4. Long-term debt is fixed at $120M per year.

Equity Assumptions

5. Stock and Additional Paid-in-Capital is fixed at $420M per year.
6. Retained Earnings is $240M in the first year. This amount increases by $10M per year, for each of the next two years.

		Historical
Income Statement
	Sales	$ 650.0
	COGS	$ 400.0
	Gross Margin	$ 250.0
	SG&A	$ 169.0
	Taxable Income	$ 81.0
	Taxes	$ 32.0
	Net income	$ 49.0
Balance Sheet
Assets
	Cash	$ 407.0
	Current assets	$ 136.5
	Investments	$ 353.0
	Total assets	$ 896.5
Liabilities
	Current liabilities	$ 116.5
	Long-term debt	$ 120.0
Equity
	Stock and APIC	$ 420.0
	Retained earnings	$ 240.0
	Total equity	$ 660.0
	Total liabilities and equity	$ 896.5