Analysis / Forecasting Data
Order Description
Scenario: You are still a consultant for the Excellent Consulting Group. You have completed the first assignment, developing and testing a forecasting method based on linear regression (Case 3). However, your consulting manager at ECG wants to go the next step and investigate another forecasting method. It is important to do a thorough job for the client, and you have the expertise to analyze different forecasting methods. You have decided to look at the sales data for client’s lottery app as a single data set and use a time series analysis, namely SES, single exponential smoothing.
Case Assignment
Using Excel, use the forecasted sales (select tab on bottom) from Case 3 to compute the MAPE, by doing the following:
1. Find the MAPE for the first 12 months (assume the forecast for Month 1 – or January – is equal to January’s actual sales). To find the MAPE, you will need to compare actual sales for each month, or Y(t), to forecasted sales, or F(t).
2. Next, forecast the sales for the next three months (Feb – Apr), and compute the MAPE for this 3-month period. Compare this 3-month MAPE to the MAPE you calculated for the SES analysis (Case 4).
Then write a report to your boss that briefly describes the results that you obtained. Make a final recommendation on which method to use, SES or Linear Regression.
Paper should include two files: (1) An Excel file; and (2) A Word document.
Data: See attachment with data that I previously have and generated from your analyses in Case 3.
Assignment Expectations
Analysis
• Accurate and complete SES analysis in Excel.
Written Report: (Use Heading for paragraphs)
• Length requirements = 4 pages minimum (not including Cover and Reference pages)
• Provide a brief introduction/ background of the problem.
• Complete and accurate Excel analysis.
• Written analysis that supports Excel analysis, and provides thorough discussion of assumptions, rationale, and logic used.
• Complete, meaningful, and accurate recommendation(s).
y= 519.4378 + 1.2485x
Month Hits Sales Hits Sales
Jan 1200 545 1200 420
Feb 820 301 820 545
Mar 1151 510 1151 301
Apr 1050 485 1050 510
May 1180 525 1180 485
Jun 1047 460 1047 525
Jul 1102 500 1102 460
Aug 1054 402 1054 500
Sep 1254 584 1254 402
Oct 1071 422 1071 584
Nov 1120 514 1120 422
Dec 1287 441 1287 514
Jan 1164 1164 441
Feb 1159 1159 499
Mar 1298 1298 497
April 563
y= (-57.5497) + 0.478374x
Forecast Forecast Sales Actual Sales Forecasting Error
Jan Sales= -57.5497 + (0.478374*1287) 558.117638 402 -38.84%
Feb Sales= -57.5497 + (0.478374*1164) 499.277636 380 -31.39%
Mar Sales = -57.5497+(0.478374*1159) 496.885466 379 -31.10%
Apr Sales = -57.5497+(0.478374*1298) 563.379752 405 -39.11%
-140.43%
Mean Error -35.11%
Mean Absolute Error 35.11%
Forecasting Error=(Actual-Forecast/Actual)*100 35.11%