Excel Demand Analytics – Fast Formulas on 65K+ Rows

by Kien Leong

Demand Analytics Chart Image

Analytics for demand planning in Excel usually involves big tables of data.  To understand the demand for a product, you need to look into its history.  Analytics with an order history over a year or more can have 100K+  records.  Here is a technique to perform fast analytical formulas on many thousand rows.

The order history will come out of a transaction system, usually MRP / ERP.  Each order record is a single order for a product, so it is a raw, lumpy transaction history.  It is helpful to smooth this demand out, so a common analytics calculation is the rolling average.  A rolling average over n days is much better than a weekly or monthly average and allows demand planners to calculate a service level for every part.

The easiest way to calculate this using the Excel AVERAGE formula across a fixed range of cells that represents the rolling average period.  The problem with this method is that it requires your order history to have zero values for non-order days.  After all, in our analysis we want to know what the average demand is for all days (calendar or working) not just the days that happen to have orders for that product.

Furthermore, the order date is not necessarily the most relevant date.  A much better date is usually the due-date or commit-date when the order should ship out.  So, combining the order book with all those extra date records is not practical.

In analytics for demand planning, we want to calculate rolling averages and service levels for every product, on every order day.  This requires single formula that we can apply to the order history and see how smoothed demand changes over time.

It looks like this formula will involve SUMIF and COUNTIF.  More precisely, multiple condition SUMIFs and COUNTIFs for the product code and date ranges.  Excel 2007 has SUMIFS, COUNTIFS formula and those on Excel 2003 can use SUMPRODUCT or  SUM(IF…) array formulas.

The trouble is that SUMIF is a very calculation intensive formula.  Just try and paste a SUMIF formula to every row in a table with 10K+ rows and see how long it takes.  We can avoid doing SUMIF (and COUNTIF etc) on a long criteria range by sorting first.

Excel sorts very fast.  You can sort a table of demand data by product code and date in an instant.  You have the choice to use a pivot table or Data|Sort command in the menu ribbon or bar.  The sorted range can then be identified using the first row and last row numbers.  The first and last row for a continuous list of product codes is a replacement for putting the product code as a criteria.   This makes your criteria ranges much smaller and the formula calculation much faster.

Download a demand analytics example of this and you can see exactly how it works.  This has a simple rolling average analysis for demand history with 65K rows to make it compatible with Excel 2003.

The data originally came from a text file import with ItemCode, Qty, OrderDate and CommitDate fields.  The data connection link is not live in this example, but you can easily set it up for the original file, or add your own demand history using text files to connect with system data.

After refreshing the demand data, hit the “Update” button on the Orders sheet.  The first thing that the macro does is sort the demand history data by ItemCode and CommitDate.  This puts all of the ItemCodes in a continuous range and the dates in chronological order.  The next step is to paste down the formulas that calculate first and last row number for each ItemCode range.  We will use this to specify a range that covers each block of ItemCode records.

This demand analytics example is created with the Fast Excel Development Template which is a free download and contains many useful functions for setting formulas once and applying them to thousands of records at the click of a button.  We use the template to build of planning and scheduling systems, and demand planning is an increasingly popular application.  There are some template video tutorials here to get you up and running with the template.  The Sort function, dynamic named ranges and mutiple paste downs are covered in the Query Template Tutorial.

Back to the demand analytics example.  You can specify a range using the first row and last row numbers using INDEX, INDIRECT or OFFSET.  I prefer to use INDEX as below

INDEX(DateRange,ItemFirstRow):INDEX(DateRange,ItemLastRow)

eg.  INDEX($E:$E,$G11):INDEX($E$E,$H11)

Now the range for the SUMIFS is only as high as the number of records for that particular part.  This makes the calculation much faster.  The actual rolling average formula is like so:

SUMPRODUCT(–(INDEX($E:$E,$G11):INDEX($E:$E,$H11)>$E11-I$6),–(INDEX($E:$E,$G11):INDEX($E:$E,$H11)<=$E11),INDEX($B:$B,$G11):INDEX($B:$B,$H11))/I$6

Where cell I6 holds the number of days in the rolling average period.

Once you have a rolling average value over time, you can apply a service level to each record.  This means that demand planners can work out what is the average daily demand for the periods where it has been the highest.   Here is a more detailed discussion of demand analytics, rolling averages and service levels.

Use this technique to measure demand variation, order frequency over the entire order history.  These are valuable metrics to use to analyze the pattern of orders and understand some key characteristics that help us predict how demand will change in the future.

{ 5 comments… read them below or add one }

John Ingram January 1, 2011 at 4:32 pm

We want to drive our stock to service level. Our current is min max on all items, same numbers.

Can you give me some more details on how to set this? I can give you some more info off line.

Kien Leong January 2, 2011 at 6:02 am

Hello Jon
Please send more info to [email protected]. I’ll be happy to share more details and see if we can find some rules for your situation.

Tim Gerdsen February 2, 2012 at 5:46 am

Beste heer/mevrouw,

Ik ben bezig met een afstudeeropdracht. in deze afstudeer opdracht moet ik duidelijk maken met historische gegevens van productgroepen wat de vraag naar het product is en wat de voorraden moeten zijn per maand. Welke formules en toepassingen van excel kan ik hier het best voor gebruiken?

mvg Tim Gerdsen

Tim Gerdsen February 2, 2012 at 5:54 am

I’m sorry u wrote my question in dutch. My question is, i have to solve a problem with excel. With historic data of products group i have to make a model for this product groups to calculate the expected question of after sales. All the historic dat per product group is available of the last 3 years, with this data i need to calculate the ratio per product group and also involving season influence. For this matter what is the best calculating systamatic in axcel.

Kind regards
tim

Stefan de Kok July 6, 2012 at 11:06 am

Hi Tim,

the topic of what is the best formula to use has not been settled once and for all (and I doubt it ever will). It really depends on the product type and historic patterns. There are 100s of formulas being used, with a few common ones.

If you need to demonstrate in your graduation that you understand how to apply a given formula in Excel, then this website is a great resource. Kien and others have placed some of the best Excel templates I have encountered for free download, and the articles do a great job explaining why these practices work well.

If you need to determine WHAT the best existing safety stock formula is you need to do a LOT of research and narrow it down to the specific characteristics of the products or services you need to forecast. Google is your best bet to finding source material.

If you need to invent a completely new safety stock formula that improves on existing ones, you are probably writing a PhD thesis 😉

If you need some input in Dutch, feel free to email me at stefan at wahupa dot com.

Vriendelijke groet,
Stefan

{ 9 trackbacks }

Previous post:

Next post: