Introduction

Every year, the Chafford School has a school prom. This year however, the Prom committee has decided that that they want the cheapest and most efficient hall, catering, decorations and music (a D.J) from previous years. Nevertheless they have encountered numerous problems.

Problems

To find out more about problems, that the prom committee was suffering from. I decided to interrogate a member of the prom committee to find out problems that they were suffering from, so I then could begin to lighten their problems and further their achievement and arrival of the prom (Appendix A).

* They would like to have a variety of choices, but unfortunately due to the low budget they haven’t got a choice, and up to yet it looks like they are going to have to use last years hall

* At the moment only one student has access to the information, which cause a problem when she is not in. Also other pupils only get to see the information when at meetings. The problem is, not enough members of the prom committee have access to the information, so they cannot further their research in their department of the prom.

* Ticket prices haven’t been decided yet, the reason for this is we haven’t found out a price for the factors affecting the prom (hall, catering, decorations, and music). This is difficult because the sums are going to have to be repeated to find the cheapest possible combination of the most efficient places, and firms.

* There are 10 people on the committee and for the time being there are 10 tasks. The committee carries out the tasks in pairs, which means at least 2 tasks for each person. The problem with this is that they could have more people on the committee that could help others with different tasks. Another problem is only 1 person is in charge of communicating between pupils and the committee; this again needs another person to help them with this as one person is never enough.

* They needed to find out what factors they are going to have and again they need to find out the positive number of pupils going. This is very much impossible because they have not figured out where to start with the problems themselves.

* The calculations that are carried out are the total cost of the factors divided by the definite number of pupils. This causes a problem because if anything changes they will have to repeat the whole lot of sums over and over and over again. However, they have asked for a deposit of 5.00 from each pupil that feels that he/she will go. So it helps them out a little. Furthermore it will give them a little more money so they can continue their research for more venues, catering firms, music and decorations etc. They can try and get previous problems sorted as well.

* They can’t use it next year because at the moment it is all on paper

Task to be done

After finding out what the problems were, I needed to find out what they wanted done to help you. So I asked members of the committee what they needed to be done to help them. In reply I found out that

* To be able to model various scenarios in order to work out break even point

* To be able to let pupils know the final price of an individual ticket as soon as possible, so they can then begin to save for things such as limousines and tuxedos.

* A system that when research is done allows mechanical calculations to be done in a couple of minutes.

* A system that can be reused for next years pupils. So that these problems won’t be repeated.

* An efficient way of storing information that can be easily established, so that they can ease the workload of their tasks

* A method of collecting data that lessens the opportunities for mistakes to be made

* A system that automatically works out the breakeven point and profit loss made based on data input

* A system that can predict ticket prices and the breakeven point based on approximate numbers of pupils that are attending.

* A system that can store information semipermanently for following years

* A ready made system that can give access to the proms committee anytime, but that can secure pieces of work with passwords.

Analysis

Volume of data

Factor

Number

Price 1

Price 2

Price 3

Price 4

Price 5

No. of pupils

180 pupils

Catering

4 firms

300

400

150

150

Decoration

3 firms

125

00

175

Music

4 firms

175

100

00

175

Venue

3 halls

00

50

300

Collecting the cost of the tickets

They needed to find out how much each ticket cost. To do this they would always have to change the sums changing each factor’s (venue, catering, music and decoration) price each time. To find out the efficient price that they would need. Efficient doesn’t always mean the cheapest though. The way that they would collect data for the price of tickets is as shown on the next page. Furthermore another problem would be that an employee would write it by hand and could write a 3 instead of a 5. This means that the whole sums system would be changed and it would be wrong. An alternative procedure of recording the figures from different firms is to use a data capture form. A data capture form is an official way of recording facts. It is a form that can be filled in with single word answers and at certain places tick boxes. Then the form would end up easier to read and maybe have commands at certain places. It would be used with such things as an estate agents job finding houses etc. This would make this an easy way of then entering the data into a spreadsheet, so 0we then could begin to work out the total cost of tickets per head.

