skip to Main Content

In Excel We Trust! It Depends…

Best Practices  
11/13/2014

There is no question that Microsoft Excel is one of the most popular business tools used throughout the world. The power of Excel is extraordinary and while many skilled users are able to make it perform complex data management tricks, they could be costing their company a fortune when used to make business decisions. If you find yourself to be one of these savvy users ask yourself, do you truly know the rules behind the formulas? Are you confident when editing sheets that the integrity of the calculations and data are preserved? Do you spend enough time learning the story behind the numbers or is too much time spent just getting to the numbers?

For the sake of this article I will mostly be referring to the demand and supply planning areas of a business. A common problem in a lot of small businesses that are growing is failure to recognize when the business has reached the size that is better served with a more subject specific software to handle all the heavy lifting. It can be hard for the small to mid-sized businesses that currently run everything off Excel to agree to implement new software. It is easy to rationalize and simply stick with what you know best and continue to push the limits of the workbook.

I know this too well. Early in my career I was much more than a skilled Excel user, I was an obsessed “Excel Junkie”. The transition became easy once I accepted a simple business rule for Excel:

Microsoft Excel is best served as a reporting tool and short-term analysis tool.

When I finally accepted this rule, my value to the business increased dramatically. From a business perspective, keeping this rule in mind will not only improve your efficiency but likely your accuracy as well, with the byproduct of course being cost-savings. Depending on the level of business decisions that are being made off large data sets analyzed in Excel, the risk of errors can cost the business tremendously.

I was lucky to start my career off in a start-up LED-lighting manufacturing company, backed by private equity, and showing incredible annual growth in sales (our first year sales were approximately $50M, the next year sales were approximately $130M). Like many start-ups, people wore multiple hats in the company. I was in charge of sales analysis, operations analysis, financial analysis, demand planning, the S&OP process, and unofficially “Chief Excel Junkie” (self-proclaimed title). My life was consumed by data housed in an ERP system. Unfortunately for me, the data was incredibly inaccurate. I quickly became consumed with data manipulation in Excel followed by very large 100MB+ Excel analytical files. 95% of my time was spent on just data manipulation and building or rebuilding analytical tools in excel that usually ended with getting too big and crashing. I was always frustrated and even more importantly only 5% of my time was actually focused on the job at hand to interpret and understand what the numbers from the analysis were telling me.

One of the board members who was also like a mentor to me changed my perspective on managing my role when he noticed how obsessed I had become with Excel. He sat me down and said:

"You either own the data or you let the data own you."

At first I thought, do I need a new mentor, does he think he’s Mr. Miyagi or something? Over the next few days he started mapping out for me what a smooth running, efficient, process driven company looked like. My eyes were finally open, it was as if I had just been converted to a new religion. My loyalty to Excel had finally been broken and I was enlightened with a few critical ideas:

  • I had been ignoring the root issue and only perpetuating the problem by using Excel. We needed to clean the data housed in the ERP system and then implement appropriate business rules to ensure the data going forward would be accurate. Managing the cleansing outside of the system only exasperated the problem.
  • Because were cleaning the data issues outside of the ERP, the “accurate” numbers were living in numerous spreadsheets and never matched other functional areas numbers. In essence, each functional area was singing from a different sheet of music.
  • By cleaning the source data, I could begin using the ERP system reports. Most of the reports were standard in the system and could be updated in real-time by a click of a button. If the report did not exist in the system it could be built within the system. This allowed for a drastic change in efficiency, consistent numbers throughout the company, and allowed the executive team to identify and react quickly to changes in the business.
  • No matter how big your Excel ego is (I took the crown for this), when dealing with large data sets and complex calculations you may build it right the first time, but the second you start tweaking it or let other people play around with it you’re doomed. Here is a great article to read by Jeremy Olshan: 88% of spreadsheets have errors that illustrates the problem.
  • The most common problem I notice in Excel is people that are sending anything in a table structure leaving filters set or hidden rows. Its incredible how many mistakes were a result of this very simple oversight. My best advice if you work in Excel is to add the “Clear Filters” command to the quick access toolbar. This will allow you to quickly see if a filter is set if it shows a red X as opposed to gray.

  • In regards to sales forecasts, spending all your time organizing the information in Excel will leave you with practically no time to spend on the important task of forecast validation. The power of most demand planning tools is incredible, with the functionality to quickly get you the type of information needed to help drive reliable and quantifiable planning decisions that can be continuously improved.

 

