Excel courses

Forecasting and data analysis with EXCEL


Télécharger Forecasting and data analysis with EXCEL

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


 

MICROSOFT EXCEL 2007-2010

FORECASTING AND DATA ANALYSIS

   

Unless otherwise stated, screenshots in this book were taken using Excel 2007 with a blue colour scheme and running on Windows Vista. There may, therefore, be minor visual differences if you are using a different colour scheme, using Excel 2010, or if you are running on a different version of Windows.

Separate screenshots and instructions are given where there is a significant difference between the versions or the operating systems. In all other cases concepts, discussions, procedures and functionality are the same.

           

CONTENTS

 

 

In this lesson, you will learn how to:

•    Work with Linear Regression o Use the Forecast function o Use the Trend function o Use the Slope and Intercept Function

•    Work with Exponential Regression o Use the Growth function

•    Use Exponential smoothing

•    Use Data Analysis Tool Exponential Smoothing

•    Use naïve forecasting

•    Use Moving Average to forecast

•    Use weighted Moving Average to forecast

•    Use the Data Analysis tool Moving Average

•    Work with Seasonal Forecasting

 

TERMS  

Discussion

Forecasting is required in many situations to be able to do effective and efficient planning. A future electricity demands forecast requires to planning building new power stations; scheduling staff in a call centre next week requires forecasts of call volume; just imaging how much forecasting the planners behind London 2012 Olympic Games must have done. Forecasts can be required years in advance, or only a few minutes beforehand. Whatever the circumstances or time horizons involved, accurate forecasts are essential part of planning.

Often in forecasting, a key step is knowing when something can be forecast accurately, and when forecasts are no better than tossing a coin. Good forecasts capture the genuine patterns and relationships which exist in the historical data, but do not replicate past events that will not occur again. 

In all environments where numbers are collected and people make use of these numbers the ability to forecast or extrapolate data may be required. It doesn’t really matter if you are talking about sales figures, expenses, man-hours, growth, market shares etc. to create a budget you need to forecast. To be able to forecast in Excel you must have historical data. In this workbook time series data is used for the forecasts. The periods can be quarters, months, minutes, hours, and years. The predictability of an event or a quantity depends on several factors including:

?

How well you understand the factors that contribute to it;

?

How much data are available;

?

Whether the forecasts can affect the thing you are trying to forecast.

It is not possible to forecast everything. It is easy to forecast the time of the sunrise tomorrow, but it is impossible to forecast tomorrow’s lottery numbers and difficult to forecast, who is going to win this year’s European Song Contest

Can you trust a forecast created using Excel?

In Excel you have different methods to calculate a forecast, but Excel can only base the calculations on known data. You can use What-if analysis tools to manipulate your forecasts based on knowledge you have about the future (you will get a new product line, you have to close down 5 stores, you will spend money on marketing). 

In forecasting you are going to look at the trends that the data have and use these trends to help forecast future values or values outside the measured data. The trends can also be used to infill data where data is missing in the collected data. You will also forecast seasonal data and also look at data which are not suitable for forecasting.

Page

 

REGRESSION

Discussion

Linear Regression analysis is a statistical technique for estimating the relationships among variables. In other words how do the sales figures change over time?

If the goal is prediction, or forecasting, linear regression can be used to fit a predictive model to an observed data set of y and x values or known actual data (y) over time (x) (time series data). After developing such a model, if an additional value of x (a new period) is then given without its accompanying value of y, the fitted model can be used to make a prediction of the value of y. 

The sales figures (y) are known for a number of periods (x) it makes it possible forecasting sales (y) for future periods (x).

 

In Excel the linear regression can be calculated using the Forecast function, The Trend function, the Fill-Handle, by calculating the equation: Y = mX + c, and by adding a Trendline to a chart. 

The Data Analysis Tool Regression is an analysis tool to return important information working with linear regression such as the Slope, the Y-Interceptor, R-square, and other statistical useful information. The different terms will be explained later in this workbook.   

You can also calculate the Slope and the Y Interceptor using the functions Slope and Intercept.

 

HE FORECAST FUNCTION  

Discussion

Microsoft Excel provides a variety of functions you can use to calculate forecasts. One of them is the Forecast function. The Forecast function can calculate trend, a linear

Page


forecast, or linear regression for as many future periods needed based on known actual data from previous periods.  

The Forecast function consists of three required arguments, in the following order: X, Known_y’s, and Known_x’s. X is the period for which you want to calculate the forecast.  Known_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years).

 

1.    To use a Forecast function, first create a data range containing known factual data and responding periods.

2.    Click in the cell where you want to place the function.

3.    Click on the Formulas tab.

4.    In the Function Library group, click on the Insert Function button.

 

5.    In the Insert function dialog box, locate Statistical category in the Or    select a category: box.

6.    Click on Forecast.

7.    Click on the OK button.

 

8.    In the Function Arguments dialog box, click in the X box.

9.    Enter the cell reference for the cell with the period information.

10.   In the Known_y’s box, select the cells containing the known values. 

11.   Make the cell references absolute (Press F4 or add the $ signs).

12.   In the Known_x’s box, select the cells containing the known periods.

13.   Make the cell references absolute (Press F4 or add the $ signs).

14.   Click OK.

 

15.   Copy down the Forecast function to get the forecast for known periods and unknown periods.

 

The forecast function is built in the Fill handle. Select the known periods’ factual data and drag down the Fill handle and the Fill handle will forecast the unknown periods.

 

If you compare the result using the fill handle in the Sales column and using the Forecast function in the Forecast column you can see that both methods will return the same result for November and December. Exactly as the Forecast function the fill handle can predict as many future periods needed based on known actual data.

 

HE TREND FUNCTION

Discussion

The Trend can calculate trend, a linear forecast, or linear regression for as many future periods needed based on data from previous periods.  

The Trend function is an array function and the steps to use it are different from “normal” functions in Excel. The whole range where you want the result to be displayed must be selected and after the needed arguments have been entered the function dialog box the keys Ctrl Shift Enter must be pressed instead of pressing Enter. Then the function will return the result for the whole range in the selected range.

The Trend function consists of one required argument and three optional arguments, in the following order: Known_y’s, Known_x’s, New_x’s, and ConstKnown_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years). New_x’s is the array with future periods if the Trend function is used for forecasting. Const is a logical value specifying whether to force the constant c (the Y interceptor) to equal 0. In other words by forcing the Y interceptor to equal 0 the trendline will have a start from 0,0 ( X=0 and y=0).

Procedures

1.    To use a Trend function, first create a data range containing known factual data and responding periods.

 

2.    Select the range where you want the result of the function.

3.    Click on the Formulas tab.

4.    In the Function Library group, click on the Insert Function button.

 

5.    In the Insert function dialog box, locate Statistical category in the Or select a category: box.

6.    Click on Trend.

7.    Click on the OK button.

 

8.    In the Function Arguments dialog box, click in the Known_y’s  box.

9.    Select the cells containing the known values.

10.   In the Known_x’s box, select the cells containing the known periods.

11.   Press Ctrl Shift Enter.

The array functions will add the result to the whole selected array and will look different in the formula bar. Excel will display the formula enclosed in curly brackets {

}.

 

If the Trend function is used for forecasting:

Procedures

1.    To use a Trend function for forecasting, first create a data range containing known factual data and responding periods and add the periods for which you want to forecast.

 

2.    Select the range where you want the result of the function.

3.    Click on the Formulas tab.

4.    In the Function Library group, click on the Insert Function button.

 

5.    In the Insert function dialog box, locate Statistical category in the Or select a category: box.

6.    Click on Trend.

7.    Click on the OK button.

8.    In the Function Arguments dialog box, click in the Known_y’s  box.

9.    Select the cells containing the known values.

10.   In the Known_x’s box, select the cells containing the known periods.

11.   In the New_x’s box, select the cells containing the unknown periods.

12.   Press Ctrl Shift Enter.

 

&

The Trend function will return exactly the same result as the forecast function and as the Forecast function able to forecast as many future periods needed.

 

HE SLOPE AND INTERCEPT FUNCTION

Discussion

To make it easy to calculate the trend or forecast equation Y = mX + c you need the slope and y interceptor.

First the equation:

Y = mX + c

Where: 

Y is the unknown measure (the value you want to forecast)

X is the X value (the period in time series data) for which you want to know the value of Y Where:  

m is the gradient of the line or the slope

Where: 

c is the Y interceptor of the line (or Y value when there is no X value or X =0)

The slope equation looks like this:

The Slope function consists of two required arguments, in the following order: 

Known_y’s, and Known_x’s. Known_y’s  is the array with the known values. Known_x’s is the array with the known responding periods (must be a numeric range and not dates, months or years).

Procedures

1.    Select the cell where the result is to be displayed.

2.    Click on the Formulas tab.

3.    In the Function Library group, click on the Insert Function button.

4.    In the Insert function dialog box, locate Statistical category in the Or select a category: box.

5.    Click on Slope.

6.    Click on the OK button.

7.    In the Known_y’s box, select the cells containing the known values.

8.    In the Known_x’s box, select the cells containing the responding known periods.

 

The Intercept function consists of two required arguments, in the following order: 

Known_y’s, and Known_x’s. Known_y’s  is the array with the known values. Known_x’s is the array with the known responding periods (must be a numeric range and not dates, months or years).

Procedures

1.    Select the cell where the result is to be displayed.

2.    Click on the Formulas tab.

3.    In the Function Library group, click on the Insert Function button.

4.    In the Insert function dialog box, locate Statistical category in the Or select a category: box.

5.    Click on Intercept.

6.    Click on the OK button.

7.    In the Known_y’s box, select the cells containing the known values.

8.    In the Known_x’s box, select the cells containing the responding known periods.

9.    Click on the OK button.

 

The Slope and Y interceptor can now be used to calculate the trend and forecast using the trend/forecast equation:

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =( click in the cell with the slope. Press F4 to lock the cell reference.

3.    Type * click in the cell with the period number (x). Type )

4.    Type + click in the cell with y interceptor. Press F4 to lock the cell reference.

5.    Press enter.

 

The Forecast function, the Trend function and the trend/forecast equation will return exactly the same result. It does not really matter which method used. The equation can be used to calculate the forecast for as many future periods needed.

   

REGRESSION

Discussion

Sometimes the growth in a model is not linear, but it is exponential. If the growth is exponential Excel has forecasting tools to replace the Forecast and Trend function. 

In the example below the sales has an exponential growth rate. The Forecast function forecasts period 11 to 2005333.333. This is not a realistic forecast, because the known sales for period 10 are already £ 2,200,000.00.

 

In this section you will see how you can forecast an exponential growth.

HE GROWTH FUNCTION

Discussion

The Growth function can calculate exponential growth and an exponential growth forecast. The Growth function is an array function and the steps to use it are different from “normal” functions in Excel. The whole range where you want the result to be displayed must be selected and after the needed arguments have been entered the Growth function dialog box the keys Ctrl Shift Enter must be pressed instead of pressing Enter. Then the function will return the result for the whole range in the selected range.

The Growth function consists of one required argument and three optional arguments, in the following order: Known_y’s, Known_x’s, New_x’s, and ConstKnown_y’s is the array with the known values. Known_x’s is the array with the known periods (must be a numeric range and not dates, months or years). New_x’s is the array with future periods if the Growth function is used for forecasting. Const is a logical value specifying whether to force the constant c (the Y interceptor) to equal 0. In other words by forcing the Y interceptor to equal 0 the trendline will have a start from 0,0 ( X=0 and y=0).Procedures

1.    To use a Growth function, first create a data range with factual known data and responding periods.

 

2.    Select the array where you want the result of the function.

3.    Click on the Formulas tab.

4.    In the Function Library group, click on the Insert Function button.

 

5.    In the Insert function dialog box, locate Statistical category in the Or select a category: box.

6.    Click on Growth.

7.    Click on the OK button.

 

8.    In the Function Arguments dialog box, click in the Known_y’s  box.

9.    Select the cells containing the known values.

10.   In the Known_x’s box, select the cells containing the known periods.

11.   Press Ctrl Shift Enter.

 

The array functions will add the result to the whole selected array and will look different in the formula bar. Excel will display the formula enclosed in curly brackets {

}.

 

The Growth function procedures if it is used for forecasting:

Procedures

1.    To use a Growth function for forecasting first create a data range with factual known data and responding periods. Add the periods for the future you want to forecast.

 

2.    Select the array where you want the result of the function.

3.    Click on the Formulas tab.

4.    In the Function Library group, click on the Insert Function button.

 

5.    In the Insert function dialog box, locate Statistical category in the Or select a category: box.

6.    Click on Growth.

7.    Click on the OK button.

8.    In the Function Arguments dialog box, click in the Known_y’s  box.

9.    Select the cells containing the known values.

10.   In the Known_x’s box, select the cells containing the known periods.

11.   In the New_x’s box, select the cells containing the unknown periods.

12.   Press Ctrl Shift Enter.

 

If you compare the Growth function with the Forecast function in a chart it is obvious that in this example you get a much more accurate forecast using the Growth function.

 

SMOOTHING

Discussion

Exponential smoothing is another method to forecast actual time series data. The method uses a percentage of the previous period’s actual data and a percentage of the previous period’s forecast. The model can easily be adjusted to increase the accuracy when more actual data is known. When the model is created you need to decide a percentage (also called Alpha). May be you decide to set Alpha to 30%. Then the exponential smoothing forecast will use 30% of the previous period actual data and 70% of the previous forecast to forecast the next period. Because exponential smoothing is calculated from the previous period’s forecast you must guess the first forecast or use the actual known value as forecast value. Exponential smoothing can only predict one future period because you need the previous period’s actual known data.

Later you will see how you can use error measurement calculations and the Solver tool to increase the accuracy when you are working with exponential smoothing.

The math: F=forecast t=period

D=demand or actual known data

Ft+1=alpha * Dt + (1-alpha) * FtExample:

Alpha = 30%

Previous forecast = 9000

Previous actual known data = 8000

Ft+1=30% * 8000 + (1-30%) * 9000 = 8700

 

1.    Guess forecast for the first period or if you have data from the previous period use them as forecast for the previous period.

2.    Select the cell where the result is to be displayed.

3.    Type = select the cell with alpha. Type  *  select the cell with previous known actual data.

4.    Type + (1- select the cell with alpha value )* select the cell with last forecast.

5.    Press enter.

   

DATA ANALYSIS TOOL EXPONENTIAL SMOOTHING

Instead of doing the calculations yourself you can use the Data analysis tool Exponential Smoothing. You can find it by selecting the Data Analysis command from the Analysis group at the far right end of the data ribbon. Select Exponential Smoothing from the list and click OK.

If there is no Analysis group or Data Analysis command on the Ribbon, you must install Data Analysis TOOLPAK (please go to Appendix A in this workbook to see how to install Data Analysis TOOLPAK).

After you have entered all necessary information into the Exponential Smoothing dialog box, you can get the result in the output range. 

You can specify the following items in the Exponential Smoothing parameters dialog box:

Parameter

Description

Input Range

The array where you have the data you want to examine

Damping factor

The damping factor is (1 – alpha) so to get an alpha value of 30% (30% of the last period actual data and 70% of the last forecast) 0.7 or 70% must be entered

Labels

If labels are selected in the input range the box must be ticked

Output Range

Enter a cell reference for the output or New Worksheet Ply to get the output in a new worksheet in the workbook with the input data or New Workbook to get the output in a new workbook

Chart Output

Tick the box to get a chart output

        Standard Errors                   Tick the box to get calculated standard errors

 

When you have finished entering the information click OK and you will get the output.

Procedures

1.    To use the Exponential Smoothing tool, first click the Data tab.

2.    In the Analysis group, click the Data Analysis button.

3.    Select Exponential Smoothing from the list and click OK.

 

4.    Select the Input Range and select the desired options.

5.    Click OK

 

Let us have a look at the output. You got the exponential smoothing forecast not for the first period as mentioned before you must have data from the previous period to work with exponential smoothing. Then you got standard error calculated based on the average from the previous 3 periods. That is why the standard error results starts from period 5. The error calculations are very important to make sure that the forecast is as accurate as possible and to make sure that you are using the right forecasting model. Later in this workbook you will see examples calculating forecast errors. The chart option was selected so you also got a chart in the output showing the factual known data and the exponential smoothing trend.

Discussion

The Naïve Forecast definition:

Estimating technique in which the last period's actuals are used as this period's forecast, without adjusting them or attempting to establish causal factors. You are just using the last period’s figures. The naïve forecast is normally not very accurate but can be useful to understand and develop other forecasting models. Because the last period is used it is only possible to forecast one future period.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type = and select the actual value from the previous period

 

AVERAGE

Discussion

The Moving Average forecast is based on the average of known values from a number of periods. Let us first have a look at the math. If I want to forecast April using three months moving average the formula will look like this: 

"January" "February" "    " ”April”=

More scientific way at looking at the equation

At At-1 At-2 FT+1=

Where F is the forecast T the period A the actual value divided with three because you are using the previous three periods. 

The equation will look like this if you want a two period moving average: 

At At-1

FT+1=

The equation will look like this if you want a twelve period moving average: 

FT+1=At At-1 At-2 At-3 At-4 At-5 At-6 At-7 At-8 At-9 At-10 At-11 At-12

 

The two months Moving Average forecast is based on 2 months known values. It can be the two previous month’s values or it can be based on two months the year before whatever gives the most accurate forecast.

If you forecast one period using the average of the previous two periods you will only be able to forecast one future period. If you use the average of two periods one period back, you can forecast two future periods using this method etc  

In the example below (Moving Average (two periods) Previous two periods) the forecast for March is the average of January and February. You can forecast November but not December with this method. You need to know the November sales first. In the example (Moving Average (two periods) one period back) the forecast for April is also the average of January and February. Here you can forecast December because it is the average of September and October.

 

The three months Moving Average forecast is based on three months known values. You can use moving average forecast based on as many periods you need to get the most accurate forecast.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =average( 

3.    Type the argument, or select the cells you want to insert in the function (the values for the periods).

4.    Type ) closing bracket.

5.    Press enter.

 

MOVING AVERAGE  

Discussion

Another Excel forecasting option is the Weighted Moving Average. The Weighted Moving Average is nearly the same as the Moving Average except that it assumes that the most recent periods are the best predictors. Each period that is used for the forecast is given a weighting. The largest weighting is assigned to the most recent period. Each preceding period is successively less and less.

For example, during the calculation of a three-period Weighted Moving Average, the most recent three periods are used. The results from the previous period might be given the weight of three, the middle period might be assigned a weight of two, and the 3rd most distant period might be assigned a weight of one.

The formula for the Weighted Moving Average is as follows:

