Question

Please complete the analysis for "Bill's Brew Threw" case on page 470. (edited question: spreadsheet modeling for business decisions by john f. kros. Edition: 5)

448 CHAPTER7 Introduction to Forecasting Forecasting Using Exponential Smoothing for Bill's Brew Threw Background Bill Fredericks runs a minimart that sells basic convenience goods like soda, bee snacks. Bill's business is good and relatively stable over time (i.e., no large trend downward over time). However, he has noticed changes in the amount of soda h ing. Bill wishes he had a forecasting method that took into account past data but chose which data he thought the most important (i.e, apply a weighting scheme) er, ice, chips,and all owed him to Bill's Demand Numbers gether data on his business's soda sales each month over the past 12 months(oed. ods).The following table illustrates Bill's demand (number of sodas sold) over the last 12 mon (periods) of business. MONTH (PERIOD) SODA SALES (UNITS) 3,591 3,703 3,952 3,882 ,677 3,659 MONTH (PERIOD) SODA SALES (UNITS) 3,935 3,615 3,879 3,653 3,768 3,754 10 12 Bill wishes to know, on the basis of past demand data, if there is a way to predict future demand for monthly soda sales. Bill has heard of a forecasting technique called exponential smoothing. Although the technique is exactly what Bill wants, a method that incorporates all past data ard allows for weighting, he does not know what weight to use or how to determine if one weight- ing scheme is better than another. Exponential Smoothing Models Input the demand data into a spreadsheet and using Excel's Exponential Smoothing option in the Data Analysis package answer the following questions Start with an alpha value of 0.1 and forecast Bill's sales. In turn, find MAD for the forecast Now increment the alpha in 0.1 increments up to an alpha of 1.0, noting the value ofthe MAD at every increment (i.e, create nine more forecasts using different alpha values How does the forecast change as alpha is incremented upward? Why d change in this manner when alpha is changed? What value of alpha outputs the lowest MAD value? What is the forecast value for Bill's soda sales for the alpha value (i.e, the alpha value that produces the lowest MAD value)? 1. oes the fore 2. 3. 13th month (period) at the best

Answer 1