Spreadsheets vs. Sigma: Scenario Modeling
Sr. Content Marketing Manager, Sigma
Imagine you’re the Sr. Manager of Finance at a global real estate investment firm. Due to the recent fluctuations caused by the COVID-19 pandemic, both globally and regionally, the executive team asked your team to prepare and analyze multiple versions of 2022’s projected revenue.
They would like you to draft a model that represents scenarios needed to stay on target by combining factors such as regional occupancy trends, rental prices, and mortgage rates, for example. But due to the size and complexity of the business’ data over the past few decades, the fully detailed 12-month fiscal plan and other historical trends contain an enormous volume of records spanning at least a dozen sources.
Because you don’t know SQL or any other coding language, you have to go to your BI team for assistance, but they tell you they can’t help you for another two weeks. You can’t afford to wait that long, so you attempt to export the data from each application into a CSV and combine it in Excel — only to realize that it can’t process more than a million rows of data without crashing or slowing down.
Frustrated and unwilling to return to leadership without any results, you decide to analyze a single geographical region at a time – and pray you haven’t made an error along the way. Sound familiar?
In the third and final installment of our Spreadsheet vs. Sigma series, we cover how the spreadsheet is evolving to help business leaders reimagine their analytics workflow and break free from the traditional cycle of high-level dashboards, BI requests queues, extensive data modeling, and siloed data extracts. You’ll learn:
- Ways business teams use scenario models to aid planning and decision making.
- Why building accurate scenario models with traditional spreadsheets is nearly impossible at the scale and speed modern organizations need.
- How Sigma’s virtually infinite scale allows teams to build scenario models that factor in billions of records at once, including years of historical data.
What is Scenario Modeling and How Does it Help Organizations Prepare for the Future?
Scenario modeling is the process of creating data models of potential future scenarios in order to aid planning and decision making. Business users like financial analysts, capacity planners, and CFOs use scenario modeling to explore iterative, “what if?” questions to evaluate possible outcomes.
Unlike conventional forecasting, which is based on a “most likely” or “best-case” prediction, scenario modeling produces many possible outcomes based on a variety of inputs and variables. This level of insight allows companies to create flexible long-term plans, develop contingencies, and make decisions that:
- Circumvent risks and financial losses
- Optimize capital and operational expenditures (CAPEX and OPEX)
- Adjust lead journeys to maximize campaign conversion rates
- Accelerate corporate growth and profitability
- Improve organizational resilience and agility
Scenario Modeling: How Traditional Spreadsheets Are Holding You Back
Organizations typically face three substantial challenges when attempting to produce accurate scenario models in traditional spreadsheets.
- Scenario modeling requires up-to-the-minute data from multiple systems including CRMs, accounting software, and more. The data from these sources often comes in different formats and requires BI teams to normalize it can be used. What’s more, because spreadsheets are instantly outdated the second they’re downloaded, updating them with the latest data requires constant reconciliation across each of these systems. This is a highly manual and time-consuming process that takes hours away from more strategic and impactful work.
- Frequent manual updates are prone to human error and likely to result in inaccurate scenarios. This critical issue is compounded by the need to run scenarios by different key stakeholders to help develop contingency plans and make decisions. Each time a spreadsheet is attached to an email, it creates a new data silo — not to mention a significant data governance and security risk!
- Due to traditional spreadsheets’ size and performance limitations, extracts must either aggregate or leave out large data sets. This makes it difficult, if not impossible, to do analysis spanning long-term trends over decades of data down to granular detail to identify subtle but essential trends or factors.
Scenario Models? Crystal Balls? No, Just Sigma.
Traditional BI tools simply weren’t built for the speed, scale, and complexity of today’s world. But Sigma was purpose-built to empower teams across finance, sales, marketing, and more to independently investigate live data at scale, easily find answers to ad hoc questions, and work together to get to the heart of complex problems in real-time.
- Sigma connects directly to a business’ cloud data platform, a centralized repository that automatically collects and stores data. This means that the data underpinning scenario models is always live, making them easy to adjust over time without having to build them from scratch. And because data is never extracted to a spreadsheet, sensitive information and corporate plans are kept safe, secure, and governed.
- Sigma’s intuitive user experience and flexibility of analysis empowers cross-functional team members to do productive, free-flowing analysis. Sigma also allows multiple scenarios to be organized and annotated within a single online doc for a collaborative, Google Docs-like experience.
- The speed and concurrency of Sigma’s direct connection to a cloud data platform means that analysts have a snappy, responsive experience no matter how large and complex the models are, the number of scenarios modeled, and the number of users collaborating on the models
Drinking our champagne
As a rapidly growing SaaS start-up, Sigma Computing requires its finance team to update its revenue forecast and run cash flow projections every month. But creating best, likely, and worst-case scenarios is traditionally a painfully tedious, time-consuming, and error-prone process. It involves manually updating a spreadsheet with new subscriptions and renewal, upsell/downsell, and churn percentages for each unique scenario.
Fortunately, Sigma’s finance team is now ahead of the curve by using cloud-native analytics to run “what if” analyses and rapidly generate multiple scenarios around critical metrics that are critical for Sigma’s success. Rather than using stale, siloed spreadsheets, all of the data they need is live, governed, and comes directly from Snowflake’s Data Cloud.
“The fact that I’m able to use my spreadsheet skills to directly access and analyze billions of rows of live data from different sources in a single Excel-like interface is a game-changer,” says Firdaus Khan, Sigma’s Senior Financial Analyst. “I’m able to set revenue goals based on historical performance that is also aligned with our corporate objectives. It’s also easy to collaborate with stakeholders around multiple sales capacity scenarios, so we can now create holistic booking forecasts in minutes rather than hours.”
How to Build a Scenario Model With Traditional Spreadsheets
- Identify all the historical data across disparate systems and applications needed to base your scenario model on.
- Request separate data extracts from the data team, keeping the data size limits under a million rows so your spreadsheet won’t crash during your analysis.
- Decide on the base data inputs and projections of factors and outcomes.
- Organize the data into the ‘most likely’ scenario in a spreadsheet.
- Create separate copies of the ‘most likely’ scenario in additional spreadsheets with different variables to represent additional scenarios. This can include more conservative and aggressive projections and adjustments of external variables and inputs, such as budget and headcount allocations.
- If a single scenario is too complex or contains too much data, it may be required to break the model down into components, like business units, in order for it to fit within traditional spreadsheets.
- Compare across spreadsheets, potentially by creating additional delta spreadsheets to analyze the differences in outcomes between different models.
Note: Since the data is not live or won’t update automatically, changing the base data requires rebuilding the spreadsheets from scratch.
How to Build a Scenario Model With Sigma
- Ensure the full required data has been loaded into your business’ cloud data platform by your data team and select the needed live data sets in your cloud data platform and join them in Sigma.
- Build a spreadsheet-style model in your Sigma Workbook with the variables you want to adjust set as parameters, charts to visualize KPIs, and a notes text section to explain the analysis, variables and goals.
- Explore the model, test out different values for the variables and share with team members for collaboration and iteration.
Note: With Sigma, team members can reuse and repurpose each other’s work without fear of erasing or changing the original analysis.
No One Can Predict the Future, But Sigma Can Help You Prepare for It
The economic uncertainty caused by the COVID-19 pandemic proved that analytics is more than a competitive advantage: It is the most critical component of a competitive, highly successful, and future-proof business.
But the current analytical workflow is broken. Traditional spreadsheets simply weren’t built for modern business needs — but they are still the most beloved and flexible analytics tool ever created. Most cloud BI solutions were created to handle the complexity and vastness of today’s data — but they limit access and analysis to only the most technical users.
With Sigma, business users can independently do complex, iterative analysis like scenario modeling no matter how large and complex the models are, the number of scenarios modeled, or the number of users collaborating on the models.