The right ingredients (and the right charts)...

Improvement Insights Blog

The right ingredients (and the right charts)…

One of the benefits to being a Freelance Quality Improvement Expert is that I set my own hours. Because of this, I often take time between appointments in the middle of the day to run errands if I happen to be in the area. Earlier this week, I was doing exactly that and picking up a few things at a grocery store. I had just put a few cans of evaporated milk in my basket when I heard someone call my name. Looking up, I saw a man with a facemask and a pricing gun walking down the aisle towards me. Though the mask obscured part of his face, his tall frame and lanky gait told me it was none other than an old friend of mine, Darren.

“I thought that was you behind your mask,” he began, stopping a safe distance away from me. “Been quite a while. How have you been?”

“Good!” I replied. “Just running a few errands between appointments. I saw a recipe for dulce de leche that I wanted to try, so I needed to get these,” I said, pointing at the cans of evaporated milk.

Darren’s brow furrowed. “Are you sure the recipe called for evaporated milk and not sweetened condensed milk?”

“You know, now that you mention it, I think you’re right,” I said, replacing the cans on the shelf and picking two cans of condensed milk instead.

“It’s a common mistake,” offered Darren. “I see it all the time. It’s similar, but the recipe won’t turn out right if you use the wrong one.”

“Then I’m glad we ran into each other,” I responded.

“As am I,” he continued. “I was going to call and make an appointment with you anyway. Are you free for a video call this evening about 5?”


That evening, with the cans of sweetened condensed milk boiling gently in a crock pot, I got a notification that Darren had joined our video call.

“Hey Darren.”

“Hey. Glad you were available. I’ve come across something that doesn’t look right, and I wanted to check with you on this,” Darren began. “Can I share my screen?”

“Be my guest.” His screen appeared and I saw an Excel sheet with figures on it.

“These are weekly hours for our restocking crews at each of our four stores. I ran an analysis and everything seems to check out… but what I’m seeing doesn’t match with what I’m seeing in payroll.”

“Is this like the time in college when you stayed up all night trying to prove the textbook wrong because the answer didn’t make sense to you?” I asked.

“Hey, who ended up being right in that case?” he responded.

“You were,” I laughed. “The professor even had you present your work to the Dean. I remember. So show me what you’re doing.”

“Okay, so I have weekly hours for each store’s restocking crew in each column. I highlight the data, and since I have multiple columns, that would be an XbarR chart. I select the chart here…”

01 XbarR highlight and choose 560x348.jpg

“…and this is what I get. Everything looks in control, but something doesn’t match up with my payroll numbers,” he concluded, showing me his completed XbarR chart.

02 XbarR 560x469.jpg

“Aha! You know what, I think you and I may have both made the same error in the same day,” I began.

“How so?” Darren asked.

“Earlier at the store, I was going to buy the wrong type of canned milk for my recipe and you corrected me. It’s the same thing here. I think you may be using the wrong chart for your data. Like you said earlier, ‘It’s similar, but the recipe won’t turn out right if you use the wrong one.'”

“But they’re multiple columns of data; isn’t that either an XbarR or XbarS chart?” he asked.

“Well, sure, but what that does is gives you an average across all stores. There are certainly similarities between the stores, but there are differences as well: different employees, different layouts, different store size. The average is lumping all those together, which can sometimes be useful, but sometimes not. What happens if we take the data and look at each store individually, but on the same page?”

“How do we do that?” Darren inquired.

“We could do it several ways. We could put all the data into one column and leave a blank row between the stores, then run an XmR chart, but that becomes difficult if you want to add data next week. What would be better off in the long run is to create a dashboard that displays all stores on one page. Then you can have all the data together in one place, and still look more deeply into each store if you need to. Can I drive the mouse on your screen for a bit?”

“Sure,” he offered.

I clicked the QI Macros, then the Control Chart Templates, Control Chart Dashboards, and then finally selected the XmR Dashboard.

03 XmR Dashboard 560x319.jpg

When the dashboard opened in a new spreadsheet, I copied his data and pasted the values into the Data Sheet tab.

04 XmR Dashboard Data sheet 560x720.jpg

“Now from here, we’ll choose which dashboard we want to create. For now, let’s just select the XmR Average R charts.” I clicked that tab at the bottom.

“Now we’ll create the dashboard for the first time. This is the only time you’ll click the Create XmR Dashboard button here. So here we go,” I said, clicking it.

05 Create XmR Dashboard 560x609.jpg

His screen flashed and flickered for a moment, and QI Macros created a new tab at the bottom called XmR Average R Dashboard. I selected it and showed him what the software had created.

06 Xmr Dashboard 560x621.jpg

“So this is what’s been created. You can see all four stores individually in one place. Once the dashboard is created, you can go back in and modify these if you’d like, making each chart larger or smaller or changing the title, axis labels or anything else about its appearance. It’ll remember the way you leave it.”

“Nice!” exclaimed Darren.

“Exactly. Now let me show you a couple more things that may interest you.”

I clicked back to the Data Sheet tab at the bottom. “Each week, you don’t have to re-create this; you can just open the file and add the new week’s data to the bottom. When you do, you’ll click one of these buttons to update all the charts. Refresh Charts simply adds the new data to the charts, but Refresh Charts With Stability also applies whatever Control Chart rules you’ve selected, and shows the in-control and out-of-control points on each chart. Let’s click that button and apply the stability rules. You’ll see how that affects the charts on your dashboard.”

07 XmR Refresh with Stability 560x495.jpg

After clicking, I went back to his dashboard tab. The charts now looked like this.

08 XmR with Stability 560x633.jpg

“Wow! Look at that!” exclaimed Darren.

“Yep. If I had to guess, I’d say that something at Store B changed somewhere around 3 to 4 months ago that’s slowing down your restocking crew and making them work longer.”

“You know, we had a couple of personnel changes and policy shifts there about that time. One of those could be causing this,” Darren offered.

“That could be,” I said. “Or it could be all of the changes together… or something else entirely. Best not to jump to conclusions until you learn a little more. Let’s look more in depth at Store B.”

I clicked the Store B tab at the bottom.

09 Store B tab 560x430.jpg

“Here’s where we can dive in a little more in depth into what’s happening with each individual column of data on the Data Sheet; in this case, each individual store. If you look at the R chart, it appears that the hours have become more consistent and less erratic from week to week. That may be due to Store B’s policy shifts. Generally, reducing the variation is a good thing, but not when the average shifts in the direction you don’t want,” I offered.

“Yep. Well, this matches more accurately with what I’ve been seeing from Store B’s payroll. This coaching session was absolutely worth the investment,” Darren said.

“Glad to have helped,” I replied. “Have a great evening, and let me know if you need a followup. That way I can let you know how the dulce de leche recipe turned out.”

“That’s a deal,” he offered. “Enjoy your just desserts!”

If you’d like to learn more about the QI Macros Dashboard tools, click THIS LINK to view Jay demonstrating the tool, and click HERE to learn more.

If you believe that I just made up that whole part about boiling sweetened condensed milk in a crock pot to make Dulce de Leche, you can FIND THE RECIPE HERE.

This entry was posted by Jay Arthur in QI Macros Monthly Newsletters. Bookmark the permalink.