Hero Group AI Labs - Ep 5 - Paint Department Analysis Machine

It's Tom, and I'm back here in the HERO group AI labs, and I'm working on another experiment. You know, I've never been a fan of math, but I am a bit of a data nerd. And back when I ran my marketing agency, one of my favorite things to do was to dive into a shop's marketing numbers to figure out what was actually working. And honestly, that curiosity didn't stop with marketing. Your shop's producing data everywhere you look, and that data can reveal things that you've never caught with your own eyes. You know, things like trends and inefficiencies and sometimes even mistakes that are hiding in plain sight. Today's AI experiment comes from a real world case. I was visiting a shop in Wisconsin that's part of a bigger mso, and the manager told me that they had a hunch that they were losing profit in the paint department, but they couldn't prove it. The numbers just weren't lining up with what they saw at other locations. So I asked him a simple question. Well, doesn't your paint scale track every mix? And he said, yes. So I told him, send me the report. And I built him a prompt, the same one that I'm about to share with you. And what we found was kind of eye opening. And I'll show you what we found. And with that, let's get into the experiment. You know, most shop owners tell me they watch the numbers closely. Sales hours, throughput, all the usual metrics. But there's one area almost no one's looking at, and it's one of the biggest blind spots in the entire collision industry. Talking about your equipment, you've invested hundreds of thousands into tools and machines that generate data every single day. Your scanners, your welders, your frame racks, and even your paint scale. But did you ever check that data? And even fewer shops have a simple way to analyze it. Until now. Today, I'm going to show you how I use the paint scale data to uncover a calibration issue inside a real shop. And then I'm going to build you a paint scale analysis tool that you can use to see exactly what's happening in your own mixroom. And clearly, quickly, and whenever you want. You can also use this with some tweaks to measure all the other equipment in your shop, which I will be doing in future videos. So let's get started. All right, so first, let's start with what I was sent. So I was sent this paint scale report, which I've got right here, and it looks something like this and this is an Excel. I also have the CSV file. But as you can see this is probably common typical of what you might see. This is the Color Net scale mix usage report that they had is what their scale system is. So you know, you can kind of look in here and see it's, it's tracking all the mixes and pouring errors and all that kind of stuff. So it's got you know, the text and you know, it's got all, it's got all the data in there. So, so that's, that's what we're going to be analyzing and I'm going to now walk you through the prompt. Okay, so here is the paint scale analysis report that we built. You're an AI paint scale analysis system for auto body collision repair shops. Please analyze the attached and you know, insert your report name when you upload it. Paint Mixing Report Examples colornet Scale Mix Usage report PPG Paint Manager report, you know, so on and so on. Your purpose Identify trends, inefficiencies, operator issues, waste formula problems, customer specific issues and potential scale calibration drift and produce a complete professional analysis report. If there's any other things that you want it to analyze, just add it here in this section. Executive Summary A short paragraph including total mixes, gallons, cost, waste percent error rate and key insight. Overall performance metrics. Provide a table showing total mixes, total ros, total gallons, standard versus actual cost cost difference total total waste cost, average cost per mix, overall error rate. We can also add to this estimate waste by total for the year. I want to add that to this. I want to see what that does. Mix status breakdown so we want a table with the percent and count for pouring errors, reformulation, reformulated mixes, aborted mixes and good mixes. Add one and two sentences explain what this means for shop owner. Error analysis error rate by formula Rank formulas by error rate. Include the mix count and cost impact. Error rate by mix size. Show precision issues especially in small mixes. Monthly error trend, month by month error rates and cost impact error factors. Table 1 to 10 Score Score Top formulas for operator error Equipment calibration Formula complexity Mixing process material variation Add short insight beneath each subsection so we can see what all that means. Cost analysis coding type and cost breakdown Paint clears primer solvent other and then the financial impact of the errors Extra cost, aborted cost, total waste cost Explain the financial impact in simple and direct language. Personnel Operator analysis this will be if you have multiple painters for each operator. Provide mix count, error rate, good mix rate, cost impact pouring tendencies over under struggles with Specific formulas or mix sizes and with actionable training opportunities by operators. So how can we help these guys out if we find issues? Customer analysis for each customer. Mix count, error rate, cost impact and any color, color pattern issues. Now this one is probably not going to relate too much to the typical auto buy shop. This was for a commercial truck shop. So they have, you know, fleet accounts and so they're doing the same types of vehicles that are the same color over and over again. So this may or may not have an impact. And if you want it, you can just delete it out of your prompt. Explain if customer specific factors may contribute to material waste. I will tell you also that this particular shop, they were waiting on a color, it was one of their fleet customers. And one thing that you could do with this if you do handle fleets, is to analyze the buying patterns of your paint. And you know how often you get certain vehicles in so that you could, you could be ahead of it. So they actually had a truck in there that they couldn't paint. They were waiting on paint. So you know, you might want to add some stuff in here if that's ever been an issue for you where you want to kind of get ahead of ordering for demand. You know, this commercial truck shop, they were doing some reconditioning so they need to have certain materials on hand, but they didn't have enough. Explain if customer specific factors may contribute to material waste. So are there any specific customers that are harder to paint? That would be an interesting thing to find. Scale calibration diagnostic. Identify patterns that indicate calibration drift, tiny consistent overpours, directional bias, high deviation on mixes. So what I want to do is I want to have the thing analyze itself. So have our tool analyze that tool, make sure that it's doing what it needs to do. Provide calibration suspicion score with an explanation like how accurate do we think the scale might be? Key findings in a bullet list. Recommendations break into priority expected outcomes. You know what we want to, what we want to work on. Once we find this data and then visual summary, this is where we can kind of see. So this is the ASCII chart. So it's, you know, this kind of stuff. But then I also have a Python one where AI will can take a look and this might be an issue. I'm not sure. We'll have to see. We may have to run this through something like a Claude, but we'll, we'll find that out in the experiment. So processing rules and then begin when the files uploaded. Okay, so we got Our prompt, I'm going to copy it and we're going to go Chat GPT and we're going to make this, we're going to run this report and see what we get. All right, so copy that and I'm going to go to Chat GPT. I'm going to start a new CHAT. Paint scale analysis. I just did that so I could name it. So here we go. Got our prompt and then I just need to upload the file. So I have it in two formats here. I'm not sure which one it's going to want. So I'm going to give it both. I'm going to give it the CSV and the Excel sheet. I have, attach the paint scale report and both a CSV. All right, let's see the magic. By the way, I'm using chat GPT 5.1, the brand new one that just came out. This is actually my first time using it, so we'll see what we get here. You're certainly welcome to use things like Claude. You can do use different chatgpts. You can use Gemini. You don't have to use chat GPT 5.1. I just use it on my experiments because that's the most common one. But we can actually even try this on, you know, try this with some other AI tools just to see if we get better results. So we can see here it's analyzing the stuff and it's writing the code. So looking at both the CSV and the Excel file, I don't remember when I did this, which one I used. I don't remember which way it came out, whether it came out as a CSV or an Excel file. But I saved it as both. So I suspect that it came out one way and needed to be converted to another. Okay, we've got our results here. So executive summary. So from June 1st to December 31st, so it's like six months analysis, there's 910 mixes across 276 repair orders. About 120.7 gallons of material. Actual material cost $76,313. Recorded waste extra and aborted $576.35. Waste as a percent of material costs less than 76%. Error rate non good mix events 93.3%. So 93% of the time their mixes come out wrong. Key insight Dollar waste was relatively low as a percentage of total material spend, but the system is logging extremely high number of pouring errors and reformulations, heavily concentrated in a handful of formula mixers, especially Rick Rusk. And fleet customers PTG and jr. This points to process and training opportunities more than calibration crisis overall performance metrics so here's all the gallons mixed the the the the you know the cost involved waste by quarter and then mixed status breakdown so 743 pouring errors, 101 reformulated paint mixes, 61 good mixes and 5 aborted mixes where they just dumped it and started over. What this means for the shop owner systems capturing almost every adjustment as an error which inflates the error rate. The actual dollar waste is small compared to the number of error events. If you're burning time and focus on corrections and reformulations especially on a few high volume formulas more than you're burning raw dollars. So the painters are able to save the mixes but they're spending a lot of time trying to get it reformulated to match error rate by formula. So these are the ones that are costing the most error rate by mix size. So which mix sizes are more problematic than others? So there's a 94% error rate on this size. 98% here, 93% here, so on and so on. All mixes show very high logged error rates. This supports the idea that the report is recording almost every correction as an event monthly error Trend Insight August and October Standout with a higher waste cost Error rate is consistently high across all months confirming this is a system behavior pattern not a one time issue. Error Factors Table Top 5 Problem Formulas they're concentrated to a few formulas and a few mixers. Not random so calibration risk is present. But secondary training SOP and formula specific handling should be tackled first. So it's possible that the scale needs calibration but suspects that training and standard operating procedures might fix a lot of this cost analysis Coding type by breakdown Financial impact of the errors not not a whole lot $576 so for every hundred dollars they spend a material losing about 76 cents due to recorded overpours and aborted mixes. So dollar wise that's pretty tight. But the time and the labor behind849 non good mix events where the hidden cost likely lives. So the time spent in the booth from the painters reformulating the paint is where the money is leaking. Where the profits leaking Personnel operator analysis so it analyzes the mixes the error rates by by painter what percent of the waste goes to each one. So Rick Rusk is the one that wastes the most paint pouring tendencies. He has the highest cost extra cost per MIT for per mix. So Rick Rusk again problematic there actionable training opportunities side by side Coaching with the top Waste formulas emphasize slowing down the first few toners and pre checking target weights before committing. Have Rick document a mini SOP for these formulas after coaching him to lock in the improvements. Tracy Halter focused on medium sized mixes where most of the waist sits. Reinforce double checking mix size selection Avoid upset sizing by habit Periodic review of daily paint usage versus RO count to make the cost more visible and then Justin Use Justin as a benchmark capture. See what he does differently and bake it into the SOPs and have him lead a short internal training on mixed discipline and how to avoid overpowers. So Justin's the best got the best eye for paint mixes so he should teach the rest of the guys. Customer Analysis which customers? Again, this might not be relevant to auto body shops, but this is which fleet accounts contribute the most to waste Scale calibration Diagnostics Signals were checked over pours across many toners. Extra costs exist, but it's small per mix and concentrated on a few operators not evenly across all all directional bias. The report shows extra cost, not under pours, so we can't fully evaluate under bias. D High deviation on small mixes less than 4 ounces have the lowest error rate and the lowest waste of dollars, which would make sense. It's the lowest amount of paint operator independent patterns Waste is heavily skewed on two mixers. Not evenly distributed mixers mean two pages painters increased variance over time. Error rate stays consistently high. Waste dollars peak in Q3 but are still modest. Calibration suspicion score 3 out of 10 if the scale were drifting badly, we'd expect problems across all operators and mix sizes and higher waste dollars. Instead, waste is low in dollars, high in event count, and heavily operator and formula specific. A routine calibration of verification is still smart, but the data points more to technique, SOP and behavior than a failing scale. So where they're bleeding money in the paint booth again is not the equipment, it's the it's the people. Key Findings Bullet List Waste is financially small. Most waste comes from small groups of formulas. Two painters account for almost all of the waste. A couple of fleet customers drive the most waste. There's no strong evidence of scale drift, so calibration might not be a problem. Medium sized mixes where the most dollars are so just a summary of what we had up there. Recommendations so high priority. Next 30 to 60 days we're going to build new SOPs. We're going to talk talk. We're going to sit down with the painters, these two in particular, and Do a little bit of coaching. We're going to do a quick calibration verification check. Why not have the scale calibrated while we're at it, run a few test weights and kind of see medium priorities. The next 60 to 120 days. Mix size and batch strategy review. So we're going to look at batch sizes to reduce consistent small corrections. We're going to avoid, just in case oversizing of mixes when not needed. Operator scorecard will build a monthly report showing mix count, error rate and extra cost per mix to each operator. So we can use that as a code moment. Customer specific color playbook. So for those two customers that have that specific color, we're going to come up with an SOP just to mix that paint that color. Long term strategy. The next 120 plus days we're going to close the loop on production and estimating. We're going to tie mixed data to our ROS and estimating so we can see material cost per RO and per labor hour. I did not have that data so if I had all the ros I could tie it back to cars, vehicles and we could see how that's impacting our profitability per ro. So I would like to do that and I may find some examples in the future to do that. Flag outlier jobs with high material per hour integrate into an AI driven command center, which is a kind of a plug for my system that I have that does all this AI analysis for you, which is called Vault Onic by the way. And if you're a commercial truck shop, it's a great fit for you. It does a lot of these things. It's a management system for body shops, commercial truck shops and dealership body shops. A lot of these tools are already built into the system. Continuous Training Culture Quarterly 10 to 15 minute refresher sessions on mixing best practices and we're going to review, document and improve improvements with the team as we kind of tighten up the paint department. Expected outcomes. We're going to reduce the error rate, we're going to get some cost reduction, we're going to get improved mixing accuracy and we hope to see some improvement in 30, 60 days. But with three to six months we should fully lock in these habits. So here's a visual summary of the data. I'm not a big fan of this style, which is why I have the Python. But ChatGPT doesn't natively run the Python, so I just asked it, could you render the Python charts? And so now we can kind of see it now if we Run this through Claude. It'll be more colorful. I can show you an example of the report here. I'll pull that up of the one that I did send to the, to this customer. But here's, you know, five of the basic charts. So we've got operator error rates, we've got system custom cost versus waste cost. So, you know, obviously not a ton of waste here. Error error mix by, by size and by formula and by, by quarter or month. But if you want other charts and graphs, you know, you can add to that. So anyway, so that's the paint scale report and hopefully you found that insightful. You know, it's. It's a lot that you can learn from your, from just one piece of equipment in the shop. There's a lot of equipment in the shop. A lot of it is producing reports. So, you know, just take this, you know, keep this in mind for, for your shop and your equipment. Take this prompt, modify it as needed. I will be spinning out different versions for different pieces of equipment in the shop so we can see what we can learn from like your frame machine or your torque wrenches. I don't know, we'll figure some stuff out. So anyway, that's it. That's a wrap for me here. Again, this is Tom, and I'm here in the HERO group AI Labs. If you've got any ideas for, if you, if you like this analysis and you've got ideas for other things in the shop, please send them to me. Tom herogroup, AI. I would love to tackle this as a project for you. And until next time, I'll see you. Bye, Sam.