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

Dear Sir,

Thanks for your reply to my post.As already I have said that the credit column i.e F column amount can match also or may not also with respect to E column becoz in case of shortfall,next value of E column is also taken and corresponding date is also taken.From this days is taken out by the difference of Sales bill date & that payment date whose sales bill match.After this days is calculated inorder to check where this falls within discount criteria or not.If so further calculation is done.Here the Reference No.is unique as well as payment received either thru Bank or Cash is also unique.There is no duplication for it neither Bank row nor cash row reference no.will match to Sales(Debit column i.e E column).D column represent Reference no. for Debit & Credit.Regarding allocation,how you do it depends upon how you try to solve .As already you have seen manual calculation which is not possible in case of large data.I want just to automate.You can apply any sort of method to get the target result which has been done in working at Right hand side of 'Question' sheet.

The formula you suggested is not appropriate.

My ultimate aim is to calculate Total discount amount based on the criteria stated in next worksheet.

With Best Rgds,
Suresh

Dear Boopathirahaji

No sir, that is not the solution to my problem, simply I was given a ledger of a party( which is exported from tally) with criteria given.I was asked to calculate the Total discount amount that respective party is entitled with credit terms given i.e 15% on 15days payment received,10% on 30 days payment received and 0% when payment is received after 30 days.Manual working already I have shown there at Right Hand Side of 1st worksheet.

I have almost more than 400 parties for which I have to calculate the total discount amount based on credit terms of discount.
Amount is received might be bill to bill, advance,against the bill, on account,etc which perhaps need allocation.How you analyse would depends upon you.

This is the practical issue and strategy of any organisation for speeding up the collection of payment against sales bill drawn.

Hope you would solve the problem.

With Best Rgds,
Suresh

Dear Sir,

Your can match on FIFO Basis of Debit column i.e to say that First In First Out of Debit column.For speeding up you can alter /change the table accordingly by any formula in order to arrive desired result.

Hope you have understood my problem.

With Best Rgds,
Suresh

Pl see the attached file with macro.

Pl attach a dummy file with all details and required results like your original file.In the attached file days and % included.

Dear Sir/Madam,

Thanks for your prompt reply to my post.

Now for the case of Opening balance which I did not included earlier is due to not expecting that it would be solved,hence partial ledger was given excluding opening balance either debit or credit.Now as enclosed in attachment in a single workbook,I have stated 4 different cases of Debit Opening balance & 4 different cases of Credit Opening Balance.Now you are requested to take into account all those situations of all sheets.Also, this is a sample one whose range might differ in actual situation as stated here is from row 3 to row 15 so it can vary from it.

You are further requested to see my assumption sheet as enclosed in my dummy work book as you said.If you require further information then do let me know.The output in general is taken out from Tally Software which is well famous in India and exported ledger is being analysed by me for the above cases.

Hoping a positive response as usual.

With Best Rgds,
Suresh

Pl See the file.Open each sheet and run macro each time.Opening balance is taken into account.It is always better to give all information at the begining itself, so that you can save time and work.

With regards

KVS murthy

Dear Sir/Madam,

Inconvenience caused to you is extremely regretted.I was not expecting that it might get solved,hence I ignored the first part of Opening balance.

With Best Rgds,
Suresh

Is now all your problems solved. IF so pl mark the thread SOLVED.