“Good day everyone. Today, We will walk through this MRP II spreadsheet step by step.
We will start with the Bill of Materials sheet, then move to the Demand Data sheet, and after that
We will explain the formulas being used. We will try two example problems: one on cost
calculation, and another on a supplier delay scenario.”
(Action: click the Bill of Material sheet tab.)
“As you can see, this first sheet is called Bill of Materials.
This sheet contains a list of our snack products and their respective prices per batch.
In the first column, we see the Product Names, like Piattos, Cheese Ring, Nova, Vcut,
and Cracklings.
In the second column, we see the Price per Batch, which tells us how much each batch
costs.
This sheet is very important because it serves as a reference table. Whenever we enter demand in
another sheet, Excel uses the prices here to calculate the total cost automatically.
For example, Piattos costs ₱18 per batch. If later on we enter 5 batches in the Demand Data
sheet, Excel will multiply 5 × ₱18 and give ₱90. The formula behind it is simple: =Batch × Price.
This way, we don’t have to compute manually.”
(Action: click the Demand Data sheet tab.)
“Now let’s move to the second sheet called Demand Data.
This sheet records the weekly purchase demand for our products.
The first column shows Batch per Purchase, which is the number of batches ordered.
The second column shows Product Name.
From the third column onward, we see Week 1 up to Week 4. These represent the demand
recorded each week.
The sheet is dynamic, which means it updates automatically. Let’s see an example.
If we enter 5 batches of Piattos in Week 1, and since each batch costs ₱18, Excel will
immediately show a total of ₱90.
If we enter 8 batches in Week 2, it will show ₱144.
If we enter 10 batches in Week 3, the result is ₱180.
And if we enter 12 batches in Week 4, the total becomes ₱216.
This allows us to track demand trends. For example, we can see that demand increases from
Week 1 to Week 4. Knowing this helps us forecast future demand and prepare enough stock
ahead of time.”
Formula Check (Action: click on a Total Cost cell)
“To see how it works, we can check the formula inside a total cost cell.
The formula is =IF($A3="","",VLOOKUP($A3,BillofMaterial!A:B,2,FALSE)*$C3), where the
batch comes from the Demand Data entry and the price comes from the Bill of Materials sheet.
This shows how Excel links the sheets together, making sure our totals are accurate and
automatic.”
Example Problem 1: Cost Calculation
“Let’s try an example. Suppose in Week 2, a store orders 6 batches of Nova.
Looking at the Bill of Materials, Nova costs ₱20 per batch.
So Excel multiplies 6 × ₱20 = ₱120.
This gives us the total cost instantly, without doing manual calculation.”
Example Problem 2: Supplier Delay (MRP Scenario)
“Now let’s test another scenario. Suppose in Week 3, we expect 10 batches of Piattos to arrive.
However, the supplier delays the delivery by two weeks.
If we still enter the demand in Week 3 but shift the actual supply to Week 5, the spreadsheet
shows a gap in Week 3 — meaning demand exists but supply is missing.
This example shows how supplier delays directly affect our planning. By simulating it here, we
can prepare backup plans, such as ordering earlier or keeping safety stock.”
“To summarize:
The Bill of Materials sheet gives us the product list and prices.
The Demand Data sheet records weekly purchases and demand trends.
The formulas connect these sheets so totals are automatic.
By entering data, we can calculate costs quickly, forecast demand, and even simulate
issues like supplier delays.
This makes the MRP II spreadsheet a very practical tool for planning, budgeting, and decision-
making.