Excel Chapter-9

NewPerspectivesExcel 2013 Tutorial9: SAM Project 1a WaysideDriving School EXPLORING FINANCIAL TOOLS AND FUNCTIONS PROJECT DESCRIPTION Emma Patterson isthe 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: o NP_Excel2013_T9_P1a_FirstLastName_1.xlsx · Open the file you just downloaded and save it with the name: o NP_Excel2013_T9_P1a_FirstLastName_2.xlsx o Hint:If you do not see the .xlsxfile extensionin 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.xlsxstill 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. 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). 2. In cell E7, enter a formula using the RATEfunction to calculate the monthly interest rate for a loan using the inputs listed under the Add 2 Locationsloan scenario in cells E9, E12, and E5(Hint:Assume the present value of the loan is the loan amount shown in cell E5). 3. In cell F5, enter a formula using the PVfunction to calculate the loan amount using the inputs listed under the Add 3 Locations loan scenario in cells F7, F9, and F12. 4. In cell G9, enter a formula using the NPERfunction 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. 5. Go to the Amortization worksheet. In cell C17, enter a formula using the CUMIPMTfunction
to calculate the cumulative interest paid on the loan after the first
year (payments 1 through 12) when the payments are made at the start 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 Startand Endarguments. Copy the formula from cell C17to the range D17:G17. 6. In cell H17, use the Error Checkingcommand to identify the error in the cell, then correct the error (Hint:The formula in the cell should calculate the total the values inC17:G17 using the SUM function). 7. In cell C18, enter a formula using the CUMPRINCfunction
to calculate the cumulative principal paid in the first year (payments
1 through 12) when the payments are made at the start 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 Startand Endarguments. Copy the formula from cell C18to the range D18:G18. 8. 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 E23to range E24:E82. 9. In cell F23, enter a formula that uses the IPMT 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 F23 to range F24:F82. 10. Go to the Depreciation worksheet. In cell C12, enter a formula that uses the SLNfunction
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 SLNformula.Copy the formula, without cell formatting, from cell C12 to the range D12:I12. 11. In cell C20, enter a formula that uses the DBfunction
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 DBformula and a relative reference for the period argument.Copy the formulafrom cell C20 to the range D20:I20. 12. Determine the error in cell D21 by using the Trace Precedentand Trace Dependentarrows. 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. 13. Go to the Income Statement worksheet. Project the revenues associated with the Classroom feescategory for 2018-2020 (cells D5:F5) using a Growth Trendinterpolation(Hint:Remember to select the range C5:G5before filling this series with values). 14. Project the revenues associated with the Other category for 2018-2020 (cells D7:F7) using a Linear Trendinterpolation (Hint: Remember to select the range C7:G7before filling this series with values). 15. Project the expenses associated with the Payrollcategory for 2018-2021 (cells D11:G11) using a Growth trend extrapolation, using a step valueof 1.07. (Hint:Remember that, when extrapolating values, the trend button in the Series Dialog Boxshould 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). 16. Go to the Rate of Return worksheet and complete the following actions. a. In cell E15, enter a formula that uses the NPVfunction to calculate the Present Value of the Add 1 Locationinvestment, using the value in cell E14 as the desired rate of return and the range D7:D12as 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 Valueby adding the Present Value of the Add 1 Locationinvestment (calculated in cell E15) to the value of the initial investment (in cell D6). 17. In cell E17, enter a formula that uses the IRRfunction to calculate the internal rate of return of the Add 1 Locationinvestment, 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.