Read Everglade Company's (attached Case), Build a new module to formulate the problem mathematically in a spreadsheet, and use the MS solver to find out how to maximize the company cash flow in the ending cash flow in 2027.

The Everglade Cash Flow Problem - The Everglade Golden Years Company operates upscale retirement communities in certain parts of southern Florida. - The past few years have been difficult ones for Everglade. The demand for retirement community housing has been light and Everglade has been unable to maintain full occupancy. - However, this market has picked up recently and the future is looking brighter - Everglade has recently broken ground for the construction of a new retirement community and has more new construction planned over the next 10 years (2020 through 2029). - Julie Lee, the chief financial officer (CFO) at Everglade, has projected Everglade's net cash flows over the next 10 years as shown in the table (next slide) The Everglade Cash Flow Problem Everglade needs loans to continue its operations. 1. Their short-term cash flow requirements cannot be met without borrowing. 2. A long-term ( 10 year) loan can be taken with an interest rate of 5% 3. A series of short-term loans ( 1 year) can be taken with a projected interest rate of 7% Question: Which loan (or combination of loans) should be taken, and in what amounts, in order to maximize their cash-balance position after 10 years? The Everglade Cash Flow Problem With all the construction costs for the new retirement community, Everglade will have negative cash flow for the next few years. With only $1 million in cash reserves, it appears that Everglade will need to take out some loans in order to meet its financial obligations. Also, to protect against uncertainty, company policy dictates maintaining a balance of at least $500,000 in cash reserves at all times. The Everglade Cash Flow Problem - Year: 2018. - Company: Everglades Golden Years Company - Business: Operating retirement communities. - Synopsis: - Unable to maintain full occupancy. - Only $1 million left in cash reserves. - Cash flow problem. - Need to maintain at least $500,000 in cash reserves at all times. The Everglade Cash Flow Problem - Objective: - Maximize cash-balance position after 10 years. - Decisions: -Whether to borrow long term (LT) or short term (ST) loan. - Amount of loan to be borrowed. - Financial Parameters: - Short Term 1-Year (ST) Loan: 7\% interest rate. Which loan (or combination of loans) should be taken, and in what amounts, in order to maximize their cash-balance position after 10 years