Home
COMPUTERISED ACCOUNTING

CHAPTER -3 USE OF SPREAD SHEET IN BUSINESS APPLICATIONS ENGLISH WITH MALAYALAM NOTE

SIMON PAVARATTY

Chapter 3: Use of Spreadsheet in Business Applications

📌 Chapter 3 – Important Topics:
  • Payroll Accounting – Components, Gross Pay, Net Pay, Template Design
  • Asset Accounting – Depreciation, SLN (Straight Line), DB (Written Down Value)
  • Loan Repayment Schedule – EMI, PMT Function, Loan Amortisation
  • Previous Year Exam Questions (2018–2024)

Introduction

Spreadsheet can be used for a number of purposes in business, some of them are Payroll Accounting, Asset Management and Loan Repayment Schedule.

Simple Explanation: Spreadsheets help businesses automate repetitive calculations – salary, asset value, loan instalments – saving time and avoiding manual errors.
📌 Live Example 1 – Payroll: A company with 200 employees calculates DA, HRA, PF and TDS for each person every month. Using formulas, the whole payroll is ready in seconds.
📌 Live Example 2 – Asset: A transport firm owns 50 vehicles. It uses SLN function to compute yearly depreciation and know the current value of each vehicle for insurance.
💭 Think: Why do businesses prefer spreadsheets over manual calculations for payroll and depreciation?
Simple Hints: Speed, accuracy, automatic re‑calculation when values change (e.g., DA percentage revision).

1. Payroll Accounting

Payroll is the statement prepared by every organisation to show the detailed salary calculation. It includes BP and allowances such as DA, TA, CCA, HRA and deductions such as contribution to PF, SLI, GIS, TDS, Loan Repayments etc.

Simple Explanation: Gross Pay = Basic Pay + All Allowances. Net Pay = Gross Pay – All Deductions.

Components of Payroll – Earnings

  • Basic Pay (BP): The fixed component, includes Grade Pay. Formula: =BP
  • Dearness Allowance (DA): % of BP + DP. Formula: =BP * DA%
  • House Rent Allowance (HRA): Fixed or % of BP. Formula: =IF(BP>55500,2500,1500)
  • Transport Allowance (TA): Fixed amount for eligible employees.

Components – Deductions

  • Provident Fund (PF): % of BP+DP. Formula: =(BP+DP)*PF%
  • Professional Tax (PT): Fixed slab.
  • TDS: Monthly income tax instalment.
  • Loan Recovery: Fixed EMI deducted.
📌 Live Example – Gross Pay Calculation: Mr. Alen – Basic ₹23,000, DA 15%, HRA ₹1,500. Gross Pay = =23000 + 23000*15% + 1500 → ₹27,950.
📌 Live Example – Net Pay Calculation: From above, PF 8% (₹1,840), SLI ₹1,000 → Net Pay = 27950 – (1840+1000) = ₹25,110.
💭 Think: If an employee takes 3 days unpaid leave, how will you adjust the Basic Pay Earned (BPE)?
Simple Hints: BPE = BP × (Days present / Days in month). Use cell references: =D4*F12/$F$3.
Exam Practice (3 marks): The 'Basic Pay' of an employee is in cell C4. Write formulas for:
(a) DA as 14% of basic pay in D4.
(b) HRA ₹2,500 if BP > 55,500 else ₹1,500 in E4.
(c) Gross Pay in F4.
Answer: (a) =C4*14% (b) =IF(C4>55500,2500,1500) (c) =C4+D4+E4.

🧮 Lab Work: Payroll Calculation with Relative & Absolute References

🎯 Objective: Learn to use absolute cell references ($) for fixed rates (DA%, PF%) and relative references for employee‑specific data (Basic Pay, HRA rate).
📌 Live Example – Scenario: Vishwas Ltd. has 3 employees. DA is 20% of Basic Pay (constant for all). HRA is 3% of BP for Manager, 2% for Technician, 0% for Driver. PF is 10% of BP (constant). Prepare the payroll statement using one formula row and fill it down.

📋 Given Data

Sl. NoNamePostBasic Pay (₹)
1ArjunManager40000
2BinaTechnician25000
3RameshDriver18000

Constants stored in cells:

CellDescriptionValue
E3DA rate20%
E4HRA rate – Manager3%
E5HRA rate – Technician2%
E6HRA rate – Driver0%
E7PF rate10%

🛠 Step‑by‑Step Solution

  1. Enter the data in cells A9:D12 (Sl.No, Name, Post, BP).
  2. Enter constants in E3:E7 as above.
  3. Formulas in row 10 (first employee): 
    Cell 10: DA          = D10 * $E$3
