Demand forcasting

Anonymous
timer Asked: Oct 16th, 2018
account_balance_wallet $9.99

Question Description

complete the worksheet using excel (please include excel equations to proof answer). Also, include plots for trendlines.

I have attached lecture note for reference

Unformatted Attachment Preview

Forecasting Chapter 8 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-1 What is a Forecast? Forecast A prediction of future events used for planning purposes. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-2 Demand Patterns • A time series is the repeated observations of • demand for a service or product in their order of occurrence There are five basic time series patterns – Horizontal – Trend – Seasonal – Cyclical – Random Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-3 Quantity Demand Patterns Time Figure 8.1 (a) Horizontal: Data cluster about a horizontal line Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-4 Quantity Demand Patterns Time Figure 8.1 (b) Trend: Data consistently increase or decrease Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-5 Demand Patterns Quantity Year 1 Year 2 | J Figure 8.1 | F | M | A | M | J | J | A | S | O | N | D Months (c) Seasonal: Data consistently show peaks and valleys Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-6 Quantity Demand Patterns | 1 | 2 Figure 8.1 | 3 | 4 | 5 | 6 Years (d) Cyclical: Data reveal gradual increases and decreases over extended periods Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-7 Demand Management Options • Demand Management – The process of changing demand patterns using one or more demand options Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-8 Demand Management Options • • • • • • • Complementary Products Promotional Pricing Prescheduled Appointments Reservations Revenue Management Backlogs Backorders and Stockouts Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-9 Key Decisions on Making Forecasts • Deciding What to Forecast – Level of aggregation – Units of measurement • Choosing the Type of Forecasting Technique – Judgment methods – Causal methods – Time-series analysis – Trend projection using regression Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-10 Forecast Error • • For any forecasting method, it is important to measure the accuracy of its forecasts. Forecast error is simply the difference found by subtracting the forecast from actual demand for a given period, or Et = Dt – Ft where Et = forecast error for period t Dt = actual demand in period t Ft = forecast for period t Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-11 Measures of Forecast Error Cumulative sum of forecast errors (Bias) CFE = Et Average forecast error CFE Ē= n Mean Squared Error MSE = Standard deviation = Copyright ©2016 Pearson Education, Inc. All rights reserved. n– 1 Mean Absolute Deviation MAD = |Et | n Mean Absolute Percent Error Et2 n (Et – Ē ) 2 MAPE = (|Et |/Dt)(100) n 8-12 Example 8.1 The following table shows the actual sales of upholstered chairs for a furniture manufacturer and the forecasts made for each of the last eight months. Calculate CFE, MSE, σ, MAD, and MAPE for this product. Error2 Et2 Month t Demand Dt Forecast Ft Error Et 1 200 225 –25 2 240 220 20 3 300 285 15 4 270 290 –20 5 230 250 –20 400 20 8.7 6 260 240 20 400 20 7.7 7 210 250 –40 1,600 40 19.0 8 275 240 35 1,225 35 12.7 –15 5,275 195 81.3% Total Copyright ©2016 Pearson Education, Inc. All rights reserved. Absolute Error |Et| Absolute % Error (|Et|/Dt)(100) 8-13 Example 8.1 The following table shows the actual sales of upholstered chairs for a furniture manufacturer and the forecasts made for each of the last eight months. Calculate CFE, MSE, σ, MAD, and MAPE for this product. Month t Demand Dt Forecast Ft Error Et Error2 Et2 1 200 225 –25 625 25 12.5% 2 240 220 20 400 20 8.3 3 300 285 15 225 15 5.0 4 270 290 –20 400 20 7.4 5 230 250 –20 400 20 8.7 6 260 240 20 400 20 7.7 7 210 250 –40 1,600 40 19.0 8 275 240 35 1,225 35 12.7 –15 5,275 195 81.3% Total Copyright ©2016 Pearson Education, Inc. All rights reserved. Absolute Error |Et| Absolute % Error (|Et|/Dt)(100) 8-14 Example 8.1 Using the formulas for the measures, we get: Cumulative forecast error (mean bias) CFE = –15 Average forecast error (mean bias): CFE Ē = n = 15 8 Mean squared error: Et2 5,275 MSE = = 8 n Copyright ©2016 Pearson Education, Inc. All rights reserved. = –1.875 = 659.4 8-15 Example 8.1 Standard deviation: = [Et – (–1.875)]2 n–1 = 27.4 Mean absolute deviation: |Et | 195 = 24.4 MAD = n = 8 Mean absolute percent error: (|Et |/ Dt)(100) 81.3% = = 10.2% MAPE = n 8 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-16 Example 8.1 • A CFE of –15 indicates that the forecast has a slight bias to overestimate demand. • The MSE, σ, and MAD statistics provide measures of forecast error variability. • A MAD of 24.4 means that the average forecast error was 24.4 units in absolute value. • The value of σ, 27.4, indicates that the sample distribution of forecast errors has a standard deviation of 27.4 units. • A MAPE of 10.2 percent implies that, on average, the forecast error was about 10 percent of actual demand. These measures become more reliable as the number of periods of data increases. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-17 Judgment Methods • Other methods (casual, time-series, and trend projection using regression) require an adequate history file, which might not be available. • Judgmental forecasts use contextual knowledge gained through experience. – Salesforce estimates – Executive opinion – Market research – Delphi method Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-18 Causal Methods: Linear Regression • • • A dependent variable is related to one or more independent variables by a linear equation The independent variables are assumed to “cause” the results observed in the past Simple linear regression model is a straight line Y = a + bX where Y = dependent variable X = independent variable a = Y-intercept of the line b = slope of the line Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-19 Linear Regression Dependent variable Y Deviation, or error Estimate of Y from regression equation Regression equation: Y = a + bX Actual value of Y Value of X used to estimate Y X Figure 8.3 Independent variable Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-20 Time Series Methods • Naïve forecast • The forecast for the next period equals the demand for the current period (Forecast = Dt) • Horizontal Patterns: Estimating the average • Simple moving average • Weighted moving average • Exponential smoothing Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-21 Simple Moving Averages • Specifically, the forecast for period t + 1 can be calculated at the end of period t (after the actual demand for period t is known) as Sum of last n demands Dt + Dt-1 + Dt-2 + … + Dt-n+1 Ft+1 = = n n where Dt = actual demand in period t n = total number of periods in the average Ft+1 = forecast for period t + 1 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-22 Example 8.3 a. Compute a three-week moving average forecast for the arrival of medical clinic patients in week 4. The numbers of arrivals for the past three weeks were as follows: Week 1 2 3 Patient Arrivals 400 380 411 b. If the actual number of patient arrivals in week 4 is 415, what is the forecast error for week 4? c. What is the forecast for week 5? Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-23 Example 8.3 a. The moving average forecast at the end of week 3 is: F4 = Week Patient Arrivals 1 400 2 380 3 411 411 + 380 + 400 = 397.0 3 b. The forecast error for week 4 is E4 = D4 – F4 = 415 – 397 = 18 c. The forecast for week 5 requires the actual arrivals from weeks 2 through 4, the three most recent weeks of data F5 = 415 + 411 + 380 = 402.0 3 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-24 Weighted Moving Averages In the weighted moving average method, each historical demand in the average can have its own weight, provided that the sum of the weights equals 1.0. The average is obtained by multiplying the weight of each period by the actual demand for that period, and then adding the products together Ft+1 = W1D1 + W2D2 + … + WnDt-n+1 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-25 Application 8.2 Using the customer arrival data in Application 14.1, let W1 = 0.50, W2 = 0.30, and W3 = 0.20. Use the weighted moving average method to forecast arrivals for month 5. F5 = W1D4 + W2D3 + W3D2 = 0.50(790) + 0.30(810) + 0.20(740) = 786 Forecast for month 5 is 786 customer arrivals. Given the number of customers that actually arrived (805), what is the forecast error? E5 = 805 – 786 = 19 Forecast error for month 5 is 19. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-26 Application 8.2 If the actual number of arrivals in month 5 is 805, compute the forecast for month 6: F6 = W1D5 + W2D4 + W3D3 = 0.50(805) + 0.30(790) + 0.20(810) = 801.5 Forecast for month 6 is 802 customer arrivals. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-27 Exponential Smoothing • A sophisticated weighted moving average that calculates the average of a time series by implicitly giving recent demands more weight than earlier demands • Requires only three items of data – The last period’s forecast – The demand for this period – A smoothing parameter, alpha (α), where 0 ≤ α ≤ 1.0 • The equation for the forecast is Ft+1 = α(Demand this period) + (1 – α)(Forecast calculated last period) = αDt + (1 – α)Ft Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-28 Exponential Smoothing • The emphasis given to the most recent demand levels can be adjusted by changing the smoothing parameter. • Larger α values emphasize recent levels of demand and result in forecasts more responsive to changes in the underlying average. • Smaller α values treat past demand more uniformly and result in more stable forecasts. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-29 Application 8.3 Suppose that there were 790 arrivals in month 4 (Dt ), whereas the forecast (Ft) was for 783 arrivals. Use exponential smoothing with α = 0.20 to compute the forecast for month 5. Ft+1 = Ft + α(Dt – Ft) 783 + 0.20(790 – 783) = 784.4 Forecast for month 5 is 784 customer arrivals Given the number of patients that actually arrived (805), what is the forecast error? E5 = 805 – 784 = 21 Forecast error for month 5 is 21 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-30 Application 8.3 Given the actual number of arrivals in month 5, what is the forecast for month 6? Ft+1 = Ft + α(Dt – Ft) = 784.4 + 0.20(805 – 784.4) = 788.52 Forecast for month 6 is 789 customer arrivals Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-31 Trend Patterns: Using Regression • A trend in a time series is a systematic increase or decrease in the average of the series over time • The forecast can be improved by calculating an estimate of the trend • Trend Projection with Regression accounts for the trend with simple regression analysis. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-32 Example 8.5 • Medanalysis, Inc., provides medical laboratory services • Managers are interested in forecasting the number of blood analysis requests per week • There has been a national increase in requests for standard blood tests. • The arrivals over the next 16 weeks are given in Table 8.1. • What is the forecasted demand for the next three periods? Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-33 Example 8.5 Arrivals at Medanalysis, Inc. Week Arrivals Week Arrivals 1 28 9 61 2 27 10 39 3 44 11 55 4 37 12 54 5 35 13 52 6 53 14 60 7 38 15 60 8 57 16 75 Table 8.1 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-34 Example 8.5 Figure 8.6(a) Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-35 Example 8.5 Figure 8.6(b) Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-36 Seasonal Patterns: Using Seasonal Factors Multiplicative seasonal method A method whereby seasonal factors are multiplied by an estimate of average demand to arrive at a seasonal forecast. Additive seasonal method A method in which seasonal forecasts are generated by adding a constant to the estimate of average demand per season. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-37 Multiplicative Seasonal Method Multiplicative seasonal method 1. For each year, calculate the average demand for each season by dividing annual demand by the number of seasons per year. 2. For each year, divide the actual demand for each season by the average demand per season, resulting in a seasonal factor for each season. 3. Calculate the average seasonal factor for each season using the results from Step 2. 4. Calculate each season’s forecast for next year. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-38 Example 8.6 The manager of the Stanley Steemer carpet cleaning company needs a quarterly forecast of the number of customers expected next year. The carpet cleaning business is seasonal, with a peak in the third quarter and a trough in the first quarter. Following are the quarterly demand data from the past 4 years: The manager wants to forecast customer demand for each quarter of year 5, based on an estimate of total year 5 demand of 2,600 customers. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-39 Example 8.6 YEAR 1 YEAR 2 Q Demand Seasonal Factor (1) 1 45 45/250 = 0.18 70 70/300 = 0.23 2 335 335/250 = 1.34 370 370/300 = 1.23 3 520 520/250 = 2.08 590 590/300 = 1.97 4 100 100/250 = 0.40 170 170/300 = 0.57 Total 1,000 Average 1,000/4 = 250 Copyright ©2016 Pearson Education, Inc. All rights reserved. Demand Seasonal Factor (2) 1,200 1,200/4 = 300 8-40 Example 8.6 YEAR 3 YEAR 4 Q Demand Seasonal Factor (3) 1 100 100/450 = 0.22 100 100/550 = 0.18 2 585 585/450 = 1.30 725 725/550 = 1.32 3 830 830/450 = 1.84 1160 1160/550 = 2.11 4 285 285/450 = 0.63 215 215/550 = 0.39 Total 1,800 Average 1,800/4 = 450 Copyright ©2016 Pearson Education, Inc. All rights reserved. Demand Seasonal Factor (4) 2,200 2,200/4 = 550 8-41 Example 8.6 Average Seasonal Factor Quarterly Forecasts Quarter Average Seasonal Factor Quarter Forecast 1 0.2043 1 650 x 0.2043 = 132.795 2 1.2979 2 650 x 1.2979 = 843.635 3 2.0001 3 650 x 2.001 = 1,300.06 4 0.4977 4 650 x 0.4977 = 323.505 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-42 Application 8.5 Suppose the multiplicative seasonal method is being used to forecast customer demand. The actual demand and seasonal indices are shown below. Year 1 Year 2 Average Index Quarter Demand Index Demand Index 1 100 0.40 192 0.64 0.52 2 400 1.60 408 1.36 1.48 3 300 1.20 384 1.28 1.24 4 200 0.80 216 0.72 0.76 Average 250 Copyright ©2016 Pearson Education, Inc. All rights reserved. 300 8-43 Application 8.5 If the projected demand for Year 3 is 1320 units, what is the forecast for each quarter of that year? 1320 units ÷ 4 quarters = 330 units Quarter Average Index 1 0.52 2 1.48 3 1.24 4 0.76 Forecast for Quarter 1 = 0.52(330) ≈ 172 units Forecast for Quarter 2 = 1.48(330) ≈ 488 units Forecast for Quarter 3 = 1.24(330) ≈ 409 units Forecast for Quarter 4 = 0.76(330) ≈ 251 units Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-44 Criteria for Selecting Time-Series Method • Criteria: – Minimizing bias (CFE) – Minimizing MAPE, MAD, or MSE – Maximizing r2 for trend projections using regression – Using a holdout sample analysis – Using a tracking signal – Meeting managerial expectations of changes in the components of demand. – Minimizing the forecast errors in recent periods. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-45 Choosing a Time-Series Method • Using Statistical Criteria: – For more stable demand patterns, use lower a values or larger n values to emphasize historical experience. – For more dynamic demand patters, use higher a values or smaller n values. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-46 Choosing a Time-Series Method • Holdout sample – Actual demands from the more recent time periods in the time series that are set aside to test different models developed from the earlier time periods. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-47 Forecasting as a Process Adjust history file 1 Prepare initial forecasts 2 Consensus meetings and collaboration 3 Finalize and communicate 6 Review by Operating Committee 5 Revise forecasts 4 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-48 Using Multiple Forecasting Methods • Combination forecasts • Judgmental adjustments • Focus forecasting Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-49 Forecasting Principles SOME PRINCIPLES FOR THE FORECASTING PROCESS  Better processes yield better forecasts  Demand forecasting is being done in virtually every company, either formally or informally. The challenge is to do it well—better than the competition  Better forecasts result in better customer service and lower costs, as well as better relationships with suppliers and customers  The forecast can and must make sense based on the big picture, economic outlook, market share, and so on  The best way to improve forecast accuracy is to focus on reducing forecast error  Bias is the worst kind of forecast error; strive for zero bias  Whenever possible, forecast at more aggregate levels. Forecast in detail only where necessary  Far more can be gained by people collaborating and communicating well than by using the most advanced forecasting technique or model Table 8.2 Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-50 Adding Collaboration to the Process CPFR Collaborative planning, forecasting, and replenishment A process for supply chain integration that allows a supplier and its customers to collaborate on making the forecast by using the Internet. Copyright ©2016 Pearson Education, Inc. All rights reserved. 8-51 BA 314 Forecasting Worksheet Name: Use Excel to calculate the values to fill in the empty boxes. Feel free to add additional tables and calculations as needed. Please use the assignment 1 discussion board to ask questions. Once completed, save this file and upload it in Canvas. Historical Demand Data 2013 to 2017: The table reproduced below is the demand data for a company (aggregated) for the previous five years. 2013 2014 2015 2016 2017 Q1 9632 15034 16117 15565 16470 Q2 22245 26824 24169 19151 32858 Q3 11646 11314 14505 11392 19278 Q4 Annual Demand Average Quarterly Demand 11355 10698 11176 10613 13934 Forecasting Using Moving Average Methods Using the historical demand data above, you are to determine the total annual demand forecast for 2017 an ➢ the three-period moving average forecasting method ➢ the three-period weighted moving average method with weights of .5, .4, and .1 Enter your forecast results in the following tables. 2017 2017 Annual Annual Forecast Forecast Using a Using a Weighted Moving Moving Average Average 2018 2018 Annual Annual Forecast Forecast Using a Using a Weighted Moving Moving Average ...
Purchase answer to see full attachment