Forecast (period four) = ((weight of period three * Value of period three) + (weight of period two * Value of period two) + (weight of period one * Value of period one) / (weight of period three + weight of period two + weight of period one)

The example below:

Forecast April=((3*210)+(2*180)+(1*200))/(1+2+3)=(630+360+200)/6=1180/6=198.3

 

1.    Select the cell where the result is to be displayed.

2.    Type =((weight value*period value)+(next weight value*next period value))/sum of weight values.

3.    Press enter.

 

In the example above the weight for January, February, and March are typed in D5, D6, and D7, in D8 (the forecast for April) the formula.

&

The weight is often a percentage. May be 10% for period 1, 30% for period 2, and 60% for period 3. The formula will now look like this. Forecast (period 4) = ((weight of period 3 * Value of period 3) + (weight of period 2 * Value of period 2) + (weight of period 1 * Value of period 1).

 

DATA ANALYSIS TOOL MOVING AVERAGE

Discussion

In Excel you can also find a data analysis tool Moving Average. Select the Data Analysis command from the Analysis group at the far right end of the data ribbon. Select Moving Average from the list and click OK.

If there is no Analysis group or Data Analysis command on the Ribbon, you must install Data Analysis TOOLPAK (please go to Appendix A in this workbook to see how to install Data Analysis TOOLPAK).

The Data Analysis tool Moving Average is not a forecasting tool but a way to smooth data to easier spot the trends.

When you calculated moving average earlier in this book you did it for forecasting by using the equation: 

                                                                         "January" "February" "               "

”April”=

or

At At-1 At-2

FT+1=

 The equation for the data analysis tool Moving Average tool is:

"January"

”March”=

"February"

 

"

"  

or

     

A SDT= t

At-1 At-2  

   

After you have entered all necessary information into the Moving Average dialog box, you can get the result in the output range. 

You can specify the following items in the Moving Average dialog box:

Parameter

Description

Input Range

The array where you have the data you want to examine

Labels

If labels are selected in the input range the box must be ticked

Interval

Enter how many periods you want to use

Output Range

Enter a cell reference for the output or New Worksheet Ply to get the output in a new worksheet in the workbook with the input data or New Workbook to get the output in a new workbook

Chart Output

Tick the box to get a chart output

        Standard Errors                   Tick the box to get calculated standard errors

 

When you have finished entering the information click OK and you will get the output.

Procedures

1.    To use the Moving Average tool, first click the Data tab.

2.    In the Analysis group, click the Data Analysis button.

3.    Select Moving Average from the list and click OK.

 

The output

 

Let us have a look at the output. You got the Moving Average output not for the first 2 periods as mentioned before you must have data from the previous 2 periods to work with 3 months moving average. Then you got standard error calculated based on the average from 3 moving average periods. That is why the standard error results starts from period 5. The error calculations are very important to see how accurate the moving average tool is. Later in this work book you will see examples calculating errors. The chart option was selected so you also got a chart in the output showing the factual known data and the moving average result.

 

FORECASTING

Discussion

If you have seasonal actual data (each year you are selling more in December and June or another seasonal pattern) you can use seasonal forecasting to forecast future data. A weight can be added to the periods to increase the accuracy of the forecast.  If you use the Forecast function to calculate a linear forecast you can see periods over forecast and under forecast. In the example below you have seasonal known actual data.  The forecast for quarter 1 is above actual data, the forecast for quarter 2 is above actual data, the forecast for quarter 3 is below actual data, and the forecast for quarter 4 is above actual data. 

 

Add an index value (a weight) to each quarter to get a more accurate forecast. In this example the periods are quarters (4 periods groups). The sum of the index values must be 4 (12 if you are working with months etc.). 

The index value multiplied by the forecast value should be very close to actual data (the index for period 1 (quarter 1 2006) must be a value multiplied by 108.84 gives a result close to 100 (the actual known value)). Later you will see how you can use error measurement calculations and the SOLVER tool to increase the accuracy when you are working with seasonal historical data. In the example above the seasonal index for quarter 1 is set to 0.9, set to 0.7 for quarter 2, set to 1.5 for quarter 3, and set to 0.9 for quarter 4. 

To get the seasonal forecast you just have to multiply the seasonal index value by the Forecast function’s forecast and if you compare the actual data with the seasonal forecast you will see that the seasonal forecast is much more accurate than the Forecast function’s forecast.

Procedures

1.    Forecast the actual data using the Forecast function .

2.    Compare actual data with the Forecast function’s calculated forecast data period by period to spot any seasonal trends.

3.    Add a seasonal index which sum should be the same as number of periods (4 if you are working with quarters, 12 if you are working with months etc.).

 

4.    Add the seasonal index values to the rest of the quarters.

5.    Select the cell where you want the next index value for quarter 1 and type = click on the cell with the quarter 1 seasonal index.

 

6.    Drag the fill-handle down to copy and paste the seasonal index value to the rest of the quarters.

7.    Select the cell where the result is to be displayed.

8.    Multiply the Forecast function’s forecast with the seasonal index.

 

&

It can be difficult to spot a seasonal trend in actual data but even that you cannot it is a forecasting model which in many situations is more accurate than other models.

1.    Open the file Forecasting and data analysis.

2.    Open the Forecast Exercises sheet.

3.    Use the Forecast function in F12 to calculate the forecast for the first quarter 2007.

4.    Copy down the Forecast function to get the forecast for the rest of the quarters.

5.    Select the range G12:G35 and use the Trend function to calculate the trend for known actual values (when entered the function arguments remember it is an array function and you must press Ctrl Shift Enter).

6.    Select the range G36:G39 and use the Trend function to forecast the 4 quarters in 2013. Remember to enter the range B36:B39 as the Trend function argument New_x’s (when entered the function arguments remember it is an array function and you must press Ctrl Shift Enter).

7.    Select G10 and use the Slope function to find the slope.

8.    Select H10 and use the Intercept function to find Y-interceptor.

9.    Select H12 and calculate the equation for trend and forecast using the slope and Y-interceptor (the equation: Y = mX + c, m is the slope, c is the Yinterceptor, and X is the period).

10.   Save the file.


1.    Open the file Forecasting and data analysis.

2.    Open the Forecast Exercises sheet.

3.    Select the range Q12:Q27 and use the Growth function to calculate the growth for the known actual data for year 2007 to 2010 (when entered the function arguments remember it is an array function and you must press Ctrl Shift Enter).

4.    Select the range Q28:Q31 and use the Growth function to forecast the 4 quarters in 2011. Remember to enter the range L28:L31 as the Growth function argument New_x’s (when entered the function arguments remember it is an array function and you must press Ctrl Shift Enter).

5.    Compare the difference between column P (the Forecast function) and column Q (the Growth function).

6.    Save the file.

           

1.    Open the file Forecasting and data analysis.

2.    Open the Forecast Exercises sheet.

3.    Enter an alpha value of 50% in G41.

4.    Enter 120,000 in G45 (you must guess the first period’s forecast).

5.    Select G46 and enter the Exponential Smoothing forecast equation =$G$41*E45+(1-$G$41)*G45 (Ft+1=alpha * Dt + (1-alpha) * Ft).

6.    Copy down the equation to forecast period 25 (quarter 1 2013).

7.    Change the alpha value in G41 and observe the changes in the range G45:G69.

8.    Click the Data tab and click Data Analysis in the Analysis Group.

9.    Select Exponential Smoothing.

10.   Enter the input range $E$44:$E$68. Damping factor of 0.7 to get an Alpha value of 30%. Tick labels and output range should be $H$45. Tick Chart output and standard errors.

11.   Change Alpha in G41 to 30% and compare the two outputs.

12.   Save the file.

1.    Open the file Forecasting and data analysis.

2.    Open the Forecast Exercises sheet.

3.    Select Q46 and type =O45.

4.    Drag the fill-handle down to row 69 to copy down the cell content. This is the Naïve forecast which is based on the previous period.

5.    Select R48 and use the Average function to calculate the average of the previous 3 periods known actual figures (=average(O45:O47) and copy down the Average function to R69. You now have the 3 periods moving average forecast.

6.    Enter 10% in S41, enter 20% in S42, and 70% in S43. The sum of the weights must be 100%. You assume that the previous period’s actual known value is more important to the forecast than two and three periods back. 

7.    Select S48 and type =$S$41*O45+$S$42*O46++$S$43*O47.

8.    Copy down the calculation to S69.

9.    Examine the result. Change the weights in the range S41:S43, but remember that the sum must be 100%. You now have a 3 periods weighted moving average forecast.

10.   Save the file.

1.    Open the file Forecasting and data analysis.

2.    Open the Forecast Exercises sheet.

3.    Examine the values in the range E84:E107. Is there any seasonal patterns? Yes there is. Quarter 4 has the highest sales figures and quarter 3 the lowest. 

4.    Select G84 and enter the weight for the first period. You have to compare the sale in E84 and the forecast in F84. If the forecast is higher than the actual value the weight should be less than 1. The weight = Sales/Forecast function or G84=E84/F84.

5.    Enter the weights in G85, G86 and G87 (the sum of the weights must equals 4 the number of periods (12 if you are working with months)). Work with 2 decimals and round up or down to make sure that the sum equals number of periods here 4.

6.    Select G88 and type =G84 and copy down the cell to G111.

7.    Select H84 and type =G84*F84. 

8.    Copy down the calculation to H111 and you now have a seasonal forecast.

9.    Save the file.


Measuring Forecast Accuracy

 

In this lesson, you will learn how to:

•    Measure forecast accuracy 

•    Calculate error/deviation

•    Calculate absolute error/deviation

•    Calculate percentage error/deviation

•    Calculate absolute percentage error/deviation

•    Calculate square error

•    Calculate standard error

•    Calculate MAD or MAE (Mean Absolute Deviation or Error)

•    Calculate MSQ (Mean Square Error)

•    Calculate MPE (Mean Percentage Error)

•    Calculate MAPE (Mean Absolute Percentage Error)

•    Calculate TSE (Tracking Signal Error)  

 

Discussion

A key question in any forecasting is how to measure performance.  Such measures are very important when you select a forecasting method and create a forecasting model, since you may compare alternatives and choose the method with the highest level of accuracy. 

Then, once the method is being used on a regular basis, you need similar measures to tell you whether the forecasts are maintaining their accuracy.  If not you will need to get things back on track by putting improvements in place such as more timely data, better statistical methods and may be change forecasting method. To keep up getting more accuracy forecasts you should work with more than one forecasting method so you always can compare the accuracy. 

To measure how accurate the forecast is you can use different methods. To be able to compare different forecasting methods you will look at the mean errors but first you will calculate the errors. Later you compare the different forecast methods to find out which one is the most accurate for your data right now, because this can change over time so it is important to use more than one method and keeping up calculate the errors.

 

/DEVIATION

Discussion

The error or the deviation is the difference between actual value and forecasted value.

You can calculate the error or deviation by simple subtract the forecast from the actual. This will give a positive or negative result for each period or tell us if the forecast for each period is over or under actual value.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type = select the cell with the actual value type – (subtract) select the cell with the forecast value.  

3.    Press enter.

 

The calculation will tell us if our forecast is over or under actual value for each period.

 

/DEVIATION

Discussion

It is a good idea to calculate absolute error/deviation to get the right picture of the errors. To compare forecast methods the average error/deviation is very important. If you calculate the average from positive and negative errors the result will not be very useful. To calculate the average you need to summarise all the values and then divide the sum with the number of data. You can have negative and positive errors which will neutralise or even out each other and the average result will not tell us anything about the forecast accuracy. 

You need to calculate the absolute error/deviation to get a useful result in an average calculation. To calculate absolute values in Excel you can use the Abs function. The Abs function has only one argument and it is number. If you type a number negative or positive the function will always return is as an absolute (positive) value. 

To calculate the Absolute Error you need to type =abs(the actual value – the forecast value).

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =abs( select the cell with the actual value type – (subtract) select the cell with the forecast value ).

3.    Press enter.

 

The result of the absolute error/deviation calculation will show the error as a positive value not if the forecast is above or below factual data.

 

/DEVIATION

Discussion

It is always useful to get something like forecast accuracy shown as percentage and the percentage error can also help you to decide which forecasting method to use for your data.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type = select the cell with the error/deviation and divide it with actual value. 

3.    Press enter.

     

/DEVIATION

Discussion

You need the Absolute Percentage Error to be able to calculate Mean Absolute Percentage Error, which are very useful to compare forecasting methods.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type = select the cell with the absolute error/deviation and divide it with actual value 

   

Discussion

A popular way of looking at forecast accuracy is calculating square error. By squaring the deviation the errors will be more obvious and easy to spot. By squaring the deviation the result will always be absolute.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =( select the cell with the actual value – (subtract) the forecast and type ) then type ^ (shift 6) and type 2. 

3.    Press enter.

   

&

The SUMSQ function can also be used to calculate Square Error.

Type =sumsq(select the actual data cell – the forecast cell and type ).

In the example above =sumsq(C5-D5). 

 

Discussion

Standard error is a statistical standard for error calculations. You need to summarise  the squares of the difference between the three previous known actual periods and the three previous periods forecast and divide the result by three (number of periods) and calculate the square root of the result. You can use the SQRT function together with the SUMXMY2 function to calculate the standard error.

The SQRT function:

The SQRT function returns the square root of a number.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =SQRT( type a number or select a cell). 

3.    Press OK and the function will return the square root.

The SUMXMY2 function:

The Sumxmy2 function returns the sum of the square of the difference of two arrays. The function has two arguments Array_x  and Array_y. Enter the range for the first array in Array_x and enter the range for the second array in Array_y.

 

1.    Select the cell where the result is to be displayed.

2.    Type =SUMXMY2( in Array_x enter the range of the first array. In Array_y enter the range of the second range). 

3.    Press OK.

Use the SQRT function and the SUMXMY2 to calculate standard error

Select the cell where you want the result to be displayed. Start with the SQRT function.

 

Nest the SUMXMY2 function and select the two arrays (the previous three known values in Array_x and the previous three forecast values in Array_y).

 

Click OK and type “/3” after the SUMXMY2 function inside the SQRT function. Press enter.

 

1.    Select the cell where the result is to be displayed.

2.    Type =SQRT( SUMXMY2(select the range with the actual values. Type”,” select the range with the forecast)/3)

3.    Click OK. 

 

MAD OR MAE (MEAN ABSOLUTE DEVIATION OR MEAN ABSOLUTE ERROR)

Discussion

The MAD/MAE is a very important measurement to compare forecast methods. You need an overall result to compare methods. MAD/MAE is the average of the absolute deviation/error.

You can calculate MAD (Mean Absolute Deviation) or MAE (Mean Absolute Error) by using the average function.  

1.    Select the cell where the result is to be displayed.

2.    Type =average( select the range or enter the cell references for the range containing absolute error/deviation and type ) 

   

MSQ (MEAN SQUARE ERROR)

Discussion

MSQ (Mean Square Error) is a method to calculate forecast accuracy and to compare forecasting methods. MSQ is the average of the calculated Square Error. 

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =average( select the range or enter the cell references for the range containing absolute error/deviation and type )

 

MPE (MEAN PERCENTAGE ERROR)

Discussion

The MPE (Mean Percentage Error) is the average of the Percentage Error. Use the AVERAGE function to calculate MPE.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =average( select the range or enter the cell references for the range containing absolute error/deviation and type )

3.    Press enter.

 

MAPE (MEAN ABSOLUTE PERCENTAGE ERROR):

Discussion

The MAPE (Mean Absolute Percentage Error) is the average of the Absolute Percentage Error. Use the AVERAGE function to calculate MAPE.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =average( select the range or enter the cell references for the range containing absolute error/deviation)

3.    Press enter.

   

TSE (TRACKING SIGNAL ERROR)

Discussion

Once a forecast model is developed it should indicate if the actual values following the forecast, and indicate if any deviation in order to correct the model. TSE (Tracking Signal Error) is used to pinpoint forecasting models that need adjustment. As long as the tracking signal is between –4 and 4, assume the model is working correctly.

To calculate TSE you need to calculate MAD (Mean Absolute Deviation). Summarise the error/deviation and divide it by MAD and you will have TSE.

Procedures

1.    Select the cell where the result is to be displayed.

2.    Type =sum( select the Error range and type ) type/select the cell with the MAD.

3.    Press enter.


   

1.    Open the file Forecasting and data analysis.

2.    Open the Error-deviation exercises sheet.

3.    Select G2 and calculate the error (=E2-F2).

4.    Copy down the calculation to G25.

5.    Select H2 and calculate the absolute error (=abs(E2-F2)) and copy down the calculation to H25.

6.    Select I2 and calculate the percentage error (=G2/E2) and copy down the calculation to I25. Add percentage style and 2 decimals.

7.    Select J2 and calculate the absolute percentage error (=H2/E2) and copy down the calculation to J25. Add percentage style and 2 decimals.

8.    Select K2 and calculate the square error (=(E2-F2)^2) and copy down the calculation to K25.

9.    Save the file.

1.    Open the file Forecasting and data analysis.

2.    Open the Error-deviation exercises sheet.

3.    Select H32 and calculate the mean absolute deviation (MAD) (=average(H2:H25)).

4.    Select I32 and calculate mean percentage error (MPE) (=average(I2:I25)).

5.    Select J32 and calculate mean absolute percentage error (MAPE) (=average(J2:J25)).

6.    Select K32 and calculate mean square error (MSQ) (=average(K2:K25)).

7.    Select J32 and calculate mean absolute percentage error (MAPE) (=average(J2:J25)).

8.    Select G32 and calculate tracking signal error (TSE) (=sum(G2:G25)/H32)

9.    Save the file.


Using the solver to Optimise forecasts

 

 In this lesson, you will learn how to:

•    Install the Solver

•    Use the Solver to optimise exponential smoothing forecasts

•    Use the Solver to optimise weighted moving average forecasts ? Use the Solver to optimise seasonal forecasts



           

 

TERMS  

Discussion

The Solver is an advanced Excel tool you can use in a lot of different situations. In this book the Solver will be used to optimise Weighted Moving Average forecasts, Seasonal forecasts, and Exponential Smoothing forecasts. 

You can specify the following items in the Solver Parameters dialog box:

Parameter

Description

Set Target cell

The cell containing the formula you want Solver to use

To (equal to Excel 2007)

Whether to set the target cell to its maximum value, its minimum value, or a specified value

By Changing cells

The variable cells that Solver can change in order to reach the desired target cell value

Constraints

You can set constraints or limitations on the changing cells, the target cell, or other cells in the worksheet

 

(This is a screenshotof the Excel 2010 Solver. The Excel 2007 Solver looks a little different, but the parameters and how to use it for optimising forecasts will be the same).

 

SOLVER

Discussion

Solver is not part of the standard Excel application as normally installed, it is an AddIn (often referred to as a “plug-in.”). Add-Ins are additional Excel components that are not commonly used and as a result, have to be installed on your computer. There are many third party (non-Microsoft) Add-Ins that help Excel do useful and clever things that it cannot normally do, or that enhance some of the things that it can do.

These can be found and downloaded from the Internet although it is wise to consult with your IT department first in case they have objections on grounds of security and computer software safety. It is unlikely that in the working environment you will be able to install these programs yourself anyway.

There are, however, selections of Add-Ins that come with Excel and that are perfectly safe and suitable for use; one of these is Solver.

Procedure

1.    Click the Office Button (Excel 2007) or the File tab (Excel 2010).

2.    Click Excel Options.

3.    Click Add-Ins at the left.

4.    Click Add-Ins at the left.

 

5.    Select Excel Add-ins from the Manage: list at the bottom of the dialog box.

6.    Click Go….

7.    Click in box next to Solver Add-in.

 

8.    Click in box next to Solver Add-in.

 

SOLVER TO OPTIMISE EXPONENTIAL SMOOTHING FORECASTS

Discussion

The Exponential Smoothing forecastingmodel needs an Alpha value. A percentage of the previous known actual value and a percentage of the previous forecast. You can analyse the data and calculate the percentage but you can also ask the Solver to find the right percentage to get the most accurate forecast. 

In the example below the Exponential Smoothing model uses an Alpha of 80%. The forecast is based on 80% of the previous actual known sales figure and 20% of the previous period’s forecast. This percentage gives a MAD of1146.08. 

Solver parameters:

The Set Objective: $G$33 is the cell with MAD. To: Min is selected because you want the lowest possible MAD to get the most accurate forecast. By Changing Variable Cells: $F$2 is selected. It is Alpha the Solver must change to bring down MAD. Subject to the Constraints: 2 constrains are entered Alpha must be less than or equal to 1 (100%) or greater than or equal to 0 (0%). 

 

The Solver found the best percentage to be 28% and this changed the MAD from1146.08 to 885.58. 

 

Now the model can be optimised every time you get new actual known data in this example, when you get the October figures. Just use the Solver each time new data is entered.

Procedure

1.    To use Solver, first click the Data tab.

2.    In the Analysis group, click the Solver button.

3.    Select the target cell.

4.    Set the Equal To: option to the one you require.

5.    In the By Changing Cells: box, select, or type, the range of cells the Solver must use to compare against the target cell value.

6.    Click the Add button.

7.    In the Add Constraint dialog box, specify the first cell constraint.

 

8.    If necessary, click on Add to specify more cell constraints.

9.    Click the OK button.

10.   In the Solver Parameters dialog box, click the Solve button. The Solver Results

The Solver Results dialog box opens with the results.

 

SOLVER TO OPTIMISE WEIGHTED MOVING AVERAGE FORECASTS

Discussion

The Weighted Moving Average forecastingmodel needs a weight/percentagevalue for previous periods. Instead analysing the data the Solver can find the right weights to get the most accurate forecast. 

In the example below the Weighted Moving Average model uses a weight for the 1st period of 10%, 20% for the  2nd period, and 70% for the 3rd period. These percentages return a MAD 1061.11. 

Solver parameters:

The Set Objective: $G$35 is the cell with MAD. To: Min is selected because you want the lowest possible MAD to get the most accurate forecast. By Changing Variable Cells: the range $F$2:$F$4 is selected. It is weightsthe Solver must change to bring down MAD. Subject to the Constraints: 1 constrain is entered the sum of the weightsmust be equal to 1 (100%).

 

The Solver found the best percentage to be 71%, 14%, and 14% and this changed the MAD from 1061.11 to 769.80. 

 

11.   To use Solver, first click the Data tab.

12.   In the Analysis group, click the Solver button.

13.   Select the target cell.

14.   Set the Equal To: option to the one you require.

15.   In the By Changing Cells: box, select, or type, the range of cells the Solver must use to compare against the target cell value.

16.   Click the Add button.

17.   In the Add Constraint dialog box, specify the first cell constraint.

 

18.   If necessary, click on Add to specify more cell constraints.

19.   Click the OK button.

20.   In the Solver Parameters dialog box, click the Solve button. The Solver Results

The Solver Results dialog box opens with the results.

 

SOLVER TO OPTIMISE SEASONAL FORECASTS

Discussion

The Seasonal forecastingmodel needs a weightedvalue for each period. The weights must equal the number of periods (if you are working with quarters the sum of the weights must equal 4, Months 12 etc.). Instead analysing the data the Solver can find the right weights to get the most accurate forecast. 

In the example below the Seasonal forecastmodel uses a weight for the 1st period of 0.8, 0.4 for the 2nd period, 2 for the 3rd period, and 0.8 for the 4th period. These percentages return  a MAD 34. 

Solver parameters:

The Set Objective: $H$3 is the cell with MAD. To: Min is selected because you want the lowest possible MAD to get the most accurate forecast. By Changing Variable Cells: the range $F$7:$F$10 is selected. It is weightsthe Solver must change to bring down MAD. Subject to the Constraints: 1 constrains are entered the sum of the weightsmust be equal to 4 (the sum of the weights and number of periods).

 

The Solver found the best suitable weights for the Seasonal forecast model and the forecast is much more accurate now with a MAD of 5 and please look at the chart. It is very clear that the forecast is very accurate.

 

1.    To use Solver, first click the Data tab.

2.    In the Analysis group, click the Solver button.

3.    Select the target cell.

4.    Set the Equal To: option to the one you require.

5.    In the By Changing Cells: box, select, or type, the range of cells the Solver must use to compare against the target cell value.

6.    Click the Add button.

7.    In the Add Constraint dialog box, specify the first cell constraint.

8.    If necessary, click on Add to specify more cell constraints.

9.    Click the OK button.

10.   In the Solver Parameters dialog box, click the Solve button. The Solver Results

The Solver Results dialog box opens with the results.

 

1.    Open the file Forecasting and data analysis.

2.    Open the Optimise using Solver exercises sheet.

3.    Open the Solver parameters dialog box.

4.    The Solver will be used to bring down the MAD (Mean Absolute Deviation). Set Objective: $G$35 To: select Min. to get the lowest possible value.

5.    By Changing Variable Cells: the cell with the Alpha ($F$2)value is the cell the Solver can change to bring down the error.

6.    Add two constraints the Alpha value must be greater than or equal to 0 and less than or equal to 1 (between 0% and 100%).

7.    Click Solve and examine the MAD and the chart.

8.    Save the file.

           

1.    Open the file Forecasting and data analysis.

2.    Open the Optimise using Solver exercises sheet.

3.    Open the Solver parameters dialog box.

4.    The Solver will be used to bring down the MAD (Mean Absolute Deviation). Set Objective: $G$71 To: select Min. to get the lowest possible value.

5.    By Changing Variable Cells: the cells with weights $F$38:$F$40values are the cells the Solver can change to bring down the error.

6.    Add one constraint the Sum of weights ($G$38)must equals 100%.

7.    Click Solve and examine the MAD and the chart.

8.    Save the file.

           

1.    Open the file Forecasting and data analysis.

2.    Open the Optimise using Solver exercises sheet.

3.    Open the Solver parameters dialog box.

4.    The Solver will be used to bring down the MAD (Mean Absolute Deviation). Set Objective: $J$106 To: select Min. to get the lowest possible value.

5.    By Changing Variable Cells: the cell with the seasonal weights or index values($G$77:$G$80)are the cells the Solver can change to bring down the error.

6.    Add one constraint the sum of the weights ($G$74)must equals 4.

7.    Click Solve and examine the MAD and the chart.

8.    Save the file.


 

In this lesson, you will learn how to:

•    Choose the right chart type

•    Create trendlines

•    Choose the best trendline for your data

•    Visualise forecasts and forecast errors in a chart

           

 

Discussion

Excel provides us with a wide selection of charts. Charts are very powerful tools to visualise data and can make it much easier to understand data analysis. In this book the primary idea is to look at the best charts for visualising forecasts and data analysis. How trends easy can be spotted, how forecasts easily can be done in a chart, how you can visualise forecast errors.

 

Discussion

The most common charts used to visualise data analysis are Column charts, Line charts, Scatter charts, and Pie charts, but all the charts provided by Excel can be used.  

Chart type

Description

Pie charts:

Pie charts are superb for displaying data points as a percentage of the whole, but as with all chart types if the value for the data points getting to small (the gap between the values to big) it is difficult to distinguish each data point in the chart. If a data point is less than 5% of the pie, it becomes hard to distinguish the slice. Pie charts can only display one data series, which means you can include only one column or row of values in your selection when you create a pie chart.

Column charts:                   Data that is arranged in columns or rows on a worksheet can be plotted in a column chart. Column charts are useful for showing data changes over a period of time or for illustrating comparisons among items.

In column charts, categories are typically organized along the horizontal axis and values along the vertical axis.

Consider using a column chart when:

•    You have one or more data series that you want to plot.

•    Your data contains positive, negative, and zero (0) values.

•    You want to compare the data for numerous categories side by side.

Line charts:

Line charts are great tools to track and trend data. Line charts can display continuous data over time, set against a common scale, and are therefore ideal for showing trends over time. As a general rule, you may want to use a Line chart if your data has non-numeric x-values. (For numeric x-values, it's usually better to use a Scatter chart).

Scatter charts:                     Scatter charts are commonly used for displaying and comparing numeric values, such as scientific, statistical, and engineering data. You should use a Scatter chart if you want to change the scale of the x-axis, or make it a logarithmic scale. You can use this chart type to effectively:

Display worksheet data that includes pairs or grouped sets of values. You can adjust the independent scales of a Scatter chart to reveal more information about the grouped values.

Show similarities between large sets of data. Rather than showing the differences between data points, a Scatter chart can point out interesting similarities.

Compare large numbers of data points without regard to time. The more data that you include in a Scatter chart, the better the comparisons that you can make.

Scatter charts can be displayed with or without lines to connect the data points, and connecting lines can be displayed with or without data markers.

 

Below you can see the Excel chart dialog box where you can find the entire chart types in Excel.

 

TRENDLINES

Discussion

Before you start creating charts and trendlines make sure that:

You have enough data to show a meaningful chart and enough data for Excel to be able to calculate a reliable trend. Insufficient data might twist the results. For example, seasonal fluctuations might be mistaken for long-term trends if the data is from only one year. The chart must be a 2-D chart. Excel cannot create trendlines on 3-D charts.

•    The data is ordered from earliest to most recent.

•    No data is missing. If data is unavailable for a period, enter an estimate.

You can forecast with trendlines you will see how later in this book.

So what are trendlines, really?

Trendlines are graphical representations of trends in data that you can use to analyse problems of prediction. Such analysis is also called regression analysis. By using regression analysis, you can extend a trendline in a chart beyond the actual data to predict future values. For example, the preceding chart uses a simple logarithmic trendline that is forecast ahead four quarters to clearly show a trend toward rising revenue.

R² (R-squared)

So you might be wondering: How reliable are these trendlines, anyway? The answer has to do with something called R-squared — or, more specifically, the R-squared value of the trendline (this is where math comes in). Think of the R-squared value as a magic number — in this case, a number between 0 and 1.

You don't have to understand all about R-squared values — just remember: A trendline is most reliable when its R-squared value is at or near 1. When you fit a trendline to your existing data, Excel automatically calculates its R-squared value based on a formula. The more closely a trendline match your existing data, the more accurate a forecast that uses this trendline is likely to be. If you want, you can display this value on your chart.

 

It can be difficult to see a trend just looking at the chart. To add a trendline you need to select the chart and select the contextual Chart Tool tab layout. Select Linear Trendline from the trendline list.

 

Right click the trendline and click Format Trendline. Click Display R-squared value on chart to display R2.

 

The R-squared value, which tells you how closely your trendline follows your data, appears on the chart. With a linear trendline in this example above, the value is 0.3692. As you can see the trendline doesn't follow the data very closely because the sales goes up and down. A linear trendline with an R-squared value of 0.3692 is not a very reliable trendline for a forecast. The linear trendline in this example will may be not be able to give us an accurate forecast but it is easy to see that the trend is going up so you have increasing sales trend and that is of course nice.

You can change the trendline to another trendline type and compare the R-square values. May be there is a trendline which is a better match to the data. In the next chapter you will see a description of all the different trendlines and when to use the different types.

Procedure

1.    Create a chart.

2.    Make sure that the chart is selected. Click Trendline on the contextual Chart Tools tab.

3.    Click on the trend line type you want to create.

4.    Right click the trendline and choose Format Trendline.

5.    Click Display R-squared value on chart to display R2.

&

Instead of right click the trendline to format the trendline you can click More Trendline Options in the trendline type dialog list.

&

You can create a trendline by right click the data series and click Add Trendline.

 

Discussion

When you want to add a trendline to a chart in Excel, you can choose between six different trend/regression types. You should use the type which fit best to your data.

Choosing the right trendline for your data is very important. Checking the R-squared value can help you choose the best trendline for your data. It also helps to understand the types of trendlines that are likely to fit different scenarios.

Type

Description

Linear

A linear trendline is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing.

 

Logarithmic       A logarithmic trendline is a best-fit curved line that is most useful when the rate of change in the data increases or decreases quickly and then levels out. A logarithmic trendline can use negative and/or positive values.

     

        Power             A power trendline is a curved line that is best used with data sets that

compare measurements that increase at a specific rate — for example, the acceleration of a race car at one-second intervals. You cannot create a power trendline if your data contains zero or negative values.

   

Moving     A moving average trendline smooth out fluctuations in data to show a average    pattern or trend more clearly. A moving average trendline uses a specific number of data points (set by the Period option), averages them, and uses the average value as a point in the trendline.

If Period is set to 2, for example, then the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, and so on.

 

Discussion

Forecast charts are exceptional to show the result in a very user friendly easy understandable manner. Line charts, scatter chart, columns charts, or a combination of column charts and line charts should be the charts used for forecasts.

In the scatter chart below the blue line is data over 20 quarters. The vertical axis shows the data and the horizontal axis shows the quarters. It is easy to see that it is seasonal data.

 

By adding a 4 periods forecast trendline it is obvious that the values increase over time but also that the data is not linear data (the R2=0.1065). It tells us that the trendline’s prediction for period 21 to 24 is not reliable.

 

For seasonal data a seasonal forecast should be better to predict the future. In the chart below the result of a seasonal forecast is added and you can see that it is much better to “find” the ups and downs in the data.

 

By adding the absolute error for the two forecast methods the forecast accuracy can be shown in the chart period by period, but it is not obvious which one is the most accurate. 

 

By adding the MAD (Mean Absolute Deviation) to the chart you can now see that the seasonal forecast is the most accurate forecast, but it is not because it is much more accurate than the trendline forecast.

 

In the example below the Solver has been used to optimise the seasonal forecast and it is easy to see that now the seasonal forecast is much more accurate than the trendline forecast. The Solver decreased MAD from 26.93 to 5.28.

 

Procedures

1. Create the data you want to visualise in a chart.

 

3. Move and resize the chart.

           

 

1.    Open the file Forecasting and data analysis.

2.    Open the Visualise forecasts exercise sheet.

3.    Right click the data series Sales on the chart in Exercise 1.

4.    Click Add Trendline.

5.    Create a linear trendline and tick Display Equation on chart and Display R-squared value on chart.

6.    Click Line Color top left in the Format Trendline dialog box.

7.    Select Solid line and the colour red.

8.    Examine the R2 value. Do you think this is the right trendline for this chart?

9.    Right click the trendline and click Format Trendline.

10.   Type 4 in the Forward: box under Forecast.

11.   Click Close.

12.   Save the file.

1.    Open the file Forecasting and data analysis.

2.    Open the Visualise forecasts exercise sheet.

3.    Right click the data series Sales on the chart in Exercise 2.

4.    Click Add Trendline.

5.    Create a linear trendline and tick Display Equation on chart and Display R-squared value on chart.

6.    Examine the R2 value. Do you think this is the right trendline for this chart? The R-squared value is relatively high but the trendline does not reflect the data series very well. 

7.    Write down the R-squared value. Right click the trendline and click Format Trendline.

8.    Try the other trendline types and compare the R-squared value.

9.    Use the trendline with the highest R-squared value

10.   Save the file.

In the exercise 3 area (starting from row 40) two different methods have been used to forecast the sales the Forecast function and seasonal forecasting. Use the chart to compare the two methods and to decide which one you should use to get the most accurate forecast. 

1.    Open the file Forecasting and data analysis.

2.    Open the Error-deviation exercises sheet.

3.    Add the Forecast function data to the chart (copy the range F40:F68 click on the chart area and paste the data).

4.    Do the same with the seasonal forecast data (copy the range O40:O68 click on the chart area and paste the data).

5.    Add the forecast function’s Absolute error (column H) and the seasonal forecast’s Absolute error (column Q) to the second chart (the blank chart).

6.    Right click the two error series and change chart type to column (If they are not in columns already).

7.    Add the forecast function’s MAD (range L40:L68) and the seasonal forecast’s MAD (U40:U68) to the second chart.

8.    Right click the two MAD series and change chart type to line.

9.    Use the Solver to optimise the seasonal forecast. The right parameters are already added to the Solver parameter dialog box.

10.   Compare the two methods which one is the best and most accurate?

11.   Save the file.


Comparing forecast Methods

 

In this lesson, you will learn how to:

•    Compare different forecasting models

•    Understand which forecasting model to use 

           

Microsoft Excel 2007/2010 Forecasting & Data Analysis                               Comparing forecast Methods

 

TERMS  

Discussion

To keep your forecast on track it is important to continuously developing your forecast model. You have seen different forecasting models and how to calculate the errors. You should use more than one method and every time you add more data calculate the errors and optimise your model. It can change over time which forecast method generates the most accurate forecasts. If you by comparison of the different method can see a change in accuracy between the methods you can keep your forecasts on track by always knowing which forecast method generates the best result for your data.

 

Create a column chart displaying all MAD results from all the forecasting method can be a nice tool. Keep an eye on the chart when you are adding new data to you forecasting model and you will very easy visualise which model is the most reliable.

Comparing forecast Methods

 

You can also after trying the different methods decide which one to use in the future and then use TSE (Tracking Signal Error) to make sure that the model is on track. The TSE value must be between -4 to 4.

Page


Microsoft Excel 2007/2010 Forecasting & Data Analysis

 

In this lesson, you will learn how to:

•    Use the Scenarios Manager  

•    Use the Goal Seek tool  

•    Use the Data Table tool 

           

 

Discussion

The What-If analysis tools are very powerful tools when you need to forecast and you have some knowledge which Excel cannot calculate from the historical data. Your company will add 5 new products to the product line. The costs will be reduced because of an investment in new manufacturing equipment. You expect a marketing investment to pay off. You can manipulate your forecasts using What-If analysis.

 

SCENARIOS MANAGER   

Discussion

The Scenarios tool can be a very nice supply to forecasting. You may need to forecast December. You have the data for each month from January 2011 to September 2012. You have the units you have sold, the unit price, the variable costs, the fixed costs, the sales, the profit, the total market units, and your company’s market shares. The Forecast function has been used to calculate the forecast for Market shares and total market units for December. You know that the forecast will not be accurate. 

First of all you know that the sales for December will be very good. You also know that your company will spend a lot of money getting a bigger market share. Your company is also working on reducing the fixed and variable costs and you also have plans changing the price policy for your products.

You want now to work with the forecast to see what will happen with all these changes. You do not know if all the changes will happen so you want to create different scenarios to be able to compare the different situations.

You can create a scenario summary report, which lists all the scenarios you have created in a side-by-side format so that you can compare them. When you create a scenario summary report, the Scenario Manager automatically inserts a Scenario Summary sheet in the workbook and places the report on it. This sheet allows you to easily view and print the scenario summary report. A scenario summary report appears in an outline format.

Page

 

Open up the scenarios tool by clicking the Data tab and click the What-if Analysis button in the Data Tools group. Click Scenarios Manager and the Edit Scenario dialog box will open. You first want to see how the forecast will change if the total market will increase because of Christmas and if your company’s investment will pay off by giving you more shares of the market. 

In the Edit Scenario dialog box first enter a name for the scenario. In the Changing cells: box you must select the changeable cells. Click OK. 

 

Based on Christmas forecast from previous years your best guess for Total Market for December will be 200.000 units. Your hope that the investment will increase the

Market share to 2%. Enter the values in the Scenario Values dialog box and click OK.

Page


 

If you click Show you can see the result of your scenario in the worksheet. If the increase of market shares and total market units will be as predicted the sales increase from £ 10,680.00 to £ 40.000,00. To add more scenarios click add and just go through the steps again.

 

Procedures

Creating scenarios

1.    Click the Data tab.

2.    Click the What-If Analysis button in the Data Tools group.

3.    Click the Scenario Manager command.

4.    Click the Add… button.

5.    In the Scenario name: box, type a name that identifies the scenario.

6.    In the Changing cells: box, select or type the cells you want to modify in the scenario.

7.    In the Comments: box, type an explanatory note regarding that particular scenario.

8.    Click the OK button.

9.    In the Scenario Values box, type values for each of the displayed changing cells.

10.   Click the OK button.

11.   The Scenario Manager box now displays the scenario.

12.   . Click the Close button.

Showing a scenario

1.    To display a scenario, first click the Data tab.

2.    Click the What-If Analysis button.

3.    Click the Scenario Manager… button.

4.    Select the scenario you want to display.

5.    Click the Show button.

6.    The chosen scenario data is now displayed.

7.    Click the Close button.

Deleting a scenario

1.    To delete a scenario, first click the Data tab.

2.    Click the What-If Analysis button.

3.    Click the Scenario Manager… button.

4.    Select the scenario you want to delete.

5.    Click the Delete button.

6.    The chosen scenario data is deleted.

7.    Click the Close button.

Edit values in a scenario

1.    To edit values in a scenario, first click the Data tab.

2.    In the Data Tools group, click the What-If analysis arrow.

3.    Click the Scenario Manager… button.

4.    Select the name of the scenario you want to edit in the Scenarios list box.

5.    Click the Edit… button.

6.    In the Edit Scenario dialog box, reselect the Changing cells that you want to edit, if necessary.

7.    Click the OK button.

8.    In the Scenario Values dialog box, type a new value for the cell you want to change.

9.    Click the OK button.

10.   To display the effect, click the Show button.

11.   Click the Close button.

12.   The effect for that scenario is now displayed.

Creating a scenario summary report

1.    To create a scenario summary report, first click the Data tab.

2.    Click the What-If Analysis button.

3.    Click the Scenario Manager… button.

4.    In the Scenario Manager dialog box, click the scenario you want to view.

5.    Click the Summary… button.

6.    In the Scenario Summary dialog box, click the OK button.

7.    A Scenario Summary worksheet is displayed with the report.

8.    The effect for that scenario is now displayed.

 

GOAL SEEK TOOL   

Discussion

The Goal Seek tool can be very useful in a forecasting situation. Your forecast predicts that you in December will reach sales of £ 9,000.00 but you really need the sales to be £ 10,000.00. 

The Goal Seek tool can now tell you what to do to get what you want. Open up the

Goal Seek tool by clicking the Data tab and click the What-if Analysis button in the Data Tools group. Click Goal Seek and the Goal Seek dialog box will open. Click in the Set Cell: box and select the cell with the value you want to change. Click in the To Value: box and type the value you want the Set cell: cell to have. Click in By Changing Cell: box and click the cell you want Excel to change to get the result you want in the Set Cell: cell.

 

Procedures

1.    To create a scenario summary report, first click the Data tab.

2.    Click the What-If Analysis button.

3.    Click the Scenario Manager… button.

4.    In the Scenario Manager dialog box, click the scenario you want to view.

5.    Click the Summary… button.

6.    In the Scenario Summary dialog box, click the OK button.

7.    A Scenario Summary worksheet is displayed with the report.

8.    The effect for that scenario is now displayed.

 

DATA TABLE TOOL

Discussion

The Data Table tool is another What-if Analysis tool which is working very well with forecasts. A lot of forecasters must have asked themselves what if this or this happens. What will happen to my forecast? The Data Table tool can work with one or two variables. The Data Table tool can return a lot of results very fast. 

You have a forecast for December but you want to see what will happen to the forecast if you change the price for the product and reduce the costs. 

 

If you want to work with two variables you must have the variables in the same column and row as the formula.

 

Select the formula, the variable rows and columns and click Data tab, click What-If

Analysis in the Data Tools group. Click Data Table and in the Data Table dialog box Row input cell: box enter the cell reference for the cell the formula use to calculate the variable in the row of the selection. In the Column input cell: box the Data Table tool need the same for the column input.

   

Procedures

One-variable

1.    To create a one-variable (column) data table, first select the cell immediately above the output column.

2.    Enter the formula you want to use.

3.    Press the [Enter] key to confirm the entry.

4.    Select the range containing the formula, the input column, and the output column.

5.    Click the Data tab.

6.    In the Data Tools group, click the What-If Analysis button.

7.    Click the Data Table… button.

8.    In the Data Table dialog box, click in the Column input cell: text box.

9.    Enter, or select, the input cell.

10.   Click the OK button.

11.   The results are displayed in your one column data table.

Two-variable

1.    To create a two-variable data table, first select the cell above the column input values and to the left of the row input values.

2.    Enter the formula you want to use.

3.    Select the complete data table.

4.    Click the Data tab.

5.    In the Data Tools group, click the What-If Analysis button.

6.    Click the Data Table… button.

7.    In the Data Table dialog box, click in the Row input cell: text box.

8.    Enter, or select, the input cell.

9.    In the Data Table dialog box, click in the Column input cell: text box.

10.   Enter, or select, the input cell.

11.   Enter, or select, the input cell.

12.   The results are displayed in your two-variable data table.

You have the data for each quarter from 2007 to 2011. You have the units you have sold, the unit price, the variable costs, the fixed costs, the sales, the profit, the total market units, and your company’s market shares. The Forecast function has been used to calculate the forecast for Market shares and a seasonal forecast has been used to forecast Total Market (units) for 2012. 

What will happen with the forecast for quarter 4 2012 if you can increase your market shares with 2%, decrease fixed and variable costs and if the total market units will increase?

1.    Open the file Forecasting and data analysis.

2.    Open the What-If analysis sheet.

3.    Open up the Scenarios tool’s parameters dialog (Data tab, Data Tools group, What-If Analysis).

4.    Click Add and call the scenario “Best case forecast”.

5.    In Changing cells: type F32:I32 or click in the box and select the range F32:I32.

6.    In Comment: type “Decrease variable and fixed costs. Increase market shares and total market”.

7.    Click OK button.

8.    Change $F$32 (variable costs) to 1.6, change $G$32 (fixed costs) to 16000, change $H$32 (total market units forecast) to 180000, and $I$32 (market shares forecast) to 0.22.

9.    Click OK button.

10.   Click Add and call the scenario “Worst case forecast” do not change Changing cells:.

11.   In Comment: type “Decrease market shares and total market units”.

12.   Click OK button.

13.   Change $H$32 (total market units forecast) to 140000, and $I$32 (market shares forecast) to 0.18.

14.   Click OK button.

15.   Select Best case forecast and click Show button. Examine the result.

16.   Select Worst case forecast and click Show button. Examine the result.

You have the data for each quarter from 2007 to 2011. You have the units you have sold, the unit price, the variable costs, the fixed costs, the sales, the profit, the total market units, and your company’s market shares. The Forecast function has been used to calculate the forecast for Market shares and a seasonal forecast has been used to forecast Total Market (units) for 2012. 

In the best case scenario from the previous exercise you reached a profit of £ 376,040.00, but you will like to find out how you can get a profit of £ 400,000.00.

1.    Open the file Forecasting and data analysis.

2.    Open the What-If analysis sheet.

3.    Open up the Goal Seek tool’s parameters dialog (Data tab, Data Tools group, What-If Analysis).

4.    In Set cell: type $K$37 or click in the box and select K37.

5.    In To value: type 400000.

6.    In By changing cell: type $I$37 or click in the box and select I37.

7.    Click OK button and examine the result (you need market shares of 26.67% to get £400,000.00 in profit).

8.    Click Cancel button to reset the values and to close down the Goal Seek dialog box.

9.    Go through step 3 to 8 again but this time please find out how much you need to increase the unit price to get £ 400,000.00 in profit.

10.   Go through step 3 to 8 again but this time please find out how much you need to decrease the variable costs to get £ 400,000.00 in profit. You will find out that it is not possible to get a profit of £ 400,000.00  by only reducing the variable costs.

11.   Save the file.

You have the data for each quarter from 2007 to 2011. You have the units you have sold, the unit price, the variable costs, the fixed costs, the sales, the profit, the total market units, and your company’s market shares. The Forecast function has been used to calculate the forecast for Market shares and a seasonal forecast has been used to forecast Total Market (units) for 2012. 

You want to find out what will happen to the forecast if you can bring down the variable and fixed cost. 

1.    Open the file Forecasting and data analysis.

2.    Open the What-If analysis sheet.

3.    For the first variable fixed costs use column K. Select K43 and type 10000. Type 12000 in K44, 14000 in K45, 16000 in K46, 18000 in K47, 20000 in K48, and 22000 in K49. 

4.    For the second variable Variable costs use row 42. Select L42 and type 1.

Type 1.2 in M42, 1.4 in N42, 1.6 in O42, 1.8 in P42, 2 in Q42, and 2,2 in R42. 

5.    Select the range K42:R49.

6.    Open up the Data Table tool’s parameters dialog (Data tab, Data Tools group, What-If Analysis).

7.    In Row input cell: type F42 or click in the box and select the range F42. The row variable is Variable Costs.

8.    In Column input cell: type G42 or click in the box and select the range G42. The column variable is Fixed Costs.

9.    Click OK button.

10.   Please do step 3 to 9 again. This time use the profit formula in K55 and Unit Price (use the range 8 to 20 pounds increased by 2 each time) in column K and Market Shares Forecast (use the range 16% to 30% increased by 2 each time) in row 55 as the two variables.

11.   Compare the two tables. To increase the profit is it best to increase the costs or to try to get a bigger part of the market shares?

12.   Save the file


 

In this lesson, you will learn how to:

•    Use the Correl function  

•    Use the Data Analysis Tool Correlation  

•    Create a scatter chart to visualise Correlation Coefficient 

 

Discussion

The Correl function determines the relationship (the correlation coefficient) between two arrays of data.

You may need to find out if your company’s increased advertising costs also increase your company’s sales figures or if higher temperatures in summer increase sales of ice cream. Or if you increase the price for your products will it affect the quantity you are selling. 

The Correl function will return a result between -1 and 1. 

Between 1 and 0:

If the result is close to 1 then it is because there is a close positive relationship between the two arrays (If the temperature rises the ice cream sales increase). Close to 0 then there is not a close relationship between the two arrays (the sales do not increase because of increased advertising costs). 

Between 0 and -1:

If the result is close to -1 then there is a close negative relationship between the two arrays (if the price goes up the sales goes down). Close to 0 but a negative value then there is not a close relationship between the two arrays (the sales does not decrease if the price goes up). 

 

CORREL FUNCTION

Discussion

The Correl function consists of two required arguments, in the following order: Array1, Array2. Array1 and Array2 are the arrays of data you want to examine to see if there is any relationship. In the Array1 box enter the first range of data. In the Array2 box enter the second range of data.

Array1 and array2 must have the same number of data points. If not the Correl function returns the #N/A error value.

If either array1 or array2 is empty the Correl function returns the #DIV/0! Error.  

Procedures

1.    To use a Correl function, first create a data range with two columns of values.

2.    Click in the cell where you want to place the function.

3.    Click on the Formulas tab.

4.    In the Function Library group, click on the Insert Function button.

 

5.    In the Insert function dialog box, locate Statistical category in the Or select a category: box.

6.    Click on Correl.

7.    Click on the OK button.

 

8.    In the Function Arguments dialog box, click in the Array1 box.

9.    Select the cells containing the value from the first column in the data range.

10.   In the Array2 box, select the cells containing the value from the second column in the data range.

11.   Click on the OK button.

 

&

The COVARIANCE.P function is another useful function to compare data from two different arrays. The COVARIANCE.P function has two arguments Array1 and Array2. Enter the ranges for the two data sets and the function will return a positive or negative number. If it is positive close to zero there is a close positive relationship between the 2 arrays. Negative and close to zero there is a close negative relationship.

If the value increases the two arrays are less related.

 

DATA ANALYSIS TOOL CORRELATION  

Discussion

The Data Analysis Tool Correlation is working the same way as the Correl function. 

The difference between the two tools is with the Correl function you can find the relationship between two arrays of data, with the Data Analysis Tool Correlation you can find the relationship between any numbers of arrays.

As the Correl function the Data Analysis Tool Correlation will return a result between -1 and 1. 

Between 1 and 0:

If the result is close to 1 then it is because there is a close positive relationship between the two arrays (If the temperature rise the ice cream sales increase). Close to 0 then there is not a close relationship between the two arrays (the sales do not raise because of increased advertising costs). 

Between 0 and -1:

If the result is close to -1 then there is a close negative relationship between the two arrays (if the price goes up the sales goes down). Close to 0 but a negative value then there is not a close relationship between the two arrays (the sales does not decrease if the price goes up). 

Using the Correlation tool

You can use Correlation tool by selecting the Data Analysis command from the Analysis group at the far right end of the data ribbon. Select Correlation from the list and click OK.

If there is no Analysis group or Data Analysis command on the Ribbon, you must install Data Analysis TOOLPAK (please go to Appendix A in this workbook to see how to install Data Analysis TOOLPAK).

After you have entered all necessary information into the Correlation dialog box, you can get the result in the output range. 

You can specify the following items in the Correlation parameters dialog box:

Parameter

Description

Input

The array where you have the data you want to examine

Grouped By

input data grouped by columns or rows

Labels

If labels are selected in the input range the box must be ticked

Output Range

Enter a cell reference for the output or New Worksheet Ply to get the output in a new worksheet in the workbook with the input data or New Workbook to get the output in a new workbook

When you have finished entering the information click OK and you will get the output.

Procedures

1.    To use the Correlation tool, first click the Data tab.

2.    In the Analysis group, click the Data Analysis button.

3.    Select Correlation from the list and click OK.

 

4.    Select the Input Range.

5.    Select the desired options for Grouped By, Labels, and Output.

6.    Click OK

The output

Let us have a look at the output. You got a table with the column labels from the input array, because you selected the column labels when you selected the Input Range. In the output table you have the labels both as row and columns labels. 

You can see that the relationship between Aircon sales and Average temperature is close, because the result is close to 1. It means that if the average temperature rises you are selling more air-condition units.  

If you look at the relationship between Aircon sales and Advertising costs you got a negative result close to 0. It means that it is not a good idea spending money on advertising. The sales of air-condition units do not increase if you spend more money on advertising. In this example the sales actually decrease a little bit if you increase the advertising budget.

You got a result close to minus 1 for the relationship between Aircon sales and Price per unit. It means that you have a close negative relationship between the two columns. If the price goes up the sales go down. If the price goes down the sales go up.

 

DISPLAY CORRELATION COEFFICIENT

Discussion

Charts are very useful to visualise data analysis. The Scatter chart is the most beneficial when working with data analysis. 

In the example below it is easy to spot the trend in the chart. There is a close relationship between average temperature and aircon sales. Of course you can also see that the Correl function returns 93% wich also emphasise that there is a close relationship between the two arrays.

 

To make it even easier to spot the trend you can add a linear Trendline to the chart and if you add the R2 (R square error) to the Trendline you can see how accurate your Trendline is. A value close to 1 shows that the Trendline is very accurate. Close to 0 there is not really any trend. 

 

In the example below there is a close negative relationship between the Price per unit and Aircon sales. Again it is easy to see in a scatter chart.

 

In the example below there is no close relationship between Advertising costs and Sales. The Correl function returns 21% and the R2 value is 0.0421 which tells you that there is not really a trend in the data.

 

In the example below the Data Analysis tool Correlation has returned the correlation for three arrays and again a chart is very useful to examine the outcome.

 

Procedures

1.    To create a chart to visualise correlation select the arrays with the data you want to examine.

2.    Click Insert  tab. 

3.    Click the scatter chart option you want to use.



 

4.    Click Layout tab (the chart most be selected to have access to the Layout tab).

5.    In the Analysis group click Trendline and Linear Trendline

6.    Right click the Trendline and click Format Trendline.

7.    Select the option Display R Squared Value on Chart

 

8.    Click Close.

 

1.    Open the file Forecasting and data analysis.

2.    Open the Correlation Coefficient sheet.

3.    Use the Correl function in D60 to get the correlation coefficient for the two arrays                    C47:C58 and D47:D58 (Average temperature and Ice sales).

4.    The result should be 0.9566537. What does this tell us about the relationship between the two arrays?

5.    Create a scatter chart showing the Correlation Coefficient between the average temperature and the ice sales.

6.    Click the Data tab. Click Data analysis in the Analysis group. Open the Correlation tool. 

7.    Use the Data Analysis Tool Correlation to find the Correlation Coefficient between the average temperature, the ice sales, and advertising costs (the input should be O46:Q58).

8.    The result should be:

9.    What does this tell us about the relationships between the two arrays?

10.   Create a scatter chart showing the Correlation Coefficient between the average temperatures, the ice sales, and advertising costs.

11.   Save and close the file


 

In this lesson, you will learn how to:

•    Calculate Break-even

•    Visualise break-even using scatter chart

•    Use the goal seek tool to calculate break-even

•    Use the Solver tool to calculate break-even

•    Exercise

 

Discussion

When you are considering an investment or launching a new product you want to know at what point you will break-even. When will you start earning money? How many units do I need to sell to Break-even if you invest £ 100.000 on television commercials? How should the products be prised to break-even this year? 

For forecasting break-even analysis is very important to forecast new product’s financial success.

In this workbook you will see the math behind break-even and the break-even will be manually calculated. The Goal Seek tool in Excel can do these calculations and is a very valuable tool for break-even analysis.

 

BREAK-EVEN

The formula for break-even looks like this:

0 (profit) =Units *(Sales Price – Variable Costs) – Fixed Costs

Example:

•    100 Units

•    Sales Price = £10

•    Variable Costs = £4

•    Fixed Costs = £600

With above values the formula will be true a profit of 0. You need to sell 100 units for a price of £10 if the variable costs are £4 and fixed costs are £600

If you want to find out how many units you need to sell to break-even you must change the formula using the math rules for equations. 

0 (profit) =Units *(Sales Price – Variable Costs) – Fixed Costs

First add Fixed Costs on both sides of the equal sign:

Fixed Costs=Units *(Sales Price – Variable Costs)

This equation can now be used to calculate the Fixed Costs to break-even.

By using the values from the example:     600=100*(10-4)

Divide with (Sales Price – Variable Costs) on both sides of the equal sign:

Units = Fixed Costs/(Sales Price – Variable Costs)

This equation can calculate how many units you need to break-even. 

By using the values from the example:     100=600/(10-4)

The equation to find the sales price is:

Sales Price = Fixed Costs/Units + Variable Costs

To get this equation start from the break-even equation again:

0 (profit) =Units *(Sales Price – Variable Costs) – Fixed Costs

First add Fixed Costs and both sides of the equal sign:

Fixed Costs=Units *(Sales Price – Variable Costs)

Then divide Units on both sides of the equal sign:

Fixed Costs/Units=Sales Price – Variable Costs

Then add Variable Costs on both sides of the equal sign:

Sales Price = Fixed Costs/Units + Variable Costs

This equation can calculate sales price you need to break-even. 

By using the values from the example:     10=600/100 + 4

Procedures (calculate units to break-even)

Units = Fixed Costs/(Sales Price – Variable Costs)

•  100 Units

•  Sales Price = £10

•  Variable Costs = £4

•  Fixed Costs = £600

1.    Type =600/(10-4).

2.    Press enter. 

Procedures (calculate sales price to break-even)

Sales Price = Fixed Costs/Units + Variable Costs

•  100 Units

•  Sales Price = £10

•  Variable Costs = £4

•  Fixed Costs = £600

1.    Type =600/100+4

2.    Press enter. 

Procedures (calculate fixed costs to break-even)

Fixed Costs=Units *(Sales Price – Variable Costs)

•  100 Units

•  Sales Price = £10

•  Variable Costs = £4

•  Fixed Costs = £600

1.    Type =100*(10-4)

2.    Press enter. 

 

-EVEN USING SCATTER CHART

Discussion

Charts can be very useful to understand and visualise data analysis. Scatter charts is the chart type, which should be used to visualise break-even analysis. 

In the example below in column A there are units going from 0 to 4000 with an increment of 400. In column B the sales (units times unit price), in column C variable cost (units times unit variable cost), in column D the fixed cost, in column E total costs (variable cost + fixed cost), and in column F the net income (sales – total costs). 

If you look at the table it is not easy to see the break-even. It is somewhere between 1200 and 1600 units.

 

To visualise break-even in a scatter chart select the units (A5:A16), the sales (B5:B16), and total costs (E5:E16), and create the straight line scatter chart. 

 

The break-even point is where the sales line cross the total costs line.

To make it easier to see the break-even in the scatter chart you can calculate the break-even using the break-even equation you have seen earlier in this chapter; 

Units = Fixed Costs/(Sales Price – Variable Costs)

In this example the calculation is; =f3/(d3-e3) and the result is 1250. You will also need to calculate the Y value to plot it on the chart. The Y value is breakeven units multiplied by unit price. In this example; =B19*D3 (the break-even equation is calculated in B19). The result is calculated in C19 and is 50000. To make the exact break-even point very clear in the chart you can add the exact value to the legend label in the chart. In cell D19; ="BEU "&B19 has been entered. This will return “BEU 1250” on the legend label.

To add the break-even point and the legend label you need to select the chart and click the contextual Design tab. Click Select Data in the Data group. Click Add in Series Name click D19 in Series X values click B19 and in Series Y values click C19.

 

The break-even point is still not visible on the chart. Well the label is visible in the legend. To make it visible you must change the data series’ formatting. Select the data series and right click. Choose Format Data Series click Marker Options select Build-in and select a type and a size. Click Close.

Now it is very easy to see the break-even point.

 

If you have created the table as the one in this example you can change the variables in row three and everything will be updated immediately.

 

1.    To create a scatter chart to visualise Break-Even for units sold select the arrays with the data you want to visualise.

2.    Click Insert  tab. 

3.    Click the scatter chart option you want to use and create the chart.

 

4.    Calculate Break-Even using the break-even equation.  Units = Fixed Costs/(Sales Price – Variable Costs)

5.    Calculate the Y value for break-even (break-even multiplied by unit price).

6.    Select the chart and click the contextual Design tab and click Select Data in the Data group.

7.    Click Add and in the Edit Series parameters dialog box in the Series Name box type a name for the data series. 

8.    In the Series X values box enter the cell reference for the cell with the break-even equation/calculation.

9.    In the Series Y values box enter the cell reference for the cell with Y value calculation.

 

11.   To make the break-even data series visible in the chart right click the series and choose Format Data Series or click Layout tab and select the data series from the chart object list in the Current Selection group and then in the same group click Format Selection.

 

12.   Click Marker Options to the left, click Build-in, select a marker type and size. Click the Close button.

   

SCENARIOS AND THE GOAL SEEK TOOL TO CALCULATE BREAK-EVEN

Discussion

The What-if analysis tools in Excel are very useful also when you are working with Break-Even analysis. How can I change the break-even point? Which parameters will affect my break-even analysis most if I change them?  

1.  Open the file Forecasting and data analysis.

2.  Open the Break-even Analysis 1 sheet.

3.  Select C68 and calculate break-even. How many units must be sold to get 0 in profit (use the equation “Units=FixedCosts/(SalesPrice-VariableCosts)”).

4.  Select F68 and calculate the selling price to break-even selling 10000 units (use the equation “SalesPrice = FixedCosts/Units + VariableCosts”).

5.  Select H68 and calculate the Fixed Costs to break-even selling 10000 units (use the equation “FixedCosts= Units*(SalesPrice - VariableCosts)”). 

6.  Select K68 and calculate the Variable Costs to break-even selling 10000 units (use the equation “VariableCosts=SalesPrice-FixedCosts/Units”). 

8. Use the Goal Seek tool to Break-even Units, Sales Price, Fixed Costs, and Variable Cost in the same 4 tables.

7. Save and close the file.


 

In this lesson, you will learn how to:

•    Use Descriptive Statistics

•    Use Histogram

•    Use Regression

•    Use Sampling

           

Discussion

You have already seen some of the tools from the Data Analysis TOOLPAK add-in. In this chapter some of the other tools will be briefly demonstrated. All the statically terms will not be explained. 

 

STATISTICS

Discussion

The Descriptive Statistics tool can return statistical information from data in Excel. 

Usually when you analyse data it is important examine the distribution and to discover information such as the minimum and maximum values and to determine if there are outliers. This is an important step in any analysis since it helps you understand if your data meet rules required by other analyses such as t-tests and regression and if the data are needed for forecasting.

The Descriptive Statistics tool can provide you with these information and much more.

 

Information you should notice includes:

1.Search for outliers: Look at the Minimum and Maximum values to see if these values fall within your expected range for these data. If a value is unexpectedly small or large, you should examine your data to see if they were wrong. If there are corrections that need to be made, make them before continuing. If you have values that are unexpectedly large or small, but are actual values, it may indicate that your data are not normally distributed. This knowledge will help you to decide forecasting method. It may also indicate that the average is not the best value to report to describe the trend of this data set.

2.Symmetry: Another measure that helps you decide normality is Skewness and Kurtosis. The Skewness measure indicates the level of non-symmetry. If the distribution of the data is symmetric then skewness will be close to 0 (zero).  The further from 0, the more skewed the data are. 

How do you tell if the skewness is large enough to cause concern? Excel doesn’t give you this value, but a measure of the standard error of skewness can be calculated as =SQRT(6/N) where N is the sample size. If the skewness is more than twice this amount, then it indicates that the distribution of the data is non-symmetric. 

3.Kurtosis characterizes the relative peakedness or flatness of a distribution compared with the normal distribution. Positive kurtosis indicates a relatively peaked distribution. Negative kurtosis indicates a relatively flat distribution. Again, for normally distributed data the kurtosis is 0 (zero). As with skewness, if the value of kurtosis is too big or too small, there is concern about the normality of the distribution. In this case, a rough formula for the standard error for kurtosis is =SQRT(24/N) where N is the sample size. If the kurtosis is more than twice this amount the data may be considered not to meet the criteria for normality by this measure. 

4.Evaluation of central trend: For normally distributed data the average is the typical value to measure. However, the average is not interesting without some evaluation of the inconsistency of the data. Therefore you should at least report three values – the average, the standard error of the average, and the sample size. 

The median is another measure of central tendency and is usually reported when the data are not normally distributed. The mode, or the most frequent value, is a third measure of central tendency.

5.Measures of inconsistency: The measures of inconsistency reported in Excel’s descriptive statistics include the standard error, the standard deviation, the variance and the range. 

                        Standard Deviation = SQRT(Variance)

                        Standard Error = Standard Deviation / SQRT(N)

You can find The Descriptive Statistics tool by selecting the Data Analysis command from the Analysis group at the far right end of the data ribbon. Select Descriptive Statistics from the list and click OK.

If there is no Analysis group or Data Analysis command on the Ribbon, you must install Data Analysis TOOLPAK (please go to Appendix A in this workbook to see how to install Data Analysis TOOLPAK).

After you have entered all necessary information into the Descriptive Statistics dialog box, you can get the result in the output range. 

You can specify the following items in the Descriptive Statistics parameters dialog box:

Parameter

Description

Input Range

The range where you have the data you want to examine

Grouped By

Do you have the data grouped by columns or rows

Labels

If labels are selected in the input range the box must be ticked

Output Range

Enter a cell reference for the output or New Worksheet Ply to get the output in a new worksheet in the workbook with the input data or New Workbook to get the output in a new workbook

Summary statistics

Tick the box if you want the output statistics summarised

Confidence Level for

Mean

Tick the box if you want the confidence calculated for the average

Kth Largest

Tick the box if you want not only the largest value in the data range shown in the output. By typing 3 in the box the third largest value will be shown in the output

Kth Smallest

Tick the box if you want not only the smallest value in the data range shown in the output. By typing 3 in the box the third smallest value will be shown in the output

When you have finished entering the information click OK and you will get the output.

   

Procedures

1.    To use a Forecast function, first create a data range with data values you know 

2.    Click in the cell where you want to place the function.

3.    Click on the Formulas tab.

4.    In the Function Library group, click on the Insert Function button.

 

Discussion

The Histogram tool can return statistical information from data in Excel and group the output data. You must first create a bin to tell the Histogram tool how you want the output data grouped.

 

In the example above the bind starts with 2000 and goes to 18000 with an increment of 2000. 

You can find the Histogram tool by selecting the Data Analysis command from the Analysis group at the far right end of the data ribbon. Select Histogram from the list and click OK.

If there is no Analysis group or Data Analysis command on the Ribbon, you must install Data Analysis TOOLPAK (please go to Appendix A in this workbook to see how to install Data Analysis TOOLPAK).

After you have entered all necessary information into the Histogram dialog box, you can get the result in the output range. 

You can specify the following items in the Histogram parameters dialog box:

Parameter

Description

Input Range

The range where you have the data you want to examine

Bin Range

Select the range where the bin range has been defined

Labels

If labels are selected in the input range the box must be ticked

Output Range

Enter a cell reference for the output or New Worksheet Ply to get the output in a new worksheet in the workbook with the input data or New Workbook to get the output in a new workbook

Pareto (sorted histogram)

Tick the box if you want the output statistics sorted

Cumulative Percentage

Tick the box if you want cumulative percentage to be a part of the output

Chart output

Tick the box if you want to create a chart to visualise the output

 

The output.

   

Discussion

The Regression tool can return statistical information from data in Excel. The tool can make a linear regression forecast returning the slope and Yinterceptor. It can also return the error or deviation and much more statistical informations.

Simple linear regression analysis can be easily completed using Excel. 

You can find the Regression tool by selecting the Data Analysis command from the Analysis group at the far right end of the data ribbon. Select Regression from the list and click OK.

If there is no Analysis group or Data Analysis command on the Ribbon, you must install Data Analysis TOOLPAK (please go to Appendix A in this workbook to see how to install Data Analysis TOOLPAK).

After you have entered all necessary information into the Regression dialog box, you can get the result in the output range. 

You can specify the following items in the Regression parameters dialog box:

Parameter

Description

Input Y Range

The range where you have the Y data you want to examine (values)

Input X Range The range where you have the X data you want to examine (periods)

Labels

If labels are selected in the input range the box must be ticked

Constant is Zero

Tick the box if you want to work with a constant of zero.

Confidence Level

Enter a percentage from 0 to 100 per cent if you want to work with confidence level 

Output Range

Enter a cell reference for the output or New Worksheet Ply to get the output in a new worksheet in the workbook with the input data or New Workbook to get the output in a new workbook

Residuals

Tick the box if you want the residuals (errors, deviation) as a part of the output

Standardized Residuals

Tick the box if you want the standardized residuals (standard errors, standard deviation) as a part of the output

Residuals Plots

Tick the box if you want the residuals (errors, deviation) visualised in a chart as a part of the output

Line Fit Plots

Tick the box if you want a chart showing the trends as a part of the output

Normal Probability

Tick the box if you want a chart showing the probability as a part of the output

 

The output:

 

Discussion

The Sampling analysis tool creates a sample from a population by treating the input range as a population. When the population is too large to process or visualise in a chart, you can use a representative sample. You can also create a sample that contains only values from a particular part of a cycle if you believe that the input data is periodic.

You can find the Sampling tool by selecting the Data Analysis command from the Analysis group at the far right end of the data ribbon. Select Sampling from the list and click OK.

If there is no Analysis group or Data Analysis command on the Ribbon, you must install Data Analysis TOOLPAK (please go to Appendix A in this workbook to see how to install Data Analysis TOOLPAK).

After you have entered all necessary information into the Sampling dialog box, you can get the result in the output range. 

You can specify the following items in the Sampling parameters dialog box:

Parameter

Description

Input Range

The array where you have the data you want to examine

Labels

If labels are selected in the input range the box must be ticked

Sampling Method

Period

If there is a periodic trend in the data select this option. Enter a period number.

Sampling Method

Random

If you want the tool to select randomly select this option. Enter a number of samples you need.

 

       

Output Range                     Enter a cell reference for the output or New Worksheet Ply to get the output in a new worksheet in the workbook with the input data or New Workbook to get the output in a new workbook

     

& PERCENTILE

Discussion

The Rank and Percentile tool ranks data. For each rank the tool return a percentage for data below the specific ranking. 

Finding and displaying the percentile of specific results can be valuable to your audience. Sometimes stand alone numbers do not tell the story, but, if I know that 80% of the other results are lower than me, that tells a more complete story.

The percentile is defined as the value of a variable below which a certain percent of observations fall. For example, if you score in the 80th percentile on a test, 80% of the other test takers did worse than you. 

You can find the Rank and Percentile tool by selecting the Data Analysis command from the Analysis group at the far right end of the data ribbon. Select Rank and Percentile from the list and click OK.

If there is no Analysis group or Data Analysis command on the Ribbon, you must install Data Analysis TOOLPAK (please go to Appendix A in this workbook to see how to install Data Analysis TOOLPAK).

After you have entered all necessary information into the Rank and Percentile dialog box, you can get the result in the output range. 

You can specify the following items in the Rank and Percentile parameters dialog box:

Parameter

Description

Input Range

The array where you have the data you want to examine

Grouped By

Do you have the data grouped by columns or rows

Labels

If labels are selected in the input range the box must be ticked

Output Range

Enter a cell reference for the output or New Worksheet Ply to get the output in a new worksheet in the workbook with the input data or New Workbook to get the output in a new workbook

 

The output:

 

Adding Data Analysis toolpak

   

DATA ANALYSIS TOOLPAK

Discussion

The Data Analysis Toolpak is not part of the standard Excel application as normally installed, it is an Add-In (often referred to as a “plug-in.”). Add-Ins are additional Excel components that are not commonly used and as a result, have to be installed on your computer. There are many third party (nonMicrosoft) Add-Ins that help Excel do useful and clever things that it cannot normally do, or that enhance some of the things that it can do.

These can be found and downloaded from the Internet although it is wise to consult with your IT department first in case they have objections on grounds of security and computer software safety. It is unlikely that in the working environment you will be able to install these programs yourself anyway.

There are, however, selections of Add-Ins that come with Excel and that are perfectly safe and suitable for use; one of these is ANALYSIS TOOLPAK. This Add-in will add 19 analysis tools to Excel. After installation you will be able to find the ANALYSIS TOOLPAK tools in the data ribbon. 

Procedure

1.    Click the Office Button (Excel 2007) or the File tab (Excel 2010).

2.    Click Excel Options.

Adding Data Analysis toolpak

3.    Click Add-Ins at the left. 

4.    Select Excel Add-ins from the Manage: list at the bottom of the dialog box.

5.    Click Go….

6.    Click in box next to ANALYSIS TOOLPAK Add-in.

 

7.    Click OK.

 

In this appendix, you will learn how to:

•    Use the Right, Left, and Mid functions

•    Use the Concatenate function

•    Use the Len function

•    Use the Find function

•    Use nested text functions


TERMS  

Discussion

Text functions are very useful if you need to clean up text strings, concatenate text, or split a text string to many cells. Specially if you import data from a database for analysing in Excel it is a advantage to know the text functions in Excel.

 

RIGHT, LEFT, AND MID FUNCTIONS  

Discussion

If you want to extract characters from a text string you can use the Left, Right, and Mid function. The Left function can extract characters from the left of the text string, the Right function can extract characters from the right of a text string, and the Mid function can extract characters from the midle of a text string.

The first five characters of the  text string “A37A221regionnorth”are the imployment number for a staff member, the next two characters are department number and the last five characters are region information. To extract the employment number you need the Left function. 

The Left function consists of one required argument and one optional, in the following order: Text, Num_chars. Text is the box in which the cell reference for the cell with the text string must be entered. In the Num_chars box enter the number of characters you want to extract from left.

 

The Mid function consists of three required arguments, in the following order: Text, Start_num, and Num_chars. Text is the box in which the cell reference for the cell with the text string must be entered. In the Start_num box enter the start number of the first character you want to extract from the middle of the text string. Num_chars is the number of characters you want to extract.

 

The Right function consists of one required argument and one optional, in the following order: Text, Num_chars. Text is the box in which the cell reference for the cell with the text string must be entered. In the Num_chars box enter the number of characters you want to extract from right.

 

Procedures

1.    To use a Left function, select a cell where you want the function to extract characters from the left of a text string.

2.    Click on the Formulas tab.

3.    In the Function Library group, click on the Insert Function button.

 

4.    In the Insert function dialog box, locate Text category in the Or select a category: box.

5.    Click on Left.

6.    Click on the OK button.

 

7.    In the Function Arguments dialog box, click in the Text box.

8.    Click the cell containing the text string from which you want to extract the characters.

9.    In the Num_chars box, type the number of characters you want to extract.

10.   Click on the OK button.

 Procedures

1.    To use a Mid function, select a cell where you want the function to extract characters from the middle of a text string.

2.    Click on the Formulas tab.

3.    In the Function Library group, click on the Insert Function button.

4.    category: box.

5.    Click on Mid.

6.    Click on the OK button.

7.    In the Function Arguments dialog box, click in the Text box.

8.    Click the cell containing the text string from which you want to extract the characters.

9.    In the Start_num box, type the number of the first character you want to extract from the middle of you text string.

10.   In the Num_chars box, type the number of characters you want to extract.

11.   Click on the OK button.

Procedures

1.    To use a Right function, select a cell where you want the function to extract characters from the right of a text string.

2.    Click on the Formulas tab.

3.    In the Function Library group, click on the Insert Function button.

 

4.    In the Insert function dialog box, locate Text category in the Or select a category: box.

5.    Click on Right.

6.    Click on the OK button.

 

7.    In the Function Arguments dialog box, click in the Text box.

8.    Click the cell containing the text string from which you want to extract the characters.

9.    In the Num_chars box, type the number of characters you want to extract.

10.   Click on the OK button.

CONCATENATE FUNCTION

Discussion

The Concatenate function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. For example, if your worksheet contains a person's first name in cell A1 and the person's previous name in cell B1, you can combine the two values in another cell by using the following formula: 

=concatenate(A1," ",B1)

The Concatenate function consists of one required argument and as many optional arguments you need up to 254, in the following order: Text1, Text2, Text3, Text4 ….to Text255. Text1 is the box in which the cell reference for the first cell with the text string must be entered. In the Text2 box enter the cell reference of the cell containing the second text string you want to join. In the Text3 box enter the cell reference of the cell containing the third text string you want to join etc If you want to have a space between the text strings you can enter “ “ (“space”) in every second argument.

 Procedures

1.    To use a Concatenate function, select a cell where you want the function to join text strings.

2.    Click on the Formulas tab.

3.    In the Function Library group, click on the Insert Function button.

 

4.    In the Insert function dialog box, locate Text category in the Or select a category: box.

5.    Click on Concatenate.

6.    Click on the OK button.

 

7.    In the Function Arguments dialog box, click in the Text1 box.

8.    Click the cell containing the first text string you want to join.

9.    In the Text2 box type “ “ to get a space between the first and second text string.

10.   In the Text2 box click the cell containing the second text string you want to join.

 

LEN FUNCTION

Discussion

The Len function returns the number of characters in a text string. A space will also be counted as a character.

The Len function consists of one required argument: Text1. In Text1 enter the cell reference for the cell with the text string and the function will return how many characters there is in text string.

Procedures

1.    To use a Len function, select a cell where you want the function to display the result.

2.    Click on the Formulas tab.

3.    In the Function Library group, click on the Insert Function button.

 

4.    In the Insert function dialog box, locate Text category in the Or select a category: box.

5.    Click on Len.

6.    Click on the OK button.

 

7.    In the Function Arguments dialog box, click in the Text box.

8.    Click the cell containing the text string you want to examine.

9.    Click on the OK button.

   

FIND FUNCTION

Discussion

The Find function can find a character or a text string within a text string and return the position of the character or text string. The Find function is very often used together with other text function such as the Mid function.

The Find function consists of two required arguments and one optional argument, in following order: Find_text, Within_text, Start_num. In Find_text you must enter the text string or character you want the function to find (the argument is case sensitive). In Within_text enter the cell reference for the cell with the text string where you want to find the character or text string. In Start_num you can enter the character number you want the function to start the search from. If you in a text string want to find the character “A” and there is two characters “A” then the function will return the position number for the first “A”.

Procedures

1.    To use a Find function, select a cell where you want the function to display the result.

2.    Click on the Formulas tab.

3.    In the Function Library group, click on the Insert Function button.

 

4.    In the Insert function dialog box, locate Text category in the Or select a category: box.

5.    Click on Find.

6.    Click on the OK button.

 

7.    In the Function Arguments dialog box, click in the Find_text box.

8.    Type the character or text string you want the function to find.

9.    Enter the cell reference for the cell you want to examine in the Within_text box.

10.   In the Start_num box enter a number you want the function to start the search from.

 

Discussion

You can nest the text functions to get more dynamic options to extract text strings. May be you have imported text strings from a database and you want to extract different part of the text string to different cells in Excel but you do not know how many characters you want in each cell. You can use a combination of the Right, Left, Mid, Len, and Find functions to solve the problem. There is many solutions in Excel to deal with this but you will see some examples here.

Example 1

You have imported a number of text strings “A4337A221north”, “C2A541south”,”A445A785westh”, and ”C5A654north”. You know that the first part of the text string is the employment number but you do not know how many characters. You know that the second part of the text string is department number and the department number is 2 characters. The last 5 characters  of the text string is the region.

 

You need the employment number in column C, department number in column D, and region in column E. The employment number is a mix of letters and digits but always there is the letter “A” and one digit at the end of the employment number. With this knowledge you can extract the employment number from the text string by using the Left and Find function.

Select the cell where you want the characters extracted.  Start with the Left function. In the Text box enter the cell reference for the cell with the text string.

 

Click in the Num_char box and nest the Find function. The Find function must find the letter “A” the second last character in the employment number. In the Find_text box enter the character you want to find. In the Within_text box enter the cell reference for the cell with the text string. In the Start_num box enter the start number for the search. You don’t want to find “A” if it is the first character in the text string as it is in this example that is why 2 is entered in the box. You want to start the search from character 2.

 

This will find the character number for “A” in the text string but the digit after “A” is also a part of the employment number. What you need to do is to click after the Find function in the Left function dialog box and type “+1”. Click OK button and you have extracted the employment number.

 

To extract the department number you need the Mid function. In the Text box enter the cell reference for the cell with the text string. In Start_num you need to nest the Find function. Again the Find function must find the character “A”. This time you need to type “+2” after the Find function in the Mid  function dialog box because the department number starts 2 characters from “A”. In Number_char box type 2 and click OK.

 

To extract the region you do not need any nested function just use the Right function.

 

Example 2

You have imported a number of text strings “A4337A221northwest”,

“C2A545east”,”A445A785southwesth”, and ”C5A654north”. You know that the first part of the text string is the employment number but you do not know how many characters. You know that the second part of the text string is department number and the department number is 2 characters. The last part of the text string is the region but you do not know how many characters.

 

You need the employment number in column C, department number in column D, and region in column E. The employment number is a mix of letters and digits but always there is the letter “A” and one digit at the end of the employment number. With this knowledge you can extract the employment number from the text string by using the Left and Find function.

Select the cell where you want the characters extracted. Start with the Left function. In the Text box enter the cell reference for the cell with the text string.

Click in the Num_char box and nest the Find function. The Find function must find the letter “A” the second last character in the employment number. In the Find_text box enter the character you want to find. In the Within_text box enter the cell reference for the cell with the text string. In the Start_num box enter the start number for the search. You don’t want to find “A” if it is the first character in the text string as it is in this example that is why 2 is entered in the box.

 

This will find the character number for “A” in the text string but the digit after “A” is also a part of the employment number. What you need to do is to click after the Find function in the Left function dialog box  and type “+1”. Click OK button and you have extracted the employment number.

 

To extract the department number you need the Mid function. In the Text box enter the cell reference for the cell with the text string. In Start_num you need to nest the Find function. The Find function must find the character “A”. This time you need to type “+2” after the Find function in the Mid  function dialog box because the department number starts 2 characters from “A”. In Number_char box type 2 and click OK.

To extract the region without knowing the number of characters you can not use the Right function. you must use a combination of the Mid, Find and Len functions. Start with the Mid function. You need to nest the Find function in Start_num again to find the character “A”. This time you need to type “+4” after the Find functionin the Mid function dialog box to find the position of the first character in the region information. Nest the Len function in Num_char this will reurn the number of characters for the whole text string. Click OK and the Mid function will return the region information.

 

 

A

Absolute Error · 48, 55

Analysis Toolpak · 142

 

B

Break-even · 121, 122, 129

Visualise break-even · 124, 125

 

C

Column charts · 79

Concatenate function · 144, 150, 151

Correl function · 98, 110, 111, 112, 113, 116, 117,

120

Correlation · 110, 113, 114, 116, 117, 120 Correlation Coefficient · 110, 116, 120

 

D

Data Analysis Tool Exponential Smoothing · 7, 28

Data analysis tools

Descriptive statistics · 131

Exponential smooth · 7, 28

Histogram · 130, 133, 134

Regression · 136

Sampling · 130, 138, 139

 

E

Excel Add-ins · 67, 142, 143

Exponential smoothing · 7, 26

 

F

Find function · 144, 154, 155, 156, 157, 158, 159

Forecast accuracy

Absolute error/deviation · 48, 55

Error/deviation · 61

MAD (Mean Absolute Deviation) · 46, 55, 59, 62,

68, 70, 72, 73, 75, 76, 77, 88, 94

MAPE (Mean Absolute Percentage Error) · 46,

58, 62

MPE (Mean Percentage Error) · 46, 57, 62

MSQ (Mean Square error) · 46, 56, 62

Percentage error/deviation · 46, 50, 51

R-Square · 87, 92, 93, 117

Standard error · 53

TSE (Tracking Signal Error) · 46, 59, 62

Forecast function · 7, 9, 10, 13, 21, 25, 38, 39, 41,

42, 45, 94, 95, 98, 107, 108, 109

Forecasting tools

Exponential smoothing · 7, 26

Forecast function · 7, 9, 10, 13, 21, 25, 38, 39,

41, 42, 45, 94, 95, 98, 107, 108, 109

Growth function · 7, 22, 24, 25, 42

Intercept function · 19, 41

Moving average · 86

Naïve forecasting · 30, 44

Seasonal forecasting · 45, 72

Slope function · 18, 41

Trend function · 7, 9, 14, 16, 17, 21, 41

 

G

goal seek · 108, 121, 128 Growth function · 7, 22, 24, 25, 42

 

H

Histogram · 130, 133, 134

 

I

Intercept function · 19, 41

 

L

Left function · 145, 147, 156, 158

Len function · 144, 152, 159 Line charts · 79, 80, 87 linear trendline · 83, 84, 92, 93 logarithmic trendline · 82, 85

 

M

MAD · 46, 55, 59, 62, 68, 70, 72, 73, 75, 76, 77, 88,

94

MAPE · 46, 58, 62

Mid function · 144, 145, 146, 148, 154, 157, 158,

159

Moving average · 86

MPE · 46, 57, 62 MSQ · 46, 56, 62

 

N

Naïve forecasting · 30, 44

P

Pie charts · 79 polynomial trendline · 85

 

R

Regression · 9

Right function · 145, 146, 149, 157, 159

 

S

Sampling · 130, 138, 139

Scenarios · 97, 98, 99, 102, 107, 128

Creating a scenario summary report · 102

Creating scenarios · 100

Deleting a scenario · 101

Edit values in a scenario · 102

Showing a scenario · 101

Seasonal forecasting · 45, 72

Slope function · 18, 41

Solver · 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75,

76, 77, 88, 94, 121, 128 SQRT function · 53, 54, 55

Standard error · 53

Sumxmy2 function · 53

 

T

Table tool · 97, 104, 105, 109

Text functions

Concatenate function · 144, 150, 151

Find function · 144, 154, 155, 156, 157, 158, 159

Left function · 145, 147, 156, 158

Len function · 144, 152, 159

Mid function · 144, 145, 146, 148, 154, 157, 158,

159

Right function · 145, 146, 149, 157, 159 Tracking Signal · 46, 59

Trend function · 7, 9, 14, 16, 17, 21, 41

Trendline · 9, 82, 83, 84, 92, 93, 117, 118 TSE · 46, 59, 62

 

V

Visualise forecasts

Column charts · 79

Line charts · 79, 80, 87

Linear trendline · 83, 84, 92, 93

Logarithmic trendline · 82, 85

Pie charts · 79

Polynomial trendline · 85

Trend lines · 9, 82, 83, 84, 92, 93, 117, 118

 

W

What-if analysis

Goal seek tool · 97, 103, 108, 122, 129

Scenarios tool · 97, 98, 99, 102, 107, 128

Table tool · 97, 104, 105, 109



5