Where is Your Data?

Over the years, I have noticed a rather disturbing trend in businesses, both large and small. While sales and operational data is often stored in databases, forecast data is often maintained in spreadsheets. Good forecasting is critical to an organizations ability to deliver goods or services. The failure to do proper forecasting can be disastrous for any size organization. If we produce too much product, we waste money. If we produce too little product, we run the risk of disappointing, and losing, our customers. We have to forecast demand as accurately as possible, for every product or service that we offer.

Part of the process of refining the forecasting process involves a comparison of forecasted results to actual results. If the forecast was significantly wrong, we need to find out why, and use that information to improve the process next time.

Nearly every organization that I have worked with has an interest in reporting actual results compared to forecasted results. That’s the good news. The bad news is that, with forecasted results stored in spreadsheets, comparing the two becomes challenging. Why is this so? Isn’t it pretty simple to build reports using data from databases, and data from spreadsheets, at the same time? Yes, it is. But let’s look at reality.

Let’s consider an organization that has ten different product lines, and a manager for each line. Each of the ten managers does their own forecasting, in their own spreadsheets, with their own formatting. The reporting team has to pull data from ten different spreadsheets, into a single report, and compare the results with actuals, retrieved from a database. However, each of the ten spreadsheets are formatted differently. The data in them is not always in a table format, which is how we need it.

So, the reporting team is tasked with having to reformat the spreadsheets, so the data can be brought into the reports. And each month, they go through the same process, reformatting the spreadsheets. Even if they can convince each manager to use the same format, which would be a miracle, what’s to keep one manager from changing his/her mind down the road, and changing the format again?

I can tell you from experience, reporting from spreadsheets is a challenging task, at best.

Considering the importance of forecasting, I can’t help wonder why forecasting is done with spreadsheets. Why isn’t it done with forecasting applications that store the data in a database? In the long run, the cost of the forecasting application would be far outweighed by the cost savings in eliminating the maintenance of spreadsheets.

Yes, there are plenty of organizations that store forecasted data in databases. These are the ones who have already seen the value of doing it right. It’s a pleasure to build reports for them. But they are few and far between.

Where does your organization store its forecast data? Tell me what you think.

Advertisements

6 Responses to Where is Your Data?

  1. Jansi says:

    Michael, the sad part is my previous client still stores the forecasting data in spreadsheets and MS access database. I would even think that my team is the most struggled team. We ended up in importing this data into DB2 database and then using that as a source we compared the actuals and the forecast. With that said, these are the issues we ran into.

    1. As you said, the format in which people entered the data was too different and none of our imports was successful at least once. We’ll have to cleanse it everytime before loading the DB2 table manually.

    2. When the size of the Access table is too much, the automatic updates which we set up later were not successful because the connection was not active for a long time before the entire data was transferred.

    3. Maintenance overhead: No team came forward to maintain it. The very primary aim of avoiding manual tasks is not met so far.

    The management has to give attention to what the poor development team says. 😦

  2. Santosh says:

    Michael very good post, at my last 2 clients place they had the same issue.. It was a completely manual process of comparing the actual results with 2 sets of forecasts. The expert knowledge to get to the forecast did never make it to the database that was the biggest drawback, at another client site though they had a pretty good understanding of the benefits to put these forecast fields to database, it was never done..

  3. Nikunj says:

    Hi Michael,
    Not sure if this is the right platform to ask this question or not. But could you please explain something about SAP HANA. What is it? Is it a separate reporting tool all together or it is just an another DB like ORACLE and teradata.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: