Calculate Salaries, Commissions, and Financial Data in Excel

Practice 4: Salaries, Overtime, SSO, PHL

Basic Salary

• = (Additional Table at $[$D$24] * Time Worked)

Total Overtime

• Fx = IF(logical test = E10 [Overtime] > 40)
• True-Value: E10 - 40
• False-value: 0

Triple Overtime

• Fx = IF Function
• Logic Test: Total Overtime [G10 > 8]
• True-Value: G10 - 8
• False-value: 0

Payment of Extra Time Triple

• Triple Overtime (H10) * Pay Per Hour (D24) * 3

Double Overtime

• = G10 (Total Overtime) - H10 (Triple Overtime)

Payment of Extra Time Double

• = J10 (Double Extra Time) * D24 (Pay per hour) * 2

Compulsory Social Security (SSO)

• = (F10 [Basic Salary] + I10 [Payment of Triple Overtime] + K10 [Payment of Double Overtime]) * 5%

Housing Policy Act (HPL)

• = (F10 [Basic Salary] + I10 [Payment of Triple Overtime] + K10 [Payment of Double Overtime]) * 3%

Net Salary

• = (F10 + I10 + K10) - (L10 + M10)
• (Base salary + Payment Triple Extra Time + Payment Double Extra Time) - (Social Security & Housing Policy Act)

Practice 12: Data Operations

Today's Date

• Today()

Age

• = 2009 - Date of Birth

Sex

Number of female persons?

• Fx = COUNTIF
• Status: All Rank
• Criteria: Female or Male, Initial "F" or "M"

Marital Status

Married Persons?

• Fx = COUNTIF
• Status: All Rank
• Criterion: Married or Single, Initial "C" or "S"

Basic Salary

• Validate Field Between 200 and 800
• Data Validation:
• Allow: Whole Number
• Details: Between
• Minimum: 200, Maximum: 800

Assignments

• Fx = IF(logical test: F10 (Age) > 25; True Value (Basic Salary) I10 * 4% (assignment); False Value: 12000)

Deductions

• Fx = IF(Logical Test: I10 (base salary) > 250000; True Value (Basic Salary) I10 * (Assignment) 3%; False Value: 0)

Net Salary

• = I10 + J10 - K10
• Basic Salary + Allowances - Deductions

Net Salary Greater than 300,000

• Fx = SUMIF
• Range: (L10:L17 (Net Salary entire row)
• Criteria: ">300000"

Practice 6: Vertical Lookup for Commission

Total Sales

• Cash & Credit

Commission

• Fx = VLOOKUP
• Lookup Value: B6 (Common Value Between The Two Tables)
• Table Array: Select the entire table with the $ sign
• Col Index Num:
• Range Lookup: FALSE

Practice 7: Vertical Lookup for Interest

Cost

• Fx = VLOOKUP
• Lookup Value: C14 (common values)
• Table Array: Entire Table
• Col Index Num: 2
• Range Lookup: FALSE

Initial * Cost = Initial

• Fx = VLOOKUP
• Lookup Value: C14 (common values)
• Table Array: Select all table with $ sign
• Col Index Num: 3
• Range Lookup: FALSE

Balance

• Cost = What is owed = D14 - E14

Interest

• = F14 (Balance) * C25 (monthly interest with $ sign)

Monthly Fee

• = (F14 [Balance] / G14 [Months of Credit]) + H14 (Interest)

Discount

• Fx = VLOOKUP
• Lookup Value: (C14 [Model])
• Table Array: B5;E10 with $ (whole table)
• Col Index Num: 4
• Range Lookup: FALSE