24 Solver

The Solver tool is an add-in program available in Excel that allows users to perform additional what-if analysis, by altering different variables in a spreadsheet in order find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. For example, what is the minimum number of sales you’d need to make to cover the cost of an expensive piece of business equipment?

Solver has three parts to it—a target value, variables that it can change to reach that value, and constraints that Solver has to work in.  Solver adjusts the values in the variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

To install Solver, go to File > Options and in the Excel Options window choose the Solver Add-in. If necessary, select Excel add-ins from the Manage drop-down menu at the bottom of the window, then press the Go button. In the Add-ins window, mark the checkbox next to Solver Add-in and click OK. The Solver option should appear in an Analysis group under the Data tab.

Let’s use Solver to determine a common staffing scheduling dilemma. You manage a small business and need to determine the optimal hours per week to schedule each employee given a set of constraints. Open the following worksheet (try using the Insert Data From Picture feature) and study the formulas in column D & F.

Consider the following constraints:

• Ensure that no employee works more than 40 hours per week.
• Fractional cakes are not acceptable.

• Select the range B9:F9 and click the AutoSum button.
• Select cell D9 and use the Solver feature to determine how many hours each employee needs to work to produce at least 750 cakes in a week. (Current output is barely 600 cakes)
• In the Solver window that opens, add the objective, variables and constraints as shown below. 

• Choose Solve. The Solver Results window should eventually display…
  
• Choose the Keep Solver Solution option and click OK. Your updated spreadsheet should now display the solution to your scheduling dilemma.