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.
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
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 eye-catching, 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 “in-house 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; “=K15-J6”
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 “in-house” 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 left-hand 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 long-winded 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 £2000.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 time-saver, 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 re-used 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 break-even point and profit loss made based on data input ▢
- A system that can predict ticket prices and the break-even 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.
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 re-used 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 break-even point based on approximate numbers of pupils that are attending.
- A system that can store information permanently for following years.
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:
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 2-raw 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.
(Evaluation not complete! Items such as appendix, Output and user manual are still to be included)
