Skip to content

Guest Post: Productivity, Forecasting and Excel with Real Data

June 8, 2017

Our Guest Post today comes from Howard Weiss, who is Professor of Operations Management at Temple University. Howard has developed both POM for Windows and Excel OM for our text.

I often search the web for real data that I can use for forecasting, and just came across data from Lowes 10 – Year Financial Information report that can be used for both productivity and forecasting.

The report has 5 sections, with 2 that are of major interest to OM. The 1st is titled “Stores and People” and lists the productivity inputs and outputs of: (1)Number of stores; (2)Selling square feet; (3)Number of employees; (4)Total customer transactions; (5)Average ticket.

The next section includes the net sales. I have the students perform several exercises using these data. Here are 5 years of past data.

The Exercises

Exercise 1 – Data integrity:  For each of the years the net sales should be equal to the anticipated net sales (my definition) given by the average ticket multiplied by the number of transactions. Of course, the anticipated and reported net sales are not exactly equal. I ask the students to compute the percentage difference between the reported net sales and the anticipated net sales and also to determine the MAPE differences.

Exercise 2 – Productivity:  For each year, there are 3 productivity measures that can be computed comparing net sales to number of stores, selling square feet and number of employees. Unfortunately, there are no multipliers available to compute the multifactor productivity measures for the 10 years.

Exercise 3 – Productivity change: For all years except the first, I ask the students to compute the productivity change for each of the 3 productivity measures. There is one small issue the students need to recognize. The data is given as most recent first.

Exercise 4 –Graph in Excel: I ask the students to graph the 3 sets of productivity measures. If the students create scatter graphs using the dates in row 3 and the productivity measures that they create in a row below the data then the graph will be fine. If the students create a line graph using only the computed productivity measures then the graph will run backwards. That is, the time axis will be backwards. This is important for the final exercise.

Exercise 5 – Regression/Trend Line – I ask the students to draw a regression/trend line for each of the three measures. I have shown my students that right-clicking on the graph is the easiest way to create the line. I also ask the students to find the three average productivity changes based on the slope of the line in each of the three graphs.

The students very much appreciate applying Productivity to real data, using data that has more than 2 periods and having the opportunity to work in Excel, especially with the graphing capability and regression capability within the graph option.

3 Comments leave one →
  1. permalink
    June 8, 2017 4:43 pm

    Thanks for sharing, Do you Have excel files for the original data and results available for use?Thanks. Javad Seyed, Ph.D.

  2. June 9, 2017 11:07 am

    I have posted the Excel file I will use this coming Fall at

  3. June 9, 2017 6:54 pm

    Thanks for your post, Howard! These are the type of quick Excel skills that all managers in the real world should be able to apply to data just like you have shown. This looks like a great way to reinforce and practice those skills…Chuck Munson

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Supply Chain Management Research

Andreas Wieland’s supply chain management blog for academics and managers

better operations

Thoughts on continuous improvement: from TPS to XPS

%d bloggers like this: