This file is available in our store. Contents:
Logic of Net Requirements, Safety and Lot Sizing
Formulas ending with a Supply Order
Scenarios to present illustrative examples, with controls and VBA
Layout and Formatting to entice interaction
Purpose
MRP stands for Material Requirements Planning. It was an early application of computers in industry, replacing manual inventory card files. It plans replenishment of manufacturing materials and parts as they are received, moved, produced, inspected, approved and consumed in production of finished goods.
Good planning balances the economics of production run quantities with inventory holding costs, and accounts for constraints. Derivation of the parameters to drive an optimal plan would be a significant project in itself. My scope here is to illustrate how such parameters are used to calculate replenishment orders, resulting in the plan. Having worked and taught in this area for many years, the topic is near and dear, but I chose it as a project because it affords an excellent self-learning opportunity.
There are two general levels of MRP. At the top is the finished product level, which plans production to meet demand from customers, called independent demand. This level is referred to as Master Production Scheduling or MPS. Below that is MRP for everything that goes into production, where demand can be calculated and is called dependent demand. This project will simulate independent demand, with the option of constant or randomized demand. Both MPS and MRP use the same logic and are often both referred to simply as MRP.
MRP plans future receipts to meet known or estimated requirements, and to maintain an inventory balance. It starts with beginning inventory and some supply parameters, is fed a series of requirements, and it calculates a series of planned replenishment orders with some desired size or frequency. These planned receipts can then be released to a supplier as a purchase order or sent to another operation or plant as a work order for manufacture.
To account for demand and supply uncertainty, safety inventory is commonly used as a buffer. The replenishment calculator plans to maintain safety as the minimum. The planner knows that the real minimum is zero, and uses safety to meet demand when replenishment is not possible. Planners also have ways to manipulate the plan by adding changes to safety on future dates, for example, for extraordinary demand, or to account for a plant shutdown.
The core logic of MRP is simple and constant and can be grasped readily. Yet, there are so many complex connections of products, parts, locations and parameters that even smart analysts are sometimes challenged to explain the result. If there is one small error in one link of the chain, it is amplified by those interdependencies with a bullwhip effect.
Planners and managers make better decisions when they clearly understand how each parameter and each type of plan manipulation works and will impact inventory levels. Despite the apparent simplicity of MRP, the combination of options makes this is a good opportunity for a demo. This is in no way intended as an actual MRP model and is purely for instructional value.
Logic
MRP logic considers three types of inputs; demand, inventory and parameters. To logically organize the project I break these out to four areas; demand, supply (inventory and lot sizing parameters), safety stock and safety time. For the purpose of explaining net requirements, inventory is considered here with demand.
Net Requirements
Net requirements means that the initial inventory balance at the end of today is yesterday’s ending balance minus today’s demand, including safety. If there was enough inventory left yesterday to cover all of today’s demand, then there is no net requirement for today. If not, the difference between inventory and demand is the net requirement.
Net requirement tells us whether or not to place a replenishment order. There should be a supply order on each day that has a net requirement. If the order exactly covers the net requirement quantity, there will be an order on each day that has demand. It is most often the case, due to logistical constraints or economics, that there is a minimum order quantity or period that exceeds daily demand, resulting in a sawtooth replenishment pattern.
The concept of net requirement is a bit elusive because it does not appear in the plan, since the plan is always to replenish in time to prevent it. It is a hidden calculation. The net requirement date tells us when to order, and the net requirement quantity feeds the replenishment order calculation. As each planned order adds to inventory, the next net requirement date is determined, and so on.
Planning usually involves a firm period in which orders can only be changed by a planner. Within this zone, if replenishment is not in time, the inventory balance can go negative, revealing unintentional net requirements. But, not all negative inventory is bad. If the plan includes safety as part of the total demand, then negative inventory means it is below safety. The planner must judge how much safety remains, if any, to determine what interventions are needed. The demo will not have a firm zone; its purpose is only to show how the MRP calculations work, so there will never be negative inventory.
Safety Stock
Safety stock that is entered as a part parameter is static – effective now and always. Statistical models are used to determine the amount based on service level desired, demand and supply variability and other factors.
Static safety is subtracted from current inventory, creating in effect a false floor for the plan, like pretending you have less than you really do, or like a reserve gas tank. The plan always calculates supply to maintain inventory at or above safety.
Safety stock can be entered to take effect on future dates. This is usually done for two general reasons; abnormal demand such as promotions or seasonality, or to prepare for supply constraints. It is driven by strategy versus statistics, and is temporary. Once the abnormality is passed, the strategy is to return to normal statistical safety.
As a temporary strategy, what goes up must come down. In effect what it is doing is not adding demand to the plan but leveling it. It borrows demand from a constrained period to put it in an earlier, less constrained period. This looks like a fake demand in the early period and a fake supply in the later period, providing relief to the constraint. When the plan is recalculated, it moves the real supply plan to match the manipulated demand plan, resulting in inventory prebuild.
The mechanism used to effect future changes to safety is to introduce a plan element – a placeholder with a date, and a quantity that can be either negative or positive. In the demo the amounts entered are absolute targets, increasing from static safety to the peak, then returning to static safety or other desired level. The program determines the plus or minus incremental amount to place in each period.
The demo is too simple to include constraints or firming, but does show the effect of mitigation measures. The user can pretend to balance a constraint as they experiment, and see how production and inventory would be impacted. A particularly revealing lesson can be learned by changing safety stock and supply parameters when there is no demand. The point often learned the hard way is that safety stock generates supply orders even with no actual demand.
Safety Time
Safety time is like safety stock in most regards. It can be either a statistically derived static parameter, or a profile of strategic ups and downs on future dates. What makes it different is the mechanism and the effect. Where safety stock is pretending you have less than you do, safety time is pretending you need it sooner than you do. It advances demand by the set days. This creates a false floor not as a constant quantity, but as a constant days of coverage. As demand goes up or down, the safety amount follows it.
When a safety days increase or decrease is set to take effect on a future date, demand is offset by the new number of days beginning on that date. This has a ramping effect as some dates have double demand and some have none.
A key advantage of safety time over safety stock is that if there is no demand, there is no safety to generate supply. For this reason it is the preferred strategy at end of product lifecycle, or when demand is very uncertain.
Lot Sizing
Once all demands are considered and netted to inventory, it’s time to create a supply order. Lot sizing is about following logistical rules. Logistics is about respecting the reality of how material is procured, packaged, shipped, handled, stored and used.
Consider how you buy food. When you eat out or take out, you purchase to your immediate need, called lot-for-lot procurement. When you go grocery shopping, you probably buy more than just your next meal. Some things you buy in large packages so that you don’t need to buy it again soon, like toilet paper or cereal. Others you buy in the minimum package, perhaps because of shelf life, like eggs. Each item causes you some mental math, even subconsciously. You know the more you buy, the cheaper the unit cost, but the higher the probability of waste. Meanwhile, you are also doing net requirements calculations based on what’s still in your refrigerator or pantry at home.
The demo includes three lot sizing parameters; cycle, minimum quantity and incremental quantity. Cycle refers to the replenishment frequency. If the nearest store is an hour away, cycle is likely a larger factor in your shopping behavior than if it’s around the corner. Minimum is the least you can buy. If it’s a low-use item, the minimum drives frequency because the package lasts longer than one shopping cycle. Order quantity must always be at least the minimum, and must be a multiple of the incremental quantity.
Formulas
A demo is likely to be viewed and analyzed by some very smart and curious people. For this reason, I pay special attention to making the formulas easy to read and understand, with structured references and named input cells. Formulas can thus be read without reference to the sheet. In fact I can turn off sheet headings for a clean screen.
There are a total of fourteen columns that I divide into two sets. The main set of columns lines up with the user input section, leaving clean lines and space for the chart. These are the columns of primary interest. Additional columns are used to break down the long calculation chain into intelligible steps, to retrieve the safety inputs, and feed outputs to the chart. Being of secondary interest, they are hidden when the chart is displayed, and revealed by toggle when the chart is hidden.
Formula notes
Formulas are self-documented with N(“text”), beginning with a brief description and a shortcut tip to either call edit mode or expand the formula bar, and including a plain English version of the formula.
All arguments are named, either as other table columns (with brackets), or as range names (without brackets). OFFSET is used to retrieve (with rows argument) or accumulate (with height argument) other table rows with reference to the current row.
Complex formulas are written with line breaks and indents for readability. The most complex, Supply, has 15 parentheses, 9 lines and 4 indent levels.
Formulas are written to avoid most errors, which might occur if the user enters unexpected values. For example, Supply uses IncQty as a denominator. To avoid a "#DIV/0!" error, I use MAX(IncQty,1).
Following are the formulas separated into primary and secondary following the sheet structure. Actual calculations are bolded.
Supply formula detail
Supply is the most complex formula, and being the essence of MRP deserves special attention. To understand a complex formula, it helps to dissect it from the inside out. Here, circled numbers in the formula show the start and end of meaningful segments, which are listed and explained below.
⑦IF(②MIN(①IF([@Day]=1, N(Stock), OFFSET([@[Cycle Inv]],-1,0)) ①+ [@[Total Dem]],0) ②, ⑥⑤ROUNDUP( ④MAX( ③-SUM(OFFSET([@[Total Dem]],0,0,MAX(Cycle,1))) ③- ①IF([@Day]=1,N(Stock),OFFSET([@[Cycle Inv]],-1,0)) ①, MinQty) ④/ MAX(IncQty,1),0) ⑤* MAX(IncQty,1) ⑥, 0) ⑦
① Beginning Inventory = IF([@Day]=1,N(Stock),OFFSET([@[Cycle Inv]],-1,0)); Stock if day 1, else previous day’s cycle inventory. Cycle inventory is the portion incurred due to supply periodicity and lot size quantities, differentiated from safety inventory, which is built into the demand.
② Net Requirements = MIN(①+[@[Total Dem]],0); if ① Beginning Inventory covers today’s total demand, then ② Net Requirements = 0, FALSE; else, ② Net Requirements < 0, TRUE; used as a Boolean trigger for ⑥ Supply Order Quantity in ⑦ Supply.
③ Net Cycle Demand = -SUM(OFFSET([@[Total Dem]],0,0,MAX (Cycle,1)))-①; cycle demand - ① Beginning Inventory; cycle demand is the negative sum (positive value) of the next Cycle rows of total demand, starting from today. (In rare conditions, today’s demand requires a supply order, but cycle demand amount is not enough to cover it. This can occur when a safety stock decrease (positive demand) exceeds actual independent (negative) demand within the cycle. In this case, negative cycle inventory can briefly occur).
④ Initial Quantity = MAX(③, MinQty); maximum of either ③ Net Cycle Demand, or the minimum order quantity.
⑤ Number of Increments = ROUNDUP(④/MAX(IncQty,1),0); ④ Initial Quantity divided by the incremental order quantity, rounded up to the next whole number (0 digits); incremental order quantity is bottomed at 1 to prevent error.
⑥ Supply Order Quantity = ⑤ * MAX(IncQty,1); ⑤ Number of Increments * the incremental order quantity is the final answer.
⑦ Supply = IF(②, ⑥, 0); if ② Net Requirements exist, then calculate ⑥ Supply Order Quantity, else 0, no supply needed.
Scenarios
A demo project is intended to be a user-interactive visual demonstration of a tool or process. It should simulate real life, but with data and features simplified and controlled for the educational purpose. A great tool for this is scenarios. With scenarios you can package data ahead of time with exemplary values. A scenario simply saves cell values, allowing them to be restored anytime. Thus you can offer scenarios while still allowing the user to modify the data for experimentation.
Scenario Manager is not difficult to learn, but it’s obscure enough to be ignored or forgotten by most users, and even when familiar it has several drawbacks; one, it covers the screen; two, the chart does not recalculate while it’s open; three, it only shows one scenario at a time. I can use code to show and manage scenarios, and to display information about all four scenario areas at once. With a few simple controls and macros, I can provide an engaging interface for self-learning.
The elements of a scenario are the name and the range of cells that are stored in it, which can be up to 32 cells. Values can be specified at creation, but normally the cells already contain the values you want to save, so once the range is defined it will by default save the current values. Comment is optional, and may be important depending on the number of scenarios and of changing cells in each. Like it or not, Excel automatically adds the user’s name and date of creation or modification to the comment. Handling this is another objective and benefit of coding.
Controls
When you have scenarios to offer, it’s nice and elegant to do so in such a way that users can scan them easily while focusing on the data, especially with a chart or Conditional Formatting to show the change. This is a good case for a spin button control. A spin button is a dual-function button that increments and decrements the output value using min, max and step properties, like a scroll bar without the bar. Often this can be done with no code; the linked cell can be hidden or visible, and used to derive outputs. Here I’ll do everything through code.
Since I will be programming the buttons, they don’t really need to be spinners, any shape will do. But a spinner is intuitive for this case of scrolling up and down through a series of data sets, and already has the up and down arrows. I will use the SpinUp and SpinDown events of each button to drive actions, so the button properties such as linked cell, min and max are irrelevant. I can also make them circular; SpinUp will not stop at the last, but go back to the first, and SpinDown will go from the first to the last.
Each of the four areas will also have a set of three buttons, to create, change and delete scenarios. With the spinners counted as two for up and down, that’s five buttons per area, total twenty, which is a lot. I don’t want to confuse the user, nor to write lengthy instructions. Design is important; I’ll use small controls with intuitive, single-character faces, repeated per area and nested for a functional and uncluttered appearance. A single help cell will give usage tips.
Technically there are two additional controls. One is the demand method checkbox, which sets random or fixed demand. The other is the chart checkbox to manage the screen. As a principle, a demo should fit a single screen, but here it is hard to do so without overlapping the table and the chart, which is inelegant. To address this, the chart checkbox toggles between two views; one with the chart, and just the primary columns showing, the other with the chart hidden and all the columns showing. The checkbox activates a macro to show or hide the chart, and is linked to a cell which feeds the Conditional Formatting of the secondary column set.
Demo Sheet with Chart hidden
VBA Coding
As the coding brainstorm begins to brew, I start with the list of controls. As noted, I have twenty controls to program (plus the chart toggle), and they must each have their own macro. I look for commonality to avoid duplication. Each control macro can call another macro to perform the common actions. To do this I will need to pass and catch arguments between the macros. To support this, I’ll build a strong naming convention for all the dependent variables; the scenario name and abbreviation, the scenario range, and the cells to hold the scenario number, count and comment.
Scenario name roots and abbreviations are Demand (Dem); Supply (Sup); Safety Stock (SST); Safety Time (STI). Scenario names are constructed as the name root followed by a space and a number. A maximum number of scenarios is set as a constant in the module, initially at 99. Abbreviations are then used as arguments linking each control with the main action subroutines, and are used in all the related range names.
Four range names are defined for each area using the abbreviation for a total of sixteen. For example, ScenCellsDem is the range of cells contained in each demand scenario.
The purpose of the Data sheet is to hold the parameter values generated in the code and displayed on the Demo sheet. The reason for this is to protect the Demo sheet, and enable tab navigation between all the input cells. If the parameters are stored directly on the Demo sheet, then their cells would need to be unlocked, and the tab cycle would include them. One option is to leave them locked and unprotect the sheet at run time. I’ve chosen the option to move the coded cells to another sheet which is unprotected and hidden, and reference their names in locked cells in the Demo sheet.
Stage 1 macros are assigned to a control. If the control is unique and the code simple enough, this can be the only stage needed, as with the chart toggle which sets the property “.Visible = Not .Visible”.
If the control is one of a class, stage 1 calls a stage 2 subroutine by passing the abbreviation argument identifying the class member. For example, there are four controls in the “create” class, each one passes the abbreviated area name to the main routine to create the scenario; CreateDemScenario() calls ScenCreate("Dem"), etc.
Stage 2 macros are the class-level subroutines that perform actions using the argument passed from stage 1. There are four stage 2 macros, one for each action; ScenCreate, ScenChange, ScenDelete, ScenSpin.
Create, Change and Delete are serious actions, and thus start with a message like “here’s what I’m going to do, continue?” The message box has OK and Cancel buttons with Cancel as the default, so to proceed with the action requires conscious choice. This is a kind way of informing the user and protecting them from inadvertent actions.
Here’s an example of the general steps in the ScenCreate Sub, followed by the actual code:
increment the next scenario number and stop if over max
prompt to continue with creation
prompt to enter scenario description
add the new scenario
set the scenario number, count and description cells
'*************************************** '* Create Scenario Routine * '* called by 4 buttons * '*************************************** Sub ScenCreate(ScenAbbr As String) Dim NewCount As Integer Dim ScenRoot, ScenDesc As String On Error GoTo oops: ScenRoot = Root(ScenAbbr) With Sheets("Demo") 'increment the next scenario number and stop if over max NewCount = Sheets("Data").Range("ScensCnt" & ScenAbbr) + 1 If NewCount > MaxScens Then MsgBox "Maximum " & MaxScens & " scenarios reached.", , _ "Add Scenario" Exit Sub Else 'prompt to continue with creation If MsgBox("Add new " & UCase(ScenRoot) & _ " scenario with current values?", vbQuestion + vbOKCancel, _ "Add Scenario") = vbCancel Then Exit Sub End If With .Range("Random") If .Value Then .Value = 1 Else .Value = 0 End With 'prompt to enter scenario description ScenDesc = InputBox("Enter a short description " & _ "for the new scenario" & vbCr & "(max 30 characters):", _ "Add Scenario") 'add the new scenario .Scenarios.Add _ Name:=ScenRoot & " " & NewCount, _ ChangingCells:=.Range("ScenCells" & ScenAbbr), _ Comment:=ScenDesc & ";" 'set the scenario number, count and description cells With Sheets("Data") .Range("ScenThis" & ScenAbbr) = NewCount .Range("ScensCnt" & ScenAbbr) = NewCount .Range("ScenDesc" & ScenAbbr) = ScenDesc End With End With Exit Sub oops: MsgBox ("Error " & Err & " " & Error) End Sub
Stage 3 macros are specialized subroutines that further modularize code segments pulled out of stage 1 or 2 code to simplify program logic. These may be reusable or not, sometimes used to manage complexity, like breaking down long formulas into stages. They can be Subs to process steps or Functions to return a result. They are opportunistic and not pre-designed.
Layout and Formatting
The purpose of a demo is to expose and illustrate a process by letting the user change inputs and immediately see the effect on outputs. Input cells, scenarios and controls enable the interaction, and are all neatly organized in a four-quadrant area. The table and chart supply the visual outputs. Good arrangement of these elements is key to a delightful and instructive user experience.
The user is overtly invited to interact with the inputs. First, all input cells and only input cells are white. Second, inputs are clearly and simply labeled. Third, controls are simple and intuitive. Finally, the output table and chart are attractive, enticing the user to see what will happen when they change a number or click a button.
Other design tips include use of shades of one color versus multiple colors, section headings in merged and rotated cells, visual symmetry, and use of custom number format to display text with numbers. For example, future days are format "Day "0, and the safety time values are format 0" days".
The Chart
The chart shows five of the table columns in Combo style, all as lines except supply which is a column, or vertical bar style. This illustrates the effect of supply receipts which instantaneously increment inventory once per cycle, while independent demand is continuous, gradually decrementing inventory. Of keen interest are the three inventory lines showing cycle, safety and total. As they scroll and change the scenarios, experienced planners and managers may be surprised and intrigued by the magnitude of inventory relative to demand. Figure 5: Custom Chart Type
If a picture is worth a thousand words, a demo is worth a thousand pictures. It’s more than an introduction for basic training, it’s also a great offering for management. Even executives can appreciate relevant elucidation. As such this is an example of how elegant design with purpose can give the designer positive exposure.
FREE DOWNLOAD FOR ONE MONTH ONLY
Download MRP Demo FREE from December 29, 2021 to January 31, 2022 with coupon code FREE MRP DEMO at checkout.
Comments