axmo: "1.0"
name: Cash balance
description: >-
  Recipe — a rolling balance carried forward month by month. Closing balance = last month's closing balance
  (or the opening balance, in the first month) + receipts − payments. Across time a balance takes its LAST value;
  across accounts it is summed.
dimensions:
  Accounts:
    type: categorical
    layers: [Top_Accounts, Account]
    tree:
      All_Accounts:
        - Bank A
        - Bank B
  Time:
    type: sequential
    sequentialType: axmo_time
    parameters:
      selected_levels: [month, quarter]
      start: "2027-01"
      end_mode: end_point
      end: "2027-06"
series:
  Opening Balance:
    type: input
    description: Cash on 1 January 2027, by account.
    dimensions: [Accounts]
  Receipts:
    type: input
    description: Cash received in the month.
    dimensions: [Accounts, Time]
  Payments:
    type: input
    description: Cash paid out in the month.
    dimensions: [Accounts, Time]
  Closing Balance:
    type: calculated
    description: Cash at the end of the month. Summed across accounts; across time the last month's value.
    formula: Nz(Closing Balance[Month[-1]], Opening Balance) + Receipts - Payments
    dimensions:
      - Accounts:
          rollUp: SUM
      - Time:
          rollUp: LAST
dataSources:
  Openings:
    sourceType: excel-range
    rangeRef: Inputs!A1:B3
    hasHeaders: true
    shape: table
  Flows:
    sourceType: excel-range
    rangeRef: Inputs!D1:G13
    hasHeaders: true
    shape: table
dataLoaders:
  Load openings:
    type: seriesLoader
    source: Openings
    target: Opening Balance
    loadMode: table
    valueColumn: Opening
    duplicateHandling: BLOCK
    dimensionMappings:
      Accounts: { column: Account }
  Load flows:
    type: seriesLoader
    source: Flows
    loadMode: table
    dimensionMappings:
      Accounts: { column: Account }
      Time: { column: Month }
    targets:
      - { target: Receipts, valueColumn: Receipts, duplicateHandling: BLOCK }
      - { target: Payments, valueColumn: Payments, duplicateHandling: BLOCK }
outputRanges:
  Cash report:
    cell: Report!A1
    rowDef:
      blocks:
        - dimension: Accounts/Account
        - dimension: Accounts/Top_Accounts
        - series: Receipts
          format: "#,##0"
          newLine: true
        - series: Payments
          format: "#,##0"
        - series: Closing Balance
          format: "#,##0"
    colDef:
      blocks:
        - dimension: Time/Month
        - dimension: Time/Quarter
data:
  Inputs!A1:B3:
    values:
      - [Account, Opening]
      - [Bank A, 10000]
      - [Bank B, 5000]
  Inputs!D1:G13:
    values:
      - [Account, Month, Receipts, Payments]
      - [Bank A, "2027-01", 4000, 3000]
      - [Bank A, "2027-02", 3500, 5000]
      - [Bank A, "2027-03", 4200, 3900]
      - [Bank A, "2027-04", 3800, 6100]
      - [Bank A, "2027-05", 5000, 2500]
      - [Bank A, "2027-06", 4600, 4400]
      - [Bank B, "2027-01", 1200, 900]
      - [Bank B, "2027-02", 800, 1500]
      - [Bank B, "2027-03", 1500, 1100]
      - [Bank B, "2027-04", 1100, 1300]
      - [Bank B, "2027-05", 900, 700]
      - [Bank B, "2027-06", 1300, 1600]