Who should read?

This article is for those who are involved in planning/budgeting/forecasting and FP&A in large, multi-country/multi-currency and progressive companies.  I am proposing a seven-step action plan for FP&A agility. Let us not worry too much about the W, U, V and the L’s – we can’t do much about that, so let us take care of the F, P and the A.

Introduction

We have all heard about the five economic/business-related macro-revolutions (or industrial revolutions). The first three relate to the advent of the steam engine, then the age of science and mass production, and recently the rise of digital technology. All three have profoundly impacted the. The forth, the “communications” or internet revolution, brought upon a fundamental change in the way we live, work and relate to one another. This has also been brought home by the COVID-19, social distancing and remote work. This revolution has been assisted by tremendous advances in fibre optics, collaboration software platforms such as Microsoft Office 365 and TEAMS, and social media. Now we are experiencing the fifth revolution, where there is a rise in even more advanced technologies like machine learning, internet of things (IoT), artificial intelligence/neural networks and Blockchain. This will impact every human being in their workplace and in their social interactions both physical and virtual.

The Coming Third Revolution in FP&A

But this article isn’t about industrial revolutions, but instead, the revolutions related to enterprise financial planning and analysis (FP&A). In this article, I want to focus on the upcoming third revolution in FP&A that is of considerable relevance to the “Office of the CFO”. The key message is that we need to go beyond profit and loss (P&L) based models irrespective of how sophisticated these may be, to be ready for future turbulence. These seven steps “call to actions” are based on my thirty years of experience in FP&A, activity-based costing and shared services architecture on a global scale. The first two FP&A revolutions would be obvious to all, but I want to mention these first to set the stage. Subsequently, I will provide three illustrative examples demonstrating that the third revolution is upon us and we need to rethink and restructure our current FP&A models and even master records in our ERP systems to be ready for another systemic event such as COVID-19 even if it is of less severity and rolls across countries at different times. Then I am proposing a seven-step action plan for FP&A agility.

My main point is that the models in the third revolution would require a much larger degree of granularity of data from both internal and external sources for actionable insights requiring a redesign of the current models.   It is also possible that the current technology platform may not even have the functionality to incorporate this data expansion since many of these platforms are based on an Excel-like sheet-based architecture with limited multidimensional modelling capabilities due to their multi-tenant architecture of one size fits all.

FP&A: A Revolutionary History

The First Revolution in FP&A – The Era of Spreadsheets: The first revolution in FP&A started with Excel workbooks when we were able to put all Excel worksheets in one workbook and crosslink using VLOOKUP, OFFSET and SUMIFS. This was accompanied by improvements in hardware and software from Winintel 256 to 356 to 456 with an incredible reduction in CPU and hardware costs. Any smart business analyst in the FP&A team could then build sophisticated and sometimes elegant FP&A models in Excel and provide management and financial reporting briefing books with graphs, charts and slicers. These “Excel jockeys” rose in the FP&A hierarchy due to their Excel wizardry.  However, it became obvious that these Excel-based models have significant limitations and we need better technology for ensuring that FP&A is cost-efficient, more collaborative, error-free and robust with adequate controls and not dependent on “Excel jockeys”.

The Second Resolution in FP&A – The Arrival of FP&A Software Applications: Then came the second FP&A revolution, leading to a move away from Excel to Corporate Performance Management (CPM) platforms. These technology systems provide all of the functionality available in Excel but could now be “modelled” in a way that was shown to be more flexible, collaborative, controllable, workflow-based and could cater to both financial reporting and planning and budgeting/forecasting and even multi-country, multi-currency consolidations. Depending on the scale and the scope of the enterprise, these platforms could cater to 10 to 1000’s of users and, at the same time, ensure that the controls are in place and human errors are minimized. The journey to the cloud also reduced reliance on IT to deploy these solutions, making it more rapid along with a lower total cost of ownership.

Getting ready for the third revolution in FP&A

If one reviews the FP&A platforms from the second revolution and the models built within them (including those built-in Excel), it is clear they follow very predictable patterns – irrespective of size and scale. I assume that since all of you who live and breathe planning/budgeting/forecasting/FP&A, you can relate to the description below with some variations to the main items. I am describing these is to set the stage to expose their limitations and to set you on a journey for the third revolution.  So please, indulge me and read on.

These models typically have six components:

1. Naturally, the first and most important component is a revenue model that caters to the complexity of the business and can be as granular as individual product/service/project mapped to individual customer/channel coded to the individual salesperson for better planning, reporting and accountability. Some of this data comes from CRM systems or is built within the FP&A model and the historical and actuals come from the sales sub-ledger of an ERP platform.

