Flexibility of technical content

Because spreadsheet programs are so flexible, they may be used in a variety of ways. Instructors may construct exercises in such a way that students are required to understand and apply mathematical concepts, or the mathematics can be hidden from them. They may be required to learn and use sophisticated spreadsheet programming skills or the work may be done for them.

The spreadsheet program may be regarded as a variable "black box" where the instructor may vary the transparency of the underlying technical tools according to ability, goals and time constraints.

Two dimensions of transparency

Options for assignments along two dimensions of transparency

As Cahill and Kosicki (2001) discuss, the transparency of spreadsheet exercises can be varied in two dimensions: (1) the sophistication of spreadsheet programming skills they require and (2) the level of mathematics they require. The graphic to the right shows four types of assignments that may be constructed. However, the level of transparency along each dimension may be incrementally varied, as described below. In general, the level of transparency may be adjusted by providing various levels of assistance, from nothing (e.g. a blank file) to detailed instructions, to partially or fully completed worksheets, to fully automated worksheets programmed with macros or a programming language. When constructing an assignment, the instructor should keep in mind the trade-offs between time spent on programming or mathematics and the core content of the assignment.

Transparency of spreadsheet programming

While spreadsheets are easier to code than many standard programming languages, setting up a spreadsheet still takes a certain level of know-how, careful scrutiny, and patience. While these may be valuable skills for students to acquire, instructors may not always be able to devote the class time to teach critical skills or debug student work. To make exercises easier to implement, as noted above the instructor may decide to distribute detailed instructions and partially or fully completed files. Another option is to embed the worksheet with pre-programmed macros or Visual BASIC (or other embedded programming language) routines. The set of spreadsheet teaching examples in this module includes applications that require a variety of spreadsheet skills, including spreadsheets that are fully automated. If students will be constructing spreadsheets on their own, Spreadsheets Across the Curriculum provides a number of ready-to-use tutorials with embedded gradable exercises to help students master critical spreadsheet skills.

Options for varying spreadsheet skills in assignments

Mathematical transparency

Spreadsheets allow students to complete a variety of sophisticated mathematical problems, and instructors can vary the required level of student understanding of the mathematics behind the computations. By varying the level of transparency, not only can an instructor allow students interact with models that may be too mathematically rigorous for them, she may creatively employ a discovery approach to learning as students explore the properties of the model or data under consideration.

To make the mathematics transparent, students can be required to carefully set up an algebraic model in a spreadsheet, and then the spreadsheet is used to make otherwise labor-intensive calculations as numerical examples are applied. On the other hand, to turn the exercise into a black box, commands native to the spreadsheet program may produce results automatically. For more elaborate exercises, the instructor may distribute completed or even automated spreadsheets in which students change particular values to observe the impact on a model. Excel contains a number of built-in functions that occupy the middle ground of transparency. For example, Solver can complete optimization problems without requiring students to compute or even recognize first-order conditions, but require students to correctly identify objective functions and constraints.