PROJECT: TOFFEE INC.: DEMAND PLANNING FOR CHOCOLATE BARS
YING MA
1
According to the project of TOFFEE INC., the main tasks include a
comprehensive forecasting and inventory management plan with a view to minimize
the cost of managing the supply chain. Specifically, the demand (production) of
chocolate bar should be forecasted according to the old data from 2006 to 2010, and
according to the quantitative relation between chocolate bar and four ingredients
(dark chocolate, cocoa butter, cocoa powder, dry fruits and nuts), we can use EXCEL
to calculate each demand, economic order quantity (EOQ), total cost (TC), reorder
point (ROP), number of order and cycle time (CT) etc. (Exhibit 1)
Firstly, type all data (demand from 2006 to 2010 and each cost da ta) given
from the project to EXCEL sheet 1(named DATA), and then copy the demand from
2006 to 2010 into EXCEL sheet 2(named 2011 Forecast Demand) in one column, and
type from 1 to 75 in the second column as number. Use data from 2006 to 2010 to
build scatter graph (scatter with smooth lines and markers), select the formula and
get the liner formula: Y = 8.747X +895.58. So we can get the initial forecast
demand number of 2011. And because the demand followed a seasonal variation, so
I need calculate the seasonality index. The method is use average of each month
demand divided by average of all month demand. (e.g. 2011-01:
=(C2+C14+C26+C38+C50)/5/AVERAGE($C$2:$C$61)). Using the initial forecast
number times the seasonality index to get the final forecast number and rebuilt the
graph by the new forecast values. (Exhibit 2) And because of the stock should be
maintained to keep meeting the expected service levels of 95%, so I calculate the
forecast daily demand and use the formula: =(B2/365)*B6+1.64*SQRT('Chocolate
Bar'!B6*STDEV('2011 Forecast Demand'!F62:F76)^2+0) to calculate the reorder
point.
