Solved

How can I create a vendor capacity tracking graph/chart?

Posted on 2016-07-25
6
35 Views
Last Modified: 2016-08-04
I would like to implement a tool vendor capacity chart for my team. They like visual information vs. a bunch of data on a spreadsheet. We work with 5 - 7 tool vendors and I want to be able to assist the team by having them review the capacity chart prior to awarding them the business.  We work with smaller ma & pop shops so they will take jobs and say they can meet the lead time and they really can't. At least with this chart we would have an idea of their ability to meet the requested lead time based on what we've given them already. I know they have other tools with other suppliers in their shop so this chart isn't going to show all of their capacity, but at least we will see ours. We have some of the vendors that won't turn down the work and would rather take the job and deliver late than turn it down (it eats into our lead time). Which is why this chart would be very helpful when we are deciding who we want to award the final job to. This would at least give my team a snapshot of the jobs in their queue and determine if we should award the job to one vendor vs. the other. What I would like to be able to show my team is the following information in a chart form.
  • tool vendor - VENDOR NAME
  • customer name - CUSTOMER
  • customer description - TOOL DESCRIPTION
  • customer ORD # - ITEM NUMBER
  • current tool lead time  (ie., 4 weeks, 9 weeks, etc.) - CREATED DATE THROUGH REQUESTED DATE
  • classification (this lets me know if the tool is seal, o'ring, bladder, etc) - TOOL CLASSIFICATION

Since I want to see a lot of information I figured viewing the data by tool vendor would be the best way. On the current tool PO tab, this is what I came up with but it looks very sad, so I'm reaching out to the Experts. The data in the tab was test data to see what the chart would look like. The first tab (data) has data that I would be using.

I would like my team to take away something like this. Tool vendor X has 7 tools in the queue. And tool 1 was place on 6/25 and is due 8/1, tool 2 was placed 6/29 and is due 7/15,etc. Not sure if it's possible to show the number of weeks next to each job but that might not even be necessary if I can get the chart look the way I am visioning it.
Tool-Vendor-Capacity.xlsx
0
Comment
Question by:Marcia Morris
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:Shabbir Rao
Comment Utility
For this you will need a excel pro to develop this thing for you.
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
I don't have a clear picture yet of the deliverables you'd like to present, but I do think that including historical data on your vendors' production record would be helpful to compare to.  

For instance, if you have a vendor with five tools pending with a history of delivering a week ahead of the required date, and another vendor with only two tools in the queue, but delivers two weeks late on average, that might be beneficial.  Also, one could check the maximum capacity of each vendor and weigh against delivery.

I also work in order deliverables and wonder if your "Required Date" and "Promised Date" are similar.  In our case, the Required Date is the initial delivery date schedule when the order was entered; the "Promised Date" can change during the course of the order (for any various reasons).

Regards,
-Glenn
0
 

Author Comment

by:Marcia Morris
Comment Utility
Glenn/Shabbir.
I found this Project Template from Visualology.Net in Excel and I'm trying to see if I can use this to kind of get me closer to what I'm looking for visually. I can't figure out to update the 2011 dates to 2016 dates. Right now when I change the date in I9 it removes the bar because it's a 2016 date. Also, I am completely stumped on what OE (c8), MLE (d8), PE (e8) stand for, any thoughts?
Excel-ProjectPlan_Tool-Capacity.xlsx
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
Hi,

Sorry for the delay in my reply.  I had no problem changing the dates in the Milestones sheet, but the Gantt chart has a fixed date range.  You just have to change the date values to 2016 dates.  That may look confusing at first because it will display the internal date value, but if you just enter mm/dd/yyyy values, that will work. (for checks:  try 42613 for minimum and 42674 for maximum).  

I also had no issues changing 2011 dates on the WBS sheet to 2016 dates.  The Holidays table looks up-to-date and doesn't need changes.

The three abbreviations are PERT three-point estimates for each job:
OE = optimistic estimate
MLE = most-likely estimate
PE = pessimistic estimate

There is a formula in column F that calculates a weighted average task time based on the three values.  Here are a couple of online resources that will describe this in more detail:

https://en.wikipedia.org/wiki/Three-point_estimation

http://www.pmdocuments.com/2012/09/17/pert-three-point-estimation-technique/
0
 

Author Comment

by:Marcia Morris
Comment Utility
Glenn..THANK YOU SO MUCH for reviewing the date issue. Question...I used the values you provided but I have no clue what 42613 means in layman's terms and I couldn't find where to change the internal date value (see screenshot). I want to be able to extend the dates out. I was able to figure out how to the change increments from 7 to 10, etc.--YIPEE!

I'm going to try and figure how to use this to portray some of the information I had in my original question. Something is better than nothing at this point. Even if it will only show me what tools are being worked on at the vendor. Once I get the basics out of the way, I'm going to see if I can use those other columns to my benefit.
tooldate.png
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
The screenshot helps.

The numbers you're seeing for minimum and maximum are Excel's internal date values (days since January 1, 1900).  You can actually type a visual date string like "10/1/2016" in those boxes for your convenience and Excel will change them to the internal numbers automatically.

You could also just click the [Reset] button for each and the chart will scale automatically depending on the dates in the source table.

-Glenn
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now