Here’s An Excel Method That Every Planner Should Know

by Kien Leong

Exploding Tables in Excel Planning

Here’s something that may not surprise you.

Excel is the number one application for planning.  Yet, Excel is not a planning application.

71% of companies use spreadsheets “as a technology enabler” for Demand Management.

(Aberdeen Group, Demand Management in Discrete Industries, November 2007).  Excel is not a demand management application either.

That planners rely on spreadsheets, this is no surprise to anyone who has spent time in manufacturing.  Billions of dollars spent on software licenses and development and still less than a third of users find their enterprise applications intuitive and easy to use.

So, more planning is done on Excel than any other application.  This is not because Excel is a great planning application.  Rather because it is flexible, transparent and evidently there is a lack of a usable alternative.

Bad Workmen Blame Their Tools

Now, spreadsheets are not immune to criticism.  Most Excel files carry the shame of many spreadsheet sins.  No-one wants enterprise data strewn across multiple files and locked up in static, error-prone workbooks.

Excel is a tool.  As good or bad as the person operating it.  It takes a little time and effort to learn the principles.  Like anything else, Excel planning excellence comes with good habits and practice.  We want excellence in everything;  but, alas, we never have the time.

If you do want to invest the time, the payback can be awesome.  I reckon that an hour spent learning Fast Excel best practices will save ten hours in higher productivity and less manual work.

Don’t take my word for it.  I will prove it to you.  First, learn how to avoid the biggest production planning mistake in Excel.  Go on, click that link and read the article.  I’ll wait for you here.

Next, let’s have a look at an Excel Method that every planner should know.

Exploding Tables

You should know by now that Excel planning is best done by calculating with tables of data.  We want to read data in a two-dimensional matrix.  Products down columns and dates across rows.  The problem is that it is a horrible way to calculate. 

Computers organise data in tables.  Humans want to view reports in matrices. Calculation is awkward and nasty in matrices.  The logical answer is to make calculation sheets and reporting sheets separate and distinct.  Calculations are calculations, reports are reports and never the twain shall meet.

Do all calculations in tables and you will be set to apply this powerful and versatile method.  It is called “Exploding Tables”.

Exploding tables leverages the power of hierarchical data.  Planning involves working with two kinds of measures- time and volume.  Time already falls into an easy and familiar hierarchy.  Minutes roll up to hours, hours to days, days to weeks and months.

Forecasts usually come in months or weeks.  Purchasing likes to work in days.  Production and capacity planning prefers minutes and hours.

This structure also applies to product and volume.  Families are made up of product items.  Products comprising of components.  Manufactured items composed of process operations.

Simply put: Exploding tables is a way to take data that is expressed at an aggregate level (family forecast, product demand) and generate the data at the lower level (SKU-level forecast, component demand).

One Method, Two Alternatives

To learn how to explode tables in Excel, you have two choices.

  1. Use our Fast Excel Development Template.  It is fast, it is built in Excel and it is a template.  And it is free.
  2. Learn three formula and do it yourself.

If you want to use the development template, there is something that you should know.  This template is for developing systems.  It is not a template for a system.  Use it to perfect the six main table operations: Query, Join, Stack, Explode, Sort and Pivot.

I have recorded a set of nice tutorials for you to learn how to use how to use the template.  You can access them all here.

If you prefer to jump straight to the tutorial one Exploding Tables then you can do so.

The manual method and its principles are described below:

How to Explode Tables in Excel

Exploding tables is a way to take a set of values for an aggregate or parent item and express them in terms of a detailed lower level.  For example, taking demand at a parent item level and expressing it in terms of a child item.  Or taking a forecast expressed in months and converting it to weeks.

Download the example workbook for Exploding Tables.  For users with Excel 2003 and older, please click this link.

The first step is to calculate how many child items there are for each parent.  So, take our parent-child example.  We need to know how many child items there are for each parent item record.

A list of sales orders and with a count for the number of child items

A pivot table of the BOM will help to make this calculation.  There is a sheet called BOM_Summary which lists the parent items, counts the number of child items and also calculates the first row number for each parent record.

This method uses INDEX and MATCH to join the tables and look up the value.

