Forcasting question

Hi:

I have a worksheet to track payments from several offices I want to forcast
what we would collect by the end of the month as we receive payments. I tried
the formula for forcast but could not get it to work.

Date Amount Collected
4/1/2009 $4,554
4/2/2009 $1,160
4/3/2009 $3,398
4/4/2009
4/5/2009 $50
4/6/2009 $2,825
4/7/2009 $2,586
4/8/2009 $2,603
4/9/2009 $2,015
4/10/2009 $1,308
4/11/2009 $500
4/12/2009 $600
4/13/2009 $-
4/14/2009 $-
4/15/2009 $-
4/16/2009 $-
4/17/2009 $-
4/18/2009 $-
4/19/2009 $-
4/20/2009 $-
4/21/2009 $-
4/22/2009 $-
4/23/2009 $-
4/24/2009 $-
4/25/2009 $-
4/26/2009 $-
4/27/2009 $-
4/28/2009 $-
4/29/2009 $-
4/30/2009 $-



--
Donna

"Donna" wrote:
I have a worksheet to track payments from several offices
I want to forcast what we would collect by the end of the month
as we receive payments.

You cannot forecast anything without knowing or observing a predictable
pattern. You have provided no information for anyone to do that. For all
we know, all payments end on 4/12 (!). Or the pattern of payments repeats
itself starting on 4/15. Or ....

If the numbers you provide is all you know about the pattern of payments,
then plotting the cumulative sum shows a decaying sum, and you can expect to
receive about $7000 by the end of the month.

I could explain how I arrived at that conclusion. But honestly, I think it
is a worthless prediction because it is completely devoid of context.

If you want constructive help, I suggest that you post more information.
For example, what is the nature of these payments; payments for what? And
what is the pattern of payments; what controls the daily amounts and the
pattern of payments?

Be forewarned: forecasting is an art, not a science. Having more
information might help. But it is unlikely to result in a reliable forecast
with so little data.


----- original message -----

"Donna" wrote in message
...
Hi:

I have a worksheet to track payments from several offices I want to
forcast
what we would collect by the end of the month as we receive payments. I
tried
the formula for forcast but could not get it to work.

Date Amount Collected
4/1/2009 $4,554
4/2/2009 $1,160
4/3/2009 $3,398
4/4/2009
4/5/2009 $50
4/6/2009 $2,825
4/7/2009 $2,586
4/8/2009 $2,603
4/9/2009 $2,015
4/10/2009 $1,308
4/11/2009 $500
4/12/2009 $600
4/13/2009 $-
4/14/2009 $-
4/15/2009 $-
4/16/2009 $-
4/17/2009 $-
4/18/2009 $-
4/19/2009 $-
4/20/2009 $-
4/21/2009 $-
4/22/2009 $-
4/23/2009 $-
4/24/2009 $-
4/25/2009 $-
4/26/2009 $-
4/27/2009 $-
4/28/2009 $-
4/29/2009 $-
4/30/2009 $-



--
Donna

Is forcasting not the right term? As stated below these are payments we
receive daily from clients. We never know what we are going to collect. Our
goal for this month for this office is $93,870. I want to know with what we
have collected what are we on track to collect?

Thank you so much for your help.
--
Donna


"Donna" wrote:

Hi:

I have a worksheet to track payments from several offices I want to forcast
what we would collect by the end of the month as we receive payments. I tried
the formula for forcast but could not get it to work.

Date Amount Collected
4/1/2009 $4,554
4/2/2009 $1,160
4/3/2009 $3,398
4/4/2009
4/5/2009 $50
4/6/2009 $2,825
4/7/2009 $2,586
4/8/2009 $2,603
4/9/2009 $2,015
4/10/2009 $1,308
4/11/2009 $500
4/12/2009 $600
4/13/2009 $-
4/14/2009 $-
4/15/2009 $-
4/16/2009 $-
4/17/2009 $-
4/18/2009 $-
4/19/2009 $-
4/20/2009 $-
4/21/2009 $-
4/22/2009 $-
4/23/2009 $-
4/24/2009 $-
4/25/2009 $-
4/26/2009 $-
4/27/2009 $-
4/28/2009 $-
4/29/2009 $-
4/30/2009 $-



--
Donna

"Donna" wrote:
Is forcasting not the right term?

Yes it is. But that does not mean that the Excel FORECAST function is the
right tool to use. You need to understand what a function does. See the
Help page.


We never know what we are going to collect. Our goal for
this month for this office is $93,870. I want to know with
what we have collected what are we on track to collect?

