Free Essay

Analysis of Tax Withholding

In:

Submitted By goldenpeach
Words 4382
Pages 18
Smith
Consulting

4/16/2010 |

Business 170 – CVS2 - Final Project | Analysis of Current Withholding Tax Policy | Andrea Smith - 7018051 |

Table of Contents

Executive Summary 3
Introduction 4
Analysis and Discussion 5
Recommendation 11
Appendix 1 - Result of Access Query – Annual Management Salaries 12
Appendix 2 - Result of Access Query – Annual Office Wages 13
Appendix 3 - Result of Access Query – Annual Production Wages 14
Appendix 4 - Excel Worksheet – Annual Management Salaries 16
Appendix 5 - Excel Worksheet – Annual Office Wages 17
Appendix 6 - Excel Worksheet – Annual Production Wages 18
Appendix 7 - Excel Worksheet – Employee Tax 20
Appendix 8 – Comparison of Tax Withholding Policy Graphs 24

Executive Summary

This report is a summarization of the results acquired utilizing the current tax withholding policy of Smith Consulting for the 2009 taxation year. It will show the classification of employees into different departments, as well as for the company as a whole, and their annual income as well as the income taxes withheld, and income taxes payable per employee.
This report explains how Excel and Access were utilized to obtain the results as well as including both linked and unlinked copies of the data as appropriate.
The conclusion of the report is a recommendation of how to change the current tax withholding policy to ensure that no employees will have income tax payable, and only management will receive a refund of over $2000.00 in future years.

Introduction
This year Smith Consulting employed 100 people in total. Of the 100 employees, 10 were managers, 20 were in the sales department, 10 were office employees, and 60 were in the production department.
Manager’s salaries ranged from $37,000 to $114,000, office employee’s wage ranged from $9,000 to $10,000, and the production employee’s wages ranged from $9,000 to $13,000. Sales employee’s wages ranged from $800 to $7,000, this is based on commission rate of 40% of sales by employee.
Currently the company’s tax withholding policy allows 30% of Management employees’ annual salary to be withheld while 8% is withheld for all non management employees. At the end of the year, income tax payable by each employee is assessed.
This results in a difference between the tax withheld and the actual income tax payable. While there were no differences between the withheld amount and the actual amount for some employees, a few employees are refunded the difference and a large number of employees, especially in production end up owing tax at the end of the year. Our goal is to change the withholding tax amounts to minimize the differences.

Analysis and Discussion
Tax withholding means that an employer withholds (deducts) amounts from an employee’s gross pay. The amount earned could be for: salary, wages, or commission. These amounts are then paid by the employer to Canada Revenue Agency. The amounts withheld from an employee’s pay by Smith Consulting are called tax withheld. The remaining amount that the employee actually receives is called net pay. Amounts withheld are not a tax on or by the employer; the employer is simply acting as a collection agent and payments are made on behalf of the employee to the Canada Revenue Agency.
The data for each employee is entered into multiple databases in Access. There are a total of 6 original tables containing the information necessary to calculate the annual salary, wage or commission for each employee.
The information is entered into tables as follows: TableName | PrimaryKey | Field Name1 | Field Name 2 | Field Name 3 | Field Name 4 | Employees | Employees | First Name | Last Name | Position ID | | Salaries | Salary ID | Employee ID | Month | Salary | | Sales | Sale ID | Employee ID | Month | Product ID | | Wages | Wage ID | Employee ID | Month | Hours | Rate | Positions | Position ID | Position | | | | Products | Product ID | Item | Price | | |

The “Employees” table contains information on the employee such as name, employee and position identification number. The Employee ID number is used throughout the process to identify employees. The Employee ID is used to link the information in the Employees table to the “Salaries”, “Sales", and “Wages” tables in a one-to-many relationship. The “Positions” table contains the information explaining what position that each position ID number corresponds to.
To the right is a copy of the results of an Access query showing the commission earned by each sales employee for the 2009 sales year. By applying a filter sales employees are separated from other employees based on their position ID. For the year John Great earned $1120.00 in commission while Tina Smallhand earned $9880.00.
To view the results of queries for the annual salaries for management, and the annual wages for office and production employees see Appendices 1-3.
By using Access, payroll was also able to calculate each employee’s gross pay by both individual months and an annual total and place the results in tables according to the positions available at Smith Consulting. This helped to distinguish the hours worked monthly and annually in by the office and production employees as well as the monthly and annual units sold by sales employees.

Above is a copy of a report of the top sales employees for 2009 which was created in Access. From this report it is clearly visible which employees have ranked in the top ten for yearly sales.
With this report the management can quickly determine which sales employees have been the top performers for the year and calculate any applicable bonus that may be awarded. This type of report can also be generated to show the top paid employees in any other position at Smith Consulting.
Below is a copy of the excel worksheet showing the annual commission of the sales employees and a total for all commissions earned for 2009.

As you can see, there are many ways to show the sales employees and their commission earned in Access as well as Excel. To see the Excel versions of the Access queries of annual salaries for management, and the annual wages for office and production employees see Appendices 4-6.
To calculate the income tax payable per employee, a look up table is needed.
This table is then used to calculate the tax rating per employee based on the annual income calculated previously by utilizing a VLookup function in Excel. With this rating the proper amount of tax payable per employee can be calculated. With this amount calculated it can then be determined which employees had sufficient, insufficient or excess tax withheld using the company’s current tax withholding policy of withholding 30% of income for management and 8% for all non management employees. Below is an example of the information calculated as explained in this paragraph. Appendix 7 contains the Excel Employee Tax table in full.
The data can then be used to create a pivot table or a pivot chart to summarize and analyze the data. The use of a pivot chart allows for visualization of the data so comparisons, patterns and efficiency of the current employee payroll tax deductions are easily distinguishable.
Below is a copy of the pivot chart and bar graph showing the number of employees in each position who have a tax amount owing, refunded or had the correct amount withheld.

