Using Excel to design an online statistics/ econometrics assessment

Lory Barile, Warwick University
Published July 2020 as part of the Virtual Symposium on Online Teaching

Introduction

This case study relates to the implementation of a summative statistics/econometrics assessment for an undergraduate level 4 Skills for Economics module to highlight the use of Excel in Economics teaching. The example discussed below is an Excel exercise created for the final examination. To date, a number of changes have been made compared to when the module was initially designed. The main one being the separation of the Mathematics component from Statistics, though the final exam for the Statistics part continues to focus on the use of Excel.   

I will now go on to provide some context for the module before briefly discussing a specific example and finally closing with some remarks on how the assessment could be potentially adapted for transitioning to online teaching and learning (during the current coronavirus pandemic).

Context

Excel was used in the introductory Skills for Economics module at a UK Post 92 University to let students in level 4 appreciate the application of statistical data analysis to practical economic problems and equip them with problem-solving and employability skills.

I will focus here on the Statistics component of the module and provide a discussion about the use of Excel under exam conditions.

The module was taught using a weekly two-hour lecture and one-hour seminar. In addition to a standard set of exercises related to the contents covered in class, each week students also received extra material online (e.g., quizzes, demos and handouts on the use of Excel) to familiarise with the module contents and the software. Two examples of them are attached with this case study (please refer to workbooks 1 & 2). The module started with a lecture on ‘why Excel?’. Using the ‘London Whale’ incident, I explained to students the relevance of knowing how to use Excel to analyse data and how this links with economics and our daily life. The syllabus included classic topics covered in an Introductory Statistics module, spanning from data description (both diagrammatical and numerical) and probability distributions, to one/two sample/s independent t-test and OLS regression analysis for moving to level 5. Students were generally opened to being taught with Excel and appreciated the possibility of acquiring knowledge and applying skills.

The assessment

The module was first introduced in a new combined Economics programme within the Social Science Department. This had the advantage of having only a small number of students enrolled in the course, though the exam setting could be easily adapted to a big class by running it in separate sessions. In its first year of development, 16 students took part to the final exam, which was held in a computer lab. At the beginning of the exam, students received an answer book, exam instructions and a question paper. Candidates were requested to sign in the answer sheet by the end of the examination and hand it in together with the question paper.

The exam was a two-hour computer-based exam worth 50% of the module mark. Students were required to answer all the four questions included in the exam paper. The weight each question carried varied according to the question difficulty levels, and for each question, there were three or four sub-questions depending on the topic.

Once in the computer lab, students logged in to the computers and accessed the Moodle module page, where a folder including all relevant material, including data sets, for the exam was made live by the instructor at the start of the exam. Before reading the question paper, students were requested to open an Excel file to be used to save their answers and wait until the start of the exam was announced.

The exam instructions were then discussed. Students were told to save all results and analysis in a single Excel file with each question clearly labelled. As a tip, I suggested them to use a new spreadsheet for each question answered and rename them accordingly. They were then asked to make sure that the answers on each sheet fitted an A4 (landscape) paper using the print preview. This was done to facilitate printing for moderation procedures after the exam.

Candidates were instructed to save the Excel file using the exam code and the candidate number - i.e., ‘code_candidate number’. I encouraged them to save changes promptly during the exam to avoid losing their work accidentally. I also warned them it was their responsibility at the end of the exam to upload successfully the Excel file with their solutions on Moodle. This was one of the learning outcomes we wanted to assess -i.e., to develop appropriate IT skills and learn how to use appropriate software packages under exam conditions. 

The questions in the exam were all linked to different datasets included in an Excel file located on Moodle under the appropriate exam folder. This file comprised different spreadsheets with raw data necessary to solve the four exercises. To give an example, students were given data on the UK rate of inflation (Inflation, consumer prices – annual %) and the unemployment (Unemployment - % total labour force) between 1989 and 2012. Using Excel, they were then asked to:

  1. Plot the UK inflation rate against the unemployment rate. Comment on the relationship between inflation and unemployment.
  2. Calculate the covariance and correlation coefficients and comment on the association between the variables.
  3. Run a simple linear regression to test the hypothesis that the rate of inflation depends on the rate of unemployment. Write down the equation of their theoretical model. Report the estimated equation and interpret the results -i.e., comment on the sign and value of the coefficients, the statistical significance of the estimated coefficients, and the goodness of fit of estimated equation.

Concluding remarks

Students enjoyed the module and the practical side of it, and they were particularly positive about the extra material provided online to support their learning experience. After the first year, the average mark of the final exam was 51% (with the highest mark being 75%, and the lowest 22%). However, over the years, the module has seen improvement in student performance thanks also to an increase in the GCSE Maths entry requirements (from C to B) for students accessing the course.

There were no challenges around the setting of the exam and monitoring candidates. Whilst this was easier with a small cohort of students and face-to-face interaction, under the current circumstances a better way to prevent students from cheating could be to use a variety of different datasets for each question. This, together with performance assessments that require students to reflect, interpret and comment on their findings, will provide students with similar but not identical assessments.

Readings

Hurtado, P. (2016). The London Whale. Bloomberg Quick Take. Available at: https://www.bloomberg.com/quicktake/the-london-whale

Contributor profiles: