Data File needed for the Review Assignments: NP_EX_9-2.xlsx After consulting with financial analysts and accountants, Asli has some new figures for the Holoease business plan. The company can get slightly better conditions on the business loan, which means that the Holoease needs less money from investors to fund the company. Asli has also modified the depreciation schedule for the businesss tangible assets. Asli wants you to make the necessary changes in the workbook to calculate the companys financial data for the next five years. Complete the following: 1. Open the NP_EX_9-2.xlsx workbook located in the Excel9 > Review folder included with your Data Files. Save the workbook as NP_EX_9_Plan in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. In the Loan Scenarios worksheet, in cell B4, enter 5.75% as the annual interest rate that the company will secure for a business loan of $750,000. 4. Complete the calculations for the Loan Scenarios worksheet, which includes the constants you will need: In cell I7, use the PMT function to calculate the size of the quarterly payments for the $750,000 loan. Use cell H7 for the Rate argument, cell F7 for the Nper argument, and cell B7 for the Pv argument. In cell C8, use the FV function to calculate the future value of the loan assuming that quarterly payments are limited to $22,000. Use cell H8 for the Rate argument, cell F8 for Nper argument, cell I8 for the Pmt argument, and cell B8 for the Pv argument. In cell F9, use the NPER function to calculate the total number of payments required to repay a $750,000 loan with quarterly payments of $22,000. Use cell H9 for the Rate argument, cell I9 for the Pmt argument, cell B9 for the Pv argument, and cell C9 for the Fv argument. In cell D9, calculate the payback period in years by dividing the value in cell F9 by the value in cell E9. In cell B10, use the PV function to calculate the present value of the loan the company can afford if quarterly payments are limited to $22,000 over a 10-year period. Use cell H10 for the Rate argument, cell F10 for Nper argument, and cell I10 for the Pmt argument. 5. In the Startup Plan worksheet, in cell B25, enter 660,000 as the size of the loan that Holoease will take out to fund its startup costs. 6. Create an amortization schedule for the business loan in the Amortization Schedule worksheet. In cell G5, calculate the payment per quarter by using the PMT function with cell D5 as the Rate argument, cell F5 as the Nper argument, and cell A5 as the Pv argument. 7. Calculate the quarterly payments made to interest and principal: In cell D9, use the IPMT function to calculate the interest payment for the first quarter. In cell E9, use PPMT function to calculate the principal payment for the first quarter. Use cell $D$5 for the Rate argument, cell B9 for the Per argument, cell $F$5 for the Nper argument, and cell $A$5 for the Pv argument. AutoFill the formulas in the range D9:E9 to the range D10:E48 to calculate the payments for the remaining quarters. Fill the formulas without formatting. Verify that the loan is completely repaid by checking that the value in cell C49 is equal to $0.00. 8. Complete the Amortization Schedule worksheet by calculating the cumulative interest and principal payments per year: In cell B55, use the CUMPRINC function to calculate the principal paid for the first year. In cell B56, use the CUMIPMT function to calculate the interest paid for the first year. Use cell $D$5 used for the Rate argument, cell $F$5 for the Nper argument, cell $A$5 for the Pv argument, cell B53 for the Start argument, cell B54 for the End argument, and 0 for the Type argument. Copy the range B55:B56 and paste it in the range C55:F56. 9. Project future income statements under this new business plan. In the Profit and Loss worksheet, in the range C8:E8, project the companys 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 companys 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 companys taxes for each year. If the companys 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: In cell C25, enter 12% as the desired rate of return for the investors. 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. 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. 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.