Cell F10: HRA        = IF(C10="Manager", D10*$E$4, IF(C10="Technician", D10*$E$5, D10*$E$6))
Cell G10: Gross Pay  = SUM(D10:F10)
Cell H10: PF         = D10 * $E$7
Cell I10: Total Ded  = H10   (only PF in this example; you can add others)
Cell J10: Net Pay    = G10 - I10
  4. Copy the formulas down from row 10 to row 12 (select G10:J10 and drag fill handle). The absolute references ($E$3, $E$4, $E$5, $E$6, $E$7) stay fixed; the relative references (D10, C10) change to D11, C11 etc.

✅ Final Output (Payroll Statement)

Sl.NoNamePostBP (₹)DA (₹)HRA (₹)Gross (₹)PF (₹)Net Pay (₹)
1ArjunManager400008000120049200400045200
2BinaTechnician25000500050030500250028000
3RameshDriver180003600021600180019800
📝 Exam Practice (3 marks): In the above payroll, if the DA rate is changed from 20% to 25% in cell E3, what happens to the Net Pay of all employees? Why?
Answer: All Net Pay values automatically recalculate because the formula =D10*$E$3 uses an absolute reference to E3. Changing E3 instantly updates every DA and hence Gross and Net Pay.
💭 Think: What would happen if we wrote =D10*E3 (without $ signs) and copied the formula down?
Simple Hints: In row 11, it would become =D11*E4 – wrong, because E4 contains HRA rate, not DA rate. Always use absolute references for constants!

✅ This problem clearly demonstrates the power of absolute referencing – one change updates the entire payroll.

2. Asset Accounting

Asset Accounting covers the complete life cycle of an asset. It means maintain assets register by considering the depreciation of the asset. The inbuilt functions of LibreOffice Calc make the asset accounting more easier.

Depreciation

Depreciation means the gradual and permanent decrease in the value of fixed asset. Two main methods:

2.1 Straight Line Method (SLN)

Fixed amount every year.

Syntax: =SLN(Cost, Salvage, Life)

  • Cost = Purchase price + Installation + Other direct costs
  • Salvage = Scrap value at end of life
  • Life = Useful life in years
📌 Live Example – SLN: Machine cost ₹11,000, salvage ₹2,000, life 10 years → =SLN(11000,2000,10) → ₹900 per year.

2.2 Written Down Value Method (DB)

Depreciation reduces each year (on opening WDV).

Syntax: =DB(Cost, Salvage, Life, Period, [Month])

  • Period – Year for which depreciation is calculated (1,2,3…)
  • Month – (Optional) Months in first year if asset bought mid‑year.
📌 Live Example – DB: Cost ₹1,00,000, Salvage ₹5,000, Life 10 years.
Year 1: =DB(100000,5000,10,1,12) → ₹20,600
Year 2: =DB(100000,5000,10,2,12) → ₹16,364 (approx.)
💭 Think: Under DB method, why does the depreciation amount keep decreasing every year?
Simple Hints: Depreciation rate is applied on the reduced book value, not on the original cost.
Exam Practice (3 marks): Explain the syntax and parameters of SLN and DB functions.
Answer: SLN(cost, salvage, life) – cost: original value, salvage: scrap value, life: useful years. DB(cost, salvage, life, period, [months]) – period: year of depreciation, months: months in first year (default 12).

3. Loan Repayment Schedule

It is a table showing the amount of principal and interest components in each instalment until the loan is fully paid off. This schedule also shows the outstanding balance of loan amount after the payment of each instalment.

EMI Calculation – PMT Function

Syntax: =PMT(rate, nper, pv, [fv], [type])

  • rate – Interest rate per period. For monthly EMI: annual_rate/12
  • nper – Total number of payments. For monthly: years*12
  • pv – Present value (loan amount)
  • fv – Future value (usually 0)
  • type – 0 = payment at end of period, 1 = beginning
📌 Live Example – EMI: Loan ₹2,00,000, 10% p.a., 5 years → =PMT(10%/12, 5*12, 200000) → ₹‑4,249 (negative sign indicates payment).

Loan Amortisation Schedule (Principal & Interest split)

  • PPMT – Principal part: =PPMT(rate, period, nper, pv)
  • IPMT – Interest part: =IPMT(rate, period, nper, pv)
