Build a working capital heatmap by plant and customer.
- User
Build a working capital heatmap by plant and customer.
Assistant
1. Understand the Goal: The user wants to build a "working capital heatmap by plant and customer." This is a data visualization task.
-
Identify Key Components:
- Metric: Working Capital (specifically, usually Accounts Receivable, Inventory, or Days Sales Outstanding/Payable Outstanding in this context, but broadly "Working Capital" implies the net or the components). Given the "Plant vs. Customer" dimension, the most relevant metric is likely Outstanding Receivables (AR) or Inventory held for that customer, or perhaps Days Sales Outstanding (DSO). Let's assume the user wants to see where capital is tied up. The most common proxy for this specific intersection (Plant/Customer) is Total Outstanding Balance or DSO. Let's provide a framework that allows for flexibility (e.g., total dollars tied up).
- Dimensions: Plant (rows), Customer (columns).
- Visualization: Heatmap (color-coded cells based on magnitude).
-
Determine the Tools: Since I cannot physically generate an interactive file or run code on the user's local machine without their data, I need to provide:
- A conceptual explanation of the calculation.
- A synthetic dataset (dummy data) to demonstrate.
- Python code (using pandas/seaborn/matplotlib) to generate the heatmap.
- Excel instructions (since many business users work in Excel).
-
Structure the Response:
- Phase 1: Data Requirements: What columns are needed?
- Phase 2: Metric Definition: How is "Working Capital" defined in this context? (AR + Inventory - AP is the standard formula, but usually, we look at specific components like "Days Sales Outstanding" or "Total AR Balance" when slicing by Plant/Customer). Let's stick to a dollar value metric (e.g., "Capital Tied Up") or a time metric (DSO). Dollar value is usually better for a heatmap of "magnitude." Let's define it as
Total Open Amount (AR) + WIP Inventory Value. - Phase 3: Python Solution: Code to generate data and plot.
- Phase 4: Excel/BI Solution: How to do it in a spreadsheet or Tableau/PowerBI.
-
Drafting the Content - Step-by-Step:
-
Concept: Working Capital . At the customer/plant level, AP doesn't usually apply (we pay suppliers, customers pay us). So, for a Customer/Plant matrix, we are looking at Accounts Receivable (money the customer owes) and potentially Finished Goods Inventory allocated to that customer/plant. Let's assume the metric is "Total Outstanding Balance" or "Days Sales Outstanding (DSO)". Self-Correction: A heatmap of DSO is great for identifying process issues. A heatmap of )** as it's visually more intuitive for a "heatmap" (hotspots of money).
-
Python Code Construction:
- Libraries: pandas, numpy, seaborn, matplotlib.
- Data Gen: Create a dataframe with
Plant,Customer,Total_AR,Total_Inventory. - Pivot: Create a matrix index=Plant, columns=Customer, values=Total_AR.
- Heatmap: Use
seaborn.heatmap.
-
Excel Construction:
- Pivot Table setup.
- Conditional Formatting.
-
-
Refining the "Working Capital" Definition for this context: Strictly speaking, Net Working Capital = Current Assets - Current Liabilities. However, in a "Plant vs Customer" analysis:
- AR (Accounts Receivable): Definitely.
- Inventory: If a plant holds stock for a specific customer (e.g., VMI - Vendor Managed Inventory), that's capital tied up.
- Unbilled Revenue: Sometimes relevant.
- Decision: I will define the metric as "Total Capital at Risk" (AR + Unbilled + Customer Inventory) to make it realistic.
-
Writing the Response (Iterative refinement):
- Introduction: Acknowledge the request.
- Data Prep: List the columns.
- Method 1: Python (The "Data Science" way).
- Write code to create dummy data.
- Calculate the metric.
- Pivot and Plot.
- Method 2: Excel (The "Business" way).
- Step-by-step instructions.
- Strategic Interpretation: What does a "hot" zone mean? (High AR for Plant X + Customer Y). What actions to take?
-
Code Check:
import pandas as pd,import seaborn as sns,import matplotlib.pyplot as plt.- Create dictionary -> DataFrame.
df.pivot_table(...).sns.heatmap(...).- Add labels and title. Looks good.
-
Formatting: Use code blocks, bold text for emphasis, and clear headings.
-
Final Polish: Ensure the tone is helpful and professional. (Self-correction: Make sure to mention that "Working Capital" is often interpreted as DSO or DIO in these heatmaps, but I will stick to Total Dollar Value for the main example as it's the most direct interpretation of "where is the money").
(Self-Correction during drafting): Wait, looking at Inventory by Customer is tricky unless inventory is allocated. Standard inventory is usually just by Plant. AR is by Customer. To combine them:
- Option A: Just AR (Customer) mapped to the Plant that shipped the goods.
- Option B: AR + Inventory (Total Working Capital Load).
- Let's stick to AR (Receivables) broken down by the Shipping Plant. This links the customer debt back to the operational unit. This is the standard "Who owes us money for goods shipped from where?" view.
Let's proceed with generating the response based on AR Balance (Money Tied Up) broken down by Plant and Customer. I will also add an optional "DSO" (efficiency) view because working capital is often about time.
Plan finalized.
- Define the Data Schema.
- Provide Python Code (Visual output).
- Provide Excel Steps.
- Explain the Business Insight. To build a Working Capital Heatmap by Plant and Customer, you need to visualize the intersection of two dimensions (Plants and Customers) measured against a working capital metric—usually Outstanding Receivables (AR), Inventory, or Days Sales Outstanding (DSO).
Since I cannot access your specific ERP data, I have provided a complete Python framework below. This script generates mock financial data and produces the heatmap visualization.
Phase 1: The Logic
In this context, "Working Capital" usually refers to Operating Working Capital specific to a transaction.
- Metric:
Total Capital at Risk= (Outstanding AR Value + Unbilled Inventory Value). - Goal: Identify specific Plant-Customer combinations where cash is trapped.
Phase 2: Python Implementation
You can run this code in a Jupyter Notebook or any Python environment.
import pandas as pd import numpy as np import seaborn as sns import matplotlib.pyplot as plt # --------------------------------------------------------- # 1. DATA GENERATION (Simulating ERP Data) # --------------------------------------------------------- # In real life, you would extract this from your SQL database/ERP. # We simulate 5 Plants and 20 Customers with random outstanding balances. np.random.seed(42) plants = ['Plant_NA_East', 'Plant_NA_West', 'Plant_EU_Germany', 'Plant_APAC_China', 'Plant_LATAM_Brazil'] customers = [f'Cust_{chr(65+i)}' for i in range(20)] # Cust_A to Cust_T data = [] for plant in plants: for cust in customers: # Create variability: Some plants have higher outstanding than others base_value = np.random.randint(10000, 500000) # Create specific hotspots (simulate problem areas) if plant == 'Plant_LATAM_Brazil' and cust in [' Cust_A', 'Cust_B']: base_value *= 3 # High Outstanding # Random chance of having zero balance if np.random.rand() > 0.85: base_value = 0 data.append({ 'Plant': plant, 'Customer': cust, 'Outstanding_AR': base_value, 'DSO': np.random.randint(30, 90) # Days Sales Outstanding }) df = pd.DataFrame(data) # --------------------------------------------------------- # 2. PRE-PROCESSING # --------------------------------------------------------- # Create a Pivot Table for the Heatmap # Index = Plant, Columns = Customer, Values = Outstanding_AR heatmap_data = df.pivot_table( index='Plant', columns='Customer', values='Outstanding_AR', aggfunc='sum', # Sum if multiple invoices exist fill_value=0 ) # Convert to Thousands (K) for better readability heatmap_data_k = heatmap_data / 1000 # --------------------------------------------------------- # 3. VISUALIZATION (The Heatmap) # --------------------------------------------------------- plt.figure(figsize=(16, 8)) # Create the heatmap # cmap="YlOrRd" means Yellow (Low) -> Orange -> Red (High) sns.heatmap( heatmap_data_k, annot=True, # Show the numbers fmt=".0f", # Format as integer cmap="YlOrRd", # Color scheme linewidths=.5, cbar_kws={'label': 'Outstanding Capital ($ Thousands)'} ) plt.title('Working Capital Heatmap: Outstanding AR by Plant & Customer', fontsize=16, fontweight='bold') plt.xlabel('Customer', fontsize=12) plt.ylabel('Plant', fontsize=12) plt.xticks(rotation=45) plt.tight_layout() plt.show()Phase 3: How to Build This in Excel
If you prefer using Excel (which is common for financial reporting), follow these steps:
- Prepare the Data:
- Extract a list of open AR invoices or Inventory holding.
- Columns required:
Plant_ID,Customer_Name,USD_Amount.
- Create a Pivot Table:
- Rows: Plant ID
- Columns: Customer Name
- Values: Sum of USD_Amount
- Apply Conditional Formatting:
- Select the numerical data inside the Pivot Table.
- Go to Home > Conditional Formatting > Color Scales.
- Select the Red-Yellow-Green scale.
- Result: Cells with high working capital will turn Red, signaling a "Heat" zone.
Phase 4: How to Interpret the Heatmap
Once the heatmap is generated, analyze it using these three lenses:
- Row Analysis (Plant Level):
- If an entire Row is red (e.g., Plant_LATAM_Brazil), the issue is operational at that plant. They might be shipping goods before credit checks, or the logistics arm is failing to collect documentation required for payment.
- Column Analysis (Customer Level):
- If an entire Column is red (e.g., Cust_A across all plants), the issue is Credit Risk. The customer is likely over-extended, and you should put a credit hold on their account globally.
- The "Hotspots" (Intersection):
- Look for single red cells. This indicates a localized relationship issue. Perhaps the customer refuses to pay that specific plant due to quality defects or delivery disputes.
Actionable Insight Example:
"The heatmap shows Plant_NA_East has a massive hotspot with Cust_D ($450k outstanding). Investigation reveals Cust_D is disputing a delivery fee. We need finance and operations to reconcile this specific invoice to release the capital."
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.