How to transform order volume to invoiced volume?
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:
- 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.
- 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:
- 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.
- 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.