📌 Live Example – Amortisation: For the above loan, first month principal = =PPMT(10%/12, 1, 60, 200000) → ₹‑3,415; interest = =IPMT(...) → ₹‑833.
💭 Think: In the early years of a loan, why is the interest portion high and principal portion low?
Simple Hints: Interest is calculated on the outstanding balance, which is large at the beginning.
Exam Practice (2 marks): Which financial function is used for preparing a loan repayment schedule?
Answer: PMT function. For detailed split, PPMT and IPMT are used.

മലയാളം വിഭാഗം – ബിസിനസ്സ് ആപ്ലിക്കേഷനുകളിൽ സ്പ്രെഡ്ഷീറ്റിന്റെ ഉപയോഗം

📌 പ്രധാന പാഠഭാഗങ്ങൾ:
  • ശമ്പള അക്കണ്ടിംഗ് (Payroll) – ഘടകങ്ങൾ, ഗ്രോസ് പേ, നെറ്റ് പേ, ടെംപ്ലേറ്റ് ഡിസൈൻ
  • അസറ്റ് അക്കണ്ടിംഗ് – മൂല്യത്തകർച്ച, SLN (സ്ട്രെയിറ്റ് ലൈൻ), DB (റൈറ്റൻ ഡൗൺ വാല്യൂ)
  • വായ്പ തിരിച്ചടവ് ഷെഡ്യൂൾ – ഇഎംഐ, PMT ഫംഗ്ഷൻ, ലോൺ അമോർടൈസേഷൻ
  • മുൻവർഷ പരീക്ഷാ ചോദ്യങ്ങൾ (2018–2024)

ആമുഖം

ബിസിനസ്സിലെ നിരവധി ആവശ്യങ്ങൾക്കായി സ്പ്രെഡ്ഷീറ്റ് ഉപയോഗിക്കാം, അവയിൽ ചിലത് ശമ്പള അക്കണ്ടിംഗ്, അസറ്റ് മാനേജുമെന്റ്, വായ്പ തിരിച്ചടവ് ഷെഡ്യൂൾ എന്നിവയാണ്.

ലളിതമായ വിശദീകരണം: ആവർത്തിച്ചുള്ള കണക്കുകൂട്ടലുകൾ (ശമ്പളം, അസറ്റ് മൂല്യം, വായ്പ ഗഡുക്കൾ) യാന്ത്രികമാക്കി സമയവും പിശകും ലാഭിക്കാൻ സ്പ്രെഡ്ഷീറ്റ് സഹായിക്കുന്നു.

1. ശമ്പള അക്കണ്ടിംഗ് (Payroll Accounting)

വിശദമായ ശമ്പള കണക്കുകൂട്ടൽ കാണിക്കാൻ ഓരോ ഓർഗനൈസേഷനും തയ്യാറാക്കിയ പ്രസ്താവനയാണിത്. ബേസിക് പേ, അലവൻസുകൾ (DA, TA, HRA), കിഴിവുകൾ (PF, TDS, ലോൺ) എന്നിവ ഉൾപ്പെടുന്നു.

📌 Live Example 1 – മൊത്ത ശമ്പളം: അലൻ – ബേസിക് ₹23,000, DA 15%, HRA ₹1,500. മൊത്ത ശമ്പളം = =23000+23000*15%+1500 = ₹27,950.
📌 Live Example 2 – അറ്റ ശമ്പളം: PF 8% (₹1,840), SLI ₹1,000 കിഴിച്ചാൽ അറ്റ ശമ്പളം = 27950 – 2840 = ₹25,110.
💭 ചിന്തിക്കുക: ഒരു ജീവനക്കാരൻ 3 ദിവസം വേതനമില്ലാത്ത അവധി എടുത്താൽ BPE എങ്ങനെ ക്രമീകരിക്കും?
സൂചന: BPE = BP × (ഹാജർ ദിവസം / മാസത്തിലെ ദിവസങ്ങൾ). =D4*F12/$F$3.

2. അസറ്റ് അക്കണ്ടിംഗ് (Asset Accounting)

അസറ്റിന്റെ സമ്പൂർണ്ണ ജീവിത ചക്രം ഉൾക്കൊള്ളുന്നു. മൂല്യത്തകർച്ച കണക്കാക്കി അസറ്റ് രജിസ്റ്റർ പരിപാലിക്കുന്നു.

മൂല്യത്തകർച്ച (Depreciation)

രണ്ട് രീതികൾ:

2.1 സ്ട്രെയിറ്റ് ലൈൻ രീതി (SLN)

ഓരോ വർഷവും തുല്യ തുക മൂല്യത്തകർച്ച.

Syntax: =SLN(Cost, Salvage, Life)

