Ferrous Metal Foundry Financial Model

Description

A dedicated financial model for a ferrous metal foundry, capturing the entire transformation from metallic charge to finished castings. The tool is built around the foundry’s tier one cost drivers — scrap and alloy blending, electricity consumption under industrial tariffs, sand system mass balance, and yield cascades — rather than generic manufacturing assumptions. It is intended for entrepreneurs and financial managers evaluating a greenfield or brownfield investment in iron and steel castings for sectors such as automotive, heavy machinery, construction, and energy.

The production program flexibly handles multiple casting families differentiated by weight, alloy grade, molding technology, and complexity. For each family, the model builds a recipe-based raw material budget that blends varying grades of steel scrap, pig iron, returns, and ferroalloys. Liquid metal output then passes through a yield cascade accounting for melting loss, pouring scrap, gating systems, and fettling, giving a realistic volume of saleable net castings. This avoids the common error of equating furnace output with finished goods.

Melting is the single largest energy consumer. The model includes a granular electricity cost module for induction and electric arc furnaces, featuring distinct tariffs per hour of the day, demand charges (kW), power factor penalties, and transformer losses. It captures the non-linear effect of heat size on unit energy consumption and allows the user to set furnace availability around relining campaigns, capturing the reduction in effective annual tonnage.

The sand department is modeled as a closed-loop system with green sand and chemically bonded sand lines. Material inputs (fresh sand, bentonite, resins, catalysts) are consumed per ton of poured metal and returned sand is subject to reclamation yield and performance decay. The model automatically calculates the bleed rate of spent sand and the resulting disposal cost, as well as periodic full rebonding. Mold and core tooling (patterns, core boxes) are depreciated per impression rather than on an arbitrary straight-line schedule, aligning costs with actual serial life.

Personnel costs are structured by shift and department — melt shop, molding, core-making, fettling, quality, maintenance — with flexible scaling based on number of active shifts and a trigger for overtime during peak campaigns. Maintenance is not a fixed overhead: planned preventive routines and break-in-related repairs are linked to cumulative tons poured and operating hours, while major overhauls can be scheduled in the project timeline, forcing downtime and reducing available capacity.

Environmental compliance is embedded through baghouse consumables (filter bags, adsorbents), dust disposal fees, slag handling and potential landfill levies, and, where applicable, a carbon cost module. By-products such as saleable slag and waste heat are also given revenue streams. The model consolidates all these drivers into monthly three-statement financials over the project life, with a construction waterfall, multiple debt tranches, interest during construction, and sculpted repayment, providing a clear order-of-magnitude view of the total capital required.

Modeling specifics

• Dynamic scrap charge optimization blends multiple scrap grades (plate, structural, shredded, borings) with variable composition and price, automatically recalculating raw material cost per liquid ton when the mix changes.
• Yield cascade from liquid metal to net saleable casting across three stages (melting, pouring/gating, fettling) — prevents the common practice of applying a single overall yield, which hides losses in individual steps.
• Melting energy cost simulation with time-of-use electricity rates, segregated demand charges (peak kW), power factor penalty bands, and heat-size-dependent kWh/ton curves for induction and EAF.
• Closed-loop sand system mass balance: tracks fresh sand addition, reclamation efficiency, binder degradation, and generates bleed-off volume and disposal cost, avoiding the error of treating sand as a once-through consumable.
• Pattern and die life amortization linked to production runs — the model automatically retires tooling after a user-defined impression count and triggers a replacement capex, aligning with the serial nature of foundry tooling.
• Shift-based labor model with core, molding, melt, and finishing crews; overtime costs activate automatically when monthly tonnage exceeds the standard shift threshold, ensuring headcount costs reflect production peaks.
• Maintenance budget divided into runtime-dependent consumables per ton poured and calendar-based major overhauls; forced outage days during relining or overhaul reduce melting capacity in the corresponding month.
• Separate contribution P&L per casting family (e.g., automotive brackets vs. pump housings) highlights which products absorb overhead and which are margin destroyers, enabling product mix rationalization.
• Environmental cost line items embedded by production volume — baghouse consumables per ton of dust, slag disposal per ton of slag, and a driver for carbon tax if the jurisdiction applies it.
• Financing structure with construction drawdown, multiple senior debt tranches, interest capitalized during the ramp-up, and sculpted principal repayment based on cash flow available for debt service.

What's included in the base version

• Capital expenditure schedule by process area (melting, molding, sand, finishing, utilities, environmental) with itemized equipment and construction costs.
• Raw material and scrap blending module with recipe-based input quantities, variable scrap composition, and alloy additive calculations per casting family.
• Product mix revenue model: sales volumes by casting family, weight category, alloy grade, and unit price, feeding into a monthly shipment plan.
• Melting energy cost module with electricity tariff structure (energy charge, demand charge, power factor surcharge), heat size selection, and furnace availability calendar.
• Sand preparation and reclamation operating expense loop with mass balance for green sand and chemically bonded systems.
• Personnel plan by shift, department, and role; includes base salary, shift loading, and automatic overtime trigger.
• Monthly three-statement financial model (income statement, cash flow, balance sheet) covering the investment phase through steady-state operations.
• Debt and equity waterfall with drawdown schedule, interest during construction, and sculpted debt repayment.
• Investment analysis dashboard: NPV, IRR, payback period, DSCR, and one-way sensitivity tables on key price and volume drivers.

Common modeling mistakes

• Assuming a constant scrap mix cost without accounting for the need to blend different steel grades — raw material expense understated by 8–12%.
• Ignoring the difference between liquid metal weight and finished casting weight (yield losses at gates, risers, and fettling) — net saleable output overestimated by 15–25%.
• Applying a flat electricity rate to melting without modeling demand charges and time-of-use tariffs — energy cost per ton underestimated by $12–20 per ton.
• Neglecting sand reclamation loss and binder dilution, treating the sand system as a single-pass consumable — sand-related cost underestimated by 20–30%.
• Modeling furnace availability at 100% capacity without reserving downtime for relining or major maintenance — annual throughput overstated by 12–18%.