As you can see clearly, there are many employees owing taxes in the production department. This shows the problem with the current tax withholding policy at Smith Consulting.
Recommendation
Due to the current tax withholding policy of Smith Consulting, the differences between tax withheld and the tax payable in some cases is quite significant. With the current tax withholding policy employees have taxes payable from between $209.00 and $13,324.00. This problem needs to be remedied so fewer employees owe tax at the end of the year. The ideal solution would be to have no employees owe taxes payable and refunds for non management employees should be less than $2000.00 in total.
The main reason for the difference between tax withheld and tax payable is because the production employee’s income ranges from $9,500 to over $13,000 and when this range of income is looked up on the tax rate table it falls into two separate tax brackets of *5 and 10%.
The recommendation for addressing the payroll tax deduction problem at Smith Consulting is to change the tax withheld for all non management employees to 10% from 8%, and changing the tax withheld for all management to 40% from 30%. A simple modification to the current payroll software changing the tax withholding amounts from 8% and 30% to 10% and 40% would eliminate the large number employees with taxes payable at year end.
This change would result in no employees having taxes payable at the end of the year. Of the 90 non management employees at Smith Consulting 55 would have the correct amount of tax withheld and 38 would receive a refund of between $56.00 and $204.00 for the 2009 taxation year. Of the 10 management employees 3 would have the correct amount of tax withheld and 7 would receive a refund of between $3064.00 and $7009.00 for the 2009 taxation year.
The above information is shown in Appendix 8 with a comparison of bar charts for the current tax withholding policy and the recommended change in the policy.

Appendix 1 - Result of Access Query – Annual Management Salaries

EmployeeID | FirstName | LastName | Sum Of Salary | 8 | Rob | Lake | 37905 | 46 | Tina | Magenta | 42395 | 47 | Rob | River | 76614 | 49 | Jay | Great | 81825 | 56 | Tina | Sharpeye | 80239 | 69 | Tina | Great | 109303 | 70 | Jason | Dark | 50063 | 82 | Rob | Smallhand | 133232 | 83 | Jason | Small | 78045 | 100 | Elis | Oak | 114693 |

Appendix 2 - Result of Access Query – Annual Office Wages EmployeeID | FirstName | LastName | Sum Of Monthly Wages | SumOfHours | 7 | Joel | Lake | 9222 | 513 | 23 | Rob | Green | 9613 | 506 | 28 | Darcy | Sharpeye | 9074 | 502 | 35 | Jason | Green | 9083 | 499 | 51 | Mike | Smith | 9639 | 522 | 79 | Joel | Magenta | 9508 | 511 | 84 | Charles | Green | 9069 | 502 | 85 | Janet | Green | 9520 | 518 | 93 | Tina | Lake | 9776 | 536 | 97 | Adam | Smallhand | 9320 | 514 |

Appendix 3 - Result of Access Query – Annual Production Wages EmployeeID | FirstName | LastName | Sum Of Monthly Wage | SumOfHours | 2 | Joel | Grey | 11066 | 552 | 3 | Jason | River | 12239 | 595 | 4 | Adam | Magenta | 9691 | 538 | 5 | Elis | River | 11256 | 558 | 9 | Rob | Grey | 10019 | 546 | 10 | Bob | Lake | 11783 | 569 | 11 | Bob | Block | 11058 | 576 | 12 | Bob | Oak | 10993 | 597 | 14 | Rob | River | 11739 | 578 | 15 | Elis | Smallhand | 11034 | 572 | 16 | Joel | River | 12163 | 583 | 18 | Darcy | Smallhand | 9888 | 555 | 19 | Rob | Grey | 11499 | 566 | 21 | Cecile | Sharpeye | 11983 | 595 | 22 | Bob | Finger | 10484 | 551 | 25 | Joel | White | 10722 | 520 | 26 | Janet | Sharpeye | 11524 | 534 | 29 | Bob | Grey | 10477 | 602 | 30 | Janet | White | 11260 | 581 | 31 | Joel | Oak | 10903 | 543 | 32 | Rob | Magenta | 12142 | 573 | 33 | Janice | Pink | 12160 | 576 | 34 | Fred | Dark | 12485 | 620 | 36 | Darcy | River | 10539 | 525 | 37 | Rob | Grey | 13050 | 619 | 39 | John | Smallhand | 10495 | 531 | 41 | Bob | Finger | 11533 | 558 | 42 | Bob | Grey | 10693 | 527 | 43 | Charles | Sharpeye | 11833 | 589 | 45 | Barb | Smith | 9650 | 511 | 48 | Rob | Finger | 11800 | 572 | 52 | Rob | Smith | 12651 | 627 | 53 | Al | Bigleg | 11998 | 572 | 54 | John | Dark | 11541 | 585 | 55 | Al | Finger | 10629 | 551 | 57 | Janice | Smith | 11680 | 614 | 58 | Rob | Bigleg | 12483 | 577 | 60 | Mike | Grey | 11112 | 584 | 61 | Al | Magenta | 12488 | 599 | 63 | Janice | Lake | 12103 | 577 | 64 | Tina | Great | 13092 | 638 | 65 | Charles | Great | 11626 | 579 | 66 | Jason | Grey | 11411 | 586 | 71 | Rob | White | 11149 | 578 | 72 | Elis | Smith | 11661 | 591 | 73 | Tina | Magenta | 10581 | 534 | 74 | Mike | Dark | 11804 | 604 | 75 | Darcy | Dark | 10192 | 526 | 76 | Janet | Small | 11103 | 584 | 77 | Fred | Small | 11264 | 566 | 80 | Adam | Magenta | 10971 | 556 | 81 | Barb | Smith | 10592 | 555 | 86 | John | Lake | 10902 | 576 | 87 | Darcy | River | 11823 | 598 | 90 | Bob | Grey | 10527 | 544 | 91 | Charles | Lake | 12887 | 595 | 92 | Rob | Green | 11328 | 575 | 94 | Mike | Pale | 12248 | 596 | 95 | Joel | Pale | 10627 | 545 | 99 | Darcy | Pale | 11556 | 577 |

Appendix 4 - Excel Worksheet – Annual Management Salaries

