Liquid Detergent, Gel and Fabric Conditioner Plant Financial Model

Description

This financial model is built for a manufacturing plant that produces multiple SKUs of liquid detergents, gel detergents, and fabric conditioners. It captures the full production cycle: raw material receiving, bulk liquid storage, batch mixing and blending, intermediate tank storage, and filling into various container formats (bottles, pouches, drums). The model accommodates separate production lines for different product families, allowing each to have its own batch size, cycle time, and changeover losses. The user can define up to X product families each with distinct formulations, filling speeds, and packaging configurations.

The model’s logic is driven by a master production schedule that translates monthly sales forecasts into batch campaigns on each line. It automatically calculates the number of batches per product, considering vessel capacities, minimum utilization thresholds, and clean-in-place (CIP) downtime. Raw material requirements are calculated from the recipe database, taking into account specific gravities for liquid ingredients, while packaging material consumption is linked to the number of filled units per shift. Utilities — electricity, steam, water, compressed air — are modeled as semi-variable costs that scale with production volume and batch count, not just with total output, which is essential for a multi-product plant where the batch schedule drastically affects resource consumption.

Investment costs are structured into major equipment (mixing vessels, storage silos, filling and capping machines, conveyors, labeling, and wrapping), auxiliary systems (CIP, water treatment, boilers), building and site works, and pre-operating expenses. The model shows the order of magnitude of the total capital required — typically a mid-sized project — without claiming to predict final contractor quotes. Working capital is sized specifically to the chemical sector: raw material supply cycles (bulk surfactants, imported additives), finished goods shelf-life, and seasonal buildup ahead of demand peaks. A multi-tier distribution framework allows modeling sales through direct retail, distributors, and private-label channels, each with its own pricing, credit terms, and commission structure. The user can test various capacity utilization scenarios and expansion phasing to see the impact on unit economics and cash flows.

Modeling specifics

• Batch scheduling engine with product-specific cycle times and CIP losses — the model schedules campaigns based on demand, vessel availability, and minimum run lengths. This prevents over-optimistic utilization rates that arise from simple capacity-volume calculations.
• Raw material recipe decomposition by specific gravity, not just weight — liquid surfactant and water-based ingredients are converted from weight percentages to actual liters and kilograms in inventory, accounting for density variations. This avoids discrepancies in stock levels and procurement timing.
• Multi-format filling line modeling — each filling line can handle several bottle sizes with distinct speeds and changeover durations. The model calculates effective OEE (Overall Equipment Effectiveness) for each line, factoring in format changeovers, breakdowns, and operator breaks, which typically reduce nominal capacity by 10–15%.
• Utilities consumption linked to batch count and volume — steam, air, and electricity are allocated not only per unit of output but also per cleaning cycle and per idle period. In a multi-product plant, shorter runs and frequent CIPs can increase energy use per liter by 15–20% compared to long campaigns.
• Shelf-life tracking for in-process and finished goods — liquid detergents and conditioners have shelf-life constraints that drive inventory write-offs and production batch size lower bounds. The model flags excess stock aging beyond defined limits and calculates the cost of potential obsolescence.
• Multi-tier distribution and trade spend modeling — separate sales channels (modern trade, traditional trade, institutional, private label) carry different discounts, rebates, credit terms, and logistic costs. The model aggregates contribution margin by channel, highlighting cross-subsidies.
• Wastewater and environmental compliance costing — the model includes an effluent treatment charge per cubic meter of wastewater generated from CIP and floor washing. This cost is often underestimated and can add a significant amount per liter to the product cost if not properly scaled with batch frequency.

What's included in the base version

• Configurator sheet: scenario assumptions, product mix, production schedule, raw material library
• CAPEX module: equipment list, building, installation, pre-operating costs, depreciation schedule
• Production plan: batch calculation, filling line utilization, CIP schedule, OEE parameters
• Raw materials & packaging: BOM per product family, price escalation, minimum order quantities, inventory
• Personnel plan: production shifts, maintenance, quality lab, supervision
• OPEX: utilities, maintenance, lab consumables, G&A, selling & distribution
• Financing: equity, debt, lease, grace period, interest-only phase
• Financial statements: monthly P&L, cash flow, balance sheet for up to 10 years
• Investment metrics: NPV, IRR, payback, unit cost breakdown, break-even volume
• Sensitivity: tornado chart on key drivers, one-way and two-way data tables

Common modeling mistakes

• Ignoring CIP downtime and batch changeover losses — assumes continuous operation at nameplate capacity, overestimating real output by 15–25%.
• Using simple weight-based recipes without density corrections — raw material inventory is misstated, leading to 2–4% error in procurement cost projections.
• Neglecting packaging material yield losses — bottle rejects, cap misplacements, and film wrap waste are omitted, understating packaging cost by 3–7%.
• Treating utilities as purely variable per liter — fixed energy & water requirements per batch cycle are overlooked, leading to understatement of total utility cost by 10–20% when campaign sizes shrink.
• Omitting shelf-life write-offs for slow-moving SKUs — inventory losses are not modeled, artificially inflating gross margin by 0.5–1.5 percentage points.
• Assuming uniform distribution margins — applying a single discount percentage across all channels ignores trade spend structure, overestimating net revenue by 5–10%.