I have been desperate to have a go myself but did not want to use the tool just because it was there. Recently I worked with a number of phase three groups to develop tasks around modeling with spreadsheets. We have introduced the tool's basics and explored the insertion of formula building simple models from the ground up, now we are approaching that point where we need to use and apply the skills we have been working on to begin developing a model of our own.
So her goes with the hmming and hahing... I have been thinking about the idea of collaboratively planning a party. Quite a common project you might say, but have wondered about taking the tack because of the student's ages of planning an evening out, perhaps beginning at the cinema, going bowling, skating as examples and following this with a visit to for example McDonalds.
In pondering this project I have several assumption
- We will be including everyone in the group,
- We will have to prebook and order because of numbers
- We will need to make sure that the event is affordable.
Surveying and collecting ideas for the pre-meal event seems a little easier to manage with a simple vote being possible, while choosing food from the menu presents more of a challenge with so many potential variables to consider. In my mind I have images of the chaos that could ensue from students collecting and collating a common data set, so my big question here is how to engage everyone in the process while quickly putting all the information in one place for everyone to access quickly. Here is where my first real collaborative adventure with Google Docs would come into play, using Google Forms to collect the initial data we would need to begin the project.
Step one suggesting and voting on a pre meal event
Setting the scene with a brief to provide the big picture would be my starting point.
We have been asked to plan and prepare an after school activity to celebrate...............? The activity will involve us all meeting up to do something for example watching a film, going bowling etc that will be followed by something to eat before we all return to school and go our separate ways.
a However we must agree as a group on the something that I was thinking that we could create a class Google form based simply on suggested venues from the students accepting and adding all suggestions. Having saved this we might distribute the link to the whole class by email or through the VLE with their votes being added to the associated spreadsheet. With all votes in the top three venues could be highlighted, and web based research carried out to identify the possible costs of a visit to each venue.
Step Two choosing our menu
Step three Collating information
Using data collected the students could begin to use it compile frequency tables tallying and recording first and second choice meals, that they would then be used to populate a prepared template that would form the basis for the final spreadsheet model.
Step four: Inserting variables to the spreadsheet model
The Excel template provided for this activity would include prepared worksheets
a menu showing current McDonalds' prices
an order form
- a cost calculator for each set of choices
- A comparison book that we could use to compare each meal option cost with the inclusion of the pre meal event included
- A final cost calculator that would help us to decide how we want to divi up the cost of the event and that I hope would allow the students to see the value of using a tool like this when considering and planning events such as this.
The students would be encouraged initialy to enter the variables, the data we had collected together into the relevant worksheets eg
- food items
- cost of one unit
- number of orders
This would be repeated for first and second choice meals
Step Four: Modeling the creation of rules
The application of formulae to the table for meal choice one would be modelled and then carried out as a class. Asking the children to describe firstly to each other and then to the class the calculation we would need to use to if we wanted to work out the cost of say 15 cheeseburgers at a cost of £0.99 each
The calculatiuon would look something like this
15 x £0.99 =
To allow the spreadsheet to do this calculation we would expect to...
input =15*0.99
However we want to be able to change our variables and allow the spreadsheet to be able to update automatically, or without us having to input each change individually so.. how have we done this previously? By using cell references to help
This process was remodeled eg =cref1*cref2 and students asked to complete the task for the remainder of the total cost cells, reminding after three or four cells that we could auto-complete using drag and fill.
Finally a rule would be added to find the total cost of the meal using autosum.
With this aspect of the model complete the students would then repeat the process independently with the second choice meal.
Stage 5: Comparing costs.
Using copy and paste the contents of the two options sheets can be moved to the comparison sheet, and a new set of cells added to calculate the meals combined with the pre meal event. Suggestions could be sought as to the calculation and formulae we would need to use in order to find the total cost of our meal and the cost of our visit.
the cost of the meal + the cost of the event = total
Using cell references a formula would be added to support rules allow each table to factor in and compare possibilities that included the new variable, the cost of the pre meal activity.
=cellref1+cellref2
With this complete we could begin identifying not only the cheapest meal option but also begin thinking about the effect that choices of venue for the pre meal event would have.
Stage 6: Using the Model to Support Making Choices
Just how good a model is this?
How robust, fair and useful is it in helping us to plan and make decisions?
Are there any changes we need to make to the model's design and what might these be if we are to make the cost of the event fair to all of the participants?
In making choices about the event as a whole I have made a number of assumptions that we need to consider as a group that should ultimately lead us back to thinking about the design of the model itself and how it works and how effective it has been in supporting our decision making process.
The most contentious I hope is that everyone should pay the same amount on the night regardless of choices they made in the vote in order that the collection of monies be made easier. It will be interesting to see what the students have to say about this in terms of fairness, however as a starting point it opens a number of interesting discussion points around the validity of the model in relation to an outcome and purpose not shared at the beginning and how this might impact on our starting point. The concept of fairness, also gives a personalised in for students to to begin thinking about and suggesting how the essentially sound principles of the model itself might be adapted inorder to make cost distribution fairer. Eg should we have surveyed at the beginning the most popular meal choices, and then limited the order around these to balance the costs.This has been an interesting thought experiment... Any thoughts?
