Appendix B: Instructor’s Manual
Assignments (with Solutions) using the Movie Data
Accompanying Article Available at http://www.amstat.org/publications/jse/v17n1/mclaren.html

Note to Instructors: Our students, like many introductory business statistics students, have access to SPSS and Minitab so in most cases these exercises provide specific instructions for these two packages. We have also included instructions for Excel 2007. While we are cognizant of the issues with using Excel for statistical analyses, we also realize its simplicity for basic functions such as graphing, and we use it in our classes along with Minitab and SPSS because we feel that business students should be proficient in its use.

We used SPSS version 15, Minitab version 15, and Excel 2007 to write the following instructions. If your students will be using other versions of these softwares or if you prefer that your students use different statistical software, you will need to edit the instructions accordingly before distributing to your students.

Exercise 1: Data Retrieval and Graphing

Learning Objectives:
1. Locate and retrieve data from a web site.
2. Place retrieved data into Excel and format appropriately. (This step is recommended because the format from the website is not easily imported directly into SPSS and Minitab).
3. Create and format a time series plot in SPSS, Minitab, Excel or other software.

Instructions for the students: For this exercise you will use movie box office data found at http://www.the-numbers.com/. You will locate data for a specific movie, bring the data to Excel to format it, and then create a time series plot using SPSS, Minitab, Excel or other software.

1. Go to http://www.the-numbers.com/ and click the Movie Archive button, found in the left column of the page. Click the letter of the alphabet for the first word in the title of your movie, then find your movie and click its link. Scroll down until you find the Weekend Chart Record. Copy the Weekend Chart record and paste it in a new Excel worksheet. Do the same for the Daily Chart Record by pasting it in a new worksheet.

2. Using this information, create two tables. Table 1 should provide the dates and the per theater weekend box office receipts. You may, if you wish, create a new column that numbers the weeks consecutively. Table 2 should provide the same information for the daily per theater box office receipts. Note that the “Days” column shows the day number since the release. Check to be sure that consecutive dates are available. If there is a gap in the time sequence, determine a way to designate that in your table.

3. If you wish to import it into SPSS or Minitab, you may need to save your Excel file as an Excel 2003 (*.xls). This Excel worksheet can be imported into SPSS or Minitab as desired. a) In SPSS, use File > Open > Data. Change “Files of type” to Excel (*.xls) and select your file. Note that SPSS will require you to import each set of data (daily and weekly) separately. b) In Minitab, use File > Open Worksheet. Change “Files of type” to Excel (*.xls) and select your file. Minitab will import each Excel sheet into a separate data sheet.

4. Create two time series plots (one for daily per theater receipts, one for weekly per theater receipts). a) In SPSS, one option is to use Graphs > Legacy Dialogs > Line > Simple (Values of Individual Cases). The Line Represents the Per Theater Receipts and the category labels come from the variable Date. b) In Minitab, use Graph > Time Series Plot > Simple. The “series” is the Per Theater variable. You may select “Time/Scale” and use “Stamp” to use the Date variable as x-axis labels. c) In Excel, on the Insert ribbon, select “Line” and choose the desired format. Excel may put all variables into the graph, so you may simply delete the ones you don’t want.
5. Format your charts so that the labels, titles, and size make the data easy to read.

Exercise 2: Descriptive Statistics & Analysis

Learning Objectives:
1. Use SPSS, Minitab, Excel, or another software package to calculate descriptive statistics for several different variables.
2. Interpret and analyze descriptive statistics to draw conclusions.

Instructions for the students: In this exercise, you will compute descriptive statistics for several different types of movies using SPSS, Minitab, Excel, or another package and examine these statistics to draw conclusions about the movie types.

1. Using the data file movietotal.dat, calculate descriptive statistics, including the mean, median, variance, standard deviation, minimum and maximum, for the movies in each category using your software.

a) In SPSS, select File > Open > Data. Change “Files of Type” to Data (*.dat) and select the file. Make sure you indicate that the file is delimited, that variable names are at the top of your file, and that only tabs are used as delimiters. Ensure that Movie and Type are both specified as String variables (40 characters should be sufficient length). Use Analyze > Compare Means > Means and select “Type” for the Independent List and “Total” for the Dependent List. Under options, select the desired descriptive statistics. b) In Minitab, select File > Open Worksheet. Change “Files of Type” to Data (*.dat) and select the file. Choose Stat > Basic Statistics > Display Descriptive Statistics. Select the Total variable into the Variables box, and Type into the By Variables box. c) In Excel 2007, select File > Open and change “Files of Type” to “All files (*.*). Indicate that the file is Delimited by Tabs and open. Sort by movie type. Then on the Data ribbon select Data Analysis > Descriptive Statistics and point to each group of data separately, OR use the Excel functions/formulas AVERAGE, MEDIAN, VAR, STDEV, MIN, MAX.

2. Examine and compare the descriptive statistics for each category. Write a paragraph discussing the similarities and differences. Be sure to mention the measures of central tendency and variability. Then write another paragraph in which you interpret these findings; in particular, explain what you think causes the differences you see between movie types. Address if these descriptive measures make sense, and, if there is anything surprising, explain what you think caused these anomalies.

Exercise 3: Examination of Time Series Data

Learning Objectives:
1. Develop a time series plot from an existing set of data values.
2. Visually examine the time series and evaluate trend, seasonal, and cyclical movements of the time series.
3. Using SPSS, Minitab or other statistical software, create graphs that more closely examine seasonality.

Instructions for the students: For this exercise you will create two time series plots using movie box office data. Using visual analysis and software tools, you will prepare a discussion of the features of the plots.

1. For a chosen movie, retrieve both the weekend and the daily per theater box office data from Datasets and Stories or from http://www.the-numbers.com/. Examine the dates associated with the values and if there are missing values, make appropriate adjustments.

2. Using the weekend time series, create a time series plot using SPSS, Minitab, Excel (see Exercise 1 above) or a software package of your choice. Format the graph for legibility. Examine the graph for any patterns, paying attention to seasonality, trend, and cyclical movements. Write a paragraph about what you see. Be sure to note any unusual values; can you provide explanations for them?

3. Using the daily time series, create a time series plot using SPSS, Minitab, Excel (see Exercise 1 above) or a software package of your choice. Format the graph for legibility. Examine the graph for any patterns, paying attention to seasonality, trend, and cyclical movements. Write a paragraph about what you see, discussing the patterns you found. Be sure to note any unusual values; can you provide explanations for them?

4. (Optional) If you can do so with your software, partition the daily time series so that you can identify the day of the week of each observation and graph the observations from each day separately.

a) In SPSS, follow these steps: i. First make sure the Date variable is formatted as a date and not a string. Use the format mm/dd/yy to avoid losing any data. ii. Next, select Transform > Compute. Create a new variable name (e.g. dayofweek), then select from the Function Group Date Extraction the function “Xdate.Wkday()” by double clicking. Select the Date variable into the parentheses by double clicking. Then press OK. This should create a new variable in your data file with values 1=Sunday through 7= Saturday. iii. To separate into distinct variables for each weekday, choose Transform > Compute. Name the new variable as Sunday and make that equal to Per Theater receipts using the condition “IF dayofweek = 1”. Repeat the computation for the new variable Monday using the condition “IF dayofweek = 2,” Tuesday IF dayofweek = 3, etc. You can then sort by dayofweek and copy the seven columns of data to a new data file. iv. Select Graph > Legacy Dialogs > Line > Multiple (Values of Individual Cases). Under “Lines Represent” enter Sunday, Monday, …, Saturday and keep the category labels as Case number. Examine your graph and explain what you see. How does each day change over time? Is one day consistently larger than the rest? Is one day always the smallest? How do these patterns compare to the way you attend movies?

b) In Minitab, follow these steps. i. First, in Excel, use the function =weekday(date,1) to place the day of the week next to the date. The “1” codes the dates using 1 for Sunday, 2 for Monday, etc. ii. Copy the daily per theater receipts column and the day of the week column to Minitab. iii. Separate the weeks of data. From the main menu, choose Data > Unstack Columns. Complete the dialog boxes and click OK. A sample is shown below.
[pic]

iv. In Minitab, choose Graph > Time Series Plot > Multiple. Specify the seven new columns with weekday data and click OK. Examine your graph and explain what you see. How does each day change over time? Is one day consistently larger than the rest? Is one day always the smallest? How do these patterns compare to the way you attend movies? Exercise 4: Nonlinear Trend Forecasting

Learning Objectives:
1. Examine a time series plot and recognize nonlinear trend.
2. Use SPSS, Minitab, Excel, or other software to determine the trend equation.
3. Evaluate trend equations for suitability for forecasting.

Instructions for the students: This project deals with nonlinear trend. You will fit several nonlinear trend equations to the weekend per theater box office receipts and determine their suitability as forecasting models.

1. Choose a movie from Datasets and Stories or from http://www.the-numbers.com/and copy the weekend box office data to a blank Excel worksheet. (This step is recommended because the format from the website is not easily imported directly into SPSS and Minitab).Check for missing values and make any necessary adjustments. Import your data into SPSS or Minitab as desired (see Exercise 1 above).

a) If using SPSS, go to Analyze > Regression > Curve Estimation. Put Per Theater receipts as the dependent variable, and click on the radio button for “Time” to indicate it as the independent variable. Click on all of the boxes under Models. Your output will include a table with each model’s r-square and parameter estimates, among other statistics. Based on the R2 value, your understanding of least squares and fits, and a visual inspection, choose the two best equations to use for forecasting this time series. Using the SPSS help, find the equations of the models you chose and insert the parameter estimates. From a practical standpoint, do these models make sense? [pic]

