B
Blddrgn700
Hi All:
I am currently working with a cash forecasting model, it needs to be
enhanced to be more dynamic and user friendly.
The problem at hand is when dating the sales forecast for imput into the
model I use a starting date that is based on the first day of the month, for
example:
6/01/08
Then I add 7 and seperate the month into four weeks so that I have:
Column A Column B
R1 Week 1 = Revenue $.$$ Expected Date of Receipt 6/08/08
R2 Week 2 = Revenue $.$$ 6/15/08
R3 Week 3 = Revenue $.$$ 6/22/08
R4 Week 4 = Revenue $.$$ 6/29/08
Etcetera.....
The above goes on for 5 months. It should feed the cash forecast as the
incoming revenue. However, the cash forecast dates are based on the end of a
week so that each week is summarized on the last friday of a week. In the
case of June:
The dates would be:
6/06/08
6/13/08
6/20/08
6/27/08
So the first set of dates to not coincide with the second set of dates. The
second set of dates extend out for 14-weeks.
I tried using the SumIf function but it would not reconize any of the value
because none of the dates matched.
I thought that the SumProduct may by useful but I did not see any reference
to segmenting week, month and year.
I saw many formulas that broke out month and year. Can anyone suggest a
function that I could use to line up the dates?
Thank you in advance for any insight and help!
Kurtis
I am currently working with a cash forecasting model, it needs to be
enhanced to be more dynamic and user friendly.
The problem at hand is when dating the sales forecast for imput into the
model I use a starting date that is based on the first day of the month, for
example:
6/01/08
Then I add 7 and seperate the month into four weeks so that I have:
Column A Column B
R1 Week 1 = Revenue $.$$ Expected Date of Receipt 6/08/08
R2 Week 2 = Revenue $.$$ 6/15/08
R3 Week 3 = Revenue $.$$ 6/22/08
R4 Week 4 = Revenue $.$$ 6/29/08
Etcetera.....
The above goes on for 5 months. It should feed the cash forecast as the
incoming revenue. However, the cash forecast dates are based on the end of a
week so that each week is summarized on the last friday of a week. In the
case of June:
The dates would be:
6/06/08
6/13/08
6/20/08
6/27/08
So the first set of dates to not coincide with the second set of dates. The
second set of dates extend out for 14-weeks.
I tried using the SumIf function but it would not reconize any of the value
because none of the dates matched.
I thought that the SumProduct may by useful but I did not see any reference
to segmenting week, month and year.
I saw many formulas that broke out month and year. Can anyone suggest a
function that I could use to line up the dates?
Thank you in advance for any insight and help!
Kurtis