Spreadsheet programs provide a wonderfully powerful and flexible tool for exploring and manipulating data and models. They can enhance learning and presentation of nearly any topic that contains data, and may be used flexibly in a
variety of assignments and settings. In addition, assignments may be constructed to teach or mask spreadsheet skills, or to require or obscure mathematical skills. As such, spreadsheets can be used flexibly as a transparent tool for enhancing technical skills or as a black box that allows for intuitive concepts to be explored. See more about
using spreadsheets flexibly to handle technical content.
However, the successful implementation of an application requires some careful choices and some preparation. In addition, there are some pitfalls instructors should be careful to avoid. See some
tips and cautions.
Six steps to formulate a successful spreadsheet example
1. Find an appropriate topic
The first step is to identify course content that can be more effectively taught with spreadsheets.
- Data topics
- Can patterns be revealed with data analysis and plotting?
- Can something be learned from manipulating the data?
- Is the data set so large that a spreadsheet is needed to handle it efficiently?
- Modeling topics
- Are numerical examples cumbersome to compute by hand?
- Can properties of the model be explored or illuminated through analyzing numerical examples?
- Are there spreadsheet tools that can be used to manipulate or solve the model?
This is a simple example for an introductory economics course.
Detailed instructions can be found here. From 1949-1972, real GDP grew at a rate of about 4.1% per year. From 1973-2009, the economy grew at a rate of 3% per year. A natural question to ask is, "How large would the economy have been if over this time if it continued growing at 4.1%?" Or, "Does a 1.1% drop in growth rate really make a difference?" Because this is a quantitative problem that requires a large number of computations, it is a natural spreadsheet assignment.
2. Choose the type of assignment and setting
Spreadsheets may be integrated with a variety of pedagogies.
See more about types of assignments and settings
This assignment may be used as a short lab class where students discover the importance of compounding growth rates. Alternatively, this can be a homework assignment where students bring results to class for discussion. Or, the instructor may make the calculations herself for presentation to class.
3. Determine the time to devoted to learning spreadsheet and mathematics skills
Spreadsheet skills may take considerable time to teach to students. Likewise, the mathematics underlying a spreadsheet model may be extensive. The instructor must decide how much time to devote to technical matters, and assignments may be tailored to demand varying degrees of student involvement in spreadsheet construction and mathematics.
See more about tailoring assignments to student technical skills
See another SERC site that teaches spreadsheet construction
If students have some mathematics and spreadsheet skills, they may be asked to simply compute the imputed GDP series, given a starting value and the actual GDP data distributed on a spreadsheet. Otherwise, the assignment may be presented in a variety of ways, depending on learning goals and time constraints.
- If students should learn the mathematics of compound growth, then students can be taught the mathematical concept, instructed on how to create a spreadsheet formula (as described below), and asked to complete the exercise.
- If students are not expected to learn the mathematics, the Fill command (described below) is easier to implement than typing in and copying a formula.
- In either case, students may be given hands-on instruction in a computer laboratory, detailed written instructions, or limited instructions depending on needs and goals.
4. Select appropriate spreadsheet tools
Excel and other spreadsheet programs offer a variety of plotting, analysis, modeling and calculation tools.
A spreadsheet should be set up with years in the first column, and the actual GDP data for 1972-2009 in the second column. The third column will contain the imputed "what if the economy grew at 4.1%" data but needs to start with the original 1972-2009 data. The first row contains data labels. The calculations to impute GDP at the higher growth rate may be performed a few different ways, depending on learning goals.
- Higher math skills option: Starting with the real GDP in 1972 in cell C2 (4,647.7 chain-weighted 2005 dollars), you can calculate the projected data by writing a formula that will increase the previous year's value by 4.1% (e.g. "=C2*(1+0.041)") and copy and paste the formula down the rows through 2009. This requires some mathematical skills. The compounding can be checked by recomputing the 2009 value with the formula for compounding for 37 years "=C2*(1+0.041)^37".
- Lower math skills option: To obscure the mathematics, you can project a data series using the "Fill" command. (See how to use the Fill command) Open the Fill dialog box, and choose a "Growth" series with a "step value" of 1.041. (See image of dialog box on the right, click to enlarge.) This will create a series automatically without needing any knowledge of mathematics.
5. Write or present the assignment carefully
As a computer program, spreadsheets are subject to the "garbage in - garbage out" phenomenon. Small errors may result in the assignment failing to work, leading to lost time and frustration. As a result, it is important to provide students with very clear instructions, training in spreadsheet skills, or partially or fully completed spreadsheets.
See some tips and cautions
The Examples page provides a list of tested activities with detailed instructions or automation, with directions on how they may be successfully implemented.
- When writing formulas, students may forget to begin with an equal sign or use an asterisk for multiplication.
- The "Fill" command is difficult to find on Excel 2007 and later.
- Students may not select "Growth" or type in "1.041" for "Step value".
- Students may not select the proper cells rows for the data to appear.
6. Assess results
While at first it may seem that giving students access to computers complicates assessment, in fact spreadsheet exercises may make it easier to judge learning in some contexts.
- Tailor assignments
- It is often straightforward to tailor assignments for particular students or groups to target learning objectives or prevent cheating:
- Assign different time periods of data
- Vary parameter values, model specifications
- Flexibility of media
- Assignments can be handed in as hard copies or electronic files
- Platforms like Blackboard and Moodle allow files to be submitted electronically
- Annotating files
- Spreadsheets may be annotated with the "comment" tool* so students can be required to explain why they constructed a worksheet in a particular way or elaborate on a cell's contents.
- *Right-click on a cell, and select Insert Comment. This is also useful for group collaboration to track the contributions of group members.
- Comments may also be used for assessment when spreadsheets are submitted and graded electronically.
- Let Excel grade for you
- Wetzel and Whicker (2007) present a way for spreadsheets to essentially grade themselves. By inserting password-protected hidden cells with logical operators, the spreadsheet can report if a student has obtained the correct result in specified cells.
Students may be asked to hand in a completed worksheet along with answers to some discussion questions that seek to assess whether the students understand the results. In addition, students may be asked to plot results or make additional computations, like "what is the percent difference between the actual and imputed GDP". To avoid cheating on a graded assignment, it may be completed in a monitored laboratory session, or students may be assigned slightly different growth rates.
Show some types of content
Hide
