Excel 2010 -Solver

Solver is not something new to Microsoft Excel; it was first introduced to Excel 3.0 on an OEM basis in 1991, i.e. 20 years ago.  However, I noticed it was one of the best kept secrets in Excel so I am going to formally introduce it to you as a NEW feature.

What’s solver?  It is a what-if analysis tool that you can use to do planning, budgeting, projection and optimization.  It consists of mainly 4 components:

  • Objective – you specify your goal in a cell

alt

  • Variable – cells that solver would keep adjusting in order to reach the objective

alt

  • Constraints – there may be constraints to your problem

alt

  • Method – How do you want to solve the problem

alt

Solver is not enabled when you first install Excel, you must first enable the Solver Add-In. To Enable Solver

  • Click the File Menu and choose Options and you would see Excel Options dialogue box.

alt

  • Select Add-Ins from the left sidebar and then hit the GO button next to Manage Excel Add-ins at the bottom.

alt

  • Check the Solver Add-in option and Click the OK button.

alt

  • Now you would see the Solver group under the Data tab.

alt

Example – Use Solver to find how much you need to contribute for your retirement You are 30 years old and earn $60,000 a year.  Your goal is to retire at age 65 and able to receive a retirement income that is equivalent to 70% of your salary at age 65. Assumptions:

  • Your salary and retirement income will increase 3% a year,
  • Interest rate is 10% until 55 and then 5% afterward

Question: What is the minimum contribution percentage that you must deposit to your retirement saving account? I prepared the following Excel worksheet.

alt

I don’t know the retirement saving percentage, so I just put 0% (I1). How to setup Solver? You can start Solver by clicking on the Solver icon on Data tab.

alt

The Solver dialogue would be shown.

  • The objective cell is $I$1 and you want to Minimize it.
  • Changing Variable Cell is also $I$1.
  • Make sure the balance for Age 65 – 85 is >= 0, so you setup the constraints to be $F$38:$F57 >= 0.
  • Choose Simplex LP Solving Method.

alt

After I click the Solve button, you would see the Solver Results dialogue.  Click the OK button.

alt

Here is the result.  You have to save at least 12.19% of your salary into your retirement saving.  You can verify the result by checking the balance at Age 85. It is $0.00.

alt

This is a very simple model. I only spent a few minutes to formulate the worksheet; but you can see it took me no time to setup the Solver. Financial planners / insurance brokers have software to do planning for their customers; but there would be situation that their software doesn’t know how to handle. If you know Microsoft Excel Solver, you would never run into such situation.

If you need to build a more sophisticated model, you can contact me at chan_a@algconsultings.com.  I am an Associate of Society of Actuaries (ASA) with extensive financial model experience.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

11 Responses to Excel 2010 -Solver

  1. myqeel says:

    Super intro to optimisation. How does this differ from Goal Seek in Excel though?

  2. Andrew Chan says:

    Goal Seek only allows 1 changing variable but Solver can support multiple changing variables. If you have 1 million dollars and want to invest into 5 different vehicle, each have its own yield rate and risk. You want to achieve 15% yield rate and minimum risk; then you should use Solver because you have 5 different variables to adjust.

  3. Pingback: Why Microsoft PowerPivot? | Technologies and your business

  4. Pingback: Are you a decision maker who often do planning, scheduling and optimization? « Technologies and your business

  5. Pingback: Nonlinear interpolation with Excel to construct US Treasury bond yield curve « Technologies and your business

  6. Pingback: Business analytic can turn your data to success « Technologies and your business

  7. Pingback: Wish you a Merry Christmas and a Happy Analytics Year! « Technologies and your business

  8. Pingback: Investment Portfolio Allocation–Excel Solver « Better and Faster Decisions

  9. Francisco says:

    Hi Guys, My solver in Excell 2010 perform with less variables than My old Solver in Excell 2007, I think it should be because of sometjhing badly set on my new Solver? I can not imagine that the newest vesrion is less powerfull than the last.
    Any suggestions?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: