- Length: 2 pages
- Sources: 2
- Subject: Teaching
- Type: Essay
- Paper: #70957537
- Related Topics:
__Accounting__,__Supply And Demand__,__Excel__,__Regression Analysis__

Table 1; Calculations to create the index

1

2

3

4

Month

Average

Year 1

Year 2

Year 3

Year 4

1

39,600

0.45

1.14

1.51

0.90

2

37,080

0.53

1.25

0.83

1.38

3

30,000

0.52

0.74

1.59

1.15

4

59,210

0.91

0.70

1.25

1.15

5

64,375

1.29

0.71

0.94

1.06

6

57,750

1.26

0.72

0.96

1.06

7

47,370

1.17

0.84

0.68

1.32

8

56,638

1.01

1.13

0.68

1.17

9

29,855

0.52

1.59

0.84

1.05

10

39,638

0.70

1.09

1.29

0.92

11

27,323

0.78

1.44

1.16

0.61

12

19,350

0.88

0.53

1.61

0.98

With the creation of the index for each moth, this may then be used to assess the most likely demand. The most appropriate method is the use of the least square regression. This uses the data from the previous years and places them on a graph, drawing a straight line through the points so it has the least distance from the different points. The future forecasts are assumed to be on this line. The equation for the line can be used to calculate forecasts. The graph for month 1 is shown below in figure 1.

Figure 2; Graph for Month 1 demand

The line shows the general trend and is the closest the line can be drawn to all the points. In Excel it is possible for the program to calculate the slope of the lie with an equation, for month 1 the equation is y = 0.1697x+0.5758, x will be the period for which the forecast (y) is being calculated. This gives a result of 1.42. The index level form month 1 is forecast at 1.42, this may then be converted to the actual amount by multiplying the index by the base line (Shmueli, 2012). This can be repeated for each month

. This gives the forecast demand shown in…

O'Connell, Richard; Koehler, Anne, (2004), Forecasting, Time Series, and Regression, South Western Collage Publishers

Shmueli, G, (2012), Practical Time Series Forecasting: A Hands-on Guide, CreateSpace Independent Publishing Platform

The forecast function in Excel prevents the need for drawing the graph