2. Next comes the cost of goods sold (COGS) model where the bill of material (including variable labour, overhead and material) is linked to the revenue model to get the gross margin. This is then linked to a production model (in manufacturing) and/or a procurement model (e.g. in distribution with long supply chain) or direct driver-based workforce planning (in case of health care services industry). This component depends on the industry.

3. The third component is workforce modelling, sometimes linked to revenue model, but sometimes focused on cost centers (departmental/SG&A) view since revenues may be fairly predictable or workforce budgeting is done through a zero-based budgeting view. This component considers ‘people’ and all associated salaries and benefits.

4&5. The next two components are non-people OPEX (can be driver based) and CAPEX/fixed assets (which can be linked to strategic intent). Many times, the CAPEX model is based on when the ‘asset’ is expected to be acquired as opposed when it is expected to be paid for, meaning it is designed to calculate depreciation and amortization but not for providing any insight into the treasury impact. Some even have some capacity planning built into these models.

6. Some companies have an additional component in their FP&A Models. There are companies where people work across projects or cost centers or where shared services allocations are important to calculate fully loaded costs of product/service/project/customer. Such companies require more sophistication in modelling since it may even require a waterfall cost allocation and dynamic drivers for cost allocations.

Depending on the dynamics of the business, these P&L based “inside-out and stop” models are redone every quarter, either to the end of the fiscal year or on a four-quarter rolling basis.  Models with these six components have been in operation for many years. To be fair, by using these models, companies have been able to model various scenarios for investigating the impacts of changes to key parameters. By building an overlay of “CFO in-a-box” to quickly investigate the impact of various scenarios/what-ifs on the bottom line before briefing the CEO or the Board of Directors, this proved to be very useful.  However, as you will see from the three illustrative examples below, these are inadequate and have been found short of what is expected by the CEO and the Board of Directors for actionable insights.  COVID-19 has also exposed some fundamental weaknesses in these models that I can now characterize as models built with “outside-in and stop” thinking. Whereas now we need models that are “outside-in and then inside-out”, looking at stakeholder impact and the entire end to end value/supply chain.  This is what I term as the coming third revolution in FP&A or CPM platforms.

Let us also assume that the CPM platforms are built under the second revolution paradigm and not in Excel.

Three Illustrative examples

1. Company #1: Distribution Company

My first example is about a distribution company. It has two main business lines with completely different characteristics. The first business line is medical equipment, prescription and non-prescription drugs, and medical consumables. The customers of this business line are naturally doctors, dentists, pharmacies, clinics, labs and hospitals. The second business line is in consumer health and beauty products. The key customers of the second business line are pharmacies, small shops as well as supermarkets and small chains.

The CPM/BI platform architecture consists of direct automated SQL queries to the enterprise resource planning (ERP) system for actuals and the forecasts are done in Excel with a fairly sophisticated Revenue and Gross Margin model. The required granularity of the model is at the “product/customer” level but not at the “supplier” level and has a separate cost center model. The suppliers are in fifteen countries and the assumption is that when products are ordered, these will arrive in 10-60 days and for critical drugs, these can be air shipped from the manufacturer or a third-party distributor. The owner group has considerable financial strength and has been able to finance revenue growth so far with ease. Since there is enough warehouse capacity, there is no modelling for capacity. Moreover, the company can hire contractors and trucks, as needed, at a known cost to fulfill demand. There is no linkage required in the model between SKU and its impact on “offload and put away” or “pick-pack and deliver” in the warehouse and inbound/outbound logistics. The forecast model results are then automatically fed to a sophisticated business intelligence (BI) tool which exposes the results to senior management. It does not require too much thinking that this client is completely unprepared for any quick modelling in the light of a systemic event.

In the wake of COVID-19 as a mass systemic event, the business is today being impacted at almost all levels: hospitals need products that are beyond the “max” inventory quantity in the ERP system. Stockouts started immediately impacting its claim to fame as “when you need us, we are here.” Some products simply stopped being ordered for reasons such as elective surgeries stopping, non-pharmacy channels closing, Chinese suppliers no longer responding and some manufacturers diverting normal shipments to their large and favourite distributors.

It comes as no surprise that an Excel-based model, even when supported by a sophisticated business intelligence platform, is simply incapable of responding to a COVID-19-type event or even a somewhat less significant systemic event. Not only is there simply no time to “tweak” and adjust Excel-based models quickly, but it is also impossible to quickly account and adjust for the myriad of dynamic variables. Unfortunately, this company learned the hard way that Excel, although great in a vacuum, was not built to bear the weight of enterprise planning.

 

