Financial Modeling: Formula Best Practices

No more “Battleship” formulas


Writing and managing spreadsheet formulas can be a tall task. That task becomes even more challenging when your model expands in size. Adding new products, years or regions to your spreadsheet can dramatically increase the cell count and the formula count. This article will provide you with some best practices to keep your models and formulas in check.

Why are formulas so important?

Your formulas determine the accuracy and scalability of your models. Over the years we’ve talked to customers and prospects about their modeling concerns. Many of these conversations revolved around formulas. For that reason, we developed a Spreadsheet Best Practices Checklist.

Spreadsheet Best Practices Checklist:

Checklist ItemCompleted?
1     The number of formulas in my workbook is reasonable*?
2     I can maintain and update my model at a moment’s notice?
3     I can audit each of my formulas to ensure they are working correctly?
4     If I share my model, I am confident that the data will remain unaltered?
5     There is only one version of the truth?
6     The size of my data is not impacting my performance?
7     The structure of my model is easy for others to comprehend?
8     The person who created the spreadsheet still works at my company?

If you couldn’t check all the boxes or are unsure about any item(s) on the checklist, your spreadsheet may be sinking. Don’t worry! There’s a life raft. Keep reading to learn more about formula writing and how to keep your model afloat.

What are “Battleship” Formulas?

In the Battleship boardgame, players place toy battleship pieces into a grid. They then take turns calling out grid coordinates hoping to hit their opponent’s ship. Similar to the Battleship boardgame, your spreadsheet uses grid coordinates to write formulas. Beneath the surface, each of these formulas leave you guessing: what’s F4 or B7? One cell change or error can sink your entire model. Worst of all, it may be very difficult for you to figure out what went wrong.


If you’re using a traditional spreadsheet…

  1. Keep actuals separate from calculations and input cells*
  2. Use named ranges to help simplify your spreadsheet formulas*

*These options, however, are just workarounds and do not fully resolve the core problem with spreadsheets: Formulas are stored within cells.

RESOLUTION: Scrap Spreadsheets, Use a Financial Modeling Tool

While traditional spreadsheets have workarounds, they lack a true permanent solution. Luckily, there are spreadsheet alternatives, like Quantrix, designed to keep formulas manageable.

Let’s take a look at how Quantrix formulas work.

Quantrix formulas are completely separated from the cells. In the image below, you can see the calculated cells at the top, but the formulas can be read and edited in a separate pane beneath. Even as your model grows, your formulas won’t break.

Introduction to Real Language Formulas:

If you’ve used traditional spreadsheets, you’re most likely familiar with the coordinate-style of formula writing. For example, you click in a cell and type “= B7 – C7.” Then, depending on your model, you will copy or drag your formulas across an array of cells. With Quantrix, you can forget about these “Battleship” coordinates and start writing with natural language. In addition to being much more easily understood, these formulas do not need to be copied or replicated. In the image below, you can see some Real Language Formula examples. You can immediately understand what these formulas are calculating.

Example: Gross Profit = Revenue – COGS

Best practices are easier to follow when they’re built-in to the product. Quantrix was designed to do just that.

If your spreadsheets are breaking, it might be time to jump ship and try Quantrix!

Want to learn more?

We would love to hear from you! We offer personalized live demos where our experts can show you how Quantrix can solve your modeling and planning problems. You can also take advantage of our free 30-day trial.

Free Trial Book a Demo

WATCH NOW: Quantrix Dimension 2022 Conference

We recently hosted Dimension 2022, our annual conference. Over the course of two days attendees heard directly from the Quantrix team as well as an incredible line up of presenters. Customers from a wide range of industries shared their Quantrix experiences and answered audience questions.

For those of you who could not attend, these sessions were recorded and are now available to watch.

Jason Simpson
Magseis Fairfield

Gilbert Hangartner
Brainwork Consulting / ALPIQ

Érica Alves
Dorel Industries

Mark Gandy

