Excel Demand Analytics – Fast Formulas on 65K+ Rows

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.