I am hoping to compute discount using last price of the previous quarter as the beginning price. I have attached an excel file which shows the formula of the concept.
Basically, my formula would be:
Discount = Trxn ASP - Beg. ASP
Trxn ASP - is the ASP of a particular transaction
ASP = Revenue / Qty
Beg. ASP - is the last price recorded from the previous quarter. If no transaction from the previous quarter, ASP to use is the price of the "first" transaction of the current quarter
I believe this will be easy using a function similar to vlookup of MS Excel... Your thoughts on how to resolve this is greatly appreciated. Thanks