Using Excel to construct your retail plan this holiday season? You’re doing it wrong

Share this Story

Blog post by Tom Jones – Anaplan Solutions Consultant

I’ve helped to build out some of the most complex promotional planning tools known to FP&A in my role as a Sr. Finance Analyst directly supporting the Entertainment group within a major Fortune 100 retailer.  The only thing we were ever sure of at the end was that we were wrong.  Minimizing the gap between wrong, and the actual result, was my mission (I stalwartly accepted). I also harbored the added bonus objectives of giving insight into mix shifts driving margin adjustment, attributing success & failure to competitive promotional behavior, and providing as close to a real-time view into progress toward our objectives as possible.

In attacking this monumental task I had to use the tools of my trade, including MS Excel, MS Access and a host of various on-premise business intelligence tools (SAP Business Objects, Hyperion Essbase, Qlikview, Terradata, among others).  Each of these tools serves a purpose, however, Excel was the only tool flexible enough to allow me to change the nature of my model to fit the business environment as it changed.  To make matters more complicated we had consultant manufactured in-house tools built using Excel & Access enabling teams to roll up assumptions and projections at a SKU level for their independent categories (no two of which were the same of course).  To achieve my goals I needed to integrate the necessary pieces of information from each team, and each tool, ensure the information was current, and synthesize answers for every possible question leadership might ask.

  • Based on current projections when will be out of stock on our Black Friday Doorbusters?
  • If we price-match SuperCorpTM  on back-to-school leg-warmers what will our margin profile look like for all of apparel? Can we do it regionally in the Northeast only?
  • What’s the elasticity on our Micro-Widgets? If we go down another 10% on our ASP can we expect to move the rest of our position to free up space for Micro-Widgets 2 next year?

To be honest, these questions were easy, the hard questions always dealt with the shifting nature of the promotions themselves.  SKUs were added to promotions on-the-fly, and often not communicated until the last minute. Promotion terms were extended or shortened depending on inventory, as was territory pricing. Additional vendor funding was provided, changed, removed, and then added as a future product credit instead of cash on a per-product basis without thought to how it might affect the analysts!  Forget about adding the complexity of channel management, the online space would have to take care of itself!

What I needed was a tool that could be changed as fast as the business itself changed.  I needed to be able to move products within their hierarchies at will.  I needed to be able to analyze products, pricing, and volume plans across any dimension I had the data for, and I needed to be able to give management the flexibility to do the same without requiring a 2-week training session on the model.  Finally, if I had a tool like this how could I adapt it for the next promotional period? What if I got promoted for doing such a great job and the team couldn’t repeat their success next year? I needed something extendable AND repeatable, that doesn’t take a degree in rocket-science.  Analysts should spend their time providing insight, not programming, am I right? I needed to solve problems, not create new ones.

If I’d had a tool like those the Anaplan Platform enables, I could have cut out weeks of work building custom analytical tools.  The SaaS environment puts the planning capabilities in the cloud making it easy for the entire team to be involved in collaborating on the best approach, while sharing accountability for the results as they actualize.  Anaplan makes multi-dimensional modeling across product hierarchies, geographies, business units, time, segments, channels, and demographics simple as pie.  Any dimension you want to define is simply a new list you can either set up in seconds, or a list you can link back to your ERP systems for constant updates that keep your dimensions aligned across the organization.  I could drag-and-drop within any hierarchy whether it be territories or products as my needs changed. By being in-memory and using Anaplan’s proprietary HyperblockTM technology, changes at any level could be applied across hundreds of thousands of SKUs, thousands of stores, and all of my channels and segments in seconds! That’s not hyperbole, it literally takes seconds to re-calculate across data points that are orders of magnitude greater than anything Excel or even Access can handle (we’re talking 100’s of millions to billions).

Once the tools were built and made available to executives I could have sent them the web address for the environment and they could have looked at updates as soon as they came in with no additional processing necessary, let alone batching or number-crunching. The best part outside of enabling management to pick and choose what they wanted to look at, and how they wanted to review it, was that it could be scaled to any part of the business, any new set of holiday promotions, or instantly handed off to a successor.

Old staid legacy tools used for personal productivity certainly have their appeal and purpose, but they will never be powerful enough for true enterprise analytics in complex environments where accuracy isn’t even enough. Speed across complex hierarchical calculations is now a standard requirement instead of a feature. Being able to build a model is a given, being able to change the model on the fly with little IT or even modeling expertise is the future. Re-forecasting tomorrow based on today’s results, pivoting the business based on changing environments, and having the tools you’ve built automatically change with you is paramount in maintaining a competitive advantage.

Tuesday’s with our Solutions Consultants is updated the last Tuesday of every month on the Anaplan Blog. Interested in connecting with Tom Jones to discuss promotional planning tools or just want to connect with him Tweet him here @tommyj314 or Tweet us at @anaplan and let us know! 

 
About the Author
Tom Jones has been with Anaplan as a Solutions Consultant since April 2012 on the Pre-Sales team. Prior to Anaplan Tom helped to define and build, best-practice financial modeling, planning, and analysis tools at Best Buy in the Entertainment group as a Sr. Financial Analyst. Tom aids the team in demonstrating the value of the Anaplan platform to customers by drawing on experience across a wide set of industry verticals including Healthcare, Banking, Transportation Logistics, and Retail. Tom holds a BS degree in Computer Science from the University of Minnesota, as well as an MBA in Finance from the Carlson School of Management at the University of Minnesota.


Leave a Reply