b) If using Minitab, open (or copy) the data in Minitab. Choose Stat > Time Series > Trend Analysis. You will see four choices for trend models (see below). Select one of the models (not Linear). Repeat the analysis for the other two non-linear models. For each your output will include a chart with the original and fitted values, the trend equation, and the MAPE, MAD, and MSD for each. Based on this information, your understanding of least squares, fits and accuracy measures, and a visual inspection, choose the two best equations to use for forecasting this time series. From a practical standpoint, do these models make sense? [pic]

c) If using Excel, create a Line Plot of your per theater time series data by going to the Insert ribbon and selecting “Line” and the appropriate chart type. Format the chart so that it is easy to read. On the Chart Tools ribbon, go to the Layout ribbon, then choose Trendline. Select More Trend Line Options. [pic]

Experiment with these Trend/Regression types, trying at least three different choices. In each case, before you say OK, click on the check boxes to specify that both the equation and the R2 value should be displayed on the chart. Based on the R2 value, your understanding of least squares and fits, and a visual inspection, choose the two best equations to use for forecasting this time series. From a practical standpoint, do these models make sense?

2. Compare the suitability of the two models you have chosen. You should use each model to create forecasts, calculate errors, and generate comparable measures. Then, based on all available information, make a recommendation of the best method and defend your choice.

Exercise 5: Project

Note to the instructor: This project duplicates the activities from previous exercises, combining them into one project and adding a calculus-based activity for rate of change. We suggest you allow the students to work in small groups and treat this as an out-of-class project. The instructions are written using the movie Lord of the Rings: Return of the King. You may choose another movie for all students to use or allow the students to choose their own films. If another movie is chosen, note that you may have to change the time periods referred to in Part II, questions 1, 4 and 5 below.

Learning Objectives:
1. Locate and retrieve data from a web site and use it to create time series plots in SPSS, Minitab, Excel or other software.
2. Visually examine the time series and evaluate trend, seasonal, and cyclical movements of the time series.
3. Use SPSS, Minitab, Excel, or other software to determine the trend equation.
4. Using the concept of the derivative, investigate the rate of change of the trend equation.
5. Evaluate models for suitability for forecasting.

Instructions for the students: This project incorporates both forecasting and calculus. Your submission should be a word-processed document, complete with tables and graphs.

