Here’s An Excel Method That Every Planner Should Know

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 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.

 

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.