Payroll Calculation Exercise

A company wants to automate the payroll calculation system for each of its workers. They have a gross salary, withholdings for (HACIENDA) and a% for social security. The payroll mode follows the formula

Net Salary = Gross Salary - Withholdings (HACIENDA) -% Social Security

The company has each of these data arranged in a spreadsheet, so that the name of each worker appears in the first column, while the gross salary, withholdings for (Treasury) and% for social security are in the third, fourth and fifth columns (You can add more if you wish).

Use the (IF) function and develop a waterfall based on gross income and another based on percentage of retention.

Withholdings are calculated based on gross salary. If the gross salary is between:

0 and 999.99 withholding is 1% of gross salary

1,000 and 14,999.99 withholding is 5% of gross salary

15,000 and 19,999.99 withholding is 10% of gross salary

20,000 and 24,999.99 withholding is 15% of gross salary

25,000 and 29,999.99 withholding is 20% of gross salary

30,000 and 34,999.99 withholding is 25% of gross salary

35,000 and 39,999.99 withholding is 30% of gross salary

is greater than or equal to 40,000 the withholding is 40% of the gross salary

Social security is calculated on the basis of gross salary. If the gross salary is between

0 and 99.999 the% deduction is 1% of the gross salary

100,000 and 149,999 the% deduction is 4% of the gross salary

150,000 and 249,999 the% deduction is 4.7% of the gross salary

250,000 and 349,999 the% deduction is 6.2% of the gross salary

350,000 and 449,999% deduction is 7.8% of gross salary

is greater than or equal to 450,000 the% deduction is 10% of the gross salary

We also want workers whose final salary is less than a certain amount, for example, $ 10,000, the box of their salary appears with the bottom of the cell in red. Design a stylized spreadsheet to automate the payroll of company workers. Use the corresponding formulas in each box.

The data of the workers, by way of example, are the following: 0;

It uses the SUM, AVERAGE, MAX, MIN, STDEV.S, VAR.S, COUNT and MODE.SNGL functions at the bottom.

The format is CURRENCY and PERCENTAGE where necessary.

Make two 3D graphs with the previous table and include them in the same spreadsheet. Give it the title of the table.

First graph of the columns of employees,

Second graph, del, Average, Max, Min, Count.

Third Pie Chart Standard Deviation

Fourth Pie Chart Variance

Fifth graph of the total payroll

Includes Header (Title-center), (Your name, Course, Teacher and date-right) and Footer (page number-center), (image-left "business") and (time-right).

Make sure everything fits on one spreadsheet.