Forecasting Data
Order Description
Scenario: You are consultant for the Excellent Consulting Group. Your client wants to be able to forecast sales on a monthly basis and believes that there is a valid relationship between sales and the number of hits on their website during the previous month. To test this theory, the client has collected data on sales of one of its products, a lottery app for smart phones and hits on its website.
Case Assignment
Using Excel and linear regression analyze the data and determine how to do forecasting using website hits.
Then forecast the next three months using the monthly hits data. Compare the forecast to the actual sales and determine the forecasting error. Actual sales data (Jan 402, Feb 380, Mar 379 and Apr 405)
Then write a report to your boss and the client that briefly describes the results that you obtained. Make a recommendation on how this might be used for forecasting purposes.
Paper should include two files: (1) An Excel file; and (2) A Word document.
Data: Attached the Word file Case 3 Data.docx with the data. Use this data in Excel for your analysis.
Assignment Expectations
Analysis
• Accurate and complete Linear Regression 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).
* Note: See attachments with Case 3- Data for assignment and complete instructions. Paper should include two files: (1) An Excel file; and (2) A Word document.
Case 3 Data
Following are the data for website hits and app sales (number of the Lottery apps.)
Month Hits Sales
Jan 1200 420
Feb 820 545
Mar 1151 301
Apr 1050 510
May 1180 485
Jun 1047 525
Jul 1102 460
Aug 1054 500
Sep 1254 402
Oct 1071 584
Nov 1120 422
Dec 1287 514
Jan 1164 441
Feb 1159 —-
Mar 1298 —-
April —- —-
IMPORTANT: Be sure to shift the monthly sales up by one month because the theory is that the hits predict the next month sales (e.g., the 1,200 hits in January are paired with February’s sales of 545). Therefore, your data will look like this:
Month Hits Sales
Jan 1200 545
Feb 820 301
Mar 1151 510
Apr 1050 485
May 1180 525
Jun 1047 460
Jul 1102 500
Aug 1054 402
Sep 1254 584
Oct 1071 422
Nov 1120 514
Dec 1287 441
Use the monthly hits for Jan through Mar to predict the sales for Feb through Apr.
When you have done so, ask your Instructor to provide the data for the actual sales for Jan through Apr.
Module 3 – Case
Linear Regression Forecasting and Decision Trees
Assignment Overview
Scenario: You are consultant for the Excellent Consulting Group. Your client wants to be able to forecast sales on a monthly basis and believes that there is a valid relationship between sales and the number of hits on their website during the previous month. To test this theory, the client has collected data on sales of one of its products, a lottery app for smart phones and hits on its website.
Case Assignment
Using Excel and linear regression analyze the data and determine how to do forecasting using website hits.
Then forecast the next three months using the monthly hits data. Compare the forecast to the actual sales and determine the forecasting error. Actual sales data (Jan 402, Feb 380, Mar 379 and Apr 405)
Then write a report to your boss and the client that briefly describes the results that you obtained. Make a recommendation on how this might be used for forecasting purposes.
Data: Download the Word file Case 3 Data.docx with the data. Use this data in Excel for your analysis.
Assignment Expectations
Analysis
• Accurate and complete Linear Regression 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).