#Forecast Sheet Excel 2013 Series On Our

Forecast Sheet Excel 2013 Series On Our

Polynomial вЂ“ Approximating a Polynomial function, a.x^n + b.x^(n-1) + c.x^(n-2) + вЂ¦ + m = 0 StatsLinest can also return a number of statistics when Stats parameter is set to TrueEg: =LINEST(C47:C51, B47:B51, TRUE, TRUE) Ctrl Shift EnterThis must be entered as an array formula of 2 columns by 5 rowsThe formula can also be entered as a normal equation also using the Index function to extract the array valuesEg: = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), Row ,Column)If you want to know the r2 value (discussed later) it is in the 3 rd row, 1 st column.Eg: = INDEX( LINEST($C$47:$C$51, $B$47:$B$51, TRUE, TRUE), 3 , 1)The above table shows the statistic and the value for our example above using both array entered and Index formulasThe r2 parameter highlighted will be discussed later.The Trend function is used to calculate a straight line best fit line based on a number of known XВ & Y values.Values of Y can be calculated for values of X inside or outside the know range of X values and so Trend can be used to interpolate or extrapolate data.Eg: = INTERCEPT (known Y values, known X values, New X Value, Const)Const = True Calculate the Intercept valueIf for example you are using this to model your power cost.If you have a fixed monthly cost plus a cost per kW, you would set Const to TrueIf you have no fixed monthly cost and are only charged per kW set Const to falseEg: =TREND($C$101:$C$105,$B$101:$B$105,B106,TRUE)The Forecast function is used to calculate a straight line best fit line based on a number of known XВ & Y values.Eg: = FORECAST (New X Value, Known Y values, Known X values)= FORECAST(B129, $C$124:$C$128, $B$124:$B$128)So far our examination of trends has revolved around the use of linear estimates and the Excel functions that support that.But as we saw above there are lots of cases where non-linear estimates are required.This section will deal with the following estimate types. SlopeThe Slope function returns the slope or gradient of the linear regression line through data points in Known_YвЂ™s and Known_XвЂ™s.Eg: =SLOPE(Known Y values, Known X values) InterceptThe Intercept function calculates the point at which a linear regression line will intersect the Y-axis by using existing X-values and Y-values.Eg: = INTERCEPT (Known Y values, Known X values) UseTo use the above 2 equations we simply enter 2 equations in cellsWe can now use our revised linear equation to plot a line of best fitWhich we can plot as a new series on our chartThe Linest function can be used to calculate the Slope and Intercept parameters for a linear functionLinest is an array formula which must be entered as an array formula to return all the values that it can return.Eg: = LINEST(Known Y Values, Known X Values,Const , Stats)=LINEST(C47:C51,B47:B51,TRUE,FALSE) will return the Slope (m) component of the equationStats = True Return additional regression statisticsFalse Return the m co-efficient and const bTo return both components you must enter the same formula in adjacent cells in the same rowAnd the equation must be entered as an array formulaEg: = LINEST(C47:C51, B47:B51, TRUE, FALSE) Ctrl Shift EnterAlternatively the values can be retrieved from the Linest array function using the Index functionGradient m =INDEX(LINEST(C47:C51, B47:B51, TRUE, FALSE),1)Intercept c =INDEX(LINEST(C47:C51, B47:B51, TRUE, FALSE),2)The use of the Index function negates the requirement to use an Array Entered formula. Excel Functions and ToolsExcel has a number of Worksheet functions specifically designed to assist us with analysing various trends.In the first Post we looked at using a linear equation in the form Y=mX + c to express our estimated line of best fit which we manual estimated was linear.Excel has 2 functions which we can use to calculate the actual slope (m) and intercept (c) for the above equation. Means multiply eg 10.2.M.X = 10.2 * m * x Why do we need to use Excel Functions?In the first post we looked at some simple data with only a few points and a trend that was very fairly obvious or was it.A number of other linear trends could have equally been used and all look about right.However in real life data is rarely this simple.Fortunately Excel has a Number of Functions and Tools that allow us to look for trends and use the data natively for forecasting purposes.There are a number of standard types of trends which can be classified as:Polynomial вЂ“ Approximating a Polynomial function to a powerLogarithmic вЂ“ Approximating a Logarithmic lineExponential вЂ“ Approximating an Exponential lineExcel supports the use of these trend types in a number of ways.

Logarithmic FunctionsLogarithmic functions are based around the formula y = b.LN(x)+aExcel doesnвЂ™t have a specific function dealing with Logarithmic functions, however we can use the Linest function as previously described by first converting the data from a Logarithmic to Straight line and this is done by talking the LN of the X values.Form: = LINEST( Known Y Values, LN(Known X Values))Eg: = LINEST( LN(C32:C39), B32:B39) as an array formulaThis is shown in a worked example on the Logarithmic Functions section of the Non-linear Functions page of the example workbook attached.Power functions are based around the formula y = a.x^bExcel doesnвЂ™t have a specific function dealing with Power functions, however we can again use the Linest function as previously described by first converting the data from a Power function to Straight line and this is done by talking the LN of the X and Y values. Exponential вЂ“ Approximating an Exponential line, y = b.m^xLuckily Excel has a number of function and some tools to assist us here as well.Exponential functions are based around the formula y = b.m^xExcel has one function specific to growth estimates and that is the Logest function.As with Linest, Logest is an array function.Eg: =LOGEST(Known YвЂ™s, Known XвЂ™s, Const, Stats)=LOGEST(C6:C13, B6:B13, true, false) Ctrl Shift EnterConst = True or omitted b parameter is calculatedStats = True Return additional regression statistics in an arrayAlternatively the values can be retrieved from the Logest array function using the Index functionB = INDEX( LOGEST( C6:C13, B6:B13, True, False), 1)X = INDEX( LOGEST(C6:C13, B6:B13, True, False), 2)The use of the Index function negates the requirement to use an Array Entered formula.1However Logest, is a tricky function as it actually just passes values to the Linest function!So we can actually use the Linest function for doing nearly all of our Exponential, Logarithmic and Power function trends.But you ask вЂњDoesnвЂ™t Linest give us the parameters for a straight line?вЂќTo use Linest to analyse an Exponential function we need to unwrap it so to speak and that is done by taking the Log of the Y values prior to putting them into the Linest equation, like this:Form: = LINEST( LN(Known Y Values), Known X Values)Eg: = LINEST( LN(C32:C39), B32:B39) Which is an array formulaOr = INDEX( LINEST( LN(C32:C39), B32:B39), 1) as a normal formulaNow the tricky part is that the m component or array parameter 2 must now be converted back to an exponential so we can use exp(m component) or =EXP( INDEX( LINEST( LN(C32:C39), B32:B39),2))This is difficult to explain but is shown in a worked example on the Exponential Functions section of the Non-linear Functions page of the example workbook attached.The Growth function can be used to calculate an exponential curve that best fits your data based on a number of known X & Y values.Form: = LINEST(Known Y Values, Known X Values, New X Values)Eg: = GROWTH($C$32:$C$39, $B$32:$B$39, B40) as a normal formulaThis is also shown in a worked example on the Exponential Functions section of the Non-linear Functions page of the example workbook attached. Logarithmic вЂ“ Approximating a Logarithmic line, y = b.ln(x) + a