Coding an inventory spreadsheet when the same item has multiple prices

How do I code the cost of an item when it has multiple purchase prices ie a wholesale price, a preferred member price and a "free' price based on a reward system. The same item will have a retail sale price, a 'special' sale price, and a 'free' promotion price.

I want to be able to:

• record how the item was purchased and sold.
• have the inventory quantity on hand reflect the transaction.
• have the transaction reflected in my stock cost

Thanks to anyone who can help on this! I'm just learning excel so please be gentle and detailed in your answer. Thank you. I'm using microsoft excel for Mac version 16.57