Business forcasting help...

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
 
E

Ed Ferrero

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


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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top