How to Use AI to Analyze Body Shop Finances – Hero Group AI Labs Ep. 2

Let's see a show of hands. How many of you shop owners got into the collision repair business because you love bookkeeping and accounting? Nobody. Don't worry. Me neither. And even if you've got QuickBooks or reports from your management system, do they really tell you how healthy your business is? Well, today we're fixing that. Luckily, I've developed an AI prompt that can analyze your numbers and tell you exactly where you stand. And today, I'm going to show you how it works. I'll show you how to upload your financials into AI and instantly see what's working, what's costing you, and whether you're actually making money. If that sounds like that may help you, then come join me in the labs. Hey, I'm Tom, and I'm back here in the HERO group AI Labs with yet another experiment. I don't know about you, but when I had my agency, my least favorite thing in the world was to pay taxes and deal with my accountants. Now, I'm not a financial whiz by any means, and half the time it was all I could do just to keep up with what they were telling me. Now, most of you are probably using QuickBooks. Maybe you pull reports out of your management system. But let's be honest. Do any of the spreadsheets ever really tell you anything about your business? Do they actually help? Do you see how your business is actually doing? Does it let you drill down visually to understand what's working and what's not? Well, today we're going to fix that. We're going to build a financial analysis machine, one that you can use anytime, simply by uploading your spreadsheets. This tool doesn't just crunch your numbers. It'll explain what those numbers mean for your business. By the end, you'll have a system that can analyze profitability and growth trends, break down your labor costs and material performance, audit your overhead costs, spot some red flags before they turn into problems, and benchmark your results against industry standards. And in the notes below, I'll share the exact prompts that I use so you can build the same thing for your shop. Here's what your new AI powered diagnostic machine will do. It'll check for profitability and growth overview. It'll do a labor and materials deep dive. We'll do an expense and overhead control. We'll do a variance on a red flag scan. And we'll do a benchmark and action summary. So in the end, you're going to get two views side by side. You'll get the financial analyst View the precise data driven and visual. And then I'm going to have the AI do it in a shop owner view to kind of give you some plain talk and some operational and actionable insights. You'll know exactly where the money's going, what's improving and what needs attention without staring at other confusing spreadsheets. Alright, so let's get to the experiment. So the first thing you're going to want to do is obviously go to your QuickBooks. You're going to sign in, so you're going to go into QuickBooks and you want to go in and pull a report out. Now you can do as long as far back as you want to look, but we're going to go into reports, standard reports, And we want profit and loss by month. And we just want to pick a time frame so you can go back a couple years if you want, however far back you want. The more data the better. But two, three years is pretty good start. So let's go January 1st to today. All right, so now I've got my profit loss by month. All right, so now the next step after you've got your downloads is to start to prompt AI with the information. So I'm going to come in here to my new project. I'm going to create one called Financial Body Shop. Analysis. So I created a project so that I can upload these spreadsheets and my prompts so that I can come back to them later, three months, six months, whenever I want to review the data again. Okay, so I just told it that I'm going to upload the P and L and then I'm going to hit it with a bunch of prompts and then I want a final prompt. I've already pre written all these out here, so I'm going to upload this P and L information that I have. And let it vectorize it for a minute. All right, now it's got all the information that it needs, so I'm going to hit it with the first prompt. The first prompt is profitability and growth. So profitability and Growth overview. It'll start by reading your P and L breaking out revenue, your cogs, your gross profit, expenses and net income. Then it'll show you how those numbers trend over time. You'll even benchmark you against top performing shops. Like go through it with you here you're a collision industry financial analyst reviewing a shop's monthly P and L statement tasks. Calculate, summarize, review cogs, gross profit, operating expenses, show year over year and month over month trends, identify Top three strongest and weakest months for gross margin benchmark against these are the benchmarks here then restate findings in other words as a shop owner consultant would simple plain language and then here's the output format. So let's grab prompt one and I also included a prompt for the visuals that I want. So we're going to come over here, we're going to add our prompt and then we're going to let it think for a while now now we got our results that we want. Perfect. Now we have full clean financial results. Here's your shop's P and L analysis. So it's gone through and it's sales fluctuate strongly month to month. Both target on average but volatile and it gross profit Operating expenses running hot Overhead likely oversized by current sales volume so it's finding some some information in here. Top three gross gross margin months December, January and August. Bottom three are July, May and November Trend does revenue drift high Highly inconsistent Likely tied to DRP volume Well this shop is not a so it is tied to work volume. Gross margin variance swings from point negative 0.4 to 64% indicating cost control or estimate compliance issues Expense leverage OPEX ratios surged above 80% weaker months so it costs a lot of money to run the company in certain months. Net margin only 3 of 13 months positive positive with sharp losses tied to expense spikes. Now I can tell you in this data it's really not a fair assessment because the owner is repaying back a very recent major shop edition and overhaul and so those expenses are in here offsetting any of his making his expenses look higher than they really are for the shop. Three strengths Exceptional gross margin recovery Demonstrated a capacity to scale sales above 400k a month August performance 59% Gross margin 22% Net shows Ideal cost Sales structure is achievable Weaknesses Overhead inflation Expenses rising faster than revenue Extreme margin volatility Cogs control is inconsistent. So that's an area we're going to drill down to later in this cash drag from losses mid year undermine liquidity those again I think are expenses related to the build out opportunities Normalize production workflow to stabilize gross margins Reforecast staffing and fixed costs Audit parts labor paint containment we're going to do that here. So now here it is from the shop owner's perspective kind of the layman's terms that have built into the prompting. You've proven that a shop can make solid Money. You hit 64% gross in December, 22% net in August but your expenses and workload are out of sync. Throughout the middle of the year when the work volume dipped, your payroll, rent and overhead stayed the same, eating up profit. As the cars came back in. Your costs didn't adjust fast enough so margins whiplashed. What's really happening? You're estimating and production discipline drives profit when it's tight. When jobs stack up, oversight slips and costs well. So the shop gets busy, it gets messy. Staffing and scheduling might be sized for a higher car count than the shop is consistently producing, so he may be a little bit lopsided on his overhead. Administrative or vendor costs are creeping, possibly DRP discounting. That is not the case. That's a non DRP shop. Parts markups or sublet management inefficiency actions to take Watch for labor efficiency parts gross profit if gross profit falls below 50%, dig into the cogs detail that month. Adjust payroll, rentals, non production spend when sales dip below 300k and fixed expense structure. So hopefully that's helpful. Let's grab the next let's move on to the next the second prompt. And put that in here. And while that's running, I'll tell you what the second prompt is going to do. So we're going to go to a deep dive into labor and materials. So it looks like payroll and paint materials cost is a percentage of sales. So it'll tell you if tech pay is rising faster than production or if materials cost or outpace sales. And it'll explain what might be happening. This will give you a real actionable advice. Tighten, estimating, review utilization or audit your finish capture from the shop owner's perspective, what's driving the change? Tech pay versus Workload labor is tracking close to last year's proportion, which means you're likely staffed okay for January's car count, but you're operating with tight margins for error if volume stays soft. Material swing the big drop in materials percent could mean better mix, more labor, heavy work or you're not capturing all refinished material items. January is just one data point. We need February to March to confirm whether it's a real improvement or one off due to job mix pricing recommendations Tighten estimating and capture audit 10 January estimates line by line for materials capture P pages blend sealer, tint corrosion protection, seam sealer, so on and so on versus the actual jobs. Review labor utilization track flag versus paid hours weekly efficiency is greater than 100 to 110% for steady week. Suggest scheduling or task batching, paint materials billing audit Compare paint scale pulls versus build materials Monthly I will be doing a paint scale report. I will be doing that as a separate experiment in a future video. I have what I need for that one. Investigate variances three to four points of sales to isolate leakage unfillable tint supplies hazardous waste masking materials. Now I'm going to move on to prompt number three and expense structure and overhead control. So here the system is going to break down fixed costs like rent, marketing and admin. It highlights where costs are climbing the fastest and what that means operational maybe your utility spike because because both, you know booth filter maintenance or H Vac inefficiency. That'll point out what to what it recommends what to do next. You're paying roughly the same kinds of overhead, but because sales dipped admin costs now eat a much bigger slice of each dollar. Rent and utilities stayed reasonable as a percent of sales. Marketing is small and steady. The big bite is general admin office software professional fees miscellaneous admin which ballooned to roughly 70% of sales in January. Translation your sales slow but the office side didn't scale down. That's where the money's going. This might ring true with your shop because it's very hard to these days to run the shop without a lot of front office overhead. But you can see where front office overhead really eats into your profits. Actions Vendor and software audit List all admin subscriptions service contracts professional FEES target a 15 to 25% reduction in the next 60 days renegotiate consolidate tools Pause non critical tools it's very easy to subscription yourself to death space and staffing utilization if the average monthly revenue tracks Recalibrate admin staffing and hours Cross train and batch admin tasks to lower idle time marketing alignment Keep a baseline spend tied to car count example 2 to 3% of sales but redirect in high ROI channels the channels that produce booked estimates within 1421 days says we can drill down into the sub line so let's see what we find. Yes, please drill down. Let's see what's in the finer print here as they say. Now we've got our breakdowns stripped out the numbers pulled every total line matching gday keywords. You'll see the full interactive table in the viewer Key takeaways High level the shop owner's perspective what's happening on the office side Admin software Professional services insurance didn't scale down with sales. A few big categories are eating most of the cash each month, which honestly that's a hard thing to do. If sales drop, it's hard to know what to pull back. But if you run an analysis like this, you'll know what you might want to start looking at next time Sales Drop why it Matters Even in small price hikes or unused seats on software compound fast when car count is light. That's why your overhead percent jumped where to look first the Pareto shows a few of the categories that matter. Start there for the biggest wins, actions practical vendor renegotiations and seat audit for the next 30 days. Pull invoices for professional fees, insurance and software. Cut unused user modules, move annual to monthly if needed and solicit competing quotes on Insurance. Targeted 10 to 20% savings on bundle map the top three admin tasks by Time claims, email status calls, supplement packaging batch low value work and shift status of updates to automated text and email are possible to reduce hours. By the way, I have a tool that does that. It's called Vault onic. It will help you to streamline communications with your customers because the AI writes the messaging for you. Policy guardrails cap miscellaneous office per month require pre approval for new tools bundle telecom and IT where possible track a simple KPI GNA is a percentage of sales weekly with the trigger at a greater than or equal to 40 short term then push towards greater than equal to 30 sales normalized. If you want I can put out a one page cut plan rank in the top GNA lines. I'm not going to do that here, but you could do that with this tool. All right, I'm going to move on now to the fourth prompt Variances and Red Flags so the Financial Analyst view I published an interactive table of variance flags of revenue gross and you can see see here's the graphs here. Perspective what the pattern says May through July gross margin dipped Likely parts delays and underbuild materials plus some overtime to push cars out November through December office overhead ran hot fees software one offs so even with decent sales, opex ate the wins. When car count softens and office spend stays the same, profit disappears quickly. Watch next time Insights Monitor Supplement backlog weekly if supplements sit five business days, expect a gross profit drift. Assign dedicated follow up slot to close them. Manage tech scheduling and overtime track flag versus paid hours if efficiency is greater than 100% for two weeks rebalance the work mix materials capture checklist for every refinish job Confirm blend sealer tank corrosion make sure everything's billed. Aim to keep materials variants within 2 to 3%. 2 to 3 points normal. By the way, there's a great tool out there that'll do this Help you with your materials billing. It's Eagle mms. If you haven't heard it, take a look at them. All right, if you want I can export the flag months and cause one page controller brief. I'm not going to do that, but you have the option if you use this tool. All right, now finally I'm going to give it one last thing. Benchmark action summary and then we'll do the master prompt. So finally this now will roll everything up into one master summary to show how your shop compares to industry targets, what's out of range, what actions to take next. And it'll even give you an action plan. Who owns it, when to start, how success is measured. So let's take a look at what it's determined here. So it's giving me all the metrics, it's giving me the actual shop percent, the benchmark percent and if I'm at above or below target and now I've got a gap analysis and trend direction. I have high impact action recommendations what I need to kind of focus on. And so I also have the shop owner perspective. So I'm right on target for gross profit. But the office and overhead side is eating all the gains. Here's what that means. Day to day, every month 61 cents of every dollar goes to rent, admin and non production costs. Target is 30 cents. If you bring overhead down by even 4 points that's roughly $8,000 a month recoverable profit. The biggest opportunities are to trim admin and vendor costs, tighten tech productivity and capture every refinished materials line. Again Eagle MMS for that rent and fixed costs are fine short term but should be watched as you scale back up. And so here's the action plan. Who to do it, when to start it, what what to expect as a success metric. Now I have one last thing I want to run the system through and that's a master prompt that I created so that it gives you kind of just a full complete overview rather than, you know, going on. I wanted to to work on each piece individually but now I'm just going to have it go through do one final kind of deliverable here. So grabbing that prompt here, I'm going to throw that in. Shouldn't take too long because it has all this data. Okay, got my results here which we've already seen but I wanted to show you what the final results going to be. So executive summary. Overhead's primary drag. Gross margins are generally within range. Net margin average is negative in 2023. Gave me three actions here to work on. Then it overviewed my profitability growth overview, gave me my financial analyst view, showed me my best months, benchmarked me against the industry, showed me what my strengths were, what my weaknesses are, where my opportunities to improve are. And then we have the shop owner perspective wrapping it up. So again, labor and materials deep dive summary, expense and structure and overhead control summary, variance and red flag scan summary, benchmark and action summary. And then kind of a closing note here. So I'm Tom again here from the HERO Group, AI Labs. Thanks for watching and I'll see you in the next experiment.