2. Company #2: Professional Services Firm

My second example comes from a global professional services company with 5 business lines, 15 locations and clients in 5 countries. This company offers both managed and non-managed services. The company has built an elegant and close-to-a-second generation planning and forecasting model that contains the required degree of granularity in the revenue model and a driver-based cost model. The cost model leverages a rate card and ‘consultant type’ per project for quick gross margin calculations using both a standard and fully loaded rate, which also allows for price flexing. The diversification strategy across business lines/locations and countries have given the company decent stability in revenues and profits (and thus partner happiness). The FP&A team does monthly/quarterly forecasts and can create scenarios that can tell the CEO and managing partners the impact of changes in forecasts on the income statement.

Sounds great, right? Well, sadly, this model is also not going to be very useful in an event like COVID-19. The reduction in probabilities of existing bookings and expected bookings of services is going to impact both revenues and profits; however, since the model was not built for agility, it cannot provide actionable insights. This model can be described as more of a “project-based P&L” model.  It now must be changed or enhanced significantly to provide decision-makers with the tools to act. This means that the model’s granularity must be increased by:

• linking services/projects to teams and individuals that are delivering or expected to deliver these projects and at which locations
• building elasticity of customer’s business (and to be even more agile, the elasticity of customer’s customers’ business) that would impact their demand on services and thus impact cancellations or deferrals
• ranking customers based on their financial strength to withstand this event and continue the work
• categorizing projects as “mission-critical, semi-critical, nice to have” from the customer’s lens (irrespective of a signed SOW) as well as whether it is managed or non-managed
• categorizing projects based on remote versus on-site commitments, which individuals can be furloughed and who must be kept on the bench and even the financial strength and flexibility of landlords from whom we rent the office properties

 

3. Company #3: Beverage Manufacturer

My third example comes from a more sophisticated company with a model that, on the surface, is ready to react to a COVID-19-type event and can provide actionable insights. This company is a beverage manufacturer that makes 70+ SKUS under four brands, 4 sizes of bottles and 4 packaging options. It delivers these products to large and small customers using dynamic dispatching for domestic customers and container shipping to foreign customers from plants in two countries. For foreign customers, the company uses a consolidated optimized delivery model to account for dynamics in demand. Some orders come through the web, some are recurring, and some are through tele-sell. The beverage plants are highly automated and require the right type and sizes and quantities at the right time of materials (preforms, caps, labels, shrink wrap, chemicals, etc.) purchased from both domestic and foreign suppliers with different lead times and payment terms to feed these sophisticated and automated high volume throughput plants to maintain high Overall Equipment Efficiency (OEE) metrics. The capital structure has both debt and equity, with loan covenants on both domestic and foreign currency loans and overdrafts.

The company has built a very sophisticated end-to-end CPM model that starts with sales forecast by SKU, by key/non-key customers and by route. The sales forecast is linked to procurement using integration with the inventory module of the ERP system for BOM. This is integrated into production planning which then checks against min-max, quantity in transit and lead time and then provides a first level procurement plan. Once the procurement plan is approved, it creates quotes or PO’s in the ERP system which then gets routed to suppliers. Using the PO’s as an input, the model is designed to provide treasury forward-looking requirements for various currencies as per the suppliers’ terms for hedging purposes, if required. This forecasting, reporting, analytics, and consolidation platform is sophisticated and provides the right information at the right time to all decision-makers on any device – 24/7/365.

We may characterize this model as an almost third FP&A revolution model, but sadly and to our dismay, it still lacks complete and total agility. In a systemic event such as COVID-19, this model is unable to deliver actionable insights on:

• varying impacts on the countries where it exports, its key customers, changes in the demand for its products (some are must-have, some are nice to have; some are price sensitive; others are not).
• which lines in which plant would require double shifts and which would require shut down (and what happens if the shutdown is non-voluntary due to infections in the workforce)
• which employees and supervisors are critical to running certain lines or contacting customers and suppliers to keep the supply chain adapting the fluidity of the situation
• the relative valuation of currencies, operational and financial stability of suppliers under such an event, constant communication with the bank with respect to covenants and interest relief and debt repayment extensions and responding to its requests for flexing of assumptions.

