Texas A&M Commerce University
College of Business – Department of Marketing & Business Analytics
Assignment for Course: BUSA 511 Analytics for Managers
Submitted to: (Professor’s name)
Submitted by: (Student’s name)
(Student’s ID number)
(Student’s email)
Date of Submission:
Title of Assignment:Individual Project on Forecasting
CERTIFICATION OF AUTHORSHIP: I certify that I am the author of this paper and that any assistance I received in its preparation is fully acknowledge and disclosed in the paper. I have also cited any sources from which I used data, ideas of words, whether quoted directly or paraphrased. I also certify that this paper was prepared by me specifically for this course.
Student Signature: ___________________________
*******************************************
Part II – Cutting Edge Company Case Study
Individual Forecasting Project Instructions& Report
Instructions
This is an individual assignment and therefore it must be completed by the individual student without outside assistance of any type.Enter your answers to all questions into this document. Use only the models provided to you in this assignment – no other statistical programs or applications are permitted.Follow the instructions below in order to complete the assignment.
- Part 1.First,download and read the Microsoft Word case write-up for the “Part II Cutting Edge Case Study for Ind Forecasting Project”. Enter your answer to Part 1 below and refer to the post consolidation period only.
- Part 2. Second, download the Excel file “Part IICutting Edge Data_ES_LR Models Student V1.xlsx” and enter your answers for Part 2 below and refer to the 18months of datacovering the consolidated center operations. Note: this is an assessment and interpretation of usingtime series data, casual analysis and linear regression–the use of the models with resulting calculations is to be accomplished.
- Part 3.Finally, provide a summary of your findings by answering the questions.
Submittals
- Submit your answers to this assignment (parts 1, 2, 3) using this Microsoft Word document to the assignment drop box before the posted deadline. Be sure to complete the above first page cover sheet. Enter your answers in the pages below to include all of your answers and results of your interpretations of model calculations for the assignment questions. Your answers must be entered directly into this Word document below each question. Keep your answers concise and use the space guidelines provided in each part.
- Submit your Excel spreadsheet(s) with calculations/ visualizations to the assignment dropbox before the posted deadline. Your Excel model calculations will be used to substantiate your answers to the assignment questions herein.
Grading
A total of 100 percentage points is possible for this assignment. This includes the point values which are assigned to each question (point values are noted next to each question below).Ten (10) points may be deducted based on not following the prescribed assignment format and/or distracting punctuation and grammarerrors. The percentage points earned on this assignment will be multiplied by 20 to obtain the final assignment grade. Use APA 7 format for any answers that the student determines to require outside references.In your written explanations, do not use underlines, highlights or italics for any part of your descriptive answers.
Part 1 (10 points)
In answering questions for part 1, reference the Cutting Edge case study file: “Part II Cutting Edge Case Study for Ind Forecasting Project”. Your answer should not be longer than 150 words.
Question 1 (5 points): Define a problem statement which reflects the challenges facing Mark as he addresses the new issues associated with monthly forecasting.
Answer:
Part 2 (50 points)
In answering Part 2 questions, download and reference“Part IICutting Edge Data_ES_LR Models Student V1.xlsx” which contains the historical data that was used in preparing the forecast results for your assessment. Note, in answering these questions you need to produce scatter plots, calculate the correlation coefficients and use the twoExcel forecasting model results. Your answers are to be based on the assessment and interpretations of 18months of datafound in the Data & Correlationtab.
Question 2a (10 points): This question has 5 sub-parts shown below in bold. Access the data in the Data and Correlation tab and produce 3scatter plots using the Actual Call Volume as the dependent variable and Engineering Head Count, Administration (G&A) Head Count and Manufacturing Head Count.Your scatter plots should be visible in the provided Excel spread sheet. Do the scatter plots show linear relationships?
Answer (yes/no):_________________
Now, using the Excel function CORREL in the provided spreadsheet Data & Correlation tab, calculate both the Correlation Coefficient and the Coefficient of Determination for the remaining three (3) independent variables to the dependent variable. Provide the resulting values and describe the strength, quality and relationship of each to the dependent variable.
Engineering Head Count values and descriptiverelationship answer:
Administrative (G&A) Head Count values and descriptive relationship answer:
Manufacturing Head Count values and descriptive relationship answer:
Next, take note of the first Correlation Coefficient (Actual Call Volume and Total Co. Employee Head Count) that is provided for you to use in comparison to the other calculated coefficients.
Which Independent variable impacts the dependent variable the most?
Answer: _________________
Question 2b (10 points): Access the data in the Data & Correlation tab and copy the Actual Call Volume data (column D) into the Exponential Smoothing tab to calculate a forecast for time period (month) 19. Note and use the value of 24,279 for the initial value in cell J10. Use trial and error to determine the best alpha value for this Exponential Smoothing Model. Provide the new forecast from the Exponential Smoothing model (at the end of column D)along with the model accuracy values and enter them below.
Month 19 Forecast:_________________
Alpha Value Selected:_________________
MAD Accuracy value:_________________
MAPE average Percent Error value:_________________
MSE value:_________________
ME Bias value:_________________
For the following, keep your answer to less than 150 words. Note the resulting line graph in the Exponential Smoothing tab and along with the qualitative facts from the Data & Correlation tab, answer the following:What are at least two (2) of the major causes(events)of the trend from period 9 through period 18 which had the greatest impact on headcount and call volume?
Causal descriptions with supporting facts/data Answer:
Question 2c (10 points): Access the data in the Data and Correlation tab and copy the dependent variable and the Total Co. Employee Head Count independent variable into Linear Regression tab data columnsand assess the results.Use the value fromthe Data & Correlation cell(E22)for the Linear Regression estimator cell (x value, cell L8). Provides a forecast of the Call Volume based on the Total Co. Employee Head Count. Also, provide the model quality values below.
Month 19 Predicted value (y) Forecast based on Total Co. Employee Head Count:_________________
MAD Accuracy value:_________________
MAPE average Percent Error value:_________________
MSE value:_________________
ME Bias value:_________________
Question 2d (10 points): Again, access the data in the Data &Correlation tab and,using the same dependent variable,copy the independent variable that has the strongest impact and highest quality to the dependent variable into the data column of the Linear Regression tab. Use the Correlation Coefficient and Coefficient of Determination values from the Data & Correlation tab as the basis of your selection. Also, use the supporting head count value from row 22 in the Linear Regression estimator cell (x value) L8. Provides a forecast of the Call Volume based on the Head Count that has the greatest impact. Also, provide the model quality values below.
Which independent variable was selected:_________________
Month 19 Predicted value (y) Forecast based on selected Head Count variable:_________________
MAD Accuracy value:_________________
MAPE average Percent Error value:_________________
MSE value:_________________
ME Bias value:_________________
Part 3 (40 points):
InPart 3, you will use your answers from parts 1 and two 2 to summarize your findingsand provide recommendations based on analyticinterpretation of data and facts.
Question 3a (10 points):
For the following, keep your answer to less than 150 words. In terms of how you used each model, what was the difference in using Exponential Smoothing verses Linear Regression in assessing causation from the data and model calculations? In other words, what assessmentinformation did each model provide you that the other did not?
Answer:
Question 3b (10 points): For the following, keep your answer to less than 150 words. Given the accuracy, percent error, and bias, which model provides the best forecast for period 19 and why?
Answer:
Question 3c (5 points): What is your forecast for period 19? Provide justification and/or rationale in terms of facts and keep your answer to less than 200 words..
Call Volume Forecast for period19 (My forecast): _________________
Explanation and Justification of Your Forecast Method:
3d Summary(15 points):Provide process and/or method recommendations to allow Mark to provide the most accurate month-to-month forecast. Respond in terms of what he should do and what methods he should use. Keep your descriptive answer to 500 words or less below.
