How to transform order volume to invoiced volume?

marcowohlgemuth

Hi There

 

For a sales forecast of an onlineshop I have the following problem to solve: I know the daily volume forecast (in a number of dimensions) of what customers order from the shop. From that daily order volume I want to derive the daily processed volume (by the warehouse). When the warehouse complete processing orders, invoicing is triggered, which is relevant for revenue recognition in our Financial Businessplan. There warehouse processes orders in a specific logic:

 

1. The warehouse only operates on work days. On weekends and special holidays (e.g. independence day) it's closed. I have a boolean for each day (and warehouse location) that is TRUE when it's a work day for the warehouse.
2. I assume that orders of a specific date are processed according to a typical schedule, depending on the weekday of the order. 
   See below: e.g.  If Weekday is Saturday, then on the first workday (Saturday) we process 40% of the volume ordered on that Saturday. If it's Sunday, then on the first workday (Monday) we process 75% of the volume ordered on the Sunday
   

    

I have not yet found a simple way how to model the daily volume processed (*). I did research a number of community pages (e.g. this one), but haven't really found a solution to my problem. Has someone faced a similar problem? How did you solve it? Thanks!!

 

(*) I found a rather cumbersome and extremely space-consuming way of dealing with it. It's the following:

1. Create a new list called "Workdate" that has as list members all the dates of the daily time range. I created a module where I map the date to each one of the list items. Then I create a module where I use time (daily) and "Workdate" as dimensions. In that module I create a workday counter (cumulative) that adds 1 for every workday in the list "Workdate" starting with 1 on the order date.
   

    

2. Then I map the percentage of that day's phasing (see the first screenshot above) to the corresponding workday counter in (see the screenshot above) and multiply that with the order volume
   

    

 

The reason why this only works in theory is that my order volume already has a number of dimensions that make it quite a big module. Adding daily timescale AND workday as dimensions for 2 years increases the module size by (365 x 2)^2 = 532’900 times! If my Forecast module already has 10 mio cells, this produces over 5 trillion cells. This is too space-consuming.

marcowohlgemuth

to close off the topic - I have found an efficient way to get the desired result: 

 

I created a new list counting from 0 to 10, called "DayDiff Order to Invoice".

Then, I run a Python script that generates a matrix with the dates from Jan 1st till dec 31st on one axis and the list dimension "DayDiff ..." on the other axis. I define a distribution vector for every weekday (1-7). The Python script then checks if the date is a holiday or a sunday. If not, it allocates the pre-set values from the vector to the (0-10) days after the order was placed (sum adds up to 100%). Whenever there is a holiday or sunday, the value for that cell is 0% and the next day will be a cumulation of that day + the balance of the days where the share was zero.  

 

In a systems module with daily time scale and the "DayDiff ..." dimension I import this data from the Python script. 

 

Then, I use the PROFILE() function to phase orders over the next 0-10 days. 

 

Magic...