Processing data/calculations

The current state of the system is absurd, the calculations are repeated by hand and unfortunately most of the time this ends up to be inaccurate. The process is extremely slow and involves a lot of hard work. Furthermore the process has to be repeated over and over again e.g.

Catering

Decorations

Music

Venue

Firm 1190

Firm 1125

Firm 1175

Firm 150

Firm 2175

+

Firm 200

+

Firm 2100

+

Firm 200

Firm 3150

Firm 3175

Firm 300

Firm 3300

Firm 4150

Firm 4175

This would end up in a complete mess, because you would have to add up a catering firm with a decoration firm, then add it to music and then finally to the venue. Changing one firm of a cost each time. This is of course assuming there is going to be 200 people coming. An expensive way of solving the problem would be to use a specialist program that would work out the sums automatically. However the program would have to be designed which costs a lot of money. An alternative method would be to use a school computer and the Microsoft package, Excel. This program is designed to deal with working out numbers and problems as long as it has the right information and is given the correct command.

Timetable for project

October

November

December

January

Feasibility Study

An alternative method would be to use a software package. This is going to be effortless to do because we already have a school computer network that we can use. There are programs such as Access, Publisher and Excel. For this situation it would be a better idea to use Excel as explained in the previous page because it was made to deal with numbers and problems. Furthermore it can do this within a click of a button after entering information as long as the right commands were given.

Publisher would also be a good program to use because it helps you create brochures; leaflets and also data capture forms. These would be the methods that I would personally choose for the prom committee. Since it doesn’t cost anything to build or run, as it is on the schools network. This would also be an advantage because pupils in years to follow could use the same system. The only disadvantage would be that you would need someone who could work the programs, although most pupils have had ICT lessons and would know straight away how to use it. If they didn’t then another person or I who knew how to use it would teach the prom committee how to use it by giving them a demonstration. A better way of solving this would be to create a user manual. Then they would be able to take over, and the system would run better.

Specification for the computers at the school

> Pentium 3 733Mhz

> 10 GB Hard Disk Drive

> 128MB SDRAM

Specification for the network server

> SCSI Pentium 3 1Ghz

> 8 GB Hard Disk Drive

> 512 MB SDRAM

Design

At this stage, I will clarify and justify why my spreadsheets appear as they do. It is not only just how the information is put across, it is also important how it is shown.

As seen below, I have carefully chosen certain factors that I have chosen. In the image it is clear to see how everything is laid out. The first factor that comes to mind is the font. I decided to choose three different fonts. These are Baskerville old face, Arial and Bell MT. For most of the spreadsheet I used Baskerville old face however I used Bell MT for extra information and Arial for headings, to make these stand out from the rest. The sizes of these fonts also have an affect on the appearance the heading fonts were the largest at size 14, then I just worked my way down. Bell MT is size 11 and then Baskerville old face is size 10.

I needed a place for information to be inserted; I chose this to be outlined white boxes, then put just under the headings “only in white boxes provided”. I chose a light background colour that made it look more professional, I chose 20% blue so it would still be able to be read. I have also broken the spreadsheet into sections so people know where to look, if they want to find out certain points. The overall effect of this is intended to make the user’s life a lot easier. As seen below, the actual design of the spreadsheet seems very eyecatching, but yet at the same time it doesn’t give the user difficulties to see the writing etc.

As you can see, certain bits of information boxes were coloured different in order to make the spreadsheet easier to understand, in this case where they actually had to enter details were white outlined boxes which stood out perfectly on a 20% blue background.

Formulas

I had to use certain formulas in order for the whole system to work, this included “sum”, “if” and “vlookup” formulas. I found these to work pretty well, especially the “vlookup” within the “if” formula. This was used with the “inhouse catering question as pictured below.

I then found that unless you formatted cells, certain formulas wouldn’t work. In this case it was with the profit, I found it very difficult to make it work properly until I pressed this then changed the cell into currency and the formula; “=K15J6”

