2
$\begingroup$

To preface, I am a statistics novice, but I am faced with a problem that I cannot seem to be able to satisfactorily resolve. The problem is as follows:

I am working to forecast future sales for one company, "Company A", based on data for 18 months before they changed to a new sales strategy. By doing so, I am looking to find out how the new strategy changes overall sales.

Additionally, within my data-set, I also have a "Company B" which also sells the same 3 products. However, I do not know how well sales from Company B may be used to predict sales from "Company A".

Regarding the data, the numbers represent % sold based on a designated population to sell to in a given month. There are three columns for two sets. Each column is representative of a product and each set is representative of a company. Company A vs Company B. Company B has a full 36 months of data and Company A has 18 months under old sales tactics and 18 months under new sales tactics. Although they sell the same products, I am not sure if I can extrapolate meaningful values out of Company B. I would like to forecast the 18 months of data for company A under the old sales strategy which ended after month 18.

Summary:

-Goal: In order to discover what kind of savings Company A has made ever since making the change from their old sales strategy to their new sales strategy at the end of 18 months, I would like to project using the initial 18 months of data for Company A to obtain a hypothetical months 19-36 using that kind of trend.

-To help visualize what I mean by savings: we can say that company A makes $1,$2, or $3 per sale of product 1,2 or 3 and there are 100000 clients in the pool per month.

- Updated Idea: Upon finding a low correlation between companies A and B, I no longer believe I can use B's data to help predict for A. However I wonder if this is truly the case or if I am missing something about the analysis. In any case, what can I do with the information I have to project a hypothetical data for Company A for months 19-36 using months 1-18.

-Company A has 18 months of data before changing to new sales tactics and 18 months of data post sales strategy change.

-Company B has 36 months of data without any change of strategy.

-Both Company A and Company B sell the same 3 kinds of products (1,2,3).

-There is certainly a significant difference in product sales distribution right after the sales strategy change for Company A.

-Question: What are some methods, In excel, I can use to forecast sales for a product from Company A using the trend from the initial 18 months of data for that product.

Excel File with DataSet Example

The data are as follows:

                    Company A                        Company B        
Period Product 1  Product 2  Product 3  Product 1  Product 2  Product 3
 1      0.0897%    0.1629%    0.0354%    0.1169%    0.1948%    0.0545%
 2      0.0873%    0.1582%    0.0425%    0.1013%    0.1948%    0.0078%
 3      0.0944%    0.1534%    0.0519%    0.1324%    0.0779%    0.0467%
 4      0.0873%    0.1039%    0.0307%    0.1013%    0.1636%    0.0545%
 5      0.0803%    0.1416%    0.0378%    0.1013%    0.1870%    0.0467%
 6      0.1110%    0.1487%    0.0449%    0.1091%    0.1480%    0.0312%
 7      0.0897%    0.1180%    0.0496%    0.1169%    0.1948%    0.0234%
 8      0.1110%    0.1464%    0.0307%    0.1324%    0.1948%    0.0390%
 9      0.1015%    0.1582%    0.0307%    0.0623%    0.1948%    0.0390%
