How to calculate forecast bias in excel

Technology And Information Management

01/28 Problem 1

01/29 Problem 2

01/30 Problem 3

01/31 Problem 4

1. Seven-Eleven Japan a. Define:

i. What has Seven-Eleven done in its choice of facility location, inventory management, transportation, and information infrastructure to develop capabilities that support its supply chain strategy in Japan?

ii. Seven-Eleven is attempting to duplicate the supply chain structure that has succeeded in Japan in the United States with the introduction of CDCs. What are the pros and cons of this approach? Keep in mind that stores are also replenished by wholesalers and DSD by manufacturers.

b. Plan: i. Read Seven-Eleven Japan Case Study ii. Answer study questions 3 and 6

c. Execute: Seven-Eleven Japan’s system is focused on both efficiency and responsiveness. They would enter a market with 50 to 60 stores supported by a distribution center. By doing so, the transportation and replenishment costs are lowered. Seven Eleven saturates an area with their stores so that customers can easily shop and it makes it easier for delivery trucks to replenish store inventory. The distribution system Seven Eleven implemented reduced daily visits from trucks from 70 to 11 in the span of 20 years. Seven-Eleven’s information infrastructure is an integrated store information system. All Seven Eleven stores are linked to the headquarters, suppliers, and distribution centers. The Total Information System implemented in every store had new hardware:

- A graphic order terminal allowed the store owner or manager to place orders based on sales data analysis which they could also view from the terminal. The orders would be relayed to appropriate vendors and the distribution center.

- A scanner terminal reduced replenishment time and delivery time by reading bar codes and recording inventory so that truck drivers wouldn’t have to wait in the store till the packages were checked.

- The store computer linked to the POS system, ISDN network, graphic order terminal, and scanner terminal. This computer could track store inventory and sales, place orders, provide detailed analysis of POS data, and maintained and regulated store equipment.

- The POS register records information as soon as a customer purchases an item including age and sex of the customer. The sales data is collected by 11pm and is organized and ready by next morning.

This information system allowed Seven Eleven to match supply with demand as store staff could adjust merchandise based on what was popular throughout the day. Seven Eleven in the US attempted to duplicate the supply chain. Here are the pros and cons Pros:

- Fresh supply of items - Efficient operations

Cons:

- DSDs allowed manufacturers to have more control of what was being received by the store.

- DSDs increased receiving costs at stores because of increased deliveries. - Seven Eleven’s system depends on the area its in as it operates mostly in densely

populated areas.

d. Check: I have checked my work from the reading I did off the case study.

e. Learn and Generalize: Seven Eleven’s system is an excellent example of a very successful supply chain. The Japanese have been known for their highly successful methods and Seven Eleven demonstrates their skill. Seven Eleven’s success has allowed them to dominate in the market and have a larger presence. Their market dominating strategy has further pushed their revenue by billions of yen within a few years.

2. Demand Forecasting a. Define:

i. What role does forecasting play in the supply chain of a build-to-order manufacturer such as Dell?

ii. How could Dell use collaborative forecasting with its suppliers to improve its supply chain?

iii. What information does the MSE, MAD and MAPE provide to a manager? How can the manager use this information?

iv. What information do the bias and TS provide to a manager? How can the manager use this information?

b. Plan

i. Review Demand forecasting ii. Answer questions

c. Execute:

D7.1: Dell uses forecasting to communicate with their suppliers and match production requirements. The forecasting determines the quantity of each component needed to produce a Dell PC/laptop. D7.2: Dell can communicate with their suppliers about the promotions that they are having. As they are expecting a fluctuation or increase in demand, they would start producing parts based on what they think is going to be demanded. D7.9: The MSE or Mean Squared Error, estimates variance in forecasting error. MAD or Mean absolute deviation, estimates the standard deviation of the forecast error and gives us the absolute error allowing us to estimate the expected value. MAD is used to provide balanced estimation of the mean value. MAPE or Mean Absolute Percentage Error gives us the overall percentage of the absolute error in terms of overall quantity that is forecasted. The manager uses this information to forecast accuracy and minimize error. D7.10: The bias and TS are used to estimate if the forecast consistently over or under-forecasts. The bias should be around a value of 0 and the tracking signal should follow an interval of [-6, 6]. If the tracking signal is outside that interval at any period, then the forecast is biased.

d. Check I have checked my work and it makes sense.

e. Learn and Generalize It is crucial that a forecast is not biased therefore MSE, MAD, MAPE and TS allow us to minimize error and prevent bias in the forecast.

3. Tahoe Salt a. Define:

i. Forecast demand using the static method, moving average, and simple exponential smoothing methods.

b. Plan: i. Use previous homework’s static method solution and then include error

analysis into it ii. Follow textbook to match solutions

c. Execute: Copied Static Method Forecasting over from HW 2 but now including error analysis: Quarterly Demand for Tahoe Salt