My greatest triumph over Excel was by far in the area of demand planning. I remember a painful chapter in my early career trying to run a forecast in Excel that would blow out to the SKU’s and then the more painful process of forecast validation. Even focusing simply on A-items via ABC analysis took up a huge chunk of time when formatting templates and trying to view the data at multiple levels. DemandCaster , the software my company finally selected after a long road of implementing other software allowed me to view the forecast quickly and from numerous aggregations to pinpoint problem areas. It also easily exported to Excel which allowed me to fine tune an executive reporting template tailored to my executive teams preference and more importantly kept the heaving lifting in the software while simply using Excel to supplement reporting. At the end of the day time, money, and accuracy were all saved from the software implementation.

 

Breaking the Excel habit is no easy feat. It can be difficult getting people to step out of their comfort zone and open up to change. It takes time to get the savvy Excel user to adopt a subject specific software, but they will quickly start noticing positive changes to the business and to their job. I came to understand this so well that I ended up joining to work on DemandCaster full time!

Making the decision to implement subject specific software to supplement the reporting power of Excel is the important first step but it doesn’t end there. Selecting the best software for your business is the next big step. I will be sharing my experience trying to implement the best demand and supply planning software in a three part series titled “

The Painful Saga of Sales and Operations Planning software.

 

Related Articles

Best Practices for Resource Capacity Planning for Small and Medium Businesses

Manufacturing can be challenging in the best of conditions.  But for small and medium-sized businesses (SMBs) striving to remain competitive with large companies, their smaller size and reach mean that planning is even more crucial.   Small and medium-sized businesses often [...]

Strategies for Optimizing Customer Service Levels

  While every business works diligently to improve efficiency and operational excellence, at the end of the day it’s the customer who determines a company’s success.  And companies are looking for new ways to optimize customer service levels to improve [...]

Going Green in Supply Chain

Can supply chain professionals help their business become more sustainable? Absolutely! You will be helping the environment, but going green can also help your business with brand building and efficiency. It can even be as simple as moving to the [...]

Computational thinking in today’s industry

Industry Week defines computational thinking as, “A structured approach to problem-solving, which ultimately allows you to come up with a solution that can be effectively carried out by computers, people, or—typically—a combination of both.” This is a great tool when [...]

A Focus on the Eighth Lean Waste in Manufacturing

There were originally seven wastes of lean manufacturing, but an eighth was added in the 1990s that should not be ignored! Dave Turbide said, “Variously called unused human creativity, under-utilized talent, incorrect use of abilities, unused human intellect, or simply [...]

The Life of a Demand Planner

What do the first 100 days look like for the average demand planner? What trends does a demand planner hope to establish when embarking on this new journey? Much like POTUS, you can’t go it alone -- you need your [...]

5 Most Common Mistakes Made in Sales Forecasting

Selling anything, from widgets to wide-load trucks, isn’t an exact science. There are fluctuations in demand, in supply of raw materials, in shipping and more. That said, getting as close to reality as possible in sales forecasting is how you [...]

Manufacturing Performance Metrics for Producing Products

Delivering goods and products begins with raw materials, which vary widely by industry and manufacturing process. No matter the industry, however, all inventory must be sourced and managed. The most effective way to do so is by measuring inventory turnover. [...]

Awareness And Knowledge Arise From Demand Planning Basics of Cross-Functional Alignment

Supply Chain, finance, commercial, marketing, and any other department that can impact sales volume, are all integral parts of a business. When it comes to the awareness, knowledge and alignment, cross-functional collaboration best practices are key to seeing both opportunities [...]

How are Millennials Affecting the Supply Chain?

Millennials want more than a good price. They want availability, speed and convenience. More than that, they want to be included in the actual co-creation of the product. To millennials, it's about the experience. Enter "experiential supply chain." At the [...]
Subscribe to our Blog
Back To Top