turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Analytics
- /
- Stat Procs
- /
- Replicating a MA(1) forecast in Excel

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-11-2014 04:22 PM

This is seamingly a trivial question, but several have looked at my question and are also perplexed. I created a simple moving average prediction based on 100 data points in JMP. I would like someone's assistantce in replicating, in Excel, the forecast provided by JMP using the coefficients provided by JMP. Writing out the equation using actual numbers would be perfect for my needs. I have looked for sources with more than generalized formulas for hours now. The coefficients are as follows:

Term | Lag | Estimate | Std Error | t Ratio | Prob>|t| | Constant Estimate |

MA1 | 1 | -0.99849 | 0.433763 | -2.3 | 0.0235 | 0.179165 |

Intercept | 0 | 0.179165 | 0.248344 | 0.72 | 0.4724 |

The first 10 data points, forecast and residuals are provided below:

Observation | Actual Y | Predicted Y | Residual Y |

1 | 1.2917 | 0.1792 | 1.1125 |

2 | 3.5004 | 0.7354 | 2.7649 |

3 | 2.1595 | 2.0225 | 0.1370 |

4 | 2.2476 | 0.2819 | 1.9657 |

5 | 2.2791 | 1.7517 | 0.5274 |

6 | 2.3501 | 0.6187 | 1.7315 |

7 | 1.2491 | 1.6633 | -0.4141 |

8 | 0.9596 | -0.1832 | 1.1428 |

9 | 1.2070 | 1.1950 | 0.0120 |

10 | 0.1668 | 0.1899 | -0.0231 |

Could someone provide the math using the numbers in the tables that yields the Predicted Y for Observation 5 and 9 as an example? Thank you!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-11-2014 09:01 PM

You should post this in the statistics portion of the forum

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-12-2014 04:40 AM

I'm not a JMP-User and can offer only a part of the answer (at best). If you put your 1st 10 observations into proc arima:

Data A;

Input Observation Actual;

Datalines;

1 1.2917

2 3.5004

3 2.1595

4 2.2476

5 2.2791

6 2.3501

7 1.2491

8 0.9596

9 1.2070

10 0.1668

;

Run;

Proc ARIMA Data=A;

Identify Var=Actual;

Estimate q=1;

Forecast Lead=0 Out=Result_ARIMA (Keep=Actual Forecast Residual);

Run;

The Result is: My=1.67681, MA1=-0.25377 and

Actual Forecast Residual

1.2917 1.6768075816 -0.385107582

3.5004 1.5790802232 1.9213197768

2.1595 2.164373954 -0.004873954

2.2476 1.6755707359 0.5720292641

2.2791 1.8219693795 0.4571306205

2.3501 1.7928119662 0.5572880338

1.2491 1.8182285508 -0.569128551

0.9596 1.5323818872 -0.572781887

1.207 1.5314547933 -0.324454793

0.1668 1.594471862 -1.427671862

The results calculate as I think they should: 1.579=1,6768-(-0.25377)*-0.3851, etc.

If you double-check your JMP-Input?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-16-2014 09:19 PM

Thank you. I'm getting different predicted values from JMP, which likely is the source of my confusion, no doubt caused by me.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-16-2014 09:22 PM

Can you post the full data so someone can replicate the results/model?