Converting a Formerly Paper Based System to Excel
Converting a Formerly Paper Based System to Excel
BRIEF
Design a system using Microsoft excel, on which a small business can run, which is presently paper based. The system should simplify the day to day running of the business and be relatively easy to use. A simple set of instructions must be supplied for the user and a set of detailed instructions in case the system needs to be upgraded.
SITUATION
The small business I have chosen is a Bagel Bar in Romford (Essex). My Aunt, Mrs Helen Hardy, owns the bar. She has agreed to supply me with any information to help in my project, and give interviews to determine what she wants from the system.
INTRODUCTION (Background)
Just Bagels is a Bagel bar in Romford (Essex). The bar aside from selling Bagels sells soft drinks, crisps and other snack food and refreshments. It is a small, yet thriving business and has two methods of selling its commodities.
. Via customers entering the bar and purchasing over the counter.
2. There is an ordering service, whereby offices can make large orders for their buildings, to be delivered or collected.
Just Bagels is in a prime situation, in central Romford, next door to 'Hollywood's' nightclub. The bar picks up a lot of business from the club, as it is open when the club closes on Friday and Saturday night.
Just Bagels receives roughly 8 large orders each day, and has 6 employees, who are each paid fortnightly, when they are also given their timetables for the following fortnight.
CURRENT SYSTEM (and the problems within it)
The owner of the bar (Mrs Helen Hardy) was interviewed so that I could establish, exactly how the current system works, and the problems with it. Overall the current system is organised very simply, and paper based. In the interview we discussed the different aspects of the system that Mrs Hardy wanted changed, or left the same. We covered the following aspects.
* Orders
Orders are taken by phone and written by hand, the order forms are then used as receipts. This process eliminates the chance of recapturing lost business from one off orders or when regular orders stop. Mrs Hardy wants the method of taking orders to become computerised, so that with a few clicks the orders are filled out and can be printed, to be used as receipts. However she would like the system to store any businesses that take orders so that she can chase up lost business with flyers and/or promotions.
* Stocktaking
...
This is a preview of the whole essay
* Orders
Orders are taken by phone and written by hand, the order forms are then used as receipts. This process eliminates the chance of recapturing lost business from one off orders or when regular orders stop. Mrs Hardy wants the method of taking orders to become computerised, so that with a few clicks the orders are filled out and can be printed, to be used as receipts. However she would like the system to store any businesses that take orders so that she can chase up lost business with flyers and/or promotions.
* Stocktaking
Stocktaking is currently taken on several sheets (for Bagels and fillings, drinks, and snacks) then counted as a tally, therefore there is no proper way of ensuring how the stock amounts change over the weeks. Mrs Hardy would like an aspect of the system whereby her stocktaking sheets can be transferred onto the computer, so that the computer could work out for her, what needs to be ordered, as well as what she is ordering the most of, and vice versa. Hence enabling her to see successful and unsuccessful dishes.
* Reviews
Currently there is a review every month of the most popular and least popular commodities, therefore the cost of the most popular can be increased, and the least popular can be removed. This is not totally efficient as it relies strongly on memory and is unreliable. This could be strengthened by efficient stocktaking.
* Wages
Employees are currently being paid 'cash in hand' at the end of each fortnight, therefore there is no proper record of hours worked, and they may be over or under paid.
* Employee Details
Sheets with employee details are always being miss-laid, and it takes a long time to find them. With details such as emergency numbers, addresses, employment history and information for references etc., it is imperative they are save. Mrs Hardy would like these computerised so when changes have to be made it is a lot easier.
* Finances
Finances are currently kept in a book, and all the finance books over the previous years of the business have been kept, taking up a great deal of room. Mrs Hardy would also like the finances transferred over to the computer.
SOURCE DOCUMENTS
* SOURCE DOCUMENT 1: Order Form
The filling out of this order form takes up lots of valuable time, and also keeps the customer placing the order waiting. However everything required of the order form is present. The only data that will have to be entered into the new system will be the assigned item number. When more than one of an item is required it will be entered twice, as this will be quicker. The rest of the information will be worked out by the system
* SOURCE DOCUMENT 2: Bagel and Filling stock take
I was provided with the stocktaking sheet for 'Bagels and Fillings' there are two more sheets one for 'Drinks' and the other for 'Snacks'. The Stocktaking sheet also has to be filled out by hand, and takes roughly half an hour. The new system should only require data on
OBJECTIVES OF THE NEW SYSTEM
I also discussed with Mrs Hardy want she wants from the new system, and I came up with a list of objectives, which she approved.
* Ordering
The orders should be entered directly onto the system. All the calculations should be made by the system. To make this as simple as possible it should be done by number reference to a menu. All the calculations should be made by the system. They should then be printed so they can be used as receipts.
* Customer list
Mrs Hardy would also like somewhere to store the data on their current clients; this should be linked to the order form so it does not have to be filled out each time a client makes an order. This list will also enable Mrs Hardy to re-capture lost business.
* Stocktaking
Stocktaking should require minimum input and be relatively easy to use. The system should calculate how much of each item needs to be re-ordered, and should show, by way of what needs to be ordered most, which dishes are successful/ unsuccessful
* Staff payroll
This section should only be accessible to Mrs Hardy. It should speed up the amount of time taken to work out the wages. The system should not only calculate the wages, but also include overtime. It should be simple to use, and arranged well so the completed sheets can be used as wage slips.
* Staff rota
This section should also only be accessible to Mrs Hardy. It should contain two weeks worth of running in the bar, and be coded to show who is working when, so that it can be distributed to each employee. There should be a blank rota to open each fortnight, and each rota should be saved, incase it needs to be known who is working when. It should also be possible to copy the previous fortnight if the rota is going to be the same.
* Employee details
Once again this section should only be accessible to Mrs Hardy. It should contain the same required information on each employee.
PERFORMANCE INDICATORS
So that I can be sure the system performs all the objectives to the desired standards, I have come up with the following set of performance indicators.
* The system should be simple to use, even for someone with a basic knowledge of computers or excel.
* It should be impossible to accidentally erase any formulae's or headings.
* Order forms should be simple to complete, and for normal sized orders take no more than 30 seconds to complete.
* All data entry should be so easy that each aspect off the system can be 100% accurate.
* Each Part of the system should be able to cater for additions, i.e. extra staff, additions to the menu etc.
CONSTRAINTS AND LIMITATIONS (human and physical)
The system, when in place will be will have to overcome the following constraints and limitations.
* HARDWARE AND SOFTWARE
Mrs Hardy owns a Pentium PC with 32MB of RAM and 8GB of Hard Drive. Loaded onto the computer are Windows '98 and Microsoft Excel '97. Mrs Hardy also owns a Desk Jet printer, for hard copy.
The development of the system will be split between the school network, and my own stand alone PC, with similar specifications to that of Mrs Hardy's. To transport files between home and school I will be using 3.5" floppy disks. Therefore the files must fit onto them.
The school network ____________________________________ ______________ _____________________________. The schools PC's offer user areas for each student, so files will also be stored on the schools PC's.
* USERS SKILL LEVEL
As the owner of Just Bagels Mrs Hardy will be the main user of the system. She has owned a PC for a number of years and is familiar with its basic use. She has used excel in the past to produce simple charts and graphs. However I will be giving Mrs Hardy a demonstration and be providing her with a user manual.
* The daily taking of orders and stocktaking will be carried out by each of the employees at some time, all of the employees have a basic knowledge of computers, although the stocktaking and ordering, should be simple enough for them to handle.
*
*
*
*
Michael Constantino