Determine how to invest excess cash in 1-month, 3-month and 6-month CDs so as to maximize interest income while meeting company cash requirements (plus safety margin). Yield Term Price Purchase CDs in months: 1-mo CDs: 1.0% 1 \$2,000 1, 2, 3, 4, 5 and 6 Interest 3-mo CDs: 4.0% 3 \$3,000 1 and 4 Earned: 6-mo CDs: 9.0% 6 \$5,000 1 Total \$0.00 Month: Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 End Init Cash: \$400,000 \$325,000 \$335,000 \$355,000 \$275,000 \$225,000 \$240,000 Matur CDs: \$0 \$0 \$0 \$0 \$0 \$0 Interest: \$0 \$0 \$0 \$0 \$0 \$0 1-mo CDs: 0.00 0.00 0.00 0.00 0.00 0.00 3-mo CDs: 0.00 0.00 6-mo CDs: 0.00 Cash Uses: \$75,000 (\$10,000) (\$20,000) \$80,000 \$50,000 (\$15,000) \$60,000 End Cash: \$325,000 \$335,000 \$355,000 \$275,000 \$225,000 \$240,000 \$180,000 Problem A company wants to invest excess cash in 1-month, 3-month and 6-month Certificates of Deposit (CDs). The company has expected uses of cash in the next 6 months, and it wants to make sure that the principal and interest from maturing CDs meet the requirements for cash plus a safety margin for each month. For simplicity we assume that 3-month CDs can only be bought at the start of months 1 and 4, and 6-month CDs can only be bought in month 1. Initial cash available is \$400,000. How many and what kind of CDs should the company buy in order to maximize the earned interest, and meet the safety margin of \$100,000 each month? Solution The characteristics of the 3 different CDs are given in cells A5 through F9. 1) The variables are the number of CDs to buy in each month. The variable cells are given names One_month_CDs, Three_month_CD1 and Three_Month_CD2, and Six_month_CDs. There are 6+2+1 = 9 variables. 2) The constraints are the limitations on the formulas in this model. First, there is the safety margin requirement for each month. This gives Monthly_cash >= 100000 Then there are the logical constraints on the number of CDs to be bought. It is not possible to buy half or other fractions, or negative amounts of CDs. We can rule out negative amounts with the Assume Non-Negative option. This gives One_month_CDs = integer Three_month_CD1 = integer Three_month_CD2 = integer Six_month_CDs = integer 3) The objective is to maximize earned interest. This is calculated by multiplying the number of CDs bought of each kind by the interest earned for each CD. This is given the name Total_interest. Remarks This is a good example of how the solver can help you make intelligent decisions in investments. Before solving the model with the Solver try to find a solution by hand. What interest is earned? The time required by the solver to solve this model can be considerable. Integer problems are very difficult to solve. In a model like this it would be possible to change the variables to the amount of money to be invested. This would give a 'normal' problem. We chose this form since often it is not possible to invest an arbitrary amount of money in a CD. In some situations it is not desirable to use integer constraints. When an expected solution of a model yields a value that is 2034.86, for example, it is safe to assume we can round this number to 2035. If the value is 0.34 however, it is not safe to assume we can round this number. In each model, you have to tradeoff precision vs solution time to make a decision whether or not to use integer variables.