For this project you will use the movie box office data base (found at http://www.the-numbers.com/). Each group will use the data from the movie Lord of the Rings: Return of the King. For this project you will be looking at weekend per theater box office receipts. (Click Movie Archive on the left border, click the letter L, find and click the link for Lord of the Rings: Return of the King, and then scroll down until you get to the weekend table. Copy the table and delete all but the date and the weekend per theater receipts. A portion of the data is shown below—you should use all of the data that has been tallied at the time you access the table.

[pic]

Part I: Description of Data
Begin by providing a table that lists the dates, the observation numbers, and the time series values. Adjust the formatting as needed. The other columns are not necessary. Next, create and include a time series plot of your data using your statistical software or Excel (see Exercise 1 above) formatted so that it is both legible and fits the space. Describe what you see.

Part II: Development of Forecasting Models
Like most movies, this time series shows a nonlinear trend. Both your statistical software and Excel have built-in routines to create nonlinear trend forecasting equations. Use your statistical software to find the best (defined as the lowest MAPE for these observations) forecasting model (see Exercise 4 above for detailed instructions).

1. Determine the best nonlinear trend equation for this data. Use the chosen model to predict the weekend per theater box office receipts for time period 24.

2. Find the derivative of the function you chose in question 1.

3. Describe how the derivative behaves as x (time) increases. Does the derivative ever change its sign? Does the derivative ever become 0? What does this say about the value of box office receipts over time?

4. Evaluate the derivative at time period 23. This value is the expected change in the function for a one unit increase in time. Use this information to predict the value of box office receipts for week 24. Fully explain your calculations.

5. Compare the forecast you obtained from the derivative in question 4 to the forecast generated by method you chose in question 1. Comment on the effectiveness of each forecast. How do these values compare to the actual value for week 24?

Exercise 6: Seasonal Forecasting

Learning Objectives:
1. Develop appropriate seasonal forecasting models using the daily per theater box office receipts. Methods to consider may include seasonal decomposition, multiple regression, and Winters’ Exponential Smoothing.
2. Create fitted values using these methods. Calculate error measurements. Choose the most suitable method and justify its choice.

Instructions for the students: In this exercise, you will examine the seasonal patterns in the daily per theater box office receipts. Using tools available to you, you will create seasonal forecasting models and evaluate them.

1. Choose a movie and retrieve the daily per theater box office data from Datasets and Stories. Check to confirm that there are no missing values; adjust if necessary. Plot the time series and format for legibility. (See Exercise 1 above for detailed instructions.)

2. Create appropriate seasonal forecasting models.

a. Seasonal Decomposition: using either the built-in methods in Minitab or SPSS (if you have access to the SPSS Trends module), or direct calculations in Excel, create a seasonal decomposition model. Begin by examining the plot to determine if an additive or a multiplicative model would be more appropriate. Report the seasonal indexes and the trend equation (ignore cycles). Use your model to create fitted values and calculate the MAD, the MAPE, and the MSE.

b. Multiple Regression: what variables might explain the variation in daily receipts? Consider time, days of the week, and indicator variables for holidays and other special events. Develop a robust multiple regression model for this time series using your software. Use your model to create fitted values and calculate the MAD, the MAPE, and the MSE.

c. Exponential Smoothing: because the time series exhibits both trend and seasonality, Winters’ model is appropriate. In Minitab go to Stat > Time Series > Winters’ Method. Try several different sets of parameter values until you feel you have developed a good model. [More advanced students who want an extra challenge could use direct calculations in Excel to develop the smoothing model and use Solver to find the best values for the smoothing parameters.] If one has access to the SPSS Trends module, this analysis may also be done there. Use your model to create fitted values and calculate the MAD, the MAPE, and the MSE.

3. Compare the three models, discussing their performance and suitability for this sort of application. Which would you recommend?

Exercise 7: Comparing Several Movies

Learning Objectives:
1. Determine a list of factors that movies with similar box office results might have in common.
2. Gather data from a group of similar movies and compare the time series results. Use the data to develop a forecasting model for a new release.

Instructions for the students: In this exercise you will play the role of a movie industry analyst who must predict box office revenue for a new movie. In order to find similar movies to use for comparison, you will need to determine which factors are appropriate. Once you have selected the comparison group, you will use its data to develop a model for the new release.

1. The fictitious new release you will use for this exercise (tentatively titled Force Factor 5) is a big-budget action movie with an ensemble cast that includes at least two big-name Hollywood stars. It is based on a best-selling novel. The director has been nominated for at least one major award. The movie will be released in May. Determine which factors you will use to define “similar” movies and explain why they could be important.

2. Determine at least three movies that fit your definition of “similar” and obtain their box office data. What similarities and differences do you find in the three time series?

3. Develop a forecasting model for the projected revenue for Force Factor 5 using the data from the three time series. Write a paragraph explaining how you decided what to use for the model.
Solutions to Assignments using the Movie Data

Note to instructor: the movie “Lord of the Rings: The Return of the King” is used for illustration of solutions.

Exercise 1: Data Retrieval and Graphing

TABLE 1: WEEKEND RECEIPTS
|Weekend # |Date |Per Theater | |Weekend # |Date |Per Theater |
|1 |12/19/2003 |$19,614 | |13 |3/12/2004 |$1,547 |
|2 |12/26/2003 |$13,664 | |14 |3/19/2004 |$1,323 |
|3 |1/2/2004 |$7,610 | |15 |3/26/2004 |$1,068 |
|4 |1/9/2004 |$4,023 | |16 |4/2/2004 |$1,074 |
|5 |1/16/2004 |$3,403 | |17 |4/9/2004 |$856 |
|6 |1/23/2004 |$2,653 | |18 |4/16/2004 |$771 |
|7 |1/30/2004 |$2,354 | |19 |4/23/2004 |$727 |
|8 |2/6/2004 |$2,286 | |20 |4/30/2004 |$700 |
|9 |2/13/2004 |$2,518 | |21 |5/7/2004 |$546 |
|10 |2/20/2004 |$2,026 | |22 |5/14/2004 |$605 |
|11 |2/27/2004 |$1,956 | |23 |5/21/2004 |$535 |
|12 |3/5/2004 |$1,596 | |24 |5/28/2004 |$415 |

[pic]
TABLE 2: DAILY RECEIPTS
|Day |Date |Per Theater | |Day |Date |Per Theater |
|1 |12/17/2003 |$9,303 | |51 |2/5/2004 |$156 |
|2 |12/18/2003 |$4,596 | |52 |2/6/2004 |$479 |
|3 |12/19/2003 |$5,890 | |53 |2/7/2004 |$1,073 |
|4 |12/20/2003 |$7,424 | |54 |2/8/2004 |$730 |
|5 |12/21/2003 |$6,299 | |55 |2/9/2004 |$164 |
|6 |12/22/2003 |$3,663 | |56 |2/10/2004 |$152 |
|7 |12/23/2003 |$3,369 | |57 |2/11/2004 |$142 |
|8 |12/24/2003 |$2,037 | |58 |2/12/2004 |$161 |
|9 |12/25/2003 |$3,777 | |59 |2/13/2004 |$481 |
|10 |12/26/2003 |$5,172 | |60 |2/14/2004 |$1,177 |
|11 |12/27/2003 |$4,658 | |61 |2/15/2004 |$860 |
|12 |12/28/2003 |$3,834 | |62 |2/16/2004 |$543 |
|13 |12/29/2003 |$2,833 | |63 |2/17/2004 |$201 |
|14 |12/30/2003 |$2,597 | |64 |2/18/2004 |$179 |
|15 |12/31/2003 |$2,025 | |65 |2/19/2004 |$176 |
|16 |1/1/2004 |$3,227 | |66 |2/20/2004 |$426 |
|17 |1/2/2004 |$2,863 | |67 |2/21/2004 |$924 |
|18 |1/3/2004 |$2,853 | |68 |2/22/2004 |$637 |
|19 |1/4/2004 |$1,894 | |69 |2/23/2004 |$147 |
|20 |1/5/2004 |$643 | |70 |2/24/2004 |$153 |
|21 |1/6/2004 |$589 | |71 |2/25/2004 |$134 |
|22 |1/7/2004 |$519 | |72 |2/26/2004 |$141 |
|23 |1/8/2004 |$446 | |73 |2/27/2004 |$450 |
|24 |1/9/2004 |$1,019 | |74 |2/28/2004 |$899 |
|25 |1/10/2004 |$1,823 | |75 |2/29/2004 |$629 |
|26 |1/11/2004 |$1,159 | |76 |3/1/2004 |$248 |
|27 |1/12/2004 |$333 | |77 |3/2/2004 |$250 |
|28 |1/13/2004 |$345 | |78 |3/3/2004 |$218 |
|29 |1/14/2004 |$299 | |79 |3/4/2004 |$227 |
|30 |1/15/2004 |$293 | |80 |3/5/2004 |$420 |
|31 |1/16/2004 |$791 | |81 |3/6/2004 |$753 |
|32 |1/17/2004 |$1,537 | |82 |3/7/2004 |$464 |
|33 |1/18/2004 |$1,075 | |83 |3/8/2004 |$117 |
|34 |1/19/2004 |$742 | |84 |3/9/2004 |$120 |
|35 |1/20/2004 |$254 | |85 |3/10/2004 |$111 |
|36 |1/21/2004 |$199 | |86 |3/11/2004 |$124 |
|37 |1/22/2004 |$214 | |87 |3/12/2004 |$383 |
|38 |1/23/2004 |$586 | |88 |3/13/2004 |$695 |
|39 |1/24/2004 |$1,235 | |89 |3/14/2004 |$480 |
|40 |1/25/2004 |$828 | |90 |3/15/2004 |$161 |
|41 |1/26/2004 |$210 | |91 |3/16/2004 |$177 |
|42 |1/27/2004 |$217 | |92 |3/17/2004 |$152 |
|43 |1/28/2004 |$231 | |93 |3/18/2004 |$171 |
|44 |1/29/2004 |$216 | |94 |3/19/2004 |$392 |
|45 |1/30/2004 |$621 | |95 |3/20/2004 |$586 |
|46 |1/31/2004 |$1,214 | |96 |3/21/2004 |$383 |
|47 |2/1/2004 |$532 | |97 |3/22/2004 |$119 |
|48 |2/2/2004 |$179 | |98 |3/23/2004 |$119 |
|49 |2/3/2004 |$184 | |99 |3/24/2004 |$116 |
|50 |2/4/2004 |$169 | |100 |3/25/2004 |$128 |

[pic]

Exercise 2: Descriptive Statistics & Analysis

1. [Descriptive Statistics for each movie type] Using Minitab, the Descriptive Statistics for Total U.S. Gross ($ Millions) by Type are:

|TYPE |N |Mean |StDev |Minimum |Median |Maximum |
|Best Picture |10 |202.6 |164.6 |55.3 |152 |600.8 |
|Biggest Gross |14 |320.4 |75.4 |234.8 |307.7 |461 |
|Series |14 |327.7 |59.9 |249.5 |313.5 |436.7 |
|Sundance |11 |9.01 |6.8 |1.28 |7.27 |21.47 |
|TOTAL |49 |228.6 |155.3 |1.28 |262 |600.8 |

2. [Examine and compare the descriptive statistics]

The means of the movie types range from about $9 million (Sundance films) to almost $330 million (sequel films). Best picture films are close behind, with a mean of $320 million. Although the biggest gross and sequels have the largest means, the standard deviation for the best picture films is substantially larger than the standard deviations of either type. The Sundance films have the smallest standard deviation.

It is clear that the biggest gross movies should have a large mean, and it is not surprising that Sundance films, which are often critically acclaimed but may not be attractive to large portions of the population, have a very small mean in comparison. It is interesting that the best picture films have such a large variation, indicating that some films chosen for this honor do have widespread appeal and therefore a large gross, while others are films praised for their artistic quality and less for their box office appeal.

What is probably most surprising about these statistics is that the biggest gross movies have a smaller mean, and in fact a smaller minimum, than the sequels movies. There are two possible contributing factors: first, the movie Titanic is the top grossing movie in the list, but is listed under the best picture category rather than the biggest gross category. Also, the sequels in the list are all very recent, while some of the biggest gross movies are from the 1970s and 1980s, which impacts the value of the box office dollars.

Exercise 3: Examination of Time Series Data

For this exercise, we will use the example above: “The Lord of the Rings: The Return of the King.”

1. [Retrieve data] Refer to the tables and graphs above.

2. [Graph weekend data] Refer to the graph above for the Weekend time series. The graph has a definite downward trend, though it is not linear. The values start out high, decreasing sharply at the beginning, and then decreasing slowly. There is no seasonality when the data are aggregated over an entire weekend. There is no evidence of cyclical behavior. There is one value for the weekend of Feb. 13, 2004 that seems a bit higher than what would be expected. This could be due to more than the normal number of people attending movies on the holiday weekend (Valentine’s Day).

3. [Graph daily data] Refer to the graph above for the Daily time series. The graph has a definite overall downward trend, though this trend is not linear. The values generally start out high, decreasing sharply at the beginning, and then decreasing more slowly. There is definite seasonality in the time series; there always appears to be a value that peaks every 7th day, with one or two other values around it almost as high, followed by several low values. This pattern is showing the movie viewing habits of audiences: most people see movies on weekends, so it is likely the peak is a Saturday, with Friday nights and Sunday afternoons also somewhat higher than the weekdays Monday through Thursday. There is no evidence multiple-week cycles. There do not appear to be any unusual values when trend and seasonality are accounted for.

4. [Partition data into days of the week]
a. [Use Excel “weekday” function to calculate the day of the week for each date, 1=Sunday]

|Day |Date |Daily Receipts |Day of Week |
|1 |12/17/2003 |$9,303 |4 |
|2 |12/18/2003 |$4,596 |5 |
|3 |12/19/2003 |$5,890 |6 |
|4 |12/20/2003 |$7,424 |7 |
|5 |12/21/2003 |$6,299 |1 |
|6 |12/22/2003 |$3,663 |2 |
|7 |12/23/2003 |$3,369 |3 |
|8 |12/24/2003 |$2,037 |4 |
|9 |12/25/2003 |$3,777 |5 |
|: |: |: |: |
|: |: |: |: |
|98 |3/23/2004 |$119 |3 |
|99 |3/24/2004 |$116 |4 |
|100 |3/25/2004 |$128 |5 |

b, c. [Copy to Minitab. Separate the weeks of data using “Data > Unstack Columns”]
|Daily |Daily |Daily |Daily |Daily |Daily |Daily Receipts_7|
|Receipts_1 |Receipts_2 |Receipts_3 |Receipts_4 |Receipts_5 |Receipts_6 | |
|6299 |3663 |3369 |9303 |4596 |5890 |7424 |
|3834 |2833 |2597 |2037 |3777 |5172 |4658 |
|1894 |643 |589 |2025 |3227 |2863 |2853 |
|1159 |333 |345 |519 |446 |1019 |1823 |
|1075 |742 |254 |299 |293 |791 |1537 |
|828 |210 |217 |199 |214 |586 |1235 |
|532 |179 |184 |231 |216 |621 |1214 |
|730 |164 |152 |169 |156 |479 |1073 |
|860 |543 |201 |142 |161 |481 |1177 |
|637 |147 |153 |179 |176 |426 |924 |
|629 |248 |250 |134 |141 |450 |899 |
|464 |117 |120 |218 |227 |420 |753 |
|480 |161 |177 |111 |124 |383 |695 |
|383 |119 |119 |152 |171 |392 |586 |
| | | |116 |128 | | |

d. [Graph time series]
[pic]
Each day has basically the same pattern as the overall series: it starts off high, decreases sharply at the beginning, and then decreases more slowly. The time series for day 7 (Saturday) is generally larger than the rest of the days, followed by Sunday (day 1) and Friday (day 6), which is not surprising given that movies are more popular on weekends. Although some weekdays appear to be larger during the first part of the series, this could be explained by a mid-week release date. Monday and Tuesday (days 2 and 3) appear to have the lowest values, followed by Wednesday (day 4), but only after its initial day. From this data, one could surmise that the film was released on a Wednesday, which is why so many people saw it initially on that date.

Exercise 4: Nonlinear Trend Forecasting

1. [Choose a movie] For this exercise, we will use the movie “Lord of the Rings: The Return of the King.”
|Observation # |Date |Per Theater Weekend Receipts |
|1 |12/19/2003 |$19614 |
|2 |12/26/2003 |$13664 |
|3 |1/2/2004 |$7610 |
|4 |1/9/2004 |$4023 |
|5 |1/16/2004 |$3403 |
|6 |1/23/2004 |$2653 |
|7 |1/30/2004 |$2354 |
|8 |2/6/2004 |$2286 |
|9 |2/13/2004 |$2518 |
|10 |2/20/2004 |$2026 |
|11 |2/27/2004 |$1956 |
|12 |3/5/2004 |$1596 |
|13 |3/12/2004 |$1547 |
|14 |3/19/2004 |$1323 |
|15 |3/26/2004 |$1068 |
|16 |4/2/2004 |$1074 |
|17 |4/9/2004 |$856 |
|18 |4/16/2004 |$771 |
|19 |4/23/2004 |$727 |

a) [SPSS Output] Two “best” fits according to R-square values are indicated by *
Dependent Variable: PerTheater Per Theater
|Equation |Model Summary |Parameter Estimates |
| |R Square |F |df1 |
|Quadratic |100 |1680 |5,051,327 |
|Exponential |21 |1148 |7,806,474 |
|S-curve |14 |1138 |10,265,789 |

