Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •