| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to forecast the future
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: -- Rick Pusateri ------------------------------------------------------------------------ Rick Pusateri's Profile: http://www.excelforum.com/member.php...o&userid=16828 View this thread: http://www.excelforum.com/showthread...hreadid=518052 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to forecast the future
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% -- Gary''s Student "Rick Pusateri" wrote: I need help with a re-forecast Im 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 cant 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: -- Rick Pusateri ------------------------------------------------------------------------ Rick Pusateri's Profile: http://www.excelforum.com/member.php...o&userid=16828 View this thread: http://www.excelforum.com/showthread...hreadid=518052 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to forecast the future
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: http://www.excelforum.com/member.php...o&userid=16828 View this thread: http://www.excelforum.com/showthread...hreadid=518052 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trying to forecast the future
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" wrote in message news:Rick.Pusateri.241zoy_1141321504.4461@excelfor um-nospam.com... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing Forecast Data to Actual | Excel Discussion (Misc queries) | |||
| HOW DO YOU MAKE FORECAST FUNCTION WORK IN EXCEL? | Excel Worksheet Functions | |||
| forecast formula question | Excel Worksheet Functions | |||
| Trend Forecast Help | Excel Worksheet Functions | |||
| How do I forecast future payments by analyzing past payments? | Excel Worksheet Functions | |||
Hybrid Mode
