Row Zero is the best spreadsheet for big data. Try for free →

Sensitivity Analysis in a Spreadsheet with Python

2025-08-29 // Tom Ward, Software and Analytics Consultant

python sensitivity analysis in spreadsheet Sensitivity analysis is a commonly used method of business planning and forecasting. Spreadsheets provide a nice UI for sensitivity analysis because you can easily see input and output data and easily change assumptions and view the instant results. However, spreadsheet sensitivity analysis in Excel is limited by the number of variables you can use, size of your data, and methods you can use for analysis. Advanced statistical software, like R, Python, or SAS enable more powerful and flexible sensitivity analysis, but can be difficult to work with and collaborate on with users who aren’t used to these tools.

In Row Zero, you can view and collaborate on sensitivity analysis in a spreadsheet built for big data. Since Row Zero workbooks come with a native Python environment alongside the spreadsheet, you can build powerful, flexible sensitivity analyses using the Python ecosystem.

In this guide, we'll show how to do sensitivity analysis with Python in a Row Zero spreadsheet. You can view an example of sensitivity analysis or get started in a new spreadsheet for free.

Table of Contents

What is sensitivity analysis?

Sensitivity analysis is a method used to determine how changes in inputs to a model impact the outputs of the model. This could be something as simple as plugging in different interest rates into a loan calculator to see how they would affect your monthly payment for the loan or something as complex as using simulations to determine how changes in millions of factors impact a weather forecasting model.

Common use cases for sensitivity analysis

Sensitivity analysis is widely used across a lot of domains. It’s used in business planning and strategy for determining how changes in input costs will impact overall profitability and how changes in prices, customer demand, and marketing spend will impact sales. It’s used in finance for determining how interest rates and growth rates impact stock prices and for assessing changes in market conditions. Sensitivity analysis is also commonly used in manufacturing and engineering for forecasting production costs, scrap rates, throughput and capacity of factories, and energy usage.

Sensitivity Analysis in Row Zero

Row Zero is a powerful tool for sensitivity analysis - you get the simplicity of a spreadsheet UI to view all of your assumptions, inputs, and outputs and you get the power and flexibility of a built-in python environment. You can also share and collaborate on workbooks so that one set of users can use a familiar spreadsheet view to test assumptions and plan scenarios and another set of users can build powerful models with python and set up live data connections to data living in Snowflake, Databricks, Postgres or other large data repositories.

One common question to answer with sensitivity analysis is “how will various combinations of my inputs affect my output?” Spreadsheets are great for visualizing this in a one-way or two-way data table. With a one-way data table, you can show a range of input values and their associated output value. With a two-way data table, you can show ranges of two input values and the output values that result from each combination of possible input values.

Since Row Zero workbooks let you define spreadsheet functions using Python, there are many ways you can create a two-way data table for sensitivity analysis. One easy way is by leveraging the Python numpy module’s meshgrid function. This function makes it easy to return a table of output values, given 2 ranges of input values. You can then view the output table, no matter how large, in the spreadsheet view in the workbook.

Another common question to answer with sensitivity analysis is “what input values do I need to get my desired output?” This is also known as an optimization problem - you want to find optimal input values that, subject to constraints, minimizes or maximizes a function. Spreadsheets often have specific features for solving these types of problems, like Excel’s Goal Seek or Solver add-in. However, the Python ecosystem has tools for solving optimization problems that are much more flexible and powerful, such as Scipy’s optimize package.

Example sensitivity analysis in Row Zero

Here is a working example of a sensitivity analysis in a spreadsheet in Row Zero. The workbook contains two tabs:

Two-Way Data Table

The Two-Way Data Table tab shows an example of a simple break-even analysis table with fixed cost, profit per unit, and units sold per month. You can change the values in the monthly profit calculator and the sensitivity analysis inputs will automatically create sensible ranges to explore the impact of similar profit per unit and units sold per month values. sensitivity analysis spreadsheet

You can click on the “Code” button in the top right part of the screen to show the Python code used to create the table, which uses the numpy meshgrid function. python sensitivity analysis in spreadsheet

Optimization Analysis

The Optimization tab shows an example of a simple mortgage calculator that allows you to see the maximum loan amount you can get for a desired monthly payment. optimization solver in spreadsheet You can click on the “Code” button in the top right part of the screen to show the Python code calculating the maximum value, which makes use of Scipy’s optimize package. python optimization in spreadsheet

You can find the Python code used for these sensitivity models in the Appendix below.

Sensitivity Analysis in Excel and Sheets vs Row Zero

You can build your own sensitivity analysis in any spreadsheet but there are key differences, especially when doing more advanced sensitivity analysis or working with large datasets.

Sensitivity analysis in Excel

