Forecast function maybe?

[HammerJoe]

Hi,

I would kindly ask for help about a problem I've been working on.
I am using excel 2003.

I keep track of the # of tasks done on a daily basis.
something like this:

-----A----------------B
1---Nov/01---------5
2---Nov/02---------3

...And so on.

What I want is to forecast at the end of the month the total number of
tasks accomplished.
I know that the month has 21 working weekdays, but right now I only
have data for two days.
As days goes by more rows are entered.
Right now I am using a pretty simple formula
=sum(b1:b21)*21/count(b1:b21) wich gives me the result 84.
But it is not very accurate at all.
I tried to use Trend, Forecast, Linest but I cant get them to work.


Any help would be much appreciated.
Thanks

 

[dgp]

This really seems to be a business modeling question rather than an
Excel question.

You say that 84 not accurate, but based on the data shown, the average
rate of completion is 4 tasks per day, so 21*4=84 seems reasonable. Is
there something different about the remaining tasks (duration,
complexity) that will prevent you from averaging 4 per day for the
remainder of the month? Can you use data from previous months to
calculate the average number of tasks completed per day per person? If
so, you can use that to estimate the number of tasks to be completed
this month.

If all your tasks are essentially equivalent (e.g. number of sales
calls, number of widgets assembled) then you should be able to use the
average daily (or weekly) completion rate to predict the number to be
completed in a month as you have done already.

However, in my company the duration and complexity of tasks vary
greatly. Furthermore, tasks tend to get completed near their deadline -
so monthly objectives tend to get completed in the last couple of days
of the month. There is no way we could predict the number of tasks to
be completed in a month based the first week or even three weeks of the
month. We track number of tasks completed versus number of monthly
objectives for each month. I guess if we wanted to predict how many
tasks we expect to complete this month we could average %complete for
past months and multiply it by the number of objectives for the month.

Trend and Forecast are not what you are looking for. They are intended
to perform a linear (or polynomial) curve fit to scattered data. For
them to work as intended your data needs to be nearly linear (or
polynomial).

Dave Parker