For a certain item, I have a specific inventory quantity of $333$ items. I have a sales rule of buying $4$ and get 1 free. Given the stock amount, how can I calculate how many sellable items I have?
I did the calculation manually with trial and error and found out that I can sell $264$ items and give $66$ items for free which would add to $330$ hence I will have 3 items in hand.
I am looking for a formula that I can apply to excel and retrieve all the above numbers individually. E.g. Total Sellable: $264$ Total Free: $66$ Stock Left: $3$.
I have many more inventory items with different sales rules e.g. buy 10 get 3 free hence cannot do it all manually and I could not find a way to calculate this in a formula.
Thanks in advance!
In each sale, you give customers $n$ items although they pay only for $m<n$ of them. If the stock is $s$, you can make $\lfloor\frac{s}{n}\rfloor$ such sales. The total sellable items are $\lfloor\frac{s}{n}\rfloor m$, total free $\lfloor\frac{s}{n}\rfloor (n-m)$ and the stock left $s-\lfloor\frac{s}{n}\rfloor n$. If you are using Excel, you can implement the floor function using
Int().