| Note that any questions about the substance of the assignment should either be asked in class or be emailed to me. The responses will be circulated by email, so you should check your messages regularly to see what clarifications other people have received. You are required to develop a spreadsheet capable of processing the payroll and taxation calculations for Harry's Hotel. This system should calculate each employee’s gross wages, taxation due and net wages for a single week (Monday to Sunday), and must also provide the summary management information detailed below. The system must take each of the following factors into account: - The hotel can have a maximum of fifteen employees in any week. The system therefore should be capable of processing payroll for any number between one and fifteen people.
- The system should allow the person calculating the payroll to enter the starting and ending time of each employee for each day for the week. Employees can only work straight shifts - i.e. they must work for a continuous period of time each day, and can only work one shift per day. Each employee can work up to six days per week. Employees must have a break of at least eight hours between shifts.
- Each employee is paid an hourly rate based on their department. Front Office are paid 8 Euros per hour, F&B are paid 7 Euros per hour, Housekeeping are paid 6 Euros per hour and all others are paid 5.50 Euros per hour.
- There are three levels of positions. Workers receive the basic wage detailed above. Supervisors receive 1.25 times the hourly wage of their workers, and Managers receive 1.75 times the hourly wage of their workers. Therefore a Front Office manager would receive 8 * 1.75 = 14 Euros per hour.
- The standard working week is 37 hours. Any employees working more than this are paid overtime. Hours above 37 and below 42 are paid at one and a quarter times the employee's normal rate. Forty-two or more hours per week are paid at one and a half times the normal rate.
- Allow a space on the worksheet for the entry of the hotel’s weekly F&B sales figure. 8% of this should be distributed to the workers and supervisors in the F&B department (but not the managers) as service charge using the proportions explained below.
- Service charge should be distributed based on the number of hours that each employee has worked. For example, if a particular employee has worked 30 hours in a week, and the total number of hours worked by all employees was 300, then that employee should receive one tenth (300 divided by 30) of the service charge for that week.
- Managers receive a bonus as follows: if the hotel occupancy EACH day is less than 50%, they receive nothing; where it is between 50% and 70%, they receive 0.05% of sales; where it is between 70% and 90%, they receive 0.065% of sales, and where it is 90% or over, they get 0.085%. In all cases, the total bonus amount should be split equally between all the managers (if they worked at all that week, then they receive an equal share). Note however that the lowest occupancy in a week that determines the bonus. So if occupancy is as follows during the week 89%, 82%, 57%, 90%, 76%, 71%, 52% - they only receive a bonus of only 0.05%.
- Managers receive a additional bonus of Euro 200 each if the worker (i.e. non supervisor and non management) labour cost percentage (excluding service charge) is below 20% of sales during the week.
Taxation information is as follows: Harry's Hotel is located in a country that uses the PAYE (Pay As You Earn) system of income tax. As a result, the employer must calculate and deduct both income tax and social charges from each employee’s income and submit them to the collector of taxes. All income, including service charge and bonuses, is subject to both income tax and social charges. Income tax is calculated on "Taxable Income" – the gross income minus the tax-free allowance. Social charges are calculated on gross income. Thus someone who is single and earns 100,000 a year pays income tax on 97,000 (see below) and social charges on 100,000. All employees are assumed to work 50 weeks per year, and thus their tax-free allowances can be distributed equally through out the year using this number. Annual figures are shown in the tables below. | Tax Free Allowances | | Single | 3000 | | Married | 6000 | | Divorced / Widowed | 5500 | Employees also receive an additional tax free allowance of 500 per dependent child.
| Income Tax Rates (on TAXABLE income) | | Rate | Single | Married | | 25% | Up to 8000 | Up to 16,500 | | 37% | Income of between 8000 and 13,000 | Income of 16,500 and 25,000 | | 43% | Income over 13,000 | Income over 25,000 | (Note: The income tax rates for divorced and widowed is the same as for married employees). | Social Charges Rates (on ALL income) | | Rate | Limit | | 5.5% | Up to 10,000 | | 3.5% | 10,000 and over | When calculating both income tax and social charges, the second rate is only applied to the income above the cut off point. So, for example, a single person with a taxable income of 12000 per annum would pay 25% of the first 8000 and 37% of the remainder in income tax, as well as 5.5% of the 10,000 and 3.5% of 2000 in social charges. Your spreadsheet should present an easily understandable outline showing who has worked or not worked each day, along with all of the requested income and tax calculations. The following management information must also be available on a separate single printable page: - Total Gross Wages
- Total Overtime at time and a quarter
- Total Overtime at time and a half
- Number of people who worked overtime
- Total Income Tax collected
- Total Social Charges collected
- Total Net Wages
- Gross Wages including service, bonuses etc as a percentage of sales
- Average Gross Wage (excluding overtime)
- Average Gross Wage (including overtime)
- Average Net Wage
- Highest Net Wage
- Lowest Net Wage
On a single printable page means that if you select "File", "Print" and click "OK", the page (and just the page, will print with no further action on the part of the user! The printout should include the title "Harry's Hotel – Labour Cost Report" and display both the date of printing and your own name in the bottom right hand corner of the page. Required - A VIRUS FREE file containing ONLY the completed Excel workbook. This should be saved with your student number as the filename.
- A PDF printout of your spreadsheet formulas. It is your responsibility to ensure that this readable, as this is effectively what will be used for grading purposes.
- This two files should be emailed tobefore the submission date / time.
- Your email's subject line must contain the words "End User Assignment One 2008" in order for your submission to be automatically acknowledged (obviously do not include the quotation marks and please do not use any other subject line).
- You should copy yourself on the email to make sure that it is sent successfully.
- Please do submit your assignments in any other way, or submit them from an email address other than the one you normally use
|
