Advanced Search

Section 3: Using spreadsheets to analyse data

The intention of this section is not to create an exhaustive list of all the functions that can be used for data analysis within a spreadsheet, but to illustrate the principle and highlight how the approach can add value to the learning experience. This section is divided into:

Section 3.1: Developing a spreadsheet model for time series data
Section 3.2: Mapping learning objectives to a spreadsheet exercise

Section 3.1: Developing a spreadsheet model for time series data

The learning objective in the following example is to make sure that the student is aware of the UK import pattern, the factors that might determine this pattern and what is likely to happen in the future.

To help meet these aims, a lesson can be designed around a spreadsheet simulation where students are given data from different time periods for the UK, and need to answer the following questions:

1. What is the relationship between the level of imports in the UK and the level of national income or the exchange rate?
2. What will be the level of national income and imports for the UK in 2040?

The aim is to use a spreadsheet simulation to allow students to enter time series data and interpret the outcomes.

Section 3.1.1: Background to the spreadsheet

It is expected that the student will be able to enter the data in the spreadsheet to produce a numeric and graphical output. In this particular case the output is interpreted by the student to assess the importance of the theory.

The numeric output includes:

1. Annual average growth rates of imports, exchange rate and national income
2. Correlation coefficients between level of imports, national income and exchange rate
3. Forecast for level of imports and national income in 2040

The graphical output includes:

1. Plot of imports, national income over time
2. Plot of annual growth rates in imports and exchange rate, and imports and national income

Section 3.1.2: Walk-through exercise

The walk-through exercise is important as it allows you to check if the spreadsheet simulation is behaving properly. The aim is to make sure that the output from the spreadsheet corresponds with the output from the walk-through exercise. In other words, it acts as a safety check, if the two do not correspond then an error has occurred in either the spreadsheet or your calculations.

Given the following input parameters a selection of outputs should be as follows:

Annual Percentage Growth Rates (%)

Conversion of Imports into £ constant prices

Section 3.1.3: The Spreadsheet

The following screen shots depict a suggested layout for the spreadsheet. Note the use of the named sheets to give instructional information.

1. The introduction sheet gives the aims of the spreadsheet activity, the sequence of tasks that you wish the student to undertake and a description of what information is on the individual worksheets. An important requirement of this sheet is to explain how the student can use the Excel comment boxes which contain a considerable amount of instructional information.
2. The simulation model worksheet contains the input area for students. The inclusion of comments within the spreadsheet allows the teacher to include glossary terms and instructions.
3. The 'output - numeric' and 'output - graphs' worksheets contain the results of the data analysis.
4. The additional information sheet could be used by the teacher to embed the spreadsheet activity within other resources, for instance, linking to relevant articles on the Web, or a discussion board.
5. The working area contains the workings of the spreadsheet model. It should have clear warnings for users not to change anything on this page unless requested by the teacher.

Screenshot 1: View of the simulation model

Screenshot 2: View of the simulation model with comment boxes

Screenshot 3: View of 'Output - Numeric' with data set added

Screenshot 4: View of 'Output - Graphs' with data set added

Screenshot 5: View of the working area

Section 3.1.4: The spreadsheet formulas and functions

This section outlines how to build the spreadsheet. The cell references and worksheet names refer to those depicted in the screen shots.

The first task is to convert the data into Imports in £ and constant prices. This manipulation is undertaken in the working area (see screenshot 5).

It is a two step process, firstly involving the conversion to sterling and then converting to real values (constant prices):

• C4 =('The Simulation Model'!C7/'The Simulation Model'!D7)
• D4 =(C4/'The Simulation Model'!F7)*100

The next step is to calculate the annual growth rates. These take information from the simulation model (exchange rates and national income) and from the working area (imports).

• C7 =(('The Simulation Model'!E8-'The Simulation Model'!E7)/'The Simulation Model'!E7)*100
• D7 =(('Working Area'!D5-'Working Area'!D4)/'Working Area'!D4)*100
• E7 =(('The Simulation Model'!D8-'The Simulation Model'!D7)/'The Simulation Model'!D7)*100

