I have a P&L line item for an expense call it Myexpense by month on my P&L Spreadsheet.
There are different payment terms of this expense depending on which product I am working with, which will affect my cash flow P&L statment. I could use some help creating a formula that will work under these pmnt scenarios (I actually have 7 different pmnt terms to accomodate)
#1 net 30
#2 net 60
#3 staggered pmnt (i.e 25% net 30, 25% net 60, 50% net 90)
I added a column(column A) next to the cash statement for the MYexpense (row1) line item as the trigger code to determine which of the above to use
(i.e in cell A1. 1 means use net 30 , 2 means use net 60 etc)
I figured net 30 and 60 are easy to accomodate as follows (note column C= Jan etc used column F for April in formuala example below
=IF(OR(A1=1,A2=2),OFFSET('p&L'!F1,0,-A1))
now I need to add an if for pmnt terms #3 staggered pmnts as follows
=IF(OR(A1=1,A2=2),OFFSET('p&L'!F1,0,-A1),IF(A1=3,OFFSET('p&L'!F1,0,-3)*0.5+OFFSET('p&L'!F1,0,-2)*0.25+OFFSET('p&L'!F1,0,-1)*0.25,0))
I will have to add add'l pmtn term options (#4,#5etc & similar to #3) and formula may get large. I was hoping I could somehow create a table with all the info and have use a sumproduct formula somehow and will need to be able to copy formula across Any ideas?
There are different payment terms of this expense depending on which product I am working with, which will affect my cash flow P&L statment. I could use some help creating a formula that will work under these pmnt scenarios (I actually have 7 different pmnt terms to accomodate)
#1 net 30
#2 net 60
#3 staggered pmnt (i.e 25% net 30, 25% net 60, 50% net 90)
I added a column(column A) next to the cash statement for the MYexpense (row1) line item as the trigger code to determine which of the above to use
(i.e in cell A1. 1 means use net 30 , 2 means use net 60 etc)
I figured net 30 and 60 are easy to accomodate as follows (note column C= Jan etc used column F for April in formuala example below
=IF(OR(A1=1,A2=2),OFFSET('p&L'!F1,0,-A1))
now I need to add an if for pmnt terms #3 staggered pmnts as follows
=IF(OR(A1=1,A2=2),OFFSET('p&L'!F1,0,-A1),IF(A1=3,OFFSET('p&L'!F1,0,-3)*0.5+OFFSET('p&L'!F1,0,-2)*0.25+OFFSET('p&L'!F1,0,-1)*0.25,0))
I will have to add add'l pmtn term options (#4,#5etc & similar to #3) and formula may get large. I was hoping I could somehow create a table with all the info and have use a sumproduct formula somehow and will need to be able to copy formula across Any ideas?