Rich Lopez
Litehouse Inc.


Quantrix Qloud Training Videos

We are happy to announce that we recently released our new Qloud Training Video Series! There are three videos designed to help you get started. Check them out below!

Using Models on Qloud

Are you new to using Quantrix Qloud? If so, this video is a great place to start. Learn about Qloud, how to use it and common terminology you may see when sharing and editing models.


Managing Models on Qloud

In this video, we review connecting Modeler & Qloud, the Qloud menu in Modeler, publishing to Qloud, checking-in & checking-out, model locking, the Qloud Portal, sharing models, removing access, viewing versions, and deleting vs. removing a model.


Qloud Admin Tutorial

In this video, we review the Qloud Admin menu, creating & managing users, the models list, settings, model server, and other features!

Additional information can be found in our Online Help.


Next Gen Financial Modeling Software

Before we can discuss the future of financial modeling, we might want to have a bit of historical context for electronic spreadsheets. After all, it wasn’t that long ago that paper ledgers were the standard for budgeting, accounting, and operational planning.

The origin of electronic spreadsheets dates to June of 1979 with VisiCalc. Amazingly, many of the initial concepts used in the preliminary versions remain intact today. To learn more, watch the TedTalk: Meet the Inventor of the Electronic Spreadsheet.

While the user interface has seen some updating and some of the use cases and calculations may seem rudimentary by today’s standards, early programs laid the foundation for the future. Over time, as the notion of these types of spreadsheets grew in popularity, so did certain terminology: tables, rows, columns, cells.

In fact, today many personal and business computers come with spreadsheet software pre-installed. Spreadsheets are so popular that nearly every student and every business is likely to be familiar with spreadsheet basics. Unfortunately, the fast growth and ubiquity of spreadsheets has come despite persistent flaws in their architecture, and spreadsheet popularity tends to overshadow more robust approaches to financial modeling which we will describe below.

Countless stories have been told about the risk of spreadsheet errors and there is clear evidence that ignoring best practices could lead to potential issues. As the use of these tools became ubiquitous, a series of best practices emerged to mitigate the flaws of spreadsheets, especially for financial modeling, such as keeping actuals separate from calculations and input cells (see the table below for a full list).

That said, if modelers keep all the best practices in mind, spreadsheets can hold a great deal of value for a variety of needs. They are essentially a blank canvas, and as a result, they are incredibly agile.

Spreadsheets aren’t perfect and as requirements scale, new solutions might be required.

