axmo: "1.0"
name: Pricing and margins
description: >-
  Recipe — totals that are not sums. Volume, revenue and cost add up. Margin %, average price and revenue share
  are ratios: at every total they are recalculated from the totals (FORMULA), never added or averaged. Unit cost is
  an input averaged with volume as the weight (WEIGHTED_AVG).
dimensions:
  Products:
    type: categorical
    layers: [Top_Products, Range, Product]
    tree:
      All_Products:
        Pans:
          - Pan 24 cm
          - Pan 28 cm
        Pots:
          - Pot 5 L
          - Pot 8 L
  Time:
    type: sequential
    sequentialType: axmo_time
    parameters:
      selected_levels: [month, quarter]
      start: "2027-01"
      end_mode: end_point
      end: "2027-03"
series:
  Volume:
    type: input
    description: Units sold.
    dimensions: [Products, Time]
  Revenue:
    type: input
    description: Net revenue, EUR.
    dimensions: [Products, Time]
  Unit Cost:
    type: input
    description: Cost per unit, EUR. At totals, the volume-weighted average.
    dimensions:
      - Products:
          rollUp: WEIGHTED_AVG
          weightSeries: Volume
      - Time:
          rollUp: WEIGHTED_AVG
          weightSeries: Volume
  Cost:
    type: calculated
    description: Cost of the units sold, EUR.
    formula: Volume * Unit Cost
    dimensions: [Products, Time]
  Margin:
    type: calculated
    description: Revenue less cost, EUR.
    formula: Revenue - Cost
    dimensions: [Products, Time]
  Margin %:
    type: calculated
    description: Margin as a share of revenue, recalculated from the totals at every level.
    formula: Margin / Revenue
    dimensions:
      - Products:
          rollUp: FORMULA
      - Time:
          rollUp: FORMULA
  Average Price:
    type: calculated
    description: Revenue per unit, recalculated from the totals at every level.
    formula: Revenue / Volume
    dimensions:
      - Products:
          rollUp: FORMULA
      - Time:
          rollUp: FORMULA
  Revenue Share:
    type: calculated
    description: Share of all products' revenue in the same period. The total element supplies the denominator.
    formula: Revenue / Revenue[All_Products]
    dimensions:
      - Products:
          rollUp: FORMULA
      - Time:
          rollUp: FORMULA
dataSources:
  Sales:
    sourceType: excel-range
    rangeRef: Inputs!A1:E13
    hasHeaders: true
    shape: table
dataLoaders:
  Load sales:
    type: seriesLoader
    source: Sales
    loadMode: table
    dimensionMappings:
      Products: { column: Product }
      Time: { column: Month }
    targets:
      - { target: Volume, valueColumn: Volume, duplicateHandling: BLOCK }
      - { target: Revenue, valueColumn: Revenue, duplicateHandling: BLOCK }
      - { target: Unit Cost, valueColumn: Unit cost, duplicateHandling: BLOCK }
outputRanges:
  Products by month:
    cell: Report!A1
    rowDef:
      blocks:
        - dimension: Products/Product
        - dimension: Products/Range
        - dimension: Products/Top_Products
        - series: Volume
          format: "#,##0"
          newLine: true
        - series: Revenue
          format: "#,##0"
        - series: Unit Cost
          format: "0.00"
        - series: Margin
          format: "#,##0"
        - series: Margin %
          format: 0.0%
        - series: Average Price
          format: "0.00"
        - series: Revenue Share
          format: 0.0%
    colDef:
      blocks:
        - dimension: Time/Month
        - dimension: Time/Quarter
data:
  Inputs!A1:E13:
    values:
      - [Product, Month, Volume, Revenue, Unit cost]
      - [Pan 24 cm, "2027-01", 1000, 25000, 14]
      - [Pan 24 cm, "2027-02", 1200, 30000, 14]
      - [Pan 24 cm, "2027-03", 900, 22500, 15]
      - [Pan 28 cm, "2027-01", 600, 18000, 18]
      - [Pan 28 cm, "2027-02", 500, 15500, 18]
      - [Pan 28 cm, "2027-03", 700, 21000, 19]
      - [Pot 5 L, "2027-01", 300, 12000, 25]
      - [Pot 5 L, "2027-02", 350, 14000, 25]
      - [Pot 5 L, "2027-03", 400, 15600, 26]
      - [Pot 8 L, "2027-01", 200, 11000, 33]
      - [Pot 8 L, "2027-02", 150, 8400, 33]
      - [Pot 8 L, "2027-03", 250, 13500, 34]