Engineering Hours Accounting System Essay

excogitationAvitronics is a medium sized engineering comp some(prenominal) that produces components for the aerospace industry. Employees keep span of the hours they work on different projects and at the conclusion of the workweek numeral in a timecard with this development on it. The comp whatsoever back tooth cook up to roughly 12 contracts a year individually(prenominal)(prenominal) of these contracts is appoint a Job-Code.The Current SystemAt present each employee keeps class of their hours victimisation their own corpse. This could be as in getal as a rough t hitlyy on a scrap of paper. At the end of the week, the employees fill their hours into a formal timecard, which is handed in and t aloneied by a clerk.Objectives of The New SystemMr. Salomon (the chief accountant) would interchangeable a system with information entry forms so that each employee can input their own hours into the system at the end of each week. He would like to conf give pull-down menus for the line of reasoning-codes, so that clumsy engineers arriveing complicated codes do non make mistakes. mins should be rounded to the neargonst soak up-hour. information entry should be quick and simple. to each one individual employee should non be able to see the hours worked by everyone else.The information should be sent to a infobase from Mr. Salomon can produce cogitations. He wants to be able to get a key that shows how many hours each employee full treatment each week (these should be sorted by employee egress). Mr. Salomon would also like to have a representical constitution, which shows the total yield of hours worked each week against each contract.He would like to fail a fresh immature system at the start of each year.Mr. Salomon would also like an gentle to understand expendr guide, which should include complete instructions for a dilettante, victimisation no jargon.Performance IndicatorsMy solving go forth have to* delay all employees seeing other employees hours.* Be simple to rehearse, and not fellate employees time.* keep errors wherever possible by using cogent evidence and jazz band-boxes.* provide Mr. Salomon to force reports (with graphs) from enciphered information.* Include a template for Mr. Salomon to re apparel the system at the start of the year.* Provide Mr. Salomon with a User Guide designed to assist novice substance ab drug users when they argon using the solution.* accurately calculate the total hours for each employee per week, stainless to each pull out hour. Accurately calculate the take of hours worked on each contract per week, accurate to each quarter-hour.If all of these criteria are met then my solution impart be successful.DesignChoice of SoftwareA Spread airplane is the best solution to the problem because I can use the software well up already and I know how to solve the problem using this software. I have chosen to use Microsoft pass because it is the most common forgat her of spreadsheet software in general use. I could use an equivalent spreadsheet encase from a different company, but this may not be matched with Mr. Salomons computer. Features of Excel that make it ideal for solving the problem* top executive to create a blank template* Pivot tables to create sophisticated reports with graphs* Formatting to help make it clear to the user where to move into entropy* Macros to automate various functions* Macros which will execute automatically on coal scuttle and closing workbooks* Validation and hold dearion of carrels to prevent tampering and errorsSystems OverviewMy system will be based on one workbook, with three sheets (excluding reports). The Hours access sheet will be the main input form, where employees will enter the hours they have worked, on what days and on which contract. The Admin Options Sheet is where the Administrator changes Contract total and groups, he/she also dos the week beginning encounter. The informationb ase sheet is where reports and charts will be produced form.Detailed DesignFollows on the next 6 pagesWorkbook and Sheet interventionHours Entry Sheet This sheet is designed to let the employees enter their hours in a quick and simple way. It is important to keep it simple so that employees do not become disgruntled and frustrated with the system. This sheet has a simple colour lineation that distinguishes input carrels from mobile phones where input is not indispensable. jazz group boxes are utilise to keep certain aspects simple and to prevent mistakes. Buttons are used to execute bigs that clear the sheet and transfer relevant info to the spreadsheet. As hours have to be recorded by the closest quarter-of-an-hour, I have used a ceiling formula in the total cells to prevent mistakes being make when info is transferred to the entropybase.Admin Options Sheet This sheet contains any options the administrator may need to set concerning the system He/She can add/remove con tracts and add/remove groups of workers. At the start of each working week He/She has to set the Week Beginning date that is needed throughout the system, it is essential they enter this date correctly. The colour scheme for this sheet is simple, every cell is white, input cells have borders to distinguish them form non-input cells. This sheet contains two buttons that execute macros Protect Workbook This macro haves up the treasure workbook dialog, where the administrator can protect the whole workbook, with the exception with cells that are not Locked (input cells). Start New Year This macro erases all the information in the workbook, and prepares the system for a in the raw year. A warning box automatically pops up when this button is leafed to warn the user that they will be deleting all entered data from the previous year. This is to prevent all the data being withdraw accidentally.Database Sheet This sheet contains all the data that has been transferred from the Hours Entry Sheet. It contains Employee , Hours Worked for each employee. Job Code, Hours Worked for each contract.From this data, an employee report can be created, this is a report listing all the employees, and the physical body they have worked in a given over week. From this data, a contract report can be created this is a report listing all the contracts and the spell of hours worked on them for a given week. From this data a contract graph can be created, this is a graph showing the number of hours worked for a given contract over the past month (4 weeks). These outputs will need to look professional and be competent for their purpose.Data entry and ValidationThese are the fields in which data will be entered into my systemLast Name The employee will theatrical role this in. I chose this method, as there is no sightly alternative.Employee This will be theatrical roled in by the employee. I chose this method because there is no reasonable alternative. Employee number is a four-digit number. This cell will be validated so that numbers bigger than 9999 are not accepted also school text is prevented from being entered. I use this organisation so that problems do not occur further on the process, when reports sorted by employee number are being produced.Group The user will select this via a combo box this ensures no typographical errors are made when typewriting it in. I am choosing to use a drop down box as there are a few(prenominal) options for the employee to choose from.Job-Code This will be selected from a drop down box to prevent typographical errors being made. If it were a plain cell, with no validation or input aids, if an error was made it could cause unwanted consequences throughout the system.Monday Employees enter their hours by typing the number in the correct cell. any number they enter will subsequently on be rounded up to the nearest quarter hour, by the use of a ceiling formula. The validation on this cell will be that it ha s to be a number between 0 and 20.Tuesday Employees enter their hours by typing the number in the correct cell. all number they enter will afterwards be rounded up to the nearest quarter hour, by the use of a ceiling formula. The validation on this cell will be that it has to be a number between 0 and 20.Wednesday Employees enter their hours by typing the number in the correct cell. Any number they enter will later be rounded up to the nearest quarter hour, by the use of a ceiling formula. The validation on this cell will be that it has to be a number between 0 and 20.Thursday Employees enter their hours by typing the number in the correct cell. Any number they enter will later be rounded up to the nearest quarter hour, by the use of a ceiling formula. The validation on this cell will be that it has to be a number between 0 and 20.Friday Employees enter their hours by typing the number in the correct cell. Any number they enter will later be rounded up to the nearest quarter h our, by the use of a ceiling formula. The validation on this cell will be that it has to be a number between 0 and 20.Saturday Employees enter their hours by typing the number in the correct cell. Any number they enter will later be rounded up to the nearest quarter hour, by the use of a ceiling formula. The validation on this cell will be that it has to be a number between 0 and 20.Sunday Employees enter their hours by typing the number in the correct cell. Any number they enter will later be rounded up to the nearest quarter hour, by the use of a ceiling formula. The validation on this cell will be that it has to be a number between 0 and 20.User support to OperationUser Aids to Operation that I have used are combo boxes, and buttons that execute useful macros. Combo boxes assist the user with entrance data when there are only a few options to choose from. They also help prevent mistakes from being made when typing out something complicated. For example in my system, the user uses a combo-box to select a job code, if they had to type every job code in it is more than than likely that every week several mistakes would be made. By using combo-boxes I have eliminated the probability of this occurring. By using buttons that execute macros I have enabled the employees to enter their hours in a simple entry form. If I did not use macros then each individual would have to type hours into the database, this could cause a huge number of problems for the company.ProtectionSheet 1, Hours Entry SheetThe adjacent cells on this sheet will NOT be protectedC6, Last NameC5, Employee C10C16, Mondays Hour Entry CellsD10D16, Tuesdays Hour Entry CellsE10E16, Wednesdays Hour Entry CellsF10F16, Thursdays Hour Entry CellsG10G16, Fridays Hour Entry CellsH10H16, Saturdays Hour Entry CellsI10I16, Sundays Hour Entry CellsThese cells will not be protected because when I protect the sheet, I want them to be editable. Eg the employees need to enter data into them.MacrosTransfer_Macr o This macro will be put to death from a button on the Hours Entry Sheet. The macro transfers ask data from this sheet into the database. After it has transferred the data it will return the user to the Hours Entry Sheet, where they can exit the spreadsheet when they are ready. If the transfer is ideal successfully then a message box will popup facial expression Transfer Successful.Create_Employees_ notify This macro will create a report on the employees. It will copy selected data from the database, paste it into a new sheet and format it appropriately. The report will show the employee number, last name, hours worked for each week for each employee stored in the database.Create_Contracts_Graph This macro will use selected data from the database to create a graph showing how many hours are worn out(p) on each job code over a breaker point of weeks. The graph will show all of the job-codes and all of the data astir(predicate) each job code that is stored in the database.Au to_Open This will execute automatically when the spreadsheet is opened, it shows the Hours Entry Sheet and clear all of the white input cells. It will select the Employee input box ready for the user to begin.Clear This macro clears all of the white input cells in the Hours Entry Sheet. If the employee enters a few mistakes or realises he has entered totally wrong data, instead of having to call off every piece of data separately he/she can precisely click the button that executes this macro.Protect_Workbook This macro will be executed via a button on the Admin Options page, this will hide all menus and the taskbar and take the user to the Hours Entry Sheet. It will also run the Clear macro so that the Hours Entry Sheet is always blank when it shows up.Start_New_Year This macro erases all data from the database ready for the system to begin a new year. The employees and job codes will stay on the admin options page as it would be untenable to assume that all the jobs were fi nished at the end of the last year.Data Flow Diagram runneling analyse StrategyThis is what my solution is required to do, as outlined in the Performance Indicators section of the Analysis* Prevent all employees seeing other employees hours.* Be simple to use, and not waste employees time.* Prevent errors wherever possible by using validation and combo-boxes.* Allow Mr. Salomon to create reports (with graphs) from entered data.* Include a template for Mr. Salomon to reset the system at the start of the year.* Provide Mr. Salomon with a User Guide designed to assist novice users when they are using the solution.* Accurately calculate the total hours for each employee per week, accurate to each quarter hour. Accurately calculate the number of hours worked on each contract per week, accurate to each quarter-hour.Here is an outline of the screens I will have to carry out on my spreadsheet* Does my system open correctly?* Ensure that the Database and Admin sections are hidden from the e mployees.* Ease of Use, as lilliputian complications as possible.* Tests of Validation (effects of incorrect/extreme data entry)* Tests of Protection* Accurate calculation of hours* Does my spreadsheet cause problems after closing?Test PlanThe following mental testings will be performedBasic Function Tests1. Open my spreadsheet.2. Add data as outlined in test data set 1.3. Test the Submit Hours button.4. Add data as outlined in test data set 2.5. Test the Clear Form Button.6. Add data as outlined in test data set 3.7. Test the Submit Hours button.8. Test the Exit System button.9. Reopen the spreadsheet.10. Try entering data from test data set 4.11. Try entering data in a locked cell (eg. The cell containing the word Group and the cell containing the word Totals).Admin Function Tests12. Open my spreadsheet.13. Test Admin Options button, enter password from test data set 5.14. Test Admin Options button, enter password from test data set 6.15. Test the submit New Employee button. En ter the data as outlined in test data set 7.16. Select cell F19, Test the Delete Job Code button.17. Select cell B11, click the Delete Job Code button.18. Test the Enter New Job-Code button, enter the data as outlined in test data set 8.19. Test the Enter New Job-Code button.20. Test the Create Contract-Chart button.21. Test the Employee Report button.22. Test the Change System Password button. Enter the password as outlined in test data set 9.23. Test the Protect System button.24. Test the Admin Options button. Enter the password as outlined in test data set 6. Error massage should appear.25. Test the Admin Options button. Enter the password as outlined in test data set 9.26. Test the Change System Password button enter the pilot password again as outlined in test data set 6.27. Test the Start New Year button.28. Click consign then Exit please do not save any changes to the spreadsheet.Jonathan Stamp ICT Excel Coursework Page 1 of 17Jonathan Stamp ICT Excel Coursework Page 1 of 17