-The Time It Takes
- Main menu macros button for navigation to all worksheets compared to doing in manually, which can cause problem if papers are misplaced
- In the Order form use of choose function for faculty and teachers makes the system user friendly
- Be able to produce 100% accurate data for any combinations of print requirements.
- Conditional formulae to add additional charges into basic printing costs-100% accurate.
- Possible to select available printing options from buttons to minimize input errors and speed input times.
- Copy job details to a daybook then to the department book and then into the journal which will all be sorted and kept accurate at all times
- It should take 10 seconds to type the job details within a spreadsheet compared to doing it manually.
- Macros should make it a click of a button to produce printouts of record sheets.
- Possible to update books using macros in less than a second.
-Calculations □
- Accuracy of the totals when it comes to complicated calculation such as adding the total spent for each month by each department can be done in faster and easier ways with the use of formulas which are continues so does not require attention.
- When using a spreadsheet if the formula which is correct is written there is little chance of a mistake.
- When totals are passed within the sheets that correct answers are produced on the summary sheet record sheet.
- When values are picked up from a lookup table they are the correct selections
-Reliability of data entry
- Invalid entries should be detected and an appropriate error messages should display.
- The data entered in one sheet for the day book should automatically come up on to the other sheets such as the day book which would reduce the chance of a mistake
- Producing report for staff will come up in minutes, where as typing the information will take hours especially when busy
- Job loads can be detected faster as information will be up on screen rather than looking at a pile of job requirement forms.
- When needed to print the same printed material an old form can be used which was saved on the hard drive
- Data can not be lost or mist-placed as it is all saved on computer’s hard drive
- Less chance of human error as all calculations are done automatic
- The end user can view the record sheet it wants rather that searching for it through a pile of paper work
- Printed paper work can be given to the bursar and so there is a less chance of mistakes there when trying to read hand writing
- When up dating information such as new prices there is a less chance of mistakes e.g. if the staff was doing it by hand and was used to an old price and mistakenly calculated the total wrong.
ii) Qualitative criteria
Here we are referring to such factors as:
- Ease of navigation around the package. The spreadsheet used in this project will be Microsoft Excel 2000, which is one of the easiest navigation programs at the current time. The people who have some skill in using Excel will find it very easy to navigate around but those who don’t have any skill in excel will find that in my project it will be easy to navigate with the use of macros that requires a push of a button to complete certain tasks
- Attractiveness of a colour screen this could make the working environment better
- Ease of using a user guide. In this guide there must not be any use of computer jargon as although the staffs working in the department have some skill the summer staff may not and so may have problems with computer literate terms.
- Security of the data within the system – here a use of a password may have to be inserted in which only those who are authorised users can have access the password would also have to be alphanumeric
- Hardware
For my developmental work will be carried out partly on the college network on a AMD Athlon XP processor At/At compatible 261,616 KB RAM . All the files will need to fit on a 31/2 floppy disk for easy transportation between home and school. The additional work I will also be doing at home will be on a Q-tech Centaur hauls VIA Samuel 129.OMB RAM. I predict that both PCs home and colleges will be fast enough to run the material
6. Software
The reprographics department has a standalone PC that uses Windows 98. Its main use is desktop publishing at the moment it has also Microsoft word 97 for processing tasks. I have chosen excel 2000 to do my project on, so unless a good reason transpires for using an alternative program or package excel would be the first choice. In the reprographics department Excel would come as an integrated system with office 97, although I would recommend a more updated piece of software would render the task successfully. This system will be implemented using Excel 2000. This package is ideal as it includes many features, which can be used in customising the application, such as
- Abilities to link product details entered in one workbook with workbook details entered in a second workbook through a table lookup.
- Sophisticated report facilities with the abilities to format text, set margins and page layout, import a company logo if desired and preview before printing.
- Macros to automate various functions
- Abilities to protect worksheets and workbooks so that the user cannot accidentally destroy formulae or heading
- Formatting to help make it clear to the user where to enter the data
7. User requirements
INPUT
Inputting is the stage in which the computer reads incoming data. It will be essential for the new system to be user friendly and so little input as possible would be a successful system. The following are all inputs needed on the new system to meet the user requirements.
- The first input the user would be required to do will be to type in a password. This will be needed to be an alphanumeric password. The password will be required every time the user attempts to access the program. A password will be needed so that unauthorised users are not able to get access.
- The next stage of input will be the main menu here the user will select individual worksheets by mouse clicking in the appropriate command button on the worksheet. These buttons will be pressed each time the user wants to access these worksheets. The reasons I will need these buttons will be so that I can save time rather than selecting the worksheet tabs and it also makes it easier for unskilled computer users.
A very important input will be entering the job requirement this can be split up in the following
- A combo box could be used to enter the staff names or department
- A scroll bar to enter the amount of copies
- A list box to enter the paper type
- And a group box to enter the type of print side
The frequency of these inputs will be every time a new print needs logging
- Other inputs maybe mouse clicking macros buttons, which will send data to another work sheet this would needed every time a new data is inputted.
- For adding new staff the end user will select the “add new staff” command button, which will take them to the staff worksheet. Here they will need to enter the new name in an inserted row and click save. The frequency of inputting will be every time the college employs new staffs.
Problems that may arise could be that untrained operates (e.g. part time staff) using the system may have difficulties using it this is why I will be making the system simple and attractive in the layout with clear labels.
ii) Output
The output is when the information is displayed or printed and presented to the user. The following are all outputs needed on the new system;
- The day book entry, the department book and the journal transfer book all of which will be generated and can be seen onscreen so that it can be verified by the end user and printed once verified.
- The layout of the day book entry will consist of the College logo and the name ST. Mary’s college with all the job requirement of the day, staffs name, the department they work for, the work title and the quantity required. This output will be required so that staff can query any past work. This will be printed off at the end of the day.
- Each time an individual job is printed off it will be outputted to the department book which is where the money spent is totalled for each month and deducted from the department’s budget. Each department would need their own individual worksheet so that they can total they’re spending and reduce it from their budget. The layout of the book will consist of the college logo and its name on the bottom of it with a list of jobs done throughout the past month. and can be printed off twice every month so that for the reprographics department can keep a copy and so can the department.
- From the system another output will be the journal transfer book which is an output used to post the total each month this too will have the college logo with the period the information it is covering (e.g. January) and underneath it a list of departments and their details of how much is spent and allocated and with a total of all the money spent from each department for that month. This will be printed off each month.
- Other outputs on the job requirement form will be the total in each work sheet. In the order forms a total cost will be outputted, in the department book a total spent will be outputted and in the journal transfer form a total left and total spent altogether will be made.
- Spinner validations can output a data validation once the user ettempts to enter a value for example 0, this validation can appear every time the user inputs a incorrect entry
- Other outs are choose functions which for example if the user selects a faculty the system will output all the staff working for that department
iii) Processes
The process is when the data is manipulated and turned into information
The following are all the different types of process needed on the new system;
- Main menu-command buttons attached to macros. Macro is where the user records a task being preformed and attaches the task to a command button, which can then be pressed and every time the button is pressed the task repeats itself.
- Order form- cell links, which are, connected with an index functions that compare the cell link to a named range clients and it will display the item in the option box in text.
- VLookups – extra jobs required such as stapling, collating these will compare the index function with values in the extra look up table to display the correct price for this job
- Adding new staff the macros will navigate to the staff worksheet where it will insert a line ready for the end user to input the new staff.
- Returning to the order form or any other form this will be done by using Macros again which will be connecting to a command button.
- = choose function which selects processes the data so that only certain data appears in a box for example the choose function between faculty and staff the system process so that the only staff that work for that particular department appear
- macros button such as the following
- Send to department this will process al the data y filtering the data and copting and pasting it into other files
- Macros buttons for navigation to other worksheets
- Macros buttons for submitting these copy and past relevant data
This will be used for the quantity needed for each print it will be given a cell link and then the index function will be used to display the item
This will be used for the type of print needed it will be taken from a named range and given a cell link where by a index function will be used to display the item in alphabetic form.
- Group box
-
This is will be used for whether the staff require the printed details colleted or stapled
This will be used for the name and the department in this case a choose function also be used this is where from the department box an option can be picked which will only display the staff working in that department
8) Design
System over view
The system will be based around the
- Two order forms - order form 0_20 -order form 20_200 this is because the price range for each is different and so will need a different workbook for each
- A daybook
- The department books for only three subjects -health & social care
- - performing arts -science
- and a journal transfer
The order form which will contain the option lists will collect together data which will generate a total cost of a print job, this will then go on into the daybook and a list of jobs will be created, and excel will be then able to filter this data into the department books where a total for each month will be created so that in the journal file a deduction of the budget can be made. In addition, a front end menu will enable the user to choose which tasks they wish to perform e.g. enter order form 0_200.
On the next pages diagrams will show the designs and functions of each workbook and their connections.
- Main menu
This sheet will act as the front end and will be selects automatically using an Autoexec macros when the workbook is loaded. It will have four macros options which are as follows:
- Order form 0-20
- Order form 20_200
- Day book
- Department book science, health and social care and performing arts
- Journal transfer
The macros that will perform these tasks will be something like the following
Select order form 0_20
End recording
The colour of the writing for the buttons connected to the macros is colour coded to make the system user-friendly.
ii) Order Form 0-20, 20_200
The order forms workbooks will consist of many option lists so that I can generate a price in order these will include the following
- Names
- Department
- Work title
- Date submitted
- Date required
- Paper type
- Quantity
- Print option library
- Price
- Total
The combo box for the department’s input range will come from the named range called faculty the staff names will be connected to a choose function so that every time a department is chosen all the options in the staff name will be all those who work in that department. The work title will need to be typed in to the computer the requirement data will too need this. I have chosen to display the print option in a list box’s there is only going to be three options – paper special effects, and card this will also come from an named range called paper type in the details sheets. The quantity will be displayed in a spinner box as this will be best for this type of option, the same would apply to the no of masters. The print side details and the extras will be displayed in a group box. The print side details needs to be connected to the print type
iii) Daybook entry
In the day book there will be the following titles
- Completion date
- Staff name
- Department
- Work title
- Quantity
- Total cost
To fill in the details under the headings relevant information will be copied from the order forms using macros. . There will also be other macros within the day book which are as follows
- Clear Daybook
- Print Day book
- Go to order forms
- Send to department book, which will send all the information on to the department book this will be further explained in the department book.
I will also be putting a logo with the address of the college and yellow colouring in the heading and in one of the rows so staff that do not get mixed up with the department book documents or any other documents in the a similar format.
iv) Department books
In the department book there is quite a complicated procedure whereby I need to take the information from the daybook and filter them accordingly to the department book, in this procedure I have used an area within the detail worksheet to help me complete the task. The way I am going to do it will be by using macros that copy the information in the daybook into the filter position in the details work sheet and filter each department one by one and copies the information into department worksheet. I will be doing this for all of the department which are health and social care, performing arts and science.
Within the department I have chosen to keep the title same as the day book as this makes it less complicated and which create a set method of report after. I have also in this system created a total in the top corner so that when teachers ask what the balance of that month the staff in the department will not have to search far.
v) Journal transfer
In the journal transfer the procedure is quite simple in finding the total here I have also kept the same formats as the rest of the sheets but with a blue colour code. I have also left the table in the same format as the one in the samples (see specification). In the amount spent the formulae I have used is
=the cell which contains the total for that department
In order to find the amount left I have deducted the amount spent from the amount allocated
=amount spent-total allocated will give me my total left,
9) Test strategies
This part of the design process is where I will explain the different types of testing available and why I are going to use them in my project. I will first begin explaining the three different types of testing and then give as many examples as possible that will relate to my project.
The test strategy will include
- Unit testing which involves testing only those items that are functions within the worksheet which does not have any connections with any other worksheet. E.g. testing each macro under different circumstances.
-
Integration testing this involves all the functions that connect two or more worksheets together e.g. does the password screen permit/reject access as it should?
- User testing simply involves the end-user using the system and commenting accordingly, it establishes whether the system meets the end-user’s requirements.
Test Plan
The following tests will be preformed
The following describes all the unit testing
