How to Create Solver Answer Report in Excel

When you're trying to find the best possible outcome for a business problem, like maximizing profit or minimizing costs, Excel’s Solver is an incredibly powerful tool. After it finds a solution, however, you're left with a wall of numbers. The Solver Answer Report is what translates those numbers into clear, actionable insights. This guide will walk you through how to create, read, and understand the Answer Report to make smarter, data-driven decisions.

We’ll cover how to set up a problem in Solver, generate the report, and then break down exactly what each section of that report is telling you.

What Exactly Is the Excel Solver?

Before diving into the report, let's quickly recap what the Solver is. The Solver is a free Microsoft Excel add-in that helps you perform complex "what-if analyses" to find an optimal solution for a model. It's designed to solve problems where you need to adjust several variables to reach a specific goal, all while respecting certain limitations.

Every Solver problem has three key components:

• The Objective Cell: This is the single cell you want to maximize, minimize, or set to a specific value. Think of it as your ultimate goal, like "Total Profit."

• The Variable Cells: These are the cells that Excel can change to achieve the objective. They represent the decisions you can make, such as "number of units to produce" or "marketing budget per channel."

• The Constraints: These are the rules or limitations you must operate within. Examples include budget limits, resource availability (like labor hours or materials), or production capacity.

Solver works by systematically changing the values in your variable cells, constantly checking if the constraints are met, until it finds the best possible value for your objective cell.

First, Enable the Solver Add-in

By default, the Solver is not enabled in Excel. If you can’t see "Solver" under the "Data" tab, you'll need to activate it first. It's a quick, one-time setup.

For Windows Users:

1. Click on the File tab, then select Options at the bottom of the left menu.

2. In the Excel Options dialog box, click on Add-ins.

3. At the bottom of the window, you'll see a drop-down menu next to "Manage." Make sure it says Excel Add-ins and click Go...

4. Check the box next to Solver Add-in and click OK.

You’ll now find the Solver icon in the "Analyze" group under the Data tab.

For Mac Users:

1. Go to the top menu and click Tools.

2. Select Excel Add-ins... from the dropdown menu.

3. In the Add-ins dialog box, check the box next to Solver Add-in and click OK.

Just like on Windows, you will now see the Solver icon under your Data tab.

Setting Up a Sample Problem in Excel

The best way to learn about the Answer Report is to work through an example. Let's imagine you own a small woodworking shop that produces two popular items: handcrafted chairs and tables. You want to determine how many of each to produce next month to maximize your profit.

Here’s the information you have:

• Profit: You make $50 profit per chair and $120 profit per table.

• Labor: Each chair takes 2 hours of labor. Each table takes 5 hours. You have a total of 150 labor hours available.

• Wood: Each chair requires 4 board feet of wood. Each table requires 10 board feet. You have a total of 350 board feet in stock.

• Demand: Your sales data shows you can't sell more than 50 chairs in a month.

Let's set this up in an Excel sheet. Organize your data cleanly like this:

Your formulas will be:

• Total Profit (E4):=(B4*B5)+(C4*C5)This is our Objective Cell.

• Units to Produce (B5 and C5):These cells are blank to start. These are our Variable Cells.

• Labor Used (E7):=(B7*B5)+(C7*C5)

• Wood Used (E8):=(B8*B5)+(C8*C5)

Running Solver and Generating the Report

With your model set up, it's time to run Solver.

1. Go to the Data tab and click on Solver.

2. The Solver Parameters window will appear. Fill it out as follows:

   • Set Objective:Select cell $E$4 (your Total Profit).

   • To:Choose Max.

   • By Changing Variable Cells:Select the range $B$5:$C$5 (the units to produce).

