question archive 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

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_**, available for download from the SAM website.*FirstLastName*_1.xlsx - Save the file as
**NP_EX19_EOM9-1_**by changing the “1” to a “2”.*FirstLastName*_2.xlsx- 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.

- If you do not see the
- With the file
**NP_EX19_EOM9-1_**still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.*FirstLastName*_2.xlsx- If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

**PROJECT STEPS**

- 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. - 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. - 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. - 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. - 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. - 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. - 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. - 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. - 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. - 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. - 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. - 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. - 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.

Purchased 13 times