How to Build Your Own Planning Systems – With No Coding

In this video I’m going to show you how to build your own planning and scheduling systems without knowing any software coding.

If you work in manufacturing or supply chain you understand the importance of planning and you’re the kind of person who wants to get the process right then this video is going to be of interest to you.

I’m going to show you the one thing that makes it possible to build powerful planning systems, really quickly, for companies of pretty much any size. And I’m going to prove it to you by giving you three ways to demonstrate how you can do it too.

Imagine This Scenario

Let me ask you to imagine a scenario. You want to improve the company’s planning performance. You want to get better on-time deliveries and customer service, but at a lower level of inventory and cost. But, the computer system the company has doesn’t support the planning process that it needs. You need a system that fits the process, rather than trying to build your process around somebody else’s syste.

So you decide to build a planning system yourself. On Monday, you talk to users and sketch out a design. On Tuesday, you gather data. On Wednesday, you start developing. And by the end of the week you have a prototype ready to test. Inside a month that prototype is tested and ready to implement. Oh yes, and you did all of this without knowing any software coding or having access to any software developers.

Sounds far-fetched right? I thought so too. I’m a management consultant and I spent the first eight years of my career doing process improvements and factory transformation. This is working with people and processes, making systems visual and driving everything to customer demand.

The Problem is When It Came To Planning

The problem was when it came to planning. My clients were mainly high-mix manufacturing businesses. They had hundreds of finished products, thousands of parts and tens of thousands of order-lines every month.

Planning is a data-driven process. Even with the best Lean practices this is too much data for someone to do manually. One option would be to partner with software companies like SAP or Oracle. But that didn’t really sit right: I’d be selling somebody else’s software for millions of dollars and still getting the feeling the client wasn’t getting something that worked for them.

Besides, the approval process for half a million dollars or more of software could be months, if not years. The client really needed something that works faster than that. Then about ten years ago, I went to speak at a conference. This conference was in a country far away from the manufacturing hub I was working in.

At that conference I met a guy who was building planning and scheduling systems for large companies like 3M, Dell and Motorola but he was doing it using Excel spreadsheets. At that conference he taught me something that changed everything when it came to planning. I want to teach it to you, today.

How It Is Today

Fast forward 10 years and I’ve built a whole branch of a consulting practice out of delivering planning and scheduling systems using Excel spreadsheets.

We build systems in days and weeks (rather than months and years). We’ve built a whole website to give away and show examples.

However, the speed of development is not the best thing. The best thing is the flexibility. I believe that any computer system should serve the process rather than having your process serve somebody else’s idea of a system.

This was such a shift to my approach to planning, I ended up partnering up with this guy. His name is Tony Rice and he founded the Fast Excel Development Methodology. Since then, we’ve taken the method a lot further. Now it really is the best practice for developing planning and scheduling systems using Excel spreadsheets. “Use Excel” is not that one thing that I talked about earlier. No. Excel is pretty good at handling large volumes of data without databases and lots of calculations without programming.

However, Excel is really only as good or as bad as the methodology you use to deploy it.

The “One Thing” That I Learned

The one thing I learned relates to how we think about planning and apply it to spreadsheets. It is a human thing, we all do it. It relates to the way that we think in two dimensions. Yes the world is three-dimensional, but when we think, we think in pictures. And that picture tends to be flat . When we think about planning that picture is often a graph or a grid.

We have time going from left to right; a list of products or parts down the side and some volumes in the middle.

This is the picture that people want to see so they create a report like that. They put their data in this form, they add some calculations and pretty soon you’ve got one sheet with a mix of reporting data and calculations in it. The problem with this structure is that it’s very limiting. It’s like a glass ceiling or an invisible barrier that restricts how far you can go.

The One Thing To Develop Your Own Planning Systems

So, the one thing that you need to do to develop your own planning systems is: Separate out data, calculations and reporting.

First of all, we want to get all of our data straightened out. We want to do that in the form of tables. Software developers might not understand your planning process, but they do understand the power of processing data in tables. We want to harness that power for two key reasons:

1. Source Data. Firstly is that it’s likely that some or all of the data that we need for our planning system is sitting in a database elsewhere. We want to connect to it. We don’t want to cut and paste that data into Excel, we want to use that single source of truth and bring the data in. If the data is in table form in the database that’s the form we want to use it in our system.
2. Data Processing. We’re going to have to process thousands of rows of data. We will do many calculations in a full scale planning system. When we put data in tables, we can do tens of thousands of calculations in seconds. Often faster than a typical MRP run.

Now we’ve got our data sorted, arranged in tables and ready to do a series of calculations. We want to lay those calculations out in a process step by step.

Manufacturing and operations people can understand a system process if you make that process transparent. If we do it step-by-step, following alongside the planning process, then it’s no longer a “Black Box”. A Black Box is where people feed data in at one end and do not know how the answer comes out at the other.

If we do all our calculations, step-by-step in a process, we can develop very quickly. Yet, still keep control of the development process.

Rather than spending months and months finding the most complex system, with all of the bells and whistles, we can start with something that’s good enough. Then test it to make sure it meets that basic specification. And then improve and adapt it later on. Once we’ve done the calculations in a process, the next step is to create the reports.

We’ve got all of the data we need in order to support all of the reports we need. Now we can create reports for different users and those users only need the output data for the reports. They don’t need all of the calculations that are built in the system.

So what does this look like in practice?

Here’s the data coming into the system, using the query template sheet. You can tell that their query sheets because they’re green.

Then we have a series of calculation sheets moving across the workbook, left to right, performing the process step by step. At the end we have the yellow reporting sheets. They could be in the same tool like this one. Or they could be in a separate workbook.

