How to generate graph using 3 variables - (Problem solving on production line)

**tisuyelo429** · 06-09-2012, 12:36 AM

Hello,

I found this forum while googling for more information about excel functions. The contributors in this forum seem to be very helpful so I thought I would join to see if anyone could assist me.

To give a little background info:

I am interning at a company who just introduced a new burger production line three months ago. There is very little data collected and analyzed on the process. My job is to increase efficiency of the production line. After spending a week observing and doing time studies, I have determined that the burger patty bagging station is often held up by patties that are stuck together, caused in the forming stage. The patties are injected into a mold (5 in a row) and then compressed by a conveyor to the desired thickness. The tolerance weight per patty is 151-154 grams. What they have been producing is 156-158 grams. This increase in diameter makes patties overlap just enough to become one solid piece after going through a freezer. The "twin patties" get stuck in the machine that stacks them and knocks over other patties that are stacked. Because of this, the workers have to do rework 25% of the time. As you can see, this is very inefficient.

Now, on to analyzing the information. There are three (possibly four) variables at play here. First, the temperature of the meat. Second, the PSI amount used to inject the meat into the molds, and third, the weight of the patty produced (grams). The fourth variable is that there are 5 different type of blends, each requiring a different temperature to perform well. I don't plan to compare blend to blend, just wanted to throw that out there as well.

QA keeps a running hourly record of temperature of patties formed and weight. Machine operators keep a record of every new batch they put in the machine (about every 2-3 hours), detailing the temperature of the meat as well as PSI used at that time.

What I have observed is:

At startup, machine operators run patties, QA checks it, weight is too high so they must lower the pressure to form smaller patties. Patties still weigh too much so they must lower it again. Before reaching the desired weight, the patties do not fully form (are misshapen) requiring rework (thrown back into machine). Because of this inconsistency they prefer to run heavier patties, throwing away money every day.

What I would like to do is chart the previous and current data, try to find some type of trend showing what has been known to work (which doesn't happen very often) and find a way to use the data to improve the process. The temperature is more than likely the constant variable however is very sensitive to how the burgers will form. If the temperature is lower, the meat is harder, requiring higher PSI to push it out into the molds.

I am aware of secondary vertical axis charts but am not sure if this is the correct choice to display the data effectively to find a trend. What I would like to be able to create is a graph that allows the machine operators to find the temperature and then what the correlating PSI is required in order to form the correct size patty.

Does anyone have an example that would be helpful, advice about finding the relationship and trend between the variables, and suggestions for the best way to display the information?

I apologize for the lengthy post !! Tried to be thorough without giving unnecessary information.

**npamcpp** · 06-09-2012, 01:19 AM

Why don't you post a data sample? Temp, PSI, weight, etc and also whether or not these parameters produced the desired patty.

**tisuyelo429** · 06-09-2012, 01:32 AM

That would be helpful.... unfortunately I was not able to bring any of the data home today. I will definitely post a data sample as soon as I can on Monday.

I have been thinking, and wonder if I can attempt to display the data similar to a sizing chart.

Example: http://www.goldtoebrands.com/aboutus/Size%20Chart.jpg

Instead of the S,M,L, show grams.

**MrShorty** · 06-10-2012, 11:02 AM

A sizing chart like that might work, but it seems fairly difficult to create.

My first thought to your question was to create a contour plot. Specifics of how to create a contour plot depend greatly on your specific data set. The idea behind a contour plot is plot two of the variables (on a scatter plot) while holding the third variable constant. Reading your post, it would appear that you should have many weight vs. temperature points for several different set pressures, so holding pressure constant might make the most sense here. The most basic layout in the spreadsheet is to build a table where temperature is in the left column of the table, with pressure at the top going across. Then fill in the weights in the middle of the table according to temperature and pressure. Then select the table, invoke the insert chart command to bring up the chart wizard. work through the options and finish. You should have a chart with several series showing weight vs. temperature. From here see if you can see a pattern in the data or not.