Navigate
Courses Tutorials Practice Pricing Tools Upgrade

Coherent forecasts finance can plan against

Today let's learn how to make a set of forecasts add up, so that finance has one set of numbers to plan against.

Maya is a planning analyst at an outdoor-gear retailer. The company sells in four regions: North, South, East and West. Maya has to prepare the 2024 monthly revenue plan, in dollars.

She fits one forecasting model to each region and one more to the national total. That makes five models, and each one forecasts revenue for 2024. The table below shows the annual plan from each of them.

Add up the four regional plans and you get $22,231,567. But the national plan says $21,932,408. So the same revenue for the same year is planned at two different totals, and they are $299,159 apart.

How four regions and a national total form a hierarchy

Let's start with the structure of Maya's data.

Revenue is recorded for each of the four regions, and the national revenue is the sum of the four in every month. A set of series where some are the sum of others is called a hierarchy. This one has two levels: the national total at the top and the four regions below it.

The rule that a total equals the sum of its parts is the aggregation constraint. Here it says that in every month, National = North + South + East + West.

A set of forecasts is coherent when it obeys the same rule: in every month, the four regional forecasts add up to the national forecast. So in a coherent plan, the regional lines and the national line can never disagree.

First we need the data. We simulate 72 months of revenue in dollars, from January 2019 to December 2024, for the four regions. Each region gets its own starting level, growth rate and seasonal peak month, and one demand shock is shared by all four regions.

RInteractive R
# Load the packages and simulate 72 months of revenue for four regions library(fable) library(fabletools) library(tsibble) library(dplyr) library(tidyr) library(ggplot2) options(width = 100) set.seed(3) month_index <- yearmonth("2019 Jan") + 0:71 month_no <- 1:72 # One demand shock is shared by all four regions; each region adds its own noise shock <- as.numeric(arima.sim(list(ar = 0.6), n = 72, sd = 0.03)) simulate_region <- function(level, growth, amp, phase) { trend <- 1 + growth * month_no / 12 season <- 1 + amp * sin(2 * pi * (month_no - phase) / 12) noise <- exp(shock + rnorm(72, 0, 0.02)) round(level * trend * season * noise, -2) } sales <- tibble( month = rep(month_index, 4), region = rep(c("North", "South", "East", "West"), each = 72), revenue = c( simulate_region(520000, 0.06, 0.16, 0), simulate_region(380000, 0.03, 0.10, 3), simulate_region(310000, 0.09, 0.10, 6), simulate_region(190000, 0.02, 0.05, 9) ) ) |> as_tsibble(key = region, index = month) head(sales) #> # A tsibble: 6 x 3 [1M] #> # Key: region [1] #> month region revenue #> <mth> <chr> <dbl> #> 1 2019 Jan East 294600 #> 2 2019 Feb East 278700 #> 3 2019 Mar East 279800 #> 4 2019 Apr East 283400 #> 5 2019 May East 283400 #> 6 2019 Jun East 323200

  

  





simulate_region() builds one region's revenue as its level, times a growth trend, times a seasonal wave, times noise. The shared shock goes into all four regions, so they tend to rise and fall together. The result is stored as a tsibble, a data frame with a time index (month) and a key (region) that says which series each row belongs to.



Next, aggregate_key(region, revenue = sum(revenue)) adds the national series. It creates one new series, shown as <aggregated>, that is the sum of the four regions in each month.




  

    
RInteractive R

    
# Add the national series with aggregate_key() and show the five series for December 2023
hier <- sales |>
  aggregate_key(region, revenue = sum(revenue))

hier |>
  filter(month == yearmonth("2023 Dec"))
#> # A tsibble: 5 x 3 [1M]
#> # Key:       region [5]
#>      month region       revenue
#>      <mth> <chr*>         <dbl>
#> 1 2023 Dec <aggregated> 1836400
#> 2 2023 Dec East          479600
#> 3 2023 Dec North         714500
#> 4 2023 Dec South         411000
#> 5 2023 Dec West          231300

    

      
      
    

    

  

  





There are now five series with 72 months each. In December 2023 the <aggregated> row is the national revenue, $1,836,400, and the four regions add up to exactly that.



Let's check that this holds in all 72 months. The block takes the largest absolute difference between the national series and the sum of the regions.




  

    
RInteractive R

    
# Check that the national series equals the sum of the four regions in every month
check <- hier |>
  as_tibble() |>
  mutate(level = if_else(is_aggregated(region), "national", "regions")) |>
  group_by(month, level) |>
  summarise(revenue = sum(revenue), .groups = "drop") |>
  pivot_wider(names_from = level, values_from = revenue)

max(abs(check$national - check$regions))
#> [1] 0

    

      
      
    

    

  

  





So the history obeys the aggregation constraint exactly. The forecasts, as we are about to see, do not have to.



Last, we split the time. The 60 months from 2019 to 2023 are the history the models are fitted on. The 12 months of 2024 are held back as the actuals, the revenue that was really recorded, and they are used only to score the plans once they are made.




  

    
RInteractive R

    
# Keep 2019 to 2023 as the history and hold 2024 back as the actuals
train <- hier |>
  filter(month <= yearmonth("2023 Dec"))
test <- hier |>
  filter(month >= yearmonth("2024 Jan"))

c(train_months = n_distinct(train$month), test_months = n_distinct(test$month))
#> train_months  test_months
#>           60           12