Section 5: Using spreadsheets for dynamic / time-series modelling
|
The aim of this section is to illustrate how spreadsheets can be applied to modelling dynamic or time-series events in economics and business studies. Also, the aim is to illustrate how a spreadsheet model can be embedded as a central part of an eLearning activity within a Virtual Learning Environment.
|
This section is divided into two parts:
Section 5.1: Developing a spreadsheet model: the cobweb theory
Section 5.2: Embedding a spreadsheet into an eLearning activity
The example focuses on illustrating how spreadsheets can be used to model dynamic or time series events. This involves creating three models to illustrate how the model can be made more realistic.
Section 5.1.1: The Model
The spreadsheet will develop three different dynamic cobweb models:
- simple deterministic model
- deterministic model with more sophisticated supply conditions
- stochastic model with more sophisticated demand conditions
The simple deterministic model is based on the following equations:
- St = a + bPt-1
- Dt = c - dPt
- Dt = St
Where t is the time period, P is the price in that particular time period, b is the price elasticity of supply and d is the price elasticity of demand.
The model implies the quantity supplied is determined by the price in the previous time period. In other words, the producer will set the output level based on what they received previously, because they assume they'll receive the same price. However, the consumer sets quantity demanded using the current price.
A more sophisticated deterministic model can be developed where the supply conditions are expanded. For instance, it could be assumed that the quantity supplied is determined by the price in the two previous years. The new model is:
- St = a + bPt-1 + ePt-2
- Dt = c - dPt
- Dt = St
Where t is the time period, P is the price in that particular time period, b is the price elasticity of supply and d is the price elasticity of demand.
A further development of the simple deterministic model is to make it stochastic, in other words, to include a random element. This can be included to account for demand being determined by non-price factors, such as tastes and fashions. This can be represented as:
- St = a + bPt-1
- Dt = c - dPt + Ut
- Dt = St
Where t is the time period, P is the price in that particular time period, b is the price elasticity of supply, d is the price elasticity of demand and U is a random variable.
Section 5.1.2: Expected outcomes
The expected outcomes will depend on the relationship between the price elasticity of demand and the price elasticity of supply. For instance, the cobweb model can be either divergent (the size of the price and quantity fluctuations increase over time), convergent (the size of the price and quantity fluctuations decrease over time) or stable (the size of the price and quantity fluctuations remain the same over time).
The following table illustrates the relationship:
| Type or Cobweb |
Relationship between the Ped and the Pes |
| Divergent |
Ped < Pes |
| Convergent |
Ped > Pes |
| Stable |
Ped = Pes |
The difference between the deterministic and stochastic models is illustrated by the graphical output. For instance, the path (plot over time) will be smoother for the deterministic model than the stochastic model.
Section 5.1.3: Walk through exercise
The walk through exercise is important as it allows you to check if the spreadsheet model 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. However, a problem with dynamic models is they become too complex to undertake a walk through exercise. Consequently, it is advisable to undertake a walk through using a few time events in a simple deterministic model than is used to construct the more complex models.
Given the input parameters as follows; a = 100, b = 1.2, c = 350, d = 0.8, Pi = 0, e = 0.5, and the equations, the complete walk through exercise grid is:
| Time Period |
Quantity Supplied |
Price |
| 1 |
100.00 |
312.50 |
| 2 |
475.00 |
-156.25 |
| 3 |
-87.50 |
546.88 |
Section 5.1.4: The spreadsheet
The screen shots depict a suggested layout for the spreadsheet. Note the use of named sheets to give instructional information.
- 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 different pages. An important requirement of this sheet is to explain how the Excel comment boxes are to be used.
- The simulation model worksheet contains the actual model. The inclusion of comments within the spreadsheet allows the teacher to include glossary terms and instructions.
- The 'Output - Deterministic 1', 'Output - Deterministic 2' and 'Output - Stochastic' sheets include the numeric and graphical output.
- 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.
Screenshot 1: View of the simulation sheet
Screenshot 2: View of the comments
Screenshot 3: View of the output - simple deterministic model sheet
The spreadsheet illustrates that the market will tend towards an equilibrium point. In this case, given the input characteristics and that the price is initially 0,the market will converge towards an equilibrium position of 183 quantity supplied, and a price of 138.89.
The next set of screen shots highlight the consequences of changing the input parameters, the new values are b = 1.0 and d = 0.6.
Screenshot 4: View of the revised output - simple deterministic model
It is evident that the different price elasticities of demand and supply have changed the market to one which is prone to price fluctuations and represents a dis-equilibrium model.
Section 5.1.5: The spreadsheet formulas and functions
This section outlines the formulas that were used in the spreadsheet. The cell addresses correspond with the spreadsheet illustrated through the previous screen shots.
The first set of formulas being developed are those that create the simple deterministic model.
- C5 = ('The Simulation Model'!C6+('The Simulation Model'!E6*'The Simulation Model'!C8))
- D5 = ('The Simulation Model'!$C$7-'Output - Deterministic 1'!C5)/'The Simulation Model'!$E$7
- C6 = ('The Simulation Model'!$C$6+('The Simulation Model'!$E$6*'Output - Deterministic 1'!D5))
The formulas used in C6 and D5 and copied and pasted into C7:C24 and D6:D24 respectively.
The formula used to identify the type of cobweb (convergent, divergent or stable) includes an 'IF' statement. For instance, if the Ped is greater than the Pes then the model is convergent.
- F6 = IF('The Simulation Model'!E7>'The Simulation Model'!E6,"convergent", IF('The Simulation Model'!E7<'The Simulation Model'!E6,"divergent","stable"))
The second set of formulas that need to be developed are those that create the more sophisticated deterministic model. This model is based on the simple model. The formulas that need to be changed are:
- C7 = ('The Simulation Model'!$C$6+('The Simulation Model'!$E$6*'Output - Deterministic 2'!D6)-('The Simulation Model'!$E$8*'Output - Deterministic 2'!D5))
The formula used in C7 is copied and pasted into C8:C24.
The third set of formulas that need to be developed are those that create the more sophisticated stochastic model. This model is based on the simple model. The formulas that need to be changed are:
- D5 = ('The Simulation Model'!$C$7-'Output - Deterministic 1'!C5+(RAND()*25))/'The Simulation Model'!$E$7
The formula used in D5 is copied and pasted into D6:D24.
The following example discusses how a spreadsheet can be used as the basis of an eLearning activity within a Virtual Learning Environment (VLE). In this case the VLE is Blackboard, however, the VLE is not important, it is the use of the VLE tools to meet a pre-determined learning objective that is important.
The example uses an elearning design activity sheet. The comments within the sheet record some of the lesson planners ideas. It illustrates how the learning outcomes can be improved through using some of the VLE tools to overcome the limitations of the spreadsheet as a learning aid.
| eLearning design activity sheet |
|
Clearly state the aim of the activity.
The aim of this activity is to find an alternative approach (instead of a lecture and workbook) for students to be able to develop their understanding of the cobweb theory.
|
|
State the intended learning outcome(s) of the activity.
- The student understands the cobweb theory.
- The student understands how the relationship between the price elasticity of demand and the price elasticity of supply will influence the likely characteristics of price fluctuations within the market.
- The student should be able to assess if the cobweb theory is a realistic model to explain price fluctuations in some actual markets.
Specifically, the student will be able to:
- Acquire knowledge of the cobweb theory.
- Read, manipulate and interpret data. The manipulation of data will focus on multiplication and division. The data will be interpreted in terms of the size of a variable being determined by the relationship between two other variables.
- Apply previously taught theories, in particular, the supply and demand model, and a selection of elasticity theories.
- Demonstrate critical thinking with respect to the discussion of how applicable the cobweb theory is to explaining actual market price patterns.
|
|
Describe the activity (what the educator and the student actually do).
The educator:
Will develop the material and monitor the discussion board. There maybe a requirement to participate in the discussion board to promote discussion, and maintain 'netiquette'. The educator is expected to summarise the discussion board after a set time period.
The student:
The student will have two tasks to complete. The first is an online assessment that requires that they have read the background information on the cobweb theory, and have completed the tasks within the spreadsheet model. The test will require them to manipulate data (division calculations), and then interpret their answers.
The second task is to actively participate in a discussion board forum that focuses on discussing how applicable the cobweb theory is to the real world. The students are expected to read the recommended supporting documents.
|
|
How / why will students be motivated to complete the activity?
The participation in the discussion board will be assessed as homework. The educator will review all contributions and assess them on the development of knowledge, development of arguments and critical thinking.
|
|
How will the students be directed through the activity?
Students will undertake this activity as an individual exercise in the IT labs over a set time period. Therefore, the activity will be, primarily, instructional information with hints to account for the differences within the group with respect to numeric and communication skills. There will also be an email address and dedicated discussion board supplied for students who experience specific problems.
|
|
What is the envisaged timescale of the activity?
The activity will be given a 1 hour classtime slot. This will be adequate for the first task involving reading the information, using the spreadsheet and completing the online assessment. Students will be advised to spend the same amount of time on the discussion activity as they would on an essay.
|
|
Identify the separate elements of the activity in terms of VLE functions.
Announcement: Make an announcement of the activity with a link to the appropriate folder within the course documents.
Course documents: The following items will need to be created
- Item 1: Instructions - this will include a link to the Biz/ed glossary search box which has been uploaded to the additional resources folder in the course documents section.
- Item 2: Spreadsheet activity - this will include the links to the spreadsheet, the online assessment and references to the appropriate areas of the text book. It will include instructions about how the student needs to navigate between the two pages, and hints on how to complete the required calculations.
- Item 3: Discussion board activity - this will include links to the appropriate discussion board forum, links to supporting content and links to the 'student help guide' for advice on using discussion boards.
Online assessment: This will be an objective test of 5 questions. The test will focus on both lower and higher order skills, knowledge, data manipulation and interpretation. The test will be formative as students will automatically get feedback after the test and be directed to additional reading material. They can also ask for further explanation through the discussion board.
Discussion board: The discussion board will contain the forum and suggested threads to initially stimulate discussion. At the end of the discussion time period the lecturer will upload a document that summarises the discussion and draws some conclusions in terms of the original threads.
|
|
What resources will you need to locate or create for this activity?
Spreadsheet model: A simple deterministic cobweb model (CREATE).
Introduction to the cobweb theory: write an introduction that contextualises the idea and makes the principle accessible to students who have weak analytical skills (CREATE).
Further reading: Need to find two articles that illustrate the principle of the cobweb theory. Also include links to data sources on price fluctuations for certain markets (LOCATE).
Online assessment: Make a 5 question online assessment which includes the student calculating the difference in price between the first and last period. The emphasis of the test will focus on the idea that the type of cobweb (convergent, divergent, stable) is related to the relationship between the price elasticities. The feedback should include links to the relevant sections of a textbook and the 'introduction to the cobweb theory' document (CREATE).
Discussion board: The discussion board forum will include three threads that relate to the data response question in the textbook (LOCATE). At the end of the time period a document will be written that summarises the discussion and includes further tutor comments.
Usability survey: An online usability survey will be conducted at the end of the eLearning activity. This survey is designed to highlight any problems with the instructional information, navigation or access to the material.
|
|
How will you know whether the activity has been successful?
The activity is being assessed in terms that the discussion board is counting as their homework.
The grade book will be reviewed to see if students tended to get the questions correct. Depending on the group performance a document may be written that further explains the ideas being assessed.
The usability survey and technical problems raised through the discussion board will give an indication of the success of the activity.
|
Welcome | Previous Section | Next Section
|