10      0.0944%    0.1629%    0.0283%    0.1169%    0.1246%    0.0857%
11      0.0968%    0.1487%    0.0307%    0.1324%    0.2181%    0.0234%
12      0.0921%    0.1582%    0.0590%    0.0623%    0.2259%    0.0312%
13      0.0897%    0.1487%    0.0472%    0.0857%    0.2026%    0.0390%
14      0.1275%    0.1818%    0.0307%    0.1013%    0.1246%    0.0390%
15      0.0873%    0.1369%    0.0307%    0.1324%    0.1558%    0.0545%
16      0.0897%    0.1841%    0.0449%    0.1091%    0.1246%    0.0701%
17      0.0614%    0.1511%    0.0236%    0.0779%    0.1870%    0.0234%
18      0.1015%    0.1416%    0.0307%    0.1013%    0.2415%    0.0312%
19      0.0472%    0.1558%    0.0331%    0.1714%    0.1948%    0.0390%
20      0.0614%    0.1723%    0.0354%    0.1558%    0.1402%    0.0234%
21      0.0472%    0.1558%    0.0496%    0.1091%    0.2493%    0.0156%
22      0.0661%    0.1653%    0.0283%    0.1714%    0.1714%    0.0467%
23      0.0614%    0.1440%    0.0307%    0.0857%    0.1636%    0.0312%
24      0.1110%    0.1534%    0.0425%    0.1246%    0.1636%    0.0467%
25      0.0873%    0.1440%    0.0331%    0.0857%    0.1324%    0.0623%
26      0.0732%    0.1440%    0.0449%    0.0857%    0.1091%    0.0623%
27      0.0732%    0.1346%    0.0496%    0.0857%    0.1324%    0.0234%
28      0.0637%    0.1747%    0.0449%    0.0857%    0.1870%    0.0312%
29      0.0425%    0.1275%    0.0472%    0.1169%    0.2259%    0.0312%
30      0.0708%    0.1416%    0.0449%    0.1013%    0.1013%    0.0545%
31      0.0708%    0.1204%    0.0519%    0.0779%    0.1480%    0.0312%
32      0.0850%    0.1747%    0.0449%    0.0857%    0.1948%    0.0234%
33      0.0873%    0.1487%    0.0519%    0.0935%    0.1636%    0.0623%
34      0.0779%    0.1275%    0.0331%    0.1013%    0.1558%    0.0701%
35      0.0850%    0.1629%    0.0543%    0.1246%    0.1792%    0.0390%
36      0.0850%    0.1629%    0.0331%    0.1013%    0.1402%    0.0467%
Improve this question
$\endgroup$
5
  • $\begingroup$ It's not clear what "accurate" and "effective" means specifically in this context; nor is it clear what specific aspects of Excel are relevant (please see the help center particularly in the paragraph that relates to Programming). Your second paragraph is a little unclear. Perhaps you can describe your specific requirements (including what kind of accuracy you seek and what effectiveness would mean). [I have edited to show the data in the question since it's only 36 rows, which will be useful if the file disappears -- but it might also be helpful for you to show a plot of the data.] $\endgroup$
    – Glen_b
    Jul 25, 2016 at 2:20
  • $\begingroup$ One other difficulty I have is the title and first paragraph refers to future values but later the body of your post seems to be talking about predicting observations in the past. Which 18 time periods are you trying to predict? $\endgroup$
    – Glen_b
    Jul 25, 2016 at 2:56
  • $\begingroup$ I am trying to predict sales numbers for products 1, 2 and 3 for Company A for time periods 19-36. This is using the trend from the initial 18 time periods for the relevant product from Company A. $\endgroup$
    – Magus Old
    Jul 25, 2016 at 3:11
  • 1
    $\begingroup$ My confusion was that -- given you have data for company A and B past period 18 -- time periods 19-36 are in the past, not the future. Do you envisage some particular relationship between what happens to B and what happens to A? (e.g. if you see the as mostly experiencing similar calendar effects like seasons, that would have them move together, while a major impact of competition between them might have them move opposite ways; might they be related across time -- such as A lagging B for example) It would be good if you could clarify all the vagueness as far as possible. $\endgroup$
    – Glen_b
    Jul 25, 2016 at 4:15
  • $\begingroup$ I was thinking of looking at a relation between Company A and B, but after taking a look at them, there seems to be very little correlation between either of their data. So now in this case, what kind of methods can I use to try and forecast a hypothetical scenario for months 19-36 for Company A? $\endgroup$
    – Magus Old
    Jul 25, 2016 at 8:57

1 Answer 1

Reset to default
1
$\begingroup$

Since they're all small percentages, you could perhaps consider these variables on the log scale, but it won't make any real difference to my discussion.

There's little relationship between any of the variables that I can see.

There's little worth noting in autocorrelation and partial autocorrelations

Looking at cross-correlations, there's little correlation at other lags as well -- a few mildly significant at the 5% level but no more than you'd expect. The only one I'd lend even the possibility of credence to is that on product 2, Company A has a weak correlation at lag 1 with B, but it's so weak it may still be noise.

You would probably not go far wrong in just treating these as noise about a mean level.

In which case you might want to consider either a step change or a jump followed by a geometric fading out between period 18 and 19 (pick whichever you would have expected beforehand, and just check that), but neither will be strong enough to really show anything much.

So anyway, my prediction for A in periods 19-36 would just be the straight prior average.

[I might just consider using an exponentially-weighted moving average that does lots of smoothing (low $\alpha$), but it wouldn't do much different from just taking the average. It would be necessary to scale the weights properly to account for how short the series is]

Improve this answer
$\endgroup$
4
  • $\begingroup$ Compared to the complete prior moving average (18 months), would it be better to only use the last 12 months since its the newest data? $\endgroup$
    – Magus Old
    Jul 25, 2016 at 11:04
  • $\begingroup$ @Mag For prediction I see no more benefit to it that I do with an exponentially weighted moving average (which focuses on the newest data in a different way) -- if you want to use a 12 month window, go right ahead, but there's nothing that really indicates there'd be much gain in it unless you expect more mean-shifting that is evident from the data. It might offer a little security if you're worried the future will change more quickly - there's no real problem in doing so. One advantage over exponential weights is a prediction interval is easier to generate, if you have a need for an interval. $\endgroup$
    – Glen_b
    Jul 25, 2016 at 11:08
  • $\begingroup$ Would it be statistically sound to average the first set of jan-june (months 1-6) with the second set of jan-june (months 13-18) to form a single set of jan-june to forecast with? (for use in tandem with the months 7-12 data, for a 12 month moving average). $\endgroup$
    – Magus Old
    Jul 25, 2016 at 20:03
  • $\begingroup$ I'm not sure what "statistically sound" means in this context; but if you want to use all the data in the average why not average all of the data? $\endgroup$
    – Glen_b
    Jul 26, 2016 at 1:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.