One step at a time, and every step makes the next one easier

Start with what you have

There are two ways in.

The tool you have. Your current tool works, mostly. It may lean on macros, on formulas that reach across sheets, and on the person who built it. That is not a failure; it is where nearly every planning tool comes from, and it holds years of knowledge about how your operation really runs. The method starts by respecting that.

Your data and a goal. Sometimes there is no tool to start from, or the one you have is so far from what you need that a fresh start is quicker. Then you start from two things: the answer you need, and the data your ERP already holds.

Both ways in begin with the same step, and join at the second.

1. Checked: know what you are building on

If you have a tool

xlrig surveys the workbook: every sheet, table, name, connection and formula, read straight from the file. It finds the things that are hard to see by eye: a formula that no longer lines up with its neighbours, a reference that broke when a column was deleted, an error quietly hidden behind a fallback value.

For each finding, you get a recommendation, and you decide. Some are simple. Some are genuinely ambiguous: a formula that once read a lead time, now points at a deleted column, and the workbook has two lead-time columns it might have meant. Only you can say which. Every decision you make is recorded, and together they become the first written account of what your tool is meant to do.

What you get: your current tool, checked and fixed, and a plain record of its intended logic. Try it on an old version you know was broken. If it finds the problem you once spent days chasing, you will know what it can do.

What it hands on: a verified version of your tool to measure everything else against.

If you are starting fresh

Rough-cut capacity is a common case. You have the orders to make, from MRP or straight from customers, and MRP has assumed every work centre has all the capacity it needs. The question is whether it does.

The check is whether your data can support that answer. Some of what the tool needs comes from the ERP, and each field has to mean what the logic needs it to mean: a due date can be the date the customer asked for, the date you promised or the date MRP wants the order finished, and each gives a different load. Some of what it needs is not in the ERP at all, or not in a form you can trust, such as the hours each item takes on each work centre, and has to be defined as new data that the tool will own. And sometimes the answer needs something nobody holds yet; then the honest result is to narrow the question, or to gather the data first.

What you get: a plain statement of the answer the tool must give, the fields that carry it, and the data still to define. What it hands on: the first draft of the tool's data contract.

2. Legible: build in blocks

Most planning tools start with the result someone wants to see, often a matrix: items down the side, weeks across the top. A matrix is quick to build, but every new question means reworking it. It is fast in the beginning, slow in the middle and brittle in the end.

The method builds the tool from blocks, each with an input, a logic step and an output: a column, a template sheet, a module. Because each block's output is a table, the next block can read it, so today's end result becomes tomorrow's intermediate step, and a new question is a new block, not a rebuild. Every boundary between blocks leaves a table you can open and look at. The matrix you wanted is still there, as a view at the end.

You choose the form. The Fast Excel Development Method, with its macros, is one of the most transparent ways to build a planning tool in Excel. Modern Excel, with Power Query in place of macros, is another. Going without macros is a choice, not a requirement.

How you know the rebuild is right: both versions run on your data, and on variations of it that you agree (a busier month, a missing lead time, an item with no supplier), and their results are compared column by column. The rebuilt tool is ready when it matches the checked one.

If you started fresh, there is no old tool to match, so the check is different and, to be plain, weaker. The tool's totals must reconcile with your data: every order's hours appear once, on one work centre, and none go missing. And you run it on a week you know, and the answers must be ones you recognise.

What it hands on: a named table at every boundary, which is the beginning of the tool's data contract.

3. Proven: hold every block to its contract

Each block now carries a data and logic contract. The data contract says what goes in and what comes out, and the rules the data must meet before anyone plans with it; the term comes from software and data engineering, and IT will recognise it. The logic definition says how the answer is worked out, grown from the record you started at the first step. The tests hold both, every time the tool runs.

If you have worked with process validation, the idea will be familiar: the columns the next block reads, or that you act on, are to a planning tool what critical quality attributes are to a process. And because history is only one of the ways things could have gone, the tests run the tool across many possible futures, not only the one you happen to have.

What you get: numbers you can defend, and a tool that explains itself without its author. What it hands on: tests that make it safe to change any block.

4 and 5. Movable and shared: where it leads

Once every block is held to its contract, a block can change without the others noticing. The one block that is too slow, or too hard for formulas, can move to Python, while the rest stays in Excel. In time the tool can run for the whole team rather than on one laptop, with data arriving through connections that check it at the door, and results landing wherever people look: back in Excel, in a web page, in an email.

There are many ways to do each of these, and the right one depends on your organisation: what you are allowed to install, where your data lives, which platform IT is heading for. The method keeps those choices open and makes them late, so the tool stays yours whichever way the organisation goes. The worked examples show the route.

The toolkit

xlrig is the toolkit the method is built with, and it grows with each release.

What the method is not

• Not a replacement for your ERP. The ERP remains the system of record. The method builds the planning around it.
• Not no-code. The early blocks are formulas; later ones may be Power Query or Python. The method is about owning the logic, in whatever form suits it.
• Not a way round IT. It gives IT what it needs to take a tool on: a contract, tests and a record of what the tool does.
• Not a product to buy. It is a way of building, taught here in the open.

Next

The worked examples follow, starting with MRP. Register and we will tell you when they open.