Deseasonalized Demand for Tahoe Salt

Regression Analysis

Level, L, is obtained as the intercept coefficient: 18438.9881 ~ 18439, and Trend T: 523.8 ~ 524. The equation for deseasonalized demand = 18,439 + 524t Deseasonalized Demand using equation 18439+524t and including estimated seasonal factor.

Next we obtain the seasonal factor for a given period by averaging the seasonal factors. The Tahoe Salt problem has 12 periods and a periodicity of 4 implies 3 season cycles. We take the Seasonal Factor for every 4 periods and add them up and divide by three. P = period (P1+P5+P9)/3 (P2+P6+P10)/3 (P3+P7+P11)/3 (P4+P8+P12)/3

Now we can forecast the demand with the formula of (L +p*T)*SeasonalFactor F13 = (L+13T)S13 = (18,963 + 13 X 524)0.47 = 11,868 F14 = (L + 14T)S14 = (18,439 + 14 X 524 )0.68 = 17,527 F15 = (L + 15T)S1s = (18,439 + 15 X 524)1.17 = 30,770 F16 = (L + 16T)S16 = (18,439 + 16 X 524)1.67 = 44,794 Now we include the error analysis

Error = Forecast - Demand

MSE =

Absolute Error = Absolute value of error =

MAD = =

Percent Error =

MAPE = =

Bias = =

TS = =

Adaptive Forecasting Moving Average: Using these formulas in excel we are able to calculate the Level, Forecast, Error, Absolute Error, MSE, MAD, %Error, MAPE, and TS.

Adaptive Forecasting Simple Exponential Smoothing: Using these formulas in excel:

Initial level: average of all the demand = 22,083

d. Check I have checked my work and compared it to the solution in the textbook

e. Learn and Generalize The different ways to forecast demand allow us to see which one has larger error. Error analysis allows us to see whether or not the quality of the forecast is good. I believe with more practice I will be able to do this with ease.

4. Demand Forecasting for ABC a. Define

i. Consider monthly demand for the ABC Corporation, as shown in Table 7-3. Forecast the monthly demand for Year 6 using the static method for forecasting. Evaluate the bias, TS, MAD, MAPE, and MSE. Evaluate the quality of the forecast.

b. Plan i. Forecast the demand ii. Evaluate those things up there

c. Execute

This graph allows us to determine periodicity and seasonality

Since we are doing an analysis on a yearly basis, periodicity (p) is 12. We must find the time at which deseasonalized demand starts.

D12 D + D + D + D +D +D +D +D +D +D +D +D1 2 3 4 5 6 7 8 9 10 11 12 = ′6.5

D12

D + D + D +D +D +D +D +D +D +D +D +D2 3 4 5 6 7 8 9 10 11 12 13 = ′7.5

D2(12) D +2(D + D + D +…+D )+D1 2 3 4 12 13 = 24

D +D′6.5 ′7.5 = ′7

Now we can use the excel formula ​=(D2+D14+2*SUM(D3:D13))/24 ​and copy it to E55 to calculate deseasonalized demand.

Now we perform a regression analysis :

Our intercept (Level = L) is 5997.26 and our regression value is 70.25. This makes our Deseasonalized Demand Eq: 5997.26 + 70.25t RDD = Regressed Deseasonalized Demand

Seasonal Factor is then calculated by dividing the Demand by the RDD.

We then calculate the average seasonal factor for 12 periods. We take the seasonal factor for each of the 12 months and average them.

Now we forecast the demand by taking the equation Deseasonalized Demand = 5997.26 + 70.25t, and multiplying it by the average seasonal factor for each period. F61 = (5997.26 + 70.25 x 61)0.43 = 4387 F62 = (5997.26 + 70.25 x 62 )0.47 = 4913 F63 = (5997.26 + 70.25 x 63)0.46 = 4822 F64 = (5997.26 + 70.25 x 64)0.40 = 4178 And so on...

Now we calculate the error for the forecast: Error = Forecast - Demand

Means Squared Error is calculated by the formula:

Absolute Error is the absolute value of the errors.

Mean Absolute Deviation (MAD): calculated by

Percent Error is calculated as the (Abs Error / Demand) * 100

Means Absolute Percentage Error is calculated by

Bias is calculated by

Tracking Signal is calculated by

Now we can evaluate the Forecast We can see from the tracking signals that 3 of the periods are outside the interval of [-6,6], meaning an underestimation in demand. The MAPE provides evidence that the forecast error is acceptable due to relatively low percentages which displays the minimal differences between the forecasted and actual demand data. The amount of errors does not seem to be high which means that the forecast was good.

d. Check I have checked my calculations and checked my formulas with that of the textbooks to make sure i am integrating them correctly into excel.

e. Learn and Generalize This was a tedious task as it required a lot of number punching, however I have further strengthened my skill in demand forecasting as I am now able to perform an error analysis.