Adjusted R-Square: 66.1

The assignment is a time-series analysis of sales data, which involves creating a linear model and predicting future sales based on historical patterns.

Question 1
"You are a financial analyst in a major company in the 'Clothes, shoes and jewerly' industry in the
United States. Your boss has tasked you with producing a sales forecast for the next 12 months of
the overall industry. Your forecast will become the input to plan internal resources. Your boss wants
to ensure that the right amount of materials, cash and people is in place to satisfy, no more and no
less, the projected sales. Therefore, accuracy is quite important.
In the file attached, you will find data from April 2003 through Mar 2014. Your task is to produce a
monthly forecast for April 2014 through March 2015 (12 next months). "
You need to come up with the best possible forecast using the methods we learnt in class. You will
need to input your forecast month by month in the last question of this quiz.
In addition, there are six numeric questions you need to answer to assess your understanding of the
different forecasting methods we learnt in class. It is important to note that myCourses randomly
selects questions corresponding to different methods, regardless of whether that is the best method
for the specific data set you have received.
There is also one open-ended question about trend and seasonality.
In other words, to achieve the highest possible grades in this quiz you need to:
 Accurately answer the numeric questions about R^2, RMSE, Seasonal Factors and
Deseasonalized Sales for a given method. This method may or may not be the most accurate
one for your industry.
 Answer the open ended question about trend and seasonality observed in your industry.
 Produce the most accurately possible sales forecast for the next 12 months using any method
and adjustments you may wish to follow.
How will you be evaluated?
 For the randomly selected set of six questions, there is one single correct answer and you will
need to input accurately to earn marks.
 Each of the six questions is worth seven points.
 You need to answer an open ended question about your understanding of the trend and
seasonality in your industry worth 15 points.
 You need to produce a usable forecast for your industry. Usable means that it makes sense
because it follows the historical trend and seasonality accounting for unusual events, as well as
any necessary manual adjustments. This is worth five points.
 Since we are aiming for accuracy, your forecast will be evaluated against the forecasts of your
fellow classmates. The most accurate forecast will receive full marks (40 points), the least
accurate forecast will receive zero marks and a ranking order for the forecasts in between will
receive partial ranked marks.

 The point above means that it is in your best interest for you to outperform your
classmates. In other words, your helping of other classmates will have a negative impact
in your score by design: the more accurate the forecast of a classmate of yours the less
marks you will receive. And the more accurate your forecast compared to the class the
more marks you will receive.
 Acknowledgement of your understanding of these instructions and confirmation of having access
to the raw historical data of your industry grants you three points. Make sure you check the
box that reads "I understand and accept my task..." to earn three points.
Access your excel called Clothes shoes and jewerly Industry (1).xlsx
The next six questions are designed to probe your understanding of the different methods we learnt
in class.
You are required to perform calculations to answer the given questions. For example, if you receive
a question about the error for the linear regression with dummies, you have to transform the data
into monthly dummy variables, run the regression, and calculate the RMSE.
You need to base your answers on the calculations using all 132 monthly sales figures. In other
words, when running your regressions and moving averages for the next six questions, you are
required to use all points given in your data set.
Question 2
What is the RMSE for the linear method? (Simple linear regression, no dummies)
Round to zero decimal places. This is, input the value as an integer (whole number). Don't use
comma or points to separate the thousands. See examples:
If your resulting RMSE = 6,890.21, you enter 6890
If your resulting RMSE = 17,219.83, you enter 17220
If your resulting RMSE = 507.41, you enter 507
Question 3
What is the Adjusted R-Square of the linear model? (Simple linear regression, no dummies)
Round to one decimal place and enter the value as percentage without the percentage sign. Use a
period (or point) to separate the integer from the fraction, DO NOT USE comma. See examples:
If your resulting Adj R-Square = 49.31%, you enter 49.3
If your resulting Adj R-Square = 65.89%, you enter 65.9
If your resulting Adj R-Square = 0.3213, you enter 32.1

