Laundry Detergent Powder and Capsule Plant Financial Model

Description

This financial model covers a greenfield plant producing laundry detergent in two formats: conventional powder and water-soluble unit-dose capsules. The production flow is split into dedicated lines—powder blending, spray drying, and packaging, and a separate line for liquid capsule forming, filling, and sealing—with shared utilities, warehousing, and quality control. The model handles the interdependencies between lines, including energy balancing (steam, compressed air, chilled water) and waste treatment capacity.

A detailed bill of materials per SKU drives raw material costing, with recipe management for surfactants, builders, enzymes, fragrance, and, for capsules, the polyvinyl alcohol film. Packaging formats are fully configurable: bulk 25 kg bags, retail pouches or cartons for powder, and tubs or flexible bags for capsules. The production schedule models shift patterns, OEE, changeover downtime, and a phased ramp-up aligned with commissioning milestones.

The financial structure is designed for project finance, with senior debt, equity injections, and revolving working capital facilities. It provides a monthly three-statement model over a typical 10–15 year horizon, enabling robust investment analysis through sensitivity tables on key value drivers like raw material price oscillations, capacity utilization per line, and product mix shifts. All values shown are illustrative and indicate the order of magnitude of investment, not final figures.

Modeling specifics

• Dual-line production with interdependencies: shared utilities (steam, compressed air, water treatment) are allocated based on real consumption drivers, preventing cross-subsidization between powder and capsule profitability.
• Recipe-driven raw material costing: each SKU has a fully editable formulation with stoichiometric yields and line-specific wastage (spray drying losses for powder, filling and film waste for capsules), enabling precise margin tracking per product.
• Packaging mix flexibility: the model handles multiple packaging formats per SKU—bulk bags, retail cartons, pouches for powder, and water-soluble PVA tubs or bags for capsules—with changeover times and minimum run lengths that affect effective capacity.
• Phased production ramp-up: monthly capacity utilization follows commissioning and performance test milestones for each line, avoiding the common trap of assuming full output from day one.
• Energy intensity profiling: electricity and natural gas consumption are tied to production volume with time-of-use and demand charges factored in, reflecting the true utility cost structure of energy-intensive spray drying and capsule forming.
• Working capital granularity: raw material inventory accounts for supplier lead times and minimum order quantities per commodity (bulk chemicals, packaging films); finished goods safety stock is modeled with service-level targets, capturing the cash lock-up during ramp-up.
• Multiple revenue streams: the base model supports own-brand sales, private label manufacturing, and contract manufacturing, each with distinct pricing mechanisms (cost-plus, fixed price, indexed) and receivable terms.

What's included in the base version

• Assumptions dashboard for project timeline, capacity, product mix, raw material recipes, utility rates, and staffing plan.
• CAPEX schedule with phasing by construction month, asset class breakdown, and automated depreciation (straight-line and accelerated methods).
• Production schedule with dual-line capacity utilization, shift patterns, OEE factors, and maintenance downtime.
• Cost of goods sold model: direct materials from detailed recipes with yield/wastage, direct labor, variable overheads (energy, consumables), and fixed overheads (QC, maintenance).
• Integrated monthly financial statements (P&L, cash flow, balance sheet) for the project's full tenor (default 12 years).
• Debt and equity financing structure with flexible senior debt terms, debt service reserve account, and equity waterfall including IRR.
• Working capital module: raw material, WIP, and finished goods inventory, trade receivables and payables linked to production and payment terms.
• Key investment metrics: Project IRR, Equity IRR, Payback Period, Net Present Value, DSCR, and LLCR.
• Pre-built sensitivity analysis (data tables) for selling price, key raw material cost, capacity utilization, and discount rate.

Common modeling mistakes

• Assuming 100% OEE from start of operations — overstates first-year production volume by 20–30% and artificially reduces unit fixed cost.
• Ignoring raw material yield loss during spray drying (up to 5% for powder) and capsule filling (3–8% loss) — leads to COGS understatement and gross margin overstatement by 2–5 percentage points.
• Using a flat electricity rate without incorporating time-of-use and demand charges — underestimates annual energy cost by 10–15%.
• Omitting working capital build-up during the ramp-up phase — understates funding requirement by $0.5–1.5M and shortens payback period by 6–12 months.
• Applying identical variable overhead allocation to powder and capsule lines — misrepresents line profitability, often making capsules appear 15–25% more profitable than they are.
• Not modeling packaging changeover downtime and minimum run lengths — overstates effective capacity by 10–20% and understates the cost impact of short production runs.