Signature assignment

Running head: FORECASTING QUANTITY USING EXCEL 1

FORECASTING QUANTITY USING EXCEL 4

Forecasting Quantity Using Excel

Student’s Name

Institution

Forecasting Quantity Using Excel

Introduction

In the ever dynamic business environment the ability to accurately forecast sales is of absolute importance. In the absence of accurate forecasts organizations waste resources by having overstocking or under stocking. Organizations utilize different techniques for guaging to precisely foresee client request. Anticipating can either be qualitative or quantitative. Quantitative guaging uses recorded information in anticipating customer request (Nahmias & Olsen, 2015). This paper focuses on qualitative approaches, particularly, moving averages and exponential smoothing methods of forecasting using excel spreadsheet.

Quantitative Forecasting

Moving averages

These are the most straightforward strategy for quantitative determining. It is for the most part a smoothing model that outcomes in more precise estimates. The primary thought behind moving normal is to utilize information from y periods to gauge future request. Longer moving midpoints dependably result in smooth informational collections (Nahmias & Cheng, 2009). Mathematically;

Moving average= ∑n Dt /n

t=1

Where Dt is the data in period t and n is the number of periods in moving averages.

Moving Average using Excel Spreadsheet

In the demand data (show in the excel sheet), the restaurant manager needs to gauge interest for ribs for one year from now. In the illustration, the manager has the genuine request information for as far back as 22 years. He chooses to utilize a 3-year and a 5-year moving average to decide or estimate the next year's request. The scientific recipe for three year and five year moving midpoints are as per the following;

3 Year Moving Average = (Dt + Dt -1 + Dt-2)

3

5 Year Moving Average = (Dt +Dt-1 +Dt-2 +Dt-3+ Dt-5)

5

Where D represents demand and t is time.

The moving averages forecasts are developed manually using the following formulae in the excel spreadsheet

	A	B	C	D
3	Year	Sales (000)	3-year moving average	5-year moving average
6	1980	52.04
7	1981	59.42
8	1982	55.66
9	1983	53.86	=(B8+B7+B6)/3
10	1984	64.59	=(B9+B8+B7)/3
11	1985	75.28	=(B10+B9+B8)/3	=(B10+B9+B8+B7+B6)/5
12	1986	61.89	=(B11+B10+B9)/3	=(B11+B10+B9+B8+B7)/5
13	1987	73.74	=(B12+B11+B10)/3	=(B12+B11+B10+B9+B8)/5
14	1988	81.19	=(B13+B12+B11)/3	=(B13+B12+B11+B10+B9)/5
15	1989	97.52	=(B14+B13+B12)/3	=(B14+B13+B12+B11+B10)/5

The actual forecasts for the 22 years are contained in the excel sheet. Based on the moving average analysis, the three year moving average sales forecast is 92,716 United States dollars while the 5 year moving average sales forecast is 92,056 United States dollars.

Exponential Smoothing

This is one of the most commonly used methods of quantitative forecasting because of its cost effectiveness as well as short term precision. Exponential smoothing is slightly complex compared to the traditional moving average method of forecasting. Exponential smoothing combines previous forecasts with weighted error measurements, which is the difference between the previous forecast and actual observation during prior forecast. Also, it requires users to select value for alpha, which must be between 0 and 1.0 (Hyndman et al., 2008). Mathematically,

Exponential Smoothing = Ft+1 = Ft +α* et

Where t = time

Ft = Forecast at time t

α = the smoothing constant, which is a value between 0 and 1.0

et = Dt-Ft

Where Dt =Real perception of time t

Hence, the stated below is the newly written equation;

Ft+1 = Ft +α* (Dt-Ft)

Exponential Smoothing Using Excel Sheet

In the illustration, alpha is set at 0.1. Likewise, bootstrapping technique is utilized to introduce the gauge for the main time frame. The bootstrap technique expect that the main year's business conjecture equivalents to the principal year's business request (see the exceed expectations sheet, cell B6 is the same as cell E6). Utilizing a similar request information, the exponential smoothing gauge is built up utilizing the accompanying formula.

	A	B	G
3	Year	Sales (000)	Exponential Smoothing Forecast (alpha= 0.1)
5			0.1
6	1980	52.04	=B6
7	1981	59.42	=G5*B6+1(1-G5)*G6
8	1982	55.66	=$G$5*B7+(1-$G$5)*G7
9	1983	53.86	=G5*B8+(1-G5)*G8
10	1984	64.59	=G5*B9+(1-G5)*G9
11	1985	75.28	=G5*B10+(1-G5)*G10

Mean Absolute Deviation

Forecasters tend to use mean absolute deviation to measure model error. Mathematically,

Mean Absolute Deviation = ∑n (Dt- Ft) /n

t=1

The mean absolute deviation for 3-year and 5-year moving midpoints are - 3.12 and - 4.48 separately. One might say that the five year moving normal model is superior to anything the three year moving normal model in determining future deals as a result of a lower mean absolute deviation. The mean absolute deviation of smoothing exponential, then again, is - 13.49. By and large, in view of the mean absolute deviation the smoothing exponential is the best model for guaging on the grounds that it has the most minimal mean absolute deviation value.

References

Nahmias, S., & Cheng, Y. (2009). Production and operations analysis (Vol. 6). New York, NY: McGraw-Hill.

Nahmias, S., & Olsen, T. L. (2015). Production and operations analysis. Long Grove, IL: Waveland Press.

Hyndman, R., Koehler, A. B., Ord, J. K., & Snyder, R. D. (2008). Forecasting with exponential smoothing: the state space approach. Berlin, Germany: Springer Science & Business Media.