Forward Weeks of Cover or Forward Weeks of Supply

JaredDolich
JaredDolich
in Modeling

Thought I would post this here as a method to calculate Forward Cover or Forward Weeks of Supply.

A year ago @AnaplanIdeas posted an idea for this which is great. I'd like to expand on this idea and also incorporate @nathan_rudman backward-looking suggestion.

 

Summary:

In the supply chain horizontal you may be asked to calculate a forward cover in Anaplan. Here's a suggestion from @nathan_rudman  based on a question posed by @mikhivin about Accounts Receivable coverage which looks backward. See this post on count back for turnover. The example for forward-looking uses ending inventory as the basis and asks the question, "how many days or weeks will it take to sell thru the inventory?"

 

Forward Cover or Forward Weeks of Supply

Unfortunately, a forward cover function doesn't yet exist in Anaplan. But, @nathan_rudman devised a process that will simulate a backward looking function and requires a little creativity. The steps to accomplish a forward-looking solution is shown in this modified approach. The steps to accomplish this are as follows:

  1. Determine how many months it will take to exhaust the inventory.
  2. Calculate the Forward Cover
  • Use the MOVINGSUM function to add up the sales forecasts and the number of days
  • Obtain the statistics in the last month and bring them forward to the same month as the ending inventory being evaluated
  • Determine the sales percentage over and use that ratio against the number of days
  • Calculate the forward cover

Results

Here is what the final output will look like.

FWOS002.png

 

Step 1: Determine the number of months to exhaust the inventory

This is the hardest part of the process but can be accomplished rather quickly with two line items and one very small list. 

 

Create a list containing the number of months you want to look forward. In this example, I have chosen 12 months as a maximum forward look. We will need this list because every month will have to be evaluated to determine the one that finally exhausts the inventory. Make sure to add a top value, this will be needed to find the one month we need. Mine is called "all possibilities".

FWOS001.png

 

Next, create a calculation module using this list and the time dimension. I called mine "Calc001 How Many Months". In this example, I am using months but it works the same with weeks. You'll just need to modify your list to be weeks forward instead of months. Add two line items that reference the sales and inventory output module which I called "OUT001 Forward Cover". Months needed simply tests every month to determine if we've exceeded the ending inventory. If we have then record the month number. If not, then use the ending inventory so we exclude that month when we summarize this line item. The Months needed should have a "Min' summary. The Min summary will take the lowest number, so using ending inventory will ensure we don't include it. If ending inventory is zero, then our forward cover will be zero. 

  • Moving Sum Sales | MOVINGSUM('OUT001 Forward Cover'.Sales, 1, VALUE(NAME(ITEM(Month Count Fwd Cover))))
  • Months Needed | IF Moving Sum Sales > 'OUT001 Forward Cover'.Ending Inventory THEN VALUE(NAME(ITEM(Month Count Fwd Cover))) ELSE 'OUT001 Forward Cover'.Ending Inventory

Notice how the summary contains the correct number of months needed to exhaust the inventory.

FWOS003.png

 

Step 2: Calculate the Forward Cover

The last step is pretty straight-forward since we now know how many months it will take. Create the following line items to your output module.

Some of these line items can be combined but I broke them up to illustrate the process which supports the PLANS methodology.

And, finally you can see the results from the top of this article.

FWOS004.png

 

Conclusions:

I'll repeat a lot of what @nathan_rudman had to say because there are some pros/cons of this method worth mentioning again.

  • Limiting the number of months or the other dimensions like product and location will significantly help with performance since this is a rather intensive calculation testing every possible combination.
  • Limit how far you will look forward. You can even start at month 2 or 3 if you think anything less than 8 weeks of supply is not worth analyzing.
  • Utilize system modules to filter your data (see DISCO)
  • Finally, impress your supply chain colleagues with this amazing statistic that can be created in Anaplan!

Again, I want to thank @nathan_rudman   for sharing his wisdom and creativity in solving the backward-looking solution. It made creating this article much easier.

Tagged:

Best Answer

  • bradmalis
    bradmalis
    Answer ✓

    i have this exact use case at a customer and put this logic in the model.  works great.  thanks so much for this article.  it was really helpful.  the only suggestion i have is that i have some product intersections where ending inventory is 0, which messes up aggregate levels.  so i built a line item, put in a constant (above the highest number in the list), and used that as the ELSE in the months needed formula instead of ending inventory.  I also checked for ending inventory of 0 in the days of supply formula in the main output module.  

Answers

Categories