1. ## TREND/GROWTH/OTHER? (Excel 97-SR2)

Hi There

Can anyone advise best Functions for applying variance against plan to future planning assumptions? I have a series of 104 element arrays, each element of the array equates to a week.

For this discussion assume there are three single 104 element arrays (rows). In the first one I enter a numeric value in each element to indicate how much resource I intend to use that week. This is my plan.

In the second I will enter in each element the actual resource used each week.

Over time, by combining the the Actuals and the Plan I can populate the third array to give some semblance of a forecast. This however ignores any variance between plan and the actuals entered to date.

Planning - P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,etc
Actuals - A1,A2,A3,A4,A5
Forecats - A1,A2,A3,A4,A5,P6,P7,P8,P9,P10,etc

What is the best method of applying the statistical variance to date to the future planned activites?

Regards
Peter

2. ## Re: TREND/GROWTH/OTHER? (Excel 97-SR2)

This a simple question with a not so simple answer <g>.

What trending function to use depends heavily on the underlying process that determines the system you're evaluating. Often the underlying algorithm is unknown and one has to revert to choosing the best fit out of a number of possibilities.

In your case I would fit the data against a number of possible functions and then choose the one with the best regression fit. That is not trivial though.

Maybe HansV has some more insight? (Are you listening Hans?)

3. ## Re: TREND/GROWTH/OTHER? (Excel 97-SR2)

I am listening (or rather reading), but I don't have useful suggestions. I know that some Loungers contributing to this forum are in financial planning - I hope they see this thread and react.

4. ## Re: TREND/GROWTH/OTHER? (Excel 97-SR2)

Hi Folks

Best guess would work at the moment :-) What I'm working on is a resource planning and cost estimating tool. Users select from a drop down list on the left a resource type. In the 104 cells (relates to weeks) on the right, they enter how much of that resource per week they estimate they will be using. On another sheet, and with the passage of time, they can record the actual resource they used.

Obviously by adding Actual and remaining Plan together you get some sort of forecast. What I could do is compare the Actual entered to date with the Plan for the same period of time/weeks, work out any percentage difference and apply that percentage to the remaining Plan entries? Make any sense?

If it does... Anyone any thoughts on a snazzy forumula to achieve it? (It's early here, haven't got my thinking hat on yet.) Something like the following may work?

VariancePercentage=IF(SUM(INDEX(lstActualArray,1:N umberOfWeeks))-SUM(INDEX(lstPlanArray,1:NumberOfWeeks))=0,0,SUM(I NDEX(lstPlanArray,1:NumberOfWeeks))/SUM((INDEX(lstActualArray,1:NumberOfWeeks))-SUM(INDEX(lstPlanArray,1:NumberOfWeeks))))

I've typed that here, not in Excel, so there are bound to be missing "()". I'm sure there must be a simpler way?

Regards
Peter

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•