
1. Re: Trend Analysis
JimMac Nov 10, 2014 7:18 AM (in response to Sam_1)You have a bit of a problem with this TREND spreadsheet function as a Custom Function in FMP. Although not impossible, it would be better solved using scripts or summary types.
The reason...
The Trend function requires x,y paired data and FMP cannot point to a series of records in a Custom Function.
Jim...

2. Re: Trend Analysis
Sam_1 Nov 10, 2014 10:24 AM (in response to Sam_1)I am not sure how can I develop a script for it. I tried using following custom function [LinearTrend ( )] but got wrong results when compared to excel
http://www.fmfunctions.com/fid/135
I have sales data for 2011, 2012 and 2013 and based on those sales data I am trying to forecast 2014 (using simple linear trend)

3. Re: Trend Analysis
JimMac Nov 10, 2014 2:23 PM (in response to Sam_1)I did a quick review of that referenced custom function. The Author did a clever trick using Value Lists to build the X and Y data. She had to parse and reparse the data set to use the SUM and other data group functions in FMP. If you note she had another custom function needed to work the main function.
http://www.fmcustomfunctions.com/fid/118
Both should work to do a Least Squares linear curve fit. The function's output may be the problem. It appears that the output is also a Value List that should have the A and B coefficients of the line function y= A +Bx, with x=years and y=forecast.
Lets talk math a bit....
If you do Least squares on annual data you will get a value when x=0, y=A that may appear erroneous. What I think you need is called Extrapolation. I would read up using http://en.wikipedia.org/wiki/Extrapolation for linear extrapolation, which uses the same technique, but not Least Squares statistical analysis. Since your X difference is always 1 year ( Quarterly forecasts would be 4 per year etc.).
Deciding on what best suits your forecast should be made with modeling using math to help what the eye can't see. Al Gore used linear trends in 1998 to forecast Arctic temperature which failed miserably.
Lagrange method is short term best in most business models and add a bit of "Risk Analysis" in case your forecast fails.
Back to your question...
A simple solution is just use the slope or change in Y over a year. Average that change over 2 years or the tendency of the tendency.
Sorry no easy answer.
Jim...