Appendix 5 - Excel Worksheet – Annual Office Wages

Appendix 6 - Excel Worksheet – Annual Production Wages Annual Wages of Production Employees | | | | | | EmployeeID | FirstName | LastName | Sum Of Monthly Wage | SumOfHours | 2 | Joel | Grey | $11,066.00 | 552 | 3 | Jason | River | $12,239.00 | 595 | 4 | Adam | Magenta | $9,691.00 | 538 | 5 | Elis | River | $11,256.00 | 558 | 9 | Rob | Grey | $10,019.00 | 546 | 10 | Bob | Lake | $11,783.00 | 569 | 11 | Bob | Block | $11,058.00 | 576 | 12 | Bob | Oak | $10,993.00 | 597 | 14 | Rob | River | $11,739.00 | 578 | 15 | Elis | Smallhand | $11,034.00 | 572 | 16 | Joel | River | $12,163.00 | 583 | 18 | Darcy | Smallhand | $9,888.00 | 555 | 19 | Rob | Grey | $11,499.00 | 566 | 21 | Cecile | Sharpeye | $11,983.00 | 595 | 22 | Bob | Finger | $10,484.00 | 551 | 25 | Joel | White | $10,722.00 | 520 | 26 | Janet | Sharpeye | $11,524.00 | 534 | 29 | Bob | Grey | $10,477.00 | 602 | 30 | Janet | White | $11,260.00 | 581 | 31 | Joel | Oak | $10,903.00 | 543 | 32 | Rob | Magenta | $12,142.00 | 573 | 33 | Janice | Pink | $12,160.00 | 576 | 34 | Fred | Dark | $12,485.00 | 620 | 36 | Darcy | River | $10,539.00 | 525 | 37 | Rob | Grey | $13,050.00 | 619 | 39 | John | Smallhand | $10,495.00 | 531 | 41 | Bob | Finger | $11,533.00 | 558 | 42 | Bob | Grey | $10,693.00 | 527 | 43 | Charles | Sharpeye | $11,833.00 | 589 | 45 | Barb | Smith | $9,650.00 | 511 | 48 | Rob | Finger | $11,800.00 | 572 | 52 | Rob | Smith | $12,651.00 | 627 | 53 | Al | Bigleg | $11,998.00 | 572 | 54 | John | Dark | $11,541.00 | 585 | 55 | Al | Finger | $10,629.00 | 551 | 57 | Janice | Smith | $11,680.00 | 614 | 58 | Rob | Bigleg | $12,483.00 | 577 | 60 | Mike | Grey | $11,112.00 | 584 | 61 | Al | Magenta | $12,488.00 | 599 | 63 | Janice | Lake | $12,103.00 | 577 | 64 | Tina | Great | $13,092.00 | 638 | 65 | Charles | Great | $11,626.00 | 579 | 66 | Jason | Grey | $11,411.00 | 586 | 71 | Rob | White | $11,149.00 | 578 | 72 | Elis | Smith | $11,661.00 | 591 | 73 | Tina | Magenta | $10,581.00 | 534 | 74 | Mike | Dark | $11,804.00 | 604 | 75 | Darcy | Dark | $10,192.00 | 526 | 76 | Janet | Small | $11,103.00 | 584 | 77 | Fred | Small | $11,264.00 | 566 | 80 | Adam | Magenta | $10,971.00 | 556 | 81 | Barb | Smith | $10,592.00 | 555 | 86 | John | Lake | $10,902.00 | 576 | 87 | Darcy | River | $11,823.00 | 598 | 90 | Bob | Grey | $10,527.00 | 544 | 91 | Charles | Lake | $12,887.00 | 595 | 92 | Rob | Green | $11,328.00 | 575 | 94 | Mike | Pale | $12,248.00 | 596 | 95 | Joel | Pale | $10,627.00 | 545 | 99 | Darcy | Pale | $11,556.00 | 577 | Total | 60 | | $682,190.00 | |

