Question

1. A manufacturer is developing a facility plan to provide production capacity for its factory. The amount of capacity required in the future depends on the number of products demanded by its customers. The data below reflect past sales of its products:

Year	Annual Sales (number of products)	Year	Annual Sales (number of products)
1	490	5	461
2	487	6	475
3	492	7	472
4	478	8	458

a)      Use simple linear regression to forecast annual demand for the products for each of the next three (3) years, by using the tabular method to:

                                            i.            derive the values for the intercept and slope                               

                                          ii.            derive the linear equation

                                        iii.            develop a forecast for the firm’s annual sales for each of the next three years                                                                                                       

                    i.            Explain the difference between qualitative and quantitative approaches to forecasting.

                  ii.            Describe three (3) qualitative methods used in forecasting.                    

                iii.            Given the following data of demand for shopping carts at a leading supermarket. Prepare a forecast for period 6 using each of the following approaches:

Period	1	2	3	4	5
Demand	60	65	55	58	64

1. A three-period moving average.                                                  
2. A weighted average using weights of .50 (most recent), .20 and .30.
3. Exponential smoothing with a smoothing constant of .40.         

                iv.            The manager of a large cement production factory in Road Town, Tortola has to choose between two alternative forecasting techniques. His production staff used both techniques in order to prepare forecasts for a six-month period. Using MAD as a criterion, which technique has the better performance record?                    

		FORECAST
MONTH	DEMAND	TECHNIQUE 1	TECHNIQUE 2
1	492	488	495
2	470	484	482
3	485	480	478
4	493	490	488
5	498	497	492
6	492	493	493

1. West Indies Batting Inc is again on the rise due to the increasing sale of cricket bats throughout the region. BATS R’ US, a cricket bat making company located in Charlestown, Nevis has been at the forefront of this increasing sales for cricket bats. The monthly demand in sales at BATS R’ US are provided in the table below.

                    i.            Compute a three-period moving average and a four-period moving average for weeks 5, 6, and 7.                                                                                          

                  ii.            Compute the MAD for both forecasting methods.                                 

                iii.            Which model is more accurate?                                                               

                iv.            Forecast week 8 with the more accurate method.                                   

Month	Sale of bats
1	119
2	147
3	189
4	217
5	133
6	119
7	147

1. Boi’s Car Rentals has been doing great business since acquiring a major North American car rental label. In preparation for the World Peace Summit slated for next month they need to project their demand for May. The business’ historic data is listed below:

           

Months	Nov.	Dec.	Jan.	Feb.	Mar.	April
Quantity	37	36	40	42	47	43

        i.            Based on the above data calculate the demand for May using a five month moving average

      ii.            Calculate the forecast for May based on a THREE month weighted moving average applied to the following past demand data and using the weights: 4, 3, 2 (largest weight is for most recent data)?                                                                                                

    iii.            Using the exponential forecasting technique with a smoothing constant value of 0.2 and an initial value of 40, forecast the quantity of cars that will be demanded for May.

Answer 1

Regression function of excel can be used to find the values of intercept and slope.

Regression settings: