Please find the financial information in this Excel file.

Using the Excel file, please calculate the following:

Create balance sheet and income statement forecasts for the next 2 years using the following assumptions:

1. Sales growth of 5% for the first year and 7% for the second year
2. Cost of sales and expenses will be the exact same proportion of sales as for the last year provided in the financials
3. Depreciation is 5% of net plant and equipment
4. Net interest expense is 3% of total debt
5. Income taxes are 20% of pre-tax income
6. Dividends paid during the year are 10% of net income of the same year
7. Accounts receivable; inventories; short-term debt; accounts payable are the same proportion of sales as for the last actual year provided in the financials
8. Other long term assets and/or other long term liabilities do not change
9. Net plant and equipment remain the same (i.e. investments in plant and equipment equal to annual depreciation)
10. Long term debt changes the same amount as net plant and equipment.
11. Cash is a pluggable value (plug in to balance the balance sheet)

What is net sales in forecasted year 1? 

What is net sales in forecasted year 2? 

What is net income in forecasted year 1? 

What is net income in forecasted year 2? 

What is inventories in forecasted year 1? 

What is inventories in forecasted year 2? 

What is total assets in forecasted year 1? 

What is total shareholder's equity in forecasted year 1? 

What is total shareholder's equity in forecasted year 2? 

Please find the financial information in this Excel file.

Using the Excel file, please calculate the following:

Create balance sheet and income statement forecasts for the next 2 years using the following assumptions:

1. Sales growth of 5% for the first year and 7% for the second year
2. Cost of sales and expenses will be the exact same proportion of sales as for the last year provided in the financials
3. Depreciation is 5% of net plant and equipment
4. Net interest expense is 3% of total debt
5. Income taxes are 20% of pre-tax income
6. Dividends paid during the year are 10% of net income of the same year
7. Accounts receivable; inventories; short-term debt; accounts payable are the same proportion of sales as for the last actual year provided in the financials
8. Other long term assets and/or other long term liabilities do not change
9. Net plant and equipment remain the same (i.e. investments in plant and equipment equal to annual depreciation)
10. Long term debt changes the same amount as net plant and equipment.
11. Cash is a pluggable value (plug in to balance the balance sheet)

What is net sales in forecasted year 1? 

What is net sales in forecasted year 2? 

What is net income in forecasted year 1? 

What is net income in forecasted year 2? 

What is inventories in forecasted year 1? 

What is inventories in forecasted year 2? 

Part 2: FCFF calculation and forecasts:

• Make FCFF forecasts for years 1-7.
• For years 1-2 calculate FCFF based on previously forecasted income statement and balance sheet.
• For years 3-7 assume that FCFF grows 3% annually

What is FCFF for the last year provided in the financials? 

What is FCFF in forecasted year 1? 

What is FCFF in forecasted year 7? 

Use the following cost of capital:

1. Cost of equity: 9,6%
2. WACC: 6,6%

Part 3: Equity valuation

Find the value of equity using the forecasted 7 years of FCFF. Do not consider terminal value but calculate discounted free cash flow only based on the forecasted 7 years. 

What is the value of the equity?