Appendix 7 - Excel Worksheet – Employee Tax EmployeeID | FirstName | LastName | Position | Hours Worked | Salary/Wage/Commission | Tax | Salary Withheld | Tax Withheld | Tax State | Tax Owing/Refund Amount | 1 | Cecile | Grey | Sales | | $6,280.00 | $502.40 | 8% | $502.40 | Even | $0.00 | 2 | Joel | Grey | Production | 552 | $11,066.00 | $1,106.60 | 8% | $885.28 | Owing | $221.32 | 3 | Jason | River | Production | 595 | $12,239.00 | $1,223.90 | 8% | $979.12 | Owing | $244.78 | 4 | Adam | Magenta | Production | 538 | $9,691.00 | $775.28 | 8% | $775.28 | Even | $0.00 | 5 | Elis | River | Production | 558 | $11,256.00 | $1,125.60 | 8% | $900.48 | Owing | $225.12 | 6 | Tina | Smallhand | Sales | | $9,880.00 | $790.40 | 8% | $790.40 | Even | $0.00 | 7 | Joel | Lake | Office | 513 | $9,222.00 | $737.76 | 8% | $737.76 | Even | $0.00 | 8 | Rob | Lake | Manager | | $37,905.00 | $9,855.30 | 30% | $11,371.50 | Refund | $1,516.20 | 9 | Rob | Grey | Production | 569 | $10,019.00 | $801.52 | 8% | $801.52 | Even | $0.00 | 10 | Bob | Lake | Production | 573 | $11,783.00 | $1,178.30 | 8% | $942.64 | Owing | $235.66 | 11 | Bob | Block | Production | 576 | $11,058.00 | $1,105.80 | 8% | $884.64 | Owing | $221.16 | 12 | Bob | Oak | Production | 597 | $10,993.00 | $1,099.30 | 8% | $879.44 | Owing | $219.86 | 13 | Janice | Dark | Sales | | $7,004.00 | $560.32 | 8% | $560.32 | Even | $0.00 | 14 | Rob | River | Production | 578 | $11,739.00 | $1,173.90 | 8% | $939.12 | Owing | $234.78 | 15 | Elis | Smallhand | Production | 572 | $11,034.00 | $1,103.40 | 8% | $882.72 | Owing | $220.68 | 16 | Joel | River | Production | 583 | $12,163.00 | $1,216.30 | 8% | $973.04 | Owing | $243.26 | 17 | Darcy | Finger | Sales | | $6,508.00 | $520.64 | 8% | $520.64 | Even | $0.00 | 18 | Darcy | Smallhand | Production | 555 | $9,888.00 | $791.04 | 8% | $791.04 | Even | $0.00 | 19 | Rob | Grey | Production | 566 | $11,499.00 | $1,149.90 | 8% | $919.92 | Owing | $229.98 | 20 | Cecile | Great | Sales | | $6,268.00 | $501.44 | 8% | $501.44 | Even | $0.00 | 21 | Cecile | Sharpeye | Production | 595 | $11,983.00 | $1,198.30 | 8% | $958.64 | Owing | $239.66 | 22 | Bob | Finger | Production | 551 | $10,484.00 | $1,048.40 | 8% | $838.72 | Owing | $209.68 | 23 | Rob | Green | Office | 506 | $9,613.00 | $769.04 | 8% | $769.04 | Even | $0.00 | 24 | Janice | Smallhand | Sales | | $7,120.00 | $569.60 | 8% | $569.60 | Even | $0.00 | 25 | Joel | White | Production | 520 | $10,722.00 | $1,072.20 | 8% | $857.76 | Owing | $214.44 | 26 | Janet | Sharpeye | Production | 534 | $11,524.00 | $1,152.40 | 8% | $921.92 | Owing | $230.48 | 27 | Jay | Small | Sales | | $8,108.00 | $648.64 | 8% | $648.64 | Even | $0.00 | 28 | Darcy | Sharpeye | Office | 502 | $9,074.00 | $725.92 | 8% | $725.92 | Even | $0.00 | 29 | Bob | Grey | Production | 602 | $10,477.00 | $1,047.70 | 8% | $838.16 | Owing | $209.54 | 30 | Janet | White | Production | 581 | $11,260.00 | $1,126.00 | 8% | $900.80 | Owing | $225.20 | 31 | Joel | Oak | Production | 543 | $10,903.00 | $1,090.30 | 8% | $872.24 | Owing | $218.06 | 32 | Rob | Magenta | Production | 573 | $12,142.00 | $1,214.20 | 8% | $971.36 | Owing | $242.84 | 33 | Janice | Pink | Production | 576 | $12,160.00 | $1,216.00 | 8% | $972.80 | Owing | $243.20 | 34 | Fred | Dark | Production | 620 | $12,485.00 | $1,248.50 | 8% | $998.80 | Owing | $249.70 | 35 | Jason | Green | Office | 499 | $9,083.00 | $726.64 | 8% | $726.64 | Even | $0.00 | 36 | Darcy | River | Production | 525 | $10,539.00 | $1,053.90 | 8% | $843.12 | Owing | $210.78 | 37 | Rob | Grey | Production | 619 | $13,050.00 | $1,305.00 | 8% | $1,044.00 | Owing | $261.00 | 38 | John | Great | Sales | | $1,120.00 | $56.00 | 8% | $89.60 | Refund | $33.60 | 39 | John | Smallhand | Production | 531 | $10,495.00 | $1,049.50 | 8% | $839.60 | Owing | $209.90 | 40 | Tina | Dark | Sales | | $8,548.00 | $683.84 | 8% | $683.84 | Even | $0.00 | 41 | Bob | Finger | Production | 558 | $11,533.00 | $1,153.30 | 8% | $922.64 | Owing | $230.66 | 42 | Bob | Grey | Production | 527 | $10,693.00 | $1,069.30 | 8% | $855.44 | Owing | $213.86 | 43 | Charles | Sharpeye | Production | 589 | $11,833.00 | $1,183.30 | 8% | $946.64 | Owing | $236.66 | 44 | Charles | Dark | Sales | | $3,780.00 | $189.00 | 8% | $302.40 | Refund | $113.40 | 45 | Barb | Smith | Production | 511 | $9,650.00 | $772.00 | 8% | $772.00 | Even | $0.00 | 46 | Tina | Magenta | Manager | | $42,395.00 | $11,022.70 | 30% | $12,718.50 | Refund | $1,695.80 | 47 | Rob | River | Manager | | $76,614.00 | $27,581.04 | 30% | $22,984.20 | Owing | $4,596.84 | 48 | Rob | Finger | Production | 572 | $11,800.00 | $1,180.00 | 8% | $944.00 | Owing | $236.00 | 49 | Jay | Great | Manager | | $81,825.00 | $29,457.00 | 30% | $24,547.50 | Owing | $4,909.50 | 50 | Al | Dark | Sales | | $9,136.00 | $730.88 | 8% | $730.88 | Even | $0.00 | 51 | Mike | Smith | Office | 522 | $9,639.00 | $771.12 | 8% | $771.12 | Even | $0.00 | 52 | Rob | Smith | Production | 627 | $12,651.00 | $1,265.10 | 8% | $1,012.08 | Owing | $253.02 | 53 | Al | Bigleg | Production | 572 | $11,998.00 | $1,199.80 | 8% | $959.84 | Owing | $239.96 | 54 | John | Dark | Production | 585 | $11,541.00 | $1,154.10 | 8% | $923.28 | Owing | $230.82 | 55 | Al | Finger | Production | 551 | $10,629.00 | $1,062.90 | 8% | $850.32 | Owing | $212.58 | 56 | Tina | Sharpeye | Manager | | $80,239.00 | $28,886.04 | 30% | $24,071.70 | Owing | $4,814.34 | 57 | Janice | Smith | Production | 614 | $11,680.00 | $1,168.00 | 8% | $934.40 | Owing | $233.60 | 58 | Rob | Bigleg | Production | 577 | $12,483.00 | $1,248.30 | 8% | $998.64 | Owing | $249.66 | 59 | Albert | River | Sales | | $9,360.00 | $748.80 | 8% | $748.80 | Even | $0.00 | 60 | Mike | Grey | Production | 584 | $11,112.00 | $1,111.20 | 8% | $888.96 | Owing | $222.24 | 61 | Al | Magenta | Production | 599 | $12,488.00 | $1,248.80 | 8% | $999.04 | Owing | $249.76 | 62 | Mike | Block | Sales | | $7,228.00 | $578.24 | 8% | $578.24 | Even | $0.00 | 63 | Janice | Lake | Production | 577 | $12,103.00 | $1,210.30 | 8% | $968.24 | Owing | $242.06 | 64 | Tina | Great | Production | 638 | $13,092.00 | $1,309.20 | 8% | $1,047.36 | Owing | $261.84 | 65 | Charles | Great | Production | 579 | $11,626.00 | $1,162.60 | 8% | $930.08 | Owing | $232.52 | 66 | Jason | Grey | Production | 586 | $11,411.00 | $1,141.10 | 8% | $912.88 | Owing | $228.22 | 67 | Jay | Pink | Sales | | $6,444.00 | $515.52 | 8% | $515.52 | Even | $0.00 | 68 | Fred | Block | Sales | | $7,876.00 | $630.08 | 8% | $630.08 | Even | $0.00 | 69 | Tina | Great | Manager | | $109,303.00 | $43,721.20 | 30% | $32,790.90 | Owing | $10,930.30 | 70 | Jason | Dark | Manager | | $50,063.00 | $13,016.38 | 30% | $15,018.90 | Refund | $2,002.52 | 71 | Rob | White | Production | 578 | $11,149.00 | $1,114.90 | 8% | $891.92 | Owing | $222.98 | 72 | Elis | Smith | Production | 591 | $11,661.00 | $1,166.10 | 8% | $932.88 | Owing | $233.22 | 73 | Tina | Magenta | Production | 534 | $10,581.00 | $1,058.10 | 8% | $846.48 | Owing | $211.62 | 74 | Mike | Dark | Production | 604 | $11,804.00 | $1,180.40 | 8% | $944.32 | Owing | $236.08 | 75 | Darcy | Dark | Production | 526 | $10,192.00 | $815.36 | 8% | $815.36 | Even | $0.00 | 76 | Janet | Small | Production | 584 | $11,103.00 | $1,110.30 | 8% | $888.24 | Owing | $222.06 | 77 | Fred | Small | Production | 566 | $11,264.00 | $1,126.40 | 8% | $901.12 | Owing | $225.28 | 78 | Janice | Small | Sales | | $9,080.00 | $726.40 | 8% | $726.40 | Even | $0.00 | 79 | Joel | Magenta | Office | 511 | $9,508.00 | $760.64 | 8% | $760.64 | Even | $0.00 | 80 | Adam | Magenta | Production | 556 | $10,971.00 | $1,097.10 | 8% | $877.68 | Owing | $219.42 | 81 | Barb | Smith | Production | 555 | $10,592.00 | $1,059.20 | 8% | $847.36 | Owing | $211.84 | 82 | Rob | Smallhand | Manager | | $133,232.00 | $53,292.80 | 30% | $39,969.60 | Owing | $13,323.20 | 83 | Jason | Small | Manager | | $78,045.00 | $28,096.20 | 30% | $23,413.50 | Owing | $4,682.70 | 84 | Charles | Green | Office | 502 | $9,069.00 | $725.52 | 8% | $725.52 | Even | $0.00 | 85 | Janet | Green | Office | 518 | $9,520.00 | $761.60 | 8% | $761.60 | Even | $0.00 | 86 | John | Lake | Production | 576 | $10,902.00 | $1,090.20 | 8% | $872.16 | Owing | $218.04 | 87 | Darcy | River | Production | 598 | $11,823.00 | $1,182.30 | 8% | $945.84 | Owing | $236.46 | 88 | Bob | Pale | Sales | | $7,724.00 | $617.92 | 8% | $617.92 | Even | $0.00 | 89 | Cecile | Pink | Sales | | $8,064.00 | $645.12 | 8% | $645.12 | Even | $0.00 | 90 | Bob | Grey | Production | 544 | $10,527.00 | $1,052.70 | 8% | $842.16 | Owing | $210.54 | 91 | Charles | Lake | Production | 595 | $12,887.00 | $1,288.70 | 8% | $1,030.96 | Owing | $257.74 | 92 | Rob | Green | Production | 575 | $11,328.00 | $1,132.80 | 8% | $906.24 | Owing | $226.56 | 93 | Tina | Lake | Office | 536 | $9,776.00 | $782.08 | 8% | $782.08 | Even | $0.00 | 94 | Mike | Pale | Production | 596 | $12,248.00 | $1,224.80 | 8% | $979.84 | Owing | $244.96 | 95 | Joel | Pale | Production | 545 | $10,627.00 | $1,062.70 | 8% | $850.16 | Owing | $212.54 | 96 | Joel | Magenta | Sales | | $8,560.00 | $684.80 | 8% | $684.80 | Even | $0.00 | 97 | Adam | Smallhand | Office | 514 | $9,320.00 | $745.60 | 8% | $745.60 | Even | $0.00 | 98 | Adam | Green | Sales | | $6,692.00 | $535.36 | 8% | $535.36 | Even | $0.00 | 99 | Darcy | Pale | Production | 577 | $11,556.00 | $1,155.60 | 8% | $924.48 | Owing | $231.12 | 100 | Elis | Oak | Manager | | $114,693.00 | $45,877.20 | 30% | $34,407.90 | Owing | $11,469.30 |

