Please solve in Excel

1 Assumptions for Forecasting Model Assumption 4 Income Statement 5 Sales 6 Cost of Sales 7 Selling, General& Admn. Expenses 8 Depreciation 9 Other Net (Income yExpenses 10 Interest Income 11 Interest Expense 12 13 Income Taxes 50% of sales 29% of sales 8% of Gross PP&E 0.7% of sales (To be deducted while calculating EBI Calculate based on average balance and interest rate specified later For STD calculate based on average balance and interest rate For LTD interest expense will remain unchanged at $13.5 million 35% of pre-tax income 15 Balance Sheet 16 Assets 17 Cash and Marketable Securities 18 Accounts Recervable 19 Inventories 20 Other Current Assets 21 Property, Plant and Equipment, Gross 22 Accumulated Depreciation 23 Property, Plant and Equipment, Net 24 Other Non-Current Assets 25 26 Liabilities and Shareholders' Equity 27 Accounts Payable 28 Short-Term Debt 29 Other Current Liabilities 30 Long-Term Debt 31 Deferred Income Taxes 32 Other Non-Current Liabilties 33 Paid-In Capital 34 Retained Eamings 35 36 Other Assumptions 37 Dividend payout ratio 38 Interest on short-term debt 39 Interest on long-term debt 40 Interest on cash & marketable securities 2.1%, of sales 8.4% of sales 8.8% of sales 7.6% of sales Will grow at 8% per year Calculated from other items Calculated from other items Will grow at 8% per year 6.1% of sales Use to balance the balance sheet (plug) 8.3% of sales Will remain unchanged 1.4% of sales 7.6% of sales Will remain unchanged Calculated from other items 40% 796, based on economic forecast Rates embedded in existing debt, annual expense $13.5 million 6%, based on economic forecast 1 Income Statement and Balance Sheet for Vitex Corp. 3 Income Statement ($ Milli 4.4 10 2 Oher 13 14 20 Net Inc 12 25 Balance Sheet ( Milli 26 Assets 19 Inventorie 30 35 40 Total 42 44 Short-Term Deb 45 51 53 Paid In Ca 54 Retained 55 Toal Starebolde 5 Total Liabilities and Shareholders 1 Assumptions for Forecasting Model Assumption 4 Income Statement 5 Sales 6 Cost of Sales 7 Selling, General& Admn. Expenses 8 Depreciation 9 Other Net (Income yExpenses 10 Interest Income 11 Interest Expense 12 13 Income Taxes 50% of sales 29% of sales 8% of Gross PP&E 0.7% of sales (To be deducted while calculating EBI Calculate based on average balance and interest rate specified later For STD calculate based on average balance and interest rate For LTD interest expense will remain unchanged at $13.5 million 35% of pre-tax income 15 Balance Sheet 16 Assets 17 Cash and Marketable Securities 18 Accounts Recervable 19 Inventories 20 Other Current Assets 21 Property, Plant and Equipment, Gross 22 Accumulated Depreciation 23 Property, Plant and Equipment, Net 24 Other Non-Current Assets 25 26 Liabilities and Shareholders' Equity 27 Accounts Payable 28 Short-Term Debt 29 Other Current Liabilities 30 Long-Term Debt 31 Deferred Income Taxes 32 Other Non-Current Liabilties 33 Paid-In Capital 34 Retained Eamings 35 36 Other Assumptions 37 Dividend payout ratio 38 Interest on short-term debt 39 Interest on long-term debt 40 Interest on cash & marketable securities 2.1%, of sales 8.4% of sales 8.8% of sales 7.6% of sales Will grow at 8% per year Calculated from other items Calculated from other items Will grow at 8% per year 6.1% of sales Use to balance the balance sheet (plug) 8.3% of sales Will remain unchanged 1.4% of sales 7.6% of sales Will remain unchanged Calculated from other items 40% 796, based on economic forecast Rates embedded in existing debt, annual expense $13.5 million 6%, based on economic forecast 1 Income Statement and Balance Sheet for Vitex Corp. 3 Income Statement ($ Milli 4.4 10 2 Oher 13 14 20 Net Inc 12 25 Balance Sheet ( Milli 26 Assets 19 Inventorie 30 35 40 Total 42 44 Short-Term Deb 45 51 53 Paid In Ca 54 Retained 55 Toal Starebolde 5 Total Liabilities and Shareholders