Task 4 - The Disco Project

Now that you have developed your spreadsheet skills, you can put together your own model. There is nothing to download for this task - you will design and create your own spreadsheet model from scratch. This is a level 5 modelling task.

The School Disco - Can we do it??

A committee of students has been formed to set up, and run, a school disco. They have asked you, as the resident spreadsheet wizard, to model the finances for the event. They need to know whether or not the event will make a profit.

Make sure you follow all the instructions on this page carefully and that you keep all the necessary printouts. Your name and form should be in the header of each printed page.

Stage 1 - The Basics

Create a spreadsheet that contains the following information (the way you set it out and format it is up to you):

• 150 people will be attending the disco
• Tickets are £2.50 each
• Everyone attending the disco will buy one drink of orange squash and will pay 50p for each drink
• Each drink of orange squash costs 20p to produce.
• The audio equipment costs £200 to rent for the night.
• The DJ charges £100 for the night

Make sure that you use formulae properly as we will be making changes later. You should avoid using numbers in your formulae. If you use cell references instead, you can change the cells and see the results of your formulae changing.

Show clearly on your spreadsheet whether the disco is predicted to make a profit or a loss.

Print two copies of your spreadsheet. The first will show the results and the second should show the formulae you used (Tools... Options...View... Formulas...).

Stage 2 - Developing the Model

You are now going to develop three different scenarios or situations. If the disco is held on a Monday, they expect to see 100 people attending. If it is on a Wednesday, they expect 150 and if it is on Friday, they expect 200.

Your spreadsheet should show, at the same time, the profit that would be made on each day so that the committee can make a comparison.

Next, it was decided to ask teachers to supervise the disco. The teachers will be paid £10 each. It was agreed that one teacher would be needed for every 50 pupils attending the disco. Add this to your spreadsheet, using formulae.

Finally, the committee decided to offer one free drink to each person. As a result, it is no longer expected that everyone will buy a drink. Instead, only 50% of people will buy orange squash (60% on a Friday) . Make the necessary changes to your spreadsheet.

Again, print two copies of your spreadsheet, one with the formulae. If you have changed your formulae, you are working towards level 6.

Stage 3 - Presentation

Use Word to write a letter to the Chairman of the Disco Committee explaining the following:

• The benefits of using a spreadsheet to model the finances (what can you do with it that you couldn't do so easily with other tools?)
• Information about the costs and income that the disco will face and the profit that it will make on each day (include at lest one useful graph). The graph can be copied and pasted from Excel into Word.
• Your thoughts on what the committee should do. Which day should they choose? Should they change their drinks plan? Explain your opinons.
• Your thoughts about how realistic the model is - do you think this will happen? Explain yourself.