Appendix 8 – Comparison of Tax Withholding Policy Graphs
Withholding Tax:
Management - 30% and Non Management – 8%

Withholding Tax:
Management - 40% and Non Management – 10%

--------------------------------------------
[ 1 ]. Income taxes payable is assessed based on annual salary at a rate of between from 5% to 40%.
[ 2 ]. The total amount earned by an employee.
[ 3 ]. A fixed amount of pay per period that is not determined by hours worked. At Smith Consulting salaries are earned by managers.
[ 4 ]. An amount that is determined by hours of work multiplied by an hourly rate. At Smith Consulting office and production employees are paid in this manner
[ 5 ]. Refers to amount of commission paid per sale by an employee. Smith Consulting pays a 40% commission.
[ 6 ]. In this relationship, a row in the “Employees” table has many matching row is the “Salaries”, “Sales”, and “Wages” tables but a row in each of the “Salaries”, “Sales”, and “Wages” tables can only have one matching row in the “Employees” table.
[ 7 ]. Each position in the company is assigned a Position ID, the position ID from sales is the number 2
[ 8 ]. Management, Office, Production and Sales
[ 9 ]. VLookup stands for vertical look up. This searches Salary/Wage/Commission column in the Employee Tax table and applies the correct tax rate (between 5% and 40%) which is stated in the second column of the Tax Rating table based on the income range stated in the first column of the Tax Rating table.