This prepares everything we need for the explode table.  The Explode table is created from our TABLE template which pastes down a set of formulas to a specified number of records.

Sheet=Explode_Example_Manual. Explode Table with Calculation Formulas

 

The formula A8 is calculating the row number for the SalesOrder sheet.  It will start at the top row, 11, and then stay on row 11 whilst  B8 counts up the child records.  The count of child comes from an INDEX of the SalesOrder sheet where we joined the number of child items in the first step.

The rest of the table joins in the whole SalesOrder sheet, calculates the BOM row (from the first BOM row and the sequence of children) and then joins in the BOM table.

The final section calculates the material requirements quantity for each parent-child record based on the BOM quantities.

It is much easier to follow if you download the examples here or by clicking here for 2003 users.  This example has both the manual method and the one using the Fast Excel Development Template

Explode Multi-Level BOMs and Other Tables

This BOM explosion will work for all single-level BOMs.  To explode a multi-level BOM you need to repeat this method for each level.  We will be publishing a post with a multi-level BOM and MRP example.  Stay tuned and subscribe to our list to get an update when this is released.

Exploding tables is a well-used method and operation for developing planning systems in Excel. 

Here are five major applications of this technique in an integrated planning system:

  • Exploding a monthly forecast to weekly volumes through a historical/predictive split
  • Exploding a family forecast to SKU volumes through a historical/predictive split
  • Exploding parent demand to a lower child level through the BOM
  • Exploding manufacturing orders to work orders through the Routing
  • Exploding weeks of calendar into total capacity through a list of work centers

Download the PS Cycles system and see these table explosions in action.  Our Capacity Planning Tool also shows the last three of these table exploding applications.

If you are not already on our list, please click here to subscribe, get free access to these downloads and many other useful updates.

{ 4 comments… read them below or add one }

Fatih ÇOLAK October 31, 2011 at 11:45 pm

I use Excel Spreadsheets for planning too. and esspecially I m following your articles. I almost added more than 50 improvements after read that articles. This article and the article which is about external data connection with Excel, can use multiple users tricks are the most useful ones for me. Thanks for that. Hope to get more knowledge in this site and meet you guys.

gerdichia May 20, 2012 at 9:20 am

I need a software for my production planning.
what I need the field to suit my need as mention: Customer,Part name,Part no. cav. Purchase order,opening stock,production run qty.There should be Purchase order received less open stock then balance to be run. The production run qty less the Purchase order balance to be run. Report able to shown balance to be run by first in date requirement.

tommypan October 26, 2012 at 9:58 pm

currently, I am using pivottable alone to make planning for purchasing and produciton. I would like to learn more with you.

Le Cen October 28, 2014 at 5:13 am

I am not a planner, but IT staff to support planner.
Like many ones mentioned, I think this site is amazing.
I am very grateful of what was done. If possible, I would be happy to be a contributor as well.
It inspired me in 2 views at least:
1) About Excel utility.
I learned about how to leverage Excel from brand-new view.
Technically multi-level design (DB-Logic-Present) is not new concept, but applied in Excel with such flexible and well-designed template is awesome. Especially Leong’s blog teaches us step by step from a planner aspect, I very appreciated this. Will recommend this side to my customers, of course, I get to say maybe it’s more suitable for senior users who master Excel skill quite.
For myself, most tricky thing is exploding tables. I will continue to see how to do multi-level explode.
2) Thought about MRP/FCS.
Around 2 years ago, I began to touch MRP/Finite capacity scheduling system(before that I had 10 years IT industry experience). Exactly speaking, I implement Asprova (APS package software which has No. 1 share in Japan market). After spending a lot of time with our customers in manufacture schedule planner position, I found it’s very difficult to fulfill all requirements from them.
In theory we supposed package software works accurately, but actual world (Inv, SO, PO, result…) not works like that, also gap between systems (PDM, MRP, MES / manual work) can’t ensure schedule valid.
Fortunately our customers is “clever” enough to handle the trouble in various workaround ways, in most time by manipulating Excel. For us IT staff, Excel is a very useful & lightweight tool, it helps in prototype quick verification as well.

{ 9 trackbacks }

Previous post:

Next post: