Quarry and Construction Dump Truck Carrier Financial Model

Description

This financial model is built for a dump truck transportation business that serves quarries, mines, and large construction projects. The business owns and operates a fleet of rigid or articulated dump trucks and generates revenue through a mix of contracts — typically paid per ton of material moved, per loaded kilometer, or per operating hour. The model translates the operational logic of the haul cycle into financial projections, capturing every variable that drives profitability.

The core of the model is a haul-cycle engine. For each contract, you define the haul distance, road grade, average speed, payload, and loading/unloading times. The model calculates trips per shift, tons per day, and fuel consumption based on the specific cycle, not just a flat rate. Idle time, queueing at the loader or crusher, and shift patterns are included. This allows testing how changes in cycle time, payload, or distance affect revenue and costs.

Operating costs are modeled with deep granularity. Fuel consumption is driven by engine load, distance traveled, and idle hours, with fuel price escalation. Tire costs are forecast using a wear model that accounts for haul distance per tire set and road surface condition, triggering periodic retreading or replacement. Maintenance and repair expenses are projected based on engine hours or mileage, with separate budgets for planned and unplanned downtime. Driver wages are structured around shifts, overtime, and productivity bonuses if applicable.

The fleet can consist of multiple truck types with individual specifications, acquisition dates, financing (bank loans, leases, or cash), and depreciation schedules. The model also supports phased fleet expansion — adding or retiring trucks — aligning capital expenditures with contract start dates and volume growth. A multi-contract revenue module handles different tariff structures, escalations, and contract durations, giving a consolidated view of cash flows and enabling sensitivity analysis on contract mix and renewal assumptions.

Modeling specifics

• Haul cycle simulation with distance, grade, speed, payload, and idle/queuing time — directly computes trips per shift, tons moved, and fuel burn per trip, rather than using flat productivity rates.
• Tire wear model that calculates replacement and retreading intervals based on total kilometers traveled per tire type and road surface condition, preventing systematic underestimation of tire costs.
• Engine hour-based maintenance scheduling with separate cost rates for preventive maintenance, major overhauls, and unplanned repairs tied to OEM service intervals.
• Multi-contract revenue module enabling each contract to have its own pricing structure (per ton, per km, per hour), start/end dates, escalation formulas, and volume commitments — automatically stacking revenues side-by-side.
• Driver shift modeling with overtime premiums, shift differentials, and productivity-linked bonuses, aligning labor expense with operational peaks and contractual requirements.
• Fleet financing builder that handles different capital structures per truck (outright purchase, finance lease, operating lease, bank loan) with individual interest rates, terms, and balloon payments.
• Scenario-controlled fleet ramp-up and retirement: you define acquisition and disposal dates for each unit; the model automatically adjusts depreciation, insurance, and property tax accordingly.
• Downtime allowance module that separates mechanical availability, weather, and contractual standby, feeding effective working days into the production forecast.

What's included in the base version

• Interactive Assumptions & Input Dashboard (truck specifications, contract parameters, cost drivers, macro assumptions)
• Haul Cycle & Productivity Engine (cycle time, trips per shift, tonnage per truck per contract)
• Revenue Projection Module (multi-contract with rate escalation and volume input)
• Fuel Consumption & Cost Model (load-distance-idle driven consumption with price escalation)
• Tire Wear & Replacement Schedule (kilometer-based wear, retreading and replacement logic)
• Maintenance & Repair Budget (engine hour-based, split by planned/unplanned)
• Driver & Labor Cost Model (shift-based wages, overtime, bonuses)
• Fleet CAPEX, Depreciation & Disposal Calculator (unit-by-unit acquisition, financing type, depreciation)
• Debt & Financing Schedule (loan drawdowns, repayments, interest calculations)
• Integrated Financial Statements (monthly P&L, Cash Flow, Balance Sheet for up to 15 years)
• Key Financial Metrics Dashboard (IRR, NPV, payback period, DSCR, equity multiple)
• Sensitivity & Scenario Analysis Table (one- and two-way on revenue drivers, costs, and financing)

Common modeling mistakes

• Assuming constant fuel burn per engine hour regardless of load, haul distance, and idle time — fuel costs are understated by 20–40%, inflating EBITDA margins.
• Neglecting the tire replacement cycle and retreading schedule — tire operating expense is underestimated by 30–50%, leading to a cash flow shock in years when multiple sets become due.
• Using a fixed trips-per-day assumption without an explicit haul cycle model — revenue is overestimated by 15–25% for contracts with long hauls or slow loading, causing overinvestment in fleet.
• Applying a flat maintenance accrual per truck-year instead of engine-hour-based scheduling — maintenance cash outflows are mistimed, leaving large unfunded overhauls in high-utilization periods.
• Ignoring idle and queue waiting times in the cycle calculation — effective payload moved per shift is overestimated by 10–20%, so the required fleet size appears smaller than it actually is.