Spreadsheet Modeling Decision Analysis

0
0
1772 days ago, 629 views
PowerPoint PPT Presentation
Time Series Forecasting. Part 11. Prologue to Time Series Analysis. A period arrangement is an arrangement of perceptions on a quantitative variable gathered over time.ExamplesDow Jones Industrial AveragesHistorical information on deals, stock, client checks, financing costs, costs, etcBusinesses are regularly extremely intrigued by guaging time arrangement variables.Often, free variables are not accessible t

Presentation Transcript

Slide 1

´╗┐Spreadsheet Modeling & Decision Analysis A Practical Introduction to Management Science 5 th release Cliff T. Ragsdale

Slide 2

Chapter 11 Time Series Forecasting

Slide 3

Introduction to Time Series Analysis A period arrangement is an arrangement of perceptions on a quantitative variable gathered after some time. Illustrations Dow Jones Industrial Averages Historical information on deals, stock, client numbers, loan costs, costs, and so forth Businesses are frequently exceptionally inspired by determining time arrangement factors. Regularly, free factors are not accessible to assemble a relapse model of a period arrangement variable. In time arrangement investigation, we dissect the past conduct of a variable with a specific end goal to anticipate its future conduct.

Slide 4

Some Time Series Terms Stationary Data - a period arrangement variable showing no critical upward or descending pattern after some time. Nonstationary Data - a period arrangement variable showing a huge upward or descending pattern after some time. Occasional Data - a period arrangement variable showing a rehashing designs at general interims after some time.

Slide 5

Approaching Time Series Analysis There are numerous, a wide range of time arrangement procedures. It is typically difficult to know which method will be best for a specific informational collection. It is standard to experiment with a few unique strategies and select the one that appears to work best. To be a powerful time arrangement modeler, you have to keep a few time arrangement strategies in your "tool kit."

Slide 6

Measuring Accuracy We require an approach to think about various time arrangement procedures for a given informational index. Four normal strategies are the: mean outright deviation, mean total percent blunder, the mean square mistake, root mean square blunder. We will concentrate on the MSE.

Slide 7

Extrapolation Models Extrapolation models attempt to represent the past conduct of a period arrangement variable with an end goal to anticipate the future conduct of the variable . We'll first discuss a few extrapolation methods that are suitable for stationary information.

Slide 8

An Example Electra-City is a retail location that offers sound and video gear for the home and auto. Every month the supervisor of the store must request stock from a far off distribution center. Right now, the chief is attempting to gauge what number of VCRs the store is probably going to offer in the following month. He has gathered 24 months of information. See document Fig11-1.xls

Slide 9

Moving Averages No broad strategy exists for deciding k . We should experiment with a few k qualities to perceive what works best.

Slide 10

Implementing the Model See record Fig11-2.xls

Slide 11

A Comment on Comparing MSE Values Care ought to be taken when looking at MSE estimations of two diverse guaging strategies. The most reduced MSE may come about because of a strategy that fits more seasoned values extremely well however fits late values inadequately. It is now and then shrewd to process the MSE utilizing just the latest qualities.

Slide 12

Forecasting With The Moving Average Model Forecasts for eras 25 and 26 at day and age 24:

Slide 13

The weighted moving normal system takes into account distinctive weights to be appointed to past perceptions. Weighted Moving Average The moving normal strategy doles out equivalent weight to every single past perception We should decide values for k and the w i

Slide 14

Implementing the Model See record Fig11-4.xls

Slide 15

Forecasting With The Weighted Moving Average Model Forecasts for eras 25 and 26 at day and age 24:

Slide 16

It can be demonstrated that the above condition is comparable to: Exponential Smoothing

Slide 17

Examples of Two Exponential Smoothing Functions

Slide 18

Implementing the Model See document Fig11-8.xls

Slide 19

Forecasting With The Exponential Smoothing Model Forecasts for eras 25 and 26 at day and age 24: Note that,

Slide 20

Seasonality is a standard, rehashing design in time arrangement information. May be added substance or multiplicative in nature...

Slide 21

Stationary Seasonal Effects

Slide 22

Stationary Data With Additive Seasonal Effects E t is the normal level at day and age t. S t is the regular element for day and age t. where p speaks to the quantity of occasional periods

Slide 23

Implementing the Model See document Fig11-13.xls

Slide 24

Forecasting With The Additive Seasonal Effects Model Forecasts for eras 25 to 28 at era 24:

Slide 25

Stationary Data With Multiplicative Seasonal Effects E t is the normal level at day and age t. S t is the regular variable for day and age t. where p speaks to the quantity of regular periods

Slide 26

Implementing the Model See record Fig11-16.xls

Slide 27

Forecasting With The Multiplicative Seasonal Effects Model Forecasts for eras 25 to 28 at era 24:

