Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / New Perspectives Excel 2013 Tutorial 9: SAM Project 1a Kush Patel 1
New Perspectives Excel 2013 Tutorial 9: SAM Project 1a
Kush Patel
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 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).
3.
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.
4.
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.
5.
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 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 Start and End arguments. Copy the formula from cell C17 to
the range D17:G17.
6.
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).
7. 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
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 Start and
End arguments. Copy the formula from cell C18 to 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
E23 to 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 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.
11.
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.
12.
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.
13.
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).
14. 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).
15.
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).
16. 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).
17.
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.
Already member? Sign In
