Create the below data set as it is using Microsoft Excel. Use suitable cell formatting to format the cell data.
Perform the below operations on the above set of data.
Create a new column next to the Basic Salary column and name it as ETF. Then calculate the ETF of each employee by using this formula. ETF = Basic Salary * 3%
Create new column next to the ETF column and name it as Allowances. Generate allowances based on the Department as described below.
For Employees in Admin department present 5,000 as allowance, for Employees in Management department present 8,000 as allowance, for employees in Finance department present 3,000 as allowance, for employees in IT department present 7,000 as allowance.
Create another column next to the allowances and name it as Net Salary. Then calculate the net salary by using this formula. Net Salary = Basic Salary - ETF + Allowance
Calculate following below the table.
How much the company pay employees as Basic Salary? Display the amount as Total Basic Salary.
How much the company pay employees as Allowances? Display the amount as Total Allowances.
What is the average basic salary of an employee in this Company? Display the amount as Average Basic Salary.
How many employees get a basic salary greater than or equal to LKR 100,000.00 ? Display the count as High paid Employees.
How many employees get a basic salary less than or equal to LKR 70,000.00 ? Display the count as Low paid Employees.
Save your work.