case study for forecast

profileC97
ChinesePharmaceuticals_ExcelFileforStudents.xlsx
Home>Business & Finance homework help>Financial markets homework help>case study for forecast

Student+Simple ES

HBP Product No.: ST5WS
UST005/WSS/1207
Simple Exponential Smoothing Model
MM-YY Period Sales Forecast Error Absolute Alpha = 0.4
t St Ft St - Ft Error
Jul-09 1 3,924 Formula
Aug-09 2 2,619 3,924 -1,304 1,304 F2 = S1
Sep-09 3 6,920 3,402 Ft+1 = Alpha*St + (1 - Alpha)*Ft
Oct-09 4 5,676 Percentage Error = (St - Ft) / St * 100%
Nov-09 5 8,348
Dec-09 6 6,044
Jan-10 7 6,877
Feb-10 8 6,535
Mar-10 9 6,395
Apr-10 10 6,684
May-10 11 5,414
Jun-10 12 3,180
Jul-10 13 4,350
Aug-10 14 3,175
Sep-10 15 6,935
Oct-10 16 6,356
Nov-10 17 8,919
Dec-10 18 7,146
Jan-11 19 7,763
Feb-11 20 7,397
Mar-11 21 7,286
Apr-11 22 7,498
May-11 23 6,386
Jun-11 24 4,209
Jul-11 25 4,825
Aug-11 26 3,764
Sep-11 27 7,066
Oct-11 28 7,015
Nov-11 29 9,535
Dec-11 30 8,278
Jan-12 31 8,773
Feb-12 32 8,393
Mar-12 33 8,288
Apr-12 34 8,432
May-12 35 7,455
Jun-12 36 5,346
MAD =
This spreadsheet is created by Professor Ronald Lau to accompany the teaching note, Reference No.: UST005/TN/1808 (HBP Product No.: ST5T), of the case : Chinese Pharmaceuticals (HK) Limited: Effective Forecasting for Optimal Inventory Management, Reference No.: UST005/1808 (HBP Product No.: ST5). © 2012 by The Hong Kong University of Science and Technology. This publication may not be digitized, photocopied or otherwise reproduced, posted, or transmitted without the permission of the Hong Kong University of Science and Technology.

You should exclude the data of the first two cycles (24months) when calculating the average error (Mean Absolute Deviation), as it takes time for exponential forecasting model to establish before providing an accurate demand forecast

Student+Adaptive ES

HBP Product No.: ST5WS
UST005/WSS/1207
Adaptive Smoothing Model
MM-YY Period Sales Forecast Abolute % Error Absolute %
t St Ft Error (in dec.) Error (in dec.) Formula
Jul-09 1 3,924 F2 = S1
Aug-09 2 2,619 3,924 1,304 0.498 0.498 Ft+1 = Alpha t+1*St + (1-Alpha t+1)*Ft
Sep-09 3 6,920 3,274 Percent Error: PEt = (St - Ft) / St * 100%
Oct-09 4 5,676
Nov-09 5 8,348 Alpha t+1 = 0.00001, if | PE t | = 0
Dec-09 6 6,044 Alpha t+1 = 0.99999, if | PE t | > 1
Jan-10 7 6,877 Alpha t+1 = | PE t | otherwises
Feb-10 8 6,535
Mar-10 9 6,395
Apr-10 10 6,684
May-10 11 5,414
Jun-10 12 3,180
Jul-10 13 4,350
Aug-10 14 3,175
Sep-10 15 6,935
Oct-10 16 6,356
Nov-10 17 8,919
Dec-10 18 7,146
Jan-11 19 7,763
Feb-11 20 7,397
Mar-11 21 7,286
Apr-11 22 7,498
May-11 23 6,386
Jun-11 24 4,209
Jul-11 25 4,825
Aug-11 26 3,764
Sep-11 27 7,066
Oct-11 28 7,015
Nov-11 29 9,535
Dec-11 30 8,278
Jan-12 31 8,773
Feb-12 32 8,393
Mar-12 33 8,288
Apr-12 34 8,432
May-12 35 7,455
Jun-12 36 5,346
MAD =
(MAD for last 12 months only)

You should exclude the data of the first two cycles (24months) when calculating the average error, as it takes time for exponential forecasting model to establish before providing an accurate demand forecast

Student+Full ES

