Mbira toll road

model "Mbira Toll Road" {
  currency  USD
  data      "toll_inputs.xlsx"       // tolls, traffic counts, fees, curves
  include   "../concession"          // shared with the Border Post model
}

timeline {
  start          2024-10-01           // financial close 2024-12-31
  fiscal_year    ends Dec
  step           quarter
  construction   36m   detail month   // monthly sheets for construction, like ConTime
  operations     until construction.start + inputs.concession
}

list vehicles = [light, minibus, bus, heavy, haulage, abnormal]
// the gates come from the rows of the Gates table

/// Tables are sheets of toll_inputs.xlsx (named in model.rfn). Scalars stay here.

table toll: USD/vehicle sheet "Tolls" {
  rows     phase
  columns  light "Light motor vehicle", minibus, bus, heavy, haulage,
           abnormal "Abnormal load"
}

/// 2018 classified AADT on the approach to each gate
table aadt: vehicle/day {
  rows     gate "Toll gate"
  columns  light "Light motor vehicle", minibus, bus, heavy, haulage,
           abnormal "Abnormal load"
}

table growth: %/y {                      // low-growth case, feasibility report
  rows     vehicle "Vehicle class"
  columns  rate "Growth rate"
}

table gates {
  rows     gate "Toll gate"
  columns  opens: date "Opens",
           through_share: % "Through traffic (heavy and haulage)"
}

inputs "Tolls and traffic" {
  concession      = 25y
  active_gates    = [chidza, nyuni]           // Base case 2
  cpi             = 3.64 %/y                  @source "US 5-year average 2018-2022"
  operations_cost = 13.5 %                    @note "12.5% per feasibility report plus 1% social"
}

inputs "Construction" {
  roadworks      = 140_964_321 USD            @note "includes 5% contingency"
  plaza          = 3_735_410 USD              // two gates
  toll_software  = 1_000_000 USD
  rams           = 2_000_000 USD
  idbz_review    = 30_000 USD

  maintenance    = 69_725_700 USD             // routine and periodic, whole concession
}

table construction_fees: %               // Fee | Share of construction amount
table transaction_fees: %                // Fee | Share of project costs
table spend_curve: %                     // Month ending | Spend
table maintenance_curve: %               // Quarter ending | Spend
table forward_curve: %/y                 // Quarter ending | Rate

inputs "Financing" {
  gearing         = 70 %
  swap_rate       = 5.79 %/y                  @source "6m USD LIBOR swap, 17 Nov 2023"
  margin          = 6.00 %/y
  hedged          = 80 %
  term            = 15y
  grace           = 1y
  sizing          = option leverage | dscr                              // base: dscr
  profile         = option level_principal | sculpted | level_debt_service
  dscr_profile    = series by operations.year { 1..25: 1.10 } x
  upfront_fee     = 1.25 %
  commitment_fee  = 0.50 %/y
  share_capital   = 72_023_659 USD
  shl_rate        = 12 %/y
  dsra_rule       = option half_of_12m | next_6m | next_3m              // base: next_3m
  mmra_cover      = 12m
  cit_rate        = 24.72 %
  tax_depreciation = 1.14 %/q
  discount_rate   = 12 %/y
}

module rev "Revenue" {
  for g in inputs.gates {
    period open[g] = inputs.gates[g].opens to operations.end
  }

  for v in vehicles {
    traffic_index[v] = grow(inputs.growth[v], from: timeline.start)   // one value column, so no .rate needed
    fee[v]: USD/vehicle = (if construction then inputs.toll[construction, v]
                           else inputs.toll[operations, v])
                        | grow inputs.cpi yearly in Dec
  }

  for g in inputs.active_gates, v in vehicles {
    through[g, v] = if v in [heavy, haulage] then inputs.gates[g].through_share else 0 %
    count[g, v]: vehicle = inputs.aadt[g, v] * traffic_index[v] * period.days
                         * (1 - through[g, v])                  | during open[g]
    revenue[g, v]: USD   = count[g, v] * fee[v]
  }

  total: USD = sum(revenue[*, *])
}

/// Lives on the monthly construction ruler, like the ConCost sheet
module cons_cost "Construction costs" monthly {
  base: USD = spread(inputs.roadworks + inputs.plaza + inputs.toll_software + inputs.rams,
                     over: construction, profile: inputs.spend_curve)

