Posted on 1 Comment

Why Are Spreadsheets Still A Common FP&A Tool?

“The status quo is not a neutral state, but a mindset to uphold the decisions of the past.”

In 2023, effective business planning, budgeting, and forecasting is a necessary capability to keep organizations running. Already this year, we have seen unexpected banking failures, unpredictable labor markets, and continued supply chain and logistics challenges based on geopolitical challenges. In light of these challenges, Amalgam Insights believes that businesses must have a shared version of the truth that they use as they look at their budget and finances.

And, in this case, we specifically talk about a “shared” version of the truth rather than the “single version of the truth” typically associated with data warehouses and enterprise applications. This is because data changes quickly and every stakeholder can potentially make different decisions to define and augment their data, even basic changes such as language and currency translation that can lead to different versions of the truth. In this analytically enhanced and globally complicated world, it makes more sense to have a shared version of the truth that is augmented with personalized or localized data and assumptions. However, this consistently shared version of the truth can be hard to accomplish in organizations where planning is handled in a distributed and personalized manner through spreadsheets. In the enterprise world, finance professionals are inured to the basic realities of auditable data, processes, and results. And they are often asked to provide reports and memos that are used at the executive level or by external investors and public markets to ascertain the health of the market. Given the assumed importance of this formality, why would experienced professionals use spreadsheets in the first place?

Let’s face it; spreadsheets are easy to use. They are the lingua franca of data; a format that every experienced data user has been trained on. And with plug-ins and Visual Basic, spreadsheets can now handle relatively complex analytic use cases. Even if they aren’t quite data science tools, spreadsheets can provide structured analytic outputs. Also, spreadsheets are accessible on every computer through Excel, Google Sheets, or other common spreadsheet software. And with the emergence of cloud-based spreadsheets, it is now possible for two or more people to collaborate within a single spreadsheet.

Spreadsheets also provide users with the ability to customize their own analytic views with their own personalized views of data and the ability to hypothesize by building their own models. Who hasn’t looked at data and wondered “what if the data looked a bit differently?” or “what if we have a drastic scenario that suddenly increases or decreases a fundamental aspect of the business?” In light of COVID, rapid interest rate hikes, global shortages in commodities production, trained labor shortages, and the increasingly unstable banking environment we are in, it is important to be able to test potential extreme assumptions and support a wide variety of scenarios. Between the ease of use, availability, and personalization aspects of spreadsheets, it is not hard to figure out why spreadsheets are still a leading tool for financial planning and analysis. Even so, Amalgam Insights has found that once organizations pass Dunbar’s number (approximately 150 employees), they start to struggle with collaborative tasks simply because it becomes difficult for any one employee to know all of the other employees who need to be involved in the business planning process and spreadsheets have been designed to maximize individual productivity, rather than collaborative work, for decades. From a practical perspective, people tend to work with the people they know best. This is fine for a small company with a dedicated office where everyone knows each other. According to US Census data, the typical 1,000-person company has 19 locations, making it highly unlikely that all of the key budget stakeholders will be in one office. In this regard, Amalgam Insights finds the following challenges in supporting spreadsheet-based planning at scale.

The distributed nature of work also makes spreadsheet governance a challenge, as it is easy for spreadsheets to suffer from version control issues, a structured workflow process, and for file owners to lose control of the inputs and outputs that they are responsible for supporting. The lack of version control, workflow, and activity tracking is especially challenging in industries and geographies that require tracking of any personal data either related to employees or customers.

Spreadsheets also struggle in large data environments, which are quickly becoming commonplace in the business planning world. Although a core enterprise database may only be a few gigabytes, accurate planning now often includes access to sales, operations, and potentially even IT transactional data sources that can quickly expand beyond the memory and data size constraints that spreadsheets are designed to use. From Amalgam Insights’ perspective, the size and variety of data are the biggest technical constraints that spreadsheets face as planning solutions.

Spreadsheets lack advanced analytic and machine learning capabilities. Although algorithmic, statistical, and machine learning tools are increasingly becoming part of the FP&A world, especially in forecasting, Amalgam Insights finds in practice that most organizations have not yet embraced complex analytics as a core part of their FP&A approach. Based on current job site metrics, Amalgam Insights estimates that less than 2% of FP&A professionals currently have a machine learning or data science certification or degree, making this an early innovator capability that has still not crossed the chasm to become a standard job requirement for FP&A.

