# Guest Post: Productivity, Forecasting and Excel with Real Data

*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.

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

I have posted the Excel file I will use this coming Fall at https://astro.temple.edu/~hweiss/lowes/

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