The following formula can be used to automatically create the time period:

• B7 =CONCATENATE('The Simulation Model'!B8,-'The Simulation Model'!B7)

The following formulas are used to calculate the forecast for imports and national income by 2040:

• H17 = FORECAST('Working Area'!B25,'Working Area'!D4:D21,'Working Area'!B4:B21)
• H18 = FORECAST('Working Area'!B25,'The Simulation Model'!E7:E24,'Working Area'!B4:B21)

To calculate the multiple increase requires the following formulas:

• I17 = (H17/'Working Area'!D21)
• I18 = (H18/'The Simulation Model'!E24)

The calculation of the correlation coefficient, the sample size and the significance level requires:

• H7 =CORREL('Working Area'!D4:D21,'The Simulation Model'!D7:D24)
• H8 =CORREL('Working Area'!D4:D21,'The Simulation Model'!E7:E24)

The formulas used for the time lag are:

• H10 =CORREL('Working Area'!D5:D21,'The Simulation Model'!D7:D23)
• H11 =CORREL('Working Area'!D5:D21,'The Simulation Model'!E7:E23)

The calculation of the sample size uses the 'Count' function.

• J7 =COUNT(C7:C23)

The calculation of the significance level involves a two step process, firstly the calculation of a t-test value, then a comparison with the book value.

The first step is undertaken in the Working Area.

• C28 ='Output - Numeric'!H7*(SQRT(('Output - Numeric'!J7-2)/(1-('Output - Numeric'!H7^2))))
• D28 =ABS(C28)

The comparison with the book value is undertaken in the numeric output page. This uses an 'If' statement that tests if the book value is less than the calculated value, then the correlation is significant, otherwise, it's not.

• I7 =IF('Working Area'!$F$5<'Working Area'!D28,"yes","no")

Section 3.1.5: Improving the degree of sophistication

It could be argued that this spreadsheet model has little scope for radical improvement due to the constraints of the dataset. However, small changes can be made, for instance allowing the user to set the forecast date and the inclusion of the t-book value data table.

Section 3.1.6: Spreadsheet features for cross sectional data analysis

The analysis of cross sectional data, for instance, comparison of spatial location of UK firms by region, or the UK regional house price index, can readily be analysed by spreadsheets. Simply follow the same principles as the time series, and apply the statistical functions available through the spreadsheet program.

Section 3.2: Mapping learning objectives to a spreadsheet exercise

The aim of this section is not to build a spreadsheet but to consider the issues involved in achieving the learning objective through the use of the spreadsheet.

The learning objective under consideration is that a student should:

• Be able to describe the spatial location (region) of economic activity in the UK in 2000 by different sector (primary, secondary, tertiary and quaternary).
• Describe how this pattern has changed over the last 40 years.

The learning objective requires that students should be able to describe cross sectional patterns. It is also evident that the teacher needs to assess if the students have achieved the learning objective.

This type of learning objective suits the characteristics of a spreadsheet, in that a spreadsheet can quickly analyse the data and create numeric and graphical output that the student can interpret. The learning objective may be assessed by the teacher through getting individuals to present their findings to the class. Hence, the teacher can divide the class into small groups who have responsibility for a specific sector. They collect the raw data, input it into a spreadsheet model (developed by the teacher) and then interpret the results. The use of the spreadsheet can enrich the amount of information the student has to describe in terms of the static and dynamic pattern, while also allowing for individual creativity as students can run their own statistical tests on the data.

The following scopes out a framework for a potential spreadsheet model. Assume that the data is collected by sector for the UK regions.

To enhance the effectiveness of this activity it is important to contextualise the variables, for instance, the inclusion of relevant newspaper articles, and also cross-reference with the variables that are outlined in the course specification.

Welcome | Previous Section | Next Section