Why What-if Analysis Lives in Excel (and How Sigma Can Fix It)

A common challenge facing companies today is an inability to explore different business scenarios dynamically. Often data pipelines represent fixed historical data, and most BI tools don’t offer native what-if analysis or forecasting. Due to how important this type of analysis is, it often ends up being a process that lives in tools like Excel. It can be decentralized, time consuming, and inconsistent to maintain. Leveraging a BI tool to design a process around this type of analysis can enable consistent and automatic processes that are more easily accessible to a larger audience of stakeholders.

Sigma is an effective tool for this type of analysis, and there are several ways to achieve it. Sigma does have useful features that allow for a smooth process in setting up and evaluating hypotheticals and forecasts. One approach that’s broadly applicable is demonstrated here: https://www.sigmacomputing.com/resources/library/sales-pipeline-forecasting-app

The primary Sigma features that are being leveraged in this approach are input tables and actions/controls, the tools which allow data apps to be built within Sigma. This type of what-if analysis in practical terms is the appending of manufactured data that represents potential outcomes onto existing historical data.

Input Tables

Documentation: https://help.sigmacomputing.com/docs/intro-to-input-tables

Input tables allow Sigma users to input data directly into the dashboard and use that data elsewhere within the dashboard. The table can also be used more broadly within a data ecosystem, as these tables can be manifested as warehouse views.

In the most simple case, hypothetical results can be added to input tables and unjoined with actuals to enable the visualization of both side by side. In the following example, two hypotheticals for future revenue are added as an input table and can then be used in other visualizations to report on any resulting metrics. This method is flexible, and very quick to implement, but it is also similar to the use of spreadsheets to check the outcome of manual inputs.

Actions

Documentation: https://help.sigmacomputing.com/docs/intro-to-actions

Actions are tools that enable interactivity within a Sigma dashboard. They can trigger a wide variety of events through interaction with several dashboard elements. For hypothetical analysis, this is a key component to enable user friendly adjustments to parameters/assumptions, or allow a quick comparison between different hypotheticals.

One consideration in building the right solution for hypothetical analysis is who the end user is, and how much of the BI tool they will have access to. Actions allow for a clear and defined path for interaction without the need for a user to have editing features enabled in Sigma.

Example

In this example case, actions can be used to affect dashboard filters that enable hypotheticals to appear in the chart as well as being used as a selector to choose which of the two hypothetical cases to display.

The rules guiding the creation of hypothetical targets or forecasts may be possible to codify into formulas. Once the future dates are appended to the source table used in the forecasting visualization, a formula could be added to calculate a forecast. As an example, a formula could take the results from the same quarter in the prior year and use them as a base with a planned percentage increase over the prior year.

This is another case where controls can be helpful. A control could be added to let the user adjust the planned percentage increase (Growth Estimate) over the prior year, and the formula can reference that control to generate hypothetical results. The same principle of setting values through inputs and parameters can be used flexibly to create what-if scenarios in either direction. For example, instead of inputting a current-state variable like a growth rate to project a future state, you could input a desired future state to calculate the required target metric, such as the required growth rate. These key hypothetical metrics can even interact, be bi-directional, or selectively applied with actions.

As an example, If we have a desired target for future profit, we can estimate the required revenue growth rate to achieve that profit given the current profit margin, or even toggle to a variable profit margin to see how balanced improvements to revenue growth and improved margins could intersect to achieve the goal.

This usage of parameters and controls within formulas allows users a great deal of flexibility through a controlled front-end environment. Users then have the option to easily switch between a live view of operational data and hypothetical cases for the future.

While formulas can address certain use cases to extend business rules to hypotheticals, more complex cases are likely better served by building forecasting within the data pipeline feeding the Sigma dashboard. Options for this include well-integrated ML functions through Snowflake like time-series forecasting.

Creating a more automated system for conducting hypothetical analysis can help ease a lot of manual work and potential errors that can arise from local solutions like spreadsheet analysis. Ideally the calculation of these hypotheticals is a process that lives in the data pipeline, but Sigma allows the flexibility to create intermediate solutions that are centralized and standardized, even if they still require manual input. It can also present an interactive front end that allows certain users to be disabled from viewing the source data. In cases with more complex models or business logic housed within the data pipeline, Sigma is an adaptable tool to visualize modeled hypotheticals, help elevate the analysis through interactivity, and distribute the results to a wider audience with controlled access.

To see a similar case of data apps within Sigma using these features, check out this embedded app template: https://www.sigmacomputing.com/resources/library/brooks-grocery-embedded-app

In this app, you can see some what-if analysis in the marketing section. This uses marketing spend as a user input very similarly to derive other metrics within the dashboard.