Similar Documents

Premium Essay

Corporations: Operational Phase

...individual must deal with double taxation, he or she may lose a significant portion of income. In some cases, this may cause the double-taxed individual to experience a lowered standard of living. Corporations deal with double taxation too, as a corporation pays taxes on its earnings only to have its shareholders taxed once more. Double tax treaties comprise of agreements between two countries, which, by eliminating international double taxation, promote exchange of goods, persons, services and investment of capital. These are bilateral economic agreements where the countries concerned evaluate the sacrifices and advantages which the treaty brings for each contracting state, including tax forgone and compensating economic advantages. Double taxation arises when an individual or business acquiring income in a foreign country is required to pay taxes on that income in both the foreign country as well as the country of origin. For example, an American company operating in a developing country, in the absence of a tax treaty between the two countries may have to pay a withholding tax to the government of the developing country, as well as corporation tax to the United States government. Double taxation is always considered to be one of the most important issues in international taxation. With the more and more business moving towards globalization and cross-border investment, double taxation is often cited as a major obstacle to liberate economic progress. There are basically three...

Words: 1643 - Pages: 7

Free Essay

Witholding Tax

...WITHHOLDING TAX INTRODUCTION Imposed on non-residents on services rendered by them. The payer as an agent, to withhold a portion of the payment ‘withholding tax’ and pay to the tax authorities. Net payment Non-resident Withholding tax Payer Tax authorities SCOPE OF WITHOLDING TAX Withholding tax is only restricted to the following types of payment made to non-resident: (a) (b) (c) (d) (e) (f) Special classes of income (S 4A) Interest (S 15) Royalty (S 15) Contract payment (S 107) Public entertainer (S 109A)’ Other income in S4(f) (S15B) Type of Income Charging Section WT Section/Rate Interest 3/4(c) 109 - 15% Royalty 3/4(d) 109 - 10% Contract payment 3/4(a) Public Entertainer Rem. 3/4(a)/4(b) 109A - 15 Special classes of income 4f 109B - 10% Non-Exempt Interest to individual 4(c) 109C - 5% resident NR R 107A - 10%+3% Sec. 4A Income. Interest. Royalty. Contract Payment. Public Entertainer’s. Remuneration. Net Payment Payer NonResident •Within 1 Month of Paying or Crediting. •Crediting means “making available to” and not crediting in the accounting sense. Tax Authorities SCOPE OF WITHOLDING TAX The non-resident would only be liable for withholding tax if all the following factors are present: (i) The recipient is a non-resident; (ii) The income has to be one of the categories in the scope; (iii) Such income has to be deemed derived from...

Words: 2582 - Pages: 11

Premium Essay

Oil Industry Analysis

...increasing and likely to increase further and as a developing country creating a best way to produce oil and gas to the unlimited demand. Petroliam Nasional Berhad(PETRONAS) has decided to analyse its business venture in three different countries all over the world. The countries are United States, China and Russia. Investing in international country may give out some financial risk. This paper is discussing the method Petronas can use to overcome all the financial risk in United States, China and Russia. A study on the derivative market of all the three countries is done to measure the risks and to know the ways to overcome the risks. Besides, this paper also discusses the taxation of every each country and how Petronas can minimize the tax burden. At the end of this paper, a conclusion is made (based on the criteria mention above) to which country to invest with different proportion. 1.0 Company Background and Risk Profile 1.1 Introduction to Oil and Gas industry in Malaysia Malaysia is one of the largest net exporters of oil and gases its region and the world. They have many gas and oil deposits on land and in the oceans surrounding the country. The country produces almost 2% of the world’s natural gas and nearly 13% of the world’s liquefied natural gas (LNG) and is ranked 25th in oil production in the world producing more than 750,000 barrels per day (bpd.) The country's state owned Petronas operates...

Words: 3719 - Pages: 15

Premium Essay

Tax Environment

...Financial Management Project: Tax Environment and Its Relation with Financial Management Submitted By: Hooria Majid Submitted To: Sir Khalid Mehmood ACKNOWLEDGEMENT I would like to thank Allah Almighty for giving us the courage and devotion to complete this project. I am highly grateful to our advisor Mr. Khalid Mahmood whose supervision and guidance in relation to selection of material for perusal and formatting of the research paper helped us to do the work on time and in a professional manner. I extend my gratitude towards University of Lahore Islamabad Campus for giving me such kind of opportunities. Thank you Hooria Majid ABSTRACT Taxes affect citizens, economy of the country, businesses, governance mechanisms, etc. Not only revenue mobilization, an effective system of taxation helps in formalizing the economy, encourages economic growth, shapes political cohesion between tiers of the Government, and results in increase in social sector service delivery. We are now quite used to hearing the rhetoric by the government and bureaucracy on how Pakistan has one of the world’s lowest tax to GDP ratios since long...

Words: 5922 - Pages: 24

Premium Essay

Witholding Tax

