We use cookies to give you the best experience possible. By continuing we’ll assume you’re on board with our cookie policy

Engineering Hours Accounting System Essay

Sorry, but copying text is forbidden on this website!

Introduction

Avitronics is a medium sized engineering company that produces components for the aerospace industry. Employees keep track of the hours they work on different projects and at the end of the week turn in a timecard with this information on it. The company can take up to roughly 12 contracts a year; each of these contracts is assigned a ‘Job-Code’.

The Current System

At present each employee keeps track of their hours using their own system. This could be as informal as a rough tally 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 tallied by a clerk.

We will write a custom essay sample on Engineering Hours Accounting System specifically for you

Order now

Objectives of The New System

Mr. Salomon (the chief accountant) would like a system with data entry forms so that each employee can input their own hours into the system at the end of each week. He would like to have pull-down menus for the job-codes, so that clumsy engineers entering complicated codes do not make mistakes. Hours should be rounded to the nearest quarter-hour. Data entry should be quick and simple. Each individual employee should not be able to see the hours worked by everyone else.

The information should be sent to a database from Mr. Salomon can produce reports. He wants to be able to get a report that shows how many hours each employee works each week (these should be sorted by employee number). Mr. Salomon would also like to have a graphical report, which shows the total number of hours worked each week against each contract.

He would like to start a fresh new system at the start of each year.

Mr. Salomon would also like an easy to understand user guide, which should include complete instructions for a novice, using no jargon.

Performance Indicators

My solution will have to:

* 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.

If all of these criteria are met then my solution will be successful.

Design

Choice of Software

A Spreadsheet is the best solution to the problem because I can use the software well already and I know how to solve the problem using this software. I have chosen to use Microsoft Excel because it is the most common piece of spreadsheet software in general use. I could use an equivalent spreadsheet package from a different company, but this may not be compatible with Mr. Salomon’s computer. Features of Excel that make it ideal for solving the problem:

* Ability to create a blank template

* Pivot tables to create sophisticated reports with graphs

* Formatting to help make it clear to the user where to enter data

* Macros to automate various functions

* Macros which will execute automatically on opening and closing workbooks

* Validation and Protection of cells to prevent tampering and errors

Systems Overview

My system will be based on one workbook, with three sheets (excluding reports). The Hours Entry 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 numbers and groups, he/she also sets the week beginning date. The database sheet is where reports and charts will be produced form.

Detailed Design

Follows on the next 6 pages………

Workbook and Sheet Discussion

Hours 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 scheme that distinguishes input cells from cells where input is not required. Combo boxes are used to keep certain aspects simple and to prevent mistakes. Buttons are used to execute macros that clear the sheet and transfer relevant data 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 made when data is transferred to the database.

Admin Options Sheet – This sheet contains any options the administrator may need to set concerning the system He/She can add/remove contracts 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 opens up the protect 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 data in the workbook, and prepares the system for a new year. A warning box automatically pops up when this button is clicked to warn the user that they will be deleting all entered data from the previous year. This is to prevent all the data being removed 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 number they have worked in a given week. From this data, a contract report can be created; this is a report listing all the contracts and the number 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 suitable for their purpose.

Data entry and Validation

These are the fields in which data will be entered into my system:

Last Name – The employee will type this in. I chose this method, as there is no reasonable alternative.

Employee # – This will be typed 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 text is prevented from being entered. I use this validation 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 typing it in. I am choosing to use a drop down box as there are few 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 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.

Tuesday – 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.

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 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.

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 Aids to Operation

User Aids to Operation that I have used are combo boxes, and buttons that execute useful macros. Combo boxes assist the user with entering 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 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.

Protection

Sheet 1, Hours Entry Sheet

The following cells on this sheet will NOT be protected:

C6, Last Name

C5, Employee #

C10:C16, Monday’s Hour Entry Cells

D10:D16, Tuesday’s Hour Entry Cells

E10:E16, Wednesday’s Hour Entry Cells

F10:F16, Thursday’s Hour Entry Cells

G10:G16, Friday’s Hour Entry Cells

H10:H16, Saturday’s Hour Entry Cells

I10:I16, Sunday’s Hour Entry Cells

These 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.

Macros

Transfer_Macro – This macro will be executed from a button on the ‘Hours Entry Sheet’. The macro transfers required 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 completed successfully then a message box will popup saying ‘Transfer Successful’.

Create_Employees_Report – 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 spent on each job code over a period of weeks. The graph will show all of the job-codes and all of the data about each job code that is stored in the database.

Auto_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 delete every piece of data separately he/she can simply 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 unreasonable to assume that all the jobs were finished at the end of the last year.

Data Flow Diagram

Testing

Test Strategy

This 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 tests 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 employees.

* Ease of Use, as little 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 Plan

The following tests will be performed:

Basic Function Tests:

1. 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 Tests:

12. 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 ‘Enter New Employee’ button. Enter 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 original password again as outlined in test data set 6.

27. Test the ‘Start New Year’ button.

28. Click ‘File’ then ‘Exit’ please do not save any changes to the spreadsheet.

Jonathan Stamp – ICT Excel Coursework Page 1 of 17

Jonathan Stamp – ICT Excel Coursework Page 1 of 17

How to cite this page

Choose cite format:

Engineering Hours Accounting System. (2017, Aug 01). Retrieved from http://blogram.org/engineering-hours-accounting-system-essay

We will write a custom essay sample onEngineering Hours Accounting Systemspecifically for you

Our customer support team is available Monday-Friday 9am-5pm EST. If you contact us after hours, we'll get back to you in 24 hours or less.

By clicking "Send Message", you agree to our terms of service and privacy policy. We'll occasionally send you account related and promo emails.
No results found for “ image
Try Our service
online

Hi, I am Sara from Studymoose

Hi there, would you like to get such a paper? How about receiving a customized one? Check it out http://goo.gl/CYf83b

online

Hi, I am Sara from Studymoose

Hi there, would you like to get such a paper? How about receiving a customized one? Check it out http://goo.gl/CYf83b

image

Your Answer is very helpful for Us
Thank you a lot!

Sitemap