Pages
Stay In Touch
More
Thanks for dropping by! Please connect with me on LinkedIn as well.
© 2007-2010 Tim Lovelock
Inspired by Ted Murphy’s post about the TIM meeting calculator, I whipped up an Excel version to meet my own needs. How could I not? After all, it’s name after me!
Anyway, I cranked this out because I lead a daily meeting with at least 2 dozen daily attendees ranging from hourly support employees to multiple vice-presidents, and I use a projector and Excel as my tools so a spreadsheet will be the most effective communication tool in my environment. I’m the Materials guy that gets assigned to the programs with “issues”, so during the early stages the meeting time can be extensive (read: expensive). As control is re-established and meeting time’s are cut down, the productive time of the team is freed to go back to generating profit.
The other reason I created the spreadsheet is because Money is Money and I didn’t want to pay for the TIM. But if you don’t get to keep Excel running during the meeting where everyone can see the result, by all means head over to Ted.me and follow the link to buy a TIM of your own.
In the meantime, feel free to download the Time is Money Excel Calculator: Single-Level MRP Model (243)
Note: The file contains 2 macros to enter the start and end times conveniently.
Down to 2 Excel worksheet tabs, from well over a dozen. Goal is to get to a single sheet, but the volume of data, screen real estate, and the fact that it is reviewed in a conference room rather than printed all factor it. I’ll get there, but I like the progress so far.
I have a tab with a dynamic chart to review planned orders by assy (over 50 assemblies). Another tab to review upcoming total production volumes by type/workcenter. Need to add demand management and WIP data, then scale it all down to a single sheet.
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)
Microsoft Excel : Create conditional drop-down lists.
I haven’t tried the INDIRECT function in Excel 2003, which is what I’m stuck using at work. Will try on my laptop tomorrow.
Just proved the wisdom of data tracking and trend analysis again.
Variation isn’t something that can be avoided, however, but as a professional I need to be diligent about controlling those factors under my control… Primarily, I use pivot tables in MSExcel in this process. I track demands over time, supply exceptions over time, excess/obsolete over time… well, you notice “over time” is the critical factor.
- 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.
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:
Continue reading »
Powered by FireStats