Abco Electric is a GTA-based company that has been in business for over 30 years. They manufacture a variety of products in the Electrical Supply Industry in Canada and the U.S. Many of their products manufactured are sold to cities and towns as well as to Provincial/ State Electrical Companies. About half of their sales consist of spare parts for their products that are in service. Annual sales are approaching twenty-five million dollars per year.

Abco is having trouble with its forecasting. One of their more popular (and long lead-time) items is the energy storage system. With each system sold, they are ensured of sales of spare parts for several years to come. If the customer places the order with a different supplier, then those future sales are lost. This item requires about ten days to manufacture parts, assemble and test. Each energy storage system brings in approximately $2500 in revenue.

The company generally runs a make-to-order system, producing the products as they are ordered. Lately, they have had trouble meeting order dates, resulting in the cancellation of orders. Often, they run short of the required materials to make these products, because they are trying to minimize inventory costs.

At the last Executive Meeting, there was a lot of discussion about making this product a stock item and manufacturing them to a forecast. The Supply Manager was quick to point out the challenges of this type of system.

You have been hired as a Business Graduate from College. One of your first tasks is to suggest a method of forecasting for this item. There does not seem to be any seasonal influence or trend in this market. You have been provided with the historical demand from 2021 and you will use these numbers to do some experimenting with Simple Moving Averages, Weighted Moving Averages and Exponential Smoothing.

2021 Sales
Energy Storage System
	Units Sold
Jan	225
Feb	276
Mar	216
Apr	312
May	340
Jun	298
Jul	167
Aug	360
Sep	311
Oct	348
Nov	289
Dec	310

Submit ONE EXCEL Document containing:

Your name, section, and student number.

1. Table One:

1. Calculate a 3-month Simple moving average, for Apr-Dec to show what your forecasts would have been for 2021. Calculate the Mean Absolute Deviation, the mean squared error, and the mean absolute percent error for this set of data.
2. Calculate a 2-month Simple moving average for March-Dec of 2021. Calculate the Mean Absolute Deviation, mean squared error and mean absolute percent error.
3. Create a text box beside the table with a sentence indicating which of the two forecasts might be more accurate and why.

2. Table Two:

A) Calculate a 3-month weighted moving average with a weighting of .5, .3, .2, for Apr-Dec of 2021. Calculate the Mean Absolute Deviation, the mean squared error, and the mean absolute percent error.

B) Calculate a second 3-month Weighted moving average using weights of .8, .15, .05 for Apr-Dec of 2021. Calculate the Mean Absolute Deviation, mean squared error, and mean absolute percent error for this method.

C) Create a text box beside the table indicating which of the two forecasts might be more accurate and why.

3. Table Three:

1. Use Exponential Smoothing and begin a forecast in April. In order to calculate the April forecast, use a prior (March) forecast of 200 units. Use .3 for Alpha. Forecast April through December using this method. Calculate the Mean Absolute Deviation, mean squared error, and mean absolute percent error.
2. Calculate another exponential smoothing forecast using an alpha of .15 beginning in April and using a prior(March) forecast of 200 units. Calculate the Mean absolute deviation, mean squared error and mean absolute percent error.
3. Include a text box to comment on which if any of the two forecasts were more accurate and why.

4. Graph your forecasts. Plot the actual demand, along with the three additional forecasts- one with the simple moving average figures ( 3 period SMA) and one with the weighted moving average figures, ( .5,.3,.2) and one with the exponential smoothing figures (Alpha .3) Use different colors for each line. You will submit one graph, with a total of FOUR lines. Label each line, the X-axis, y-axis, and include a descriptive title.

5. Insert a text box with your brief comments on which of all your forecast methods you recommend and why. Also, include one paragraph with your thoughts on how this change of method from make-to-order to make-to-stock for this item may impact the operations and finances of the business.

The report must be in ONE EXCEL document, in the order given above. Round all forecasts and your Accuracy measures to one decimal place. Excel formulas must be used for all calculations in order for this assignment to receive a grade. Format your tables to provide a professional appearance Include a title for each table and labels for each column.