Conducting a sensitivity analysis in Google Sheets allows you to evaluate how changes in one or more input variables affect an output variable. Here's how to do it:
First, ensure your spreadsheet models the scenario you're interested in analyzing. This typically involves:
Input Variables: Clearly define and isolate the cells where your input variables are entered. These are the factors you'll vary to see how they affect the outcome.
Output Variable: Identify the cell with the output variable. This is the result affected by changes in the input variables.
For example, you are selling a product and want to understand how changes in the sales price and cost per unit affect your profit. Your base model includes:
We can perform a sensitivity analysis to see how varying the sales price from $45 to $55 (in $1 increments) and the cost per unit from $25 to $35 (in $1 increments) impacts the profit.
This is our base model:
Next to or below your model, set up a data table to systematically vary the input variables and record the impact on the output variable.
In the first column of the data table, list different values for the input variable you want to test. These could be different cost estimates, pricing levels, demand forecasts, etc.
In the first row above the data table, reference the output variable's cell. This sets up the table to capture how changes in the input affect this output.
For our example, we can use a formula to calculate the profit based on the sales price in that column and the base cost per unit. For example, in D2, the formula would be =(D$1-B3)*B1. Copy this formula across the range D2 to N2.
Consider creating charts or graphs to visualize how changes in the input variables affect the output. This can help identify thresholds or levels of sensitivity in your model.
We hope that this article has helped you and given you a better understanding of how to perform sensitivity analysis in Google Sheets. If you enjoyed this article, you might also like our articles on how to insert a degree symbol in Google Sheets and how to calculate confidence intervals in Google Sheets.