Tutor Answer

uoscar
School: Carnegie Mellon University

The solution is attached.Remember to Include your name in the Excel file.Please, give me feedback.

BA 314 Forecasting Worksheet
Name:
Use Excel to calculate the values to fill in the empty boxes. Feel free to add additional tables and calculations as
needed. Please use the assignment 1 discussion board to ask questions. Once completed, save this file and
upload it in Canvas.

Historical Demand Data 2013 to 2017:
The table reproduced below is the demand data for a company (aggregated) for the previous five years.
2013

2014

2015

2016

2017

Q1

9632

15034

16117

15565

16470

Q2

22245

26824

24169

19151

32858

Q3

11646

11314

14505

11392

19278

Q4
Annual
Demand
Average
Quarterly
Demand

11355

10698

11176

10613

13934

54878

63870

65967

56721

82540

13720

15968

16492

14180

20635

Forecasting Using Moving Average Methods
Using the historical demand data above, you are to determine the total annual demand forecast for 2017 an
➢ the three-period moving average forecasting method
➢ the three-period weighted moving average method with weights of .5, .4, and .1
Enter your forecast results in the following tables.
2017
2017
Annual
Annual
Forecast
Forecast
Using a
Using a
Weighted
Moving
Moving
Average
Average
62,186

61,134

2018
...

flag Report DMCA
Review

Anonymous
Thank you! Reasonably priced given the quality not just of the tutors but the moderators too. They were helpful and accommodating given my needs.

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors