Expert answer:MS Excel project
Expert answer:
instructions_sc_ex16_4a.docx
sc_ex16_4a_igormbiso_1.xlsx
Unformatted Attachment Preview
Shelly Cashman Excel 2016 | Module 4: SAM Project 1a
Camp Millowski
FINANCIAL FUNCTIONS, DATA TABLES, AND AMORTIZATION SCHEDULES
GETTING STARTED
•
Open the file SC_EX16_4a_FirstLastName_1.xlsx, available for download
from the SAM website.
•
Save the file as SC_EX16_4a_FirstLastName_2.xlsx by changing the “1” to
a “2”.
o
•
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 SC_EX16_4a_FirstLastName_2.xlsx still open, ensure that
your first and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 does not display your name, delete the file and download a new
copy from the SAM website.
PROJECT STEP
1.
Justin and Kaleen Millowski have always dreamed of purchasing and running a
campground. Kaleen wants to be ready when a campground becomes available,
so she decides to start calculating how a mortgage will impact her family’s
budget on a monthly basis and over the life of the loan. She also wants to
consider how different mortgage interest rates will impact the total cost of the
campground.
Switch to the Campground Mortgage worksheet.
In cell D5, create a formula using the PMT function to determine the monthly
payments for the anticipated Campground mortgage, using the defined names
Rate, Term_Years, and Loan_Amount as the rate, nper, and pv arguments
in the formula.
2.
a.
Put a negative sign before the PMT function to make the formula return
a positive value.
b.
In the function, Rate should be divided by 12 to calculate the monthly
interest rate, and Term_Years should be multiplied by 12 to calculate
the total number of monthly payments.
Kaleen calculated the anticipated total cost of the campground using the
mortgage interest rate she expects to qualify for. She now wants to determine
how different interest rates could impact the total cost of the campground.
Select the range A12:A26 and fill it with a percent series based on the values in
range A12:A13. These values are the interest rates that Kaleen will analyze in
the Varying Interest Rate Schedule.
Shelly Cashman Excel 2016 | Module 4: SAM Project 1a
3.
4.
Create a single variable data table to determine the impact that the variable
interest rates (in the range A12:A22) will have on the total cost of the
campground.
a.
In cell B11, create a formula without using a function that references cell
D5 (the monthly payments).
b.
In cell C11, create a formula without using a function that references cell
D6 (the total interest paid on the loan).
c.
In cell D11, create a formula without using a function that references cell
D7 (the total cost of the mortgage).
d.
Select the range A11:D26 and create a single-variable data table, using
an absolute reference to cell D3 (the mortgage interest rate) as the
Column input cell.
To help Kaleen identify how each rate in her Variable Interest Rate Schedule
compares to the interest rate she anticipates on her mortgage, she decides to
highlight the matching interest rate in the schedule with a conditional
formatting rule.
Apply a Highlight Cells conditional formatting rule to the range A12:A26 that
formats any cell in the range that is equal to the value in cell D3 (using an
absolute reference to cell D3) with Green Fill with Dark Green Text.
5.
Kaleen now wishes to finalize the Amortization schedule.
In cell J4, create a formula without using a function that subtracts the value in
cell I4 from the value in cell H4 to determine how much of the mortgage
principal is being paid off each year.
Copy the formula in cell J4 to the range J5:J18.
6.
In cell K4, create a formula using the IF function to calculate the interest paid
on the mortgage (or the difference between the total payments made each year
and the total amount of mortgage principal paid each year).
a.
The formula should first check if the value in cell H4 (the balance
remaining on the loan each year) is greater than 0.
b.
If the value in cell H4 is greater than 0, the formula should return the
value in J4 subtracted from the value in cell D5 multiplied by 12. Use
a relative cell reference to cell J4 and an absolute cell reference to cell
D5. (Hint: Use 12*$D$5-J4 as the is_true argument value in the formula.)
c.
If the value in cell H4 is not greater than 0, the formula should return a
value of 0.
Copy the formula from cell K4 into the range K5:K18.
7.
Apply the Accounting number format with two decimal places and $ as the
symbol to the range K4:K18.
8.
In cell K20, create a formula without using a function that references the
defined name Down_Payment.
9.
Kaleen decides to add custom cell borders to the amortization schedule to make
it easier to read.
Shelly Cashman Excel 2016 | Module 4: SAM Project 1a
Apply custom cell borders with a Green, Accent 6, Darker 50% (10th column,
6th row in the Theme Colors palette) line color as described below:
10.
a.
Add an Outline border with a Medium border style (2nd column, 5th row)
to the range G3:K21.
b.
Add a Vertical Line border with a Light border style (1st column, 7th row)
to the range G3:K21.
c.
Add a Top border with a Light border style (1st column, 7th row) to the
range G4:K4.
d.
Add a Bottom border with a Light border style (1st column, 7th row) to
the range G18:K18.
To make the various elements of the Campground Mortgage worksheet easier
to select and print, Kaleen wants to add custom names to ranges in the
worksheet.
a.
Apply the custom name Mortgage_Payment to the range A2:D7.
b.
Apply the custom name Interest_Rate_Schedule to the range A9:D26.
c.
Apply the custom name Amortization_Schedule to the range G2:K21.
11.
Assign names to the cells in the range D5:D7 by selecting the range C5:D7 and
creating names from the selection using the values in the Left column as the
defined names.
12.
Kaleen wishes to protect the worksheet, so that she doesn’t make any
accidental changes to the values. However, since her assumptions about the
price of the campground, the down payment, and the mortgage interest rate
may be incorrect, she wants to be able to update these values in the protected
worksheet.
13.
a.
Select and unlock the range B5:B6.
b.
Select and unlock cell D3.
c.
Protect the Campground Mortgage worksheet without a password.
Kaleen had previously hidden a worksheet containing data on other recently
purchased campgrounds in New Hampshire. Now she wants to compare the
data in that worksheet with the data she just calculated.
Unhide the Campground Research worksheet.
14.
Switch to the Campground Research worksheet.
In cell B8, create a formula without using a function that determines the total
interest associated with the mortgage. First multiply the value in cell B6 (the
number of terms) by the value in cell B7 (the number of monthly payments)
and by 12 (to convert the yearly terms to monthly terms), and then subtract
the value in cell B4 (the total loan amount).
Copy the formula in cell B8 into the range C8:E8.
15.
Kaleen would like to be able to see the remaining balance of the campground
mortgage at the end of the current year.
Shelly Cashman Excel 2016 | Module 4: SAM Project 1a
In cell B11, create a formula using the PV function to determine the
outstanding balance of the campground mortgage at the end of the current
year using the parameters below:
a.
For the rate parameter, use the value in cell B5 (the yearly interest rate
of the mortgage) divided by 12.
b.
For the nper parameter, subtract the value in cell B10 (the current year
of the mortgage) from the value in cell B6 (the total number of years of
the mortgage), and multiply that by 12.
c.
For the pmt parameter, use the value in cell B7 (the monthly payments),
putting a negative sign before this value to make the outcome of the PV
function positive.
Copy the formula from cell B11 to the range C11:E11.
Your workbook should look like the Final Figures below. Save your changes, close the
workbook, and then exit Excel. Follow the directions on the SAM website to submit your
completed project.
Final Figure 1: Campground Mortgage Worksheet
Shelly Cashman Excel 2016 | Module 4: SAM Project 1a
Final Figure 2: Campground Research Worksheet
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from t
SAM website.
Shelly Cashman Excel 2016 | Module 4: SAM Project 1a
Camp Millowski
FINANCIAL FUNCTIONS, DATA TABLES, AND AMORTIZATION SCHEDULES
Igor Mbiso
edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the
SAM website.
Camp Millowski
Date
Item
Price
Down Payment
Loan Amount
Mortgage Loan Payment Calculator
15-Aug-18 Rate
Campground Term (Years)
$
486,000.00 Monthly Payment
$
97,200.00 Total Interest
$
388,800.00 Total Cost
Varying Interest Rate Schedule
Monthly Payment Total Interest
Rate
3.500%
3.750%
5.750%
15
$
$
(388,800.00)
97,200.00
Total Cost
Year
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Amortization Schedule
Beginning Balance
Ending Balance
Paid on Principal
$
388,800.00 $
371,973.53
$
371,973.53 $
354,153.63
$
354,153.63 $
335,281.65
$
335,281.65 $
315,295.47
$
315,295.47 $
294,129.30
$
294,129.30 $
271,713.49
$
271,713.49 $
247,974.26
$
247,974.26 $
222,833.46
$
222,833.46 $
196,208.36
$
196,208.36 $
168,011.31
$
168,011.31 $
138,149.52
$
138,149.52 $
106,524.69
$
106,524.69 $
73,032.73
$
73,032.73 $
37,563.42
$
37,563.42 $
Subtotal
$
Down Payment
Total Cost
Interest Paid
$
–
$
–
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades