GROUP ACTIVITY: AUTOMATE IT: Using any spreadsheet application (MS Excel, WPS Sheet, Google Sheet), solve the previous problem given. | NAME | PAY PER HOUR | TOTAL HOURS WORKED | OVERTIME PAY PER HOUR | TOTAL OVERTIME HOURS | GROSS SALARY | INCOME | SSS | PAG IBIG | PHILHEALTH | TOTAL DEDUCTIONS | NETSALARY | | :-------- | :----------- | :----------------- | :-------------------- | :------------------- | :----------- | :----- | :-- | :------- | :----------- | :--------------- | :---------- | | SPONGEBOB | 50 | 160 | | 10 | | | 250 | 200 | 250 | | | | SQUIDWARD | 50 | 140 | | 20 | | | 250 | 200 | 250 | | | | PATRICK | 40 | 130 | | 30 | | | 250 | 200 | 250 | | | OVERTIME PAY PER HOUR = PAY PER HOUR * 1.5 GROSS SALARY = (TOTAL HOURS WORKED * PAY PER HOUR) + (TOTAL OVERTIME HOURS * OVERTIME PAY PER HOUR) TAX = 15% OF GROSS SALARY TOTAL DEDUCTIONS = TAX + SSS + PAG-IBIG + PHILHEALTH NETSALARY = GROSS SALARY - TOTAL DEDUCTIONS

To automate the calculation of salaries using a spreadsheet application like MS Excel, WPS Sheet, or Google Sheet, we can follow these steps. These instructions will help you to set up formulas for calculating Gross Salary, Total Deductions, and Net Salary for each employee.

Calculate Overtime Pay Per Hour :

Formula: Overtime Pay Per Hour = Pay Per Hour × 1.5
Apply this formula for each individual in the 'OVERTIME PAY PER HOUR' column. For instance, if SpongeBob's pay per hour is $50, his overtime pay per hour will be 50 × 1.5 = 75 .


Calculate Gross Salary :

Formula: Gross Salary = ( Total Hours Worked × Pay Per Hour ) + ( Total Overtime Hours × Overtime Pay Per Hour )
Use this formula for each employee. For example, SpongeBob’s Gross Salary would be calculated as ( 160 × 50 ) + ( 10 × 75 ) = 8000 \+ 750 = 8750 .


Calculate Tax :

Formula: Tax = 0.15 × Gross Salary
Calculate tax for each employee’s gross salary.


Calculate Total Deductions :

Formula: Total Deductions = Tax + SSS + Pag-IBIG + PhilHealth
Add up all deductions including the tax calculated in the previous step.


Calculate Net Salary :

Formula: Net Salary = Gross Salary − Total Deductions
Subtract the total deductions from the gross salary to find each employee’s net salary.



Here’s how you might set this up in a spreadsheet:

Column A: Name
Column B: Pay Per Hour
Column C: Total Hours Worked
Column D (Calculated): Overtime Pay Per Hour
Column E: Total Overtime Hours
Column F (Calculated): Gross Salary
Column G (Calculated): Tax
Column H: SSS
Column I: Pag-IBIG
Column J: PhilHealth
Column K (Calculated): Total Deductions
Column L (Calculated): Net Salary

By using these formulas in your spreadsheet, you can efficiently calculate the salaries automatically for SpongeBob, Squidward, and Patrick.

Answered by JessicaJessy | 2025-07-21