Five things you didn’t know about Planning Worksheets
The planning worksheet is a nasty word to most NAV/BC consultants. In my 12 years’ experience with NAV/BC, I haven’t met many colleagues who would have a similar opinion as I do: Planning Worksheet is what makes the difference between a simple ERP system and a great solution that adds enormous value.
What are Planning worksheets? It’s a tool that balances supply and demand patterns. Based on the rules you define (reorder policies and planning attributes), the tool tries to advise you on what’s best to do with your existing data to satisfy the highest service levels.
The following advice can entirely change your view on the value that Business Central can provide through Planning worksheets:
1) Run a plan only for items on sales orders or production order components.
Most consultants suggest an Order Planning screen for this scenario. Forget that I have never seen that working well. You cannot use any order modifiers (Min/Max, Order Multiple), so it will only be utilized well with extra overhead or expensive modifications.
So how do you do this? The item card summarises demand and supply on the Inventory Tab:
Why don’t you run a plan using these fields?
This filter helped a few of my customers understand how to run planning worksheets and define the correct sequence of actions to achieve the best results. Typically, you want to do this daily to find shortages.
2) The Beauty of Starting Date
I’ve seen companies setting Starting to past date to avoid Warning message types. You couldn’t have been given worse advice. I have met a lot of consultants at traditional partners who did think they were real experts. Still, they don’t understand the difference between Reorder Policies and how to implement this solution correctly. A lot of them likely have not been on site ever. This advice led to Excel spreadsheets being outside the system and mistrusting the technology.
Production – very likely, your production process includes a period for which you can’t change the production plan (e.g., due to setup times profitability, shift patterns, material limitations, etc.). This is called a frozen period, but how do you run worksheets to include it?
Take today’s date, include the frozen period, and set your Starting date accordingly to the future. Why? You probably should not have any demand without balancing supply in the frozen period anyway. And what if you do? The system gives you an Emergency warning – existing demand before the Starting date of the plan without coverage.
This will give you great insight into what’s not been planned and should have been. If you use the system properly, and the solution is accordingly settled (e.g., this is hard a week after going live on BC), you should not have any emergencies as this fantastic tool suggests.
Purchasing – unfortunately, in Planning Worksheets, you can’t define for how long you are buying.
Your start and end date define what demand will be included in planning worksheet suggestions, so the critical thing is to have your longest lead times on items you procure. Double that lead time to get what you need. For example, If your lead time on raw materials is a month, then put two months in your cycle to cover for variations and ever-changing demand.
Every company is different; it might not apply to each company; very often, you need a Sales Plan (Demand Forecast) to determine your procurement plan. I will expand on tools that can fully automate a sales plan creation with minimum effort in future blog posts.
3) Categorise, Categorise, Categorise
When I started with NAV in 2012, I was part of the manufacturing team (our customers were only manufacturers), and it was only possible to get away by diving deep into what this tool offers. I was searching for how to tackle my customer's problems and found something I call the holy bible for NAV/BC consultants interested in the production module: Olof Simren’s blog (thank you, my hero).
The following blog post gave me much insight into how things should be run. Please read this article from 2014: https://www.olofsimren.com/guide-to-successful-mrp-implementation/
Production planner - do you have multiple levels in the BOM, and is it hard to run everything in one go and then pick up relations? Just categorize your data well.
E.g., If you put all your related Items (e.g., top-level items and their subassemblies) in the same category or at least share a common string in the category code (e.g., MILLING MANUFACTURED; and MILLING COMPONENTS), then you can run a plan for Item Category = MILL*, and you will have all related products in the worksheet without the need of running it twice. It doesn’t matter if multiple final products share your components; get the logic in Item Category Codes right, define the sequence of how you run them, and you nearly won above this beast.
Purchase Agent – Item Category Codes can help, but you will likely need your vendor code to optimize your planning routine. Why would you run that vendor daily if you have cut off with a particular vendor on Wednesday at 3 pm? Run in Wednesday morning and advise the production planner to run his plan for this portfolio the day before yourself. Find logical sequence and path within your ERP data to cover “everything.”
4) Warning types explanation
The Planner's primary tasks are not to miss anything. It’s hard to do so while living a normal life with a sufficient work-life balance ratio and still getting a good sleep. The spreadsheets and off-system processes cannot deliver on this promise, although best practices can.
Typically, when you run the Planning Worksheet for everything, it’s a noisy mess. Which ones to look at first? There are thousands of lines, and most of them make no sense.
First, you should focus on Warning fields and sort them out, as the system categorizes common issues. If you keep deleting the same lines (or ignoring them), there must be something wrong. Your planning attributes might not describe your supply chain rules, so you can’t rely on system suggestions. Fix them, rerun the plan, and try more—every day.
Attention – this warning type have two causes:
Leadtime Issue
Purchase: Your supplier's lead time is longer than the customer's Requested Delivery Date.
Production - your routing lead time is too long to satisfy demand date
Amendments to released Production Orders – e.g., you are already producing on Released Production Orders, and the customer has canceled his order. Are you going to ignore this line? It might cost your organization a lot of money.
2. Exception – your projected availability is below your Safety Stock Value. For forward planner Reorder Policies, you can see this occurring even though your Safety Stock is zero, as your projected availability runs below this figure on the date.
3. Emergency – you either missed something important, or your data is messy. If your sales team members do not tidy up sales orders with outstanding quantities that will not be shipped, planning worksheets will balance demand with supply and tell you to produce and make some money for the organization. But if the date is in the past, the system will classify this as an emergency. Don’t ignore this. Everybody in the company must be responsible for their data (Sales team - sales documents; Production team – production orders)
5) Run worksheets as little as possible. Define strategy for daily, weekly, and monthly runs.
You get real value when you structure your company process so that planning runs are well-sequenced, and departments cooperate in the right manner to achieve mutual goals.
It’s pointless if the production planner runs his worksheets after the purchase agents run their own. The Key to understanding is that demand is driven from the top to the bottom (Sales Item > Prod/Assembly Bom).
You shouldn’t run worksheets daily for everything. Check shortages on sales orders or production components, but do not run everything without any filters, as this will likely only confuse you.
Daily runs – sales orders shortages, production orders components – Net Change Plan
Weekly runs – per suppliers’ cut-off times/production item categories produced weekly.
Monthly runs – execute mid to long-term planning, including next month's Demand forecast to create new supply.
Did you link the content? Please share, like, and comment on LinkedIn. Tell me what you have learned through comments. Are you looking for a partner who can deliver outstanding value? Please get in touch. We have consultancy and development capacity to onboard new customer in rapid time. Thank you for reading.
