MS-EXCEL APPLICATION PROJECT
Overview
Developing an MS-Excel application moves students from an advanced level of understanding to that of a first level expert. This skill-set will be extremely valuable when students are applying for jobs and they should be sure to mention it during their employment interviews. (This is a BIG selling feature for potential employers.)
All companies use some type of spreadsheet application in the day-to-day operation of their Accounting/Finance departments. This can be as a stand-alone tool (e.g., mortgage calculator; amortization schedule), or — particularly for smaller companies — a significant part of their financial systems (e.g., payroll application; inventory sub-ledger).
There will be several students assigned to each team for this application development exercise (students are to form their own groups). Each student in the team will receive the same grade for the assignment. The student teams will be self-regulating, which means that the group will determine the allocation of work and appropriate quality control standards. Groups can decide to reject a team member if they are not ‘pulling their weight’. If this happens, the rejected student will either complete the Excel application by themselves, or receive a grade of zero for the project.
The purpose of this assignment is to create a payroll processing application that calculates employee net pay, after deducting certain statutory claims by the federal government. The application should be menu driven and allow for input (employees hours), processing (statutory deductions) and output (various reports). It should also have functionality that allows users to (i) create, (ii) edit and (iii) delete employee records.
Note that the application should be designed for someone (a payroll clerk) who has no knowledge of MS-Excel. It is your job to create an application that is simple and intuitive to use.
THE ASSIGNMENT
To manage the scope of this project, certain parameters (i.e., limiting assumptions) are used to develop the application. These are described below.
Suggested Task List
Your assignment should include the following tasks.
- Create a MASTER data table of employees showing the following fields:
- Last name
- First name
- Employee Number (use 1, 2, 3, etc., in sequence)
- Hourly pay rate: there are only two pay rates: lowest rate = $30/hour; highest rate = $50/hour (Note that we are assuming all employees are salaried. These hourly rates translate into annual fixed amounts paid — i.e., based on an 80-hour work week: $30/hour = $62,400 per year and $50/hour = $104,000 per year.)
You should populate your employee data table with 30 records. If you find it difficult to think of 30 employee names, simply use the rosters for any professional sports team (e.g., the Vancouver Canucks; Toronto Raptors or Blue Jays). Remember to ensure that the cells/fields have been protected and validated to ensure:
- Authorized access (password protection). All applications will use the same password(s): ‘4360’, which will give users permission to enter the payroll program.
- Data validation (defining field characteristics: numeric, alpha, data limits: e.g., <= 80 hours bi-weekly).
- Create a data table showing the statutory deductions (CPP, EI and income tax). These should be based on the current rates used for processing Canadian payrolls for 2017. This information can be obtained from any Canada Revenue Agency (CRA) office, or the CRA website. (Alternatively, students can develop a sub-routine that calculates tax withholdings based on the requirements of the Income Tax Act.). For convenience, assume that all employees have a claim code of ‘1’ on their TD1 form, which means that they only claim the basic personal exemption for tax purposes.
- Create a means for processing the following payroll deductions from an employee’s gross pay for 26 bi-weekly pay periods (using 2017 rates):
- CPP deductions
- EI deductions
- Income tax deductions (based on TD1 submitted, assuming only the basic personal exemption is claimed)
You should populate approximately 3 or 4 bi-weekly pay periods with data. However, leave some of the pay periods without data so that the application can be tested during your in-class presentation.
- Consolidate the results for each pay period, for up to 26 bi-weekly pay periods – i.e., provide year-to-date amounts.
- Generate reports showing:
- Employee net pay slip (a separate slip for each employee)
- Summary of gross payroll for the pay period and in total (YTD).
- Summary of net payroll for all employees for the pay period and in total (YTD).
- Summary of deductions by type: CPP, EI, tax for the pay period and in total (YTD).
- Create a means for adding, deleting and editing employee information in the data table.
The payroll applications will be evaluated based on completeness and accuracy of processing, ease of navigation and the usefulness of the reports generated.
Some Common Problems Encountered:
Based on experience with this project, listed below are some of the most common problems that students have had to address when developing their Excel-based payroll application.
- Working as a group on the application can create problems with version control.
- Calculating EI and CPP deductions when the annual limits have been reached.
- Using tax tables to calculate withholding tax, vs. using formulas, or a combination of both.
- Adding an employee and making them active in a pay period (i.e., active/inactive status)
- Ensuring that the screen navigation is intuitive (i.e., easy to follow).
- Screen Appearance: hiding rows, columns, tabs and the Excel menu/ribbon.
- Create a professional look, such as a corporate logo and/or screen background
- Incorporating graphics in reports – e.g., YTD amounts as bar charts
Suggested Task Allocation
A suggested allocation of tasks for team members is as follows.
- Overall design of application (flow diagram) – ALL
- Application testing and documentation (QA) – 1 member
- Database design and application development – 2 members
- Power Point presentation of payroll application – 1 member
This is only a suggested task allocation and team members can choose to delegate work as they consider appropriate.
THE DELIVERABLES
The following information (4 deliverables) must be submitted to the course instructor for evaluation by e-mail (electronic files) and/or in-person (hard-copy files).
| Item | Deliverable | Format |
| 1 | Application flowchart | Hardcopy file |
| 2 | Quality Assurance testing | Electronic and hardcopy files |
| 3 | MS-Excel Payroll application | Electronic file |
| 4 | Power Point presentation | Hardcopy file |
Application Flow Chart
The flow diagram should be the starting point for developing your payroll application. Like using a blueprint when building a house, it allows you to more efficiently construct your application. The flowchart will show the overall schema, using appropriate symbols, to outline the sequence of steps required to process the employees’ bi-weekly payroll.
Many students mistakenly begin their application development by creating a number of worksheets and then attempt to link them using a trial and error method. This is a time consuming and inefficient way of working on this project. It is recommended that students dedicate time in thinking about the logic and sequencing of the application by using a flow diagram before they begin using the Excel functionality. Inherent in this process is creating a user-friendly navigation for the application – in other words, can an elementary school student easily work with the application, without having to know anything about Excel?
Please refer to the following web link for some examples of common flowcharting symbols:
Quality Assurance (QA) Testing
The QA part of the deliverables should document the specific tests that you performed on the payroll application, as well as the results of those tests. The format for documenting this testing should include the following elements:
- The feature being tested (e.g., data validation of employee time records to ensure that they do not exceed 80 hours in a bi-weekly pay period).
- The testing of that feature, along with the results (e.g., enter alphabetic data and numeric data that exceeds 80 hours). A screen shot of the error message/warning should be provided with this QA test.
- The status of the test: PASS or FAIL.
In addition, your QA testing should include the following:
- A sample of the reports generated (provide screen shots of reports created using the application).
- A sample of various scenarios where employee deductions for EI, CPP and taxes are manually calculated and the results are compared to the application’s calculation of these amounts for both accuracy and completeness.
The most important aspects of QA testing are completeness and documentation. In other words, be thorough in describing what you are testing and document the results of that testing for my review. (Please note that this is standard practice for all ‘beta’ applications.). You should provide at least 30 test scenarios as part of your QA testing for the payroll application.
MS-Excel Payroll Application
In overview, the payroll application should be designed to perform three basic functions:
- Enter employees’ bi-weekly hours
- Generate pay slips and summary reports
- Manage employee data (add/delete/edit)
Some of the MS-Excel functionality that will be particularly useful to students when developing their payroll applications will be the following (which will be discussed in class).
- Cell/worksheet protection
- Data validation
- Consolidations
- Developer toolbar
- Recording/assigning macros
- Forms control
- Pivot tables/charts
Remember to ensure that your application is user-friendly and that the on-screen navigation is intuitive (i.e., easy to follow). Please note that the application should be driven primarily by using macros. Students may use the VBA programming language if they wish, but this is not required for the assignment. Also, note that students will likely spend a significant amount of time on this project and therefore should begin working on it as soon as possible. It is expected that while students may at times feel ‘frustrated’ by the complexity of the exercise, they will also learn a great deal about using Excel. It is expected that the total time to complete this project will be approximately 40 to 60 hours, depending on the capabilities of the team members.
PowerPoint Presentation
The presentation should be about 15 minutes in duration. The presentation should include the following components:
- Overview of the payroll application, based on your flow diagram. Discuss the flowchart design and its logic
- Discussion of the difficulties encountered when developing the payroll application and how they were resolved. Specific explanations are required for how each group handled the following common problems:
- Calculating income tax
- CPP/EI maximum deduction limits
- Employees starting during the year
- Employees terminating during the year
- Adding/deleting/updating employee records
- Show a screen shot of the main page of the application. Conduct a test demonstration of your payroll application, including:
- adding an employee
- deleting an employee
- modifying an employee
- updating hours for a pay period
- producing reports (on-screen only)
- As part of the test demonstration, a member of the audience will be randomly selected to perform certain tasks using the payroll application (i.e., end-user stress testing).
- Some common presentation mistakes:
- Not appearing professional. This typically occurs because insufficient time is spent rehearsing the presentation.
- If you are not speaking, please sit down! There is no need to have the entire group standing.
- Be careful to ensure that all PowerPoint graphs and other illustrations can be clearly seen from the far end of the classroom.
- Poor choice of font colors. Keep it simple: use black font on a white background for slides.
- Font size on PP slides is too small. Use at least a 36 cpi font (it’s a lot easier on the eyes).
- When using a laser pointer, don’t ‘jitterbug’ — hold the laser pointer at a position on the screen and then begin explaining.
- Not wearing business attire — make sure all presenters are appropriately dressed.
- Poor speaking voice (i.e., volume/speed). You should be audible and not speaking too fast.
- Not maintaining eye contact with audience. Do not read from a script, cue cards, or PP.
- Poor time management. If 15 minutes is assigned, then use 15 minutes (not more or less).
- Not responding to questions. Remember, you are the expert!
Note: You are strongly advised to test your application before the presentation, using the classroom equipment, including the computer and projector. This is to ensure that there are no ‘surprises’ on the day you demonstrate your payroll project for the class.
THE EVALUATION
Group projects will be evaluated based on the following method.
Marks (A)
| Expectations | Mark |
| Exceeds expectations | 3 |
| Meets expectations | 2 |
| Below expectations | 1 |
Weighting (B)
Weighting factors will be assigned to the various deliverables, as follows.
| Component | Weighting Factor |
| Application flowchart | 15 |
| Quality Assurance testing | 15 |
| MS-Payroll application | 55 |
| Power Point presentation | 15 |
| TOTAL | 100 |
The following criteria will be used to evaluate the components of the project.
| Component | # | Criteria |
| Application flowchart
(4 criteria)
|
1 | Appropriate symbols used |
| 2 | Logical design | |
| 3 | Sufficiently detailed description | |
| 4 | Complete (addresses requirements) | |
| Quality Assurance testing
(4 criteria) |
5 | 30 tests (minimum) |
| 6 | Tests calculation of CPP, EI, tax | |
| 7 | Pass/fail noted | |
| 8 | Screen shots documented | |
| MS-Payroll application
(10 criteria) |
9 | Password protection (to enter application) |
| 10 | Appearance (professional — consider creating a company logo) | |
| 11 | Navigation (easy to use; intuitive) | |
| 12 | Data validity checks (for all input fields) | |
| 13 | Pay slip generation (complete/accurate) | |
| 14 | Reporting functions (per requirements) | |
| 15 | Add/delete/edit capability | |
| 16 | Calculation accuracy (tax, CPP, EI) | |
| 17 | Input screens (clean/uncluttered) | |
| 18 | Durability (did it ‘crash’?) | |
| Power Point presentation
(3 criteria) |
19 | Clear (understandable) |
| 20 | Addresses requirements (per course outline) | |
| 21 | Passes user ‘stress test’ |
Because of the significant weighting allocated to the MS-Payroll application (55%) each criterion for this component will be evaluated based on the following grading schema.
| Grading | Mark |
| Fully meets criterion | 1.0 |
| Does not fully meet criterion | 0.5 |
| Does not meet criterion | 0.0 |
The mark obtained will be compared to the maximum available marks and multiplied by 3 (i.e., exceeds expectations). For example, consider the following evaluation of the MS-Payroll application component.
| Component | Criteria | Marks
Obtained |
Maximum Marks Available |
| MS-Payroll application
(10 criteria) |
Password protection (to enter application) | 1.0 | 1.0 |
| Appearance (professional — consider using a company logo) | 1.0 | 1.0 | |
| Navigation (easy to use; intuitive) | 0.5 | 1.0 | |
| Data validity checks (for all input fields) | 1.0 | 1.0 | |
| Pay slip generation (complete/accurate) | 0.0 | 1.0 | |
| Reporting functions (per requirements) | 0.0 | 1.0 | |
| Add/delete/edit capability | 1.0 | 1.0 | |
| Calculation accuracy (tax, CPP, EI) | 1.0 | 1.0 | |
| Input screens (clean/uncluttered) | 1.0 | 1.0 | |
| Durability (did it ‘crash’?) | 1.0 | 1.0 | |
| TOTAL | 7.5 | 10.0 |
Therefore, 7.5/10 X 3 = 2.3 (rounded)
Calculation (A X B)
The overall grade for the project will be based on the sum of the marks for each deliverable, multiplied by the appropriate weighting factor. Note that greater weighting has been allocated to the MS-Payroll application (Excel file) because of the significant effort required to complete this part of the assignment.
The percentage calculated will be based on the ‘awarded marks’ relative to the maximum ‘marks available’ (i.e., 300 maximum marks available). The maximum marks available are calculated as follows:
| Expectations X Weighting Factor | Maximum Marks Available |
| 3 X 15 = | 45 |
| 3 X 15 = | 45 |
| 3 X 55 = | 165 |
| 3 X 15 = | 45 |
| Maximum = | 300 |
The percentage calculated using the awarded marks relative to the maximum marks available will then be applied to the 20% of the course grade relating to the assignment. For example, if the awarded marks are calculated as follows:
| Deliverable = | Flowchart | QA Testing | Application | Presentation | Totals |
| Marks (A) = | 3 | 1 | 2.3 | 3 | 10 |
| Weighting (B) = | 15 | 15 | 55 | 15 | 100 |
| A X B = | 45 | 15 | 127 | 45 | 232 |
Then the grade for the project is determined as follows:
232/300 X 20 = 16 (rounded) marks for the payroll application (i.e., 16/20 = 80%).
New material for FALL 2012:
- Overview of MS-Excel functionality: tabs; ribbons; drop down menus
- Developing scenarios and sensitivity analysis using cost accounting models
- Linking to external data sources (database; Internet; MS-Excel data tables)
- Managing databases that have poor data definitions (LEFT; RIGHT; TRIM; LEN; MID)
- Using pivot tables to determine causal relationships for regression analysis
- Video on hypothesis testing (Kahn Academy) for regression analysis output reports