User Requirements
- The system must use a simple and efficient user interface.
- The system must keep itself up to date.
- The system must display its inputs and outputs clearly.
- The system must not be time consuming for the end user.
- The system must work. – The calculations it produces must be correct.
- The system must have a corporate image.
- The system must calculate the % added by the company and the profit from that.
- The system must keep an accurate log of past transactions.
- The system must produce an invoice.
- The system must automatically backup the data within it
Performance criteria
- The system must use a simple and efficient user interface.
- The system must keep itself up to date.
- The system must display its inputs and outputs clearly.
- The system must not be time consuming for the end user.
- The system must work. – The calculations it produces must be correct.
- The system must have a corporate image.
- The system must calculate the % added by the company and the profit from that.
- The system must keep an accurate log of past transactions.
- The system must produce an invoice.
- The system must automatically backup the data within it
- The system must be protected from the end user accidentally damages the system.
- The system must stop the user from entering an incorrect data type in a form.
- The system must use macros to hide complex actions.
Initial information from end user
Here are the main pieces of information I gathered from the user:
“The main purpose of the system would be to increase productivity and efficiency. It would be best to keep everything onscreen simple, and allow for quick and easy usage. All important boxes to fill in should be clearly labelled.”
“Because of how exchange rates work it is very important to calculate using the latest available data as we purchase currency very soon after the customer buys the currency.”
“The system must produce identical calculations to the current handwritten method.”
“It must also calculate the profit we add to the transaction. Our rates are as follows:
Transactions between £0-1000 the rate is 3%
£1000.01-£5000 the rate is 2.5%
£5000.01-£10000 the rate is 2%
£10000.01-£30000 the rate is 1.5%
£30000+ the rate is 1%”
“The system must use our corporate image. I will supply the logo to you via an email.”
“It is very important that the system logs previous transactions accurately.”
“The ability to product an invoice would also be useful.”
“There must be some protection against data loss.”
What is the estimated time scale for implementation?
I have been given a deadline of end of February for completion of my system. Therefore, planning will attempt to ensure that this deadline is met.
The following Gannt Chart shows how I intend to allocate time to produce the system.
Design
Design
What information do I need to gather and where will I gather it from?
I will need most of my information from 2 sources, the first being the Manager of the travel agent, and I will also need information from the internet.
The information I need from the manager is the rates the company charges for each of the money exchange service, and I also need information about their corporate image to incorporate into the Project.
The information need from the internet will be the live exchange rates which will be automatically downloaded on every launch of the application, and also every hour.
Flow of Data through the System
What Hardware and Software will be needed?
Hardware:
- A computer capable of running windows XP and the software Microsoft Excel 2003.
Recommended system specification:
- 1ghz processor
- 256mb ram
- 20gb hard drive
- Internet access is also required (broadband or dialup)
Software:
- Windows XP
- Microsoft Excel, however I would recommend the user to buy the full package for further development, and it is also better value. (or the free alternative OpenOffice)
How Will the User Input the Data into the System?
The main way the user will input data is using the keyboard, the person serving the customer will input all of the data specific to the customer using a series of boxes. The user will use the mouse to click the macro buttons on the spreadsheet and then the spreadsheet will automatically calculate the values required.
What Processes will be carried out on the Data?
The first process that will be carried out to the data is the conversion of currencies (using the rates downloaded from the net). The worksheet will then apply the company’s rate (profit) to the figure. The customers data is then stored into a database of past transactions, and it will also automatically produce an invoice for the customer. For simplicity, printing will be contained within excel, using a range of different formatting to create a letter-like format.
How will the data be Presented or Outputted?
There will be 2 main forms of output, the on-screen output, and a printout. The on-screen output will show the required in a number of formats depending on the required use of the data. The main on-screen outputs will be the instant data, to tell the customer how much the amount costs, however there will also be the database of past transactions, and the invoice page (ready to be printed). Once the invoice is satisfactory the user will be able to press a print button which will automatically configure the printing to fit the area to a page etc. this is the printout data.
How will the user Store and Backup the Data generated by the System?
The system will automatically save a backup every time new data is added to the system. The backup with be stored separately to the system on the hard drive. There will also be a macro that allows a manual backup that is not written to unless the user specifically wants to – every hour or at the end of the day etc (for the event of the original and backup getting corrupted)
How will the Data within the System be Protected and made Secure?
All of the required security and protection tools are already built into excel, and with the use of macros, protection and data verification I can block the user from editing anything unnecessary or damaging to the system. I can also ensure they do not enter the wrong data type into the entry forms.
How will the system look?
I will produce a design of how I think my system will look. I will produce a series of designs that will cover the separate areas of the worksheet that the user will see, and any areas that the user won’t see.
Design Specification – the function of each worksheet
I will design the system to use 4 worksheets. Below I will specify each page and its functions.
Worksheet 1 – User interface
This page will be the main page that the user will look at. It will include a section of the page that will contain buttons that run macros that will perform the main functions of the spreadsheet. The user will spend a large amount of time on this page so it will need to be simple and easy to access the main functions of the sheet. It will include the main input area, where the user has the required forms to collect the user’s data.
Worksheet 2 – User information Database
This worksheet will be the page that user’s data is logged to when the ‘make order’ button is clicked. A macro will automatically store all the data to this page is separate lines for different customers. The entries will be numbered so specific order data can be chosen from the list. An exact date and time of the transaction will also be logged.
Worksheet 3 – Invoice Page
This page will contain the invoice. It will only be seen on paper after it is printed.
Worksheet 4 – Exchange Rates
This worksheet will include a lookup table that will include the exchange rates that are automatically updated via the internet. This will be the central point of the currency calculation and will be used to hide most of the calculations required. The end use will not see this page, so it will not be required to be too simple or clear.
Detailed design for each worksheet
Worksheet 1
First I will create a small table on the page headed ‘Client Information’. See images below for first version of list. This area will allow the user to input the client’s basic information. There will be a separate space for the user to enter the details about the client’s transaction. There will be a box for the user to enter the clients’ amount of money exchanged in GBP, a drop down box for the user to select the client’s currency of exchange, and a box that contains the amount of money in the desired currency. The currency calculation will work both ways, so the user can specify either how much money they want to convert, or how much they want converted.
Worksheet 2
This page will be a simple page that contains clients’ information in a simple table. See below for draft layout. Using the below interface I will create a button that will allow the user to edit a clients’ data by inputting the client’s reference number and then clicking the edit button. The user will also be able to print an invoice directly from the page by entering the client’s reference number and then pressing print invoice. The invoice will then be automatically printed using the default printer without the users input.
Worksheet 3
This worksheet will contain the temporary invoice created by the button on worksheet 2, and will then be printed. The macro on worksheet 2 will automatically fill the page with the required information and will then print the page. The page below contains placeholders for where client’s information will be placed.
Worksheet 4
This page will not be seen by the end user. It will contain the exchange rates that have been automatically downloaded from the internet. It will also contain any temporary values created by calculations and macros if necessary. I will set up a web query using the web query wizard in the menu. I will use the website www.x-rates.com as the travel agent already used the same source of information.
All spreadsheets will be protected where user input is not necessary to the worksheet.
Detailed Design for User Interface
The user interface will be mostly comprised of macros. This will make the interface as simple as possible. Below is a mock-up of a possible design.
The current interface does not have any logos, names etc - the company’s corporate identity has not yet been included.
Re-usability
The user interface is re-useable as it can be automatically cleared and then made available for the next customer. The Database, by design, is reusable as it allows many entries into the system, and then allows them to be recalled when needed for reference etc.
The spreadsheet also has a high re-usability level as the data used to convert the important values in the spreadsheet is automatically downloaded from the internet – giving the spreadsheet the latest information without the user having to manually update any values.
The invoice is also re-useable as it can be automatically filled with different user’s information when necessary.
The invoice production system should allow for many invoices to be created.
Testing Plan
Implementation
I will now create the live data import from the web, so I can build the user interface fully.
Testing
Testing
The system must keep itself up to date.
The system must have a corporate image.
The system must correctly calculate the % added by the company and the profit from that.
The system must automatically backup the data within it.
Every time the system is saved, the system automatically keep the file ‘Backup of Implementation.xlk’ stored in the same directory, this allows any major mistakes to be reverted by going back to the old file.
The system must be protected from the end user accidentally damaging the system.
The system must stop the user from entering an incorrect data type in a form.
The ‘Send to Database’ button works correctly.
The ‘Go to Database’ button works correctly.
The ‘New Client button works correctly.
The ‘Print Invoice’ button works correctly
Currencies are correctly converted and the correct exchange fees are applied.
GBP exchanged: £3000
Currency: Euro
After converting the exchange by hand, the results I got where that the exchange would come to € 3,949.83, and the agency would earn £75 profit, making the total cost £3075.
The system must produce an invoice.
Evaluation
Evaluation
The system must use a simple and efficient user interface.
When creating the user interface, I ensured that all related information was displayed in grouped sections. I also tried to keep the interfaces un-cluttered, making it easy to see where necessary buttons and input areas are. This method was successful, however the interface I designed does not have a professional look to it – it may give a bad impression it a customer sees the system.
The system must keep itself up to date.
The system uses Microsoft Excel’s feature ‘web query’ to automatically download the latest exchange rates every 30 minutes. This solution is very effective as it updates in the background without the user’s knowledge or input, and only takes a short amount of time to complete the operation.
The system must display its inputs and outputs clearly.
When creating the interface I ensured that all input and output sections of the spreadsheet were clearly visible using excels formatting features.
The system must not be time consuming for the end user.
I have minimised the time the end user has to spend using the system by utilising features in excel such as macros and formulae to complete complex tasks. This method is effective as the user know only needs to type the customer’s information. The rest of the functions of the spreadsheet are automated, or can be activated simply by pressing a button.
The system must work. – The calculations it produces must be correct.
I created the calculations the formulae apply using the information provided by the end user. I then did the calculations myself using the same live data the spreadsheet does. The answers the system produced were all correct, so from this I know the system works.
The system must have a corporate image.
I have included the company logo on all pages the user will see. This is effective as it shows the system is produced for the specific business, however the overall look of the spreadsheet is unprofessional.
The system must calculate the % added by the company and the profit from that.
I used nested IF formulas to automatically decide the % fee the company will earn from the transaction. This works well as it happens automatically, however it does not allow for user configuration, and will require someone with full knowledge of the system to modify the settings later.
The system must keep an accurate log of past transactions.
The system keeps a log of transactions in the form of a database. It contains the date of the transaction, all customer information, and all information used in calculations. This works well as the user can check any information about a customer via the database at any time, and each customer is also given a reference number for ease of finding their information. A problem with this is that you cannot search for records via client name etc.
The system must produce an invoice.
The system automatically produces invoices based on vlookup formulas relation to the customer’s reference number. This system works well as the user will only need to enter a reference number to get a complete invoice. A problem with this method may be that the customer has forgotten their reference number and it cannot be found by other means, the user will have to manually search through the database for their record to be able to produce an invoice.
The system must automatically backup the data within it
The system automatically backs up the data within it by means of keeping a second copy of the file that is always one update behind. This works well as it is a simple and transparent method of backup that does not involve the user unless the backup is needed. This disadvantage to this method is that it requires the user to manually store separate backup files in case of a failed computer.
The system must be protected from the end user accidentally damages the system.
All cells that are not going to be edited directly by the user are protected from being modified. This works well as the user is locked out from making any damaging alterations to the system
The system must stop the user from entering an incorrect data type in a form.
It was not possible to meet this requirement as Microsoft excel did not have specific enough cell criteria to effectively limit the user. If I created the system again I may use a different piece of software that allows this to be built into the system.
User Feedback
After allowing the user to use the system I received the following feedback:
“The system work very well, it completes all of its functions correctly. It is very useful for converting currencies and saves a lot of time for staff. The system is easy to use and understand. The system has improved efficiency for us and so reduces potential customer waiting times. I also like how all customer information is stored in a database that can be easily found for later reference – and can easily be used to print out an invoice. It would, however be useful to allow the customer to specify the amount of money in GBP, and then the system convert it to the desired currency, instead of the other way around. I also think that the user interface, although quick and easy, does appear unprofessional. A better system to find customer data in the database is also desirable. After checking calculation myself I can confirm that they are working correctly, and the system will work fine as it is now.”
Comments on User Feedback and Possible Future Improvements
After speaking to my end user, I feel that the overall user feedback is good, however does touch on some issues of missing features in the system. If required by the user I could solve the problems shown:
Unprofessional interface
To solve this problem it is possible to tweak the looks of the system without modifying the calculations of the spreadsheet. Excel provides ample formatting tools to create a professional interface.
Better database search
To solve this problem I could set up filters in the headers of columns in the database to allow the user to specify required data.
Possibility of reverse currency calculation
An easy way of solving this problem would be to include a section in the user guide on using excel’s feature ‘goal seek’ to find the required value. This will require no modification to the system itself.
User Guide
User Guide for Exchange Calculator
Installation
To install the system simply copy the system from the supplied CD, and then paste it into an available and empty folder on the computer that will be used with the system.
Running
To run the system, open Microsoft excel. Then click ‘File’ and then ‘Open’. Browse to the location the system is stored, highlight ‘Exchange Calculator.xls’ (Do not select ‘Backup of Exchange Calculator.xlk’), and then click open.
Navigation
The system has 2 different pages that you will see, the interface, and the database. This is the interface:
The other page is the database:
Creating an Order
Moving Orders to Database
Once you have finished filling in the form, press the ‘Send to Database’. This will move all of the client’s information to the database.
Printing an Invoice
To print an invoice, first navigate to the database page. You will then need to find out the clients unique reference number. This can be found by looking through the database until the client’s information is found. The most recent addition to the database will be at the top. Then fill in the Order reference number at the top of the page.
Once this information has been filled in simply press ‘Print Invoice’, and an invoice will be prepared and then printed. Ensure that the required printer is switched on at the time.
Modifying Mistakes in the Database
To modify a mistake in the database, you must simply find the customers information (if they have their reference number, the process is made much easier). Then simply edit the cell as you would any other cell in Microsoft excel.
Starting a New Client
Just press the ‘New Client’ button.
Saving the System
To save the system, just press file on the bar at the top of excel, and then press save.