Please solve this using an excel spreadsheet and show each step
5. A small college wants to add some new parking locations. The president would like each building to be within a 5-minute walk of at least one set of new parking spaces (the spaces will be added in blocks of 10 parking spaces). The college is considering six locations for the new parking spaces, but would like to minimize the overall cost of the project. In addition to the walking time requirement, the university would like to add at least 40 new parking spaces (at least 4 blocks of 10). The estimated walking time from each of the campus buildings is shown in the table. The college would need a 10-year bank loan to pay for the parking lot project. Its budget office prepared an estimate of the annual mortgage payment required for each of the possible parking locations. The estimate is based on a single equal annual payment (at end of each year), with an annual interest rate of 5%, compounded annually. a. Please calculate the present value of the loan required for each parking location option. 25 pts. b. Please provide an algebraic formulation of the college's parking lot problem. - 25 pts. c. Please prepare an Excel spreadsheet to represent the problem, indicating the data, its constraints, decision variables, and constraints. - 25 pts. d. Please use the present value of the loan to determine the locations of the parking lots that meet the average walking time requirement at minimum cost. Please indicate the value of that minimum cost. 25pts. i(1 + i)n] P=A (1 + i) -1]/ P= present value of the investment A = uniform annuity amount (mortgage payment) i= interest rate n= number of interest periods Parking Lot #and - Average Walking Distance # Building 1 2 3 5 6 1 Recreation 0 4 6 10 3 2 2 Science 15 8 7 / 2 4 L 5 3 Medical 2 9 6 1 3 12 4 Library 8 5 4 2 6 / 7 5 Engineering / 9 7 2 4 1 6 6 Arts 8 3 7 8 7 President's - 4 | Annual Mortgage $51,801.83 $48,564.22 $55,039.44 $45,326.60 $53,096.88 $64,752.29 WVuo 00 3 5. A small college wants to add some new parking locations. The president would like each building to be within a 5-minute walk of at least one set of new parking spaces (the spaces will be added in blocks of 10 parking spaces). The college is considering six locations for the new parking spaces, but would like to minimize the overall cost of the project. In addition to the walking time requirement, the university would like to add at least 40 new parking spaces (at least 4 blocks of 10). The estimated walking time from each of the campus buildings is shown in the table. The college would need a 10-year bank loan to pay for the parking lot project. Its budget office prepared an estimate of the annual mortgage payment required for each of the possible parking locations. The estimate is based on a single equal annual payment (at end of each year), with an annual interest rate of 5%, compounded annually. a. Please calculate the present value of the loan required for each parking location option. 25 pts. b. Please provide an algebraic formulation of the college's parking lot problem. - 25 pts. c. Please prepare an Excel spreadsheet to represent the problem, indicating the data, its constraints, decision variables, and constraints. - 25 pts. d. Please use the present value of the loan to determine the locations of the parking lots that meet the average walking time requirement at minimum cost. Please indicate the value of that minimum cost. 25pts. i(1 + i)n] P=A (1 + i) -1]/ P= present value of the investment A = uniform annuity amount (mortgage payment) i= interest rate n= number of interest periods Parking Lot #and - Average Walking Distance # Building 1 2 3 5 6 1 Recreation 0 4 6 10 3 2 2 Science 15 8 7 / 2 4 L 5 3 Medical 2 9 6 1 3 12 4 Library 8 5 4 2 6 / 7 5 Engineering / 9 7 2 4 1 6 6 Arts 8 3 7 8 7 President's - 4 | Annual Mortgage $51,801.83 $48,564.22 $55,039.44 $45,326.60 $53,096.88 $64,752.29 WVuo 00 3
