9. Project future income statements under this new business plan. In the Profit and Loss worksheet,...

Transcribed Image Text:

9. Project future income statements under this new business plan. In the Profit and Loss worksheet, in the range C8:E8, project the company's revenue for the next five years by interpolating the Year 2 through Year 4 revenue assuming a growth trend. The costs of marketing and R&D as well as gross profit will be calculated for you. 10. In the range C14:F14, extrapolate the Year 2 through Year 5 payroll expenses by assuming the payroll will grow by 12% per year. In C15:F17, extrapolate the other expenses by assuming they grow by 5% per year from the initial Year 1 values. The range C18:F18 automatically calculates the total expenses for Year 2 through Year 5. 11. Calculate depreciation of the company's tangible assets. In the Startup Plan worksheet, in cell B12, enter 350,000 as current value of the long-term tangible assets. 12. Asli estimates that the long-term tangible assets will depreciate to a salvage value of $50,000 in 15 years. In the Depreciation worksheet, in the range B10:F10, calculate the yearly straight-line depreciation of the long-term assets using the SLN function with absolute references to the Cost, Salvage, and Life values in the range B4:B6. 13. In the range B16:F16, use the DB function to calculate the yearly declining balance of the assets using absolute references to the Cost, Salvage, and Life values in the range B4:B6 and relative references to the Period values in the cells B15 through F15. 14. In the Profit and Loss worksheet, in the range B22:F22, enter formulas to reference the declining balance depreciation values in the range B16:F16 of the Depreciation worksheet. 15. In the range B25:F25, enter formulas to reference the cumulative interest payments in the range B56:F56 of the Amortization Schedule worksheet. Enter the interest expenses as positive values by changing the sign of the interest value. 16. In the range B28:F28, use an IF function to calculate the company's taxes for each year. If the company's pretax profit in row 26 is negative, set the tax to 0; otherwise, multiply the assumed tax rate in cell F5 by the pretax profit. 17. Calculate the value of the company to potential investors. In the Startup Plan worksheet, in cell B30, enter $250,000 as the amount the company hopes to attract from investors. 18. Calculate the rate of return if the investor group is paid $55,000 per year for the next five years on the $250,000 investment. In the Investment worksheet, in cell B9, use the RATE function to calculate the interest of the proposed repayment schedule using the corresponding values in the range B6:B8. 19. The company will also offer investors dividends on their investment. In cell B13, enter $0 for the Year 1 dividend. In cells C13 and D13, enter $6,000 as the Year 2 and Year 3 dividends. In cells E13 and F13, enter $25,000 for the Year 4 and Year 5 dividends. 20. In the range C18:C23, calculate the payback period of the investment by calculating a running total of the values in the range B18:B23. 21. Determine the profitability of the investment to the investors: a. In cell C25, enter 12% as the desired rate of return for the investors. b. In cell C26, use the NPV function to calculate the present value of the investment using the desired rate of return in cell C25 and the payments in the range B19:B23. c. In cell C27, calculate the net present value by adding to the cost of the initial investment in cell B18 to the present value in cell C26. d. In cell C28, use the IRR function to calculate the internal rate of return for this investment using the cash flow values in the range B18:B23. 22. In the Profit and Loss worksheet, in the range B31:F31, enter formulas to reference the yearly dividend values paid to the shareholders in the range B13:F13 of the Investment worksheet. 23. An error is somewhere in the workbook. Starting with cell F18 in the Balance sheet, trace the #REF error in the workbook back to its source, and correct it. 24. Save the workbook, and then close it. 9. Project future income statements under this new business plan. In the Profit and Loss worksheet, in the range C8:E8, project the company's revenue for the next five years by interpolating the Year 2 through Year 4 revenue assuming a growth trend. The costs of marketing and R&D as well as gross profit will be calculated for you. 10. In the range C14:F14, extrapolate the Year 2 through Year 5 payroll expenses by assuming the payroll will grow by 12% per year. In C15:F17, extrapolate the other expenses by assuming they grow by 5% per year from the initial Year 1 values. The range C18:F18 automatically calculates the total expenses for Year 2 through Year 5. 11. Calculate depreciation of the company's tangible assets. In the Startup Plan worksheet, in cell B12, enter 350,000 as current value of the long-term tangible assets. 12. Asli estimates that the long-term tangible assets will depreciate to a salvage value of $50,000 in 15 years. In the Depreciation worksheet, in the range B10:F10, calculate the yearly straight-line depreciation of the long-term assets using the SLN function with absolute references to the Cost, Salvage, and Life values in the range B4:B6. 13. In the range B16:F16, use the DB function to calculate the yearly declining balance of the assets using absolute references to the Cost, Salvage, and Life values in the range B4:B6 and relative references to the Period values in the cells B15 through F15. 14. In the Profit and Loss worksheet, in the range B22:F22, enter formulas to reference the declining balance depreciation values in the range B16:F16 of the Depreciation worksheet. 15. In the range B25:F25, enter formulas to reference the cumulative interest payments in the range B56:F56 of the Amortization Schedule worksheet. Enter the interest expenses as positive values by changing the sign of the interest value. 16. In the range B28:F28, use an IF function to calculate the company's taxes for each year. If the company's pretax profit in row 26 is negative, set the tax to 0; otherwise, multiply the assumed tax rate in cell F5 by the pretax profit. 17. Calculate the value of the company to potential investors. In the Startup Plan worksheet, in cell B30, enter $250,000 as the amount the company hopes to attract from investors. 18. Calculate the rate of return if the investor group is paid $55,000 per year for the next five years on the $250,000 investment. In the Investment worksheet, in cell B9, use the RATE function to calculate the interest of the proposed repayment schedule using the corresponding values in the range B6:B8. 19. The company will also offer investors dividends on their investment. In cell B13, enter $0 for the Year 1 dividend. In cells C13 and D13, enter $6,000 as the Year 2 and Year 3 dividends. In cells E13 and F13, enter $25,000 for the Year 4 and Year 5 dividends. 20. In the range C18:C23, calculate the payback period of the investment by calculating a running total of the values in the range B18:B23. 21. Determine the profitability of the investment to the investors: a. In cell C25, enter 12% as the desired rate of return for the investors. b. In cell C26, use the NPV function to calculate the present value of the investment using the desired rate of return in cell C25 and the payments in the range B19:B23. c. In cell C27, calculate the net present value by adding to the cost of the initial investment in cell B18 to the present value in cell C26. d. In cell C28, use the IRR function to calculate the internal rate of return for this investment using the cash flow values in the range B18:B23. 22. In the Profit and Loss worksheet, in the range B31:F31, enter formulas to reference the yearly dividend values paid to the shareholders in the range B13:F13 of the Investment worksheet. 23. An error is somewhere in the workbook. Starting with cell F18 in the Balance sheet, trace the #REF error in the workbook back to its source, and correct it. 24. Save the workbook, and then close it.