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 (77)

Related posts

• There’s a reason fundamental skills are called “Fundamental” (1)
• The best laid plans of mice & men: Putting the challenge into perspective (0)
• Taking Responsibility for Materials Management on a Mature Program or Product (2)
• I can see a ton of uses in my day job for this: #Excel conditional drop-down lists (0)
• Excel functions I use daily (0)