Don’t forget to share this post

Google Sheets has no built-in what-if analysis tool like Excel. However, this doesn’t mean you can’t perform this type of analysis in Google Sheets. Using a free add-on like Goal Seek, you can carry out what-if analyses to explore and anticipate the consequences of possible future changes.

In this article, you will learn the difference between what-if and sensitivity analyses and how to use Google Sheets’ Goal Seek add-on to run what-if analyses on multiple scenarios. To learn more about the Goal Seek add-on, check out our article on How to Use Goal Seek in Google Sheets.

What’s the Difference Between What-If and Sensitivity Analysis?

In what-if analysis, various situations or “scenarios” are contemplated through the manipulation of variables. Among others, there are two very popular types of what-if analysis: scenario and sensitivity analysis.

In sensitivity analysis, the focus is on the effects of changes to one specific variable; this type of analysis explains how variables (dependent) are affected based on changes made in another variable (independent). In scenario analysis, the focus is on the effects of changes to multiple variables; here, the different scenarios correspond to these changing variables. For example, a worst-case scenario could involve higher-than-average costs and minimum sales.

As you can see, both types of what-if analysis can be very valuable, allowing you to prepare for change or take calculated risks. In the next section, you will learn how to carry out what-if analyses in Google Sheets.

How To Use Goal Seek in Google Sheets
How To Use Goal Seek in Google Sheets

Goal Seek for Sheets is a powerful add-on for Google Sheets for data analysis. Here’s how to use Goal Seek in Google Sheets.

READ MORE

What-If Analysis with Goal Seek in Google Sheets

By setting up different scenarios in your spreadsheet, you can use Goal Seek for what-if analysis. Whether you carry out sensitivity or scenario analysis will depend on how many variables you manipulate and to what extent you test individual ones.

Follow the steps below to install the Goal Seek add-on for Google Sheets, set up various scenarios, and use Goal Seek to modify multiple variables.

Install the Goal Seek Add-on in Google Sheets

Like with most Google products and features, you simply need a Google account and a few minutes. Follow the steps below to find and install Goal Seek in Google Sheets.

  1. 1. Open Google Sheets and go to Extension > Add-ons > Get add-ons.
How To Perform What If Analysis in Google Sheets Get Add ons
How To Perform What-If Analysis in Google Sheets - Get Add-ons
  1. 2. In the Google Workspace Marketplace, search for Goal Seek, click on the add-on, and then “Continue.”
How To Perform What If Analysis in Google Sheets Goal Seek Add on
How To Perform What-If Analysis in Google Sheets - Goal Seek Add-on
  1. 3. You will see a pop-up asking you to grant Goal Seek permission to access your account. Click “Allow” to finish installing the add-on.
How To Perform What If Analysis in Google Sheets Allow Access
How To Perform What-If Analysis in Google Sheets - Allow Access
  1. 4. Once the add-on is successfully installed, you’ll see a confirmation message like the one below.
How To Perform What If Analysis in Google Sheets Add on Installed
How To Perform What-If Analysis in Google Sheets - Add-on Installed

Set Up What-if Scenarios

In the example spreadsheet below, you can see three copies of the initial dataset. Here, the goal is to reach profits of $20000 and to analyze different scenarios.

In the first, you’ll see the effects of changing the selling price; in the second, the consequences of changing the number of units sold; and, in the third, the effects of changing the unit cost.

How To Perform What If Analysis in Google Sheets What if Scenarios
How To Perform What-If Analysis in Google Sheets - What-if Scenarios

What-If Analysis in Google Sheets with Goal Seek

You can use Goal Seek to carry out a what-if analysis of multiple scenarios by running it on multiple copies of your data, where you can set your goal and specify which variables to change.

Goal Seek Required Parameters

There are three required parameters in Goal Seek:

  • Target cell: This is the cell containing the formula that will link other cells together. Goal Seek will ensure that the result obtained from this formula corresponds to the value you set as your target.
  • Target value: This is the value we want to obtain in our target cell.
  • Changing cell: This is the cell that Goal Seek can manipulate to reach the target value in the target cell.

Goal Seek Optional Parameters

You can also configure some optional parameters in Goal Seek:

  • Maximum iterations: the default value is 200. You can increase this number, so that Goal Seek makes more attempts to find a solution, but this will also depend on the time limit you set.
  • Tolerance: the default value is 0.0001. You can increase this number if your solution doesn’t need to be this precise.
  • Maximum time limit: the default value is 120 seconds. You can increase this number for more complex calculations. Calculations will stop when this limit is reached, regardless of the “Max Iterations” setting.

