Tim Lovelock

While I do quite a bit of data analysis and manipulation in Excel using pivot tables, logical functions, lookup functions, and statistical functions, I haven’t spent much time learning Reference Functions in Excel.

Given that my day job is centered around production schedules and materials requirements planning (MRP), I wanted to try to create a simple MRP model that utilized reference functions. The new function for me is the OFFSET function. After using it I can certainly recommend it, although if anyone knows a better way to accomplish the same thing in a more elegant fashion please leave a comment below and I’ll give it a shot in the next iteration. No matter how much I already know, I’m always trying to improve my Excel skills.

I am assuming you have an idea how MRP works in materials management or you wouldn’t have read this far, but if you need a tutorial Wikipedia is a decent place to start. In simplest terms, it is the recipe for building a product; it calculates how many components it takes to build a quantity of end-items, and when the components need to be ordered.

Download the Single-Level MRP Model (243)