Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Sample of Dealing with Tiered Pricing

There are a number of approaches to working with prices that adjust by volume tiers.  This is just one example that outlines a solution and the things you should consider.

One general rule for all things, plan for the most common case and find a way to deal with exceptions vs build everything custom.  For example: if 80% of your customers have the same tiers and prices, build for that and add a discount option to deal with variances vs having different tiers and prices for every single customer/product combination.

This example will build at the most complex of the possible use cases:  prices and tiers are defined by every customer and product combination.

Step 1 - Select a sheet type

We need to decide what type of sheet makes the most sense to use for entering the tier and price data.

  • Usually this sheet will be setup as a User Assigned Sheet because it does not vary by level and only certain people setup the data
  • Choose the sheet type works best for your needs
    • Modeled sheet:  works best if the same tier and price data holds true for the entire version
    • Cube sheet:  works best of the tier breakpoints or the price can vary by time (can set time to be any stratum in your model)

For this example we will go with the cube sheet to give us the flexibility to change the tiers or prices throughout the year.

Step 2 - Build the tier/price sheet

This sheet will be an user assigned cube sheet, and we will need to create 2 accounts for every tier you expect.  We need the Tier#Max and Tier#Price, note that we do not need a minimum account as we will start at 1 and the min for each additional tier is just 1 + the prior Tier#Max.

For this example, we will assume that there is never more than 5 tiers for discounts, with most situations only needing 3.  This example also supports the tiers to vary by Customer and Product so we will need to include those dimensions on the sheet.

The accounts we create will all be setup as type Cumulative so that we enter the max or the price and all following periods will be the same.  We only need to revisit in the future if something changes. The accounts are:

clipboard_e94207a3846ae8e1fe46a651fdedfdd9e.png

The following video walks through the setup of this sheet.  Note that there is one important account setting not shown in the video.  All accounts must be set to “Value of account is public at all levels”, you will find that setting on the account under Data Privacy.

Step 3 - Enter your tier/price data

The user assigned to the sheet can now either upload data to the sheet or enter the values by Customer and Product.

For this example, we end up with variations of the screenshot below for every valid Customer/Product combination.  We also don’t have to use all 5 tiers, just set the max on top tier to a number that will never be exceeded to make it the final tier.  Additionally this example does not validate the data entered, you could extend on this example by adding check accounts that validate if each tier max is larger than the prior max.

Because we used Cumulative type accounts, when you set or change a price it will auto populate into the future.

Step 4 - Using the tier/price data in your volume entry cube sheet

There are a number of ways you could leverage the tier/price data to calculate revenue in your sheet.  A couple common options are:

  • The volume determines what price to use and Revenue = Volume * Selected Price
    • To simplify this option, we recommend using an account to pull in the Price so that you can verify it, then Revenue is your Volume times the Price account
  • The revenue builds up by tier, so the first X number of items are at the Tier 1 price, the next Y items are at the Tier 2 price, etc.
    • For this option it is recommended that you use an account for each tier that sum up to Revenue.  This will help with debugging and avoid hitting a 4000 character limit in formulas

For this example, we will model out both of these scenarios.

Option 1 Formulas:
Tier Price

iff(
# first check to see if any volume data has been entered, if not there is nothing to do
isblank(ACCT.Sales_Volume.Volume), 0,

 # if there is sales volume return the price for the tier it falls into
switch(ACCT.Sales_Volume.Volume,

#Tier 1
range(1,ACCT.TierPriceList.Teir1Max[level=Total Company, Customer=this, Product=this]), ACCT.TierPriceList.Teir1Price[level=Total Company, Customer=this, Product=this],

#Tier 2
range(ACCT.TierPriceList.Teir1Max[level=Total Company, Customer=this, Product=this]+1,ACCT.TierPriceList.Teir2Max[level=Total Company, Customer=this, Product=this]), ACCT.TierPriceList.Teir2Price[level=Total Company, Customer=this, Product=this],

#Tier 3
range(ACCT.TierPriceList.Teir2Max[level=Total Company, Customer=this, Product=this]+1, ACCT.TierPriceList.Teir3Max[level=Total Company, Customer=this, Product=this]), ACCT.TierPriceList.Teir3Price[level=Total Company, Customer=this, Product=this],

#Tier 4
range(ACCT.TierPriceList.Teir3Max[level=Total Company, Customer=this, Product=this]+1, ACCT.TierPriceList.Teir4Max[level=Total Company, Customer=this, Product=this]), ACCT.TierPriceList.Teir4Price[level=Total Company, Customer=this, Product=this],

#Tier 5
ACCT.TierPriceList.Teir5Price[level=Total Company, Customer=this, Product=this]

   )
)

Rev Sample 1

iff(
isblank(ACCT.Sales_Volume.Volume),0,
ACCT.Sales_Volume.Volume * ACCT.Sales_Volume.Tier_Price
)

Option 2 Formulas:
Tier 1 Rev

iff(
isblank(ACCT.Sales_Volume.Volume),0,
least (ACCT.Sales_Volume.Volume, ACCT.TierPriceList.Teir1Max[level=Total Company, Customer=this, Product=this]) * ACCT.TierPriceList.Teir1Price[level=Total Company, Customer=this, Product=this]
)

Tier 2 Rev

iff(
isblank(ACCT.Sales_Volume.Volume) or ACCT.Sales_Volume.Volume <= ACCT.TierPriceList.Teir1Max[level=Total Company, Customer=this, Product=this],0,

(least(ACCT.Sales_Volume.Volume, ACCT.TierPriceList.Teir2Max[level=Total Company, Customer=this, Product=this]) - ACCT.TierPriceList.Teir1Max[level=Total Company, Customer=this, Product=this]) * ACCT.TierPriceList.Teir2Price[level=Total Company, Customer=this, Product=this]
)

Tier 3 Rev

iff(
isblank(ACCT.Sales_Volume.Volume) or ACCT.Sales_Volume.Volume <= ACCT.TierPriceList.Teir2Max[level=Total Company, Customer=this, Product=this],0,

(least(ACCT.Sales_Volume.Volume, ACCT.TierPriceList.Teir3Max[level=Total Company, Customer=this, Product=this]) - ACCT.TierPriceList.Teir2Max[level=Total Company, Customer=this, Product=this]) * ACCT.TierPriceList.Teir3Price[level=Total Company, Customer=this, Product=this]
)

Tier 4 Rev

iff(
isblank(ACCT.Sales_Volume.Volume) or ACCT.Sales_Volume.Volume <= ACCT.TierPriceList.Teir3Max[level=Total Company, Customer=this, Product=this],0,

(least(ACCT.Sales_Volume.Volume, ACCT.TierPriceList.Teir4Max[level=Total Company, Customer=this, Product=this]) - ACCT.TierPriceList.Teir3Max[level=Total Company, Customer=this, Product=this]) * ACCT.TierPriceList.Teir4Price[level=Total Company, Customer=this, Product=this]
)

Tier 5 Rev

iff(
isblank(ACCT.Sales_Volume.Volume) or ACCT.Sales_Volume.Volume <= ACCT.TierPriceList.Teir4Max[level=Total Company, Customer=this, Product=this],0,

(least(ACCT.Sales_Volume.Volume, ACCT.TierPriceList.Teir5Max[level=Total Company, Customer=this, Product=this]) - ACCT.TierPriceList.Teir4Max[level=Total Company, Customer=this, Product=this]) * ACCT.TierPriceList.Teir5Price[level=Total Company, Customer=this, Product=this]
)