Dr. Phillip S. Rokicki

The Prescott College Bookstore Case Study

Qnt. 5040 – Winter 2015

Forecasting Case Study

Maximum Points: 35 points

Files Needed: The Prescott College Bookstore Case Study 2015 (a Word file)

Prescott Bookstore Forecasted Sales 2015 (an Excel file)

Introduction:

The vice president of business services at Prescott College (PC) is worried about the future of her on campus bookstore. The 25 year old college has operated its own bookstore for its entire history, but now finds that it may have to contract out the operation of the bookstore. Dr. MaryAnn Lane, vice president, knows that the college president want to “off-load the bookstore and use the proceeds to finance a new bell tower on the campus.”

The college president has said to Dr. Lane that a unnamed college bookstore company would make a $1 million donation to the college for the contract to operate the bookstore for 10 years. Unfortunately Dr. Lane knows that this particular company has the reputation of “increasing the cost of textbooks and e-books by over 20%” and she is worried that the students attending PC will not be able to afford those costly textbooks.

Dr. Lane has gathered the last several years of monthly sales (in thousands of dollars) at the college bookstore but needs to forecast the next 12 months of sales. She has turned to you, a friend of the college in Prescott to help. She has asked you to do the following:

You are supplied with the prior 4 years of monthly sales at the PC bookstore. You are to use this information (NOTE: There are two spreadsheets on in a single column and one with the data in rows) to complete the following tasks:

Analyze the historical data using the Sales in Single Column and StatTools’ one variable summary and describe the important information that is contained in this data including the mean, median (comparing both), the skewness and Kurtosis and the quartiles and interquartile range. What does this data tell you about the sales at the bookstore? Are the sales stable, declining or increasing? Does it appear that the school sales are seasonal or not?

Create a histogram of the historical data and analyze the results. What different picture does this histogram tell you? When do the majority of the sales occur?

Using the sales in row data create box and whisker plots for all 4 years. What do these box and whisker plots tell you about the bookstore sales data over the years? Has the sales remained the same from year to year, or has it changed? Do the sales appear to be increasing or decreasing?

Using the sales in single column and StatTools forecasting functions create the following forecasts for the next 12 months:

a.A moving average forecast;

b.A simple exponential smoothing forecast;

c.A Holt’s double exponential smoothing forecast; and,

d.Winter’s exponential smoothing forecast.

Compare the mean absolute error, root mean square error, and the mean absolute percent of error for all four – what do these statistics tell you about the forecasts? Which one is the best forecast and why? Use Table 1 to do this comparison and include it in your individual case study report.

Table 1. Comparison of the Forecasting Techniques
Moving / Exponential / Holts / Winters
MAE
RMSE
MAPE

Compare the forecast lines of the four techniques, what do they tell you about the possible forecast? Which one is the best forecast and why?

Compare the 12 month forecast for the forecast technique you have selected as the best to the historical data provided about the actual bookstore sales. What does the forecast versus the original data show you? Is the forecast the same or different from the actual data?Be specific.

Complete the following table and include it in your report using the forecasting technique that you have selected as the best for the college.

Table 2. Historical and Forecast data for the Prescott College Bookstore

Year / Jan / Feb / Mar / Apr / May / Jun / Jul / Aug / Sep / Oct / Nov / Dec / Total Annual Sales
2009 / 196 / 188 / 192 / 164 / 140 / 120 / 112 / 140 / 160 / 168 / 192 / 200 / 1,972
2010 / 200 / 188 / 192 / 164 / 140 / 122 / 132 / 144 / 176 / 168 / 196 / 194 / 2,016
2011 / 196 / 212 / 202 / 180 / 150 / 140 / 156 / 144 / 164 / 186 / 200 / 230 / 2,160
2012 / 242 / 240 / 196 / 220 / 200 / 192 / 176 / 184 / 204 / 228 / 250 / 260 / 2,592
2013 / 0

Based on the information and forecast that you have calculated, determine if the vice president of business is correct that her bookstore is a money making enterprise, or if the college president will get his new bell tower. Be specific on your answer, this is not a yes or no answer.

Write up your case study results using the examples of case studies provided, and include the individual case study grading rubric and the required first page for your report. Consult the various chats concerning the individual case study for what the individual case study report is to include and how it is to be formatted.

Complete the case study and attach your Excel spreadsheet in the assignment drop box by the deadline for your section.

# # #