Although the MSE for the quadratic model is lowest, the quadratic model eventually increases, so this would probably not be the best model. Of the others, the exponential and the S-curve model appear to be very similar with regard to MAD and MAPE. An inspection of the graphs (see step 1b above) shows that neither method works particularly well until period 4, at which point both methods fit closely with the actual values. The exponential method is closer to actual in the first three periods; however, in periods 4 through 10, the S-curve model appears closer. An argument could be made for choosing either model as the “best” for this data, depending on whether MSE or MAD/MAPE were used as criteria.

4. [Compare the suitability of the two models you have chosen. Create forecasts, calculate errors, and generate comparable measures.]

a) In SPSS, one would rerun the Curve Estimation for the chosen method, pressing the “Save” button and then checking off the boxes for Predicted Values and Residuals as shown below. You will then need to calculate MAD, MSE and/or MAPE from the residuals displayed.
[pic]

b) In Minitab: Forecasts can be generated by repeating the “Trend Analysis” for the chosen method and pressing the “Storage” button to compute Fits, Residuals, and Forecasts as shown below. Of course, the MAD, MSE and MAPE are already computed and displayed. [pic]

c) In Excel, one could use the specified equation of the chosen method to calculate forecasted values using Excel formulas, and then to compute the residuals and the MAD, MSE and/or MAPE by manipulating those errors with additional formulas.

