New Perspectives Excel 2019 | Module 9: End of Module Project 1 Canyon Transport   PERFORM FINANCIAL CALCULATIONS     GETTING STARTED Open the file NP_EX19_EOM9-1_FirstLastName_1

Subject:MS ExcelPrice:20.99 Bought13

Share With

New Perspectives Excel 2019 | Module 9: End of Module Project 1

Canyon Transport

 

PERFORM FINANCIAL CALCULATIONS

• GETTING STARTED

• Open the file NP_EX19_EOM9-1_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_EOM9-1_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_EOM9-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Elena Gonzalez works in the Operations Department of Canyon Transport, a company providing delivery and shuttle services in Flagstaff, Arizona. Canyon Transport is planning to offer shuttle services to the Phoenix Sky Harbor Airport, and Elena is using an Excel workbook to analyze the financial data for these expanded services. She asks for your help in making financial calculations in the workbook.
   Go to the Business Plan worksheet. In the range A2:B29, Elena has already entered expenses, assets, and other information for the new airport shuttle service. Now she needs to make financial calculations in the range E4:H11. In cell E11, enter a formula with the PMT function that uses the rate per quarter (cell E10), the total payments (cell E8), and the business loan amount (cell E4) to calculate the quarterly payment amount for a 10-year loan at a 5.6 percent annual interest rate.
2. In cell F5, enter a formula with the FV function that uses the rate per quarter (cell F10), the total payments (cell F8), and the quarterly payment amount (cell F11) to calculate the future value of the loan assuming the quarterly payments are limited to $15,000.
3. In cell G8, enter a formula with the NPER function that uses the rate per quarter (cell G10), the quarterly payment amount (cell G11), the amount of the business loan (cell G4), and the future value of the loan (cell G5) to calculate the total number of payments required to repay the $490,000 loan with quarterly payments of $15,000.
4. In cell H4, enter a formula with the PV function that uses the rate per quarter (cell H10), the total payments (cell H8), and the quarterly payment amount (cell H11) to calculate the present value of the loan Canyon Transport can afford if the quarterly payments are $15,000 over a 10-year period.
5. Now Elena asks you to calculate the annual principal and interest payments for the airport shuttle service expansion. Go to the Loan Details worksheet. In cell B9, enter a formula using the CUMPRINC function to calculate the cumulative principal paid for Year 1 (payment 1 in cell B7 through payment 4 in cell B8). Use 0 as the type argument in your formula because payments are made at the start of the period. Use absolute references for the rate, nper, and pv arguments, which are listed in the range A3:G3. Use relative references for the start and end arguments. Fill the range C9:F9 with the formula in cell B9 to calculate the principal paid in Years 2–5 and the total principal.
6. In cell B10, enter a formula using the CUMIPMT function to calculate the cumulative interest paid on the loan for Year 1 (payment 1 in cell B7 through payment 4 in cell B8). Use 0 as the type argument. Use absolute references for the rate, nper, and pv arguments, and use relative references for the start and end arguments. Fill the range C10:F10 with the formula in cell B10 to calculate the interest paid in Years 2–5 and the total interest.
7. Go to the Buy or Lease worksheet. Elena wants to compare the costs of buying a shuttle bus with the costs of leasing one. She has entered information for both scenarios in the range A2:B19 but needs to complete the table in columns D through G to track the depreciation of the shuttle bus value. In cell E4, enter a formula that subtracts the result of the DB function from the initial asset value (cell E3) to calculate the difference between the initial value of the shuttle bus and its depreciation during the first month of use. In the DB function, use absolute references for the cost, salvage, and life arguments, which are listed in the range B3:B5. Use a relative reference for the period argument (cell D4). Fill the range E5:E39 with the formula in cell E4, filling without formatting, to calculate the depreciated value for months 2–36.
8. Elena is ready to calculate the net present value of buying the shuttle bus. If it is greater than the cost of the net present value of leasing, cell B22 displays the recommendation "BUY"; otherwise, cell B22 displays the recommendation "LEASE". Elena has already calculated the initial investment amount (cell F3) as the current price, plus the cost of a service contract, plus the tax on the sale. In cell B20, enter a formula that adds the initial investment amount of buying the shuttle bus (cell F3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Buy Scenario values for months 1–36 and the ending value (range F4:F40) as the cash flows for owning and using the shuttle bus.
9. Next, calculate the net present value of leasing the shuttle bus. Elena has already entered the security deposit amount (cell G3) as the initial investment for leasing and the monthly payments in the range G4:G39. In cell B21, enter a formula that adds the security deposit amount (cell G3) to the result of the NPV function. In the NPV function, use the monthly discount rate (cell B19) as the rate of return and the Lease Scenario values for months 1–36 and the ending value (range G4:G40) as the cash flows for leasing the shuttle bus.
10. Elena also needs to compare straight-line depreciation amounts with declining balance depreciation amounts to determine which method is more favorable to the company's finances. Go to the Depreciation worksheet. In cell B9, enter a formula using the SLN function to calculate the straight-line depreciation for the new shuttle service during its first year of operation. Use absolute references for the cost, salvage, and life arguments, which are stored in the range B3:B5. Fill the range C9:F9 with the formula in cell B9 to calculate the annual and cumulative straight-line depreciation in Years 2–5.
11. In cell B15, enter a formula using the DB function to calculate the declining balance depreciation for the new shuttle service during its first year of operation. Use Year 1 (cell B14) as the current period. Use absolute references only for the cost, salvage, and life arguments. Fill the range C15:F15 with the formula in cell B15 to calculate the annual and cumulative declining balance depreciation in Years 2–5.
12. Go to the Profit & Loss worksheet. Elena has entered most of the income and expense data on the worksheet. She estimates revenue will be $825,000 in Year 1 and $1,400,000 in Year 5 of the shuttle service. She needs to calculate revenue for Years 2–4. Revenue should increase at a constant amount from year to year. Project the revenue for Years 2–4 (cells C7:E7) using a Linear Trend interpolation.
13. Elena also needs to calculate expenses for payroll and rent for Years 2–5. She knows the starting amount for each expense, and estimates the rent in Year 5 will be $64,000. She expects the payroll expenses to increase by at least 6 percent per year and the rent to increase by a constant rate. Project the expenses for Payroll in Years 2–5 (cells C13:F13) using a Growth Trend extrapolation. Use 1.06 (a 6 percent increase) as the step value. Project the expenses for Rent in Years 2–4 (cells C14:E14) using a Growth Trend interpolation.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.