Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Excel module 4 sam end of module project 1

08/01/2021 Client: saad24vbs Deadline: 2 Day

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


C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png 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”.


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


· If cell B6 does not display your name, delete the file and download a new copy from the SAM website.


PROJECT STEP

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.


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.


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.


c. In cell B11, create a formula without using a function that references cell D5 (the monthly payments).


d. In cell C11, create a formula without using a function that references cell D6 (the total interest paid on the loan).


e. In cell D11, create a formula without using a function that references cell D7 (the total cost of the mortgage).


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


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


g. The formula should first check if the value in cell H4 (the balance remaining on the loan each year) is greater than 0.


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


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


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% (10th column, 6th row in the Theme Colors palette) line color as described below:


j. Add an Outline border with a Medium border style (2nd column, 5th row) to the range G3:K21.


k. Add a Vertical Line border with a Light border style (1st column, 7th row) to the range G3:K21.


l. Add a Top border with a Light border style (1st column, 7th row) to the range G4:K4.


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


n. Apply the custom name Mortgage_Payment to the range A2:D7.


o. Apply the custom name Interest_Rate_Schedule to the range A9:D26.


p. Apply the custom name Amortization_Schedule to the range G2:K21.


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.


q. Select and unlock the range B5:B6.


r. Select and unlock cell D3.


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


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:


t. For the rate parameter, use the value in cell B5 (the yearly interest rate of the mortgage) divided by 12.


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


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




Final Figure 2: Campground Research Worksheet




2



Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

University Coursework Help
Helping Hand
Top Essay Tutor
Instant Assignments
Financial Hub
Writer Writer Name Offer Chat
University Coursework Help

ONLINE

University Coursework Help

Hi dear, I am ready to do your homework in a reasonable price.

$102 Chat With Writer
Helping Hand

ONLINE

Helping Hand

I am an Academic writer with 10 years of experience. As an Academic writer, my aim is to generate unique content without Plagiarism as per the client’s requirements.

$100 Chat With Writer
Top Essay Tutor

ONLINE

Top Essay Tutor

I have more than 12 years of experience in managing online classes, exams, and quizzes on different websites like; Connect, McGraw-Hill, and Blackboard. I always provide a guarantee to my clients for their grades.

$105 Chat With Writer
Instant Assignments

ONLINE

Instant Assignments

Good day dear client, I am a full-time freelance writer with years of experience. My strongest strengths are strong dedication to duty, reliability, quality and excellence. I am going to do an outstanding job for you, kindly PM me and let us commence. Thank you so much in anticipation of your positive response. Thank you very much.

$95 Chat With Writer
Financial Hub

ONLINE

Financial Hub

Hey, I have gone through your job posting and become very much interested in working with you.I can deliver professional content as per your requirements. I am a multi-skilled person with sound proficiency in the English language for being a native writer who worked on several similar projects of content writing and can deliver quality content to tight deadlines. I am available for both online and offline writing jobs with the promise of offering an incredibly responsive and supreme level of customer service. Thanks!

$95 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Crypto Security Architecture - Physics vce cheat sheet - The top-level executive task of crafting a diversified company's overall or corporate strategy - Explain transformational improvement in relation to organizational performance - Math Questions due at 10:00pm tonight. - Explanation - Quatrain poems for kids - Colored lights can hypnotize - The house with a clo - 10 strategic points grand canyon university - Flysky fs gt3c reverse throttle - A midsummer night's dream project - How Can Tutorsonspot Help You Find Reliable Services? - Glue boiler animal farm - The oasdi taxable wage base is correctly defined as - The great gatsby chapter 1 - Op - The term _____________ describes circumstances where a country's exports exceed its imports. - Importance of ritz carlton wow stories - Grass fed milk malaysia - 6 4 parallel lines and proportional parts worksheet answers - Concept analysis paper - Phillip hylander goldman sachs - Plane wreck at los gatos poem analysis - Select characteristics exhibited by exotoxins but not exhibited by endotoxins - Charles perkins centre summer research scholarships - Case studies of organelle diseases answers - I have 2 parts to this report that needs to be complete. The topic is attached in the pdf. - Aquatic safety training academy - 375b springvale road springvale - Testout security pro final exam answers - Discussion - Eaco chem nmd 80 - Themes in so long a letter by mariama ba pdf - Currency refers to umuc - The Ethics Behind Assessment - Marshall l fisher what is the right supply chain - Point nepean walking tracks - Apple's sTRATEGY - Resolvedd strategy - Macroeconomics - 216 bus times cork - Week5 -LDR - Craven community college calendar - Unrelated incidents poem audio - CHRISTIAN SERVANT LEADERS - Vinegar contains acetic acid the formula - Gamma distribution moment generating function proof - Tricep extension machine instructions - Difference between power divider and directional coupler - Input output devices in embedded system ppt - An Introduction to Python Modules - Amp retirement savings account abn - Redesign and re-warding PPT - Ryan boot company comprehensive problem - Lección 4 contextos - Create a list of stakeholders for the ir planning committee - The apathetic organizational culture exhibits - Compute the takt time for a system - Jeff cavaliere workout pdf - DIscussion1 - Stolen jane harrison scenes - Yellow bus 6 wimborne to bournemouth - Discussion forum - Most reactive metal in group 1 - The death of the ball turret gunner answers - Byte of accounting inc answers - Cu no3 2 naoh ionic equation - HW - Internal auditors are primarily involved with - Cr acac 3 ir - How did president hoover respond to the great depression - Discussion 7a - Discussion 2 Number 4 - Nursing diagnosis related to heart failure - How would you build a csirt? what are the components to building an effective team? - Fartlek training fitness components - The grammatical structure "a unless b" is a logically equivalent to __________ - 350 words - Introduction to signals and systems edward w kamen - CHRISTIAN SERVANT LEADERS - How do heavy sediment deposits affect waterways - Ansi isa 5.1 1984 r1992 - Staple spun yarn characteristics - Mds business rules examples - Political Issue - William graham sumner argued that distinguishing between our ingroups and our outgroups helps us: - Project - Weak ties are valuable parts of a social network because - Suppose there is a mbps microwave link - Research Paper - Dulux durebild ste application - French pronunciation cheat sheet - City of monroe continuous problem solution - Mediation analysis spss output interpretation - 73 coronet crescent burleigh waters - Interactive profile interpretation system - Has Globalization Benefitted Humanity? - Mason company schedule of cost of goods manufactured - For some similar Earth Hour events that have been held