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
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?  
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.  
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.