Overall this system is very primitive and basic system, which can be easily lost. The good thing about this system is that data can be easily deleted even if it looks messy and data can be easily added. This disadvantage is that it might be difficult to read the writing afterwards and there is a greater chance of human error.
All the invoices (produced by hand), which are given to the customers are copied, and filed in a ring binder for the business’s use. Invoices are written in a book containing carbon copies. When they write invoices, they give the original to the customer, and keep the carbon copy of the invoice themselves. The business then keeps a copy of this invoice and files it away. The invoice totals are added up at the end of each week and the profits are noted down.
Mr. Bains doesn’t use a computerized system to monitor stock levels. Therefore, he has to count the stock every few days, to see which stock needs reordering. He keeps a note of the amount of stock and keeps it in a box, which also contains customer details. He then orders the stock by phoning his suppliers, or going to the Cash and Carry.
The Input, Output and Processing of the Current System
Data Flow Diagrams of the current system
The Problem with the existing system
- There is no backup copy so if the system were lost you would lose all the information.
- It is also very difficult to backup because if you wanted a backup copy of the data you would have to re-write it all out again because the current system is a manual system. This would be time consuming and if you were to photocopy the data as a backup copy it would be very costly.
- You can’t password the data because it is written onto paper.
- The system is not easily transportable because it is very bulky and some of the information could get lost on the way.
- It is not easy to search because there are hundreds of bits of card and paper so it would take hours to find an older record because the users don’t store names in alphabetical order.
- Every time you type a customers name, address or telephone number there is a chance that you write it wrong. This is called a transcription error.
- The box, which contains the data inside it, could easily get knocked over underneath the till and data can easily get lost. This would mean that the company would lose money.
- The box could also get stolen very easily because there is no security or password to stop people stealing it or reading personal information. Again the company would lose money and even customers because the customers would be reluctant to give the addresses and telephone numbers if they found out that other people could find out their personal information.
- It is not user friendly because it is difficult for the user to search for information.
What the new system has to do to satisfy the user’s requirements?
The users want the new system to be more advanced with a password to make more secure and safe. They want this new system to have a backup copy so that if the data is lost or stolen they can recover the data lost. They also want the new system to be easy and simple to use, with user-friendly icons. The want the new system to create computerised invoices, as they are faster and more professional than invoices created manually. They want the system to tell them the total cost, profit and retail price of each sale. They want the system to update stock, when purchases are made, and tell them the quantity of stock left on each item. It is important that when deliveries arrive the stock levels rise according to how many purchases are bought. They want this new system to allow greater control over the business and for more security. They want to delete and add data more easily and efficiently. They also want to be able to look at the sales they have made in the week, month or year. They want the new system to show the cost of purchases and expenses. They would like to see the profits or sales made by the company in the form of charts or graphs and in user-friendly tables. They would like a balance sheet, which shows monthly income and expenditure of the company. The want the system to store customer details, used in invoices and for deliveries of goods. They would like to search for customers through the system, when they need to and easily edit customer details. Overall they want this system to be easy to use for people with little IT skills.
The New System
Below are the inputs, outputs and processes of the new system.
The Invoice Sheet
INPUTS
Designer
- The Company logo (The Wine Barrel).
- The company name and address.
- A text box for customer details (name, address, telephone number).
- A box at the top right hand side of the page, with the invoice number, date and customer ID.
- There will also need to click on buttons related to font sizes, styles and colour themes.
- The invoice column headings will be Product code, product name, Quantity, Unit Price and total Cost.
- There will also be row labels for Sub Total, VAT and Total. There will be formulas to calculate the totals and VAT costs. For example the formula in the Total cell, will be Totalling the Sub Total by the VAT. There will also be a formula to work out the total cost of each product. This formula will multiply the unit cost by the quantity of the product.
- The designer will also need to add borders around the invoice to make it look more professional.
- There will also be macros on the page, for the user to use, to speed up daily tasks. There will be a macro to:
- Enter customer details
- Add a product
- Print the invoice
- Clear Invoice
- Save the invoice
- Update stock
- Return to the main menu
- The macros will be neatly aligned and will be surrounded by a border when
- At the bottom of the invoice there will be a comment on the guarantee of the products.
- The designer will need to make forms, so that when the customer clicks on the macro to add a new customer, a form appears so they can enter their details. If the customer is not a new customer, then a form will appear so they can enter their ID, and when they click the OK button, their details will be copied into the invoice. The designer will also need a form to add a product to invoice. On this form the customer will choose a product from a drop down list, and then click the quantity that he/she want. When they click the OK button, the product details are copied into the invoice.
User
- The user will need to enter there customer details or customer ID into the customers form
- Entering purchase details (for example choosing the product name and quantity)
- There will be other macros they will need to click on for example saving, clearing, and printing the invoice. The users will also need to click on the update stock button when they are finished, and the main menu button to return to the main menu.
PROCESSES
User
- The user will also enter their product details, and the product codes, unit prices and total prices will be entered. The row total will be calculated by a formula, which will add up the total cost of each product. After all the products are added the invoice grand totals with VAT will be calculated.
- When the customer enters their product details, the total cost of that particular product will be calculated. This will be calculated by multiplying the quantity by the unit price.
- There will be a macro to print, save and clear the invoice, update the stock table and to add the invoice details to the database. When they click on the main menu button the main menu will appear, so the user can view another sheet.
- The user will click on a button to enter customer details. A form will appear and when the customer enters their details, they will be added in the invoice.
- If the customer is a current customer, then they will enter their ID into the form and when they click the OK button, their details will go into the invoice.
OUTPUTS
User
- The user forms to enter customer details and purchase details.
- The main menu form
- Screen output of the invoice sheet.
- A hard copy of the invoice for the customer. The original invoice will remain on the computer for the business.
The Balance Sheet
INPUTS
Designer
- The Company logo
- Company address
- Various headings and sub-headings from the balance sheet:
- Income; rent, sales and purchases
- Expenditure; lighting, heating, rent, wages, advertising and insurance
- Opening and closing stock
- Gross profit
- The months of the year will also be added as one of the headings
- Profits and the months of the year. There will be formulas to calculate the total profits, expenses and monthly incomes.
- There will be macros to print the balance sheet, return to the main menu, and add or start a New Year sheet.
User
- Entering in monthly incomes and expenses.
- They will also need to click on the macros, which print the balance sheet, clear the balance sheet, and a New Year and return to the main menu.
PROCESSES
User
- The total expenses, incomes, and profits will be calculated by formulas.
- There will be macros to print the balance sheet, and add a new year to the sheet, and clear the sheet.
- When they click on the main menu button the main menu will appear, so the user can view another sheet.
OUTPUTS
User
- Screen output of the balance sheet
- A hard copy of the balance sheet for the business.
- The main menu form
Monthly Charts
INPUTS
Designer
- Company logo
- Company address and telephone number
- There will also be a graph, which will be created using a chart wizard. There will be a heading for the graph.
- There will be macros to print the charts, return to the main menu, and create a new graph with the new months figures.
User
- The user will need to click on the macros, which print the charts, create new charts and return to the main menu.
PROCESSES
User
- There will be macros to print the monthly charts, create new charts and return to the main menu.
OUTPUTS
User
- Screen output of the charts
- A hard copy of the chart for the business.
- The main menu form
The Stock List
INPUTS
Designer
- Various headings and sub-headings for the stock lists will be:
- Product Code
- Product Name
- Description
- Amount in stock
- Unit Price
- Stock Status
- There will be macros to print the stock list, update the stock list, return to the main menu, edit products and add products to the stock list.
- There will be a form to add product details
- There will be an IF Statement in the stock status cells. This will tell the user if the stock levels are low or high. For example if the level of stock is lower than 15 the cell should say ‘Low’.
User
- Entering or editing products.
- They will also need to click on the macros, which will print the stock list, add stock, update stock and return to the main menu.
PROCESSES
User
- When the user clicks on the add or edit product buttons, forms will appear so the user can enter the correct details. When they have finished adding in the details the details will go into the stock list.
- There will be macros to print the stock lists, add new products, update the stock table and edit products.
- There will be formula under the stock status column. This IF Statement This will tell the user if the stock levels are low or high. For example if the level of stock is lower than 15 the cell should say ‘Low’.
OUTPUTS
User
- Screen output of the stock list
- The main menu form
- The add product form
- A hard copy of the stock list for the business
Customers Accounts
INPUTS
Designer
- Various headings and sub-headings for the customers Accounts sheet:
- Customer ID
- Title
- Name
- Address
- Town
- County
- Postcode
- Telephone Number
- There will be macros to print the customer list, delete and edit customers and return to the main menu.
User
- The users add customer details to this database through the invoice.
- The user can print this customers list, using a macro
- There will also be macros to edit or delete customers, and return to the main menu
PROCESSES
User
- There will be macros to print the customer’s lists, delete customers, and return to the main menu.
OUTPUTS
User
- Screen output of the customer list
- A hard copy of the customer list for the business
- The main menu form
Invoice List
INPUTS
Designer
- Various headings and sub-headings for the Invoice Lists sheet:
- Invoice Number
- Date
- Customer ID
- Title
- Name
- Address
- Town
- County
- Postcode
- Sub Total
- VAT
- Total Amount
- There will be macros to print, delete and edit the Invoice list and return to the main menu.
User
- The users add invoice details to this sheet through the invoice.
- The user can print, delete and edit this invoice list, using macros
- There will also be a macro return to the main menu
PROCESSES
User
- There will be macros to print the customer’s lists, delete customers, and return to the main menu.
OUTPUTS
User
- Screen output of the invoice list
- A hard copy of the invoice list for the business
- The main menu form
Objectives of the new system
Mr. and Mrs. Bains would like a new system, which will perform the following functions:
- Allow data from the invoices to be entered and recorded quickly and accurately
- Calculate the monthly income, expenses and profits made by the business in the form of balance sheets and user-friendly graphs
- The system must record sales and profits
- The system must record customer names, telephone numbers and addresses
- The system must record customer Ids
- The worksheet must be protected so that users cannot change or edit the system, unless they are required to do so.
- The system must record stock levels
- This system must contain a password or some kind of security to keep data safe
- There must be a backup copy
- The system must be advanced with good technology
- The system must be safe
- The system must be user friendly, robust and easy to use
- The system must have an invoice system
- The system must update stock levels when deliveries arrive
- The system have a balance sheet to show the profits, and expenses of the business
Performance Indicators
The following performance indicators will be used:
- It should take no longer than 2 minutes to enter the data into each invoice
- The monthly sales summary and chart will be produced using a menu item or button
- The system should be easy to use for anyone with a basic knowledge of Excel
- The data from the invoice must be transferred to the database storing all invoices, at the click of a button, before a new invoice can be created
- Products prices must be changed easily
- Products and new stock must be added easily
- The system should allow the user to print invoices, stock lists, balance sheets and monthly charts and sales tables at the click of a button
- There should be a menu, which enables the user to access, the balance sheet, invoice, monthly chart or the stock table at the click of a button
- The user should be able to exit the system quickly at the click of button
- It should be quick and easy to search for particular customer through the system
In conclusion the analysis shows that a computerized system would be the best solution, so I must now look at resource requirements and what is available to me.
The resource requirements
-
The company I am designing a system for have a Dell OptiPlex GX270 computer. It has an Intel Pentium 42.60GHz processor. There is 38GB of hard drive space left and 256MB of RAM memory. The operating system on this computer is Windows 2000.
- They have the basic software on their computer. They have Microsoft Word, Microsoft Publisher, Microsoft Access, Microsoft Excel and Microsoft Photo draw.
- They have a monitor, keyboard and a wireless mouse on which they could potentially use the system.
- They also have a CD burner, and a floppy disk drive, which could be useful to back the system up on.
- They have a fast, high-quality HP laser printer, which would be useful at printing customer invoices at high speeds.
-
My home computer is a Dell computer, with an Intel Pentium 4 processor. There is 97.96GB of free hard drive space left, and IGB of RAM memory. My home computer has Windows XP as the operating system, with Microsoft Excel and Microsoft Access as two packages, which could be used to design the system.
User’s skill level
There will be two users of this new system, Mr. And Mrs. Bains who are the owners of the wine shop. Mr Bains is very computer-literate and has a good basic knowledge of Excel. Mrs Bains has less background knowledge in IT, and fewer IT skills than Mr. Bains. Mrs. Bains will need to be trained to use Excel, so the system will need to be user-friendly and easy to use if Mrs Bains is to use the system. Both Mr and Mrs Bains have experience in filling out invoices, though the format and layout may be different to the invoice, which will be created using the new system. Mr Bains will be the main user of the system, as he works longer hours in the wine shop.
Now that I have finally analysed the system by researching, finding out how the system works and how the users want the new system to work and what the new system must do to fulfil the current tasks and the existing problems I can now design and plan the new system.
Design
System Design
There are many ways in which the system can be designed.
1. You can buy in a programmer to design the system for you.
Advantages
- No work involved for you.
- The programmer will design the system like you want it.
- If the programmer designs it, it will do all the tasks that the users want it to do and the existing tasks that the current system does.
Disadvantages
2. Program the system yourself.
Advantages
- It will do exactly what you want it to do.
- The system will do all the requirements of the new system and will do what the current system does.
Disadvantages
- Costs of buying a programming language.
3. Get a tailor made system, which is already made.
Advantages
- It is already made for you.
- It is less work for you.
- Quicker than programming it yourself.
Disadvantages
- The system will not do everything the users want it to do. It might do some of the tasks but not all of them. For example it might record customer names and addresses but it might not record customer accounts.
- It will have limitations.
- No financial resources as users are not willing to pay.
4. Use a software package like spreadsheets or databases to design the system.
Advantages
- It will do exactly what you want it to do so the system will achieve all the user requirements and all the things that the current system does.
- We have the skill involved.
- We have the resources.
Disadvantages
- Time consuming.
- You have to have the software available to design the system.
- You have to be able to know how to use the software.
Conclusion
- I am going to design the system myself using a spreadsheet or database package to help me.
- I think that this is the best choice because money wise it is financially cheaper and as I have no money to spend on the system I have to look at the available resources.
- I also think it would be better because I have spreadsheets and database packages on my home computer and both these packages are also available at the company shop.
- I have also got the skill involved in making the system and have worked on both spreadsheets and databases.
- If I design the system myself it will be able to work exactly the users want it to work.
- If I hired a programmer it would be too expensive and I have no financial resources.
- I wouldn’t be able to program the system myself because skill is required and it would be very time consuming. I also wouldn’t be able to pay for the costs of buying a programming language.
- It would be a bad idea to buy a tailor made system because it will not do everything the users want it to do ands it will have limitations. Again there are no financial resources as users are not willing to pay for the package.
- I am also choosing to design the system myself using a spreadsheet or a database package because it will do everything the system has to do in order to fulfil the user needs.
As I have decided to use spreadsheets or databases to design the system I will compare both packages in order to decide which package to use.
Comparing a database package and an excel package
When I compare the packages I will compare Excel and Access because these are the two packages, which are available at home and at the company shop.
Here are some areas to test each application:
- Can it store data?
- Access- yes data is stored in tables.
- Excel- yes data is stored in sheets.
- Have they both got facility forms?
- Access- yes access has forms and tables.
- Excel- yes excel has forms and sheets.
- Can you add menus?
- Access- yes, in you can create simple and complex menus.
- Excel- yes excel allows you to create menus as forms or inside sheets
- Can you validate data to test if what you are using is correct?
- Access- yes you can use table validation
- Excel- yes you can use form validation
- Can they edit and delete data?
- Can you backup data?
7) Can you add macros, to shorten the time taken to perform tasks?
8) Can you use Visual basic to allow macros to be written?
The final package for the system
I have looked at both applications, Microsoft Access and Excel. After looking at both of them I have decided to use Microsoft Excel 2000. This is because Excel enables designers to create specifying error messages. It is generally a better application to use, to create and invoicing system, as access is more of a database package, for storing and searching through customers. On my system the users will not be searching through customers on the computer, as they will have hard copies of invoices, for them to read. Excel allows you to use formulas to work out complex sums automatically. There is security and passwords to limit access to the system.
Excel allows you to create forms and menus, to create a user-friendly system. Excel allows the user to use macros to shorten long tasks, for example copying and pasting cells to another sheet. Although access is ideal for creating queries to search through the system, and making reports, this will not be needed in my system. My system will depend on the invoice sheet and balance sheet, rather than reports and queries. The users wanted a system where they could look at their profits and losses of each month/year in the form of graphs or charts. Unlike access, excel allows users to create charts and graphs. I will have a separate worksheet with a macros button, allowing the user to look at their graphs of profits and losses.
Features of Excel
- In excel you can use formulas to work out sums automatically. In my system I will be using many formulas in the worksheets. I will need formulas to work out the sub total, VAT and total in the invoice sheet. The computer should automatically work out the totals, when the customer has entered the purchases, and the totals should appear in the cells.
- For my system I will be using many different types of macros, to shorten long tasks for users. I will be using macros to print the stock list, invoice sheet, balance sheet, customer’s list or the charts. I will also need macros to save the work sheets or return to the main menu. Other macros are described in the inputs, outputs and processes.
- Excel also allows users to create forms. This will be a major part of my system, and will be used on nearly every worksheet. On the invoice sheet for example, there will be a form to enter customer purchases, enter the customer details, and enter the customer ID or a form for the main menu. In the stocks worksheet there will be a form to enter a new product. Every worksheet will have a form, which will take them back to the main menu. From here the will be able to move to another sheet.
- The users of the system want more security from the system that will be designed for them. In excel I will be able to add security to the system, and I will be able to protect certain worksheets. I can also add user names and passwords, and control access levels, so certain members of the staff have access to certain worksheets. Protecting worksheets will also stop users from accidentally deleting headings
- The users also requested that they could look at their profits and losses of each month/year in the form of graphs, as well as on the balance sheets. Unlike access, excel allows users to create charts and graphs.
- I can also add an auto filter in excel, or sort data in particular worksheets. This will be a good feature of excel to use in my system, as I can use auto filters to sort the customers or invoice lists. For example, the user may want to target their advertising to the customers in a particular area such as slough. To do this they could use an auto filter function, which would come up with all the customers who live or work in slough.
- The users of the system also think that it is important that the stock levels are monitored carefully. Therefore, I can use an ‘IF Statement’ in the stock sheet, which tells the user if the stock levels are ‘High’ or ‘Low’.
- Excel has sophisticated report facilities with the ability to format text, set margins and page layout, import a company logo and preview before printing.
- Using Excel for my system will mean that I can customise menus and toolbars.
Overview of the System
The system will be based on one workbook, containing a number of worksheets. The main worksheet is the invoice sheet, which allows users to add customer details, and to choose their products. Once they have finished they can save and print the invoice, and their details will be added to the customers worksheet, and the invoice lists sheet. Then they can either exit the menu, or go to the main menu and click on another worksheet. The other worksheets in the system is the balance sheets, which shows company profits and losses, and the worksheet which allows the user to create charts based on the profit and losses figures from the balance sheet. There is also a stocks worksheet, which allow users to edit stock levels, add and delete products. The other two worksheets will contain details of all the customers, and all invoices, which have been created. Each year, the users can create a new balance sheet, and chart to show the profits and losses. Each time the user starts using the system, there will be a comment to welcome them, and then the main menu will appear. This will allow them to go to the worksheet that they require. They will also be able to print off each worksheet, using a macro. There will be a number of macros on each worksheet, which will make it easier for the user to complete tasks on the system, for example, printing the sheet.
Data Flow Diagram of the proposed new system
Below is a data flow chart. A data flow chart shoes how data flows through a system. Here is the data flow chart for my system.
Below is the data flow chart for the Stocks sheet
Below is the data flow chart for the invoice sheet
Below is the data flow chart for the Balance sheet
Below is the data flow chart for the Charts Worksheet
Manual Designs of Worksheets
Below are the manual designs, of the worksheets in my system
Manual Designs of forms
Below is the design of my main menu form, which will appear when the user opens the system. There is also a macro on each worksheet, which allows the user to go back to the main menu, and go to another worksheet.
Below is the design of my Add products form. This will appear when the user clicks on the add product macro in the stocks form.
Below are the designs of the forms, which will be in the invoice sheet. This particular form is the first part of three forms, which allows the user to choose whether the customer is an existing or new customer.
This is the second form, which allows the user enter the Customer ID. It appears when the user clicks on the form above, on the existing customer button.
This is the third form, which allows the user enter the new customer details. It appears when the user clicks on the first form, on the new customer button.
This is the third form, which allows the user enter the new customer details. It appears when the user clicks on the first form, on the new customer button.
