Finance Question- matching sales value date with that of amount received date

Dear All Forum Contributors,

This is actually the Finance Question.As enclosed in the spreadsheet link, In first sheet,A company has a party ledger with all necessary information regarding sales and payment received with datewise,Reference no.wise.Party does the payment in the following manner-Bill to Bill,Advance,Partial payment & so on.Company wants to know what is the total cash discount that the party is likely to get based on company policy for cash discount. A company has following policy for cash discount which is in 2nd sheet :

Cash Discount Rate applicable
1 If payment is received within 15 days from Bill Date 15% of Sales Bill Amount
2 If payment is received within 30 days from Bill Date 10% of Sales Bill Amount
3 If payment is received more than 30 days from Bill Date 0% of Sales Bill Amount


https://docs.google.com/spreadsheet/...kE&usp=sharing

With Best Rgds,
suresh