BBA Core Assessment Analytic Critical Thinking Skills MGMT

BBA Core Assessment Analytic & Critical Thinking Skills MGMT 3213 Part 1 Forecasting (30 pts) All analysis and calculations and report must be done in a single (ONE) Excel file . Put your name at the top of the worksheet Make Excel do all of the calculations. . lude report/answers below the forecasting calculations. o Make sure answers are clear, complete and easy to find. o Your report must include: a. Presentation of forecasts b. Explanation of why you chose each of the methods Annual sales of product from XYZ corporation have been recorded as follows: Year I. |Sales (000 units) 1- Sales 360 400 380 420 450 460 475 Year units 220 245 280 275 300 310 350 Management is interested in implementing a new forecasting system and investigating the following forecasting methods as possibilities: Simple moving average, Weighted moving average, Simple Exponential Smoothing. Regression Choose two of these methods and calculate forecasts for Present your forecast results If you use Exponential Smoothing, begin with the assumption the Forecast for Year 12 was 435 (choose your own alpha) o . Explain why each of your chosen methods is appropriate for the data and time frame given

Solution

Simple moving average can be done using 3,4 or 5 years of past data and taking an average of them to arrive at the forecast.

Now, taking 5 year moving average for our calculation:

Forcast for year 6 = average of preceding 5 years sales = (220+245+280+275+300)/5 = 264

Similarly forecast for year 9 = average of preceding 5 years = (360+350+310+300+275)/5 = 319

SImilarly forecast for year 15 = average of sales of year 14,13,12,11,10 = (475+460+450+420+380)/5 = 437

HEnce, using simple moving average the forcast for year 15 = 437(000) units

Weighted moving average:

here we assign weights to different years depending on their relevance and importance:

Here for our calculation, we will use past 3 years for forecast:

the preceding year is 50% weightage, the year befor that is 30% and the year before that is 20%.

Now forecast for year 6 = 50%*year 5 + 30%*year 4 + 20%*year 3 = 0.5*300 + 0.3*275 + 0.2*280 = 288.5

forecast for year 9 = 50%*year 8 + 30%*year 7 + 20%*year 6 = 0.5*360 + 0.3*350 + 0.2*310 = 347

Similarly forecast for year 15 = 50%*year 14 + 30%*year 13 + 20%*year 12 = 0.5*475 + 0.3*460 + 0.2*450 = 465.5

Hence, using weighted average the forcast for year 15 = 465.5(000) units