Exercise 5: Project

Part I: Description of Data

The table of data and the time series plot are shown as part of the solutions to Exercise 1 (see page 11 of this instructor’s manual for solutions).

Description: The movie takes in a lot of money in the opening weekend and then the pattern shows a general decline in per theater average on each subsequent weekend. This pattern is somewhat broken by week 13, which as a holiday weekend (Labor Day), had slightly higher weekend receipts. Finally, when the per theater average is very low, the movie’s run ends.

Part II: Development of Forecasting Models

The forecasting models for this portion of the project are identical to those in Exercise 4 (see pages 16-19 of this instructor’s manual for solutions).

1. [Which model provides the best fit and most useful forecasting model for this data? Report the equation and justify your choice.] • The “winner” using both Excel and SPSS was the power function: Y = 25,755x-1.189 • For time period 24, if we plug in x = 24, the power function predicts a value of $589.

2. [Find derivative] The derivative of this function is:
(-1.189) (25,755) x(-1.189 – 1) =
-30,623 x-2.189 =
-30,623 / x2.189

3. [Describe behavior of derivative] The derivative will always be a negative coefficient divided by a positive number (positive x to a positive power); therefore, the derivative will always be negative. The derivative increases (becomes closer to zero) as x increases. This can be seen from the last line where the derivative is written as a fraction; while the numerator stays the same, the denominator increases and so the fraction becomes a negative number with a smaller absolute value (closer to zero). Although the limit of this function is zero, the derivative will never be zero or positive. In terms of box office receipts, one could say that at the beginning of a movie’s release, box office receipts decrease sharply (derivative is very small), and over time continue to decrease but at a slower rate as the movie’s run progresses.

