Cash required for a year must be withdrawn from the bank

Question

It is January 1, 2011. Beth is the finance manager at the Glop Foundation whose sole asset is $75,000 in cash in its checking account. The Foundation must make the following cash payments for the next three years:

2011: $25,000 2012: $25,500 2013: $26,000

Cash required for a year must be withdrawn from the bank and set aside at the beginning of that year. The Foundation is scheduled to shut down on January 1, 2014. On that date, all cash left over in its checking account will be withdrawn and paid to the Foundation's beneficiaries.

In addition to the checking account, the Foundation's bank offers two savings vehicles: a 2-year Certificate of Deposit (or CD) paying 3% interest per year and a 3-year CD paying 5% interest per year. Each CD can be purchased in any amount. Neither CD permits early withdrawal. Any cash that is not invested in the CD's on January 1, 2011, is left in the checking account that pays 2% interest per year. Interest (from the checking account and from the CDs) is deposited in the Foundation's checking account on January 1 of each year and is available for the Foundation's cash requirements for that year. For simplicity assume the checking account interest is paid at the end of the year balance.

Beth would like to develop a financing plan that maximizes the amount that can be paid to the Foundation's beneficiaries on January 1, 2014, while meeting the Foundation's payment requirements for the next three years. Develop a linear optimization model to accomplish this objective.

 

1. Specify the decision variables.
2. Specify the objective (as a function of the decision variables and/or intermediate variables).
3. Specify the constraints (as a function of the decision variables and/or intermediate variables).
4. Specify all intermediate variables (if any).

Implement your model in Excel. What is the optimal investment strategy and how much can the Foundation's beneficiaries expect to get paid on January 1^st 2014. Include Exhibit 1-1 of your implemented model that is clearly labelled (with row and column headings) and shows the formulas.