Excel - task num. 4: Payment of workers

This lesson aims to teach students to calculate the payment they illuminate the concepts of gross and Super-tax salary. Explain why they are paid salary from social security and health insurance which is paid to what is increasingly used, why and in what amount is deducted from the salary advance on income tax which is extracted and what are the discounts from tax personal income .
Health insurance:
This insurance is administered by health insurance and pays it doctors on a scale medical procedures and medications. For kids, students, women on maternity leave, registered unemployed and pensioners pay this insurance from the state budget. Other, even when not working you must pay this insurance for yourself at least a basic statutory rate.
Social Security:
These income administered by Social Security.
This insurance is divided into three parts:
health insurance - is it paid sickness benefits
pension insurance - is it paid pensions (old age, widows orphans, disabled,...)
contribution to the state employment policy - the state of it is paid unemployment benefits and funded active measures to combat unemployment (retraining, financial support for job creation, regional programs in the most affected areas, ...)
Tax on personal income taxes and discount
Income tax is 15% of the super-gross wage.
135% of the gross wage = super gross wage
There is remembered on account of the social situation of the taxpayer and are used by the non-taxable part of the tax:
CZK 24 840 per year per taxpayer (this deduction is entitled to each other than working. Pensioner)
CZK 10 680 per year for a dependent child (living in al. Household with the taxpayer is entitled to only one parent)
CZK 24 840 per annum to his wife (husband) who has an income exceeding CZK 38 040 per year
2 520 CZK per year if the taxpayer receives a partial disability pension
5040 CZK per year if the taxpayer receives full disability pension
CZK 16 140 per year if the taxpayer holds a ZTP / P
4020 CZK per year for a student who is constantly preparing for a future career studying
Since the tax base can also deduct
the value of gifts provided by legal entities on the territory of the Republic to finance specified activities (science, education, social. Purposes, culture, police, ...), if the total value of donations for the year exceeds 2% of the tax base (but min. 1 000 CZK) and a maximum can be 10% of the tax base.
interest
life insurance
pension
trade union posts, etc.

Tasks:
Calculate the payment of workers:
Gross wages = hours x rate.
When calculating the "%" do not forget the numbers given in the entered value 100th divide
Social insurance is 6.5% of gross pay when calculating = Use an absolute cell address of the M5 - 6.5.
Health insurance is 4.5% of gross pay when calculating = Use an absolute cell address M6 - 5.4
Advance tax is 15% of the super-gross wage rounded to hundreds up (SHM) - 2070 CZK (price reduced taxes for every taxpayer) - discount for each child is 967 CZK.
SHM is 134% of gross wages.
If an employee has a child which applies a discount of income tax the discount is 967 CZK per child and is deducted from the income tax base.
If it advance tax is negative, fill in the column "Adjusted tax" value of 0
Net salary = gross wage - Social insurance - health insurance - tax advance payment after the discount.
In the row "total" count the net number of total wage and what is the average rate per hour.
In the table below, calculate how much money is contributions to social, health insurance, income tax.
Paid wages, average minimum and maximum salary.
The hours worked column to display the designation "h"
Orámujte table accompaniment black headers, fonts give bold white.
Texts in the table header wrap.
Create a graph - the column itself where workers will be listed last name, social, health insurance, tax and net pay.

All tasks are in the attached file: zadani-prikladu-4-en.xls