Building your own spreadsheet to keep track of important supply chain metrics is a tricky path to take. There are plenty of resources planning tools out there that are already built and which can provide the supply chain planning information you're probably looking for.
But if you're thinking about tackling a spreadsheet from scratch, here are some important tips to help you do it.
Relatively simple spreadsheets that you can make may be able to help you:
- Track and project inventory
- Calculate re-order points
- Project stock out dates
- Calculate months on hand
- And more
But remember, even once you have a spreadsheet template—it's going to be up to you to keep the data current and accurate.
Before you begin, do you have a baseline of data that will make this spreadsheet useful? Here's what you'll need before you get started:
- Current on hand inventory
- Future demand (either forecasts or orders)
- Supplier lead times
- Minimum order quantities (MOQ)
- Order multiples
That data will ensure that you are beginning with a useful supply chain planning tool.
Time Intervals and Planning Horizon
Do you need to do your planning daily, weekly, monthly or with some other regularity? The way to answer this question is to consider how long it takes you to replenish your inventory. That speaks to your supplier lead times.
For the sake of this example, let's say you're putting together a monthly supply chain planning spreadsheet. If you have suppliers who can deliver goods in four to six weeks, then you'll likely have a monthly planning spreadsheet.
Your planning horizon is basically how far out you would want your spreadsheet to plan. You should keep this limited to a useful horizon. Your planning horizon is typically dictated by how far you can realistically predict customer demand.
If your customers have placed orders that cover your demand for the next three months, but you can use history, seasonality and other customer demand data (customer forecasts, for example) to estimate the three months after that—then you can likely use six months as your planning horizon.
Forecasts and Orders Are Not the Same Thing
Beware, not all customer demand is created equal. If a customer gives you a forecast, that's just their way of letting you know what they may end up buying. Forecasts do not typically come with a financial obligation.
If a customer places an order, then you've got a much more realistic chance of realizing the revenue from that sale (although, as we all know, customers can try to cancel orders or return goods once they've been sold).
Building Your Supply Chain Planning Spreadsheet
Now that you've got your time intervals, planning horizon and customer demand sorted out—it's time to build your supply chain planning spreadsheet.
The purpose of this spreadsheet will be to track your inventory as it is decremented over time. So start with your current on hand inventory.
(By the way, you should be tracking your part numbers/items separately.)
At the top of your far left column, enter your on hand inventory. Let's say it's 100.
The first row in your spreadsheet will be "Starting Inventory" for each time interval.
The second row will be your inventory reduction during that time interval—for the current time interval, that would be actual sales. For subsequent intervals (again—days, weeks or months), that will be future orders or forecasts.
The third row is your replenishment row. This is when you'll get more inventory from your supplier.
Your fourth row is your "Ending Inventory" for each interval.
For example—you start January with 100 each. 100 goes in row one. You sell 30 each in January, so 30 goes into row 2. Your supplier delivers 40 each in January, so you put 40 in row 3. You subtract row 2 from row 1 and add row 3 to that total. That number goes in row 4. In this case, that would be 100-30+40=110.
110 populates row 4 and is also copied into the "Starting Inventory" in Feb. By carrying those formulas out into the following months, you will easily discover when you're going to run out of inventory—so you can plan when to place new purchase orders with your suppliers (remembering their lead times, MOQ and order multiples).
The information in your spreadsheet is only accurate if you make sure your inventory numbers are correct. Keep your cycle count programs in place and work with your suppliers to make sure they are delivering per your expectations—and this simple spreadsheet should keep you going until you're ready into invest in more robust planning tools.