Slide 28

Trend Models Trend is the long haul range or general course of development in a period arrangement. We'll now think of some as nonstationary time arrangement procedures that are suitable for information displaying upward or descending patterns.

Slide 29

An Example WaterCraft Inc. is a producer of individual watercrafts (otherwise called stream skis). The organization has delighted in a genuinely enduring development in offers of its items. The officers of the organization are planning deals and assembling anticipates the coming year. Figures are required of the level of offers that the organization hopes to accomplish each quarter. See document Fig11-19.xls

Slide 30

Double Moving Average E t is the normal base level at day and age t. T is the normal pattern at day and age t. where

Slide 31

Implementing the Model See record Fig11-20.xls

Slide 32

Forecasting With The Double Moving Average Model Forecasts for eras 21 to 24 at day and age 20:

Slide 33

where E t = a Y t + (1-an )(E t - 1 + t - 1 ) T t = b (E t - E t - 1 ) + (1-b ) t - 1 Double Exponential Smoothing (Holt's Method) E t is the normal base level at day and age t. T is the normal pattern at era t.

Slide 34

Implementing the Model See record Fig11-22.xls

Slide 35

Forecasting With Holt's Model Forecasts for eras 21 to 24 at day and age 20:

Slide 36

Holt-Winter's Method For Additive Seasonal Effects where

Slide 37

Implementing the Model See document Fig11-25.xls

Slide 38

Forecasting With Holt-Winter's Additive Seasonal Effects Method Forecasts for eras 21 to 24 at era 20:

Slide 39

Holt-Winter's Method For Multiplicative Seasonal Effects where

Slide 40

Implementing the Model See record Fig11-28.xls

Slide 41

Forecasting With Holt-Winter's Multiplicative Seasonal Effects Method Forecasts for eras 21 to 24 at day and age 20:

Slide 42

The Linear Trend Model For instance:

Slide 43

Implementing the Model See record Fig11-31.xls

Slide 44

Forecasting With The Linear Trend Model Forecasts for eras 21 to 24 at day and age 20:

Slide 45

The TREND() Function TREND(Y-go, X-go, X-esteem for expectation) where: Y-range is the spreadsheet run containing the reliant Y variable, X-range is the spreadsheet run containing the autonomous X variable(s), X-esteem for forecast is a cell (or cells) containing the qualities for the free X variable(s) for which we need an expected estimation of Y. Take note of: The TREND( ) capacity is powerfully refreshed at whatever point any contributions to the capacity change. Notwithstanding, it doesn't give the measurable data gave by the relapse apparatus. It is best two utilize these two distinctive ways to deal with doing relapse in conjunction with each other.

Slide 46

The Quadratic Trend Model

Slide 47

Implementing the Model See document Fig11-34.xls

Slide 48

Forecasting With The Quadratic Trend Model Forecasts for eras 21 to 24 at day and age 20:

Slide 49

Computing Multiplicative Seasonal Indices We can register multiplicative occasional modification files for period p as takes after: The last figure for period i is then

Slide 50

Implementing the Model See record Fig11-37.xls

Slide 51

Forecasting With Seasonal Factors Applied To The Quadratic Trend Model Forecasts for eras 21 to 24 at era 20:

Slide 52

Summary of the Calculation and Use of Seasonal Indices 1. Make a pattern demonstrate and compute the assessed esteem ( ) for every perception in the specimen. 2. For every perception, figure the proportion of the real incentive to the anticipated pattern esteem: (For added substance impacts, register the distinction: 3. For each season, figure the normal of the proportions ascertained in step 2. These are the regular lists. 4. Duplicate any conjecture created by the pattern demonstrate by the suitable occasional file ascertained in step 3. (For added substance occasional impacts, add the suitable element to the conjecture.)

Slide 53

Refining the Seasonal Indices Note that Solver can be utilized to at the same time decide the ideal estimations of the regular lists and the parameters of the pattern model being utilized. There is no assurance that this will create a superior gauge, yet it ought to deliver a model that fits the information better as far as the MSE. See document Fig11-39.xls

Slide 54

Our case issue includes quarterly information, so p =4 and we characterize the accompanying 3 pointer factors: Seasonal Regression Models Indicator factors may likewise be utilized as a part of relapse models to speak to occasional impacts. On the off chance that there are p seasons, we require p - 1 marker factors.

Slide 55

Implementing the Model The relapse capacity is: See document Fig11-42.xls

Slide 56

Forecasting With The Seasonal Regression Model Forecasts for eras 21 to 24 at day and age 20:

Slide 57

Crystal Ball (CB) Predictor CB Predictor is an include that rearranges the way toward performing time arrangement investigation in Excel. A trial rendition of CB Predictor is accessible on the CD-ROM going with this book. For more data on CB Predictor see: http

SPONSORS