...Withholding tax Withholding tax is tax deducted at source from income earned by a taxpayer on a qualifying transactions, investment or income stream. It is designed to capture tax and information on transactions to prevent tax evasion. Relevant Tax laws 1) Companies Income Tax Act 2) Personal Income Tax Act 3) Tax regulations introduced from time to time by minister of finance: (a). S.I. of 1997 – personal income tax [rates, etc. of tax deducted at source (withholding tax ) regulations] (b) S.I.10 of 1997 – companies income tax [rates, etc. of tax deducted at source             (withholding tax) regulations] (c) Personal income tax [rates, etc. of tax deducted at source (withholding tax amendment) regulations of 2000]   . Tax rates Qualifying income: Divined ,rent or interest RoyaltyHire of equipment , motor vehicles ,plant & machineryAll commissions, consultancy, technical & management fees, legal fees, audit fees, listing fees and other professional fees. Building ,construction or related activityAll types of contracts & agency arrangement ,other than sales in the ordinary course of businessDirector’s fees |                     Recipient | |  Companies 10%15%10% 10% 5%5% 10 | Individuals10%15%10% 5% 5% 5%10 | Rates are reduced to 7.5% on dividend, rent, interest or royalty for entities operating in double taxation treaty countries. Tax on dividend, rent, interest or royalty is final tax for non –resident companies. Dividends distributed from...

Words: 2035 - Pages: 9

Free Essay

Fatca

...Foreign Account Tax Compliance Act (FATCA) Overcoming challenges relating to operational implementation Editorial In March last year, the US President signed the HIRE Act, thus paving the way for the implementation of the Foreign Account Tax Compliance Act (FATCA). In our first brochure “Mastering the challenges of the new US regulations”, we presented the fundamental considerations which were necessary following the introduction of the FATCA regulations. We also explained how FATCA works, and how participating foreign financial institutions (“participating FFIs”) will be affected as from 2013. Because the text of the HIRE Act functions within the meaning of a framework legislation and the specific guidance are still to be drawn up that there is considerable uncertainty among financial intermediaries. The focus is particularly on the question of whether or not a financial service provider consitutes an FFI as defined under the FATCA regulations and must make appropriate analysis. On the other hand, there are the issues of identifying customer and US accounts and the treatment of withholdable payments. Finally, there also appears to be widespread uncertainty concerning the question of what – if any – alternative strategic options an FFI has. The US Internal Revenue Service (IRS) shed some light on this issue by releasing Revenue Notice 2010–60. This notice deals in particular detail with the issues concerning the identification of customers and US accounts as well as the...

Words: 6304 - Pages: 26

Free Essay

Pa Exam Preparation

...The key areas of concern for students writing the PA exams are as follows: 1) Time management 2) Missing level 3 of the competency and jumping directly to level 4 3) Missing the competencies to be found in the exhibits in the case questions. Another concern is when students are writing both PA1 and PA2 at the same time. It is critical that those students “switch hats” from being a public practitioner in PA1 to a comptroller in PA2. And for those writing only one exam, make sure you write from the correct perspective. Consider the following for each case: 1) Type of communication (memo, letter or report) 2) Who is writing 3) To whom the case is being written to 4) The time frame. a. ie when in the audit: beginning, middle, end; there is no point writing about issues at beginning of audit if the audit is over and there is a draft audit report b. ie regarding purchase or sale of property: has the decision already been made to buy the property (shares or assets) or not; and if so, has the financing been decided. Or maybe there is an issue such as in Feed & Grow with the auditor owning shares of company to be consolidated with client; the extra shares to result in >50% ownership have not yet been bought so it is next year’s issue. The issue must still be addressed but the recommendation might be to consider the issue further. Not all issues have to be answered today; just that they must be addressed. The recommendation can be to explore...

Words: 1168 - Pages: 5

Premium Essay

Sunset Medical: a Statement of Cash Flow Case

...Sunset Medical: A Statement of Cash Flow Case Scott Wandler* College of Business Administration University of New Orleans New Orleans, LA 70148 swandler@uno.edu Kevin Watson College of Business Administration Iowa State University Ames, IA 50011 kwatson@iastate.edu *Corresponding Author Abstract Medical is based on a real situation occurring at an Orthopedic Medical practice in Colorado. While attending a trade show Dr. Jones, the managing partner at Sunset Medical, was approached by a medical consulting firm, Physicians Medical Inc. (PMI), to provide the practice billing and administrative services. Dr. Jones decided to hire PMI and signed a contract in February of 2011. Based on the interim financial statements that were released in June of 2011, Dr. Jones gave PMI control of the overall day to day operations of the practice. PMI immediately relieved the office manager of her duties and took over all operations of the practice. In early 2012, the 2011 financial statements were released and were not as impressive as the mid-year results. Dr. Jones is now worried that the increased power given to PMI may have been a mistake and has asked you to give a full assessment of the situation. The case is suitable for an introductory Financial or Managerial Accounting class at the M.B.A. level once the students have a working knowledge of the financial statements. The students must critically evaluate contract language and...

Words: 2829 - Pages: 12

Premium Essay

Org Communication

...Case law analysis Name Institution Professor Course Date United States vs Quality Stores Inc The case of United States government vs quality stores, Inc. was argued on 14th January, 2014 and decided on 25th march 2014. The plaintiff was the United States government with the petitioner being quality stores, Inc. the respondent was accused of making severance payments to its involuntarily terminated employees. The payments made varied depending on the seniority of the employees and the time they served and was not connected to receipts of the state insurance for unemployment. This led to the respondent withholding inter alia taxes that were supposed to be paid to the state as per the Federal Insurance Contribution Act (United States vs Quality stores Inc. et al, 2014). The disagreement occurred since Quality stores Inc. believed that severity payments were not taxable as per the Federal Insurance Contributions Act and was seeking a reimbursement on behalf of itself and its 1850 terminated former employees. Proceedings were initiated at the bankruptcy court since the internal revenue service never allowed or denied the refund and ultimately, the summary judgement made went in favor of Quality store Inc. The district court and the sixth circuit courts of the appeal concluded that severance payments never amounted to wages under the federal insurance contribution act. This ruling was made despite FICA’s definition of wages broadly incorporating any form of remuneration...

Words: 655 - Pages: 3

Premium Essay

Sunset Medical Case Study

