So basically the problems are:
- My friend has to do all calculations by hand, which takes up time.
- My friend has to write out invoices by hand, which also takes up time.
- My friend has to do overtime to get through the orders.
- My friend has less social time.
So my friend asked me to help.
Solution
He basically wants me to make a system, which he can print of invoices for customers who place an order via the telephone, which will show details of price, vat, delivery charge and other calculations.
To solve this problem I told my friend to invest in a computer. He did just that and now he has 1000 MHz processor, which has 20 GB hard drive and 128 MB RAM. So now im going to create my friend a database system, which he could run from his new computer.
Programs to be used
Programs that could be used to make a system that could print out invoices and work out calculations need to be spreadsheet based. Here are a list of possible spreadsheet packages:
Spreadsheet QC £149.00
Microsoft Excel 2002 £281.86
Lotus 1-2-3 Millennium Edition £270.74
Crystal Deluxe £339.99
Quattro Pro £139.99
I am going to use Microsoft Excel as it has a variety of tools on offer, which I think will be well suited to my system. I have also chosen this package as it is cheaper than most of the other packages, as my friend is on a low budget. My friend is already familiar with some Microsoft packages as he did a management course using Microsoft Word. For this reason I am going to use Microsoft Excel for the system. As he knows the basic layout of Microsoft Office already, then I see no point on making him learn something completely different.
Tools
His new computer came with a laser desk printer, so he can use this for fast, good quality printouts. This will make the company look professional.
Tools im going to use in Excel:
Vlookup: This function helps you locate data in a list. It searches vertically down the leftmost column of a list and then reads across the row to find the data or value in the column you specify. E.g. if you type the number “1” into a cell the system will look up what number “1” means and inputs it in the desired cell. This will be useful, as the user will just need to click one button instead of having to manually type in all the data (Name and price of books). This will make the system user friendly and will save loads of time.
Macros: These are a set of recorded instructions that tell the computer to perform a task or series of tasks. This is very useful as I can record various things that will take much longer by hand. This will make the system easier for the user to use, as all he would need to do to navigate around the system is to click a button. This would be user friendly.
Sum: This adds a list of values. This will save my friend’s time as he won’t have to work out the calculations by hand, and so can just do it at the same time as he is on the phone. This is a formula.
If: This tool gives you two outcomes, TRUE and FALSE. If the information typed into the cell complies with the formula then the outcome is TRUE, if not then the outcome is FALSE. E.g. If someone orders more than 20 books then they get free delivery. If this happens then the outcome should be TRUE. This will be good for my friend, as he would be able to know if the customer can get free delivery.
Formulas: These are mathematical combinations of the contents of cells. These can be used to carry out calculations such as V.A.T and the total bill of the order. They can save a lot of time.
System Objectives
- The system should be user friendly. I am going to do this by making a homepage, which would have macros on it. These macros would be able to take the user from sheet to sheet without getting lost or stuck. The other sheets will have macros on it to take the user back to the homepage or to other sheets. There will be macros in the invoice sheet. These macros will help the user in various situations. I.E. suppose the user has done one invoice and then needs to do another one, instead of the user having to clear all the data from the past order manually, the user can just click the macro and it will clear everything for him. Another macro allows him to print the invoice just by clicking it.
- Minimal amount of effort required by the system. The user should just type a few numbers in the Vlookup and all the information should come up. The computer should then calculate the total price, V.A.T, delivery charge and various other calculations automatically, leaving the user to only click the print macro to print the invoice.
- All the calculations should be worked out by the system correctly, all the macros should work correctly and all the Vlookups should work.