A defining concept of spreadsheet software is that all model information is contained in the cells. That is to say, the data, the formulas, the logic, the structure, the checks etc. etc. are overloaded and every cell that participates in the structure and logic will have its own unique formula. There is no structure, no logic, and no relationships that are not designed and implemented by the modeler through abstract formulas and references like “=sum(VLOOKUP(L34,MySheet!A17..C29,2,FALSE)” where the business meaning of the value to be looked up and the search range is unknown, and the proper functioning of the formula requires a certain configuration of data that is not validated or supported by the spreadsheet at all, yet it must be absolutely correct to return the proper answer.

Such a system might seem flexible at first, but as soon as structure and logic are built, models start to become rigid and fragile.

If a cell value is deleted, so too is its formula, which may cause the entire logic of the spreadsheet to fail and provide incorrect answers – if those answers were correct in the first place.

Furthermore, carefully built structure is resistant to change – if ever the model needs to scale out, adding new scenarios, products, regions, departments, channels, accounts etc. Customers describe the horrors of “open sheet surgery” wherein worksheets are duplicated, thousands of formulas changed or added, and the whole thing stitched back together.

Whole industries exist to reinforce best practices and mitigate the risks of spreadsheets, to audit spreadsheets, and to patch the functionality of spreadsheets with add-ons of various kinds.

If your needs extend beyond simple budgets and forecasts, to complex operational models in sales operations, supply chain, manufacturing, real estate or strategic finance you may run into the limitations of spreadsheets. For that reason, two other software categories have emerged: Enterprise Planning Tools and Financial Modeling Software.

Can these alternatives replace spreadsheets but address the more intense demands of real business planning and operations? Let’s explore.

Enterprise Planning Tools

Enterprise Planning Tools, as the name implies, are designed explicitly for large enterprises. While they provide the scalability and governance enterprises expect, such platforms also require enterprise grade resources, of both the financial and people varieties, to deploy and manage. Often they require database admins, IT admins, plus configuration and developer resources to be customized to the specific needs of the business.

Such planning tools provide a robust platform to develop important, standardized models that will be used by multiple stakeholders in the business. Those models will be managed, maintained and run as any large scale project would be. Adopting these systems can help to standardize certain business processes but buyers should expect some ‘sticker shock’ when it comes to purchasing this category of solution.

Beyond the costs and resources required, the limitation of enterprise planning tools is agility. Models that used to take a few minutes to create in a spreadsheet now might take multiple people, multiple design / build cycles and become similarly rigid. Most customers of enterprise planning tools actually never get rid of their spreadsheets, and end up modeling in a spreadsheet anyway, and later attempt to push back into the planning system as a repository. Many enterprise planning tools use spreadsheets as the “front end,” in many cases defeating the purpose of more rigorous model development but maintaining the cost.

A New Generation of Financial Modeling

Now that we understand where we’ve come from, you’re probably wondering: what is the future of financial modeling? Is there a software option that addresses the major pitfalls of spreadsheets at a reasonable price and maintains agility throughout the model lifecycle?

The answer: Professional financial modeling software from Quantrix.

Quantrix provides the agility of spreadsheets and the scalability and governance of an enterprise planning application. Moreover, Quantrix Qloud includes low-code design of model-based applications, so that the power of your models can be distributed in easy-to-use applications across your organization.

How is this possible?

Matrices: Unlike spreadsheets, every familiar table you see in Quantrix is actually a multi-dimensional matrix under the hood. This means that adding a new scenario, region or product is a single click, no more “open sheet surgery” required. Need an entire new forecast for next year? Again, a single click, no more saving a copy, dragging formulas, or duplicating sheets. Customers have reported up to 75% reduction in time to prepare operational reviews compared to their old spreadsheets.

Named Objects & Real-language formulas: Categories and metrics in your model are all referenced by their names, you can use the same familiar spreadsheet formula syntax, but formulas now read like a sentence, like “Profit = Revenue – Expenses”. No alphabet soup of abstract references. Human readable formulas are easier to write, read and troubleshoot so having a new team member (or auditor) understand how your model works is straightforward. Collaboration, confidence, and shared understanding of your decisions will improve.

Inherited Relationships: Relationships between tables are created with drag and drop, and formulas inherit those relationships automatically. This fortifies models with simple, logical structure without any special work or spreadsheet best practices. Because table relationships are inherited, there is no longer any requirement for that VLOOKUP/HLOOKUP/XLOOKUP/INDEX/MATCH/FILTER syntax that you have to research every time. The Profit formula above is exactly the same no matter if you are calculating it across 100 products with individual growth rates, 17 regions with distinct currencies, or time periods of 3, 5 or any number of years. One Quantrix formula in simple, real language, takes the place of hundreds or thousands of spreadsheet formulas, dramatically reducing the possibility for error and increasing efficiency.

Enterprise Capabilities: Quantrix also has data connectivity, role-based security, an audit trail, low code cloud apps, simultaneous collaborative editing and support for large models that the enterprise platforms have, but it costs a fraction of the enterprise tools in terms of both resources and budget.

Look how easy it is to understand the formulas in Quantrix:

Focus on modeling not maintaining.

To summarize, consider that every user must learn a set of spreadsheet best practices when creating a model to make it more secure, and less error prone. But these practices also make models less performant, and more rigid – Wouldn’t it be better if these best practices were built into the product?

The table below shows why Quantrix is the Future of Financial Modeling:

Spreadsheet Best Practices

Quantrix Built-In Functionality

Keep actuals from calculations and input cellsFormulas and cells are always separated
Create named ranges to simply formulasAutomatic ranges with real language formulas
Keep pivot tables on separate sheets (or well-spaced)Always on pivoting
Enforce consistent naming across worksheets for lookupsLinked structure automates names updates and lookups
Keep data in rows and formulas consistent left to rightOrient data in any direction without changing formulas
Avoid non-tabular layouts and merged cellsModel multiple dimensions easily and instantly
Build in calculation checks to identify errorsDependency inspector, audit trail, problems log

To learn more about these best practices, watch our recent webinar: How to Ignore Spreadsheet Structure and Come Out Ahead.

Quantrix delivers the agility of spreadsheets and the scalability of enterprise planning software.

We would love to show you the power of our agile modeling platform so that you can see firsthand how it might deliver value in your business. Over 50k Quantrix users have saved time, reduced errors, considered new scenarios and made more confident business decisions with Quantrix across Sales, Finance, Marketing, Manufacturing, Supply Chain, HR, R&D in every industry.

There isn’t a modeling problem we haven’t solved using Quantrix. Why not challenge us?

You can book a demo and download our free 30-day trial!

Book a Demo Free Trial

What software models financial information?

What software models financial information? If you’re asking this question, you may have experienced the inefficiencies of traditional spreadsheets and now you’re seeking a better alternative. From balance sheets and income statements to cash flow statements and forecast models, you need a software that can securely and accurately represent your business.

Not surprisingly, the best software to model financial information is a Financial Modeling Tool. Unlike spreadsheets, Financial Modeling Tools are designed to scale as your business grows. They can also help you accurately forecast the future of your business by answering your ‘what-if’ questions.

Although there are a variety of Financial Modeling Solutions and Enterprise Planning Tools, we want to highlight Quantrix and show the features that we believe are most impactful in financial modeling.

About Quantrix

Quantrix is a multi-dimensional financial modeling solution used by businesses around the world.

Quantrix Modeler is a desktop application that allows you the agility to create just about any type of model. Quantrix Qloud allows you to share your models online without compromising their integrity. Combined, Modeler and Qloud are the perfect complement.

Logic is Separated from the Structure

When your formulas are separated from the cells, you can ensure your structure remains intact, even as the model grows.

Natural Language Formulas

Formulas in Quantrix can be written using Real Language. This makes it so much easier for anyone reading your models to understand the calculations. Say ‘goodbye’ to the days of writing = B35 – C35 and ‘hello’ to writing Gross Profit = Revenue – COGS.

Import and Export Data

To build accurate financial models, you’ll want to import existing data. Quantrix makes this easy! There are a variety of options for you to Import including: Google Sheets, Salesforce, a Text File or even a Database.

Expanding Your Models (Adding New Regions, Products etc.)

Businesses are always changing. They must adapt to global factors such as pandemics or internal circumstances such as introducing a new product. In turn, financial models require edits and updating. What if you could add new products, regions, years or any other category to your model simply by pressing enter? With Quantrix, you can! When your formulas are separated, it’s easy to extend your model without breaking the structure.

Click here to watch the rest of our recorded demo.

Software Options

If you’re looking for a software that’s more powerful than traditional spreadsheets to model all your financial information, look no further. When it comes to Agility, Scalability and Structure, Quantrix has it all!



Enterprise Planning Tools




NonePre-built, little customizationFlexible structure


Now that you’ve seen some of the major benefits of Quantrix, you may be thinking: what’s next?

We offer a free, fully featured 30-day trial as well as personalized demos. We would love to hear about your use case and show you how Quantrix can fit into your business.


Book a Demo Free Trial

Top Financial Modeling Software

If you have been using a traditional spreadsheet tool for your financial modeling needs, you may have noticed some limitations. Your models may get too large, you may lose the ability to audit your formulas, or you may just simply get numerous errors. Finding the right replacement is an important decision.

Our last blog entry, Financial Modeling Software Comparison Chart, compared Spreadsheets, Enterprise Planning Tools and Quantrix. All three have pros and cons as well as differing approaches to their agility, scalability, and structure.

The decision you make will depend on your financial modeling goals and your budget.

Decades ago, a new software called Lotus Improv was introduced, which aimed to transform financial modeling away from two dimensional spreadsheets. Quantrix was designed to keep that spirit alive.

Quantrix Modeler

Here are just some of the features that you’ll find when using Quantrix:

Top Financial Modeling Software Features

  • Import and Export Data
  • Always-on Pivoting
  • Interactive Dashboards
  • Audit Trail
  • Scripting
  • And Learn About our Natural Language Formulas

Although some spreadsheet options are free (or close to free), there can still be a price to pay in terms of time. With traditional spreadsheets, you can spend countless hours building, maintaining, and auditing your models. The best way to deal with this issue is to use a financial modeling tool that values your time. Our recent webcast highlights how you can Ignore Spreadsheet Structure Rules and Come Out Ahead.

30 Day Free Trial, Personalized Demos, Professional Services

Finding the best Financial Modeling Software for your company can seem overwhelming. We want to change this perception. To make sure that Quantrix is the right fit for your business, we offer a free, fully featured 30 day trial.

You can also reach out to us to set up a personalized demo, where we can show you how Quantrix can work within your business.

We also understand that some customers will need help building their models. For that reason, we can offer professional services. We have a network of expert modelers who can assist your company in building a model that reflects your business.

Free Trial  Book a Demo

Financial Modeling Software Online | Online Apps

Time is exceptionally valuable in business. Spending that time emailing spreadsheets back and forth is not the best use of it. Making edits, sharing revisions, and updating coworkers can take up more time than you bargained for.

Not only are these practices time consuming, sharing your models with colleagues is not without risks. There’s a chance they could accidently break a formula or change the structure.

What if you could share models online with colleagues for review without compromising their integrity? What if your sales team could enter their data online without worrying about the underlying structure of the model? What if you could set up permissions so that only parts of the model are visible to people of a certain role?

With Quantrix, you can!

Quantrix Qloud allows you to use Quantrix in your browser. You can build models on our desktop application, and then share your models with others online.

To get a better understanding of how Quantrix Qloud works, we have put together a series of interactive demo models for a variety of use cases. This also provides you the opportunity to see the types of dashboards that can be created.

Quantrix is a professional financial modeling software that can be used online.

INTERACTIVE DEMO MODELS: Build models like these or create something new for your business

Revenue Tracker: Leads, Opportunities, Revenue
For your business to flourish, you must be able to answer a variety of questions: How many leads are in my pipeline? How many opportunities will that turn into? And most importantly, will I reach my sales goals?

This example model will help you learn about Quantrix and answer those questions. Try it now!

Try Model Online

Integrated Financials: Growth Rates & Forecasting  
– Use our slider functionality to alter growth rates
– Select different forecasting methods and enter data into cells
– Expand the model to test new scenarios.

If you’re struggling with spreadsheets, there is a better way. Try this model today!

Try Model Online

Real Estate Valuation: Compare Rates of Return, Evaluate Scenarios and More
In this interactive demo, you’ll be able to compare rates of return on property investments, evaluate ‘what-if’ scenarios for inflation and interest rates and generate a graph to show net income projections.

This is a great opportunity to see how Quantrix can help you make better decisions. Try the model in your browser now!

Try Model Online


Financial Modeling Software Online

Although there are a range of online alternatives, if model security and integrity are paramount to your business, Quantrix is the right choice for you.

If you are interested in learning more about Qloud and trying it for yourself, request a demo! We can show you how Quantrix can work within your business. We would love to hear about your financial modeling use case!

Free Trial  Book a Demo

Financial Modeling Software Comparison Chart

Are you in the market for financial modeling software? 

There are a variety of software options available for financial modelers to choose from.  Each option has pros and cons. It’s important for you to understand each possibility and all the ramifications that accompany them. The goal of this article is to help you better comprehend how each option stacks up.

It’s important to note that each use case is different; and your choice will depend on what goal you want the software to accomplish as well as your budget.

From our perspective, software options can be bundled into three groups: Spreadsheets, Enterprise Planning Tools & Quantrix.

Additionally, there are three major categories needed to evaluate financial modeling software: Agility, Scalability & Structure.

Let’s see how they compare.


Enterprise Planning Tools




NonePre-built, little customizationFlexible structure



Many spreadsheet options are inexpensive (some even may come with your computer). They are also agile. For maintaining basic lists and smaller datasets, spreadsheets are usually a solid choice. The downside is that spreadsheets are generally not scalable. For instance, if you wanted to add new regions or products to a model, that would require extra steps such as copying formulas. The agility that spreadsheets offer is a double-edged sword. The freedom to create anything within the sea of spreadsheet cells sadly lacks structure. You must build it yourself. Even with spreadsheet add-ins, you are still constrained.

Enterprise Planning Tools:

Unlike spreadsheets, Enterprise Planning Tools can leave you with sticker shock. The pricing comes in a variety of forms – the software, the maintenance, consultant fees. These types of tools are scalable though. They are designed to incorporate new categories, such as new regions or products. Enterprise Planning Tools offer pre-built structural options (with little customization). As long as your data fits into these pre-built options, your models will be able to scale. However, it may not perfectly represent your business without consulting help.

Quantrix | Financial Modeling Tool:

Professional Modeling Tools, such as Quantrix, check all the boxes. These tools are designed to be agile, scalable and offer flexible structure. You are able to add new categories and build out your model with ease. Quantrix uses a mutli-dimensional modeling approach, whereas spreadsheets are two-dimensional. And unlike Enterprise Planning Tools, Quantrix gives you complete control to build what you want. Quantrix is able to offer all this at a much more reasonable price compared to some Enterprise Planning Tools.

Quantrix Demo Video:

Hopefully, you have been able to learn more about how these offerings compare and have a better idea about the right fit for you.

If you think a financial modeling software, like Quantrix, might be the right choice for your business, please reach out for a demo and try our free 30 day trial! We are mentioned as an Financial Modeling alternative to traditional spreadsheets on

Book a Demo Free Trial

Supply Chain Modeling

There has arguably never been a more volatile time in supply chain history than today. A global pandemic coupled with political tensions and rising costs has resulted in a nearly unpredictable imbalance of supply and demand.

Global logistics providers are strained from a staffing perspective and are also faced with challenges at every port and terminal. Suppliers are being squeezed by raw material shortages and costlier packaging.

When disruptions to supply and demand occur, supply chain modelers and planners go to work to retune the forecast and rebalance product available with customer orders. But how quickly can the numbers be re-crunched and coordinated, particularly across multi-regional networks or transmodal routes?

Dashboard: Monthly S&OP Outlook | Production Plan

Many supply, demand, capacity, and production (S&OP) planners rely on a series of spreadsheets to do the work. Often, planners will have refined spreadsheet models that have been improved and expanded over time, and have been passed from planner to planner as the organization grows. In many cases, these models include highly-tuned forecast calculations and segmented sheets by SKU, region, warehouse, or other dimensions of the business. Some even have nearly real-time connectivity to the ERP or MRP system, so planners can quickly refresh their boards to see the current inventory levels or lead times projected for next month’s shipments. These models are iterated with each supply chain crises or disruption to, ideally, shorten the time it takes to redo the plan the next time an unexpected event strikes.

As a former supply planner, here are three critical challenges with maintaining spreadsheet models in periods of high uncertainly and volatility:

  1. Lack of transparency / readability within the model. When new planners are onboarded and inherit former planners’ spreadsheets, there is very little time to focus on learning the details of how the model operates. In businesses where the S&OP cycle is executed monthly, planners often must devote their time to fighting (hopefully not literal) fires and understanding the business, rather than studying formulas and macros in a spreadsheet they’re expected to use daily to drive the organization’s supply accuracy and service level performance. It can be a struggle to learn how to use the model correctly, and just a small mistake could mean incorrectly forecasting the required product – in extreme cases, resulting in a customer stockout or a warehouse capacity breach.
  2. Fragility when changes occur. When a new SKU, plant code, or packaging configuration is created at the MRP level, ensuring the change is properly and fully incorporated into the planning model is a tedious task. Leaving off the new SKU in just one location in the model could skew the plan or result in a less-than-optimal suggested production volume. In other cases, incorrectly mapping a packaging configuration could also have far-reaching implications with trade regulations or logistics provider costs, if order details are not properly communicated to service providers downstream of production. For models that rely on add-ins or integrations, a new change in the ERP system could result in VBA update code crashing or corrupting a model file (believe me, it’s the worst). And while it is great that more “mature” models contain better sheets to represent all sorts of potential scenarios and perspectives, these models are massive in size and are prone to crashing at just the moment you need them not to (again, believe me, it’s the worst).
  3. “We need a new plan.” When an unexpected crisis strikes in a global supply chain network, it often means getting everyone on the Zoom call to problem solve together. The Asia, Europe, and Americas supply planners jump on a call, each equipped with their own version of a spreadsheet model. The production planners or manufacturing counterparts come with (you guessed it) their own spreadsheets too. We need a new plan – one that syncs across regions, operates within the constraints of warehouse and terminal throughput, and maintains profitable operations at each of our global production facilities. How do we do it? With half a dozen people on a call at all hours of the day and night thanks to time zones, we are faced with the insurmountable task of somehow harmonizing all of our spreadsheets together to find the solution. Stakes are high, and by the time our team has worked together to pull the numbers, it’s likely the circumstances have changed yet again.

Have you been here before? As supply chain professionals, it’s all too easy to dive into a vicious cycle of late night “quick response team” or “war room” calls to resketch the supply-demand balance, only to determine early the next morning that the plans have changed again. Constantly chasing uncertainty with cobbled together spreadsheets (even when we’ve done immense work to streamline and fail-proof them) is a battle lost. If not lost quantitatively, then surely, emotionally – supply chain professionals are among the smartest, most resilient folks I’ve ever worked with, but even the strongest planners burn out after months of constant fire fighting with tools that fail to provide fast enough insights.

We certainly can’t control the volatility in the world, so what can be done to make the planning process better, for the sake of both our organizations and our supply chain professionals?

The need to combine existing business structure and plans with “clean-sheet” brainstorming is the missing piece in an organization that relies on disparate spreadsheets. Given the 3 challenges described above, it’s incredibly difficult to take a highly-tuned, VBA-laced S&OP spreadsheet model and create a new scenario to cover, let’s say, a worker strike at a production facility. Quickly transforming the existing plan and inventory to reflect the sudden unavailability of massive amounts of product is not possible, particularly when inventory is being fed into the model by the ERP system and cannot easily be changed. In fact, ERP and MRP systems are mostly incapable of incorporating “imaginary” scenarios (such as strikes) into the platform in order to answer common “what if?” questions that planners have.

We need a way to take the existing SKUs, networks, plants, and demand, and overlay our human context of the situation on top of the model.  This means not only assessing the situation operationally, but financially as well – making these strategic decisions requires visibility into our end-to-end supply chain costs and revenues. Instead of opening a blank spreadsheet file and starting from scratch, we need a way for all 6 planners to work within one model simultaneously, adding their respective inventory constraints, incoming sales orders, and suggested rebalances. As the situation changes daily or even hourly, each planner should be able to enter the model and add a new scenario for lead time delay, or a new assumption for how much inventory could be transferred from one plant to another to make up for lost production and reduce holding costs. Once the newest version of the plan is optimized, it should be able to be sent directly back to the ERP and MRP systems for execution.

If you or your S&OP organization are caught in this relentless planning cycle, a professional financial modeling solution can unleash flexibility and agility like never before. That’s where Quantrix comes in.

  • Load in your organization’s supply/demand/production/business structure to ensure every SKU, warehouse, minimum order quantity, transportation lane, cost, etc. is included in the model
  • Receive real-time inventory and order updates from your ERP, MRP and other systems
  • Write formulas using real language (such as product names, months, goods in-transit) rather than cryptic cell coordinates
  • Create new scenarios and hypothetical cases on the fly with one-click (with automatic formula and business logic extension)
  • Overlay financial data with operational plans to see a holistic view of your organization and make informed strategic decisions
  • Maintain a single source of truth with one model and multi-user collaboration in real time
  • Preserve security with granular user and user group permissions to ensure planners (and sales, manufacturing, procurement, etc.) only have access to the portions of the model they need
  • Deploy the model online to share with stakeholders and quickly gather buy-in (and make new scenarios directly from presentation mode without having to edit formulas)
Monthly S&OP Outlook | Sales Plan: Inventory Projection – created in Quantrix

As a former supply chain modeler, I would love to discuss with you how our financial modeling software can reduce the time your planners spend fixing spreadsheets and battling corrupted macros or add-ins to create new forecasts. With Quantrix, S&OP professionals can have greater confidence when the next disruption occurs and someone says, “We need a new plan.”

Lyndsey Weber
Senior Solutions Consultant




Free Trial  Book a Demo

Ignore Spreadsheet Structure Rules for Stronger, Speedier Business Models

Imagine you’re an F1 driver, eagerly awaiting the race this weekend. But when you show up at the circuit bright and early they hand you a shovel. “The gravel pile is here, the tar is over there. Your teammates will be running the excavator – with luck you’ll finish the track by the end of the quarter and we can start our trials.”

“That stinks!” you say, and heft your shovel for the first scoop of gravel. “What are you doing?” you hear from over your shoulder. “Are you just going to build the track any old way? We have construction rules here! But, good news, we have best practice classes to help you understand how to set the radius of the turns, the allowed grade angles, the smoothness requirements and all of that.”

“Oh, you’re right, without those best practices, we drivers would be careening off the track and piling into each other in no time. Errors could be very costly and painful!” Sadly, by the time you’ve finished the classes, the quarter is over and you’re supposed to be training for the next race – such is the life of a professional racer.

Financial Modeling Software?

If you are in strategic finance, sales operations, supply chain management or other planning intensive activities and using spreadsheets… this silly story might sound familiar. The same tool that everyone uses to plan their potluck dinner, the soccer team lineup, their grocery list, etc. etc. just isn’t optimized for the kind of business planning you need to do. Although there can be similarities, financial modeling software is not the same thing as a pile of spreadsheets.

Spreadsheets provide the ultimate in agility, but this strength is also a weakness in that building a financial or operational model in a spreadsheet isn’t just about the math and business logic, you also have to build the road (i.e. structure) using “best practices”. The quotation marks aren’t there because those practices aren’t good, in fact, if you are using a spreadsheet, they are crucial in order to avoid errors and to put up guard rails around your models!

Spreadsheet “Best Practices” Explained

If you’d like to learn about spreadsheet modeling best practices we’ve got you covered. In our upcoming webcast, we’re going to walk through and explain a list of commonly recommended approaches to spreadsheet modeling, tell you why they are important and then tell you how to completely ignore them and enjoy more agile, powerful and robust models as a result.

After the webcast, you’ll have the knowledge to get out on whichever kind of track you drive and … close the books quickly, make that M&A decision confidently, rearrange your supply chain or propose that complex hardware/software/services deal and win it!

Sign up below to join us at the upcoming Quantrix webcast premier!

Brad Hopper
General Manager

Register Now