But perhaps the most significant challenge with spreadsheet models is that they are often fragile: created based on the logic of a single person rather than on defined business logic and with little to no documentation associated with the plans, forecasting algorithms, and multi-tabular complexity that inevitably occurs when a spreadsheet is the primary planning tool for a business, which can also lead to costly data accuracy issues. The model is only as adaptable as the spreadsheet creator’s knowledge of the industry and is dependent on that employee staying employed. Considering that it is unrealistic to expect an FP&A senior analyst to remain in that role for more than five years before either getting promoted or getting a better offer, this human risk is a significant challenge for business planning solutions.

As organizations grow in size to support more than a handful of locations and a set of workers that exceeds Dunbar’s number of 150 colleagues, Amalgam Insights believes that it becomes necessary to adopt a formalized planning solution that supports collaboration, scale, advanced analytics, continuous planning across many scenarios, and advanced forecasting analytics. Otherwise, it is difficult for businesses to maintain a consistent and shared version of the truth across financial planning and analysis personnel that can drive both departmental and executive planning efforts.

Ultimately, the use of spreadsheets as a formal system of record for business planning is a risky one for any organization with a formal corporate structure, governed industry or geography, or any organization that has a significantly distributed business. The ubiquity of the spreadsheet makes it an easy place to start modeling a budget, and the value of the spreadsheet in helping users to structure small datasets will exist for the foreseeable future. But the fragility of the data structure, lack of user and version control governance, inability to scale, and the difficulty of verifying data with other sources while avoiding human error all lead to the need of supporting a more formalized planning solution over time. As organizations face a future of keeping distributed groups focused on a shared version of the truth and collectively consider a variety of scenarios at any given time, the risk of spreadsheet fragility needs to be matched up against the value of using a formalized FP&A solution designed to analyze, govern, and protect all relevant business data, formulas, and outcomes.

Posted on

Navigating The Road to Retail Analytic Success

Analytics in the Retail and Consumer Packaged Goods (CPG) markets is more complex than the average corporate data ecosystem because of the variety of analytic approaches needed to support these organizations. Every business has operational management capabilities for core human resources and financial management, but retail adds the complexities of hybrid workforce management, scheduling, and operational analytics as well as the front-end data associated with consumer marketing, e-commerce, and transactional behavior across every channel.

In contrast, when retail organizations look at middle-office and front-office analytics, they are trying to support a variety of timeframes ranging from intraday decisions associated with staffing and customer foot traffic to the year-long cycles that may be necessary to fulfill large wholesale orders for highly coveted goods in the consumer market. Over the past three years, operational consistency has become especially challenging to achieve as COVID, labor skill gaps, logistical bottlenecks, commodity shortages, and geopolitical battles have all made supply chain a massive dynamic risk factor that must be consistently monitored across both macro and microeconomic business aspects.

The lack of alignment and connection between the front office, middle-office, and administrative analytic outputs can potentially lead to three separate silos of activity in the retail world—     connected only by some basic metrics, such as receipts and inventory turnover, that are interpreted in three different ways. Like the parable of the blind men and an elephant where each person feels one part of the elephant and imagines a different creature, the disparate parts of retail organizations must figure out how to come together, as the average net margin for general retail companies is about 2% and that margin only gets lower for groceries and for online stores.

Analytic opportunities to increase business value exist across the balance sheet and income statement. Even though consumer sentiment, foot traffic, and online behavior are still key drivers for retail success, analytic and data-driven guidance can provide value across infrastructure, risk, and real-time operations. Amalgam Insights suggests that each of these areas requires a core analytic focus that is different and reflects the nature of the data, the decisions being made, and the stakeholders involved.

Facing Core Retail Business Challenges

First, retail and CPG organizations face core infrastructure, logistics, and data management challenges that typically require building out historic analysis and quantitative visibility capabilities often associated with what is called descriptive or historical analytics. When looking at infrastructure factors such as real estate, warehousing, and order fulfillment issues, organizations must have access to past trends, costs, transactions, and the breadth of relevant variables that go into real estate costs or complex order fulfillment associated with tracking perfect order index.

