Managerial Accounting

Managerial Accounting

Use the data in the Excel file provided to forecast the demand of a particular product using various methods and time periods as noted below. NOTE: YOU MUST SHOW ALL YOUR WORK IN THE EXCEL FILE. MAKE SURE YOUR ANSWERS APPEAR IN THE WORKSHEET THAT IS NAMED “TEMPLATE.” YOU CAN CREATE NEW WORKSHEETS OR SHOW ADDITIONAL WORK IN THE WORKSHEET PROVIDED.

a. Prepare a time-series plot of the data. Does the data appear to have a trend or seasonality?

b. Use the naïve method to forecast the demand for the last three months of 2016.

c. Use the moving average method with 4 periods, i.e., MA(4), to forecast the monthly demand for January 2016 through December 2016.

d. Use the exponential smoothing method with a=0.4 to forecast the monthly demand for January 2016 through December 2016. Use the actual demand observed in January 2015 as your initial forecast for January 2015 (That is, start the model in January 2015.

e. Fit a trend line to the data provided for January 2015 to December 2015. Using the equation of the trend line you found, forecast the monthly demand for January 2016 through December 2016.

f. Compute the MAD for each method, that is, the Naïve, MA(4), ES(0.4), and the trend line during January 2016 to September 2016.

g. Choose one of the time-series methods you analyzed thus far which will provide you the most reliable estimate and why? What are the demand forecasts for the last three months of 2016 if this method is used?