A few days ago I told you we’d started on the development of storage fees. I also reported that as usual, Amazon won’t make it easy to get to the metrics YOU want (profitability by SKU), as they are only interested in the metrics THEY want (how much to charge you).
Here’s how I explained the challenge back then:
– The storage fee reports come with an ASIN and an FNSKU alongside the storage charges for one month. So, we can certainly provide a breakdown per ASIN, but when it comes to assigning the cost to a product for product profitability calculations, we need to assign it to an SKU rather than an ASIN.
– We can certainly convert the FNSKU to an SKU, that is not a problem. However, in the cases where there is no FNSKU on the report, we are stuck.
– Cases with no FNSKU include comingled inventory and inventory where the UPC or EAN numbers are used instead.
After much head-scratching, here is how we have specified our way out of this mess:
If the Storage Fees report shows a row with an FNSKU present and the FNSKU maps to an SKU, assign the storage fee in the report to its deduced SKU (stemming from FNSKU).
If the Storage Fees report shows a row with FNSKU not present, or FNSKU does not map to an SKU:
– Accumulate estimated-monthly-storage-fee for that report row to an ‘unallocated cost’ bucket
– When all FC/ASINs/FNSKU in the report have been apportioned to their respective SKU, if there is any amount left in the unallocated cost bucket, apportion that amount to the remaining SKUs of the ASIN which have not yet had a fee charged, based on their current inventory on hand
Example
ASIN B0101 has 6 SKUs
– SKU0101
– SKU0102
– SKU0103
– SKU0104
– SKU0105
– SKU0106
Storage Fees Report shows 4 rows for ASIN B0101, two rows which can be directly attributed to an SKU and two rows which cannot
– ASIN B0101 -> FNSKU X0101 ->SKU S0101 cost 5.00 Avg Untis 30
– ASIN B0101 -> FNSKU X0102 ->SKU S0102 cost 3.00 Avg Units 70
– ASIN B0101 -> FNSKU X0101 ->SKU NULL cost 2.00 Avg Units 50
– ASIN B0101 -> FNSKU NULL cost 3.00 Avg Units 80
Processing of the Storage Fees reports rows results in:
a) Calculated total storage fees for ASIN B0101
— $5.00 + $3.00 + $2.00 + $3.00 = $13.00
b) Fees directly attributable to SKUs from the report:
– – SKU 0101 Cost $5.00 (From report)
– – SKU 0102 Cost $3.00 (From report)
c) Fees which are not directly attributable to SKUs from the report (Unused cost bucket) :
— Total $13.00 – ($5.00 + $3.00) = $5.00
d) Unallocated SKUs for ASIN B0101 which have received no cost yet
— SKU0103 current inventory 150
— SKU0104 current inventory 0
— SKU0105 current inventory 60
— SKU0106 current inventory 90
e) Apportionment of Unallocated Bucket Cost to Unallocated SKUs as follows:
— Total units in stock for unallocated SKUs = 150 + 30 + 60 + 90 = 300
— SKU0103 cost = 150 / 300 * $5.00 = $2.50
— SKU0104 cost = 0 / 300 * $5.00 = $0
— SKU0105 cost = 60 / 300 * $5.00 = $1.00
— SKU0106 cost = 90 / 300 * $500 = $1.5
I am sure you cannot wait to spend what is left of your Christmas day to tackle this issue, so I’ll come back and read your comments in an hour or so.
Just kidding. (but we’d be very happy to hear what you think, though)