If this company was simpler and smaller in scale (e.g. had very few finished good SKUs, and less than 10 raw material SKUs, two lines, few customers and suppliers), then this could be done by the CFO on paper. But when the business is large, even this second-generation sophisticated CPM/BI model is not designed for agility beyond dealing with small changes in demand. The good news for this company is that the platform is an all-in-one (i.e. fully integrated, end-to-end), is modelled to suit its needs, and can ingest an infinite level of granularity without impacting performance. So, confronting the third revolution for this company is much easier than the companies in the first or the second example.

A Seven-Step Action Plan for FP&A Agility

These three examples show challenges faced by the FP&A teams and the CFO to build increased degrees of sophistication in FP&A models, with a high degree of agility. Many, if not all, existing models are designed with an “inside-out and stop” mentality, meaning the ultimate output is typically the projected income statement and cash flow statements and only sometimes balance sheets. These models were designed from a financial and management reporting perspective and for dealing with revenue estimates of minus 10 to plus 10 percent. But COVID-19 has woken all of us up to the fact that we need to start thinking about the third revolution in FP&A: The Age of Data & Agility.

So, what specific actions are demanded from you to adapt to this third revolution? Here is a list of seven specific actions that you need to consider and execute.

1. Think multidimensional! First and foremost, you’ll need to be able to bring in a large amount of multi-dimensional data points for each of your key stakeholders. Then, change the models so that all your data and variable have end-to-end linkage. This will ensure that you can react to ever-changing and fluid situations and be able to model across multiple scenarios.
2.  Enrich the master records: This may even require making changes to master records in your ERP system or even COA and dimensions. Taking the time to adjust your system setup and structures to account for new data will pay off.

3. Bring in external data sources! Bring in data from outside of your ERP. This may include data about substitute products and suppliers, customers, and even data on and codification of your customers’ customers. Overall, the more data the better and if the examples above highlight anything, it is that just because you don’t think you need it today, if it relates to any aspect of your business, it can be used and will be crucial for agility and planning.

4. Link to business performance metrics: If your company has built concepts like Balance Scorecards or strategy maps, your FP&A model should be able to provide a directional impact on the key dimensions of these models. These models also need to have the ability to do various top-down adjustments, and not just at the COA level, but deep into the sales models. 

5. Build enterprise thinking: For multi-subsidiary multi-country multi-currency companies, you will need to find a way to show the impact at the consolidation level. So play in the details but be prepared to report on the big picture.
6. Model capital provider needs: These models must be able to provide capital providers (e.g. banks, investors, analysts) the information they need based on what they think would happen to the business environment. So when modelling, be sure to incorporate not only your predictions but consider the variables and conditions that your capital providers will want to see considered.
7. Don’t stop there: Last but not least, these models would also us to manage our stakeholders (key customers, suppliers and employees) with agility and foresight. I am sure you may think of many more ideas that pertain to your business. So, document these before you forget!

Conclusion

So, there you go, integrated data & model agility are the key elements of this third revolution in FP&A that has descended upon us (albeit far more quickly than we would have liked – but such is change). In the wake of this shift, many of us will have to go back to the drawing board and inspect systems and processes for deficiencies and risks. If you are using Excel (no matter how impressive your workbooks are), or if your models are built in a vacuum and used mainly for financial reporting, then how will you plan for the now, not to mention for future such events?

It doesn’t feel like it today, but the economic impacts of COVID-19 will subside. It seems almost counter-intuitive to speak of growth at this moment, but the world economy will recover, and one thing we all must do as finance professionals is to learn from this experience and never let ourselves be blindsided again. To quote Mike Tyson – “Everyone has a plan until they get punched in the face.” It is our duty as finance professionals to safeguard and strengthen our enterprise performance and health. Go back to your systems and processes without ego or bias and start making the changes you need to rebound now and to futureproof against similar future hits as best possible. Leverage the tools available to you (no excuses now) and learn whatever is needed to finally link every dollar, data point, and variable across your entire enterprise into one pool of truth and be ready to model for every possible scenario in an instant. Information & agility will be what defines the success of the CFO in the new normal.  Let us not worry too much about the W, U, V and the L’s – we can’t do much about that so let us take care of the F, P and the A.

 

 

 

Author: Vijay Jog is the founder and president of Corporate Renaissance Group (CRGroup), a Quisitive Company (QUIS) and Ottawa-based firm dedicated to transforming business management and performance. He has led CRGroup’s growth in areas of strategic finance, corporate performance and dashboards, strategy design and execution and helping clients bridge the gap between technology and finance. Dr. Jog consults with organizations around the world and is a leading author and speaker in the areas of corporate performance and the office of the CFO. 

 

For additional resources, visit crgroup.com/model-cfo