9. Project future income statements under this new business plan. In the Profit and Loss worksheet,...
Fantastic news! We've Found the answer you've been seeking!
Question:
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.
Expert Answer:
Related Book For
Posted Date:
Students also viewed these accounting questions
-
To run as candidate for president, you have to be: A ) liked by everyone. B ) good looking c ) smart d ) a U . S . citizen
-
As outlined in the business plan, the directors of Toy Warehouse Pty Ltd signed a three-year lease at $5,000 per month (GST-exclusive commencing 1 August 2020) for the premises at 11 Elland Road,...
-
Question: Old MathJax webview Old MathJax webview i need ans of these question but the source is alot Old MathJax webviewOld MathJax webview i need ans of these question but the source is alot these...
-
Write a detailed executive summary about the appropriation and advancement of Information and Communication Technology (ICT)
-
A 1.345-g sample of a compound of barium and oxygen was dissolved in hydrochloric acid to give a solution of barium ion, which was then precipitated with an excess of potassium chromate to give 2.012...
-
Legitron Corporation has $350 million of debt outstanding at an interest rate of 9 percent. What is the dollar value of the tax shield on that debt, just for this year, if Legitron is subject to a 35...
-
A graph with four vertices is isomorphic to a graph with five vertices. Determine whether each statement is always true, sometimes true, or never true.
-
Determining materials price and usage variances Gwen's Florals produced a special Mother's Day arrangement that included six roses. The standard and actual costs of the roses used in each arrangement...
-
determine the point(s) on the curve defined by the function f(x)=2^x(4-x) where the tangents are horizontal to 1 dp.
-
In a half-wave rectifier, the load current flows for more than half cycle but less than complete cycle of the input signal O less than half cycle of the input signal O either positive half cycle or...
-
Acme Company s production budget for August is 1 8 , 9 0 0 units and includes the following component unit costs: direct materials, $ 7 . 2 0 ; direct labor, $ 1 1 . 4 0 ; variable overhead, $ 5 . 4...
-
How do you figure out the FUTA Taxes payable for the employer? $ 5 7 . 8 4 How do you figure out the SUTA Taxes payable for the Employer? $ 3 5 5 . 6 1
-
(a) Suppose h is a function with a continuous second derivative that satisfies h(3) = -5, h(8) = 3, h'(3) = 1, h'(8) = 0, h"(3) = -5, h" (8) = 4. Evaluate the following definite integral, where a and...
-
Tesla management are trying to decide whether to keep an older piece of machinery or buy a replacement. Management was presented with the following information to assist in their decision: The old...
-
Explain the concept of continuous improvement in product and service design.
-
A, B and C were partners with capital balances on January 1, 2016 of P300,000, P200,000 and P100,000, respectively. On July 1, 2016, A retires from the partnership. On the date of retirement, the...
-
State whether each of the following will increase or decrease the power of a one-way between-subjects ANOVA. (a) The effect size increases. (b) Mean square error decreases. (c) Mean square between...
-
Fitch and Wall have been in partnership for many years sharing profits and losses in the ratio 5 : 3 respectively. The following was their balance sheet as at 31 December 2002. On 1 January 2003,...
-
The Unilever 2002 annual review stated: Total Shareholder Return (TSR) is a concept used to compare the performance of different companies stocks and shares over time. It combines share price...
-
On the Internet find the 2010 Transparency International Corruption Perception Index and determine the ranking of these countries. a. United States b. Japan c. Chad d. Iceland e. Denmark
Study smarter with the SolutionInn App