Universal Household Chemical Plant Financial Model

Description

A flexible, recipe-driven financial model for a plant producing liquid and powdered household chemicals — detergents, cleaners, degreasers, shampoos, and auto chemicals. The model supports batch mixing with multiple base formulas, additive dosing, and inline blending, allowing simulation of any product portfolio from commodity mass-market goods to niche private-label products.

The production layout assumes a medium-scale facility with raw material tank farms, mixing vessels (from 1 to 10 m³), intermediate storage, and a packaging hall covering PET, HDPE, and glass bottle filling, as well as powder dosing and sachet lines. The model explicitly accounts for CIP (clean-in-place) cycles, product changeovers, and sanitation downtime — parameters that fundamentally shape realistic OEE and true capacity.

Logistics and inventory are built around real chemical constraints: shelf-life tracking per raw material and finished SKU, storage class separation (acids, alkalis, solvents, oxidizers), and minimum order quantities for contract manufacturing and bulk intermediates. The model includes both make-to-stock and make-to-order logic, with dynamic safety stock targets driven by batch sizes and supplier lead times.

Utility infrastructure is modeled in detail: steam generation, compressed air, chilled water, and wastewater pre‑treatment, each with its own consumption drivers, maintenance overhead, and CAPEX phasing. The model separates direct utilities (assigned to production) from plant-level common utilities, preventing cross-subsidization and ensuring accurate product cost allocation.

Regulatory and quality aspects are embedded: in-process QC hold times, finished product quarantine and release, and compliance with REACH-like registration cost blocks for formulations. This allows the user to stress-test the impact of tightening environmental norms or raw material bans on margins without rebuilding the model.

Modeling specifics

• Multi-product, multi-format recipe engine: one model runs liquid detergents, powders, gels, and aerosols simultaneously, with automatic bill-of-material explosion per SKU.
• Batch scheduling with CIP and color/scent changeover matrices: capacity is not a single number but depends on campaign sequence; the model calculates true attainable output, not theoretical nameplate.
• Tank farm and raw material storage modeled with segregation rules and minimum/maximum inventory levels, preventing unrealistic co-storage of incompatible chemicals.
• Shelf-life mapping across 100+ RM and FG items: the write-off logic reduces phantom inventory, corrects working capital, and prevents overstated asset turnover.
• Packaging hall simulation with multiple line speeds, format change times, and shift patterns, producing per-equipment OEE and line-specific throughput.
• Hourly utility demand profiles linked to production schedules (steam for heating, CIP, reactor jacket) — OPEX forecasts that remain valid under load-following behavior.
• Waste and emissions calculator: sludge from neutralization, VOC releases, wash-down water — these are cost and compliance items, not afterthoughts.
• Integrated QA/QC hold block: quarantine delay, batch rejection rates, and rework loops, all feeding back into COGS and capacity.

What's included in the base version

• Revenue dashboard by product category, SKU, and sales channel (own brand, contract manufacturing, bulk)
• Recipe database with BOM, routing, and batch cycle-time calculator
• Production scheduling engine with changeover matrices, CIP logic, and shift calendar
• Raw material warehouse with safety stock, re-order logic, and FIFO costing
• Finished goods warehouse with shelf-life tracking and slow-moving provision
• Packaging lines module: speed, efficiency, format change times, labor, and maintenance
• Utility plant block: steam, compressed air, chilled water, wastewater, and power loads
• Direct and indirect OPEX: consumables, lab costs, PPE, pest control, plant overheads
• CAPEX schedule: civil works, equipment, MEP, commissioning, and initial spares, with depreciation splits
• Staff plan covering shifts, operators, maintenance, lab, warehouse, and admin, with auto-cascading costs
• Tax, VAT, and working capital model (receivables, payables, raw and finished goods inventories)
• Three-statement model (P&L, balance sheet, cash flow) on monthly granularity for up to 15 years

Common modeling mistakes

• Ignoring CIP and product changeover time — effective capacity is overestimated by 15–25%, making the investment case look achievable when it is not.
• Treating all products as having identical batch durations — COGS per unit becomes distorted by 10–20%, leading to wrong margin hierarchy and misallocated sales incentives.
• Omitting raw material and finished goods shelf-life constraints — working capital is understated by 8–18% and phantom stock inflates the balance sheet.
• Consolidating all utilities into a single overhead percentage — OPEX is typically underestimated by 6–12% under load-following behavior, especially for steam and waste treatment.
• Applying a flat yield loss to all products — certain formulas (e.g., thickeners, enzymes) have structurally lower yields, causing RM cost underestimation of 3–7% on those SKUs.
• Ignoring QA quarantine hold and rework delays — throughput is overestimated by 4–8% and cash conversion cycle lengthens, affecting liquidity projections.