Forecasting with irregularity

[scruz9]

Hi All,

I would love to get your insight on the best way to project future values. Please take a look at the attached file.

Product Type 1 is 2 Gold bracelets. We offer 2 unique designs every 2 weeks (as denoted by Row 10) and they each have their own respected price (typically within a few dollars of each other). Row 7 is the average revenue earned based on sales. And Row 4 is the % of total Gold Bracelets Sold/ Total Bracelet.

As you can see, the first week we introduce the new bracelets, it has a higher buy rate than week 2. Right now, I am forecsating just using an average of actuals, but it has caused errors for me as it ticks down the 2nd week.

Product Type 2 (Cloth Bracelet) applies the same logic as Product Type 1, but the price is ALWAYS consisent. $4.

Would love to hear the best way to model this and figure out the product relationship? Would I use a correlation function?

[loginjmor]

Hi -

I'm not sure this is really an Excel question. I just graphed Row 7 versus time (Row 3) and there isn't really a discernable pattern other than the linear trend is generally downward. Part of the problem is I'm not sure you are looking at the right data. Using weekly product revenue versus time (the graph I made) doesn't really show much of a trend. This is further complicated by the fact you change the offering every two weeks. So that's a third variable. I'm not sure the comparison is apples to apples. About all I can tell from the data provided is about half the time (5/9) the second week of a particular bracelet pair performs as good, or better as the first week.

I guess if you always offer these same bracelet pairs in the same order, you could do a linear regression on your data versus time which will give you the equation of a line that best fits your data. But again, I'm not sure that is the best data to use as a predictor. What is driving or hurting sales? Are all weeks exactly the same? Are there holidays in some of those weeks? Did you run any advertising specials in some of those weeks? Were there discounts offered? etc. If you can quantify those items, perhaps you can smooth the data a bit. Again, this gets more into statistics and market forecasting than Excel.

Just my opinion, but I'm only an engineer, not a market analyst.