Worked perfectly. This was very much the case with the working out at the bottom of the spreadsheet, all the cost cells had to be currency otherwise you wouldn’t get the cost only a decimal as such.

I didn’t imagine that the formulas would be simple, but I did find that I really didn’t have a lot of problems with it, obviously apart from the few mentioned. Nevertheless I needed to test the spreadsheet properly and carefully, so I then I could begin to see where certain aspects had to be changed.

Excel has various formulas that I can use to find out certain answers. Such as the ‘if’ or ‘and’ formula. E.g. if (`#*`#)+#. ` Is a letter and # is a number.

The values of ` and # make up a cell where information is recorded

Implement

After carefully looking through the spreadsheet, I found quite a few problems that wouldn’t make the spreadsheet as successful as I would have preferred. Nevertheless I listed these problems and then tried to figure out how I could solve it to make the spreadsheet work better.

Problems

The problems that I found are listed below

* There was a certain problem with the cafeteria cells, as circled there were certain halls that had a choice of “inhouse” catering. Then it would give you a price at meals per person (picture 1). However further down the spreadsheet you can still enter A catering firm and the price will still appear (picture 2) changing the overall total to be more than what it should be. This certainly could make the school lose a profit.

(Picture 1)

(Picture 2)

So after carefully thinking about it, I decided to change the catering firm formula from a normal “Vlookup formula” to a “Vlookup” within a “if” formula. This then would give a more accurate price and could increase the profit entirely.

This was the original formula that had caused the obstacle of my aim. So I then changed it, and found that the new formula had solved my previous problem.

* However the total at the bottom of the screen (picture 3) still appeared to be nothing, so now I had to find an efficient formula that would solve this. After thinking about this over a period of time, I came up with the idea of an “if” formula. (picture 4) This was proven to be efficient enough, so I could then begin testing to see if the spreadsheet was to have a chance at completing my aim.

(Picture 3)

(Picture 4)

Data entry

I found that the idea of a spreadsheet for the “Prom Committee” was very efficient, all they would have to do is enter information into the tables (picture 1). Then on the actual spreadsheet itself all they would have to do is enter the name of the hall etc. In the lefthand column (Picture 2) then the rest of the information would appear if and when you wanted it. Therefore the prom committee wouldn’t need Data capture forms and it would solve the palaver of longwinded sessions just to enter information.

(Picture 1)

(Picture 2)

Validation Rules

Although I had solved the problems with the formulas I still needed to limit the amount or what the user could enter. I done this with ease using Validation rules. However on the “user input” page the information mostly came from the “raw data” page. So the only limitations I could achieve on the page were only allowing currency on the profit and prices. These are shown below

I then needed to think of what I wanted as my validation rules for “Raw data” so for each column I made a rule. This was easy to achieve and is all seen below.

Other Validation rules that I used were mainly for the names of the halls, catering, decorations etc.

I saw this as the only available rule that I could recognise. I limited the amount of characters you could enter into these cells. The limit was 30.

Currency Validation

This was used in the columns with the costs. I chose these because it would stop somebody entering insignificant numbers that were way out of reality such as 000.00 etc. I limited this to 2 decimal places as I thought it was appropriate.

Welcome screen

I came up the idea of a welcome screen so the committee had a choice of what sheet they wanted to go to, whether it was the “user input” sheet to work out the price of tickets or the “raw data” sheet to add or view the decorations, catering, music.

After I had done this I decided to make the whole program automatic, so on the user input screen I added the choice of returning to the welcome screen or to moving on to the “Raw Data” screen. Furthermore I also done this on the raw data screen, either the choice of going back to the welcome screen or the user input screen.

Why?

I constructed this screen, to make the user’s job easier. This is intended because it saves them working out the data in a mathematical way. The whole spreadsheet is a timesaver, works much faster, has room for new data, validated rules and is an easier way to work, but the question really is, does it work?

Test

What needs to be tested?

* The problems that were first mentioned (see problems pg1) have to be solved

* The Task to be done (see Task to be done pg1) that concern the spreadsheet itself need to be completed

How I can do this?

My first chance would be to view the “problems” and “task to be done”. Then arrange a checklist, so I can see exactly what I need to test.

Checklist

* To be able to model various scenarios in order to work out break even point

* To be able to let pupils know the final price of an individual ticket as soon as possible, so they can then begin to save for things such as limousines.

* A system that when research is done allows mechanical calculations to be

Done in a couple of minutes.

* A system that can be reused for next years pupils. So that these problems won’t be repeated.

* An efficient way of storing information that can be easily established, so that they can ease the workload of their tasks

* A method of collecting data that lessens the opportunities for mistakes to be

Made

* A system that automatically works out the breakeven point and profit loss made based on data input

* A system that can predict ticket prices and the breakeven point based on approximate numbers of pupils that are attending.

* A system that can store information permanently for following years

From the checklist it is apparent that every task is completed. The only issue that I have now is to test these factors to see if they work appropriately, as one would expect.

I. To be able to model various scenarios in order to work out break even point

This was mentioned in order to use a program that could use different scenarios that have been stored in “raw data” and have room for more data to be entered

This has plenty of room for new data to be entered for future years (A system that can be reused for next years pupils. So that these problems won’t be repeated) and also has formulas that can model various scenarios in any combination that is desired. (Fig. 1)

Fig. 1

Some of the different Scenarios that I have tested are seen in the appendix as A, B and C. This also includes such completed tasks as

* To be able to let pupils know the final price of an individual ticket as soon as possible, so they can then begin to save for things such as limousines

* A method of collecting data that lessens the opportunities for mistakes to be made

* A system that can predict ticket prices and the breakeven point based on approximate numbers of pupils that are attending.

* A system that can store information permanently for following years.

II. An efficient way of storing information that can be easily established, so that they can ease the workload of their tasks

The information (raw data) is shown in the appendix under D. that has been made efficient because its on a completely different sheet and has plenty of room for new data. The list of further investigations that I took is listed on the next page:

0Test

What is tested

How its tested

Results of test

Action taken

- A system that when research is done allows mechanical calculations to be done in a couple of minutes.

Enter data from research and see if calculations are done automatically by the program “excel”.

Data was entered and the ticket price was found

2. An efficient way of storing information that can be easily established, so that they can ease the workload of their tasks.

Find an efficient way of storing information. Also add the option of entering new data

Added an extra sheet named “raw data” (see appendix D)

3. A system that automatically works out the breakeven point and profit loss made based on data input

Enter formulas that will work to the standard that I wish for working out certain factors

Entered different formulas and found that they worked to a standard that I wished (see fig. 1 in checklist)

4. A system that can predict ticket prices and the breakeven point based on approximate numbers of pupils that are attending

Enter data from research and see if calculations are done automatically by the program “excel”.

Data was entered and the ticket price was found

Evaluation

After I had finished my testing, I needed to consider if the list of tasks to be done have been completed.

So after I had seen what they were (see previous page) I then looked through the coursework to find if there was some inkling of them included. Fortunately all of them have been included in my spreadsheet except one! This was ‘a ready made system that can give access to the proms committee anytime, but can secure pieces of work with passwords’. However a password protection on a spreadsheet is not easy to do and I really didn’t have the time to accomplish that. In order for the system to be kept running properly. I’m going to make a user manual that has step by step guide on how to use the spreadsheet. This can be seen under user manual. The questions I would ask them would be so I could help improve the system further for future years

User Manual

This is a step by step guide to help the users operate the system for the prom. This is considered to be important because the system needs to be run properly in order to get accurate positive results.

Step by step guide

Step 1

When opening the system you arrive at a welcome screen and you arrive at a welcome screen. Then there is the option of going to the raw data screen or finding ticket prices. If you decide to go straight to finding ticket prices to step 3.

Step 2raw Data

Here you have the choice of seeing data that is already available with all the efficient details needed or entering new data for anyone of the values. This includes venues, catering, decorations, and music.