MVP #67: Using Solver To Find How To Make The Most Of Your Resources

Solver is an amazing tool. It's a decision analysis tool that lets you set your goal and set the conditions to adhere to while searching for the best way to achieve your goal.

Let's see an example I have set up. 

Say we are a new company and have a budget for payroll which we want to maximize to the highest number of staff possible. Below are the details.

So we want Excel to calculate how many Senior Staff, how many Mid-level Staff, how many Junior Staff and how many Fresh Graduate we can employ with the annual budget of N105 million for payroll? And it should take into considerations the following constraints:

• At least one of every staff level
• Maximum of 3 Fresh Graduate
• Each Senior Staff must have exactly 2 Mid-level Staff to manage
• Mid-level Staff must be more than 3
• Each Mid-level Staff must have at least 2 Junior Staff to manage

It's the kind of task you'll use Solver for.

Solver is in Data Menu. 

If you don't have it in yours, follow the screenshots below to enable it. Goto Excel Options, Add-ins, Excel Add-ins and enable Solver.

Now you will be able to see under Data menu.

Launch it and set the goal (objective of maximum staff) and the conditions to adhere to.

See the screenshots below for how-to.

And that's all! See the result below.

Congrats! You just did what is referred to as linear programming. You can try include more conditions or use if for your business decision analysis.