Business forcasting help...

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi all,

I am trying to take a spreadsheet that has columns labeled by the week
ending date. One section of rows are recurring payables, some of which are
bi-weekly, weekly, monthly (by varying date - 15th, 12th, 20th ... etc.),
and quarterly .

I am asking if someone can direct me as to how to get a bi-weekly (or
weekly, or monthly ... etc.) payment to carry itself across the sheet ...
into the appropriate weeks. What we presently have is col.A lists vendors;
Col.B lists frequency or date (15th, 20th, Q, M, B, W....) and Col.C is the
expected recurring payment amount. Basically the remaining columns are the
weeks of the year.

Thank you for your help and time

Mike
 
Hi Mike
I am trying to take a spreadsheet that has columns labeled by the week
ending date. One section of rows are recurring payables, some of which are
bi-weekly, weekly, monthly (by varying date - 15th, 12th, 20th ... etc.),
and quarterly .

I am asking if someone can direct me as to how to get a bi-weekly (or
weekly, or monthly ... etc.) payment to carry itself across the sheet ...
into the appropriate weeks. What we presently have is col.A lists vendors;
Col.B lists frequency or date (15th, 20th, Q, M, B, W....) and Col.C is the
expected recurring payment amount. Basically the remaining columns are the
weeks of the year.

Thank you for your help and time

Mike
Click to expand...


If,
Col A - Vendors
Col B - Frequency in days
Col C - Amount
Col D - Start payment date
Col E onwards - days

Then
=IF(MOD(MOD(H$1,$D2),14)=0,$C2,0)
Will show payment if the day in cell H1 is start payment date, or a multiple
of this given by the days frequency.

That is close to what you want, but not quite there.

If you show week ending date in row 1, then
=IF(MOD(MOD(H$1,$D2),14)<7,$C2,0)
Will show if payment is due that week (note I am using Friday as last day of
week, you may have to change formula slightly to suit)

Monthly frequency requires a different approach
=IF(DAY(H$1)-DAY($D2)>0,$C2,0)

Hope this points you in the right direction,
 
Back
Top