Pages
Stay In Touch
More
Thanks for dropping by! Please connect with me on LinkedIn as well.
© 2007-2010 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)
I know I said I would “live-blog” the process I go through when taking responsibility for materials management on a program, but I took a detour that will wind up being incredibly valuable. Someday.
Among my career experiences I was fortunate to work in marketing for a period of time when a new VP was hired to bring organizational leadership to the business development process. The first thing he did was hunker down in his office with several members of his staff, dissecting the business development status of the company. After a couple weeks he emerged from his office to give a presentation to the staff, explaining that the first thing he does when he takes on new responsibilities is to put the challenge into perspective.
As I mentioned, this is a mature program with significant challenges. Taking my cue from this former manager, I pulled my usual post-MRP-run reports: Backlog, Planned Orders, Open Orders, On-Hand Inventory. I normally lay the data out in Excel and do the math: how many units have to be built, how many units are on-hand, how many units are being built in the open orders, and how many units remain to be built. Simply put, the way production scheduling and MRP work is:
Continue reading »
In it’s entry on Safety Stock, Wikipedia states that some of the more common reasons for safety stock include:
# Supplier may deliver their product late or not at all
# The warehouse may be on strike
# A number of items at the warehouse may be of poor quality and replacements are still on order
# A competitor may be sold out on a product, which is increasing the demand for your products
# Random demand (in reality, random events occur)
# Machinery Breakdown
# Unexpected increase in demand
There are many reasons and methods for calculating safety stock,
Continue reading »
Powered by FireStats