Let's
learn how to maximize our interest earnings from surplus cash using
Solver in MS Excel. If you keep the surplus cash at home or in the
current account as many small companies tend to, you are not managing
your cash resources well. But a smart manager will always find a way to
generate more money from his surplus cash.
We first check our amount of daily cash
Next we decide to invest carefully every month in
1-month -, 3-month- and 6-month-CDs that give us a yield of 1%, 3% and
6% respectively
We wish to also ensure that we have always have a
cash amount of 75000 so that our business is never short of cash that
we may need to run it properly
Based on our regular investments we can calculate
that we can earn 7200 from investing our surplus cash
The manager, however, feels that the investment
amounts can be optimized by using the Solver feature in MS Excel
So he starts Solver by clicking on the Data tab and
selecting Solver from the analysis group
He sets the cell G3 to Max in the Solver window
He defines the cells $B$8:$G$8,$B$9,$E$9,$B$10 that
he would like to change to maximize interest earnings
So that Solver doesn't use negative values and he
always has 75000 units of cash to run his business properly the manager
also defines some contraints like all the investments values will be
>=0 and all cells that containg the End Cash valuse
B12 to G12 will always be >=75000
Now when he runs the Solver he finds that he can earn
interest of 31929 if he adopts a better investment strategy.
His earnings from interest are optimized to 4-times
of what he would have earned earlier.