Weekly Forecasting - HELP!!

[MarkAndrews]

Hi Guys,

I am stumped with some work at the moment & need help

Basically I've been asked to provide a forecast for the next 12 weeks (or 3 months - these being Aug, Sep & Nov 08)

However, the tricky part comes in that the forecast is weekly & not monthly

Hopefully the example of my (automatically) generated pivot table will work in this post

Col A contains the product codes
Col B contains the description
C - Z contains the qty by week

<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com) --><TABLE borderColor=#c0c0c0 cellSpacing=0 borderColorDark=#ffffff cellPadding=2 border=1><TBODY><TR><TD vAlign=bottom><NOBR>Sum of Qty</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom><NOBR>Period No</NOBR></TD><TD vAlign=bottom><NOBR>Week No</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>10801</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom><NOBR>10801 Total</NOBR></TD><TD vAlign=bottom align=right><NOBR>10802</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom><NOBR>10802 Total</NOBR></TD><TD vAlign=bottom align=right><NOBR>10803</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom><NOBR>10803 Total</NOBR></TD></TR><TR><TD vAlign=bottom><NOBR>Item</NOBR></TD><TD vAlign=bottom><NOBR>Item Description</NOBR></TD><TD vAlign=bottom align=right><NOBR>10753</NOBR></TD><TD vAlign=bottom align=right><NOBR>10801</NOBR></TD><TD vAlign=bottom align=right><NOBR>10802</NOBR></TD><TD vAlign=bottom align=right><NOBR>10803</NOBR></TD><TD vAlign=bottom align=right><NOBR>10804</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>10804</NOBR></TD><TD vAlign=bottom align=right><NOBR>10805</NOBR></TD><TD vAlign=bottom align=right><NOBR>10806</NOBR></TD><TD vAlign=bottom align=right><NOBR>10807</NOBR></TD><TD vAlign=bottom align=right><NOBR>10808</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>10809</NOBR></TD><TD vAlign=bottom align=right><NOBR>10810</NOBR></TD><TD vAlign=bottom align=right><NOBR>10811</NOBR></TD><TD vAlign=bottom align=right><NOBR>10812</NOBR></TD><TD vAlign=bottom align=right><NOBR>10813</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>0060875</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG ML-D4550 LASER TNER/DRM BLK</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>0247812</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG CLP-500 LASER TON</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>0377152</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG CLP-510/510N DRUM</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>10</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR>5</NOBR></TD><TD vAlign=bottom align=right><NOBR>19</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>4</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR>10</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>14</NOBR></TD><TD vAlign=bottom align=right><NOBR>18</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>36</NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>0418711</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG CLP-500 LASER TONER CYAN</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>6</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>8</NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>0731801</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG CLP-600/650 CYAN LASER TONER</NOBR></TD><TD vAlign=bottom align=right><NOBR>3</NOBR></TD><TD vAlign=bottom align=right><NOBR>17</NOBR></TD><TD vAlign=bottom align=right><NOBR>33</NOBR></TD><TD vAlign=bottom align=right><NOBR>24</NOBR></TD><TD vAlign=bottom align=right><NOBR>15</NOBR></TD><TD vAlign=bottom align=right><NOBR>92</NOBR></TD><TD vAlign=bottom align=right><NOBR>3</NOBR></TD><TD vAlign=bottom align=right><NOBR>20</NOBR></TD><TD vAlign=bottom align=right><NOBR>11</NOBR></TD><TD vAlign=bottom align=right><NOBR>17</NOBR></TD><TD vAlign=bottom align=right><NOBR>6</NOBR></TD><TD vAlign=bottom align=right><NOBR>57</NOBR></TD><TD vAlign=bottom align=right><NOBR>14</NOBR></TD><TD vAlign=bottom align=right><NOBR>14</NOBR></TD><TD vAlign=bottom align=right><NOBR>31</NOBR></TD><TD vAlign=bottom align=right><NOBR>25</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>84</NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>0732803</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG SCX4100 TONER/DRUM UNIT</NOBR></TD><TD vAlign=bottom align=right><NOBR>13</NOBR></TD><TD vAlign=bottom align=right><NOBR>17</NOBR></TD><TD vAlign=bottom align=right><NOBR>11</NOBR></TD><TD vAlign=bottom align=right><NOBR>15</NOBR></TD><TD vAlign=bottom align=right><NOBR>13</NOBR></TD><TD vAlign=bottom align=right><NOBR>69</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>14</NOBR></TD><TD vAlign=bottom align=right><NOBR>14</NOBR></TD><TD vAlign=bottom align=right><NOBR>10</NOBR></TD><TD vAlign=bottom align=right><NOBR>6</NOBR></TD><TD vAlign=bottom align=right><NOBR>44</NOBR></TD><TD vAlign=bottom align=right><NOBR>12</NOBR></TD><TD vAlign=bottom align=right><NOBR>74</NOBR></TD><TD vAlign=bottom align=right><NOBR>9</NOBR></TD><TD vAlign=bottom align=right><NOBR>5</NOBR></TD><TD vAlign=bottom align=right><NOBR>3</NOBR></TD><TD vAlign=bottom align=right><NOBR>103</NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>0952516</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG SF830 FAX TONER BLACK</NOBR></TD><TD vAlign=bottom align=right><NOBR>5</NOBR></TD><TD vAlign=bottom align=right><NOBR>28</NOBR></TD><TD vAlign=bottom align=right><NOBR>31</NOBR></TD><TD vAlign=bottom align=right><NOBR>34</NOBR></TD><TD vAlign=bottom align=right><NOBR>9</NOBR></TD><TD vAlign=bottom align=right><NOBR>107</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>21</NOBR></TD><TD vAlign=bottom align=right><NOBR>41</NOBR></TD><TD vAlign=bottom align=right><NOBR>14</NOBR></TD><TD vAlign=bottom align=right><NOBR>31</NOBR></TD><TD vAlign=bottom align=right><NOBR>107</NOBR></TD><TD vAlign=bottom align=right><NOBR>8</NOBR></TD><TD vAlign=bottom align=right><NOBR>16</NOBR></TD><TD vAlign=bottom align=right><NOBR>38</NOBR></TD><TD vAlign=bottom align=right><NOBR>31</NOBR></TD><TD vAlign=bottom align=right><NOBR>12</NOBR></TD><TD vAlign=bottom align=right><NOBR>105</NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>1031136</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG CLP510 CYAN TONER CARTRIDGE</NOBR></TD><TD vAlign=bottom align=right><NOBR>4</NOBR></TD><TD vAlign=bottom align=right><NOBR>6</NOBR></TD><TD vAlign=bottom align=right><NOBR>18</NOBR></TD><TD vAlign=bottom align=right><NOBR>5</NOBR></TD><TD vAlign=bottom align=right><NOBR>5</NOBR></TD><TD vAlign=bottom align=right><NOBR>38</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>7</NOBR></TD><TD vAlign=bottom align=right><NOBR>15</NOBR></TD><TD vAlign=bottom align=right><NOBR>5</NOBR></TD><TD vAlign=bottom align=right><NOBR>12</NOBR></TD><TD vAlign=bottom align=right><NOBR>41</NOBR></TD><TD vAlign=bottom align=right><NOBR>4</NOBR></TD><TD vAlign=bottom align=right><NOBR>13</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>4</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>23</NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>1055460</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG SF-750 LASER FAX</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>3</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>4</NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>1095937</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG CLP-300 BLACK LASER TONER</NOBR></TD><TD vAlign=bottom align=right><NOBR>38</NOBR></TD><TD vAlign=bottom align=right><NOBR>29</NOBR></TD><TD vAlign=bottom align=right><NOBR>30</NOBR></TD><TD vAlign=bottom align=right><NOBR>137</NOBR></TD><TD vAlign=bottom align=right><NOBR>26</NOBR></TD><TD vAlign=bottom align=right><NOBR>260</NOBR></TD><TD vAlign=bottom align=right><NOBR>4</NOBR></TD><TD vAlign=bottom align=right><NOBR>103</NOBR></TD><TD vAlign=bottom align=right><NOBR>44</NOBR></TD><TD vAlign=bottom align=right><NOBR>28</NOBR></TD><TD vAlign=bottom align=right><NOBR>33</NOBR></TD><TD vAlign=bottom align=right><NOBR>212</NOBR></TD><TD vAlign=bottom align=right><NOBR>33</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>23</NOBR></TD><TD vAlign=bottom align=right><NOBR>10</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>66</NOBR></TD></TR><TR><TD vAlign=bottom><NOBR>111-8377</NOBR></TD><TD vAlign=bottom><NOBR>OBSOLETE SAMSUNG ML-2510 LASER PRIN</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR>1</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD></TR><TR><TD vAlign=bottom align=right><NOBR>1120125</NOBR></TD><TD vAlign=bottom><NOBR>SAMSUNG CLP-600/650 MAG LASER TONER</NOBR></TD><TD vAlign=bottom align=right><NOBR>2</NOBR></TD><TD vAlign=bottom align=right><NOBR>18</NOBR></TD><TD vAlign=bottom align=right><NOBR>8</NOBR></TD><TD vAlign=bottom align=right><NOBR>20</NOBR></TD><TD vAlign=bottom align=right><NOBR>17</NOBR></TD><TD vAlign=bottom align=right><NOBR>65</NOBR></TD><TD vAlign=bottom align=right><NOBR>3</NOBR></TD><TD vAlign=bottom align=right><NOBR>6</NOBR></TD><TD vAlign=bottom align=right><NOBR>12</NOBR></TD><TD vAlign=bottom align=right><NOBR>14</NOBR></TD><TD vAlign=bottom align=right><NOBR>7</NOBR></TD><TD vAlign=bottom align=right><NOBR>42</NOBR></TD><TD vAlign=bottom align=right><NOBR>8</NOBR></TD><TD vAlign=bottom align=right><NOBR>9</NOBR></TD><TD vAlign=bottom align=right><NOBR>45</NOBR></TD><TD vAlign=bottom align=right><NOBR>19</NOBR></TD><TD vAlign=bottom align=right><NOBR></NOBR></TD><TD vAlign=bottom align=right><NOBR>81</NOBR></TD></TR></TBODY></TABLE>

 