This pool of data ideally combines public data, industry data, and operational business data that includes, but is not limited to, sales trends, receipts, purchase orders, employee data, loyalty information, customer information, coupon redemption, and other relevant transactional data. This set of data needs to be available as analytic and queryable data that is accessible to all relevant stakeholders to provide business value. In practice, this accessibility typically requires some infrastructure investment either by a company or a technology vendor willing to bear the cost of maintaining a governed and industry-compliant analytic data store. By doing so, retail organizations have the opportunity to improve personalization and promotional optimization.

A second challenge that retail analytics can help with is associated with the risk and compliance issues that retail and CPG organizations face, including organized theft, supplier risk, and balancing risk and reward tradeoffs. A 2022  National Retail Federation (NRF) survey showed that organized retail crime had increased over 26% year over year, driving the need to identify and counter organized theft efforts and tactics more quickly. Retailer risk for specific goods and brands also needs to be quantified to identify potential delays and challenges or to determine whether direct store delivery and other direct-to-market tactics may end up being a profitable approach for key SKUs. Risk also matters from a profitability analysis perspective as retail organizations seek to make tradeoffs between the low-margin nature of retail business and the consumer demand for availability, personalization, automation, brand expansion, and alternative channel delivery that may provide exponential benefits to profits. From a practical perspective, this risk analysis requires investment in a combination of predictive analytics and the ability to translate the variance and marginal cost associated with new investments with projected returns.

A third challenge for retail analytics is to support real-time operational decisions. This use case requires access to streaming and log data associated with massive volumes of rapid transactions, frequently updated time-series data, and contextualized scenarios based on multi-data-sourced outcomes. From a retail outcome perspective, the practical challenge is to make near-real-time decisions, such as same-day or in-shift decisions to support stocking, scheduling, product orders, pricing and discounting decisions, placement decisions, and promotion. In addition, these decisions must be made in the context of broader strategic and operational concerns, such as brand promise, environmental concerns, social issues, and regulatory governance and compliance associated with environmental, social, and governance (ESG) concerns.

As an example, supply chain shortages often come from unexpected sources. An unexpected geopolitical example occurred in the United States when the government’s use of containers as a temporary barrier to block illegal immigration checkpoints on the US-Mexico border led to shortages at US ports for delivery. This delay in accessing containers was not predictable based solely on standard retail metrics and behavior and demonstrates one example of how unexpected political issues can affect a hyperconnected logistical supply chain.

Recommendations for Upgrading Retail Analytics in the 2020s

To solve these analytic problems, retail and CPG organizations need to allow line-of-business, logistics, and sourcing managers to act quickly with self-service and on-demand insights based on all relevant data. This ultimately means that to take an analytic approach to retail,     Amalgam Insights recommends the following three best practices in creating a more data-driven business environment.

  • Create and implement an integrated finance, operational, and workforce management environment. Finance, inventory, and labor must be managed together in an integrated business data store and business planning environment or the retail organization falls apart. Whether companies choose to do this by knitting together multiple applications with data management and integration tools or by choosing a single best-in-breed suite, retail businesses have too many moving parts to split up core operational data across a variety of functional silos and business roles that do not work together. In the 2020s, this is a massive operational disadvantage.
  • Adopt prescriptive analytics, decision intelligence, and machine learning capabilities above and beyond basic dashboards. When retail organizations look at analytics and data outputs, it is not enough to gain historical visibility. In today’s AI-enabled world, companies must have predictive analytics, statistical analysis, detect anomalies quickly, and have the ability to translate business data into machine learning and language models for the next generation of analytics and decision intelligence. Retail can be more proactive and prescriptive with AI and ML models trained to their enterprise data to support more personalized and contextualized purchasing experiences.
  • Implement real-time alerts with relevant and actionable retail information. Finally, timely and contextual alerts are also now part of the analytic process. As retail organizations have moved from seasonal purchases and monthly budgeting to daily or even hourly decisions, regional and branch managers need to be able to move quickly if there are signs of business danger coordinated revenue leakage, brand damage across any of the products held within the store, unexpected weather phenomena, labor issues, or other incipient macro or microeconomic threats.