3 Replies Latest reply on Nov 10, 2014 2:23 PM by JimMac

    Trend Analysis

    Sam_1

      Title

      Trend Analysis

      Post

      Does FileMaker has any in-build or custom function that duplicates excel TREND function (link below)

      http://office.microsoft.com/en-us/excel-help/trend-function-HP010062548.aspx

      Thank you,

       

        • 1. Re: Trend Analysis
          JimMac

          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

            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

              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 re-parse 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.surprise

              Sorry no easy answer.

              Jim...