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

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

Tags: , , , , , , , ,

Related posts

Excel functions I use daily

  • VLOOKUP
  • IF
  • ISERROR
  • CONCATENATE
  • YEAR
  • MONTH
  • WEEKNUM

My favorite function mashup is to combine IF, ISERROR, and VLOOKUP to return either a value from another table, or 0 (or blank, if you prefer). Here’s how to do it:

=IF(ISERROR(VLOOKUP([Std vlookup formula])),0,(VLOOKUP([Std vlookup formula])))

Here’s the logic: If the Vlookup returns an error (i.e., the value of the referenced cell does not exist in the lookup range), then return a value of Zero, else return the value found in the lookup range.

Why? It gets rid of those nasty “#N/A” errors.

Tags: , , , , , , , ,

Related posts

Tagged with:
 

I’ve already given an overview of the process I go through when doing material planning for a new product. This was based on successfully launching multiple new programs simultaneously, and I absolutely loved being involved at the early stage of a program or product. Being there in the beginning, a master scheduler or production planner has the opportunity to influence the way bills of material are configured, demand is loaded, and planning factors are set.

In contrast to this is planning for a mature product or program. I am taking over a mature program with a family of very complex assemblies on Monday, and this weekend will be spent preparing. Except for when I’ll be watching the Florida-Georgia game. I do have my priorities, after all.

I plan to “live-blog” the process as much as I can without giving away any proprietary information. To set the groundwork, the program is very important to my company’s current and future business and is generally considered a cash-cow. It is not without challenges however; inside lead-time demands, material shortages, and the sub-optimal impacts these have on inventory, operations, and profitability.

Step one in this effort: (more…)

Tags: , , , , , , , , , ,

Related posts