If your resulting Adj R-Square = -1.82, you enter -1.8
If your resulting Adj R-Square = 49.03, you enter 49.0
Question 4
What are the desasonalized sales for December 2012?
Round to zero decimal places. This is, input the value as an integer (whole number). Don't use
comma or points to separate the thousands. See examples:
If your resulting deseasonalized sales = 6,890.21, you enter 6890
If your resulting deseasonalized sales = 17,219.83, you enter 17220
Question 5
What is the RMSE of the linear model using dummy variables for the months?
Round to zero decimal places. This is, input the value as an integer (whole number). Don't use
comma or points to separate the thousands. See examples:
If your resulting RMSE = 6,890.21, you enter 6890
If your resulting RMSE = 17,219.83, you enter 17220
If your resulting RMSE = 507.41, you enter 507
Question 6
What are the desasonalized sales for May 2009?
Round to zero decimal places, this is input the value as an integer (whole number). Don't use
comma or points to separate the thousands. See examples:
If your resulting deseasonalized sales = 6,890.21, you enter 6890
If your resulting deseasonalized sales = 17,219.83, you enter 17220
Question 7
What is the seasonal factor for the month of January?
Round to two decimal places. Use a period (point) to separate the integer from the fraction, DO NOT
USE comma. See examples:
If your resulting Seasonal Factor = 1.23902, you enter 1.24
If your resulting Seasonal Factor = 0.7831, you enter 0.78
If your resulting Seasonal Factor = 2.07002, you enter 2.07

If your resulting Seasonal Factor = 0.96814, you enter 0.97
Question 8
Describe the trend and seasonality of your industry.
 Write your answer in form of a business write-up. Pretend you are addressing the paragraph to
your boss who just joined the company and is not familiar with the industry. Help her to
understand how the industry typically behaves and what the historical trends on sales shows.
 One or two paragraphs.
 Provide business explanations behind your times-series. This means, keep it simple and
understandable to any reader. Do not embellish your text, and do not include statistics jargon
like R-Square, Seasonal Factors or RMSE.
 Explain the seasonality and what could be causing it. Feel free to use Internet resources to
research reasons behind your peaks and valleys. If you choose to do so, you must provide a
reference.
 Is the industry growing, decreasing, or remaining flat?
 Are there any interesting changes on trend or seasonality? If so, explain why this could be.
Question 9
Enter your forecast for the next 12 months.
 Remember that you are aiming for accuracy.
 Your chosen model to forecast the sales may not necessarily be any of the models you worked
during the first six questions.
 In the next question you must submit the file showing your work, and clearly identifying the cells
containing your sales forecast. Failure to do so, or if your numbers in the Excel file do not match
the numbers provided here, will earn you zero marks for this question.
Round to zero decimal places. This is, input the value as an integer (whole number). Don't use
comma or points to separate the thousands. Do not enter currency signs or any other symbol that is
not a numeral. See examples:
If your resulting forecast sales = 6,890.21, you enter 6890
If your resulting forecast sales = 17,219.83, you enter 17220
If your resulting forecast sales = $63,087.41, you enter 63087
If your resulting forecast sales = 63,087.41USD, you enter 63087
If your resulting forecast sales = 63,087.41 dollars, you enter 63087
Your forecast:
April 2014:
May 2014:

June 2014:
July 2014:
August 2014:
September 2014:
October 2014:
November 2014:
December 2014:
January 2015:
February 2015:
March 2015:
Question 10
Upload your file showing your work.
You must clearly label a worksheet as "Final Forecast" that includes:
 The 12 months sales forecast, one month on each cell.
 Clearly identify which number corresponds to which month.
 The numbers must match to the numbers you input in the question above.
 The cells containing your final forecast cannot be values only. You must have formulas, or
references that point to the actual calculations you performed to arrive to the forecast.
 The point above means that, if it is not clear which method or adjustment you did to obtain your
sales, your answer will be considered invalid. In other words, inputting numbers for the sake of
inputting numbers as the forecast, even if accurate, will earn you no marks.
On top of the worksheet called "Final Forecast", feel free to add as many supporting worksheets as
you deem necessary.