Skip to main content

Pigment is well suited for many use cases, and in some of them, you will need to use the value that was previously calculated (some examples of this can be found in cash flow, balance sheet & inventory planning, and ARR waterfall use cases).

Using a previously calculated value will lead you to a “circular reference” issue that is well known in the modeling world and on the classic tools. Here’s how to manage it in Pigment.

 

 

The example: Inventory Planning

 

Here are the details to note for the following inventory planning example:

  • Beginning Inventory: previous month’s End Inventory
  • Incoming Re-order: replenishment that is arriving in the company’s warehouse this month
  • Outgoing Sales: Sales order leaving the warehouse
  • End Inventory: Beginning Inventory + Incoming Re-order - Outgoing Sales

In Pigment, the ‘Beginning Inventory’ Metric is calculated from the ‘End Inventory’ Metric which means writing the formula End Inventory = Beginning Inventory + Incoming - Outgoing will result in a circular reference.

 

 

Using previous to do recursive calculations

 

To calculate the ’Beginning Inventory’ Metric, we will use the SELECT modifier:

‘End Inventory’rselect: Month-1]

This will bring the value of End Inventory of the previous Month to Beginning Inventory.

However, if you try to type the End Inventory formula again (Beginning Inventory + Incoming Re-order - Outgoing Sales), it will still result in a circular reference!

 

In Pigment, only the function PREVIOUS() is recursive and will allow the calculation to work the way we want it to here:

Previous will return the value of the same Metric offset by a number (by default one).

The formula for ‘End Inventory’ Metric should be:

PREVIOUS(Month)+'Incoming Re-order'-'Outgoing Sales'

 

And there you go, you now have the calculation available in the example!

 

Hey Nathan,

Thanks a lot for your post it’s super useful!

I am facing a very similar case but I can’t figure out how to deal with it.

To apply to your example, I’d need to express “incoming re-order” as a function of the beginning inventory (eg. Incoming re-order = Beginning inventory * 10%). But if I do it I also have a circular reference.

Do you have an equivalent function of “PREVIOUS” that’d allow specifying the metric it refers to?

 

Update: I just found out on this post that it’s been added to the roadmap

Indeed. At the moment you can go around it by recalculating from the source

PREVIOUS(Month)+'Incoming Re-order'-'Outgoing Sales'   formula will work only if we have previous month of End Inventory otherwise, it will not work. If Beginning inventory starts from current month and first entry in that month with formula override toggle ON in metric setting then we will have to apply   PREVIOUS(Month)+('Incoming Re-order'-'Outgoing Sales')aSelect:Month-1] in beginning inventory Metric. and in End Metric formula will be  'Incoming Re-order'+’Beginning Inventory’-'Outgoing Sales'

PREVIOUS(Month)+'Incoming Re-order'-'Outgoing Sales'    formula will not work. The correct formula shall be   PREVIOUS(Month)+('Incoming Re-order'-'Outgoing Sales')eSelect:Month-1]

are you sure ? the goal is to calculate current month end of inventory

Reply


Ask your question here!

Terms & ConditionsCookie settings