Forecasting and Business Analysis

A Practice session This practice session will provide a review of some of the basic skills needed in Excel. It is expected that most students will have covered this material before and that this is simply a refresher. If you have not covered all of this material before then you may also find it necessary to use a simple "how to" type Excel reference in order for you to get started with Excel. What you will do in this Session? This session will take you through the worked example that is used for a simple naive model that includes a proportion of the change in actual values. It is a very simple model so the calculations should be easy to follow. The example is built up in several steps so that you can become more familiar with Excel but hopefully not be lost in any of the steps. In particular you will learn - how to input a function and formula - the difference between relative and absolute referencing and how to use each - how to copy a formula to make a single formula apply to a group of cells - how to use cell naming How do I follow this session? Its easy. At the bottom of this page you will see a number of "tabs". They look like this. You are currently looking at the Introduction Tab. So its highlighted. If you click on another tab, the sheet will open. To follow this session simply worked through the tabs in order! I hope that you find this session to be useful

Ft is the forecast at time t At-1 is the actual value at time t-1. The value in the period before t. At-1-At-2 is the change in the actual values between t-1 and t-2 Pis the weight In this example a 50% weight (p=.5) is used

The naïve forecasting model used in this session uses the last actual value and the last CHANGE in actual values in order to make a forecast. The proceeding actual value is adjusted depending on size and direction of the last change in actual values. This change is then weighted using a proportion P. This can be expressed as:-

Ft=At-1+P(At-1-At-2)

This is what we are aiming to produce. It’s a simple spreadsheet to make a naïve forecast. The concept of the forecast is explained in the text box below and the final worked example is shown. We will be working towards this outcome through the various steps.

Relative Cell References This sheet uses a relative cell reference. This means that as you copy the formula it will "point" to a new set of cells. In the next step we examine an absolute cell reference. At this time you should go to the Excel Help system and learn more about cell referencing. To do this open the Help System and search for relative cell reference in the index.

Now move your cursor over cell D8. A large Ft will appear to show you that this is what we are calculating. I have added similar markers for At-1 and At-2. (This will not normally happen in your spreadsheets). Now click the left mouse button. The formula will appear in the formula bar. Move your cursor into the formula bar as in the diagram on the right and click the left hand mouse button when the cursor is in the formula bar. You should see the formula and relevant cells change colour. This should help you to match the cells in the formula with the original formula.

Ft=At-1+P(At-1-At-2)

We can start by entering the formula into the appropriate cell. The formula is :-

In this example the first actual values are in cell C6 and C7. We will make our first forecast in period 3 and enter this into a new column headed Forecast2. We can enter the formula as shown below. Please enter this formula into cell D8.

=C7+0.5*(C7-C6)

You should get and answer of 10.8. This is the first period when we can make a forecast since we need two proceeding actual values.

We can now copy this formula to apply to the full range of data. Move you cursor to cell D8 and click the left mouse button. Now move the cursor to the lower left hand corner of the cell and the cursor should change to the cursor as indicated on the diagram.

Now click and HOLD the left hand mouse button and move the mouse down the spreadsheet. This will "drag down" the formula. You can drag it down to cell D16

At-2

At-1

Ft

In step 1 the proportion was included as a fixed value (.5). In this step we will add the proportion as a cell reference. The proportion is input into cell D2. The formula can be modified to point to this cell. The formula becomes

=C7+$D$2*(C7-C6)

Note that the reference to D2 is shown as $D$2. This is an absolute reference. Unlike the relative reference used before, this part of the formula will always point to exactly the cell D2 even when "dragged down". You can easily make a cell reference absolute by using the F4 key.

If you "drag down" the formula now, you will find that the answer remains as before. The advantage of this method is that you can now change the proportion for the whole forecast by simply changing the value in D2. Using the same method as in step one, drag the formula down until period 11. Now try changing this value and see the effect n the forecast values.

In this example the absolute reference refers to an absolute column ($D) and an absolute row ($2). It is also possible to keep one part of the reference absolute, while leaving the other section relative. So $D2 would mean that the reference is always to Column D but that as the formula is copied the row reference will change. There is a good discussion of this in the Excel help menu under the heading, the difference between relative and absolute references. You should read this as you will need to use all forms of relative and absolute references in later problems.

Absolute Cell References This sheet uses a combination of relative and absolute cell references. This means that as some references will always point to a specific or absolute cell regardless of how or where the formula is copied

If you click on cell D2 you will see that the letter P appears in the Name Box as in the diagram on the right. Since the proportion is now named P we can use the formula below to calculate the naïve forecast. To name a cell, simply click on a cell, then move the cursor to the name box and type in the name

Cell Names as a Reference This sheet uses Cell Names. Cell names are very useful in complex formula's or sheets as it makes it easier to keep track of variables. You should read about cell names in in the Excel Help under the heading, about labels and names in formulas.

=C7+P*(C7-C6)

In step 2 the proportion was included by using an absolute cell reference. In more complex situations we may find that naming the cell is easier. A named call can be referred to by name rather than the cell reference. This is particularly useful if we are using a large number of variables or the same variable on multiple sheets. In this case we call the Proportion, P.

Try entering a cell name. Click on cell D16 and name it forecast by typing this in the name box. When you have typed it hit the return (enter) key. Now click on cell C16 and enter the formula =forecast. This should give you the value for the cell that you have named the forecast.