Solver in Microsoft Excel

Home Back More on Solver



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.

Reference

Home Back More on Solver