software

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
Y2003Jan1,845 39 2.1%
Y2003Feb1,792 32 1.8%
Y2003Mar1,953 48 2.5%
Y2003Apr1,912 55 2.9%
Y2003May1,837 57 3.1%
Y2003Jun1,888 55 2.9%
Y2003Jul2,009 52 2.6%
Y2003Aug1,991 52 2.6%
Y2003Sep2,105 41 1.9%
Y2003Oct2,128 51 2.4%
Y2003Nov1,867 40 2.1%
Y2003Dec2,069 36 1.8%
Y2004Jan2,182 41 1.9%
Y2004Feb2,145 51 2.4%
Y2004Mar2,356 74 3.2%
Y2004Apr2,250 73 3.2%
Y2004May2,071 67 3.3%
Y2004Jun2,239 75 3.4%
Y2004Jul2,348 72 3.1%
Y2004Aug2,359 73 3.1%
Y2004Sep2,376 66 2.8%
Y2004Oct2,317 57 2.4%
Y2004Nov2,236 57 2.6%
Y2004Dec2,229 26 1.2%
Y2005Jan2,459 53 2.2%
Y2005Feb2,387 64 2.7%
Y2005Mar2,517 85 3.4%
Y2005Apr2,372 79 3.3%
Y2005May2,308 85 3.7%
Y2005Jun2,307 89 3.9%
Y2005Jul2,375 81 3.4%
Y2005Aug2,452 85 3.5%
Y2005Sep2,408 63 2.6%
Y2005Oct2,338 57 2.5%
Y2005Nov2,241 63 2.8%
Y2005Dec2,089 60 2.9%
Y2006Jan2,367 51 2.2%
Y2006Feb2,482 81 3.3%
Y2006Mar2,503 82 3.3%
Y2006Apr2,568 86 3.4%
Y2006May2,562 84 3.3%
Y2006Jun2,459 81 3.3%
Y2006Jul2,434 81 3.3%
Y2006Aug2,572 83 3.2%
Y2006Sep2,576 84 3.2%
Y2006Oct2,663 88 3.3%
Y2006Nov2,641 85 3.2%
Y2006Dec2,550 82 3.2%Rick Pusateri
Tel: (206) 925-1689
Fax: (206) 545-6091
Email: --
Rick Pusateri
------------------------------------------------------------------------
Rick Pusateri's Profile: www.excelforum.com/member.php...oamp;userid=16828
View this thread: www.excelforum.com/showthread...hreadid=518052I 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%
--
Gary''s Studentquot;Rick Pusateriquot; wrote:

gt;
gt; I need help with a re-forecast I’m trying to create. As you can see from
gt; the table, the planned % Subset of Total from Feb 06-Dec 06 do not look
gt; like they will follow the historical trend. What I want to do is
gt; re-forecast the subset column, based upon the % Subset of Total in
gt; prior years/months. I know there must be a statistical formula(s) that
gt; I should use, but I can’t figure out which one(s). Thanks in advance!
gt;
gt; Year_Mo_ Total Subset % Subset of Total
gt; Y2003Jan1,845 39 2.1%
gt; Y2003Feb1,792 32 1.8%
gt; Y2003Mar1,953 48 2.5%
gt; Y2003Apr1,912 55 2.9%
gt; Y2003May1,837 57 3.1%
gt; Y2003Jun1,888 55 2.9%
gt; Y2003Jul2,009 52 2.6%
gt; Y2003Aug1,991 52 2.6%
gt; Y2003Sep2,105 41 1.9%
gt; Y2003Oct2,128 51 2.4%
gt; Y2003Nov1,867 40 2.1%
gt; Y2003Dec2,069 36 1.8%
gt; Y2004Jan2,182 41 1.9%
gt; Y2004Feb2,145 51 2.4%
gt; Y2004Mar2,356 74 3.2%
gt; Y2004Apr2,250 73 3.2%
gt; Y2004May2,071 67 3.3%
gt; Y2004Jun2,239 75 3.4%
gt; Y2004Jul2,348 72 3.1%
gt; Y2004Aug2,359 73 3.1%
gt; Y2004Sep2,376 66 2.8%
gt; Y2004Oct2,317 57 2.4%
gt; Y2004Nov2,236 57 2.6%
gt; Y2004Dec2,229 26 1.2%
gt; Y2005Jan2,459 53 2.2%
gt; Y2005Feb2,387 64 2.7%
gt; Y2005Mar2,517 85 3.4%
gt; Y2005Apr2,372 79 3.3%
gt; Y2005May2,308 85 3.7%
gt; Y2005Jun2,307 89 3.9%
gt; Y2005Jul2,375 81 3.4%
gt; Y2005Aug2,452 85 3.5%
gt; Y2005Sep2,408 63 2.6%
gt; Y2005Oct2,338 57 2.5%
gt; Y2005Nov2,241 63 2.8%
gt; Y2005Dec2,089 60 2.9%
gt; Y2006Jan2,367 51 2.2%
gt; Y2006Feb2,482 81 3.3%
gt; Y2006Mar2,503 82 3.3%
gt; Y2006Apr2,568 86 3.4%
gt; Y2006May2,562 84 3.3%
gt; Y2006Jun2,459 81 3.3%
gt; Y2006Jul2,434 81 3.3%
gt; Y2006Aug2,572 83 3.2%
gt; Y2006Sep2,576 84 3.2%
gt; Y2006Oct2,663 88 3.3%
gt; Y2006Nov2,641 85 3.2%
gt; Y2006Dec2,550 82 3.2%
gt;
gt;
gt; Rick Pusateri
gt; Tel: (206) 925-1689
gt; Fax: (206) 545-6091
gt; Email:
gt;
gt;
gt; --
gt; Rick Pusateri
gt; ------------------------------------------------------------------------
gt; Rick Pusateri's Profile: www.excelforum.com/member.php...oamp;userid=16828
gt; View this thread: www.excelforum.com/showthread...hreadid=518052
gt;
gt;


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--
Rick Pusateri
------------------------------------------------------------------------
Rick Pusateri's Profile: www.excelforum.com/member.php...oamp;userid=16828
View this thread: www.excelforum.com/showthread...hreadid=518052Rick -

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 quot;Time
Series Seasonalityquot; chapter of my book quot;Data Analysis Using Microsoft Excel:
Updated for Office XP.quot; 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

quot;Rick Pusateriquot; gt;
wrote in message
news:Rick.Pusateri.241zoy_1141321504.4461@excelfor um-nospam.com...
gt;
gt; Thanks, Gary - appreciate your response. The problem I see is that there
gt; is seasonality to the numbers, and the % subset is growing overall. How
gt; does your equation factor that piece in? Thanks!
gt;
gt; Rick