Hello,
I am trying to estimate margin (using Current Cost of Sales accounting logic) based on my proceeds and cost tables, using always the latest best cost info available at the given point in time. I was looking for solutions, but they either give me always the latest cost available (no ok for historical calc.), or I am warned against (EARLIER: lots of computing power if used on big tables; FILTER: used on Data tables instead of reference tables). I operate this currently in Excel (per month, maxifs), and it is very slow, so I am looking for optimum performance, grateful for advice.
Deliveries table. Costs, per day, per site, per product, per depot. Obviously lots of deliveries, so usually I would have a delivery per day per depot. (Meaning: if I know the depot that was used last to supply my site, I can pick up the freshest cost info for that depot.)
- I have day/site/product combinations when there is no delivery (magenta in example)
- I have combinations when there are multiple deliveries – two depots (green in example)
- I have combinations where a site is changing over time from Depot1 to Depot2 (red in example)
- cca ~30.000 delivery combinations/month (this includes depot layer as well)
Sales table. Proceeds and quantities sold, per day, per site, per product.
For each combination (~30-40k rows per month), need to know freshest cost info until any given day to enable margin calculation.
1. last delivery to the site, for the given product, until the given day
2. the depot used for this delivery (largest delivery in case more deliveries per day) - "which depot supplied the site last?"
3. the freshest cost information available for the given depot, for (or closest up to) the given day
I have reference tables for site, day, products, depots. The two data tables (sales and deliveries) are both linked to my reference tables.
Table examples below, with results expected.
Thank you!
-----------------------------------
Delivery table example: (also used to calculate unit cost)
Posting date;SiteCode;Depot;Product code;Quantity;Total Cost
2021.03.01;11111111;BR1008;123456789;1 040;-31 006
2021.03.05;11111111;BR1007;123456789;5 870;-175 797
2021.03.05;11111111;BR1008;123456789;10 690;-320 149; two deliveries on the same day/site/product
2021.03.03;11111111;BR1008;345678901;5 920;-177 265
2021.03.06;11111111;BR1008;345678901;11 050;-330 875; no deliveries (but sales yes!) for the 4th
2021.03.08;11111111;BR1008;345678901;4 810;-131 711
2021.03.02;22222222;BR1000;123456789;3 590;-100 985
2021.03.01;22222222;BR1000;345678901;6 790;-174 270
2021.03.03;22222222;BR3098;345678901;2 300;-60 648
2021.03.05;22222222;BR3220;345678901;4 710;-125 591; depots changing over time
Sales table example, including results expected
Green: pick depot with the biggest quantity
Magenta: use depot that last sent this product here
Red: depot changes over time
Product code;Selling date;Site;Proceeds;Quantity;RESULT: last biggest delivery to site;RESULT: Depot used for last deliv.;RESULT: freshest cost info to date
123456789;01.03.2021;11111111;21 556;440;1040;BR1008;-29,81
123456789;04.03.2021;11111111;116 596;2380;1040;BR1008;-29,81
123456789;05.03.2021;11111111;78 874;1610;10690;BR1008;-29,94
345678901;04.03.2021;11111111;14 697;300;5920;BR1008;-29,94
123456789;02.03.2021;22222222;263 724;5530;3590;BR1000;-28,1
123456789;07.03.2021;22222222;96 821;2030;3590;BR1000;-28,1
345678901;01.03.2021;22222222;344 551;7150;6790;BR1000;-25,6
345678901;04.03.2021;22222222;216 339;4480;2300;BR3098;-26,3
345678901;05.03.2021;22222222;201 852;4180;4710;BR3220;-26,6
