OLIVER – Extension pack

AN EXCEL PROJECT

Guildford County School has decided to put on a school play – Oliver Twist.

In previous years all the costing was calculated manually. Various businesses were contacted for quotes, decisions were made and supplies were ordered. However, this information was recorded on bits of paper that tended to be misplaced (see sheets)

The Department calculated its total expenditure based on rough estimates – usually from a number of poorly recorded figures and calculated using paper and pen. The department would also hazard a guess about the number of tickets they would need to sell to break-even. This too was done using paper and pen. Such things led to real difficulties and problems.

What the Drama department needs is an easy to use system that records orders, calculates a budget, works out break-even and allows them to experiment with numbers. They just want a system where figures are entered and answers given in an ‘easy to read’ and ‘easy to understand format’.

The Drama Department is able to borrow a computer from the Network Manager with Microsoft Office installed on it, but they are not sure about what exactly they would need.

Part 1: Identify

You have read the background information. Now you must do a write up.

Section A)

  • Open Word, and call it ‘Oliver’.
  • Now create a sub heading called ‘Part 1 Identify’
  • Save it as ‘Oliver Identify’.

Now you must explain:

  • What the Department is trying to do
  • How they normally do it
  • What problems arise from their present way of doing things
  • What they want.

Section B)

You now have to consider how the Drama Departments problems can be solved. Your teacher will discuss some ideas with you.

Now you should:

 Create a sub heading called: ‘Two possible solutions’

 Offer two alternative solutions to the problem

  • Compare the two solutions
  • Conclude which is best

Section C)

Time for the tricky bit! This is where you have to think of 5 system objectives. In other words, you have to decide what your computer system should be able to do in order to resolve the Drama Departments difficulties. You should refer to your theory pack on Excel and think carefully about what this software has to offer. You should also try to make sure your objectives are quantitative (e.g. ‘….it should be able to do this in 2 minutes’)

Have you:

 Created another sub heading called ‘System objectives’

 Given at least 5 system objectives

  • 3 of which are quantitative

Fantastic! You’ve finished!

Part 2: Analyse

Section A)

Just as before this is where you have to consider what hardware and software the Drama Department needs to borrow for your solution.

You also have to explain what security measures and what steps the Department should take to protect the information they will store on the computer.

However, you can save yourself a lot of time and effort by copying and pasting work you have already done in other projects and tailoring it to suit this one!

You should now:

  • Create the main heading ‘Part 2: Analyse’
  • Create a sub heading called ‘System Requirements’
  • Explain what hardware is necessary
  • Explain what software is necessary
  • Explain what security measures are necessary
  • Explain how the system can be backed up

Section B

This bit is easy!

You simply have to list the information that Drama department will have to put into an Excel spreadsheet. Think about what information: figures, prices etc the Department would need in order to create a financial spreadsheet.

  • List of information or data that will be stored into Excel

As well as thinking about what kind of information will go into Excel, you have to think about how you can get that information. In the past two pieces of coursework you created a data capture sheet (or a questionnaire). Is this appropriate for the Drama Department? What would it be used for?

Have you now:

  • Considered how information will be captured
  • Explained how data will be put into the spreadsheet accurately

Section C

Unfortunately, you have come to the tricky bit. You now have to explain how your system will work through the use of a flow chart. Your teacher will give you some key ideas but you need to remember to use the appropriate symbols when you put pen to paper:

Used to show that something is being typed in

Used to show that the computer is working something out

Used to show a printout

Used to show that information is being saved to disk

Used to show on screen display

Have you now:

  • Drawn a system flow chart?
  • Explained your system flow chart?
  • Explained carefully what printouts and output your system will create

Part 3 & 4: Design and Implementation

By the time you finished this next section of your coursework, you have practically finished. But be careful here, this section is worth a lot of marks.

Section A)

  • Open Excel.
  • Create your spreadsheet with the ‘pointers’ below in mind

Pointers:

In order to get the top marks, you will need to create a spreadsheet that accurately records

1) Prices taken from different shops for goods like script, costume etc

2) How much money the department has (e.g. from gifts or last years profit etc) 

3) How much money the department is spending (e.g. on props, sound, costume etc) 

4) How much money will be left over

5) How many tickets the department needs to sell to make a profit

On top of this, your workbook will need

  1. Several sheets (all appropriately named)
  2. Several different types of formulas (addition, multiplication, subtraction, fixed cell references) 
  3. Two functions (the If function (for break-even) and V-lookup)
  4. You should also use ‘Paste Special’, to link spreadsheets together
  5. You should include some sort of graph
  6. You should include validation rules
  7. You should include conditional formatting (to highlight negative values in red etc) 
  8. It might also be a good idea to include a password to protect your workbook 
  9. You should format the workbook so that it has a user friendly interface (so it looks good and important information can be seen quickly and easily) 
  10. Make sure that each of your spreadsheets has an appropriate header and your name in the footer 

Ask your teacher for their opinion on your spreadsheet as you create it. Don’t spend too long formatting it until your teacher has approved your design.

Section B)

Now that you have completely finished your spreadsheets, its time to design it on paper.

You will need several sheets of Excel paper to do this (you can get this from: shared area, Information Technology, GSCE, Oliver)

Hints:

  1. Make sure that your designs shows all the formatting features you have used (e.g. fonts, font sizes, font colours, alignment, bold, underlining, italics, cell colours, borders, page orientation (landscape or portrait) conditional formatting etc)
  2. Make sure you show what formulas and functions you have used and where you have used them.
  3. Show which cells have validation rules and which cells have ‘Paste Special’ links.

Have you now:

Done all your designs on paper?

Section C)

  • Create a new Word document and call it ‘Spreadsheet Implementation’ 
  • You now need to explain how you created each of your sheets. However, don’t make a big job of this. You simply need a few screen shots and some written statements or arrows showing the important features of your spreadsheets and how you created it. It is best to make screen shots that show the formulas you have used. To do this, go to ‘Tools, ‘Options, ‘Formulas’ 

Section D)

Its time to create a Test Plan.

  • Open a new document in Word and call it ‘Spreadsheet Test Plan’
  • Write the title ‘Testing the System’

You now need to create a number of tests to show that your spreadsheet is a good solution to the Drama Departments problems.

  • Create a table with four columns and 6 rows

Test /

Test Data

/ Purpose of Test / Expected Results
Password check / Try to enter open the database without the correct password / To prevent hackers / Excel will block my attempt.

The example shows one good test. It is up to you to create another 5 like these. You should try to base them on your system objectives.

Have you:

  • Created your test plan?
  • Created a document in Word showing screen shots of you performing your tests? 
  • Written a sentence to explain that each test worked as expected?

Part 5: Evaluation

It’s time to say whether your solution was any good

  • Create a Word Document and save it as ‘Spreadsheet Evaluation’
  • Write the title ‘Evaluating my Spreadsheets’
  • Copy and paste your system objectives
  • Under each objective write one or two sentences explaining whether your system managed to do it. 

E.g:

The system must be able to turn information into a clear visual representation so as to help analyse financial data – achievable in under three minutes

The screen shot of the break-even chart demonstrates that financial information can be turned into clear visual representations enabling the user to analyse vital statistics.

Now write a small letter from the Drama Department. It should say one or two things that they liked about your system and one or two suggestions to improve it