3. Now, let’s add the constraints. Click the Add button:

   • Labor:Cell Reference: $E$7, Operator: <=, Constraint: $G$7(Labor Used must be less than or equal to Labor Available). Click Add.

   • Wood:Cell Reference: $E$8, Operator: <=, Constraint: $G$8(Wood Used must be less than or equal to Wood Available). Click Add.

   • Chair Demand:Cell Reference: $B$5, Operator: <=, Constraint: 50(Number of chairs produced must be less than or equal to 50). Click Add.

   • Non-Negativity:Cell Reference: $B$5:$C$5, Operator: >=, Constraint: 0(You can't produce a negative number of products). Click OK this time.

4. Ensure the box for "Make Unconstrained Variables Non-Negative" is checked. This does the same thing as our last constraint, but it's good practice.

5. For the "Select a Solving Method" dropdown, choose Simplex LP. This method is ideal for linear problems like ours, where the relationships between variables are straightforward.

6. Click Solve.

After a moment, the "Solver Results" dialog box will pop up, confirming that Solver has found a solution. Now, the important part: in the "Reports" box on the right, click on Answer to highlight it, and then click OK.

Excel will instantly update your variable cells with the optimal values and create a new worksheet named "Answer Report 1."

How to Read and Interpret the Solver Answer Report

The Answer Report is cleanly organized into three sections. Let's break each one down to understand the story it tells.

Here's what your generated report might look like:

1. Objective Cell Report

This tiny section at the very top gives you a high-level summary of your goal.

• Original Value:This was the value in your objective cell before running Solver. In our case, it was $0 because we hadn't produced anything yet.

• Final Value:This is the optimized value for your objective. This is the main answer you were looking for! In our example, the maximum possible profit is $3,850.

2. Variable Cells Report

This section provides the details on the decisions Solver made to achieve the final value in the objective cell. It tells you exactly what to do.

• Cell & Name:These columns identify the decision variables. Naming your cells in Excel (using the Name Box to the left of the formula bar) makes this report much easier to read.

• Original Value:The initial values for your decision variables (chairs and tables), which were 0.

• Final Value:This is the core solution. Excel is telling you the optimal mix is to produce 25 chairs and 20 tables. This is your action plan.

• Integer:This just confirms if you constrained your variables to be whole numbers (Integers) or not. In our case, we didn't add this constraint, but Solver found an integer solution anyway.

3. Constraints Report

This is often the most insightful part of the report. It tells you which resources limited your results and which ones you had left over.

• Cell Value:This shows the final value for the left side of your constraint formula. For the Labor constraint, the cell value is "150 Hours," meaning we used exactly 150 hours of labor.

• Formula:Shows the formula of the constraint (e.g., $E$7<= $G$7).

• Status:This column is critical. It can have two values:

  • Binding:A binding constraint is one that has been fully maxed out and is preventing you from achieving even more profit. The solution pushed right up against this limit. In our example, both the Labor and Wood constraints are "Binding." We used every single labor hour and every single board foot of wood.

  • Not Binding:A non-binding constraint is one that did not limit the solution. You had more of this resource than you needed for the optimal outcome.

• Slack:Slack is the margin or "leftover" amount for a constraint. It quantifies the difference between the final cell value and the constraint limit.

  • For a Binding constraint, the slack is always 0. This confirms you have nothing leftover. We have 0 slack for both labor and wood.

  • For a Not Binding constraint, slack tells you exactly how much wiggle room you have. If our chair demand constraint were, for example, met by producing 25 chairs when the limit was 50, the status would be "Not Binding" and the Slack would be 25 (50 - 25).

From this Constraints section, we learn something extremely valuable: labor and wood are our bottlenecks. If we want to increase our profit beyond $3,850, we need to find a way to get more labor hours or more wood. Chair demand is not a problem at all, we have plenty of slack there.

Final Thoughts

The Solver Answer Report translates a complex optimization model into a simple, three-part story. It gives you the optimal objective, the final decision values you need to implement, and a crystal-clear picture of which constraints are holding you back. Learning to read it is essential for moving beyond just finding an answer to truly understanding why it's the right answer.

Excel Solver is phenomenal for these kinds of specific, well-defined what-if analyses within a spreadsheet. But often, the data you need for these decisions lives outside of Excel - in your Google Analytics, Shopify store, or CRM. Instead of manually pulling CSVs to update your model, we created Graphed to help connect to all your data sources and create real-time reports and dashboards automatically. You can just ask questions in plain English, and Graphed uses AI to build the visualizations you need, giving you back time to focus on making bigger business decisions instead of wrestling with spreadsheets.