Suppose you want to save up funds over the next few years to buy a house in the very expensive SF Bay Area, and the required down payment on the house today is $100,000 plus the last 3 digits of your student ID number, eg. if your student ID number is 987654321, then the down payment today would be $154,321 However, assume that real estate prices will keep growing at an average rate of s percent per year, so the future amount of money you're trying to save up is FV8154,321(1+x, where is the number of years from now that you buy the house. In Excel you can raise a number x to the power by using either the cell formula POWER,y) orx'y. To save up FV. you plan on methodically depositing D dollars every month into a sinking fund" that cams interest at an annual rate of r percent per year, compounded monthly. The interest rate per month is 1-112. The monthly deposit D needed to reach your goal in years is D - (FV)/(1+1)-1). Where is the total number of months over which you'll be making deposits. For example, if you want to buy in 1 - 4 years, then 2 - 48 months. Alternatively, you can also calculate D in Excel using the cell formula -PPMTU, 1. , , FV), where FV is the future value described above. One key decision to make is how many years (1) you want to take to accumulate FV, which impacts the size of your monthly deposit. Todos 1. Name your Excel file "Al-LastnameFirstname" and submit on iLcam by 8:00 pm on Feb. 2 2. On Sheet 1: Make an influence chart for this situation, with monthly deposit Das the output. You can use Excel's Insert Shapes to make rectangles, arrows, etc. This may take a little time and practice 3. On Sheet 2: Create your Excel model following the format used in class, le, have different sections for Inputs (eg, down payment. 8.), Decision Variables (there's only one), Calculated Quantities and Outputs, also use range names a Use an average percentage increase in housing prices g -3% per year b. Initially, use an annual interest rate canned on the sinking fundr - 3% per year. This will be varied in part 4 below.) c. Initially, assume you will take 4 years to accumulate FV. (This will be varied in parts 3 & 4 below.) 4. Also, on Sheet2 make a 1-way Data Table in which the time to accumulate FV varies from 2 to 6 years (in 1-year increments) and the output columns are monthly deposit and future value FV 5. Also on Shect.2. make a 2-way Data Table for Din which the time to accumulate FV varies from 2 to 6 years (in l-year increments) and the annual interest rate r earned by the sinking fund varies from 3% to 6% (in 0.5% increments). Highlight cells with a monthly deposit $3,000. 6 On Sheet 3 Make the requested plots and give brief answers to the following questions a. Make an XY plot of FV vs. number of years (put FV on the vertical axis). What is the relationship (positive negative. linearnonlinear) between FV and the number of years h Make an XY plot of Dvs. number of years (put on the vertical axis). What is the relationship (positive negative. lineat nonlinear) between D and the number of years c. What is the relationship (positive negative, linear nonlinear) between D and the sinking fund's annual interest? d. If you can afford to make monthly deposits of at most $3,000 and the sinking fund's annual interest rate is 5%, how many years will it take to make the down payment on a house? Suppose you want to save up funds over the next few years to buy a house in the very expensive SF Bay Area, and the required down payment on the house today is $100,000 plus the last 3 digits of your student ID number, eg. if your student ID number is 987654321, then the down payment today would be $154,321 However, assume that real estate prices will keep growing at an average rate of s percent per year, so the future amount of money you're trying to save up is FV8154,321(1+x, where is the number of years from now that you buy the house. In Excel you can raise a number x to the power by using either the cell formula POWER,y) orx'y. To save up FV. you plan on methodically depositing D dollars every month into a sinking fund" that cams interest at an annual rate of r percent per year, compounded monthly. The interest rate per month is 1-112. The monthly deposit D needed to reach your goal in years is D - (FV)/(1+1)-1). Where is the total number of months over which you'll be making deposits. For example, if you want to buy in 1 - 4 years, then 2 - 48 months. Alternatively, you can also calculate D in Excel using the cell formula -PPMTU, 1. , , FV), where FV is the future value described above. One key decision to make is how many years (1) you want to take to accumulate FV, which impacts the size of your monthly deposit. Todos 1. Name your Excel file "Al-LastnameFirstname" and submit on iLcam by 8:00 pm on Feb. 2 2. On Sheet 1: Make an influence chart for this situation, with monthly deposit Das the output. You can use Excel's Insert Shapes to make rectangles, arrows, etc. This may take a little time and practice 3. On Sheet 2: Create your Excel model following the format used in class, le, have different sections for Inputs (eg, down payment. 8.), Decision Variables (there's only one), Calculated Quantities and Outputs, also use range names a Use an average percentage increase in housing prices g -3% per year b. Initially, use an annual interest rate canned on the sinking fundr - 3% per year. This will be varied in part 4 below.) c. Initially, assume you will take 4 years to accumulate FV. (This will be varied in parts 3 & 4 below.) 4. Also, on Sheet2 make a 1-way Data Table in which the time to accumulate FV varies from 2 to 6 years (in 1-year increments) and the output columns are monthly deposit and future value FV 5. Also on Shect.2. make a 2-way Data Table for Din which the time to accumulate FV varies from 2 to 6 years (in l-year increments) and the annual interest rate r earned by the sinking fund varies from 3% to 6% (in 0.5% increments). Highlight cells with a monthly deposit $3,000. 6 On Sheet 3 Make the requested plots and give brief answers to the following questions a. Make an XY plot of FV vs. number of years (put FV on the vertical axis). What is the relationship (positive negative. linearnonlinear) between FV and the number of years h Make an XY plot of Dvs. number of years (put on the vertical axis). What is the relationship (positive negative. lineat nonlinear) between D and the number of years c. What is the relationship (positive negative, linear nonlinear) between D and the sinking fund's annual interest? d. If you can afford to make monthly deposits of at most $3,000 and the sinking fund's annual interest rate is 5%, how many years will it take to make the down payment on a house