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.
Data Required
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.
Data Collection
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
Data Input
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.
Data Flow
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
Data Manipulation
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
Output
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.
Backup/Security Strategy
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.
Part Three-Design
Initial Designs
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.
Final Design
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.
Accommodation sheet:
Arial 14pt
The other three sheets, travel, amusements and food, will follow the same design and work in the same way as the accommodation data sheet.
Subtasks Identified
The implementation of the final design will need to be broken down into more manageable sub-tasks:
- Create data entry form. Add validations to each cell where the user can enter data.
- Enter data for each of the different sheets. Name worksheet tabs and cells.
- Enter formulae to calculate the cheapest options.
- Post the details of the cheapest deal back to the data entry screen.
- Add a button on the data entry screen, which runs a macro to perform task 4.
- Protect all cells except those that require input and add a password.
Task 1: Create Data Entry Screen
A title will need to go at the top of the page. This will be followed by three headings and spaces to input a value after them:
Test Plan
A test plan needs to be devised to make sure that the system does exactly what it is supposed to do.
Part Four-Implement
Finished Design
The finished design has been implemented in Microsoft Excel. We can enter our budgets on the data entry screen as shown below.
The Data entry screen
The macro attached to these buttons sorts takes the user to that page. The code for the accommodation macro is shown below
There are four worksheets for each of the budgets. The formulae for amusements are shown below.
The transport formulas are show below.
Test Results
This section shows the results of the test performed following the test plan in the Design section.
I only had two problems, a spelling which was corrected, and I couldn’t enter the password on the school computers so I sent this home and entered it there.
Part 5-Evaluate
Evaluation of Initial Objectives
The system has a bright user interface, displays the information within three seconds and handled all of my figures efficiently. The system didn’t incur any additional hardware or software costs. The system has five different sheets, showing accommodation, transport, food, amusements and a data entry screen. Tom said that the system was easy to update, he found it easy to use and he thought that it was accurate.
All figures were rounded to two DP and I used validation, a macro and a drop down menu.
All of my objectives from my user requirements have been achieved.
User Feedback
Tom was quite pleased with the spreadsheet and said that I fulfilled the requirements well. He also said that different people had different budgets so this was difficult to solve, we just added everybody’s budgets together and entered this but Tom said it would have been good if we had thought of a different way.
His letter is shown below
Suggestions for Improvement
To improve this system I may have devised an individual system for each person so that their budget wasn’t a problem, as some people could afford what others couldn’t,
I would then incorporate all of this into a bigger system.
I would also try to present the system better by removing the gridlines, using more colour and adding pictures of the different options.
