50.1K

Verified Solution

Link Copied!

FIN 6150 Individual Assignment - Forecasting and DCF valuation 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 (2023-2027). 1. 2. Income Statement Assumptions a. Sales, for the next five years are expected to grow at rates of 10%, 8%, 7%, 5% and 5% respectively. COGS (excl. depr.) are expected to remain constant at 76% of sales. Depreciation is expected to be 10% of net fixed assets. b. C. d. e. f. Balance Sheet Assumptions a. b. Cash is expected to be 1% of sales. Accounts receivable is forecast to be 10% of sales. Inventory is forecast to be 20% of sales. Net fixed assets are forecast to be 40% of sales. Accounts payable are expected to be 4% of sales. Accruals are expected to be 6% of sales. 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) (total capital includes short term debt, long term debt, preferred stock, and common stock) 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. C. d. Financial Management e. f. Other operating expenses are expected to be 10% or sales each year. The interest rate on debt is 10% for short term debt and 9% for long term debt. The tax rate is expected to be 25% each year. The preferred dividend is 8% of the book value of the preferred stock. g. Investment in fixed assets is equal too ending fixed assets minus beginning fixed assets plus depreciation.

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) (total capital includes short term debt, long term debt, preferred stock, and common stock) Calculate the after tax free cash flow for the company for each year in the five-year forecast. FCF= EBIT (1t)+ depreciation - changes in Working Capital - investment in fixed assets. Changes in working capital equals AR+INVAP - 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. Forecasting and DCF Valuation Assignment

Answer & Explanation Solved by verified expert

See Answer