4. [Use derivative at t = 23 to predict for t = 24] The derivative at time period 23 (x = 23) is:
-30,623 / x2.189 = -30,623 / (23)2.189 = -30,391 / 930.18 = -32.01

This implies that the expected decrease in box office receipts will be about $32 for a one week period. To predict the receipts for time period 24, we would subtract $32 from the value of the receipts at t = 23 which is $535. Thus, we predict for the 24th week receipts of:
$535 + -32 = $503.

When we compare this prediction to the actual value of $415 in week 24, we see that the prediction is off by about $88 (about 21%). While this is not very close in terms of the percentage error, an actual error of less than $100 is unlikely to be considered significant for a given theater.

5. The derivative produces a forecast of $503, which is about $88 higher than the actual value of $415 for time period 24. The forecasting method itself predicted for t = 24 a value of $606, which is about $191 higher than the actual. The derivative method was much closer.

Exercise 6: Seasonal Forecasting

1. [Retrieve data and plot] For this exercise, we will use the movie “The Lord of the Rings: The Return of the King.”
[pic]

2. [Create appropriate seasonal forecasting models] a. [Seasonal Decomposition] A multiplicative model would be appropriate since the amount of seasonality (in dollars) seems to decrease as the total per theater receipts decrease; therefore, it is appropriate to assume that seasonality is a percentage of receipts, rather than a dollar amount.

Using Minitab’s Decomposition command, the output/model is as follows: Time Series Decomposition for Daily Receipts Multiplicative Model Data Daily Receipts Length 100 NMissing 0 Fitted Trend Equation Yt = 4224.17 - 54.6425*t

Seasonal Indices Period Index 1 1.56820 2 0.47080 3 0.45703 4 0.45479 5 0.45367 6 1.24089 7 2.35462 Accuracy Measures MAPE 200 MAD 1195 MSD 2961515

