fjkilken
asked on
Statistical model validation/advise needed
Hi
I have 2 different methods which are used to forecast future product returns, the problem is that the answers from each method are drastically different - ie; one method is nearly double the forecast qty of the other (4966 vs 2689), and I'm looking for some advice on which is the most appropriate or "statistically correct" (if there is such a thing).
See attached file to better understand the methods used, and their results.
The data that is involved is Sales and Returns of a product across the product timeline.
The product timeline refers to the date from when the product was first sold, to the date when the warranty runs out on that first sale.
The warranty period for the product is eg; 36 months - so 36 months after the first sale is made, the warranty expires and the product is no longer in the Warranty Install Base (WIB).
The WIB is cumulative sales from the first sale of the product until the warranty runs out.
The first method uses the returns per month against the WIB (for that month) to provide the monthly return rate.
The second method uses the cumulative returns per month against the WIB (for that month) to provide the cumulative return rate.
The first method:
Assuming we are running the report in May, n order to project/predict the return rate for June, the report looks at the average return rate for the previous 12 months, and uses this average as the return rate for June.
Then, to predict the return rate for July, we use the average of return rate for June to 11 months prior to June (ie; 11 months of historical returns rate plus the previously forecasted rate for June)
This method rolls forward using a combination of both historical and forecasted rates, and 12 months after the date of the last historical return rate - purely forecasted rates, until the last date, which is the end of the WIB.
The second method:
This method uses Excel linear regression in order to predict future return rates (I have used the FORECAST formula in Excel for this purpose which provides the same answer)
A key difference here is that it uses cumulative returns per month (as opposed to monthly returns per method one)
The Excel linear regression is used on the historical data (ie data prior to May) to predict future return rate (and thus a future return quantity)
Appreciate any advice you can provide!
Fergal
I have 2 different methods which are used to forecast future product returns, the problem is that the answers from each method are drastically different - ie; one method is nearly double the forecast qty of the other (4966 vs 2689), and I'm looking for some advice on which is the most appropriate or "statistically correct" (if there is such a thing).
See attached file to better understand the methods used, and their results.
The data that is involved is Sales and Returns of a product across the product timeline.
The product timeline refers to the date from when the product was first sold, to the date when the warranty runs out on that first sale.
The warranty period for the product is eg; 36 months - so 36 months after the first sale is made, the warranty expires and the product is no longer in the Warranty Install Base (WIB).
The WIB is cumulative sales from the first sale of the product until the warranty runs out.
The first method uses the returns per month against the WIB (for that month) to provide the monthly return rate.
The second method uses the cumulative returns per month against the WIB (for that month) to provide the cumulative return rate.
The first method:
Assuming we are running the report in May, n order to project/predict the return rate for June, the report looks at the average return rate for the previous 12 months, and uses this average as the return rate for June.
Then, to predict the return rate for July, we use the average of return rate for June to 11 months prior to June (ie; 11 months of historical returns rate plus the previously forecasted rate for June)
This method rolls forward using a combination of both historical and forecasted rates, and 12 months after the date of the last historical return rate - purely forecasted rates, until the last date, which is the end of the WIB.
The second method:
This method uses Excel linear regression in order to predict future return rates (I have used the FORECAST formula in Excel for this purpose which provides the same answer)
A key difference here is that it uses cumulative returns per month (as opposed to monthly returns per method one)
The Excel linear regression is used on the historical data (ie data prior to May) to predict future return rate (and thus a future return quantity)
Appreciate any advice you can provide!
Fergal
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER