excel - Waterfalls in Excel

Question

UPDATE I tried to upload pictures, but I got the message that I couldn't upload pictures but I got links instead. Let me know if this works...

Thanks for taking a second to look through this. This is my first post so I am trying to make sure that I make the question (and hopefully, the solution) relevant to more than just me.

I work in a repair/manufacturing planning position and have to identify the bottlenecks in my various processes. I have one type of asset that has multiple flavors. For example, if I worked on Honda Accords, I would have to distinguish between a 1986 Accord LX from a 1996 Accord EX-L.

Based on the induction schedule of my assets and how long they take in different parts of my processes, these bottlenecks can be manifest in several different ways: a lack of facility space, a lack of a certain type of manpower, or a lack of support equipment just to name a few. Now, I have dates for incoming assets for the next two years, and I have the durations for the different parts of my processes that each type of asset will. More simply: an Accord from 1986 takes 3 weeks to inspect (Gate 1), 4 weeks to repair (Gate 2) and 5 weeks to put back together and test (Gate 3); whereas an Accord from 1996 takes 3 weeks to inspect(Gate 1) 2 weeks to repair (Gate 2) and 3 weeks to put back together and test (Gate 3).

I have already been able to take the incoming dates an lay them out on our fiscal year (vs. the calendar year) using =weeknum(). I then used an =IF() to place an "x" on the corresponding week.

What I am looking to do is place a series of colored cells (corresponding to the weeks of the gates) that starts in the cell with the "x" and extends to the cells to the right the number of cells corresponding to the number of weeks. For example, if I have a 1986 Accord start in week 3, I would like the 3 cells to the right to be blue, followed by 4 green cells, and finally 5 yellow cells. I am essentially trying to graphically represent the times that the assets will be within my facilities and where my bottlenecks are. If I only have 5 locations for Gate 1 and my waterfall shows I have times where I need 8 spaces, I need to let the boss know.

I didn't see a way to upload the files I am working from, so if someone will let me know, I'll post up what I have so far...

Again, thank you for looking. The data as I initially receive it and format it: Initial Information The finished waterfall product I am looking to make: Final Waterfall

标签： excel