Welded Steel Pipe Plant Financial Model

Description

The model covers a full-cycle welded pipe manufacturing facility producing straight-seam ERW or spiral SAW pipes from hot-rolled coil. All major production stages — slitting, forming, welding, sizing, straightening, end-facing, hydrostatic testing, and coating — are captured in the operational logic. The plant is designed as a multi-line operation, allowing simultaneous runs of different diameters and wall thicknesses, which directly affects throughput and the interplay of capacity constraints.

Unlike generic manufacturing templates, this model builds a detailed product matrix by steel grade, diameter, and schedule. Each SKU carries its own coil consumption factor with a cascading yield chain (slit edge trim, mill scrap, cut-off, test rejects, rework) and specific variable energy consumption (welding power, drives, induction heating). Throughput per hour is defined per line and adjusts for product changeover times, giving a realistic achievable annual capacity.

Capital expenditure is structured by equipment groups (slitter, forming-welding line, hydrotester, finishing, utilities, buildings) and phased according to a construction schedule. The model automatically accrues a maintenance reserve based on operating hours and equipment replacement cycles, separating routine upkeep from periodic overhauls that temporarily reduce line availability.

Working capital is modeled with a granular schedule for raw coil inventory, work-in-progress after forming, finished pipes before shipment, and accounts receivable. Inventory levels respond to supplier lead times, production cycle times, and customer payment terms, making the peak cash requirement visible during the ramp-up and steady-state phases.

Modeling specifics

• Cumulative material yield chain: models yield losses sequentially from coil to final pipe, with adjustable coefficients per process step; prevents gross margin overstatement.
• Multi-assortment bottleneck engine: calculates effective capacity as the minimum of slitter, welding, testing, and finishing throughput, accounting for dedicated line assignments and changeover downtime.
• Energy profile per SKU: derives variable electricity and gas consumption from welding current needs, drive power, and coating heat, then computes monthly costs using contracted tariffs.
• Calendar-based maintenance planner: scheduled downtime for line overhauls is placed on a calendar, automatically reducing production volume and increasing maintenance cash outflows in those periods.
• Coil inventory and WIP dynamics: tracks material quantities at each buffer (coil yard, after forming, before test, before coating) to correctly time working capital requirements.
• Sales contract price escalation: built-in ability to link a portion of pipe selling prices to a raw material index (coil price), simulating margin-at-risk in volatile steel markets.
• Operational expenditure split: consumables (welding wire, saw blades, coat material) are flexed per ton; fixed plant overhead is allocated by capacity block, making cost behavior transparent at different utilization levels.

What's included in the base version

• Revenue model with product mix, price list per SKU, and order book assumptions
• Production and capacity planning with hourly rates by line and product
• Variable and fixed operating cost modules (per ton and monthly)
• CAPEX schedule with depreciation groups and maintenance reserve accrual
• Staff plan by role, shift, and headcount with associated payroll costs
• Equity injection and debt drawdown schedule with repayment logic
• Annual financial statements: income statement, cash flow, balance sheet
• Key investment metrics: NPV, IRR, payback period, DSCR
• Tornado sensitivity analysis on main value drivers

Common modeling mistakes

• Assuming 100% material efficiency from coil to pipe — overstates gross profit and EBITDA by 8–15% per ton.
• Using a single average energy consumption rate for the entire plant — misstates variable cost by 15–25% when product mix changes.
• Treating maintenance as a fixed cost without scheduled line outages — overestimates annual throughput by up to 20% and understates maintenance cash outflows.
• Ignoring working capital buildup during initial ramp-up — understates peak cash requirement by 30–40% in the first 12 months.
• Neglecting changeover time between pipe sizes — inflates effective capacity and makes revenue projections unattainable.