1.Using the historic financial statements for MicroDrive from the attached spreadsheet and the assumptions listed below create a five-year financial statement forecast (Income statement and Balance sheet) for the company (2018-2022). Show formulas in cells, and how the numbers were calculated.

Income Statement Assumptions

a.Sales, for the next five years are expected to grow at rates of 10%, 8%, 7%, 5% and 5% respectively.

b.COGS (excl. depr.) are expected to remain constant at 76% of sales.

c.Depreciation is expected to be 10% of net fixed assets.

d.Other operating expenses are expected to be 10% or sales each year.

e.The interest rate on debt is 10% for short term debt and 9% for long term debt.

f.The tax rate is expected to be 25% each year.

g. The preferred dividend is 8% of the book value of the preferred stock.

Balance Sheet Assumptions

a.Cash is expected to be 1% of sales.

b.Accounts receivable is forecast to be 10% of sales.

c.Inventory is forecast to be 20% of sales.

d.Net fixed assets are forecast to be 40% of sales.

e.Accounts payable are expected to be 4% of sales.

f.Accruals are expected to be 6% of sales.

g.The target capital structure for the company is short term debt of 2%, long term debt of 28%, preferred stock of 3%, and common stock of 67%.(total capital = total assets (accounts payable and accruals))

2. Calculate the after tax free cash flow for the company for each year in the five-year forecast.

FCF = EBIT(1-t) + depreciation changes in Working Capital investment in fixed assets.

Changes in working capital equals AR + INV AP ACCRUALS for the current year minus AR + INV AP ACCRUALS for last year.

Investment in fixed assets is equal too ending fixed assets minus beginning fixed assets plus depreciation.