This post is intended to give you an example of using Solver in Excel to solve a causal problem in daily life – buying postal stamps.  Don’t consider it a tutorial. 😛

Have you ever heard of Solver in Excel?

I think Solver is not a common tool to most users, regardless of years of experience of using Excel.  I learned Solver in my MBA study a decade ago.  I was impressed by the power of Solver.  Nevertheless, I have never encountered a situation that I needed Solver in my work life, so far (probably due to my job nature).  Solver is just for fun to me until one day I went to post office…

There was a super long queue and I was not patient enough to wait.  Therefore I went to weigh my small parcels and see how much fare was required for each parcel.  It was HK$9.8 and HK$11.7.  Then I went to the self-service stamp vending machine to buy the stamps… Wait, there were only four options available.  I don’t remember the four options, let say HKD1.7, HKD2.2, HKD3.0, HKD3.7.

Obviously, the question was: How many stamps of what values should I buy, so as to minimize the total amount to be paid while ensuring enough postal fare???

Standing in front of the machine,  it took me quite a while to figure out the solution.

After I got home, I decided to solve the problem in Excel!  First, I think of formula.  To be honest, it would be far more complicating… i.e. I don’t know how. 😛

Then Solver came to my mind!  See the demonstration below:

Isn’t it cool?

You may download the Sample File – Solver usage in daily life.

(Somehow WordPress.com does not support .xlsm, therefore the sample file is a marco-free file.  Instead of clicking the textbox to run the solver, please go to Data Tab –> Analysis Group –> *Solver –> Solve; note: do not change the parameters set)

I am not going to give you a detailed explanation on how it works, but you may see the <Brief Explanation> in the file.

While using Solver in Excel is not difficult, understanding the problem and building a corresponding model for Solver is.  Not to mention the different Solving Methods: GRG Nonlinear, Simplex LP, Evolutionary. 😛

FINAL NOTE:

The model is not perfect.  In some circumstances it refuses to give optimal solution (local vs. global) where there is an obvious one.  Honestly, I have not master the skill of using Solver yet.  As mentioned in the beginning, I hope to show you an example of using Solver – a powerful tool in Excel and hence raise the awareness of Solver.

If you have suggestions to enhance my model, please leave your comments.

*Note:  If you do not see “Solver” on your Data tab, follow the steps below to install the Solver Add-in.

File –> Options

Check the Solver Add-in, then press OK, and OK

You should see the “Solver” on Data tab