Create a memo $(2$ pages or less$)$ with the calculations and tables requested by Mayor Goodyear. Please provide your Excel documents as an Appendix. The project needs to be uploaded to Canvas by the deadline listed in Canvas.

Case Description: Waterloo is a small suburban city without many amenities. It is an embarrassment to some that the town doesn$$t even have a water park. $($The kids in neighboring towns call it $$Waterless$.)$ The Mayor of Waterloo, Mayor Goodyear, has decided to fix this situation and has tasked you with helping with the financing of the waterpark.

Early discussions with council were positive when the architect$$s estimate of the cost of a reasonable pool came in at a modest $$230,000.$ The council was supportive as long as some conditions were met. First, the financing of the pool had to be less than $10$ years as they did not want to burden future generations with the pool and the Mayor said that was likely in $8$ years. The council also did not want to spend too much on the pool and said that local governments were not paying very much on debt in $2013.$ So$,$ they capped the interest they would pay at $2\mathrm{.}5\%.$ Finally, they are concerned about how much the total cost of building the pool, operating, and maintaining the pool would be$.($Your estimates are that the pool will cost about $$50,000$ per year to run and maintain the pool in the summertime once it is built based upon the cost of similar pools in nearby communities.$)$

You called your municipal financial advisor $($FA$)$ and asked them to look into what the financing of a pool of $$230,000$ over eight years would look like. You assume that you will borrow the money on January $1,2014.$ You have asked that the schedule be slightly loaded toward the end of the eight years in terms of principal repayment.

Once you get back the repayment schedule, you are worried that the final cost on the bonds maturing in $2021$ and $2022$ might not be acceptable to council, but the mayor has pointed out that you need to account for the bonds that mature in $2015$ and $2016$ that have a much lower interest rate. You remember from a class that you took long ago some crazy professor talking about Net Interest Cost $($NIC$).$ You tell the mayor that if you calculate the NIC that you will hopefully see that the cost of the interest is below $2\mathrm{.}5\%.($Note here that this is just the offering statement. So$,$ there is no premium or discount at this point.$).$ She has agreed and asked you to do the calculations in a memo to her and council.

Due $1-$ Jan. Principal Amt. Interest rate $\%$

$2015$ $$25,0000\mathrm{.}75\%$

$2016$ $$25,0000\mathrm{.}95\%$

$2017$ $$30,0001\mathrm{.}10\%$

$2018$ $$30,0001\mathrm{.}35\%$

$2019$ $$30,0001\mathrm{.}75\%$

$2020$ $$30,0002\mathrm{.}25\%$

$2021$ $$30,0002\mathrm{.}60\%$

$2022$ $$30,0003\mathrm{.}10\%$

Finally, Mayor Goodyear also wants you to calculate the yearly financing costs $($use the rates given to you by your FA as the cost of financing the pool$)$ plus the cost of running and maintaining the pool that you estimated at $$50,000$ per year. Report back to her in your memo on how much this is going to increase taxes for people with property worth $$200,000,$ $$300,000,$ and $$400,000.$ You know from your city$$s most recent valuation that the amount of taxable property in the city is $$200,000,000.($To do this, you will need to make some assumptions. For the assessment ratio for the homeowners calculation use the Kansas residential property assessment ratio of $11\mathrm{.}5\%.$ Also, assume that $1\%$ of the property tax that you levy is uncollectable

Show work.