Reference no: EM132732107
Question: Ryan has some new figures for the business plan for QR Shopper. He has received slightly better conditions on the business loan, which means that he needs less money from investors to fund the company. He has also modified the depreciation schedule for the company's tangible assets. Ryan wants you to make necessary changes in the workbook to calculate the company's financial data for the next five years. Complete the following:
1. Open the Business workbook located in the Excel9 > Review folder included with your Data Files,and then save the workbook as Business Plan in the location specied by your instructor.
2. In the Documentation worksheet, enter your name and the date.
3. In the Loan Scenarios worksheet, in cell B4, enter the 4.85% annual interest rate that the company has secured for a business loan.
4. Calculate the following possible loan scenarios:
a. In row 7, for a $425,000 business loan that is repaid in 10 years at a 4.85 percent interest rate with quarterly payments, use the PMT function in cell I7 to calculate the quarterly payments.
b. In row 8, for a $425,000 loan at a 4.85 percent interest rate with quarterly payments of $10,000 made over 10 years, use the FV function in cell C8 to calculate the principal at the end of 10 years.
c. In row 9, for a $425,000 loan at a 4.85 percent interest rate that is completely repaid with quarterly payments of $10,000, use the NPER function in cell F9 to calculate the number of quarterly payment periods and then in cell D9, calculate the number of years required to repay the loan.
d. In row 10, for quarterly payments of $12,000 for 10 years at a 4.85 percent interest rate, use the PV function in cell B10 to calculate the largest loan the company could completely repay in 10 years.
5. In the Startup Plan worksheet, in cell B25, enter 425,000 as the size of the loan that QR Shopper will take out to fund the startup costs of the business.
6. In the Amortization Schedule worksheet, in the range A5:F5, enter the conditions for a $425,000 loan at a 4.85 percent interest rate with quarterly payments to be repaid in 10 years. Reference the loan value from cell B25 in the Startup Plan worksheet. In cell G5, use the PMT function to calculate the amount of the quarterly payments required to repay the loan.
7. In the range C9:F48, complete the amortization schedule as follows:
a. Use absolute references to the loan conditions in row 5 of the worksheet for your formulas.
b. Use the PPMT function to calculate the principal payment for each quarter.
c. Use the IPMT function to calculate the interest payment for each quarter.
d. Reduce the principal owed for each new quarter by the amount paid in the previous quarter.
e. Verify that the loan is completely repaid by displaying the value of the remaining principal in cell C49.
8. Calculate the cumulative interest and principal payments per year as follows:
a. In the range B55:F55, use the CUMPRINC function to calculate the cumulative principal payments in each of the rst ve years of the loan. Include absolute references to the loan conditions in row 5 as part of your calculations.
b. In the range B56:F56, use the CUMIPMT function to calculate the cumulative interest payments in each of the rst ve years of the loan.
c. In cells G55 and G56, calculate the total principal payments and interest payments in the rst ve years of the loan.
d. In the range B57:F57, calculate the remaining principal at the end of each of the rst ve years of the loan.
9. In the Prot and Loss worksheet, in the range C8:E8, project the company's income and expenses for the next ve years by interpolating the Year 2 through Year 4 revenue assuming a growth trend.
10. In cell F3, enter 20% as the percent cost of marketing and in cell F4, enter 10% as the percent cost of R&D. In the range B9:F10, use those percentages to calculate the Year 1 through Year 5 cost of sales. In the range B11:F11, calculate the gross prot by subtracting the cost of market and R & D from the annual projected revenue.
11. In the range C14:F14, extrapolate the Year 2 through Year 5 payroll expenses by assuming the payroll grows by 12 percent per year. In C15:F17, extrapolate the other expenses by assuming they grow by 5 percent per year from the initial Year 1 values. In the range C18:F18, calculate the total expenses for Year 2 through Year 5.
12. In the range B21:F21, calculate the company's initial earnings for each year, equal to the gross prot minus the total general expenses.
13. In the Startup Plan worksheet, in cell B12, enter 225,000 as the long-term tangible assets that will need to be depreciated.
14. In the Depreciation worksheet, in cell B4, reference the long-term assets' value from cell B12 in the Startup Plan worksheet. In cell B5, enter 35,000 as the assets' salvage value. In cell B6, enter 15 as the useful lifetime of the assets.
15. In the range B10:F10, calculate the yearly straight-line depreciation of the long-term assets using the SLN function. In the range B11:F11, calculate the cumulative depreciation through the rst ve years. In the range B12:F12, calculate the depreciated value of the assets at the end of each of the rst ve years.
16. In the range B16:F16, use the DB function to calculate the yearly declining balance of the assets. In the range B17:F17, calculate the cumulative depreciation of the assets. In the range B18:F18, calculate the depreciated value of the assets at the end of each year.
17. 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. Calculate the company's operating prot in B23:F23 range by subtracting the yearly depreciation from the yearly initial earnings.
18. In the range B25:F25, enter formulas for the yearly interest expenses that 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 value. In the range B26:F26, calculate the company's pretax prot by subtracting the interest expenses from the operating prot.
19. In cell F5, enter 33% as the assumed tax rate. In the range B28:F28, use an IF function to calculate the company's tax liability for each of the rst ve years assuming the tax rate in cell F5. If the company's pretax prot is negative, set the tax burden to $0; otherwise, multiply the assumed tax rate by the pretax prot. In the range B29:F29, calculate the company's after-tax prot by subtracting the taxes owed from the pretax prot.
20. In the Startup Plan worksheet, in cell B30, enter $160,000 as the amount the company hopes to attract from investors.
21. In the Investment worksheet, in cell B6, enter a reference to cell B30 in the Startup Plan worksheet as a negative cash -ow. In cells B7 and B8, enter values to show that the company repay investors $35,000 per year for 5 years. In cell B9, use the RATE function to calculate the interest of the proposed repayment schedule.
22. In the range B12:F13, enter the annual payment and dividend schedule using cell references to cell B7 to enter the yearly payments and dividends of $4,000 in Year 2 and Year 3 and $15,000 in Year 4 and Year 5. In the range B14:F14, calculate the total sum paid to the investors each year
23. In the range B18:C23, determine the payback period, and calculate the net cash -ow to the investors.
24. 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 Year 1 through Year 5 payments from the range B19:B23 using the desired rate of return specied in cell C25. In cell C27, calculate the net present value of their investment in the company by adding the startup payment to the present value of the Year 1 through Year 5 repayments.
25. In cell B28, use the IRR function to calculate the internal rate of return of their investment.
26. In the Profit and Loss worksheet, in the range B31:F31, enter references to the yearly dividend values paid to the shareholders as specied in the range B13:F13 of the Investment worksheet. In the range B33:F33, calculate the company's retained earnings by subtracting the dividends from the after-tax profit.
Attachment:- CS_3Business.rar