This is a better description, with key additional information. However, for
a better forecast, you still need to know something about the pattern of
payments. The question to ask youself: how does the pattern of payments
over the past 12 days predict the pattern for the remainder of the month?
Even better: do you have data from previous months or, best, the previous
year(s) that will help predict the pattern of payments for the remainder of
the month?

If all you have is the data presented, there are a couple of ways that you
can look at it to predict the future. But be forewarned: such forecasts
are very unreliable.

The simplest approach.... Divide the sum of what you received the goal, and
divide the number of elapsed days by the number of days in the month.
Compare those two percentages subjectively. There are many ways to write
such formulas, depending on how robust you want to make them. The simplest
might be:

=sum(B2:B31) / 93870

=day(today()) / day(eomonth(A2,0))

Format each cell as Percentage. With the data through 4/12/2009, I get
about 23% and 40%. Unless you know more about the pattern of payments, you
can see that you are behind.

Another simple approach.... Compute the average daily receipt to date and
multiply by the total days in the month. Compare with the goal
subjectively. For example:

=average(B2:B31) * day(eomonth(A2,0))

With the data through 4/12/2009, I get about $53,998. Again, unless you
expect the average daily receipt to be significantly more in the latter part
of the month, you can see that you are far behind.

Finally, you can employ fancier forecasting tools. But note: just because
they are more elaborate, that does not mean they are more accurate. GIGO:
garbage in, garbage out.

I started by computing the cumulative sum of payments received. That is, in
C2: =B2; and in C3 and copied down: =B3+C2. Then I used the Chart Wizard
to create an XY chart of those 12 data points. Note: Interpretation of
graphs is very subjective. What I saw is a log curve -- that is, an
increasing curve that tapers off -- starting at 4/5/2009. So I created a
new chart of the cumulative sums for 4/5 through 4/12 -- caveat: not much
data to go on -- and created a log trendline, showing R-sq and the equation.
That confirmed a close fit.

At this point, we want to use the trendline equation to extend the existing
data to the end of the month. There are several ways to do that.

To begin with, we can use Format Trendline in the chart to extend the line
forward by 18 data points (the remaining days in the month) and eyeball the
end value off the chart.

Alternatively, we can use the treandline equation in formulas in the
worksheet to compute the cumulative amount on 4/30. The simplest way to do
that is to copy the equation in the chart. Starting in D2 and copy down:

=6445.4*ln(row(1:1)) + 8354.4

I get a cumulative total of about $30,276. That is less than the total
expected based on the average daily incoming rate, and it is significantly
below your goal. But I reiterate: that assumes a pattern of future
receipts that track a "log" trend of the previous receipts. I have no
reason to expect that; I also have no reason not to.

FYI, if you want to get really fancy, the constants in the last formula can
be replaced by the exact coefficients. The resulting formula would be:

=INDEX(LINEST($C$6:$C$13,LN(ROW($1:$8))),1) * LN(ROW(1:1))
+ INDEX(LINEST($C$6:$C$13,LN(ROW($1:$8))),1,2)

Gulp! The fact is: in this case, that yields about the same results. And
you can also get fancier and more robust. But that might be "killing an ant
with a sledgehammer".


----- original message -----

"Donna" wrote in message
news
Is forcasting not the right term? As stated below these are payments we
receive daily from clients. We never know what we are going to collect.
Our
goal for this month for this office is $93,870. I want to know with what
we
have collected what are we on track to collect?

Thank you so much for your help.
--
Donna


"Donna" wrote:

Hi:

I have a worksheet to track payments from several offices I want to
forcast
what we would collect by the end of the month as we receive payments. I
tried
the formula for forcast but could not get it to work.

Date Amount Collected
4/1/2009 $4,554
4/2/2009 $1,160
4/3/2009 $3,398
4/4/2009
4/5/2009 $50
4/6/2009 $2,825
4/7/2009 $2,586
4/8/2009 $2,603
4/9/2009 $2,015
4/10/2009 $1,308
4/11/2009 $500
4/12/2009 $600
4/13/2009 $-
4/14/2009 $-
4/15/2009 $-
4/16/2009 $-
4/17/2009 $-
4/18/2009 $-
4/19/2009 $-
4/20/2009 $-
4/21/2009 $-
4/22/2009 $-
4/23/2009 $-
4/24/2009 $-
4/25/2009 $-
4/26/2009 $-
4/27/2009 $-
4/28/2009 $-
4/29/2009 $-
4/30/2009 $-



--
Donna

Joel

Thank you so much for your help and taking the time to explain the different
options.

--
Donna


"JoeU2004" wrote:

"Donna" wrote:
Is forcasting not the right term?

Yes it is. But that does not mean that the Excel FORECAST function is the
right tool to use. You need to understand what a function does. See the
Help page.


