Shelly Cashman Excel 2016 | Module 4: SAM Project 1a

Camp Millowski

Don't use plagiarized sources. Get Your Custom Essay on

Select the range A12:A26 and fill it with a percent series based on the values in range A12:A13.

From as low as $9/Page

Financial Functions, Data Tables, and Amortization Schedules

- Open the file
**SC_EX16_4a_**, available for download from the SAM website.*FirstLastName*xlsx - Save the file as
**SC_EX16_4a_**by changing the “1” to a “2”.*FirstLastName*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
**SC_EX16_4a_**still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.*FirstLastName*xlsx- If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

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

- Put a
**negative sign**before the PMT function to make the formula return a positive value. - 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.

- 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.
- In cell B11, create a formula without using a function that references cell
**D5**(the monthly payments). - In cell C11, create a formula without using a function that references cell
**D6**(the total interest paid on the loan). - In cell D11, create a formula without using a function that references cell
**D7**(the total cost of the mortgage). - 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.

- In cell B11, create a formula without using a function that references 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**.

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

- 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).- The formula should first check if the value in cell
**H4**(the balance remaining on the loan each year) is**greater than 0**. - 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.) - If the value in cell H4 is not greater than 0, the formula should return a value of
**0**.

- The formula should first check if the value in cell

Copy the formula from cell K4 into the range K5:K18.

- Apply the
**Accounting**number format with**two**decimal places and**$**as the symbol to the range K4:K18. - In cell K20, create a formula without using a function that references the defined name
**Down_Payment**. - Kaleen decides to add custom cell borders to the amortization schedule to make it easier to read.

Apply custom cell borders with a **Green, Accent 6, Darker 50% **(10^{th} column, 6^{th} row in the Theme Colors palette) line color as described below:

- Add an
**Outline**border with a**Medium**border style (2^{nd}column, 5^{th}row) to the range G3:K21. - Add a
**Vertical Line**border with a**Light**border style (1^{st}column, 7^{th}row) to the range G3:K21. - Add a
**Top**border with a**Light**border style (1^{st}column, 7^{th}row) to the range G4:K4. - Add a
**Bottom**border with a**Light**border style (1^{st}column, 7^{th}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.- Apply the custom name
**Mortgage_Payment**to the range A2:D7. - Apply the custom name
**Interest_Rate_Schedule**to the range A9:D26. - Apply the custom name
**Amortization_Schedule**to the range G2:K21.

- Apply the custom name
- 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.
- 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.
- Select and unlock the range B5:B6.
- Select and unlock cell D3.
- 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.

- Switch to the
*Campground Research*

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.

- Kaleen would like to be able to see the remaining balance of the campground mortgage at the end of the current year.

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:

- For the rate parameter, use the value in cell
**B5**(the yearly interest rate of the mortgage)**divided by 12**. - 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**. - 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.

Our Advantages

Plagiarism Free Papers

All our papers are original and written from scratch. We will email you a plagiarism report alongside your completed paper once done.

Free Revisions

All papers are submitted ahead of time. We do this to allow you time to point out any area you would need revision on, and help you for free.

Title-page

A title page preceeds all your paper content. Here, you put all your personal information and this we give out for free.

Bibliography

Without a reference/bibliography page, any academic paper is incomplete and doesnt qualify for grading. We also offer this for free.

Originality & Security

At thehomeworkwritings.com, we take confidentiality seriously and all your personal information is stored safely and do not share it with third parties for any reasons whatsoever. Our work is original and we send plagiarism reports alongside every paper.

24/7 Customer Support

Our agents are online 24/7. Feel free to contact us through email or talk to our live agents.

Try it now!

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

We work around the clock to see best customer experience.

Pricing

Our prices are pocket friendly and you can do partial payments. When that is not enough, we have a free enquiry service.

Communication

Admission help & Client-Writer Contact

When you need to elaborate something further to your writer, we provide that button.

Deadlines

Paper Submission

We take deadlines seriously and our papers are submitted ahead of time. We are happy to assist you in case of any adjustments needed.

Reviews

Customer Feedback

Your feedback, good or bad is of great concern to us and we take it very seriously. We are, therefore, constantly adjusting our policies to ensure best customer/writer experience.