Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Solver background in forecasting modeling
Hi!
I am modeling forecasting functions in MS Excel. In case of weighted average, exponential smoothing and Holt Winter's methods one has to optimise the coefficients alpha, beta or gamma using any error calculations such as MAE, MSE, RMSE or MAPE. Has anybody give a thought about mathematical background of such a optimization with Solver such as: - which is better to optimize MAE or MSE? - in the first case we are talking about linear optimization in the second non linear? - Anybody draw error function for MAE or MSE and tried to describe solver optimization function in Coordinate System for both functions? I am also open for cooperation with anyone who is involved in business forecasting training!!! Regards Rado |
#2
|
|||
|
|||
Rado -
Some quick thoughts: If the difference in cash flow (the outcome from a future decision based on the forecast) is proportional to the "error" (where error is the difference between actual and forecast), use MAE. If the difference in cash flow is proportional to the square of the error, use MSE. In this sense, MAE is a V-shape, and MSE is a U-shape. Both are nonlinear; MAE is piecewise-linear. In practice, you may obtain very similar results for MAE, MSE, RMSE or MAPE. It may be more important to "look at the data" first using a time-sequence plot to detect seasonality and outliers before selecting a forecasting method. - Mike www.mikemiddleton.com "RL" wrote in message ... Hi! I am modeling forecasting functions in MS Excel. In case of weighted average, exponential smoothing and Holt Winter's methods one has to optimise the coefficients alpha, beta or gamma using any error calculations such as MAE, MSE, RMSE or MAPE. Has anybody give a thought about mathematical background of such a optimization with Solver such as: - which is better to optimize MAE or MSE? - in the first case we are talking about linear optimization in the second non linear? - Anybody draw error function for MAE or MSE and tried to describe solver optimization function in Coordinate System for both functions? I am also open for cooperation with anyone who is involved in business forecasting training!!! Regards Rado |
#3
|
|||
|
|||
Hi Mike.. thanks for your thoughts. Regarding Excel Solver and MSE/MAPE optimization: " The Solver works better with MSE than MAPE (mean absolute percent error) beacuse MSE is a "smooth" function of w whereas MAPE is not. With MAPE Solver may be stuck at a local minimum and miss global minimum.... D. Aczel, J. Sounderpandian: Business Statistics, page 624.. Need to find or make this explanation in coordinate system.. Rado On Fri, 25 Mar 2005 11:52:10 -0800, "Michael R Middleton" wrote: Rado - Some quick thoughts: If the difference in cash flow (the outcome from a future decision based on the forecast) is proportional to the "error" (where error is the difference between actual and forecast), use MAE. If the difference in cash flow is proportional to the square of the error, use MSE. In this sense, MAE is a V-shape, and MSE is a U-shape. Both are nonlinear; MAE is piecewise-linear. In practice, you may obtain very similar results for MAE, MSE, RMSE or MAPE. It may be more important to "look at the data" first using a time-sequence plot to detect seasonality and outliers before selecting a forecasting method. - Mike www.mikemiddleton.com "RL" wrote in message .. . Hi! I am modeling forecasting functions in MS Excel. In case of weighted average, exponential smoothing and Holt Winter's methods one has to optimise the coefficients alpha, beta or gamma using any error calculations such as MAE, MSE, RMSE or MAPE. Has anybody give a thought about mathematical background of such a optimization with Solver such as: - which is better to optimize MAE or MSE? - in the first case we are talking about linear optimization in the second non linear? - Anybody draw error function for MAE or MSE and tried to describe solver optimization function in Coordinate System for both functions? I am also open for cooperation with anyone who is involved in business forecasting training!!! Regards Rado |
#4
|
|||
|
|||
Rado -
I don't have the Aczel book available. But the quote is directly related to what I said in my message: a squared-error function (U-shape) is smooth, an absolute-error function (V-shape) is not. I don't know what you mean by "explanation in coordinate system." Perhaps you want to plot MSE or MAPE as the the Y variable versus a parameter (w ?) or error as the X variable. Do you need instructions about creating the data or creating an XY (Scatter) plot? To create the data, hold all other parameters and variables constant, change only the single input that you want on the X axis, and use a worksheet formula of MSE or MAPE to get values for the Y axis. My forecast is that you'll see either a U-shape or a V-shape. - Mike "RL" wrote in message ... Hi Mike.. thanks for your thoughts. Regarding Excel Solver and MSE/MAPE optimization: " The Solver works better with MSE than MAPE (mean absolute percent error) beacuse MSE is a "smooth" function of w whereas MAPE is not. With MAPE Solver may be stuck at a local minimum and miss global minimum.... D. Aczel, J. Sounderpandian: Business Statistics, page 624.. Need to find or make this explanation in coordinate system.. Rado On Fri, 25 Mar 2005 11:52:10 -0800, "Michael R Middleton" wrote: Rado - Some quick thoughts: If the difference in cash flow (the outcome from a future decision based on the forecast) is proportional to the "error" (where error is the difference between actual and forecast), use MAE. If the difference in cash flow is proportional to the square of the error, use MSE. In this sense, MAE is a V-shape, and MSE is a U-shape. Both are nonlinear; MAE is piecewise-linear. In practice, you may obtain very similar results for MAE, MSE, RMSE or MAPE. It may be more important to "look at the data" first using a time-sequence plot to detect seasonality and outliers before selecting a forecasting method. - Mike www.mikemiddleton.com "RL" wrote in message . .. Hi! I am modeling forecasting functions in MS Excel. In case of weighted average, exponential smoothing and Holt Winter's methods one has to optimise the coefficients alpha, beta or gamma using any error calculations such as MAE, MSE, RMSE or MAPE. Has anybody give a thought about mathematical background of such a optimization with Solver such as: - which is better to optimize MAE or MSE? - in the first case we are talking about linear optimization in the second non linear? - Anybody draw error function for MAE or MSE and tried to describe solver optimization function in Coordinate System for both functions? I am also open for cooperation with anyone who is involved in business forecasting training!!! Regards Rado |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver Constraints | Excel Discussion (Misc queries) | |||
Excel aficionado wants to learn Access | Excel Discussion (Misc queries) | |||
Excel aficionado wants to learn Access | Excel Discussion (Misc queries) | |||
Excel user desires to learn ABC of Access | Excel Discussion (Misc queries) | |||
OPENING TWO DIFFERENT EXCEL PROGRAMS | Excel Discussion (Misc queries) |