Aluminium cable plant

model "Aluminium Cable Plant" {
  sponsor  "Rho Power"
  currency USD
  display  thousands
  data     "plant_inputs.xlsx"  // product mix and the machinery quote
}

timeline {
  start        2026-06-01
  step         year
  construction 1y
  operations   20y
}

/// The product mix and the supplier quote are sheets of plant_inputs.xlsx

table products sheet "Product mix" {
  rows     line "#"
  columns  product: text,
           size:   mm2   "Size (mm2)",
           length: km    "Length (km)",
           al:     kg/km "Al (kg/km)",
           gsw:    kg/km "GSW (kg/km)",
           pvc:    kg/km "PVC (kg/km)",
           price:  USD/m "Selling Price (US$/m)"
}

table machinery {                        // Jiacheng quote, FOB Shanghai
  rows     item "#"
  columns  description: text "Machine Description",
           qty "Qty (sets)",
           unit_price: USD "Unit Price (US$)"
}

inputs "Products" {
  stock_turn   = 6 w
  price_uplift = 0 %
}

inputs "Materials and operations" {
  material_price   = { al_rod: 3_500, gsw: 2_000, plastics: 1_450 } USD/t
  waste            = 5 %
  import_duty      = 5 %           // of raw material cost
  freight          = 250 USD/t
  inflation        = 3.3 %/y
  employees        = 30
  staff_cost       = 1_500 USD/month
  electricity      = 1_000_000 kWh/y
  grid_tariff      = 0.12 USD/kWh
  generator_cost   = 0.64 USD/kWh
  generator_share  = 25 %
  packaging        = 3 %           // of revenue
  maintenance      = 2 %/y         // of capex
  debtor_days      = 45d
  creditor_days    = 30d
}

inputs "Capex and financing" {
  vat              = 15.5 %
  vat_reclaimable  = option no | yes
  factory_shell    = 1_400_000 USD
  shell_surcharge  = 20 %          // duty and freight
  civils           = 1_000_000 USD
  electrical       = 1_500_000 USD // generator and transformers
  contingency      = 500_000 USD
  asset_life       = 20y
  gearing          = 70 %
  rate             = 15 %/y
  tenor            = 10y
  dsra_cover       = 0 USD
  tax_rate         = 25 %
  loss_expiry      = 5y
  tax_holiday      = 0y
  discount_rate    = 10 %/y
}

module ops "Operations" {
  /// One stock turn produces every product in the mix once
  revenue_per_turn: USD = sum(inputs.products.length * inputs.products.price)
                        * (1 + inputs.price_uplift)

  // km x kg/km gives kg; declared as t, so the compiler divides by 1,000
  al:  t = sum(inputs.products.length * inputs.products.al)  * (1 + inputs.waste)
  gsw: t = sum(inputs.products.length * inputs.products.gsw) * (1 + inputs.waste)
  pvc: t = sum(inputs.products.length * inputs.products.pvc) * (1 + inputs.waste)
  materials_per_turn: USD = al  * inputs.material_price[al_rod]
                          + gsw * inputs.material_price[gsw]
                          + pvc * inputs.material_price[plastics]

  turns: /y = rounddown(52 w/y / inputs.stock_turn)      // 8 a year
  index     = grow(inputs.inflation, from: operations.start)

  revenue:     USD = revenue_per_turn * turns * index           | during operations
  materials:   USD = materials_per_turn * turns * index         | during operations
  power_rate: USD/kWh = inputs.grid_tariff * (1 - inputs.generator_share)
                      + inputs.generator_cost * inputs.generator_share
  power:       USD = inputs.electricity * power_rate * index    | during operations
  packaging:   USD = revenue * inputs.packaging
  freight:     USD = (al + gsw + pvc) * turns * inputs.freight * index
  duty:        USD = materials * inputs.import_duty
  salaries:    USD = inputs.employees * inputs.staff_cost * index | during operations
  maintenance: USD = capex.total * inputs.maintenance * index   | during operations

  opex:   USD = materials + power + packaging + freight + duty + salaries + maintenance
  ebitda: USD = revenue - opex
}

module capex "Capex" {
  machinery: USD = sum(inputs.machinery.qty * inputs.machinery.unit_price)   // 2,507,400
                 * (1 + if inputs.vat_reclaimable == no then inputs.vat else 0 %)
  shell:     USD = inputs.factory_shell * (1 + inputs.shell_surcharge)
  total:     USD = machinery + shell + inputs.civils + inputs.electrical + inputs.contingency
  spend:     USD = total | at construction.first
}

module debt "Term loan" {
  period repayment = operations.start for inputs.tenor

  size: USD = inputs.gearing * capex.total
  balance loan: USD {
    + size | at construction.first
    - principal
  }
  interest:  USD = loan.opening * inputs.rate
  payment:   USD = pmt(inputs.rate, inputs.tenor, size)    | during repayment
  principal: USD = payment - interest

  dsra = reserve(target: inputs.dsra_cover | during repayment)
}

module tax "Tax" {
  depreciation: USD = capex.total / inputs.asset_life      | during operations
  pbt: USD = ops.ebitda - depreciation - debt.interest
  losses = tax_losses(profit: pbt, expiry: inputs.loss_expiry)
  period holiday = operations.start for inputs.tax_holiday
  tax: USD = losses.taxable * inputs.tax_rate              | during not holiday
}

module valuation "Valuation" {
  fcf:  USD = ops.ebitda - capex.spend - ops.wc.change - tax.tax
  project_irr = xirr(fcf)
  equity_cash: USD = fcf + debt.size * construction.first - debt.payment
  equity_irr  = xirr(equity_cash)
  payback     = payback(fcf)

  nav:   USD = (capex.total - cumulative(tax.depreciation)) + ops.wc.net
             + cash.balance.closing + debt.dsra.closing - debt.loan.closing
  going_concern: USD = npv(fcf | after period.end, rate: inputs.discount_rate)
                     - debt.loan.closing
}

snapshot "Valuation at key periods" {
  at    commissioning: operations.start,  year_1: operations.start + 1y
  show  valuation.nav, fs.bs.equity, valuation.going_concern
}

/// The Sensitivity sheet's two-way tables, run by macro, no data tables
results "Payback: aluminium price vs stock turn" {
  across  inputs.material_price[al_rod] = [3_000, 3_250, 3_500, 3_750, 4_000] USD/t
  by      inputs.stock_turn             = [4, 5, 6, 7, 8] w
  show    valuation.payback
}

results "Payback: grid tariff vs aluminium price" {
  across  inputs.grid_tariff            = [0.08, 0.10, 0.12, 0.14, 0.16] USD/kWh
  by      inputs.material_price[al_rod] = [3_000, 3_500, 4_000] USD/t
  show    valuation.payback
}