axmo: "1.0"
name: Verde Logistics cost-centre reporting
description: >-
  Recipe: cost-centre reporting. The cost-centre tree is generated from a master list; actual cost (a long table) and
  budget (a wide table with a trailing Total column, which the band leaves out) fill one series over a Versions
  dimension; variance %, and cost per head over an averaged headcount, are recalculated at every total. The report
  picks each version with a filter written in the range (Actual := Cost[Actual]).
dimensions:
  CostCentres:
    type: categorical
    layers: [Top_CostCentres, Department, Cost Centre]
    tree:
      All_CostCentres: null
  Versions:
    type: categorical
    layers: [Top_Versions, Version]
    tree:
      All_Versions:
        - Actual
        - Budget
  Time:
    type: sequential
    sequentialType: axmo_time
    parameters:
      selected_levels: [month, quarter]
      start: "2027-01"
      end_mode: end_point
      end: "2027-03"
series:
  Cost:
    type: input
    description: Cost, EUR, by cost centre, version and month. Actual and Budget are never added together.
    dimensions:
      - CostCentres:
          rollUp: SUM
      - Versions:
          rollUp: NONE
      - Time:
          rollUp: SUM
  Headcount:
    type: input
    description: People at the end of the month. Summed across cost centres; averaged across the months of a quarter.
    dimensions:
      - CostCentres:
          rollUp: SUM
      - Time:
          rollUp: AVG
  Variance:
    type: calculated
    description: Actual less budget, EUR. Positive means overspent.
    formula: Cost[Actual] - Cost[Budget]
    dimensions: [CostCentres, Time]
  Variance %:
    type: calculated
    description: Variance as a share of budget, recalculated from the totals at every level.
    formula: Variance / Cost[Budget]
    dimensions:
      - CostCentres:
          rollUp: FORMULA
      - Time:
          rollUp: FORMULA
  Cost per head:
    type: calculated
    description: >-
      Actual cost per person, EUR. Recalculated from the totals, so a quarter is the quarter's actual cost over the
      average month-end headcount of its three months.
    formula: Cost[Actual] / Headcount
    dimensions:
      - CostCentres:
          rollUp: FORMULA
      - Time:
          rollUp: FORMULA
dataSources:
  Cost centres:
    sourceType: excel-range
    rangeRef: CostCentres!A1:B6
    hasHeaders: true
    shape: table
  Actuals:
    sourceType: excel-range
    rangeRef: Actuals!A1:C16
    hasHeaders: true
    shape: table
  Budget:
    sourceType: excel-range
    rangeRef: Budget!A1:E6
    hasHeaders: true
    shape: table
    columnLayout: dimension
    columnsDimension: Time
    bandStartMode: after
    bandStartColumn: Cost centre
    bandEndMode: before
    bandEndColumn: Total
  Headcount:
    sourceType: excel-range
    rangeRef: Headcount!A1:C16
    hasHeaders: true
    shape: table
dataLoaders:
  1 Departments:
    type: dimensionGenerator
    source: Cost centres
    target: CostCentres
    sourceColumn: Department
    parentElement: All_CostCentres
  2 Cost centres:
    type: dimensionGenerator
    source: Cost centres
    target: CostCentres
    sourceColumn: Cost centre
    parentColumn: Department
  Load actuals:
    type: seriesLoader
    source: Actuals
    target: Cost
    loadMode: table
    valueColumn: Actual
    duplicateHandling: BLOCK
    dimensionMappings:
      CostCentres: { column: Cost centre }
      Time: { column: Month }
    fixedCoordinates:
      Versions: Actual
  Load budget:
    type: seriesLoader
    source: Budget
    target: Cost
    loadMode: table
    valueColumn: _Value
    duplicateHandling: BLOCK
    dimensionMappings:
      CostCentres: { column: Cost centre }
      Time: { column: _HeaderDimElement }
    fixedCoordinates:
      Versions: Budget
  Load headcount:
    type: seriesLoader
    source: Headcount
    target: Headcount
    loadMode: table
    valueColumn: Headcount
    duplicateHandling: BLOCK
    dimensionMappings:
      CostCentres: { column: Cost centre }
      Time: { column: Month }
outputRanges:
  Cost report:
    cell: Report!A1
    rowDef:
      blocks:
        - dimension: CostCentres/Top_CostCentres
        - dimension: CostCentres/Department
        - dimension: CostCentres/Cost Centre
        - series: Actual := Cost[Actual]
          format: "#,##0"
          newLine: true
        - series: Budget := Cost[Budget]
          format: "#,##0"
        - series: Variance
          format: "#,##0"
        - series: Variance %
          format: 0.0%
        - series: Cost per head
          format: "#,##0.00"
    colDef:
      blocks:
        - dimension: Time/Month
        - dimension: Time/Quarter
data:
  CostCentres!A1:B6:
    values:
      - [Cost centre, Department]
      - [OPS-NORTH, Operations]
      - [OPS-SOUTH, Operations]
      - [SALES-IT, Sales]
      - [SALES-EXPORT, Sales]
      - [ADMIN, Administration]
  Actuals!A1:C16:
    values:
      - [Cost centre, Month, Actual]
      - [OPS-NORTH, "2027-01", 84000]
      - [OPS-NORTH, "2027-02", 86500]
      - [OPS-NORTH, "2027-03", 83200]
      - [OPS-SOUTH, "2027-01", 61000]
      - [OPS-SOUTH, "2027-02", 59800]
      - [OPS-SOUTH, "2027-03", 64300]
      - [SALES-IT, "2027-01", 33500]
      - [SALES-IT, "2027-02", 35100]
      - [SALES-IT, "2027-03", 34800]
      - [SALES-EXPORT, "2027-01", 21900]
      - [SALES-EXPORT, "2027-02", 24600]
      - [SALES-EXPORT, "2027-03", 23100]
      - [ADMIN, "2027-01", 28700]
      - [ADMIN, "2027-02", 28900]
      - [ADMIN, "2027-03", 29400]
  Budget!A1:E6:
    values:
      - [Cost centre, "2027-01", "2027-02", "2027-03", Total]
      - [OPS-NORTH, 85000, 85000, 85000, 255000]
      - [OPS-SOUTH, 60000, 60000, 62000, 182000]
      - [SALES-IT, 34000, 34000, 34000, 102000]
      - [SALES-EXPORT, 22000, 23000, 23000, 68000]
      - [ADMIN, 29000, 29000, 29000, 87000]
  Headcount!A1:C16:
    values:
      - [Cost centre, Month, Headcount]
      - [OPS-NORTH, "2027-01", 42]
      - [OPS-NORTH, "2027-02", 43]
      - [OPS-NORTH, "2027-03", 41]
      - [OPS-SOUTH, "2027-01", 30]
      - [OPS-SOUTH, "2027-02", 30]
      - [OPS-SOUTH, "2027-03", 32]
      - [SALES-IT, "2027-01", 11]
      - [SALES-IT, "2027-02", 12]
      - [SALES-IT, "2027-03", 12]
      - [SALES-EXPORT, "2027-01", 7]
      - [SALES-EXPORT, "2027-02", 8]
      - [SALES-EXPORT, "2027-03", 8]
      - [ADMIN, "2027-01", 9]
      - [ADMIN, "2027-02", 9]
      - [ADMIN, "2027-03", 9]