Help creating inventory volume report.
I've been working with Zoho support for a few months now trying to develop a inventory volume report. Though they have been helpful, it never seems to generate exactly what we're looking for. I thought I'd turn to the community for further help. We've been approaching this via Zoho Inventory and Zoho Analytics to generate the actual report.
In the most basic form we're looking to generate the average volume in our warehouse by client on a monthly basis to determine storage billing.
——————
Monthly storage volume billing calculation
1cu ft: 12in x 12in x 12in = 1728
Volume: (Package H x W x D) / 1cu ft
Avg Monthly Volume: Volume * Average Daily Qty/Stock
Pallet space: 52.8 cu ft
Storage Cost: =CEILING(Avg Monthly Volume/pallet space) * $30
——————
How this would translate to our use case
| Client | SKU | Item Name | Package Length | Package Width | Package Height | Volume (cubic ft) | Avg Monthly Volume | Monthly Storage Charge |
| Client A | 2320700000 | Item 1 | 28.5 | 19 | 19 | 6 | 68.12903226 |
|
| Client A | 2220700002 | Item 2 | 6 | 5 | 3.81 | 0.1 | 0.1129032258 |
|
| Client A | 2120700000 | Item 3 | 9.75 | 6.75 | 15.75 | 0.6 | 1.296774194 |
|
|
|
|
|
|
|
| 69.53870968 | $60 |
|
|
|
|
|
|
|
|
|
| Client B | 2222500001 | Item 1 | 33.5 | 47.94 | 23 | 21.4 | 341.7096774 |
|
| Client B | 2222500002 | Item 2 | 19.38 | 47.94 | 20 | 10.8 | 417.0193548 |
|
| Client B | 2222500000 | Item 3 | 20 | 33 | 13.75 | 5.3 | 114.2064516 |
|
| Client B | 2222500003 | Item 4 | 22 | 9.5 | 0.1 | 0.1 | 0.7903225806 |
|
|
|
|
|
|
|
| 873.7258065 | $510 |
The above formula is pretty straight forward but what doesn't seem possible is capturing/accessing the stock on hand for each day in a given time frame (as we plan to use a date range filter in our report)
Again, basic math/formula, but how we would determine the average stock for an entire month