Quarter 2_Module 5: Using E-Spreadsheet in the Computation of Salary and Overtime

Microsoft Excel Basics for Salary Computation
📌 The process begins with opening Microsoft Excel by searching for it and selecting the correct icon from the desktop search bar.
📄 A blank workbook should be opened to start data entry and calculations, as demonstrated using the example from Module 5, page 9.
↔️ Column widths can be adjusted by clicking and dragging the black cross cursor between the column headers (e.g., between E and F).
🎨 Formatting includes applying all borders via the Home tab (Font group) to structure the input data table.

Calculating Total Hours Worked (Overtime Module Example)
➗ To calculate total hours worked (Time Out - Time In), the formula used is `=(TimeOutCell - TimeInCell) * 24 - 1`.
⏳ The multiplication by 24 accounts for the time representation in Excel, and -1 subtracts the one-hour lunch break.
🔢 Cells must be formatted as Accounting with two decimal places (and the currency symbol set to 'None') to accurately handle partial hours.
➡️ Formulas are efficiently copied down to subsequent rows by clicking and dragging the small black square (fill handle) in the bottom-right corner of the cell.

Calculating Overtime Hours using IF Function
🕒 Overtime is calculated only if regular hours (8 hours) are exceeded, using the IF function: `=IF(TotalHoursCell > 8, TotalHoursCell - 8, 0)`.
✅ If Total Hours Worked is greater than 8, the overtime is calculated (Total Hours - 8); otherwise, the overtime is automatically set to zero (0).
🔗 When copying the IF formula down, the reference to the Total Hours Worked cell must be made absolute using dollar signs (e.g., `$B5`) if that cell were fixed, though in this specific example, the relative reference was maintained as the formula was copied down relative to each row's total hours.

Calculating Overtime Pay
💵 Assuming a regular workday (no stated holiday), overtime pay is calculated at the regular rate plus an additional 25%.
🧮 The formula used for Overtime Pay is: `=(HourlyRateCell * 1.25) * OvertimeHoursCell`. The 1.25 factor represents 100% of the rate plus the 25% additional amount.
🔒 When copying the Overtime Pay formula, the reference to the Hourly Rate cell must be made absolute using dollar signs (e.g., `$C$5 * 1.25`) to ensure it always refers to the correct rate cell during replication.
➕ The Total Overtime Pay is calculated using the SUM function across all calculated overtime pay cells for the period.

Graphical Presentation of Salary Data
📊 Lesson 2 focuses on presenting salary particulars using graphs like bar graphs or pie charts via the Insert tab.
❌ When graphing breakdowns (like Gross Earnings components or Deductions components), do not include the total line items (e.g., exclude Total Earnings or Total Deductions) in the highlighted selection.
🏷️ To add values or percentages to the graph, use the Design tab, click Add Chart Element, and select Data Labels, choosing options like 'Outside End' or 'Callout' for clarity.
🗂️ Submitted assignments should use separate sheets within the same Excel file: Sheet 1 for Activity 1.1 (Overtime Pay) and Sheet 2 for Activity 2.1 (Graphical Presentation).

Key Points & Insights
➡️ Ensure spelling accuracy when searching for applications like Microsoft Excel to avoid delays.
➡️ Excel formulas must always start with an equals sign (`=`) to perform calculations automatically.
➡️ Data presentation via graphs requires selecting the component breakdown data, excluding the resulting totals, for accurate visualization.
➡️ Submitted Excel files should be named using the format: LastName\_FirstName\_Section\_ModuleFive.

📸 Video summarized with SummaryTube.com on Oct 27, 2025, 07:27 UTC