Trying to forecast the future

[Rick Pusateri]

I need help with a re-forecast I’m trying to create. As you can see from
the table, the planned % Subset of Total from Feb 06-Dec 06 do not look
like they will follow the historical trend. What I want to do is
re-forecast the subset column, based upon the % Subset of Total in
prior years/months. I know there must be a statistical formula(s) that
I should use, but I can’t figure out which one(s). Thanks in advance!

Year_ Mo_ Total Subset % Subset of Total
Y2003 Jan 1,845 39 2.1%
Y2003 Feb 1,792 32 1.8%
Y2003 Mar 1,953 48 2.5%
Y2003 Apr 1,912 55 2.9%
Y2003 May 1,837 57 3.1%
Y2003 Jun 1,888 55 2.9%
Y2003 Jul 2,009 52 2.6%
Y2003 Aug 1,991 52 2.6%
Y2003 Sep 2,105 41 1.9%
Y2003 Oct 2,128 51 2.4%
Y2003 Nov 1,867 40 2.1%
Y2003 Dec 2,069 36 1.8%
Y2004 Jan 2,182 41 1.9%
Y2004 Feb 2,145 51 2.4%
Y2004 Mar 2,356 74 3.2%
Y2004 Apr 2,250 73 3.2%
Y2004 May 2,071 67 3.3%
Y2004 Jun 2,239 75 3.4%
Y2004 Jul 2,348 72 3.1%
Y2004 Aug 2,359 73 3.1%
Y2004 Sep 2,376 66 2.8%
Y2004 Oct 2,317 57 2.4%
Y2004 Nov 2,236 57 2.6%
Y2004 Dec 2,229 26 1.2%
Y2005 Jan 2,459 53 2.2%
Y2005 Feb 2,387 64 2.7%
Y2005 Mar 2,517 85 3.4%
Y2005 Apr 2,372 79 3.3%
Y2005 May 2,308 85 3.7%
Y2005 Jun 2,307 89 3.9%
Y2005 Jul 2,375 81 3.4%
Y2005 Aug 2,452 85 3.5%
Y2005 Sep 2,408 63 2.6%
Y2005 Oct 2,338 57 2.5%
Y2005 Nov 2,241 63 2.8%
Y2005 Dec 2,089 60 2.9%
Y2006 Jan 2,367 51 2.2%
Y2006 Feb 2,482 81 3.3%
Y2006 Mar 2,503 82 3.3%
Y2006 Apr 2,568 86 3.4%
Y2006 May 2,562 84 3.3%
Y2006 Jun 2,459 81 3.3%
Y2006 Jul 2,434 81 3.3%
Y2006 Aug 2,572 83 3.2%
Y2006 Sep 2,576 84 3.2%
Y2006 Oct 2,663 88 3.3%
Y2006 Nov 2,641 85 3.2%
Y2006 Dec 2,550 82 3.2%


Rick Pusateri
Tel: (206) 925-1689
Fax: (206) 545-6091
Email: (e-mail address removed)

 

[Guest]

I plotted you total vs time and your % vs time.

Your total is growing at a nice linear rate:

T=14.779*M+1881.9 where M is one month units

Your % is converging to around 3.2%

I would forecast using the linear growth in total and a fixed percent of 3.2%

 

[Rick Pusateri]

Thanks, Gary - appreciate your response. The problem I see is that there
is seasonality to the numbers, and the % subset is growing overall. How
does your equation factor that piece in? Thanks!

Rick

 

[Mike Middleton]

Rick -

I have not looked at your data, but if you have monthly seasonality with
linear trend, you could use one of the three methods described in the "Time
Series Seasonality" chapter of my book "Data Analysis Using Microsoft Excel:
Updated for Office XP." One approach is linear time trend regression with
indicator (zero or one) variables for seasonality; another is autoregression
with both lag 1 to model trend and lag 12 to model seasonality. The methods
are a bit too much to explain in an email message, but you might be able to
find some online explanations using google or google groups.

- Mike
www.mikemiddleton.com

"Rick Pusateri" <[email protected]>
wrote in message
news:[email protected]...