How to Use GOOGLEFINANCE Function in Google Sheets?

Import current or historical financial market data from Google Finance & monitor real-time. Here's how to use the GOOGLEFINANCE function in Google Sheets

READ MORE
How to Use GOOGLEFINANCE Function in Google Sheets
  1. 1. Go to Extensions > Goal Seek > Open.
How To Perform What If Analysis in Google Sheets Open Goal Seek
How To Perform What-If Analysis in Google Sheets - Open Goal Seek
  1. 2. You will see the Goal Seek sidebar on the left of your spreadsheet, including “Settings”, “Solve Status”, and “History”.
How To Perform What If Analysis in Google Sheets Goal Seek Sidebar
How To Perform What-If Analysis in Google Sheets - Goal Seek Sidebar
  1. 3. Click on the cell containing the profit formula, then click on the grid icon beside “Set Cell” in the sidebar. If you place your cursor over the grid icon, you will see the text “Capture selected cell”, as in the screenshot below.
How To Perform What If Analysis in Google Sheets Set Cell
How To Perform What-If Analysis in Google Sheets - Set Cell
  1. 4. Next, type in the “Target value” for the “Set Cell”. Here, “20000”.
How To Perform What If Analysis in Google Sheets Target Value
How To Perform What-If Analysis in Google Sheets - Target Value
  1. 5. Click on the cell containing the value you want Goal Seek to change, then click on the grid icon beside “By Changing Cell” in the sidebar. Here, the selling price for Scenario 1, cell B14.
How To Perform What If Analysis in Google Sheets By Changing Cell
How To Perform What-If Analysis in Google Sheets - By Changing Cell
  1. 6. To change the number of iterations, the tolerance, and/or the time limit, click on “Options”. Here, I will use the default values.
How To Perform What If Analysis in Google Sheets Options
How To Perform What-If Analysis in Google Sheets - Options
  1. 7. Once everything is ready, click “Solve.” With the default options used above, it will take a maximum of two minutes to calculate the solution. If Goal Seek cannot find a solution, try increasing the tolerance or the time limit and number of iterations.
How To Perform What If Analysis in Google Sheets Solve
How To Perform What-If Analysis in Google Sheets - Solve
  1. 8. When Goal Seek finds a solution, you will see a pop-up like the one below. You can see the solution directly in the cell or the “Solve Status” at the bottom of the sidebar.
How To Perform What If Analysis in Google Sheets Goal Seek Complete
How To Perform What-If Analysis in Google Sheets - Goal Seek Complete
  1. 9. Next, let's configure the settings for Scenario 2. First, select the “Set Cell” and the “Target Value”. Here, the “Set Cell” contains the profit formula in cell E20, and the target value is shown in “To Value”, i.e. 20000.
How To Perform What If Analysis in Google Sheets Scenario 2 Target
How To Perform What-If Analysis in Google Sheets - Scenario 2 Target
  1. 10. Once you have set both values, click on the cell with the changing value. Click “Solve” when you’re happy with the settings.
How To Perform What If Analysis in Google Sheets Scenario 2 Changing Cell
How To Perform What-If Analysis in Google Sheets - Scenario 2 Changing Cell
  1. 11. You will see the “Goal Seek Complete” pop-up again.
How To Perform What If Analysis in Google Sheets Scenario 2 Complete
How To Perform What-If Analysis in Google Sheets - Scenario 2 Complete
  1. 12. Finally, configure the settings for Scenario 3, following the same steps as before, and click “Solve” to finish.
How To Perform What If Analysis in Google Sheets Scenario 3 Settings
How To Perform What-If Analysis in Google Sheets - Scenario 3 Settings
  1. 13. That’s it. The solution for Scenario 3 is available, as shown below.
How To Perform What If Analysis in Google Sheets Scenario 3 Complete
How To Perform What-If Analysis in Google Sheets - Scenario 3 Complete
  1. 14. You should see the results obtained with Goal Seek for all three scenarios; the changing cells have a green background, and the target cells have a red one.
How To Perform What If Analysis in Google Sheets Scenario 3 Complete 2
How To Perform What-If Analysis in Google Sheets - Scenario 3 Complete

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

Conclusion

You can perform what-if analysis directly in Google Sheets using Goal Seek. You can set up different scenarios with different goals and choose any variable you want as the changing cell. As Goal Seek keeps a history of attempts, you can always return to previous solutions.

After reading this article, you know how to install the Goal Seek add-on, set up scenarios in Sheets, and analyze them using Goal Seek. To learn more about Goal Seek, check out How to Use Goal Seek in Google Sheets.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jul 6 2022, Updated Nov 11 2022