FP&R, or, Why we kicked the spreadsheet habit

0

Are you missing the “A” in your FP&A (financial planning and analysis)?  Maybe missing some of the “P” as well?  Are you and your department getting a bit tired of the “FR” gig you seem to have landed?

I just got back from chairing last week’s IE Group Financial Innovation Summit in Boston, and I don’t have to be told twice that this issue has touched an exposed nerve.  Day 1 saw no fewer than three of the dozen or so speakers address this specific topic.  If this hits too close to home for you too, don’t fret, you are not alone – almost halfway through the second decade of the 21st Century there are still a surprising number of large companies conducting substantial financial processes via spreadsheets.

And as I’ve heard at nearly every FP&A conference I’ve attended over the past two-and-half-decades, ever since I was a neophyte finance manager, the 80/20 challenge is still alive and well: how to flip that 80% data collection and reporting over into 80% planning and analysis.  After 20+ years we do seem to have made some progress – the consensus seems to be that we’ve cut our transaction-level data gathering and reporting back from 80% to maybe 50%, back from Monday-through-Thursday to Monday-through-lunch-on-Wednesday.

Unless you’re still relying on spreadsheets, in which case you never get to go home early on Fridays.

The “R” Problem

The problem with depending on spreadsheets for the major financial processes (reporting, budgeting and forecasting) boils down to three primary issues:

  1. You can’t do anything quickly with spreadsheets and email.
  2. Once you allow information to be re-keyed into spreadsheets, you have lost significant data quality control.
  3. Lack of transparency / no drill-down to the detail capability.

Depending on your own circumstances it’s probably a coin flip as to which is the worst problem of the first two.  My sympathies lie with the first one:  you will NEVER get all your data collection and reporting done by 5:00pm Monday afternoon if you are relying on spreadsheets.  You simply have no chance of flipping that 80/20 situation around.  Not gonna happen.

But I've also heard the pain regarding the second issue – data quality.  Those most impacted by this problem often phrase it as, “if the data isn’t right, it doesn’t matter how much time you save or allow”.  The ‘data not right’ problem spans the gamut:

  • Operator error / re-keying mistakes
  • Non-standard data collection templates
  • Deliberate malfeasance (i.e. changing formulas to protect the guilty)
  • De-linking from hard won policy and data definitions

That last one, losing the data definition battle, appears to be quite common.  Once you introduce a manual step in the data custody chain, you stand a good chance of losing control of the previous hard work you’ve done to drive data definition consistency.  Is this Service Revenue or Warranty?  Is this Other Direct Materials or COGS?  Does subcontractor travel roll-up into travel or subcontract expense?  You answered all these questions long ago and incorporated them into your formal policies and chart of accounts hierarchy, but give someone the chance to manually key it into the wrong line item, inevitably they will.

The “A” Problem

Let’s now assume that you’ve finally solved the 80/20 problem, that you’ve solved your data collection and reporting problem.  It’s close-of-business on Monday and the data is all there, all clean and correct.  What are you going to do starting Tuesday morning?

I’m serious.  You’ve got four whole days for value-added analysis and planning and business partnering.  This is what you’ve been waiting for.  Are you just going to extract it into spreadsheets, do some sorts and run some pivot tables?  After all the hard front-end work you’ve put into getting to this point, you deserve some real analytical tools.

On the forecasting/planning front, there is SAS Financial Management with embedded, easy-to-use, high performance forecasting (or you can use SAS Forecast Server separately).  Automatically forecast thousands and thousands of rows, tens of thousands of cells, of account-level, SKU-level, or store-level data in minutes, only touching the less than 10% of the data that requires manual intervention and business judgment outside of the automated system.  Know what the confidence intervals are and therefore how much risk there is in the forecast.  Reconcile from top-down, bottom-up or middle-out.  Match supply with demand.  Do some robust scenario planning.  Start with an inherently better forecast, based on real history and statistics instead of “last-year-plus-X%”, and take the time you’ve saved and put that toward working with the business units to make better decisions about production and inventory levels, staffing, and marketing promotions to close the remaining gaps.

On the analysis front there is SAS Visual Analytics, which combines a robust BI platform with embedded analytics, plus visualization techniques that allow you to extract more value from those first two capabilities.  Visually identify correlations, trends and patterns.  Layer in visualization that adds context to the data, be that metrics with visual indicators and alerts, or heat maps, or bubble charts on top of geographic maps.  Compare yields across production facilities, then check to see if there are correlations between the results and specific parts or suppliers.  Which customers are getting the highest discounts, does that match with your expectations, and see if there are correlations between high discounts and particular branches or products.  Compare utilization against staffing levels and product lines across your supply chain to search for potential bottlenecks.

Each one a classic case of “tell me something I don’t know”.  Each one an opportunity to add value to the business operations.

When it comes to getting value from your FP&A efforts, one of the biggest issues is not adequately planning ahead for the “A” part of the equation.  Too many IT-driven BI initiatives stop short of the goal.  They take care of the “R” problem nicely, they get the organization to 5:00pm Monday with the quality data they need, but then leave the scene of the crime too quickly.  Too little thought is given to the downstream business problems that need to be addressed and solved.  A short-sighted BI implementation leaves the “R” institutionally disconnected from any hope for value-added “A”.

When it comes to BI and FP&A process improvement projects, remember to start with the end in mind.  What marketing, distribution, production, staffing, service, risk, and cash management problems do you want to address; what specific business decisions are you looking to better support?  Be certain that you are supporting the “A” in FP&A; don’t just settle for a kinder, gentler FP&R.

Share

About Author

Leo Sadovy

Marketing Director

Leo Sadovy currently manages the Analytics Thought Leadership Program at SAS, enabling SAS’ thought leaders in being a catalyst for conversation and in sharing a vision and opinions that matter via excellence in storytelling that address our clients’ business issues. Previously at SAS Leo handled marketing for Analytic Business Solutions such as performance management, manufacturing and supply chain. Before joining SAS, he spent seven years as Vice-President of Finance for a North American division of Fujitsu, managing a team focused on commercial operations, alliance partnerships, and strategic planning. Prior to Fujitsu, Leo was with Digital Equipment Corporation for eight years in financial management and sales. He started his management career in laser optics fabrication for Spectra-Physics and later moved into a finance position at the General Dynamics F-16 fighter plant in Fort Worth, Texas. He has a Masters in Analytics, an MBA in Finance, a Bachelor’s in Marketing, and is a SAS Certified Data Scientist and Certified AI and Machine Learning Professional. He and his wife Ellen live in North Carolina with their engineering graduate children, and among his unique life experiences he can count a singing performance at Carnegie Hall.

Leave A Reply

Back to Top