...were not as impressive as the mid-year results. Dr. Jones is now worried that the increased power given to PMI may have been a mistake and has asked you to give a full assessment of the situation. Introduction Dr. Sally Jones, a practicing Orthopedic Surgeon, is the managing partner at Sunset Medical3, a professional corporation located in Colorado. Sunset, which has been in business for approximately 10 years, is a small medical practice with 2010 revenues of just over $1,000,000. The practice employs a support staff that includes an office manager, billing secretary, nurse, and radiology technician. In addition to the staff, Sunset retains Jackson and Associates, a CPA firm, to provide financial statements and tax documents. As a small, privately held corporation, Sunset is only required to submit an Income Statement and...

Words: 2781 - Pages: 12

Free Essay

2012 Global Pricing System, Transfer Pricing

...GLOBAL TRANSFER PRICING SERVICES Global Transfer Pricing Review kpmg.com TAX © 2012 KPMG International Cooperative (“KPMG International”), a Swiss entity. Member firms of the KPMG network of independent firms are affiliated with KPMG International. KPMG International provides no client services. All rights reserved. Contents Introduction Country Snapshots Country Overviews Glossary of Terms Find out more 2 4 10 255 256 © 2012 KPMG International Cooperative (“KPMG International”), a Swiss entity. Member firms of the KPMG network of independent firms are affiliated with KPMG International. KPMG International provides no client services. All rights reserved. 2 | Global Transfer Pricing Review Introduction © 2012 KPMG International Cooperative (“KPMG International”), a Swiss entity. Member firms of the KPMG network of independent firms are affiliated with KPMG International. KPMG International provides no client services. All rights reserved. Introduction | 3 As multinational companies continue to globalize their supply chains, transfer pricing is increasingly at the forefront of business transformation initiatives. Organizations recognize that transfer pricing strategies can add significant value to business projects and help fund future growth as they look to maximize efficiencies and minimize their global tax liabilities. The transfer pricing environment is constantly changing, in terms of both risks and opportunities. Multinational companies...

Words: 144636 - Pages: 579

Premium Essay

Oracle Erp Ap Interview Guide

... Enter employees. If you have Oracle Human Resources installed, use the People window. See: Entering a New Person (Managing People Using Oracle HRMS). If you do not have Oracle Human Resources installed, use the Enter Person window. If Oracle Inventory or Oracle Purchasing is installed, you must define at least one Inventory Organization before defining Financials Options. Define payment programs. Install or upgrade Payables. Select your primary set of books. Use the System Administrator responsibility to assign your set of books to a responsibility. Define Financials options. Define Payables options. Define your payment terms. If you plan to use automatic withholding tax, define Tax Authority type suppliers. You must do this before defining tax codes and tax groups. Define bank accounts. Define Suppliers. Open your Payables accounting period....

Words: 7183 - Pages: 29

Free Essay

Outsourcing Payroll and Tax Services

...Pro Staffing Research for Outsourcing Payroll and Tax Services 03/15/2014 Table of Contents Project Scope 4 Requirements 4 Procurement Advantages 5 Savings Analysis 5 Procurement Disadvantages 6 Risks 6 Scoring Matrix for Procurement Proposals 8 Contractual Analysis 12 Considerations 12 Proposal Information 12 Legal Compliance Subjects 12 Proposal Questionnaire 12 Instructions to Proposers 13 Project Scope Pro Staffing research of outsourcing payroll and tax responsibilities versus in-house or new software package to evaluate which process will better meet the needs of corporation in both cost and quality of services. The analysis shows the cost savings for the procurement average 31% less than in-house management either by current processes or new software. Risk/ liability for Federal Tax withholding and reports is moved to the vendor. Requirements The solution needs to provide minimal 1. Direct deposit or mail for employees and contractual workers regular payroll, bonus pay, and expense checks 2. Handle workers compensation payment 3. Payroll withholding 4. Payroll tax management 5. Knowledge of international payments to employees or contractors 6. Online payroll management by Pro Staffing 7. Web access with required registration access for previous W2 and payroll check information for up to five years 8. Documents should be printable or delivery by mail 9. Strict documented protocol...

Words: 1830 - Pages: 8

Premium Essay

Fa Chapter 11 Exercises

...CHAPTER 11 CURRENT LIABILITIES AND PAYROLL 1 EYE OPENERS 1. A discounted note payable has no stated interest rate, but provides interest by discounting the note proceeds. The discount, which is the difference between the proceeds and the face of the note, is the interest and is accounted for as such. 2. a. Income or withholding taxes, social security, and Medicare b. Employees Federal Income Tax Payable, Social Security Tax Payable, and Medicare Tax Payable 3. There is a ceiling on (c) the social security portion of the FICA tax and (d) the federal unemployment compensation tax. 4. The deductions from employees’ earnings are for amounts owed (liabilities) to others for such items as federal taxes, state and local income taxes, and contributions to pension plans. 5. Yes. Unemployment compensation taxes are paid by the employer on the first $7,000 of annual earnings for each employee. Therefore, hiring two employees, each earning $12,500 per year, would require the payment of twice the unemployment tax than if only one employee, earning $25,000, was hired. 6. 1. a 2. c 3. c 4. b 5. b 7. The use of special payroll checks relieves the treasurer or other executives of the task of signing a large number of regular checks each payday. Another advantage of this system is that reconciling the regular bank statement is simplified. The paid payroll checks are returned by the bank separately from regular checks and are accompanied by...

Words: 6693 - Pages: 27

Premium Essay

Business

...are required to respond to a range of prompt questions that examine your understanding of key legislative and financial management requirements for a case study organisation. This assessment also requires you to review available financial information and establish a budget for the organisation. Introduction I am making a budget plan and am going to recommend financial managing application for the business as a business manager. The company is named Houzit Pty Ltd, it is a retailer for home wares. It is a growing business. It has 15 stores in Brisbane area. It has 150 staff members. It is registered with ASIC. As per the review of financial structure of this company the report below has been made. 1. The statutory requirements for tax compliances are listed...

Words: 955 - Pages: 4