“What-If Analysis” is a key set of features for sensitivity analysis in Excel. There are 3 features that are available in Excel’s What-If Analysis: Data Table, Goal Seek, and Scenario Manager. Data Table is a tool for visualizing output values that result from different combinations of input values. Goal Seek is a one-variable solver tool that provides possible input values that will result in a target or “goal” value. Scenario Manager lets you save different sets of input values (scenarios) and switch between them in your workbook.

Sensitivity analysis in Google Sheets

You can do basic sensitivity analysis in Google Sheets, but it isn’t as fully featured as other tools and suffers from the same performance constraints as Excel. Sheets does not have versions of Excel’s What-If analysis features like Data Table, Goal Seek, and Scenario Manager and it lacks the big data performance and native python features of Row Zero.

Limitations of sensitivity analysis in Excel and Google Sheets

While Excel and Google Sheets allow for basic sensitivity analysis, there are downsides and limitations. Performance can quickly degrade with two-way data tables with even a few hundred data points, particularly if you are using complex formulas. Excel’s specific sensitivity analysis tools, Data Table and Goal Seek, are both limited. Data Table only supports two variables and anything higher dimension requires macros or complex formula nesting. Goal Seek is a very limited optimization tool - it only allows for one variable and one target and also doesn’t allow you to specify a method for optimization. In addition, the limits of Excel (1,048,576 rows) and Google Sheets (10 million cells) make it challenging to analyze big data, and large complicated models may become slow or freeze.

Advantages of sensitivity analysis in Row Zero

Row Zero offers several unique features for advanced sensitivity analysis:

  1. Big data power - Row Zero is specifically built for big data and supports much larger datasets than Excel and Google Sheets.
  2. Native Python - Row Zero's built-in Python window lets you import Python packages like pandas, numpy, and scipy and leverage the power and flexibility of Python for sensitivity analysis in your spreadsheet. You can easily build a sensitivity analysis with many variables to accurately model real-world applications.
  3. Connected to your data - Row Zero has built-in connectors to your data warehouse, so you can build powerful models on live data and automatically update your sensitivity analysis with new data. You can also write-back your output to the data warehouse.
  4. It's a spreadsheet - Unlike advanced statistical software solutions, Row Zero is a full-featured spreadsheet so you can easily view the raw data, variables, and outputs, and build your sensitivity analysis as part of a larger spreadsheet model that leverages features like charts, pivot tables, and formula functions. You can also easily share and collaborate with teammates, given the widespread familiarity with spreadsheets.

Conclusion

Row Zero makes it easy to do advanced sensitivity analysis in a spreadsheet with Python. You can build sensitivity models with many variables and analyze massive datasets. You can view a live example sensitivity analysis using Python in Row Zero and try Row Zero for free to get started.

Try Row Zero for free

Related Content

Appendix

Below you'll find the python code for the sensitivity analysis used in the example above. You can copy and paste this into the code window in Row Zero to use as a starting point to create your spreadsheet functions:

import numpy as np
import pandas as pd
from scipy.optimize import minimize

######################## Data Table ############################

def my_data_table(fixed_cost, profit_low,profit_high,profit_step,units_low,units_high,units_step):

    profits_unit_array = np.arange(profit_low, profit_high + profit_step, profit_step) 
    units_array = np.arange(units_low, units_high + units_step, units_step)       
      
    units_grid, profits_unit_grid = np.meshgrid(units_array, profits_unit_array, indexing="ij")

    profit_grid = units_grid * profits_unit_grid - fixed_cost

    df = pd.DataFrame(profit_grid, index=units_array, columns=profits_unit_array)

    return(df)

######################## Optimization ############################

# Helper to calculate PMT
def pmt(P, apr, n):
    r = apr / 12.0
    if abs(r) < 1e-12:
        return P / n
    a = (1 + r) ** n
    return P * r * a / (a - 1)

def optimize(goal,loan_length,min_apr,max_apr,min_loan_amt,max_loan_amt):

    loan_length_months = loan_length * 12
    
    bounds = [(min_apr, max_apr), (min_loan_amt, max_loan_amt)]

    obj_A = lambda x: -x[1] + 1e-6 * x[0]  # maximize loan amount (minimize -P), tiny tiebreaker on APR
    constr_A = [{"type": "ineq", "fun": lambda x: goal - pmt(x[1], x[0],loan_length_months)}]  # payment <= TARGET
    initial = np.array([min_loan_amt, max_loan_amt])
    
    res_A = minimize(obj_A, initial, method="SLSQP", bounds=bounds, constraints=constr_A)
    apr_A, P_A = res_A.x
    pay_A = pmt(P_A, apr_A, loan_length_months)

    return apr_A, P_A, pay_A, res_A.message

FAQs