Canned Fish Plant Financial Model
Description
This Excel model simulates a greenfield canned fish processing plant centered on one primary product line — receiving, filleting, canning, sterilizing, and storing such as sardines or mackerel in a single can format. It covers the entire operational and financial lifecycle, from procurement to shipment, and is built for owners/investors to accurately assess feasibility without relying on oversimplified proxies.
The model uniquely captures the seasonal nature of fish supply, linking monthly catch availability and quota windows to the production schedule. It incorporates species-specific yield loss at each stage (filleting, can filling, retort), a mass-balance module that automatically generates by-product volumes (fishmeal and oil), and cold storage dynamics that directly feed working capital calculations. Even with a single core product, it faithfully represents cannery bottlenecks: retort cycle times per batch, canning line changeovers for different packaging counts, and HACCP compliance costs.
Financial statements are integrated with operations: COGS reflects real bills-of-materials per can, variable utilities scale with throughput, and CAPEX includes cold rooms, processing machinery, and building construction, with debt drawdowns and a grace period. (Note: figures illustrate the order of magnitude of investment, not final values.)
Modeling specifics
- Monthly raw material availability driven by species seasonality and fishery quotas, preventing flat production profiles.
- Stage-wise yield tree from whole fish to finished cans, with distinct loss factors for heading, gutting, filleting, and retort shrink.
- Mass-balance co-product computation: offal, heads, and trimmings converted to fishmeal and oil using recovery ratios, generating separate revenue streams.
- Retort autoclave batch capacity modeled with sterilization cycle time per can size and batch size, setting a realistic daily production ceiling.
- Cold store inventory limits and shelf-life tracking for both raw material and finished goods, influencing order timing and working capital peaks.
- Comprehensive utility modeling: water for washing and retort, steam for cooking/sterilization, electricity for refrigeration and line drives — all linked to volume drivers.
- HACCP fixed and per-batch costs embedded in overheads (lab tests, monitoring personnel, traceability).
- Working capital built dynamically: pre-season raw material stocking, finished goods inventory for off-season sales, and receivables from offtake contracts.
What's included in the base version
- Monthly production plan and raw material procurement schedule
- Bill-of-materials and direct costing per can
- Payroll and headcount planning (direct and indirect)
- Variable utility cost calculation (water, electricity, steam)
- Fixed overheads including HACCP, maintenance, insurance
- CAPEX & depreciation schedule
- Financing structure with equity, senior debt, working capital line
- Three-statement financial model (monthly P&L, Cash Flow, Balance Sheet)
- Investment return metrics (IRR, NPV, payback)
- Sensitivity scenarios (raw material price, sales price, capacity utilization)
Common modeling mistakes
- Assuming year-round constant catch — overestimates annual capacity utilization by 20–35% and underestimates working capital needs 1.5–2x.
- Neglecting filleting and can-fill yield loss — understates COGS by 6–12%, inflating gross margin.
- Ignoring retort cycle time constraints — overstates effective daily output by 15–25%.
- Dismissing by-product revenue — lengthens payback period by 0.5–1.5 years.
- Using flat utility cost per can — understates OPEX volatility, causing cash flow timing mismatches and potential liquidity shortfalls.
- Not modeling shelf life and spoilage — overstates inventory value by 3–8% and hides write-off risks.
- Overlooking HACCP fixed costs — understates annual fixed overhead, leading to up to 3% overstatement of operating profit.
