SEPARATING TOTAL INVENTORY # INTO BATCHES - $20 GC as appreciation for best solution :)

Incredible wizards of Excel, please help me improve my business.

I have hundreds of products that I normally sell as individual units. However they come in multi unit cases, and sometimes even inner packs. I am trying to find a formula/solution to easily be able to split my total inventory of each unit into:
- Single Unit
- Inner Pack qty
- Master Case qty

Example:
SKU10001 has a total on hand qty of 600. It comes 60 per case, with 5 inner packs of 12. So I would like to sell it as:
10001 - SINGLE UNIT
10001-12 - 12 UNITS
10001-60 - 60 UNITS

(the reason I add -12 and -60 is so my warehouse guys know how many to pull just by looking at the SKU)

Is there a formula or any solution that would automatically split the total volume of 600 into evan batches of each SKU volume? For example:
- 120 - singles
- 15 - 12 packs (180 units)
- 5 - 60 packs (300 units)

So each morning I download/upload a spreadsheet of total qty on hand, it continues to divide available inventory so there is enough for each SKU qty variation?

P.S. As a token of my appreciation I will give a $20 gift certificate to my website to person that comes up with the best (working) solution.

Gotcha,
Attached is an example of a small batch of our products. As perfect example, let's use SKU number 18024 (Grill lighters) that I highlighted in the attached file. It comes 120 per case and 4 inner packs of 30. So I could sell it as:
- 18024
- 18024-30
- 18024-120

And I just wanna stress what I am looking for in the solution as I may have not made it clear.
Everyday after we process all orders, we upload a file that has the current qty on hand (Yesterday's qty minus what sold). It is easy when we only sell products as one qty option. However we were always scared to add different qty options for buyers as we didn't know how to manage dividing the master inventory into each qty variation.

Does that make sense...?

Guys... I think I actually figured it out on my own.
- First I duplicated all SKUs to create a full case variant for each SKU.
- I inserted a column After original SKU, assigned a new SKU number by using the old sku number & dash symbol & the qty per case
- I sorted by Original SKU, then by SKU variant.
- Then I took the original SKU qty and by a formula divided by qty per given SKU, so I ended up with (example: 20 total = 20 singles and/or 2 cases of 10).

(I attached a new adjusted spreadsheet)

Only problem of this solution is that we upload qty on hand once a day, in the morning. So if there is low qty on hand and one person buys all available cases and another buys a few singles, we will have to tell one of them that we don't have enough on hand afterall. Which looks unprofessional. Any ideas to prevent that and make the system work better?