**New Perspectives **Excel 2013| Tutorial 9: SAM Project 1a

**New Perspectives **Excel 2013

Tutorial 9: SAM Project 1a

Wayside Driving School

EXPLORING FINANCIAL TOOLS AND FUNCTIONS

Project Goal

M Project Name

Project Goal

## PROJECT DESCRIPTION

Emma Patterson is the owner of *Wayside Driving School*, and wants to expand operations into neighboring towns. To do so, she would need a loan to cover the costs of additional classroom space, vehicles, and instructors. Emma has asked you to create a detailed analysis of various expansion and financing options, complete with loan amortization and depreciation schedules. She also wants you to create a five-year sales forecast and income statement.

## GETTING STARTED

· Download the following file from the SAM website:

· **NP_Excel2013_T9_P1a_ FirstLastName_1.xlsx**

· Open the file you just downloaded and save it with the name:

· **NP_Excel2013_T9_P1a_ FirstLastName_2.xlsx**

· *Hint: *If you do not see the **.xlsx **file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

· With the file **NP_Excel2013_T9_P1a_ 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

Go to the *Loan Analysis* worksheet. In cell **D12**, use the **PMT** function to calculate the monthly payment for a loan using the inputs listed under the *Add 1 Location* loan scenario in cells **D5**, **D7**, and **D9** (*Hint*: The result will be displayed as a negative number to reflect the negative cash flow of a loan payment).

In cell **E7**, enter a formula using the **RATE **function to calculate the monthly interest rate for a loan using the inputs listed under the *Add 2 Locations *loan scenario in cells **E9**, **E12**, and **E5** (*Hint: *Assume the present value of the loan is the loan amount shown in cell **E5**).

In cell **F5**, enter a formula using the **PV **function to calculate the loan amount using the inputs listed under the *Add 3 Locations* loan scenario in cells **F7**,** F9**, and **F12**.

In cell **G9**, enter a formula using the **NPER **function to calculate how many months it would take to pay back a $1 million loan using inputs listed under the *Expansion + BuyOut* loan scenario in cells **G7**,** G12**,** **and **G5.**

Go to the *Amortization* worksheet. In cell **C17**, enter a formula using the **CUMIPMT **function to calculate the cumulative interest paid on the loan after the first year (payments 1 through 12) when the payments are made at the **end** of the period (*Hint:* Use 0 as the type argument in your formula). Use absolute references for the **RATE**, **NPER**, and **PV** arguments and relative references for the **Start **and **End **arguments. Copy the formula from cell **C17 **to the range **D17:G17**.

In cell **H17**, use the **Error Checking **command to identify the error in the cell, then correct the error (*Hint: *The formula in the cell should calculate the total the values in **C17:G17** using the **SUM** function).

In cell **C18**, enter a formula using the **CUMPRINC **function to calculate the cumulative principal paid in the first year (payments 1 through 12) when the payments are made at the **end** of the period (*Hint:* Use 0 as the type argument in your formula). Use absolute references for the **RATE**, **NPER**, and **PV** arguments and relative references for the **Start **and **End **arguments. Copy the formula from cell **C18 **to the range **D18:G18**.

In cell **E23**, enter a formula that uses the **PPMT** function to determine the amount of loan payment number 1 devoted to the principal. Use absolute references for the **RATE**, **NPER**, and **PV** arguments and use cell **A23** as the period argument *(Hint: *Remember that the period used in the formula is based on a monthly payment schedule). Copy the formula from cell **E23 **to range **E24:E82**.

In cell **F23**, enter a formula that uses the **IPMT** function to determine the amount of loan payment number 1 devoted to the interest. Use absolute references for the **RATE**, **NPER**, and **PV** arguments and use cell **A23** as the period argument *(Hint: *Remember that the period used in the formula is based on a monthly payment schedule). Copy the formula from cell **F23** to range **F24:F82.**

Go to the *Depreciation* worksheet. In cell **C12**, enter a formula that uses the **SLN **function to calculate the straight-line depreciation for the new vehicle fleet during the first year of service, with the value in cell **D6** representing the expected *life *of the vehicle fleet. Use absolute references for the cost, salvage, and life arguments in the **SLN **formula. Copy the formula, without cell formatting, from cell **C12** to the range **D12:I12**.

In cell **C20**, enter a formula that uses the **DB **function to calculate the declining balance depreciation for the new vehicle fleet during the first year of service, with the value in cell **D6** representing the expected *life *of the vehicle fleet and the value in cell **C19** as the current *period*. Use absolute references for the cost, salvage and life arguments in the **DB **formula and a relative reference for the period argument. Copy the formula from cell **C20** to the range **D20:I20**.

Determine the error in cell **D21** by using the **Trace Precedent **and **Trace Dependent** arrows. The formula in cell **D21** should calculate the cumulative depreciation of the vehicle fleet by adding the Cumulative Depreciation value in year 1 to the Yearly Depreciation value in year 2. Correct the error in cell **D21**, copy the corrected formula in cell **D21** to the range **E21:I21**, and then remove any arrows from the worksheet.

Go to the *Income Statement* worksheet. Project the revenues associated with the *Classroom fees *category for 2018-2020 (cells **D5:F5**) using a **Growth Trend** **interpolation** (*Hint: *Remember to select the range **C5:G5 **before filling this series with values).

Project the revenues associated with the *Other* category for 2018-2020 (cells **D7:F7**) using a **Linear Trend** **interpolation** (*Hint: *Remember to select the range **C7:G7 **before filling this series with values).

Project the expenses associated with the *Payroll *category for 2018-2021 (cells **D11:G11**) using a **Growth trend extrapolation**, using a **step value **of **1.07**. (*Hint: *Remember that, when extrapolating values, the trend button in the *Series Dialog Box *should not be checked). Do not set a stop value for the series (*Hint: *Remember to select the range **C11:G11** before filling this series with values).

Go to the *Rate of Return* worksheet and complete the following actions.

a. In cell **E15**, enter a formula that uses the **NPV **function to calculate the *Present Value* of the *Add 1 Location *investment, using the value in cell **E14** as the desired rate of return and the range **D7:D12** as the return paid to investors (*Hint: *If it appears, ignore the *Formula Omits Adjacent Cell* error warning).

b. In cell **E16**, enter a formula that calculates the **Net Present Value **by adding the **Present Value** of the *Add 1 Location *investment (calculated in cell **E15**) to the value of the initial investment (in cell **D6**).

In cell **E17**, enter a formula that uses the **IRR **function to calculate the internal rate of return of the *Add 1 Location *investment, using the range **D6:D12** as the returns paid to the investors.

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

## Final Figure 1: Loan Analysis Worksheet

Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2014 Cengage Learning. All Rights Reserved.

## Final Figure 2: Amortization Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

**Final Figure 3: Depreciation Worksheet**

Copyright © 2014 Cengage Learning. All Rights Reserved.

**Final Figure 4: Income Statement Worksheet**

Copyright © 2014 Cengage Learning. All Rights Reserved.

** Final Figure 5: Rate of Return Worksheet**

**Copyright © 2014 Cengage Learning. All Rights Reserved.**