One of the most common questions asked about Excel is how to use different functions. When people are new to Excel, they can be overwhelmed by all the formulas and Excel functions available. Figuring out which ones are best for your specific needs can be tough.

The 6 Scenario modeling functions you need to know in Excel are: Goal Seek, Solver, What-If Analysis, Data Table, Pivot Tables, and Scenario Manager. These six powerful tools will allow you to model many different scenarios through a single spreadsheet. This article will teach you how to use these six Excel functions so that you can make better decisions for your company’s future.

Scenario modeling is a powerful problem-solving technique that helps you understand how different assumptions can result in different outcomes. It’s a useful tool for understanding the ramifications of decisions and choices.

 

What is scenario modeling?

Solver, What-If Analysis, and Data Table are all useful for a single point in time. These functions can be used to analyze a decision on a certain assumption. For example, you may want to know what the cost will be if you spend more or less on advertising.

Goal Seek is especially helpful when you’re trying to find an answer that satisfies specific parameters. If you want to know the break-even point for your business, Goal Seek can help you locate it by changing variables until you reach one that satisfies those requirements.

Pivot Table and Scenario Manager are both useful for looking at different sets of assumptions over a period of time. For instance, Pivot Table lets you look at how different decisions affect each other over time. With Scenario Manager, you can analyze how different decisions would have played out historically!

 

Goal Seek

Goal Seek is an Excel function that takes three inputs: a cell you want to solve for, a desired result, and a goal value. It then calculates the formula in the specified cell until it reaches the goal value.

This is helpful when you want to find out how your company would be affected by different variables, such as price points or quantities. For example, you might know that for every 10 units sold of your product, your company earns $10 more. You might also know that the variable cost per unit is $5.

If you input 10 as your goal value and want to find out what price point will allow your company to break even (a 0 profit margin), you can input those two numbers into Goal Seek and Excel will show you what the price needs to be.

 

Solver

The goal of Solver is to find the optimal solution to a given problem. The solver will find the best value for an unknown variable, or constants, to satisfy a defined equation or inequality.

For example, you could set up the following equation in Excel:

=6x+8y-5z

The goal would be to find the values of x, y and z that result in a total of 25.

In order to solve this problem with Excel’s solver function, click on “Solver” under the “Tools” tab. Then click on “OK.” In the Case 1 field, type in 6, 8 and 5 for x, y and z respectively. Clicking on “OK” will give you your answer: -2 for x and -1 for y—this means that x is 2 and y is 1 in order to get a total of 25.

 

What-If Analysis

What-If Analysis is a powerful function that allows you to see what would happen if you changed one of your assumptions.

For example, if you have an invoice for $100 with a 20% discount, What-If Analysis can tell you how much the invoice would be without the discount. You can then decide whether or not the discount is worth it for this customer.

What-if analysis can also help you optimize your marketing spend by analyzing how different campaign strategies affect performance metrics. For example, if your goal is to increase conversion rates, What-If Analysis can tell you which marketing channels are best for your business and which ones aren’t worth the investment.

 

Data Tables

Data Tables are a common tool that use conditional formatting to help you see how changing one variable will affect the rest of the data.

One example of a Data Table is a Sales Report. The table below shows how much revenue Excel can generate from different price points for a product.

The blue cells show the number of units sold at each price point, and the green cells show revenue generated at each price point.

In this example, you can see that if Excel prices their product at $10, they can sell 10 units and generate $100 in revenue. This would be true for any price point below $10 as well. However, notice what happens when Excel prices their product at $11? They can only sell 9 units and generate $99–a difference of $1! This is because an increase in price results in lower demand for their product.

 

Pivot Tables

Pivot Tables are one of the six scenario modeling functions you need to know in Excel. They allow you to display summarized data from a large set of data.

In this article, we will be using Pivot Tables as an example to demonstrate how they work. In order to create a Pivot Table, first create a table with four columns and five rows. In the first row, input the following: month , salesperson , product category , and year. Next, input your data into each column below it.

Once all of your data is inputted, create a Pivot Table by going to Insert > Pivot Table > OK. The next step is creating your pivots or what-if scenarios. You can do this by selecting any of the cells in either the “Sum of Sales” column or “Sum of Sales” row and then clicking on any of the four buttons at the top right corner: Filters > Add Filter > Top 10 Items > Clear All Filters > Clear Top 10 Items.” These four buttons represent different possible results based on your assumptions – these are your various pivots or what-if scenarios.

 

Scenario Manager

Scenario Manager is a powerful Excel tool that will allow you to create multiple scenarios. Scenarios are sets of calculated values based on different inputs.

For example, using Scenario Manager, you could model the impact of price changes on your company’s sales. You can then analyze the output for each scenario to determine which price change would result in the most profitable outcome.

Using this easy-to-use function, you can create multiple scenarios based on different assumptions about potential outcomes. This will help you make better decisions for your company’s future.

 

Create a Scenario

With scenario modeling, you create a spreadsheet that calculates different outcomes under different assumptions. For example, you might want to calculate the profitability of your business under two different scenarios: one for if your company’s prices go up and another if they stay the same.

To do this in Excel, we’ll create a data table and then use what-if analysis to solve for our desired result.

 

Add additional Scenarios to your Model

Adding additional scenarios to your model will help you understand the impact of different assumptions.

One of the most common problems people have with scenario modeling is they don’t think about what their outputs will be. This can make it difficult to see how changing an input impacts the output. For example, what if you were trying to figure out how many widgets you would need to produce per day? To answer that question, you would need to know the demand – or how many orders for widgets are expected per day.

To set up your scenario, type in the two inputs:

Input 1: The demand for widgets

Input 2: The number of days in a month

Then go up and click on Data Table on the Ribbon. Select Add Row Twice.

This will give you two columns in your data table, so that you can input your two different demands (one high and one low). Then use Goal Seek to find out how many widgets you need to produce every day given those demands.

 

Compare scenarios with each other

When you’re trying to compare different scenarios, Pivot Tables are a great way to do it.

Pivot Tables allow you to take one set of data and compare it to another set of data. Pivot Tables are very useful for comparing different scenarios, because they allow you to see the effects on one area when something changes in another area.

For example, let’s say that your company is considering expanding their business to include selling frozen yogurt. But, before you make the decision, you want to know how this might affect the average annual revenue per store.

You could use a Pivot Table in Excel to explore how an expansion would affect total yearly revenue or monthly revenue, based on different assumptions about customer numbers or product prices. The pivot table will show the effect on each scenario side by side in a single spreadsheet!