Lesson 36: Use of Spreadsheet in Business Applications
Master computerized Payroll Accounting and Fixed Asset Depreciation using Microsoft Excel spreadsheets. Learn pay structures, earnings, statutory deductions, Excel mathematical formulas, and financial depreciation functions like SLN, DB, DDB, and SYD.
Payroll Accounting & Payroll Components
What is Payroll Accounting?
Payroll Accounting refers to the maintenance of computerized accounting records regarding employee remuneration paid for services rendered during a specific payroll period (usually a month). The contract of service incorporates predetermined pay rules to avoid subjectivity in determining salary payable.
The Net Salary Equation
Payroll is an accounting statement displaying the gross salary, statutory/voluntary deductions, and net amount payable:
Detailed Classification of Payroll Components
| Gross Earnings (TE) | Deductions from Pay (TD) |
|---|---|
|
Basic Pay (BP) & Grade Pay (GP): Core pay scale salary plus designation pay. Dearness Pay (DP) & Dearness Allowance (DA): Inflation compensation calculated as a % of (BP + DP). |
Provident Fund (PF): Statutory social security contribution calculated as % of (BP + DP). Tax Deduction at Source (TDS): Monthly income tax liability deducted by employer. |
|
House Rent Allowance (HRA): Paid to facilitate residential lease accommodation. Transport Allowance (TRA): Paid to facilitate commuting to work place. |
Professional Tax (PT): Statutory state legislature deduction. Loan Recovery & Advances: Monthly installment deductions for loans/salary advances. |
Payroll Formulas & Excel Spreadsheet Design
Standard Payroll Computations
- • NOEDP: NODM - (Leave Without Pay + Unauthorised Absence)
- • BPE (Basic Pay Earned): BP × (NOEDP ÷ NODM)
- • DA Amount: BPE × Applicable DA Rate %
- • HRA Amount: BPE × Applicable HRA Rate %
- • PF Contribution: BPE × PF Rate %
Typical Excel Cell References
=D4 * (E4 / NODM)=F4 * $DA$Rate=IF(HRA_Eligible, F4 * $HRA_Rate, 0)=SUM(F4:J4)=SUM(L4:N4)=K4 - O4Computerized Asset Accounting & Depreciation
What is Depreciation?
Depreciation is an allocation of the depreciable cost of a non-current (fixed) asset over its expected useful life. It matches fixed asset costs consumed with revenue generated. Land (freehold) is generally non-depreciable. Under Schedule XIV / Schedule II of the Companies Act, organizations compute depreciation using Straight Line Method (SLM) or Written Down Value (WDV) method.
Schedule Asset Classification
- Land (Freehold / Leasehold) & Buildings (Factory, Office, Residential)
- Plant & Machinery, CNC Machines & Equipments
- Furniture & Fixtures, Office Vehicles
- Capital Work-in-Progress & Intangibles (Goodwill, Patents)
Excel Financial Functions for Depreciation
Inbuilt MS Excel Depreciation Functions
| Excel Function | Depreciation Method | Function Syntax | Description |
|---|---|---|---|
| SLN | Straight Line Method | =SLN(cost, salvage, life) | Returns constant depreciation expense per period over asset useful life. |
| DB | Fixed-Declining Balance (WDV) | =DB(cost, salvage, life, period, [month]) | Calculates depreciation at a fixed rate on reducing book value for a specified period. |
| DDB | Double Declining Balance | =DDB(cost, salvage, life, period, [factor]) | Computes accelerated depreciation using double or custom declining rate. |
| SYD | Sum of Years' Digits | =SYD(cost, salvage, life, period) | Calculates accelerated depreciation based on sum-of-years digits fraction. |
Live Payroll & Asset Depreciation Simulators
Test live payroll accounting computations and generate multi-year depreciation schedules using simulated MS Excel formulas.
Payroll Accounting Workflow Pipeline
Sequential flow from contract terms to net salary bank disbursement.
Basic Input Data
Basic Pay (BP), Grade Pay (GP), Days in Month (NODM) & Effective Days Present (NOEDP).
Earnings Calculation
Compute BPE, DA, HRA, TRA to determine Total Earnings (TE).
Deductions Computation
Compute PF contribution, TDS, PT & Loan Recovery for Total Deductions (TD).
Net Salary Disbursement
Net Salary = TE - TD. Generate Monthly Payroll Statement in Excel.
Live MS Excel Payroll Slip Generator
Input employee attendance and pay rates to simulate Excel formulas and generate salary slip.
Live Asset Depreciation Schedule Generator (`SLN` vs `DB`)
Compute multi-year asset depreciation schedules using MS Excel `SLN` and `DB` mathematical function algorithms.
| Year | Opening Book Value (₹) | Depreciation Expense (₹) | Closing Book Value (₹) | Excel Function Used |
|---|
5 Golden Rules for Spreadsheet Applications
Memorize these 5 key accounting principles and Excel syntax rules for high marks in NIOS exams.
Net Salary Formula Hierarchy
Payroll FundamentalGross Salary (Total Earnings) is the sum of Basic Pay Earned + Allowances (DA + HRA + TRA). Net Salary is obtained by deducting Total Deductions (PF + TDS + Loan Repayments) from Gross Salary.
Basic Pay Earned (BPE) Adjustment
Attendance Adjustment
Basic Pay is earned based on Effective Days Present: BPE = BP × (NOEDP / NODM), where NOEDP = NODM - (Leave Without Pay + Unauthorised Absence).
Excel SLN vs DB Depreciation Syntax
Excel Function Rules
=SLN(cost, salvage, life) requires 3 arguments and returns constant depreciation. =DB(cost, salvage, life, period) requires the specific period argument to calculate diminishing balance depreciation.
Statutory Deductions (PF & TDS)
Legal DeductionsProvident Fund (PF) is a statutory social security deduction calculated as a % of (Basic Pay + Dearness Pay). TDS is a monthly income tax apportionment mandated by Income Tax Act.
Non-Depreciable Asset Exception
Schedule Asset RulesOnly non-current assets that lose useful value over time are depreciated under Schedule XIV / Schedule II of Companies Act. Freehold Land is an exception and is NOT depreciated because its value generally appreciates or does not decrease over time.
10 MCQ Practice Quiz
Test your knowledge of Payroll formulas, Excel spreadsheet cell references, and financial depreciation functions.
10 Interactive 3D Flashcards
Click or tap the card to flip between terms, formulas, and Excel functions.
Loading Term...
Click card to reveal definition & Excel formula
Loading Definition...