b. [Multiple Regression] The variables that might explain variation in daily receipts include: • Time, t (number of days since release) • School vacation/holiday indicator variable, X1 (1 = vacation, 0 = school year) • Day of the week indicator variables: X2 (Tuesday), X3 (Wednesday), X4 (Thursday), X5 (Friday), X6 (Saturday), X7 (Sunday) • First day of release of film indicator, X8 • First week of release of film, X9 We can formulate a model that says:

Y (daily receipts) = B0 + B1X1 + B2X2 + B3X3 + B4X4 + B5X5 + B6X6 + B7X7 + B8X8 +B9X9 +B10 t + E

Results give: Regression Analysis: Daily Receipts versus X2_Tue, X3_Wed, ... The regression equation is Daily Receipts = 666 + 208 X2_Tue + 345 X3_Wed + 394 X4_Thu + 960 X5_Fri + 1464 X6_Sat + 974 X7_Sun + 2054 hol_vac_X1 - 12.9 t + 3932 First_Wk + 4372 First_Day

Predictor Coef SE Coef T P Constant 666.0 212.3 3.14 0.002 X2_Tue 208.2 205.7 1.01 0.314 X3_Wed 345.1 207.0 1.67 0.099 X4_Thu 393.9 201.1 1.96 0.053 X5_Fri 959.8 206.4 4.65 0.000 X6_Sat 1464.0 206.2 7.10 0.000 X7_Sun 973.5 206.0 4.73 0.000 hol_vac_X1 2054.3 205.7 9.99 0.000 t -12.922 2.340 -5.52 0.000 First_Wk 3932.5 261.9 15.02 0.000 First_Day 4372.4 602.8 7.25 0.000

S = 538.953 R-Sq = 91.0% R-Sq(adj) = 90.0%

Analysis of Variance Source DF SS MS F P Regression 10 261590325 26159032 90.06 0.000 Residual Error 89 25851890 290471 Total 99 287442215

Unusual Observations Obs X2_Tue Daily Receipts Fit SE Fit Residual St Resid 1 0.00 9303.0 9303.0 539.0 -0.0 * X 4 0.00 7424.0 6010.7 256.4 1413.3 2.98R 7 1.00 3369.0 4716.1 256.4 -1347.1 -2.84R 8 0.00 2037.0 907.7 183.6 1129.3 2.23R 10 0.00 5172.0 3550.8 222.7 1621.2 3.30R 17 0.00 2863.0 1406.1 171.0 1456.9 2.85R 34 0.00 742.0 2280.9 204.6 -1538.9 -3.09R 62 0.00 543.0 1919.1 221.9 -1376.1 -2.80R R denotes an observation with a large standardized residual. X denotes an observation whose X value gives it large influence.

After saving Fits and Residuals in the Minitab regression procedure, and calculating absolute, squared, and mean percentage errors, we find: MAD = 365.65 MSE = 259,819 MAPE = 86

c. [Exponential Smoothing. Winters’ model. Try several different sets of parameter values until you feel you have developed a good model.]
|Smoothing Constants |MAPE |MAD |MSE |
|0.2, 0.2, 0.2 |50 |543 |1,114,339 |
|0.3, 0.3, 0.3 |49 |543 |1,275,292 |
|0.3, 0.3, 0.5 |42 |485 |1,163,557 |
|0.3, 0.3, 0.6 |40 |464 |1,163,557 |
|0.3, 0.2, 0.6 |38 |458 |1,034,149 |
|0.3, 0.2, 0.7 |38 |447 |1,012,678 |
|0.3, 0.2, 0.8 |39 |441 |999,513 |
|0.35, 0.2, 0.8 |38 |437 |1,004,296 |
|0.35, 0.15, 0.8 |39 |448 |976,360 |
|0.4, 0.15, 0.8 |38 |441 |971,444 |
|0.4, 0.15, 0.9 |39 |435 |958,413 |

We choose the smoothing constants alpha = 0.4, beta = 0.15, gamma = 0.9.

3. [Compare the three models, performance and suitability.]
| |MAD |MSE |MAPE |
|Decomposition |1195 |2,961,515 |200 |
|Multiple Regression |365 |259,819 |86 |
|Exponential Smoothing |435 |958,413 |39 |

Clearly decomposition does not perform well. Multiple regression and exponential smoothing appear better. While regression performs better in terms of MAD and MSE, smoothing is better with respect to MAPE. The fact that the MSE for exponential smoothing is so much larger than that of regression implies that there are quite a few very large errors in the smoothing forecasts. If a goal is to not to make these very large errors, then multiple regression model might be a better option. Otherwise, a good option is to use exponential smoothing (Winter’s Method).

Exercise 7: Comparing Several Movies

Note to instructor: This exercise may be better suited for advanced undergraduate/graduate level students. Answers will be in essay format. It may be appropriate to discuss combining forecasts before assigning this exercise. Similar movies students may consider for comparison might include the Bourne series, the Die Hard series, and the Oceans series.
Business