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