What-If Analysis and the Solver Tool in Excel

In this edition, we’ll dive into one of the most powerful tools Excel offers: What-If Analysis, along with an introduction to the Solver Tool. Both of these features can significantly enhance decision-making by allowing users to test different scenarios and find optimal solutions.

What is What-If Analysis?

What-If Analysis allows you to explore different outcomes by changing the input values in your formulas. Excel provides three types of What-If Analysis:

1. Scenario Manager
2. Goal Seek
3. Data Tables

Each of these tools can be used to answer important business questions, such as forecasting revenues or analyzing the impact of changes in price on profits.

Let’s take a closer look at each one.

1. Scenario Manager

Scenario Manager is a powerful tool that allows you to create different sets of input values (scenarios) and compare them side-by-side. For example, you could create a scenario to analyze the impact of three different budget plans: a best-case, worst-case, and most likely scenario.

Example: Suppose you are projecting annual revenue for the next year. You can use Scenario Manager to create different revenue estimates based on varying sales growth rates (e.g., 5%, 10%, and 15%). Once created, you can easily switch between these scenarios to view how each affects your total revenue.

Steps:

1. Go to the Data tab.
2. Click on What-If Analysis and choose Scenario Manager.
3. Define the changing cells and the different values you want to test.

2. Goal Seek

Goal Seek helps you find the exact input value needed to achieve a specific outcome. It’s perfect when you know your goal but aren’t sure what input will get you there.

Example: Imagine you own an online store and want to find out how many products your sales team needs to sell per hour or how many salespeople you need to make a $50,000 profit in a month after covering all costs. Goal Seek can help you figure out the exact number of products each salesperson needs to sell per hour or how many salespeople you need to reach your profit goal.

Steps:

1. Go to the Data tab.
2. Click on What-If Analysis and select Goal Seek.
3. Set the desired outcome (e.g., $50,000 in Profit) and define which cell Excel should adjust to reach that goal.

3. Data Tables

Data Tables let you visualize the impact of changing one or two variables in your formula. This is extremely useful when you want to create a sensitivity analysis or explore the outcomes of different combinations of inputs.

Example: If you're evaluating the impact of different monthly payment amounts on the duration of a loan, you can construct a data table like the one shown. This table illustrates how varying monthly payments affect the total number of payments required to fulfill an $8,000 loan with a monthly-compounded interest rate of 5%. The accompanying graph provides a visual representation, demonstrating the inverse relationship between monthly payment amounts and the number of payments needed.

The Solver Tool: Optimizing Your Solutions

The Solver Tool takes What-If Analysis one step further. It finds the optimal solution by adjusting multiple variables to meet a defined goal, subject to certain constraints.?

To add the Solver button to the Data ribbon in Excel, follow these steps:

1. Open Excel and click on the File tab in the top-left corner.
2. Scroll down and select Options to open the Excel Options dialog box.
3. In the Excel Options window, click on Add-ins from the left-hand menu.
4. At the bottom of the window, you will see a Manage dropdown box. Select Excel Add-ins from the list and click Go.
5. In the Add-Ins box, check the box next to Solver Add-in and click OK.
6. Once Solver is added, go to the Data tab on the ribbon, and you will see the Solver option on the right side, under the Analysis group.

Now you can use Solver to run optimization and constraint-based analyses in Excel!

Example: Imagine you own an online store and want to determine how to achieve a $50,000 profit by adjusting factors like the number of salespeople, how many products they can sell per hour, and how many hours they work per day. Using Solver, you can set up an optimization model to find the best combination of these variables while meeting specific constraints.

In the attached example, Solver is used to optimize the number of salespeople, their work hours, and sales rates to reach the target profit. The constraints ensure that the number of salespeople is less than equal to 18, working hours are less than equal to 8 hours per day, and weekly working days do not exceed 6.

Solver helps determine the best possible mix of sales rates and workforce that allows you to reach the $50,000 profit goal while minimizing costs, such as salaries and working hours, and maximizing productivity.

Steps:

1.???? Go to the Data tab.

2.???? Click on Solver.

3.???? Define the objective (e.g., maximize profit).

4.???? Set constraints (e.g., limited resources like raw materials or labor hours).

5.???? Select the changing variables (e.g., production quantities).

6.???? Run Solver, and it will suggest the optimal solution based on your inputs.

Conclusion

Both What-If Analysis and the Solver Tool are essential for making data-driven decisions in Excel. By leveraging these tools, you can forecast scenarios, find the right input values for a target result, and even optimize complex systems with multiple variables and constraints.

Subscribe now for exclusive tips, tutorials, and expert insights to level up your Excel game. Don't miss out on mastering powerful tools and boosting your productivity!