How to transform order volume to invoiced volume?

marcowohlgemuth_*33165
marcowohlgemuth_*33165
in Modeling

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
    marcowohlgemuth2_7-1630674116911.png

     

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.
    marcowohlgemuth2_8-1630674158702.png

     

  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
    marcowohlgemuth2_9-1630674179818.png

     

 

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.

Tagged:

Best Answer

  • marcowohlgemuth_*33165
    marcowohlgemuth_*33165
    Answer ✓

    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...

Comments

  • RobBird82
    RobBird82

    Dear @marcowohlgemuth 

     

    Apologies if I send you down the wrong track, I'm sure there are some Master builders / more experienced model builders floating about with better suggestions.

    I was a little confused by your message, so if I have missed the mark, please feel free to disregard. But would you be looking to build a set of line items to bring you from the sales volume of a given date to the processed volume by the warehouse?
    Similar to the training in Level 2 this works to my mind as a series of line items with time running as the other dimension and any location dimensions (as required).

    Line Items:
    1. Sales Volume - Number of sales, entered by yourself or sales staff etc.
    Weekday Boolean - Set to show only weekdays
    2. Offset line (label as required) - 'If' equation where Boolean is false, offsetting to next working day (sorry wasn't sure if the warehouse was open on a Saturday or not).
    3. Total Sales Volume Pending - Sales Volume + Offset volume due to weekend + Offset value of Outstanding Orders incomplete. Care would have to be taken not to create circular referencing here. You may feel that you want surplus unprocessed orders to filter through differently. Aim is to bring in the weekend unprocessed orders and any orders from workdays that have not been completed. You could amend this as required.

    4. Percentage Processed in Warehouse - Seemed to be variable although you mentioned a pattern. May therefore be worth setting up a separate module to determine percentages on any given date / day.

    5. Total Processed Orders - Sales volume v percentage processed by warehouse

    6. Outstanding Orders incomplete - Total Sales - Total Processed Orders, I assume that these role into the workflow for the warehouse for the next day?

     

    Changing the format of the line items would help show key information such as Total Sales Volumes Pending and Total Orders Processed. These line items could then be referenced in secondary modules for further manipulation.
    This was just the process that occurred to me upon reading your explanation, apologies if its not what is required.

     

    Kind Regards

    Rob

  • edward94
    edward94

    Hello,

     

    Thanks for the update and quick reply. I'll be sure to keep an eye on this thread. Looking for the same issue. Bumped into your thread. Thanks for creating it. Looking forward for solution.

  • marcowohlgemuth_*33165
    marcowohlgemuth_*33165

    Hi Rob

     

    Thanks a lot for your quick response and apologies if I didn't describe the situation clearly enough. Let's see if I can be more specific this time. 

     

    If I understand you correctly, then you suggest I first move ordered volumes to the next workday. Then I use the distribution module to spread that volume over the next X days. One thing I then struggle with is that when using e.g. the Profile() function to allocate the volume, I will allocate volume ordered on a Friday also on a Sunday (t+2), even if it's not a working day. A possible way around that might be to create a distribution for every date of the calendar, which would then be used by the Profile() function.

     

    When using a Backlog calculation (as you suggest in (3)), I have a different issue: Backlog and Capacity is a top level number. When I look at sales on product level I don't know yet which product is being processed when (sequencing of the order backlog). So let's say my daily order intake and processing capacity is 100. On a specific date I have a backlog of 200. I don't know how the 200 are composed. That will be an issue when I want to calculate the revenue from it, because different products have very different price points.

     

    I'll spend some more time trying to work out a solution. If I find something, I'll post it here.

Categories