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