Skip to main content
Call Us: 888-831-0333
Free Trial

Working Capital Management

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.