Question

Create a worksheet to keep a record of employees of M/s Opportunities Company. Employee details should include Name of Employee, Designation and Basic Salary. Enter 50 records. Calculate Dearness Allowance (DA) as 37.5% of Basic Salary, House Rent Allowance (HRA) as 22.5% of Basic Salary, Provident Fund (PF) as 12% of Basic Salary, and Gross Salary as Basic Salary + DA + HRA. The Income Tax (IT) is 20% of Gross Salary, and Net Salary is Gross Salary – (PF + IT) for each employee. Also calculate Total Salary, Average Salary, Maximum Salary and Minimum Salary paid by the company.

Answer 1

The worksheet layout begins with a formal company header banner, followed by 50 detailed employee records mapped from row 4 down to row 53.

Individual Salary Breakdowns (Rows 4 to 53):

• Basic Salary: Assigned dynamically across realistic corporate levels (e.g., Manager, Analyst, Consultant) ranging between ₹ 30,000.00 and ₹ 88,000.00.
• Dearness Allowance (DA): Calculated as 37.5% of the Basic Salary.
  • Formula for Row 4: =D4*37.5%
• House Rent Allowance (HRA): Calculated as 22.5% of the Basic Salary.
  • Formula for Row 4: =D4*22.5%
• Gross Salary: Formulated as the sum of Basic Salary, DA, and HRA.
  • Formula for Row 4: =SUM(D4:F4)
• Provident Fund (PF): Derived as 12% of the Basic Salary.
  • Formula for Row 4: =D4*12%
• Income Tax (IT): Calculated as 20% of the Gross Salary.
  • Formula for Row 4: =G4*20%
• Net Salary: Evaluated as Gross Salary minus the combined total deductions (PF + IT).
  • Formula for Row 4: =G4 - (H4 + I4)

Corporate Summary Statistics (Rows 54 to 57):

At the base of the dataset, explicit accounting summaries analyse total organisational outflows and distributions:

• Total Outlays: =SUM(D4:D53) to =SUM(J4:J53) (Aggregates cumulative fields)
• Average Salaries: =AVERAGE(D4:D53) to =AVERAGE(J4:J53) (Provides structural midpoints)
• Maximum Salaries: =MAX(D4:D53) to =MAX(J4:J53) (Pins high-earning markers)
• Minimum Salaries: =MIN(D4:D53) to =MIN(J4:J53) (Pins entry-level baselines)

Visual & Technical Highlights within the File:

• Gridline Integrity: Explicitly preserves default workbook gridline visibility for easy reading and clean layout structure.
• Format-Specific Masking: All monetary columns are locked with explicit localised currency mask tracking (₹#,##0.00).
• Visual Hierarchy: Features a dark blue corporate primary banner (#1F497D), bold column indices, alternating background rows to minimise reading strain, and double-underline ledger accounting borders on the total rows.