My friend Thomas Edmondson and I are planning a trip in the summer holidays to Bologna. We need to work out the different costs for accommodation, food, transport and amusements. The user for this will be Thomas Edmondson. I am going to do this in a system because it would be difficult to work out the costs and keep it all in order in a written document. Using this system will allow us to work out our budget and sort everything efficiently.
Consideration of Alternative Solutions
There are some different ways in which this problem could be solved.
1. We could write and calculate everything by hand, this is the method we were originally using but we need a better system.
2. A special program could be written to do this. This is time consuming and I do not have the right skills for it.
3. I could use a database package. Each person’s budget and expenditure could be stored but this isn’t as well suited to the requirements as some of the other options as it would be difficult to do calculations on here.
4. A spreadsheet could be used. Separate worksheets can be used for accommodation, transport, food and amusements.
I am going to use a spreadsheet because:
1. If prices or budget changes the spreadsheet can be easily edited and any related figures will change automatically. In a manual solution this would have to be completely redone.
2. The spreadsheet will be nicely presented easily.
3. I can use complicated things like macros that can be very useful.
4. All the figures will be accurate and reliable.
Questionnaire for User
This is the questionnaire that I have designed for the user so that I can find out what kind of system he needs.
1. Does it need to be presented in any particular colours?
Not really, just something bright.
2. How many sheets would you like to be included?
3. What would you like on each sheet?
A page for the accommodation, transport, food, amusements and data entry.
4. Are you able to use spreadsheets efficiently or does it need to be easy to use?
I can use them quite well but other people may also need to use the system so it should be reasonably easy to use.
5. Will the system need to use big or small figures?
The system should be able to use small and large figures.
6. Will the system be dealing with anything other than money?
7. Is there anything else that you would like to specify?
Yes, it must be quite fast to use.
The user knows what he wants the system to be like and has specified the following requirements:
1. The system must have a nice, bright looking interface and be easy to use.
2. The system must find an answer within three seconds.
3. The system must be robust enough to handle small and large figures.
4. The system should have no additional hardware or software costs.
5. The system must clearly show five different sheets with accommodation, transport, food and amusements and a data entry screen.
6. Must be able to deal with decimals and convert it to euros.
7. Must be easy to update.
8. Must be able to round to 2 DP.
9. The system must take no more than a few minutes for a new user to learn how to work it.
10. The system must display the cheapest options accurately.
11. The system must use at least one macro.
12. The system must use a drop down menu.
13. At least one method of validation.
Part 2- Analyse
Appropriate Software and Hardware
I have chosen to use a spreadsheet package and this gives me two options. These are Microsoft Excel and Lotus 1-2-3.
Lotus 1-2-3 is a popular spreadsheet program and would be suitable for solving such a problem. Unfortunately neither Tom nor I are familiar enough with the software to create the system and we would need to install the software which could be expensive.
Microsoft Excel is the best-known package and would be great as I know how to use it, Tom is also capable with this package so he can update it if need be. I already have MS office (including MS Excel) installed onto my computer so wouldn’t have to buy any extra software.
I will use Excel as me and Tom both have a good knowledge of it, far superior of our knowledge of Lotus 1-2-3 and it wont cost us anything extra as the software is already installed.
The hardware required will consist of a basic processor, as calculations in Excel do not require a great processor speed, a monitor, mouse and keyboard. We will use the system on my home computer.
The data required to build this system will be accommodation, transport, food and amusements information. When the system is used we will have to supply:
* Our budget for each part of the trip
The system will then show appropriate places to stay, transport, food and things to do.
The information I need can be found on hotel websites, bus/train websites, leaflets
and by calling companies for quotes. It is important that the prices are all up to date so that we will get the most accurate results, to make sure of this I will collect data from both websites and leaflets to make sure that they are the same. The user will get his input data from checking his finances.
A hotel website showing price information
Once all the information has been collected I will key it into the spreadsheet straight from the websites/leaflets. Tom will key in any updates made to the system. Tom will type in details as the system is being used. I will verify the initial data by visually checking it once I have entered it. Prices need to be automatically validated so that the system won’t accept negative costs and will only accept figures of up to ï¿½5000 so we don’t spend too much.
Updating Cost Information.
If costs change they will need to be updated. The new price information will replace the old information in the spreadsheet.
Price Details Price Details
Dealing with Users.
Each person will enter his or her details into the system and after this the best deals
will be displayed.
Details Best Deal
The raw holiday information will need to be keyed in to the spreadsheet. Formulae will be entered into the spreadsheet to find the best deals.
The data is manipulated as follows:
* User enters his or her details
* Formulae are used to calculate the costs
* These costs are sorted into ascending order
* The first on the list (i.e. the cheapest) is displayed on the user’s screen
The output for the system will be that the system is displayed and used on screen. It could be printed out but if this were done the system wouldn’t be able to calculate the options.
The computer file containing the final system must be backed up onto floppy disk. This copy should be kept at a different location from the computer in case there is a fire, theft or flood at the premises. A backup copy needs to be made each time the system is updated.
There should be password security on the computer so that people using the system cannot change any of the formulae. This can be done by protecting the worksheet so that nothing can be changed without a password. If anyone attempts to make a change to the worksheet Excel will ask for a password. The password should be at least six characters long and not a common word that could be easily guessed.
I drew up the following initial design system:
The spreadsheet will calculate the cheapest deal on the figures that we input. The information for each section is stored on a separate sheet. Each of these sheets contains tables of formulae, which will calculate the cheapest deal. The system will then sort the table of figures by ‘total bill’ and perform the lookup operation to post the result back to the entry form for the user to see. This operation will be done at the click of a button using a recorded macro assigned to it.
User Feedback on initial designs
When I interviewed Tom he said that he liked having the different cost information on different sheets because it shows all the options rather than just the cheapest; we may want a better quality than the cheapest option can provide. We decided that instead of each of us entering our details it would be best if we entered an average and find what is best for everyone as a group. Tom also had to decide on a colour for an interface so he went for oranges, reds and yellows, as these are bright and friendly.
Data entry sheet:
Hotel and travel budget, suitable dates.
Best deals for accommodation, travel and times to go.
Command button Arial 16pt.
All text Arial 14pt except where labeled differently.
The other three sheets, travel, amusements and food, will follow the same design and work in the same way as the accommodation data sheet.
The implementation of the final design will need to be broken down into more manageable sub-tasks:
1. Create data entry form. Add validations to each cell where the user can enter data.
2. Enter data for each of the different sheets. Name worksheet tabs and cells.
3. Enter formulae to calculate the cheapest options.
4. Post the details of the cheapest deal back to the data entry screen.
5. Add a button on the data entry screen, which runs a macro to perform task 4.
6. Protect all cells except those that require input and add a password.