MIS 211 Homework Solution, Fall 2014

Scenario Summary Current Values: Worst Middle Best Changing Cells: $C$3 50,000 50,000 100,000 120,000 $C$4 13,200 13,200 20,000 20,000 Result Cells: $C$5 36,800 36,800 80,000 100,000 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray. Gross Revenue 50,000 Cost of Goods Sold 13,200 Gross Profit 36,800 Loan Amount $100,000.00 $100,000.00 Terms in Months 180 180 Interest Rate 8.00% 5.18% Payment $955.65 $800.00 Q1- The above data shows the worst Case scenario. You need to create this scenario plus two other scenarios detailed as: Middle Case Scenario: Gross Profit=100,000 and cost of Gooids Sold=20,000. Best Case Scenario: Gross Profit=120,000 and cost of Gooids Sold=20,000. After you create the above scenarios, then you need to show the Scenario Summary. Q2-You are working for a bank calculating mortgage (House Loan) monthly payment where a total of 180 months term duration for the $100,000 loan. (a)- Find out the monthly payment required from your customer (Pink area). Briefly explain steps taken. Answer: =pmt(b18/12,b17,-b16) (b)- Your customer was not happy with the result and informed you that another bank (competitor) told him that he will only pay $800 a month for the same amount of loan. How much interest rate your competitor is offering your customer? Briefly explain steps taken by seeking this goal. Show your answer in the Yellow area above. ITM211 Computer Assignment1 Name:Rayane El Medawar Grading: Practical-Q1 0.5% Practical-Q2(a) & (b) 1% Practical-Q3 0.5% Price of car $20,000 Down payment $5,000 $473.51 4000 5000 6000 7000 Amount financed $15,000 12 $1,395.52 1308.297 1221.077 1133.857 Annual interest rate 8.50% 24 $727.29 681.8351 636.3794 590.9238 Number of payments 36 36 $505.08 473.5131 441.9455 410.378 48 $394.37 369.7246 345.0762 320.4279 Monthly payment $473.51 60 $328.26 307.748 287.2314 266.7149 Total interest paid $2,046.47 Q3- The data below is for car loan it is required to build a What-if table for different values of Number of Payments: These are: 12, 24, 36, 48 and 60 months. As well as different possible values of down payment : These are:4000,5000,6000,7000 mputer Assignment1 ID:201302483 Grading: 0.5% ) & (b) 1% 0.5% Worst Page 4 50000 13200 Middle Page 5 100000 20000 Best Page 6 120000 20000