📌 Live Example – SLN: യന്ത്രത്തിന്റെ വില ₹11,000, സാൽവേജ് ₹2,000, ആയുസ്സ് 10 വർഷം → =SLN(11000,2000,10) → ₹900 / വർഷം.

2.2 റൈറ്റൻ ഡൗൺ വാല്യൂ രീതി (DB)

മൂല്യത്തകർച്ച ഓരോ വർഷവും കുറഞ്ഞു വരും.

Syntax: =DB(Cost, Salvage, Life, Period, [Month])

📌 Live Example – DB: വില ₹1,00,000, സാൽവേജ് ₹5,000, ആയുസ്സ് 10 വർഷം. ഒന്നാം വർഷം: =DB(100000,5000,10,1,12) → ₹20,600.

3. വായ്പ തിരിച്ചടവ് ഷെഡ്യൂൾ

ലോൺ പൂർണമായി അടച്ചുതീരുന്നതുവരെ ഓരോ ഗഡുവിലെയും മൂലധനവും പലിശ ഘടകങ്ങളും കാണിക്കുന്ന പട്ടിക.

ഇഎംഐ കണക്കാക്കാൻ PMT ഫംഗ്ഷൻ

Syntax: =PMT(rate, nper, pv, [fv], [type])

📌 Live Example – EMI: വായ്പ ₹2,00,000, പലിശ 10%, 5 വർഷം → =PMT(10%/12, 5*12, 200000) → ₹‑4,249.
💭 ചിന്തിക്കുക: വായ്പയുടെ ആദ്യ വർഷങ്ങളിൽ പലിശ ഭാഗം കൂടുതലും മൂലധന ഭാഗം കുറവും ആകാൻ കാരണമെന്ത്?
സൂചന: പലിശ കുടിശ്ശിക ബാലൻസിൽ കണക്കാക്കുന്നു; തുടക്കത്തിൽ ബാലൻസ് കൂടുതലാണ്.

📘 Important Exam Questions – Chapter 3 (2018–2024)

Q1 (2018 Mar – 1 Mark): Find the odd one (not an element of Gross Pay).
a) HRA b) CCA c) PF d) DA
Answer: c) PF (Provident Fund is a deduction).
Q2 (2018 Say – 1 Mark): Name the Spreadsheet function used for calculating the annual depreciation under the fixed instalment method.
Answer: SLN function.
Q3 (2020 Mar – 1 Mark): Which of the following is not a component of Payroll statement?
(a) D.A. (b) D.B. (c) H.R.A. (d) P.F.
Answer: (b) D.B.
Q4 (2020 Say – 1 Mark): Which one of the following is a deduction in the calculation of Net Pay?
(a) HRA (b) PF (c) DA (d) BP
Answer: b) PF
Q5 (2018 Mar – 3 Marks): Explain the syntax and parameters of:
a) SLN b) DB c) PMT
Answer: a) SLN(cost, salvage, life) – cost: purchase price, salvage: scrap value, life: useful life.
b) DB(cost, salvage, life, period, [months]) – period: year of depreciation, months: months in first year.
c) PMT(rate, nper, pv, [fv], [type]) – rate: interest per period, nper: total payments, pv: loan amount.
Q6 (2018 Say – 3 Marks): Basic Pay in C4. Write formulas for:
(a) DA 14% in D4
(b) HRA ₹2,500 if BP > 55,500 else ₹1,500 in E4
(c) Gross Pay in F4
Answer: (a) =C4*14% (b) =IF(C4>55500,2500,1500) (c) =C4+D4+E4
Q7 (2019 Mar – 3 Marks): One of your friends argued that there is no difference between SLN and DB functions. Do you agree? Substantiate.
Answer: Disagree. SLN gives equal depreciation each year (Straight Line). DB gives decreasing depreciation each year (Written Down Value). Both methods and results are different.
Q8 (2022 Mar – 1 Mark): Range of numbers are used for codification in:
(a) Sequential codes (b) Block codes (c) Mnemonic codes (d) Alpha Numeric codes
Answer: (b) Block codes
Q9 (2023 Mar – 1 Mark): Alphabets or symbols used to codify a piece of information is
(a) Sequential codes (b) Block codes (c) Mnemonic codes (d) Numeric codes
Answer: (c) Mnemonic codes
Q10 (2024 Mar – 1 Mark): CLT-Kozhikode is an example of
(a) Sequential codes (b) Block code (c) Mnemonic codes (d) None of these
Answer: (c) Mnemonic codes

About the author

SIMON PAVARATTY
PSMVHSS Kattoor, Thrissur

Post a Comment