ONLY QUESTION 10! use excel and show the formulas used

E B D BALANCE SHEET AND INCOME STATEMENT Liabilities and Equity Current liabilities 10,000 Accounts payable 1,500 Total current liabilities 11,500 Long-term liabilities Debt 2,000 2,000 10,000 30,000 -14,000 16,000 Equity Equity Accumulated retained earnings 10,500 5,000 1 2 Assets 3 Current assets 4 Cash 5 Prepaid expenses 6 Total current assets 7 8 9 Fixed assets 10 At cost 11 Accumulate depreciation 12 Net Fixed assets 13 14 Total assets 15 16 Income statement 17 Sales 18 Cost of Goods Sold (COGS) 19 Depreciation 20 Interest on cash 21 Interest payments on debt 22 Profit before tax 23 Tax (40%) 24 Profit after tax 25 Dividend 26 Retained Earnings 27,500 Total liabilities and equity 27,500 20,000 -12.000 -2.000 300 -400 5,900 -2,360 3,540 -708 2,832) . a. You believe that these financial statements are representative of the firm's value drivers (For example, if the sales are $20,000 and the COGS are $12,000, then the COGS to sales param- eter is 60%). Find and calculate the value drivers that can be derived from the firm's balance sheet and P&L statement. b. Use the following data to project the financial statements for year 1: The sales growth is 12%. The depreciation is 10% of the average of the fixed assets value during the year. The interest rate earned on cash is 5% on the average cash balances. The debt payments are $2,000 each year. The interest rate on debt is 8%. c. Show in a graph the change in the firm's profit relative to the change in the COGS. . . . 10. a. Extend the project of the previous exercise to 6 years. b. The model we are dealing with doesn't contain advertising and marketing costs (these are usually a part of the SG&A). Assume that these costs are $800 in year 0 and 5% of sales in years 1-6. Furthermore, assume that the firm has to pay each year for its license a fixed cost of $1,500. Adjust the model in Exercise 10a to these new assumptions. honon in the license fee E B D BALANCE SHEET AND INCOME STATEMENT Liabilities and Equity Current liabilities 10,000 Accounts payable 1,500 Total current liabilities 11,500 Long-term liabilities Debt 2,000 2,000 10,000 30,000 -14,000 16,000 Equity Equity Accumulated retained earnings 10,500 5,000 1 2 Assets 3 Current assets 4 Cash 5 Prepaid expenses 6 Total current assets 7 8 9 Fixed assets 10 At cost 11 Accumulate depreciation 12 Net Fixed assets 13 14 Total assets 15 16 Income statement 17 Sales 18 Cost of Goods Sold (COGS) 19 Depreciation 20 Interest on cash 21 Interest payments on debt 22 Profit before tax 23 Tax (40%) 24 Profit after tax 25 Dividend 26 Retained Earnings 27,500 Total liabilities and equity 27,500 20,000 -12.000 -2.000 300 -400 5,900 -2,360 3,540 -708 2,832) . a. You believe that these financial statements are representative of the firm's value drivers (For example, if the sales are $20,000 and the COGS are $12,000, then the COGS to sales param- eter is 60%). Find and calculate the value drivers that can be derived from the firm's balance sheet and P&L statement. b. Use the following data to project the financial statements for year 1: The sales growth is 12%. The depreciation is 10% of the average of the fixed assets value during the year. The interest rate earned on cash is 5% on the average cash balances. The debt payments are $2,000 each year. The interest rate on debt is 8%. c. Show in a graph the change in the firm's profit relative to the change in the COGS. . . . 10. a. Extend the project of the previous exercise to 6 years. b. The model we are dealing with doesn't contain advertising and marketing costs (these are usually a part of the SG&A). Assume that these costs are $800 in year 0 and 5% of sales in years 1-6. Furthermore, assume that the firm has to pay each year for its license a fixed cost of $1,500. Adjust the model in Exercise 10a to these new assumptions. honon in the license fee