Donna said:
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 -----