  for f in inputs.construction_fees {
    fee[f]: USD = base * inputs.construction_fees[f]
  }
  idbz: USD = inputs.idbz_review | at construction.first
  project_costs: USD = base + sum(fee[*]) + idbz

  for t in inputs.transaction_fees {
    transaction[t]: USD = inputs.transaction_fees[t] * sum(project_costs)
                        | at construction.first
  }

  prefunding: USD = reserves.dsra.initial + reserves.mmra.initial + ops.wc.initial
                  | at construction.last
  total: USD = project_costs + sum(transaction[*]) + prefunding
}

module cons_fin "Construction funding" monthly {
  /// "Project costs / funding requirement - paste" on the Solve sheet
  solve requirement: USD = cons_cost.total + fees + idc

  funding requirement {
    first  share_capital      up to inputs.share_capital
    then   shareholder_loan   up to equity.shl_commitment
    then   construction_debt  rest
  }

  balance construction_debt: USD {
    + funding.construction_debt
    - converted
  }
  converted:  USD = construction_debt.opening + funding.construction_debt
                  | at construction.last          // becomes the term loan
  idc:        USD = construction_debt.opening * (inputs.swap_rate + inputs.margin)
  upfront:    USD = inputs.upfront_fee * debt.size | at construction.first
  commitment: USD = (debt.size - construction_debt.opening) * inputs.commitment_fee
  fees:       USD = upfront + commitment
}

module debt "Senior debt" {
  period repayment = (operations.start + inputs.grace) for inputs.term

  all_in: %/y = inputs.hedged * inputs.swap_rate
              + (1 - inputs.hedged) * inputs.forward_curve
              + inputs.margin

  /// "Cash flow available for senior debt - paste" on the Solve sheet
  solve cfads: USD = cash.cfads

  target_dscr: x = inputs.dscr_profile[operations.year]
  capacity:  USD = npv(cfads / target_dscr | during repayment, rate: all_in)
  size:      USD = match inputs.sizing {
    leverage => inputs.gearing * sum(cons_fin.requirement)
    dscr     => capacity
  }

  // cons_fin.converted is monthly. Read here, it is summed into the quarter.
  balance senior: USD {
    + cons_fin.converted
    - principal
  }
  interest:  USD = senior.opening * all_in
  principal: USD = match inputs.profile {
    level_principal    => size / count(repayment)
    sculpted           => cfads / target_dscr - interest
    level_debt_service => pmt(all_in, periods_left(repayment), senior.opening) - interest
  } | during repayment

  debt_service: USD = interest + principal
}

module reserves "DSRA and MMRA" {
  dsra_target: USD = match inputs.dsra_rule {
    half_of_12m => 50 % * (debt.debt_service | next 12m)
    next_6m     => debt.debt_service | next 6m
    next_3m     => debt.debt_service | next 3m
  }

  dsra = reserve(target: dsra_target, initial: at construction.end)
  mmra = reserve(target: ops.maintenance | next inputs.mmra_cover,
                 initial: at construction.end)
}

scenario four_gates "Base case 1 (4 toll gates)" {
  inputs.active_gates  = [chidza, tonde, nyuni, pfuma]
  inputs.plaza         = 7_470_821 USD
  inputs.toll_software = 2_000_000 USD
  inputs.dscr_profile  = series by operations.year { 1..8: 1.60, 9..11: 1.70, 12..: 1.60 } x
}

scenario base "Base case 2 (2 toll gates)" {}

scenario three_gates "Base case 3 (3 toll gates)" {
  inputs.active_gates  = [chidza, tonde, nyuni]
  inputs.plaza         = 5_603_116 USD
  inputs.toll_software = 1_500_000 USD
  inputs.dscr_profile  = series by operations.year { 1..4: 1.40, 5..: 1.50 } x
}

/// The Guide sheet's colour key, stated once
style {
  font        "Calibri"
  inputs      fill #FFFFCC
  imports     font #0000FF          // from another sheet
  exports     font #FF0000          // used on another sheet
  separators  fill #DDEBF7
  checks      ok #C6EFCE  error #FFC7CE  alert #FFEB9C
  tabs        inputs #FFFFCC  monthly #D9D9D9  outputs #DDEBF7  checks #CCFFFF
}