HBP Product No.: ST5WS
UST005/WSS/1207
Exponential Smoothing with Trend and Seasonality Model
MM-YY Period Sales Level Trend Seasonality Forecast Absolute % Error Absolute %
t St Lt Tt It Ft Error (in dec.) Error (in dec.)
Jul-09 1 3,924 5718 0 0.686 3,924 Alpha = 0.1 Beta = 0.2 Gamma = 0.15
Aug-09 2 2,619 5718 0 0.458 2,619
Sep-09 3 6,920 5718 0 1.210 6,920 Initialization (1<= t <= 12) Note: This procedure helps determine the initial values of Seasonality for the first year
Oct-09 4 5,676 5718 0 0.993 5,676 Set Ft = St Tt = 0 Lt = average of first year sales
Nov-09 5 8,348 5718 0 1.460 8,348 It = St / average of first year sales
Dec-09 6 6,044 5718 0 1.057 6,044
Jan-10 7 6,877 5718 0 1.203 6,877 Formula (for t>12)
Feb-10 8 6,535 5718 0 1.143 6,535
Mar-10 9 6,395 5718 0 1.118 6,395 Lt = Alpha (St / It-c) + (1 - Alpha) (L t-1 + Tt-1)
Apr-10 10 6,684 5718 0 1.169 6,684 Tt = Beta (Lt - Lt-1) + (1 - Beta) Tt-1
May-10 11 5,414 5718 0 0.947 5,414 It = Gamma (St / Lt) + (1-Gamma) It-c
Jun-10 12 3,180 5718 0 0.556 3,180
Jul-10 13 4,350 5,780 12 0.696 3,924 F t+m = ( Lt + (Tt * m) ) * It-c+m (for m-step-ahead forecast)
Aug-10 14 3,175 5,906 35 0.470 2,653
Sep-10 15 6,935 5,921 31 1.204 7,191
Oct-10 16 6,356 5,997 40 1.003 5,908
Nov-10 17 8,919 6,044 41 1.462 8,813 For F13 to F36:
Dec-10 18 7,146 6,153 55 1.073 6,433 Ft +1= (Lt+Tt)*It+1-c
Jan-11 19 7,763 6,233 60 1.209 7,466 e.g. F17= (L16+T16)*I5 (seasonal cycle c = 12)
Feb-11 20 7,397 6,310 63 1.147 7,191
Mar-11 21 7,286 6,388 66 1.122 7,128
Apr-11 22 7,498 6,450 66 1.168 7,544
May-11 23 6,386 6,539 70 0.951 6,169
Jun-11 24 4,209 6,705 89 0.567 3,675
Jul-11 25 4,825
Aug-11 26 3,764
Sep-11 27 7,066
Oct-11 28 7,015
Nov-11 29 9,535
Dec-11 30 8,278
Jan-12 31 8,773
Feb-12 32 8,393
Mar-12 33 8,288
Apr-12 34 8,432
May-12 35 7,455
Jun-12 36 5,346
Average Error =

You should exclude the data of the first two cycles (24months) when calculating the average error, as it takes time for exponential forecasting model to establish before providing an accurate demand forecast

Student+Inv. Development

HBP Product No.: ST5WS
UST005/WSS/1207
Replenishment Template of Notoginseng Capsules (Lead time = 2 months)
Warehouse capacity of inventory 25,000 Safety stock =
MM-YY Beginning Inventory (Book Record) Stock to be received in the month Stock in Transit (to be received in next month) Inventory Position Inventory required covering lead time & review period Order Quantity Actual Demand Forecast Demand Buffer (gift for promotion) Forecast & Buffer
Definition (=Beg. inv. of last month + stock received - actual demand) (= Order quantity 2 months ago) (= Order quantity 1 month ago) (= Beginning inventory + Stock receipt + Stock in transit) (= Sum of forecast & buffer for the current month and lead time) (= Forecast demand over vulnerable period + Safety stock - Inventory position) Given Results from Forecast Model (= forecast*0.2) (= forecast + buffer)
Mar-11 26,662 0 0 0 Legend 7,286 4,813
Apr-11 0 0 0 Order 7,498 4,244
May-11 1000 No need to order 6,386 3,727
Jun-11 4,209 3,275
Jul-11 4,825 3,664
Aug-11 3,764 3,569
Sep-11 7,066 7,833
Oct-11 7,015 8,377
Nov-11 9,535 11,224
Dec-11 8,278 8,315
Jan-12 8,773 8,328
Feb-12 8,393 7,616
Mar-12 8,288 7,375
Apr-12 8,432 7,482
May-12 7,455 6,567
Jun-12 5,346 5,033
Jul-12
Aug-12
Sep-12
Oct-12
Nov-12
Dec-12
Jan-13
Feb-13
Mar-13
Apr-13
May-13
Jun-13