Solver in Microsoft Excel

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 constraints like all the investments values will be >=0 and all cells that containing the End Cash values 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.

Watch the Excel training video on Solver in Excel:


Further reading:
Introduction to optimization with the Excel Solver tool

Leave a Reply

Your email address will not be published. Required fields are marked *