We never know what we are going to collect. Our goal for
this month for this office is $93,870. I want to know with
what we have collected what are we on track to collect?

This is a better description, with key additional information. However, for
a better forecast, you still need to know something about the pattern of
payments. The question to ask youself: how does the pattern of payments
over the past 12 days predict the pattern for the remainder of the month?
Even better: do you have data from previous months or, best, the previous
year(s) that will help predict the pattern of payments for the remainder of
the month?

If all you have is the data presented, there are a couple of ways that you
can look at it to predict the future. But be forewarned: such forecasts
are very unreliable.

The simplest approach.... Divide the sum of what you received the goal, and
divide the number of elapsed days by the number of days in the month.
Compare those two percentages subjectively. There are many ways to write
such formulas, depending on how robust you want to make them. The simplest
might be:

=sum(B2:B31) / 93870

=day(today()) / day(eomonth(A2,0))

Format each cell as Percentage. With the data through 4/12/2009, I get
about 23% and 40%. Unless you know more about the pattern of payments, you
can see that you are behind.

Another simple approach.... Compute the average daily receipt to date and
multiply by the total days in the month. Compare with the goal
subjectively. For example:

=average(B2:B31) * day(eomonth(A2,0))

With the data through 4/12/2009, I get about $53,998. Again, unless you
expect the average daily receipt to be significantly more in the latter part
of the month, you can see that you are far behind.

Finally, you can employ fancier forecasting tools. But note: just because
they are more elaborate, that does not mean they are more accurate. GIGO:
garbage in, garbage out.

I started by computing the cumulative sum of payments received. That is, in
C2: =B2; and in C3 and copied down: =B3+C2. Then I used the Chart Wizard
to create an XY chart of those 12 data points. Note: Interpretation of
graphs is very subjective. What I saw is a log curve -- that is, an
increasing curve that tapers off -- starting at 4/5/2009. So I created a
new chart of the cumulative sums for 4/5 through 4/12 -- caveat: not much
data to go on -- and created a log trendline, showing R-sq and the equation.
That confirmed a close fit.

At this point, we want to use the trendline equation to extend the existing
data to the end of the month. There are several ways to do that.

To begin with, we can use Format Trendline in the chart to extend the line
forward by 18 data points (the remaining days in the month) and eyeball the
end value off the chart.

Alternatively, we can use the treandline equation in formulas in the
worksheet to compute the cumulative amount on 4/30. The simplest way to do
that is to copy the equation in the chart. Starting in D2 and copy down:

=6445.4*ln(row(1:1)) + 8354.4

I get a cumulative total of about $30,276. That is less than the total
expected based on the average daily incoming rate, and it is significantly
below your goal. But I reiterate: that assumes a pattern of future
receipts that track a "log" trend of the previous receipts. I have no
reason to expect that; I also have no reason not to.

FYI, if you want to get really fancy, the constants in the last formula can
be replaced by the exact coefficients. The resulting formula would be:

=INDEX(LINEST($C$6:$C$13,LN(ROW($1:$8))),1) * LN(ROW(1:1))
+ INDEX(LINEST($C$6:$C$13,LN(ROW($1:$8))),1,2)

Gulp! The fact is: in this case, that yields about the same results. And
you can also get fancier and more robust. But that might be "killing an ant
with a sledgehammer".


----- original message -----

"Donna" wrote in message
news
Is forcasting not the right term? As stated below these are payments we
receive daily from clients. We never know what we are going to collect.
Our
goal for this month for this office is $93,870. I want to know with what
we
have collected what are we on track to collect?

Thank you so much for your help.
--
Donna


"Donna" wrote:

Hi:

I have a worksheet to track payments from several offices I want to
forcast
what we would collect by the end of the month as we receive payments. I
tried
the formula for forcast but could not get it to work.

Date Amount Collected
4/1/2009 $4,554
4/2/2009 $1,160
4/3/2009 $3,398
4/4/2009
4/5/2009 $50
4/6/2009 $2,825
4/7/2009 $2,586
4/8/2009 $2,603
4/9/2009 $2,015
4/10/2009 $1,308
4/11/2009 $500
4/12/2009 $600
4/13/2009 $-
4/14/2009 $-
4/15/2009 $-
4/16/2009 $-
4/17/2009 $-
4/18/2009 $-
4/19/2009 $-
4/20/2009 $-
4/21/2009 $-
4/22/2009 $-
4/23/2009 $-
4/24/2009 $-
4/25/2009 $-
4/26/2009 $-
4/27/2009 $-
4/28/2009 $-
4/29/2009 $-
4/30/2009 $-



--
Donna