How to Perform What-If Analysis in Google Sheets

What-If Analysis in Google Sheets is a process that allows you to change certain input values to see how they impact the output values in your spreadsheet. This can help you make data-driven decisions or forecast outcomes based on different scenarios. You can perform What-If Analysis in Google Sheets using Data Validation, Goal Seek, and Scenario Manager (via an add-on).

Here are the steps to perform What-If Analysis in Google Sheets:

1. Data Validation: Data Validation is a feature that helps you control the data entered in a specific cell or range. You can create a drop-down list with different input values, which can help you quickly change the input and analyze the impact on the output.

• Select the cell where you want the drop-down list to appear.
• Click on "Data" in the menu and select "Data validation."
• In the "Criteria" drop-down, choose "List of items" or "List from a range."
• Enter the list of items separated by commas or the range of cells containing the list.
• Click "Save."

Now you can select different values from the drop-down list and see how they affect the output.

2. Goal Seek: Goal Seek is a built-in feature in Google Sheets that allows you to find the input value needed to achieve a specific output value. It's suitable when you have a target value and want to know the input value required to reach that target.

• Click on "Extensions" in the menu and select "Goal Seek."
• If you don't have the Goal Seek add-on, click on "Get add-ons" and search for "Goal Seek" to install it.
• In the Goal Seek dialog box, enter the cell reference containing the output value (the value you want to achieve) in the "Set cell" field.
• Enter the target value in the "To value" field.
• Enter the cell reference containing the input value (the value you want to change) in the "By changing cell" field.
• Click "Start" to let Goal Seek find the input value needed to achieve the target value.

3. Scenario Manager (via an add-on): Scenario Manager is an add-on for Google Sheets that allows you to create and compare different scenarios in your spreadsheet. It's useful when you want to see the impact of multiple input values on the output.

• Click on "Extensions" in the menu and select "Get add-ons."
• Search for "Scenario Manager" and install the add-on.
• Once installed, click on "Extensions" again, hover over "Scenario Manager" and click "Start."
• In the Scenario Manager dialog box, click "Add" to create a new scenario.
• Enter a name for the scenario and select the cells containing the input values you want to change.
• Enter the new values for each input value and click "Save."
• Repeat this process to create additional scenarios.
• To compare scenarios, click on the checkboxes next to each scenario and click "Show."

Now you can analyze and compare the impact of different scenarios on the output values in your spreadsheet.

Example

Let's say you're analyzing the sales of a product based on its price, and you want to find out how different prices affect the total revenue. Here's an example of how to perform What-If Analysis in Google Sheets using Data Validation:

1. Create a sample spreadsheet with the following columns: Price, Quantity Sold, and Total Revenue.
2. In cell A2, enter the current price of the product (e.g., $10).
3. In cell B2, enter the quantity sold at that price (e.g., 100).
4. In cell C2, enter a formula to calculate the total revenue (e.g., "=A2*B2").
5. Select cell A2 and create a Data Validation drop-down list with different prices (e.g., 8, 10, 12, 14, 16).
6. Now, you can select different prices from the drop-down list in cell A2 and see how they affect the total revenue in cell C2.

By analyzing the impact of different prices on the total revenue, you can decide the optimal price for your product.