Ask Management Information System Expert

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

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 dividedby12to calculate the monthly interest rate, and Term_Yearsshould be multipliedby12 to calculate the total number of monthly payments.

2. Kaleen calculated the anticipated total cost of the campground usingthe 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.

3. 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 formulawithout 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 absolutereference to cell D3 (the mortgage interest rate) as the Column input cell. 

4. 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 J4subtracted from the value in cell D5multiplied 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.

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:

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.

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

a. Select and unlock the range B5:B6.

b. Select and unlock cell D3.

c. Protect the Campground Mortgage worksheet without a password.

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

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

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.

Attachment:- database.rar

Management Information System, Management Studies

  • Category:- Management Information System
  • Reference No.:- M92758870

Have any Question?


Related Questions in Management Information System

Search the csu library the internet or any specific

Search the CSU library, the Internet, or any specific websites, and scan IT industry magazines to find an example of an IT project that had problems due to organizational issues. Write a paper summarizing the key stakeho ...

Question how can company protect the new emerging

Question : How can company protect the new emerging technology ventures from profit pressures of the parent organization (APA format required, Turntin check required . Minimum 250 words essay) How do companies overcome l ...

Communication and team decision makingpart 1 sharpening the

Communication and Team Decision Making Part 1: Sharpening the Team Mind: Communication and Collective Intelligence A. What are some of the possible biases and points of error that may arise in team communication systems? ...

Question provide an explanation of ifwherehow does active

Question : Provide an explanation of if/where/how does Active Directory support network security,14 pages (2,000-2,500) in APA format. Include abstract and conclusion. Do not include wikis, message boards, support forums ...

Question how companies could effectively use emerging

Question : How companies could effectively use emerging technology to win over its competitors. APA format required. 250 words essay required. The response must be typed, single spaced, must be in times new roman font (s ...

Question how customers could effectively use emerging

Question : How customers could effectively use emerging technology to win over its customers. APA format required. 250 words essay required. turntin check require. The response must be typed, single spaced, must be in ti ...

Part 1 - create an 8 slide powerpoint presentation on

Part 1 - Create an 8 slide PowerPoint presentation on foundational concepts specific to physical security. Part 2 - Write 4 pages detailing the framework for the design of an integrated data center. Assessment Instructio ...

In chapter 2 of the text - managing amp using information

In Chapter 2 of the text - Managing & Using Information Systems: A Strategic Approach, the chapter discusses why information systems experience failure often because of organizational strategy. A classic example of this ...

Review at least 4 articles on balanced scorecard and

Review at least 4 articles on Balanced Scorecard and complete the following activities: 1. Write annotated summary of each article. Use APA throughout. 2. As an IT professional, discuss how you will use Balanced Scorecar ...

Data resources management questionsq1 the dama dmbok

Data Resources Management QUESTIONS Q1. The DAMA DMBOK textbook describes the following two core activities as part of the Data Architecture management exercise: "Understanding enterprise information needs" and "Develop ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As