Here’s something else and this is something that really makes it possible to link it all together and to develop according to this method.

Dynamic Named Ranges Join It all Together

In Excel, there’s a feature called Named Ranges. As you’d expect, that’s a name that you give to a range of cells.

The key thing is that a Named Range can be defined using a formula. If we can use a formula, we can create a Dynamic Named Range. A Dynamic Named Range is one where the range can expand or contract to exactly fit the data that’s coming into the system.

We use Dynamic Named Ranges to link together all of these sheets and put it all in a single process. When I first learned this method, we used to have to create Dynamic Named Ranges manually. That involved lots of repetitive formula. Wo we’ve automated the process. Now you can create Dynamic Named Ranges at the click of a button.

Fast Excel Development Template and Course

We’ve put this function and much much more into our Fast Excel Development Template. The Fast Excel development Template automates the methodology of using Excel to build planning systems. It takes care of the coding, so that you can focus on the important bits: Namely, building a system that fits your planning process.

You can get the Fast Excel Development Template for free here. It’s packed full of functions and features that we use to build powerful planning and scheduling systems of many different kinds.

Because there’s so much in the Fast Excel Development Template we wanted to create a way for people to learn the fundamentals. The basic ideas and techniques so they can get going on their first build. So we’ve created the fast Excel Development Template Fundamentals Course.

This course goes through eight modules. It covers the overall function of the template workbook, plus each of five template sheets in detail. This way, you can understand how the system works.

For example:

• We have the Query Template. The Query Template gives us two different ways of connecting into data that might sit in a resident system. One using database connections, the other allowing us to use the power of data tables with no database programming required at all.
• The Table Template is a really flexible powerful tool to be able to create those calculation sheets, step by step.
• The Pivot Template works, even if you have no experience of using Pivot Tables. This template brings the power of pivot tables into the method without you having to learn a lot of Excel theory.
• The Stack Template is a really great way of combining multiple tables of data into a single source.

Once we’ve gone through the fundamentals of operating the Development Template, we then have a demonstration build. I’ll take a blank template and build a simple capacity planning tool step by step. Through the video so you can watch “over my shoulder” as I build it. We have broken it down into manageable steps so you can follow along and build the same tool too. Of course, you get the output tool and the starting template as well as the videos as part this course.

But that’s not all I’ve got for you. Soon we’re going to be starting a series of live build classes. The first is the most common planning process for a manufacturing company: Material Requirements Planning. We will be starting with conventional MRP. We’ll have demand coming from sales orders and the forecast. We’ll have supply coming from inventory, purchase orders and other supply orders. we’ll be pushing it through the Bill of Materials to create a requirements for material at every level.

We won’t stop there! We’ll also be looking at what this system would be like if it was more demand driven. We can set inventory targets and generating replenishment orders based on targets that respond daily to changes in demand.

You’ll get the finished tool at the end of the course. But,the purpose of this class is not to give you a planning system. It is to show you how to build your own. So you can create a system that fits your process.

We’re also doing another live build class which is about advanced capacity planning and production scheduling.

In the fundamentals course, we built a simple capacity planning tool. I this one we’re going to go much further this will allow you to load up with the forecast and sales orders, create total demand and show where the capacity constraints are against all of your work centers, in the weeks out into the future.

We’ll also be able to create a simple short-term production schedule which will use finite scheduling to model the exact progression of each sales order and work order through all of the processes.

This way, you’ll be able to understand which of your sales orders will get to the customer on time. This will also be a live build class, we will teach it every week and you’ll be able to ask questions as we explain the steps as we go.

We’re going to be teaching both of these build classes live every week and this way there’s plenty of chance for you to get your questions answered. Ensure that the system that you build is going to fit your process.

We’re also going to limit participation in the class in two key ways:

1. We want everybody to have been through the Fast Excel Development Template Fundamentals Course and know how to use the template. This way, everybody is starting from the same base position with all of the key ideas. It’s also the only training that you’ll need in Excel in order to follow along with a live build class.
2. We’re going to limit the numbers. We’ve had over 60,000 people download our capacity planning tool and the material planning tools from the website. But we want a very small group to go through these first classes to ensure that it’s interactive and everybody has a chance to make it fit for them.

So here’s everything you’re going to get:

• You will get the fast Excel development template which is the best way of building powerful planning and scheduling systems using Excel.
• You will get a place in the fast Excel development templates fundamentals course. These are the core ideas and techniques that you’ll need to use the template to build your very first planning and scheduling system. As part of that course, we will put it all together and build a simple capacity planning tools step by step and you’ll get the finished tool afterwards for reference.
• You will get a seat in our live build class where we build a fully functional MRP system in Excel and we go beyond MRP to show how to get it demand driven by generating replenishment against dynamic inventory targets.
• You will get a seat in our advanced capacity planning and production scheduling course. Here we’ll build a system that identifies capacity constraints and shows you what action needs to be done in order to relieve them. it will also create a short-term production schedule, show the progression of all of the sales orders and work orders through the process and which ones are going to get to the customer on time.

You get all this for one payment of $297.

Join How To Build Course - Material and Capacity Planning

I mentioned the places are limited for good reason so to help you make the decision today I’m also going to include access to the Fast Excel Development Method eLearning Modules.

This is the first eLearning course that i used to fully learn the fast Excel Development Method. Some of the examples are a little dated because they are using Excel from ten years ago. However, the principles and techniques are as valid today as they were back then. This is normally a $200 value and if you order today you’ll get that one included too.

So thanks very much for watching. We put this package together because we really believe it’s the best way for you to get going on building your own planning and scheduling systems using Excel. Thanks again and we look forward to seeing you in one of the classes.