• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Payment due date wise based on payment terms

N

neeraj2050

Member
Hi,

I have attached the file, wherein, I want to reflect payment based on payment terms

Please refer the attached file

Regards
Neeraj Kumar Agarwal
 

Attachments

I have looked at your file and have no idea what you hope to achieve. Suggest you explain in the forum thread exactly what you want. Step by step if you were to do this manually. We are here to help, but we need to understand you issues. All details of each loan needs to be explained as it is not obvious as shown.
 
Thanks for your reply!!

With same vendor I procure material multiple times and payment terms is 5 days from each supplies date. But I pay him as per available cash balance to me. So I want to know how much is due to vendor on a particular date

Hope it clarifies
 
I would set up the worksheet as shown below.
[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[td="bgcolor:#ECF0F0, align:center"]F[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Opening Balance[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Date[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Invoice[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Date Due[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Payment[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Balance[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]100000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/1/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]90000[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/2/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/7/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]110000[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/3/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]90000[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/4/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]40000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/9/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]130000[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/8/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]110000[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4/10/2020[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]40000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]70000[/td]
[/tr]
[/table]

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[td="bgcolor:#ECF0F0, align:center"]F[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Opening Balance[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Date[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Invoice[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Date Due[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Payment[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Balance[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]100000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]43922[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=IF(C2<>"",B2 +5,"")[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=A2+C2-E2[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]43923[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=IF(C3<>"",B3 +5,"")[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=F2+C3-E3[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]43924[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=F3+C4-E4[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]43925[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]40000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=B5+5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=F4+C5-E5[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]43929[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=F5+C6-E6[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]43931[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]40000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]=F6+C7-E7[/td]
[/tr]
[/table]
 
@Rahulshwale1: Hi, payment gets due(N+1) from the date of supplies i.e If a material is delivered on 2nd then it will get due on 7th but not on 2nd
@AlanSidman : Hi, format would remain the same, could you please accodingly
 
Your data is not normalized and as such, I will not spend any more time on a spreadsheet that is not set up for easy analysis. Good Luck with finding a solution.
 
You must log in or register to reply here.
Back
Top