[dk]

Maybe I'm being thicker than a whale omlette, but what is your question?

 

[MarkAndrews]

Maybe I'm being thicker than a whale omlette, but what is your question?

I am trying to forecast (weekly) from historic sales figures for the next 3 months & have no idea how to<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Using the data in my original post?<o></o>

 

[Kaps_mr2]

surely we need to know your methodology first i.e average of last 3 weeks or something first and then we can look at how Excel can do this ?

Kaps

 

[MarkAndrews]

surely we need to know your methodology first i.e average of last 3 weeks or something first and then we can look at how Excel can do this ?

Kaps

Thats what i'm struggling with, my knowledge of forecasting is limited to say the least

 

[Domski]

Hi Mark,

I've never really done much on forecasting but I'm guessing a bit more info would help.

To my mind you would need to consider a few things such as what is it that you are actually forecasting, e.g. sales/usage amd the factors do you need to take account of when making your forecast.

This might give you a few ideas:

http://www.statisticalforecasting.com/

Dom

 

[MarkAndrews]

Hi Mark,

I've never really done much on forecasting but I'm guessing a bit more info would help.

To my mind you would need to consider a few things such as what is it that you are actually forecasting, e.g. sales/usage amd the factors do you need to take account of when making your forecast.

This might give you a few ideas:

http://www.statisticalforecasting.com/

Dom

Thanks Dom, will have a read after lunch

 

[Geoff Taylor]

For forecasting purposes there's too little information here. Next week's sales are not a function of this week's. Yes, it's possible to extrapolate a figure from some kind of average of the sales so far but, speaking as an accountant, it'd be a bit rubbish. For instance, look at the Samsung CLP-300 Toner. It sold 137 units in one week, completely out of the blue and completely out of kilter with the sales in the weeks before and after. It does it again a few weeks later. Why is that, and how can you predict when it's going to happen again?

Possible extra info...
You could look at last year's trading patterns to try and predict seasonal fluctuations.
You could check the order book for sales not made yet.
You could check the buying patterns of major customers.

For what you want to do, your existing data is insufficient.