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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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"
<[email protected]> 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" <[email protected]> wrote in message
>news:[email protected]...
>> 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
>>
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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"
> <[email protected]> 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" <[email protected]> wrote in message
>>news:[email protected]...
>>> 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