Spreadsheets are powerful tools for business applications. This chapter covers the application of spreadsheets (using Excel) to Payroll Accounting, Asset Management, and Loan Repayment.
Every employee is paid salary on a pre-determined date within the framework of an employee contract. The computation of salary is based on attendance, grade of pay, allowances, and deductions.
Salary computation involves the following elements:
The mathematical formulas used for payroll calculations are:
Number of Effective Days Present (NOEDP):
$$NOEDP = NODM - \text{Leave Without Pay} - \text{Unauthorised Absence}$$Basic Pay Earned (BPE):
$$BPE = BP \times \frac{NOEDP}{NODM}$$Total Earnings (TE):
$$TE = BPE + DA + HRA + TRA$$Total Deductions (TD):
$$TD = PF + TDS + LOAN$$Net Salary (NS):
$$NS = TE - TD$$A spreadsheet template defines the layout and formulas for automated calculations. Below is the column mapping and formula structure:
| Column | Column Heading | Abbreviation | Formula / Cell Content |
|---|---|---|---|
| A | Employee No | Emp No | Value entered directly |
| B | Employee Name | Emp Name | Value entered directly |
| C | Employee Type | Emp Type | Value entered directly |
| D | Deduction Days | Ded Days | Value entered directly |
| E | Basic Pay | BP | Value entered directly |
| F | No. of Effective Days Present | NOEP | =$I$3-D12 (where I3 is NODM) |
| G | Basic Pay Earned | BPE | =E12*F12/$I$3 |
| H | Dearness Allowance | DA | =G12*$I$4 (where I4 is DA Rate) |
| I | House Rent Allowance | HRA | =IF(C12="Sup", G12*$I$5, IF(C12="Nsup", G12*$I$6, 0)) |
| J | Transport Allowance | TRA | =IF(C12="Sup", $I$7, IF(C12="Nsup", $I$8, 0)) |
| K | Gross Salary | TE | =G12+H12+I12+J12 |
| N | Provident Fund | PF | =G12*$I$9 (where I9 is PF Rate) |
| O | Tax Deduction at Source | TDS | Value entered directly |
| P | Loan Repayment Inst. | LOAN | Value entered directly |
| Q | Total Deductions | TD | =N12+O12+P12 |
| R | Net Salary | NS | =K12-Q12 |
Asset Accounting involves tracking fixed assets and computing Depreciation over their useful lives.
Depreciation is the systematic allocation of the cost of a physical asset over its useful life.
Under the Straight Line Method, depreciation is constant across periods.
Excel Built-in Function:
=SLN(cost, salvage, life)The Written Down Value method (or Declining Balance method) computes depreciation at a decreasing rate based on the book value at the start of the period.
Excel Built-in Function:
=DB(cost, salvage, life, period, [month])| Parameter | Explanation |
|---|---|
| Cost | Initial cost of the asset |
| Salvage | Value at the end of the useful life |
| Life | Number of periods over which the asset is depreciated |
| Period | The period for which you want to calculate the depreciation |
| Month | Number of months in the first year (optional, defaults to 12) |
A Loan Repayment Schedule details the periodic payments of principal and interest over the loan term.
Excel's built-in function PMT is used to calculate the periodic payment for a loan based on constant payments and a constant interest rate.
=PMT(rate, nper, pv, [fv], [type])| Parameter | Explanation |
|---|---|
| Rate | Interest rate per period for the loan |
| Nper | Total number of payments for the loan |
| Pv | Present value (the total amount that a series of future payments is worth now; principal) |
| Fv | Future value, or a cash balance you want to attain after the last payment is made (defaults to 0) |
| Type | Whether payment is made at the beginning (1) or end (0) of the period |