Home »
Salescast » Here
Excel as Data Repository will hurt your business
Microsoft Excel is a great and very popular tool among businesses. Yet, like any powerful tool it can do a lot of damage when used improperly. On this page, we explain why
Excel should not be used as a data repository in order to move around data required for sales forecasting or inventory optimization. Again, we don't hold any grudge against Excel. As a matter of fact, Salescast does produce
Excel reports, and we believe it's a
proper usage of Excel of doing so. There are easier, cheaper and more powerful alternatives to Excel to move your data around for the sake of sales forecasting, namely
databases. And what's even better: you don't actually need any database knowledge to benefit from them!
The Temptation and The Fall
This is not going to fly!
One of the most frequent requests we get at Lokad, is the following:
I would like to make a test to evaluate your forecasts, could you please import my historical sales data from Excel? Indeed, benchmarking our forecasts is a
very reasonable request: when it comes to statistical forecasting, we suggest never taking any vendor's word for it (not even ours!).
Yet, what we believe
not to be reasonable is to
import data from Excel. As far as forecasting is concerned, the quality of input data is crucial. If junk enters Lokad, junk forecasts will be produced by Lokad. This is the
Garbage In Garbage Out pattern. No technology can evade this problem, which has to be addressed at the core. Yet, one of the issues inherent to
Excel, is that
entry mistakes are neither detectable nor recoverable (see below for more details).
We believe there are two main reasons that compel people into demanding Excel:
- They feel at ease with Excel, and have no database skills. This is a not an issue, because you actually don't need to know anything about the database that we'll use.
- Company IT is holding back the data, and getting access to the data turns into a political battle. Although infrequent, this one can be real tough. But we can help you to get things moving forward.
We will address those concerns later. But first, let's go a few years back. During the early stages of Lokad, we did
carry out more than 50 company setups based on Excel. Although we are not particularly proud of this part of our history,
we never managed to get a single satisfied client out of the Excel process despite frequent, massive (and desperate) efforts pushed by our prospects on the case.
The Excel import process typically went as follows,
Bob being our prospect:
- Bob sets up an Excel sheet and sends it to us.
- Sheet is not exactly set up the way needed for automated processing, we go back to Bob with a request for changes.
- Bob gives another try, and sends us back the sheet.
- We detect further glitches.
- Bob gives another try.
- More glitches. We send more feedback.
- Bob starts to think: Damn! This data import logic is so rigid, shifting the data from one cell is breaking everything, but Bob keeps trying.
- Insert here half-a-dozen iterations spread over 2 weeks.
- We finally receive the first Excel sheet that we can automatically process. We send back forecasts to Bob.
- Bob has a look and it is disappointing: forecasts do not make any sense at all!
- We investigate the case, input data looks very odd. We forward many questions to Bob.
- Bob spends a lot of time validating inputs. A lot of errors are found.
- Insert here weeks of efforts from Bob to chase subtle input issues.
- Bob sends a revised input sheet. In order to make it 100% correct, Bob has significantly reduced the data size: few products and only a few months of history.
- Lokad produces forecasts and sends them back.
- Bob gets the updated forecasts, which again look disappointing. Forecasts do make sense, but they are not accurate enough.
- Lokad team has a quick look at the case, and the culprit is obvious: the amount of input data is very limited, it's simply not possible to produce good statistical forecasts. We need more data.
- Bob has already spent so much effort on collecting data, and now he has to do it all over again with a 5x bigger dataset. Feeling depressed, Bob gives up.
Obviously, this scenario was only played in full by the most tenacious prospects, as most people gave up somewhere in the middle. The good news is:
there are easier, cheaper and more powerful alternatives to Excel available to you. We will get to this point.
Issues
We presented a rather grim outlook here above. Let's review why such a scenario was repeatedly occurring despite the best efforts from both parties involved - the prospect and the Lokad team.
Issue 1: Excel exports are NOT intended for statistical forecasting
You might think that because your ERP (or company system) is capable of producing Excel reports, it will be good enough as input for the forecasting system:
it won't be - by a long shot.
First, the Excel report will typically be truncated to the last 12 months (or some arbitrary duration). In order to have the most accurate forecasts, you need
all data available. Providing a longer history is the easiest and most efficient way to improve accuracy. Truncating your historical data is just starting the task with a huge handicap.
Second, data is very likely to be pre-aggregated - at the weekly or monthly level instead of providing daily sales. Unfortunately
data aggregation is a lossy process. Much information is lost once the data is aggregated in a tabular format, and the forecast accuracy decreases.
Third, no meta-data will make it into the Excel sheet. In particular,
Tags, which are used to identify relationships between products, will typically not be available. Many correlations between products will be lost, again at the expense of accuracy.
Issue 2: Mistakes are neither detectable nor recoverable
Any user opening an Excel file is free to change its content at will. This is a great feature to get tasks done quickly, but the downside is that there is no way to ensure data quality. If an entry is wrong, Excel does not tell you
when it was introduced, by who, and what was the previous value. Moreover, erroneous entries are hidden among all other seemingly valid entries. Beside spotting obvious errors (like a negative sales value), there is no way to check the content of an Excel sheet except through manual validation of the content of every single cell.
This aspect of Excel's design is painless if you happen to
consume data with Excel; but whenever Excel becomes a primary data repository (Excel is used to enter data), the pain grows exponentially with the number of manual entries. In many situations, this aspect is not an issue, because amount of data is limited. Our experience indicates that when it comes to supply chain, there is nearly always a LOT of data, and the friction is just enormous.
Issue 3: Lock-down on single PC
Supply chain is all about collaboration: there are suppliers, clients and a lot of goods being moved around in-between. It always involves several people. Unfortunately, Excel is NOT intended for multiple people writing in the same sheet at the same time. Every time you
share an Excel sheet by email, you're not
sharing anything but merely
creating new copies of the file.
As a result of there is a sort of
impedance mismatch between the very nature of supply chain, and the mono-computer format of Excel. Any process relying on Excel
to collect data becomes a source of friction at every step. Yet,
consuming data with Excel is fine, because many people can read the same Excel sheet at the same time.
Issue 4: No automation possible
One of the greatest features of Excel is also its greatest weakness when it comes to sales forecasting: you can enter pretty much
anything into an Excel sheet. This freedom gives you the possibility to visualize data pretty much any way you want.
The price to pay for this freedom however, is that (dumb) automated software cannot make sense of this loosely structured data. Software will stumble and fail at processing of the sheet if any cell happens not to abide to the super-narrow processing logic. It only takes one click, one keystroke to accidentally crash automated processing. And what's worse, as soon as you surrender Excel's flexibility to match whatever rigid format is required, Excel loses much of the interest it had in the first place.
Facts
If Excel is certainly NOT the solution to collect input data that will feed a sales forecast or an inventory optimization, what alternative do we have left? Well, it turns out there are many alternatives available, and most likely they are already implemented in your company.
Fact 1: Your company already has a database
Unless your company's ERP (or accounting package, eCommerce Cart, etc.) is 20+ years old, your ERP most probably relies on a
database of some kind. A database is a reliable and structured data storage, precisely intended for supporting all sorts of automated data manipulation. In particular, databases are a very good fit for sales forecasting.
Caveat: Microsoft Access. This product is somewhat halfway between Excel and a database system. Since 2005, Access is being phased out by Microsoft as a much better alternative is taking all the development attention, named
Microsoft SQL Express. Heaviest limitations of Access are lack of proper remote access (hint: Lokad won't be able to remotely pull your data if data are contained within an Access file) and lack of proper support for concurrent users (much like Excel actually).
Fact 2: Testing with a database is both easier and cheaper
You first reaction so far might have been
but we'll only use Excel for testing Lokad. It turns out that testing Lokad (or any other forecasting software for that matter) using Excel only makes the process both more complicated and more expensive. With Excel, it only
seems easy because the path is somewhat straightforward: all it takes is tweaking the sheet until it works. Yet, as mentioned previously, our experience indicates that
tweaking the sheet typically takes
forever - quite literally.
Databases have been developed over the last 3 decades to facilitate the transfer of data in a completely reliable way. You can think of databases as of military-grade equipment precisely intended for the tasks at hand: duplicating your historical data from your system into another.
Fact 3: You don't need database skills
Lokad and your company will setup a data transfer through a database, but you don't actually need to be knowledgeable about databases. Establishing the database setup is indeed technical, but it's far from rocket science. Database administration is a well-established specialty in the IT industry.
Lokad only needs a technical contact with either your internal IT department or your outsource IT provider. In both cases, the amount of work required to get a Lokad demo running is very little, typically a few days for a single database administrator - or even a few hours if your database administrator is both a Guru and very familiar with the existing data setup.
Caveat: for large companies, it might require more effort if the amount of data is significant - but the larger the data, the stronger the benefits to choose databases anyway.
Fact 4: IT won't deny access to the data
In many companies, IT is driving modernization and improvements of supply chain operations. Yet, one of toughest situation we at Lokad encounter is when the IT department is doing the exact opposite and is holding the data back from operations. When this happens, people tend to think that any project that requires the consent of IT (or worse, some actual work from IT!) is doomed. And hence is the strong desire to use Excel, because nothing else will work.
We believe that IT is at the core of the most radical innovations that 21st century businesses are undergoing. IT should be driving your business forward. Thousands of online apps are being pushed to the web and they typically cost only a fraction of what their on-premise ancestors did. Not leveraging those apps can only cripple your company in the face of your competition.
We can't fight the inner battle at your company on your behalf, but we believe it's a case that deserves attention of the top decision makers - no matter how large your company is. Investigation of such matters will not be a waste of time for them. In the long (?) run, it will be a matter of company survival.