1. Timov family budget

/in /by

CIS 1100: Final Exam Part B case-Timov_Family_Budget

Data File needed for the case: Timov_Family_Budget.xlsx

Instructions

Sergei and Ava Timov have asked you to create an Excel workbook for their family budget. The Timovs want to purchase a new home. They are considering two houses with different mortgages. They want the budget worksheet you create to display the impact of monthly mortgage payments on the couple’s cash flow. The couple has already designed part of the workbook and entered estimates of their take home pay and expenses for the upcoming year. They want you to set up the formulas for the remaining spreadsheet including the mortgage payments and base their decision on the Average Net Cash flow for the month for each of the two options.

Please complete the following:

  1. Open the Timov_Family_Budget datafile attached to this Exam. Click Save as and save the workbook as Timov_Family_Budget_yourlastname on your own computer or on your flash drive.
  2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4 using the function Today().
  3. In cell C6, enter a formula to calculate the sum of Serge’s monthly salary for the entire year. In cell D6, calculate Sergei’s average take-home pay each month. In cell E6, calculate Sergei’s maximum monthly take-home pay. In cell F6, calculate Sergei’s minimum monthly take home pay. (Hint: Use AutoSum and then select cells C18:N18, for Average, Max and Min, select the appropriate function in the drop down list on the AutoSum button and then select the cells C18:N18).
  4. Select the range C6:F6, and then use AutoFill to complete the formula in the C7:F15 range. Use the AutoFill Options button to copy only the formulas into the selected range (radial button fill without formatting) and not both the formulas and formats. (Hint: Because you haven’t yet entered any mortgage payment values, cell D13 will show the value #DIV/0!, indicating the Excel cannot calculate the average mortgage payment because it cannot divide by zero. You’ll correct that problem shortly.) NOTE: If done correctly C6:F8 will be purple font, C9:F13 will be orange font and C14:F15 will be bold black font.
  5. In the range J5:J12, enter the following loan and loan conditions of the first mortgage to compute fixed monthly payments for the loan in cell J11 using the appropriate function.
  • The loan amount (or value of the principal) is $335,000.
  • The annual interest rate is 5%.
  • The interest rate is compounded 12 times a year (or monthly).
  • The mortgage will last 30 years.

Use cell references where ever appropriate in the formula.

  1. In cell J11, make the Monthly payment value positive by multiplying by -1 or inserting a minus sign between the = and the payment function.

 

  1. In the range N5:N12, enter the following loan and loan conditions of the second mortgage to compute fixed monthly payments under Mortgage 2 (Remember to use cell references as above):
  • The loan amount (or value of the principal) is $242,000.
  • The annual interest rate is 4.75%.
  • The interest rate is compounded 12 times a year (or monthly).
  • The mortgage will last 20 years.
  1. In cell N11, enter the function to calculate the monthly payment needed to pay off this loan, and then make the monthly payment value positive.
  2. Sergei and Ava want to be able to view their monthly cash flow under both mortgage possibilities. The mortgage being applied to the budget will be determined by whether 1 or 2 is entered into cell C3. To switch from one mortgage to another, do the following:
  • In cell C25, enter an IF function that tests whether cell C3 equals 1. If it does, display the value from cell J11; otherwise, display the value from cell N11. You will need to use absolute cell references in the formula.
  • You will also need to copy the formula in cell C25 to range D25:N25 to reflect the same monthly payment for other months.
  • Verify that the values in the range C25:N25 match the monthly payment for the first mortgage condition.
  1. In cell C3, edit the value from 1 to 2. Verify that the monthly payment for the second mortgage appears in the range C25:N25.
  2. Sergei and Ava want to maintain an average net cash flow of at least $1,000 per month. Under which mortgage is this achieved? Which cell on the sheet will help you make that decision? Please write your answer in cell A30.
  3. Please save the sheet with your recommendation 1 or 2 in Cell C3. Save and close the workbook, and then upload and submit the finished file using the Final Exam link on Blackboard.