Allocation Using RANKCUMULATE

jasnyder1973
jasnyder1973
in Best Practices

 

For a refresher on the RANKCUMULATE formula, click here.

Overview

The purpose of this article is to demonstrate how to use RANKCULUMATE to allocate a constrained supply or something (units, dollars, hours, space, etc.) when the demand is greater than supply. This approach allows you to build rules on how you allocate the constrained item.  

Some examples include:

  1. Filling a space with products based on sales performance.
  2. Allocating labor shifts based on cost considerations.
  3. Determining what products to produce based on product margin.

This is solution is provided as an alternative to using Optimizer. While Optimizer is a more robust/sophisticated tool, using the RANKCUMULATE you can provide a simple transparent approach that is in memory and easy to audit.

Before You Start

  1. Define the problem statement.
  2. Review the RANKCUMULATE Function.
  3. You will need to identify how you want to order the entities to allocate the constrained supply.

Key Considerations

  • Time and Versions - RANKCUMULATE does NOT rank across Time or Versions but within them.  
  • The RANKCUMULATE formula is a performance-heavy function, single-threaded, and has 50 million cell limit. However, this limit does not account for Summarized Values, Time, and Versions Lists.

For further details click here.

Allocation Example

Business Problem

You have 15 different products in jars (Jams, Jellies, Butter Spreads, etc.). Each item has a unit sales forecast and a margin forecast. In addition, to the per item forecast, each item has a requirement of shelf slots required to achieve that forecast. The problem you have is there is a finite amount of shelf slots and you need to determine what you want to put on the shelves.

Image 1 - Data in module PROP: ProductsImage 1 - Data in module PROP: Products

Module Overview for this Example

For this example, I set up three modules:

  1. INPUT: Constraints - Basic module to capture the high-level constraints. In this example, there was no list associated.
  2. PROP: Products - This captured production information in terms of Forecasted Sales Units, Average Selling Price (ASP), Margin %, and Slot Need (how many slots are needed to support the sales forecast)
  3. CALC: RANKCUM Alloc - This module executes the allocation.

Image 2 - Module Layout for ExampleImage 2 - Module Layout for Example

Setting up the CALC: RANKCUM Alloc Module

Set up a calculation module with the below elements: (see images below)

  1. Demand for the constrained item: 'Slot Need' (how much need there is for each product).
  2. Ranking: 'Rank' which is driven in this example by the 'Sales Units' and is descending to rank the highest value item first. The item you select to rank is the figure you are focused on optimizing for in your allocation algorithm.
  3. RANKCUMULATE Function: 'RankCum Sales' This is your running total of the demand for the constrained item based.
  4. Allocation formula: 'Alloc Amount Sales' This formula evaluates the running total in the RANKCUMULATE function to allocate until the constrained amount equals 0.

Image 3 - RANKCUMULATE CalcImage 3 - RANKCUMULATE Calc

Close up of the allocation formula

Image 4 - RANKCUMULATE allocation formulaImage 4 - RANKCUMULATE allocation formula

Evaluating the Results

For this simulation, I completed a few different rankings: by Sales Units, by Total Margin, and by Margin / Slot. The constraint I used was 20 available slots when 35 were demanded. Below are the results of the different Allocation Outputs. Based on the different ranking criteria, there are different allocations as a result of what metric was being optimized.

Image 5 - RANKCUMULATE allocation of Slots using Sales UnitsImage 5 - RANKCUMULATE allocation of Slots using Sales Units

Image 6 - RANKCUMULATE allocation of Slots using Total MarginImage 6 - RANKCUMULATE allocation of Slots using Total Margin

Image 7 - RANKCUMULATE allocation of Slots using Margin per SlotImage 7 - RANKCUMULATE allocation of Slots using Margin per Slot

Further Considerations

You can get creative with your ranking value to determine the order in which to allocate. In addition, you can execute an initial allocation to distribute some of the constrained items and then use the RANKCUMULATE to allocate the remainder. The initial allocation ensures that some entities get a baseline allocation ahead of allocating the balance in forced rank order.

Enjoy, and let me know how you have used this to solve problems!

Tagged:

Comments

  • PhilippErkinger
    PhilippErkinger

    Thanks for sharing this great summary. An even more advanced version would be to allocate via the time dimension. This allows usage of PREVIOUS() and NEXT() to "search" for demand that is still possible to allocate. However, it will be very VERY heavy on the performance! 😅

Categories