The profit leaks analysis machine

Follow all instructions below exactly.

STEP 1 — CLEAN & STRUCTURE THE DATA

Take the uploaded P&L and:

• Identify:
  • Month
  • Total Sales
  • Total COGS
  • Gross Profit
  • Operating Expenses
  • Net Profit
  • All individual Chart of Accounts expense lines by month
• Create a month-by-month table in a structured dataframe.
• Standardize month names or convert to a date index.
• Confirm all numeric fields are floats.

If the P&L includes:

• Car count
• Average RO
• Labor hours Use these as supplemental metrics.

STEP 2 — CALCULATE MONTH-OVER-MONTH METRICS

A. Revenue Growth %

For each month:

Revenue Growth % = (Sales_this_month – Sales_last_month) / Sales_last_month

B. Expense Growth % (for every Chart of Accounts category)

Expense Growth % = (Exp_this_month – Exp_last_month) / Exp_last_month

C. Growth Delta (Leak Indicator)

Leak Delta = Expense Growth % – Revenue Growth %

Rank severity:

• Severe Leak: > 15% over revenue growth
• Moderate Leak: 5–15%
• Watch List: 0–5%

STEP 3 — EXPENSE-TO-SALES RATIO TRENDS

For every expense category:

Expense Ratio % = (Expense / Sales)

Flag categories where:

• Ratio is rising across 3 or more months
• Ratio rises faster than sales
• Ratio exceeds common industry norms (explain the norm if possible)

STEP 4 — GROSS PROFIT QUALITY ANALYSIS

Calculate and interpret:

• Gross Profit % trend
• GP per RO (if RO data exists)
• GP per labor hour (if labor data exists)
• Materials cost %
• Sublet cost %
• Parts margin % trend

Flag any declining profits or rising cost percentages.

STEP 5 — PAYROLL LEAK ANALYSIS

Evaluate:

• Payroll % of sales
• Overtime spikes
• Month-over-month increases not supported by revenue growth
• Underproduction if hours billed vs paid are provided

Flag any payroll inflation or staffing inefficiencies.

STEP 6 — MATERIALS / VENDOR LEAK ANALYSIS

Look for:

• Paint & materials cost outpacing sales
• Supplies trending upward without volume increase
• Sublet cost spikes
• Parts gross declining month over month

Explain likely causes.

STEP 7 — VISUALS (GENERATE WITH python_user_visible)

Create the following charts:

Month-over-Month Sales and Net Profit Trend

All charts must follow these rules:

• Use matplotlib
• One chart per figure
• No custom colors unless requested
• No seaborn

STEP 8 — PROFIT IMPACT CALCULATOR

For each leak, compute:

Monthly Loss = (Current expense – Target expense based on revenue growth)

Annualized Loss = Monthly Loss * 12

Also calculate:

Net Profit Recovery Potential = Sum of all recoverable leaks

STEP 9 — EXECUTIVE SUMMARY

Provide:

• Total number of leaks
• Top 3 largest leaks
• Total monthly loss
• Annualized loss
• Most urgent corrective action
• The single biggest opportunity

STEP 10 — DETAILED LEAK REPORT

For each leak category:

• Name of the expense line
• Severity (Severe / Moderate / Watch)
• Growth Delta %
• Expense Ratio trend
• Probable root cause
• What it’s costing the shop per month
• What it is costing annually
• What to do about it

Format clearly with headers.

STEP 11 — 90-DAY PROFIT RECOVERY PLAN

Break into:

Month 1 — Administrative & Fast Wins

• Fix billing, pricing, approvals
• Tune materials cost
• Vendor corrections

Month 2 — Gross Profit & Cycle Time

• Labor efficiency
• Touch time improvements
• Material usage discipline
• Sublet and parts margin fixes

Month 3 — Payroll & Scaling

• Staffing alignment
• Throughput optimization
• Overtime elimination
• Final margin tuning

STEP 12 — FINAL OUTPUT

Deliver:

Executive summary

Stop after producing the complete report.

END PROMPT