StudyOSStudyOS CBSE Study Material, Class 5–12

Use of Spreadsheet in Business Applications

NCERT Class 12 · Accountancy Based on NCERT Class 12 Accountancy textbook · Free CBSE study kit

Everything you need to ace this chapter, in one place.
Notes · Mind Map · Quiz · Assertion-Reason · Case Studies · Question Bank · Past Papers

Get Full Study Kit →
Studying with friends? Send them this chapter.

Chapter Notes

Overview
This chapter demonstrates the practical application of spreadsheet software (MS Excel) in automating routine business calculations. It focuses on three core areas: Payroll Accounting, Asset Management (Depreciation), and Loan Repayment Scheduling. The central idea is to move from manual computation to formula-driven, reusable templates, leveraging built-in financial functions like SLN, DB, and PMT to ensure accuracy and efficiency in business data processing.
Key Concepts (20)
Payroll Accounting
The process of computing employee salary based on attendance, applicable pay scales, allowances, and statutory deductions, culminating in the generation of salary statements and bank transfer advice.
e.g. Calculating the monthly salary for all 14 employees of M/s XYZ Enterprise by inputting their basic pay and deduction days into a pre-designed Excel template.
Basic Pay (BP)
The pay in the pay scale plus Grade Pay, excluding any Special Pay. It forms the foundation for calculating most other earnings and deductions.
e.g. An employee in a government department has a BP of Rs. 25,000, which is the base figure used to compute their Dearness Allowance.
Grade Pay (GP)
An additional pay component added to the Basic Pay, determined by the employee's designation and the applicable pay band or scale of pay.
e.g. A 'Supervisory' employee might have a higher Grade Pay than a 'Non-supervisory' employee in the same pay band.
Dearness Allowance (DA)
A compensation paid to employees to offset the erosion of purchasing power due to inflation. It is granted periodically as a percentage of the sum of Basic Pay and Dearness Pay.
e.g. If the DA rate is 35%, an employee with a Basic Pay Earned of Rs. 20,000 will receive a DA of Rs. 7,000.
House Rent Allowance (HRA)
An amount paid to an employee to facilitate the acquisition of residential accommodation on lease. It is typically calculated as a percentage of Basic Pay Earned.
e.g. A supervisory employee receives HRA at 40% of their Basic Pay Earned, while a non-supervisory employee receives it at 30%.
Transport Allowance (TRA)
A fixed or percentage-based amount provided to an employee to cover the cost of commuting to the place of work.
e.g. In the XYZ Enterprise example, a supervisory employee gets a fixed TRA of Rs. 1000, while a non-supervisory employee gets Rs. 500.
Provident Fund (PF)
A statutory deduction under the Provident Fund Act, serving as a social security measure. It is computed as a percentage of the sum of Basic Pay and Dearness Pay.
e.g. A PF rate of 12% applied to a Basic Pay Earned of Rs. 20,000 results in a monthly PF deduction of Rs. 2,400.
Tax Deduction at Source (TDS)
A statutory deduction made monthly from an employee's salary towards their annual Income Tax liability, essentially apportioning the yearly tax over 12 months.
e.g. An employee's estimated annual tax liability is Rs. 60,000, so the employer deducts a TDS of Rs. 5,000 each month.
Number of Effective Days Present (NOEDP)
The number of days an employee is considered present for salary calculation, computed as the Number of Days in a Month (NODM) minus Leave Without Pay (LWP) and Unauthorised Absence.
e.g. In a 31-day month, if an employee has 2 days of LWP, their NOEDP is 29.
Basic Pay Earned (BPE)
The portion of the Basic Pay an employee actually earns in a month, calculated proportionally based on their attendance: BPE = BP * NOEDP / NODM.
e.g. An employee with a BP of Rs. 31,000 and an NOEDP of 29 days in a 31-day month has a BPE of Rs. 29,000.
Total Earnings (TE)
The aggregate of all earning components for an employee in a payroll period. TE = BPE + DA + HRA + TRA.
e.g. If BPE=20000, DA=7000, HRA=6000, and TRA=1000, the Total Earnings are Rs. 34,000.
Total Deductions (TD)
The sum of all deductions made from an employee's salary. TD = PF + TDS + LOAN.
e.g. If PF=2400, TDS=5000, and LOAN=2000, the Total Deductions are Rs. 9,400.
Net Salary (NS)
The final amount payable to an employee after subtracting Total Deductions from Total Earnings. NS = TE – TD.
e.g. With Total Earnings of Rs. 34,000 and Total Deductions of Rs. 9,400, the Net Salary is Rs. 24,600.
Asset Accounting
The process of maintaining an asset register, computing periodic depreciation to recognize the cost of a fixed asset consumed over its useful life, and preparing a schedule of fixed assets for the balance sheet.
e.g. A company calculates the annual depreciation on its machinery to reflect wear and tear and report the correct book value in its annual accounts.
Straight Line Method (SLM)
A depreciation method that charges an equal amount of depreciation expense for each year of an asset's useful life. The formula is (Acquisition Cost - Salvage Value) / Useful Life.
e.g. An asset costing Rs. 1,10,000 with a salvage value of Rs. 10,000 and a 10-year life will depreciate by Rs. 10,000 every year.
Written Down Value (WDV) Method
Also known as the Declining Balance (DB) method, it applies a constant depreciation rate to the asset's reducing book value each year, resulting in higher depreciation in the initial years.
e.g. A vehicle depreciates by 20% of its book value each year, so the depreciation amount is Rs. 20,000 in the first year but only Rs. 16,000 in the second year.
Acquisition Cost
The total cost incurred to acquire an asset and make it ready for use, including Purchase Value, Transportation Expenses, Installation Expenses, and Pre-operating Expenses.
e.g. The acquisition cost of a machine includes its purchase price of Rs. 1,00,000, transport cost of Rs. 5,000, and installation cost of Rs. 5,000, totaling Rs. 1,10,000.
Salvage Value
The estimated realisable value of an asset at the end of its useful life. It is subtracted from the acquisition cost to determine the total depreciable amount.
e.g. A company estimates that its delivery van, which cost Rs. 5,00,000, can be sold for Rs. 50,000 after 5 years of use.
Loan Repayment Schedule
A table detailing each periodic payment on a loan, showing the breakdown of the payment into interest and principal components, and the declining outstanding balance over the loan period.
e.g. A bank provides a schedule for a Rs. 1,00,000 car loan showing 24 monthly installments of Rs. 4,614, with the interest portion decreasing each month.
PMT Function
An Excel financial function used to calculate the periodic payment for a loan based on constant payments and a constant interest rate. Its parameters are Rate, Nper, Pv, Fv, and Type.
e.g. =PMT(10%/12, 24, -100000, 0, 1) calculates the monthly installment for a Rs. 1,00,000 loan at 10% annual interest over 2 years.
Formulas & Equations (11)
BPE = BP * NOEDP / NODM
Reads as: Basic Pay Earned equals Basic Pay multiplied by Number of Effective Days Present, divided by Number of Days in a Month.
Variables: BPE: Basic Pay Earned; BP: Basic Pay; NOEDP: Number of Effective Days Present; NODM: Number of Days in a Month.
Use when: Calculating the actual basic salary an employee earns based on their attendance in a given month.
⚠ Forgetting to divide by NODM, which leads to calculating the salary as if the employee worked every day, ignoring unpaid leave.
DA = BPE * (Applicable Rate of DA for the Month)
Reads as: Dearness Allowance equals Basic Pay Earned multiplied by the DA rate.
Variables: DA: Dearness Allowance; BPE: Basic Pay Earned; Rate: Percentage of DA declared by the government.
Use when: Computing the inflation compensation component of an employee's salary.
⚠ Applying the DA rate to the full Basic Pay (BP) instead of the earned Basic Pay (BPE), which overstates the allowance for an employee with absences.
HRA = BPE * (Applicable Rate of HRA for the Month)
Reads as: House Rent Allowance equals Basic Pay Earned multiplied by the HRA rate.
Variables: HRA: House Rent Allowance; BPE: Basic Pay Earned; Rate: Percentage based on employee type (e.g., 40% for Supervisory, 30% for Non-supervisory).
Use when: Determining the housing allowance for an employee.
⚠ Using a single HRA rate for all employees without using an IF function to differentiate between employee types like 'Sup' and 'Nsup'.
TE = BPE + DA + HRA + TRA
Reads as: Total Earnings equals the sum of Basic Pay Earned, Dearness Allowance, House Rent Allowance, and Transport Allowance.
Variables: TE: Total Earnings; BPE: Basic Pay Earned; DA: Dearness Allowance; HRA: House Rent Allowance; TRA: Transport Allowance.
Use when: Calculating the gross salary before any deductions.
⚠ Omitting one of the allowance components, especially a fixed-value allowance like Transport Allowance, leading to an understated gross salary.
PF = BPE * PF Rate
Reads as: Provident Fund deduction equals Basic Pay Earned multiplied by the PF rate.
Variables: PF: Provident Fund; BPE: Basic Pay Earned; PF Rate: Statutory rate, e.g., 12%.
Use when: Calculating the monthly statutory provident fund deduction.
⚠ Calculating PF on (Basic Pay + DA) as per the definition, but using only BPE in the formula, which is a common simplification in the chapter's examples that students may misapply.
TD = PF + TDS + LOAN
Reads as: Total Deductions equals the sum of Provident Fund, Tax Deduction at Source, and Loan Repayment Installment.
Variables: TD: Total Deductions; PF: Provident Fund; TDS: Tax Deduction at Source; LOAN: Recovery of Loan Instalment.
Use when: Aggregating all deductions from an employee's salary.
⚠ Forgetting to include fixed deductions like TDS or Loan installments, treating them as zero when they are actually entered as direct values in the spreadsheet.
NS = TE – TD
Reads as: Net Salary equals Total Earnings minus Total Deductions.
Variables: NS: Net Salary (in-hand pay); TE: Total Earnings; TD: Total Deductions.
Use when: Calculating the final amount payable to an employee.
⚠ Subtracting deductions from Basic Pay Earned instead of from Total Earnings, which ignores the fact that allowances are also part of the pay from which deductions are effectively made.
Straight Line Depreciation = (Acquisition Cost – Salvage Value) / Expected Useful Life
Reads as: Annual depreciation equals the total depreciable amount divided by the asset's life in years.
Variables: Acquisition Cost: Purchase cost + all expenses to make the asset usable; Salvage Value: Estimated scrap value at end of life; Useful Life: Number of years the asset will be used.
Use when: Calculating a fixed annual depreciation charge using the Straight Line Method.
⚠ Forgetting to subtract the salvage value from the acquisition cost, thereby depreciating the entire cost including the recoverable scrap value.
SLN(Cost, Salvage, Life)
Reads as: The Excel SLN function calculates straight-line depreciation for one period.
Variables: Cost: Initial cost of the asset (G5); Salvage: Salvage value (H5); Life: Useful life in years (I5).
Use when: Using a spreadsheet to compute depreciation by the Straight Line Method.
⚠ Confusing the order of arguments, particularly swapping the Salvage and Life arguments, which will result in a #NUM! or incorrect value.
DB(Cost, Salvage, Life, Period, Month)
Reads as: The Excel DB function calculates depreciation for a specified period using the fixed-declining balance method.
Variables: Cost: Initial cost (G5); Salvage: Salvage value (H5); Life: Useful life in years (I5); Period: The specific year for which depreciation is calculated (J5); Month: Number of months in the first year (K5).
Use when: Using a spreadsheet to compute depreciation by the Written Down Value (WDV) Method.
⚠ Incorrectly calculating the 'Period' argument, especially for the first and last years, or miscalculating the 'Month' argument for assets purchased mid-year.
PMT(Rate, Nper, Pv, Fv, Type)
Reads as: The Excel PMT function calculates the periodic payment for a loan.
Variables: Rate: Interest rate per period; Nper: Total number of payment periods; Pv: Present value (loan amount); Fv: Future value (usually 0); Type: 1 for payment at beginning, 0 for end of period.
Use when: Calculating the equated monthly installment (EMI) or yearly installment for a loan.
⚠ Using the annual interest rate and number of years directly without converting them to the same period unit (e.g., dividing the annual rate by 12 and multiplying years by 12 for monthly payments).
How It Works
Payroll Calculation Process
  1. Step 1: Design a template with columns for Employee No., Name, Type, Deduction Days, and Basic Pay, along with a separate area for global constants like NODM, DA Rate, HRA Rates, TRA amounts, and PF Rate.
  2. Step 2: Enter direct values for each employee, such as their unique ID, name, employee type ('Sup' or 'Nsup'), number of leave without pay days, and basic pay.
  3. Step 3: Compute the Number of Effective Days Present (NOEDP) using the formula =NODM - Deduction Days, using an absolute reference for the NODM cell (e.g., $G$3).
  4. Step 4: Calculate Basic Pay Earned (BPE) as =BP * NOEDP / NODM, again using absolute references for NODM.
  5. Step 5: Calculate allowances: DA as =BPE * DA_Rate; HRA using a nested IF function to apply different rates for 'Sup' and 'Nsup' employees; TRA using a nested IF function to assign fixed amounts based on employee type.
  6. Step 6: Sum all earnings to get Total Earnings (TE) = BPE + DA + HRA + TRA.
  7. Step 7: Calculate deductions: PF as =BPE * PF_Rate; enter TDS and Loan as direct values; sum them to get Total Deductions (TD) = PF + TDS + LOAN.
  8. Step 8: Compute Net Salary (NS) = TE – TD. The template is now ready to be replicated for all employees.
💡 Students often forget to use absolute cell references (e.g., $G$3) for constants like NODM and DA Rate. If relative references are used, copying the formula down the column will shift the reference, leading to incorrect calculations or errors.
Computing Depreciation using WDV Method in Excel
  1. Step 1: Set up the spreadsheet with columns for Asset Name, Purchase Date, Installation Date, Cost of Purchase, Installation Expenses, and Pre-operating Expenses.
  2. Step 2: Calculate the 'Cost to Use' (Acquisition Cost) as the sum of Purchase Cost, Installation Expenses, and Pre-operating Expenses.
  3. Step 3: Enter direct values for Salvage Value and Life of Asset in Years.
  4. Step 4: Calculate the 'Period' (for which depreciation is to be computed) using an IF statement: if the installation month is after March, the period is (Current Year - Year of Installation); otherwise, add one more year.
  5. Step 5: Calculate 'Months in 1st Year' by finding the difference between the Year-End Date in the 1st Year and the Installation Date, divided by 30 and rounded.
  6. Step 6: Determine the 'Year-End Date in 1st Year' using another IF statement: if the installation date is between Jan and Mar, it's March 31st of that year; otherwise, it's March 31st of the next year.
  7. Step 7: Use the DB function =DB(Cost to Use, Salvage Value, Life, Period, Months in 1st Year) to compute the depreciation amount.
💡 The most complex part is correctly calculating the 'Period' and 'Month' arguments for the DB function, especially when assets are purchased mid-year. The logic for determining the first year's year-end date is a critical step that is easily overlooked.
Creating a Loan Repayment Schedule
  1. Step 1: Design a template with columns for Loan Amount, Loan Disbursement Date, Period of Loan (in Years), Rate of Interest, and Future Value (FV).
  2. Step 2: Enter the direct values for each loan. The FV is typically entered as 0, assuming the loan is fully repaid.
  3. Step 3: Calculate the Yearly Installment Amount using the PMT function: =PMT(Rate, Period, -Loan_Amount, FV, Type). The loan amount is entered as a negative value to represent a cash inflow.
  4. Step 4: Calculate the Monthly Installment Amount by dividing the Yearly Installment by 12: =Yearly_Installment / 12.
  5. Step 5: The PMT function handles the complex iterative calculation internally, providing the fixed periodic payment that covers both interest and principal repayment.
💡 The loan amount (Pv argument) must be entered as a negative number in the PMT function to get a positive payment result. Also, the interest rate and number of periods must be consistent (e.g., both monthly or both yearly).
Exam Traps — Don't Make These Mistakes
⚠ Confusing Relative and Absolute Referencing in Payroll
✗ When copying the formula =G3-D12 down a column, a student might write =G3-D12, =G4-D13, =G5-D14, etc.
→ The cell G3 contains the constant 'Number of Days in a Month' (NODM). Using a relative reference (G3) causes it to change to G4, G5, etc., which are empty or contain wrong data, leading to incorrect NOEDP calculations for all employees except the first.
✓ The correct formula is =$G$3-D12. The absolute reference $G$3 locks the cell containing NODM, ensuring it remains constant when the formula is copied down the column.
⚠ Misunderstanding the Nested IF for HRA
✗ =IF(C12="Sup", G12*$I$5, G12*$I$6)
→ This formula only handles two conditions ('Sup' and everything else). If an employee type is 'Nsup', it works, but if a third type like 'Consultant' is introduced, this formula would incorrectly give them the non-supervisory HRA rate instead of 0.
✓ A complete nested IF is required: =IF(C12="Sup", G12*$I$5, IF(C12="Nsup", G12*$I$6, 0)). This explicitly handles all three cases, ensuring a 'Consultant' gets 0 HRA.
⚠ Incorrect Period Calculation in WDV Method
✗ For an asset installed in 2015 and the current year being 2018, a student might directly write Period = 2018 - 2015 = 3.
→ The rule states that if the installation date is on or before March, one additional year must be added. If the asset was installed in January 2015, the depreciation for the year ending March 2018 is for the 4th period, not the 3rd. The formula =YEAR($F$3)-YEAR(C5) would give 3, which is wrong.
✓ The correct formula is =IF(MONTH(C5)>3, (YEAR($F$3)-YEAR(C5)), (YEAR($F$3)-YEAR(C5))+1). This adds 1 if the installation month is Jan, Feb, or Mar.
⚠ Forgetting to Negate the Loan Amount (Pv) in PMT
✗ =PMT(D6, C6, A6, 0, 1) where A6 contains a positive loan amount like 100000.
→ The PMT function follows cash flow sign conventions. A positive Pv represents money received (inflow). To return a positive payment (outflow), the Pv must be negative. Using a positive Pv will result in a negative PMT value, which is technically correct but confusing in a report.
✓ The standard practice is to write =PMT(D6, C6, -A6, 0, 1). This makes the loan amount negative, resulting in a positive, easy-to-read installment amount.
⚠ Using Annual Rate and Years for Monthly PMT
✗ To calculate a monthly EMI, a student writes =PMT(10%, 2, -100000).
→ The 'Rate' and 'Nper' arguments must be consistent. Here, a 10% annual rate is used with 2 annual periods. This calculates the yearly payment, not the monthly one. The result would be a single large annual payment, not 24 small monthly payments.
✓ To get the monthly installment, the formula must be =PMT(10%/12, 2*12, -100000). This converts the annual rate to a monthly rate and the number of years to the number of months.
Compare & Contrast
Depreciation Charge over Asset's Life
Straight Line Method (SLM) of Depreciation
Written Down Value (WDV) Method of Depreciation
SLM charges an equal amount of depreciation every year, making it constant. WDV charges a higher amount in the initial years and a progressively lower amount in later years, as it is calculated on the reducing book value.
Behavior when Formula is Copied
Relative Cell Reference (e.g., G3)
Absolute Cell Reference (e.g., $G$3)
A relative reference adjusts automatically based on the new row or column position when a formula is copied. An absolute reference remains fixed on a specific cell regardless of where the formula is copied, which is essential for referring to constant values like tax rates or days in a month.
Definition and Use in Payroll
Basic Pay (BP)
Basic Pay Earned (BPE)
BP is the fixed monthly pay scale amount, while BPE is the actual amount earned based on attendance. BP is a static input value, whereas BPE is a calculated value (BP * NOEDP / NODM) used as the base for computing all allowances and the PF deduction.
Exam Focus
Marks pattern: This chapter is typically assessed through 2-3 MCQs (1 mark each) testing function syntax and definitions, 1 Short Answer question (3 marks) asking to write formulas or explain a process, and 1 Long Answer question (5-6 marks) requiring the design of a spreadsheet template or step-by-step calculation of payroll/depreciation.
Most asked: Designing a payroll template with correct formulas for BPE, DA, HRA (using nested IF), and Net Salary. · Differentiating between and applying absolute vs. relative cell references in a spreadsheet context. · Using the PMT, SLN, and DB financial functions with their exact syntax and arguments. · Calculating the 'Period' and 'Month' arguments for the DB function in WDV depreciation. · Defining payroll components like DA, HRA, PF, and TDS.
Don't skip: A 5-mark question asking to list the columns and write the exact Excel formulas (with cell references) for a payroll or depreciation spreadsheet.; A 6-mark practical problem providing data for a few employees and asking to calculate Net Salary, requiring the correct application of absolute referencing and nested IF functions.
Safe to skip: The detailed preparation of the 'Schedule Forming Part of the Balance Sheet' (Section 3.2.2.3) is a lower-weightage area. While the concept of Gross Block and Net Block is important, the specific spreadsheet layout for this schedule is less frequently asked in full detail compared to the core depreciation calculations.
One-Line Revision (read aloud before the exam)
  1. Payroll Accounting automates salary computation including earnings, deductions, and net pay using spreadsheet formulas.
  2. Basic Pay Earned (BPE) is the actual basic salary for the month, calculated as BP * NOEDP / NODM.
  3. Dearness Allowance (DA) is an inflation compensation calculated as a percentage of BPE.
  4. House Rent Allowance (HRA) and Transport Allowance (TRA) often require a nested IF function to apply different rules for 'Sup' and 'Nsup' employees.
  5. Total Earnings (TE) is the sum of all earning heads: BPE + DA + HRA + TRA.
  6. Provident Fund (PF) is a statutory deduction computed as a percentage of BPE.
  7. Total Deductions (TD) is the sum of PF, TDS, and any Loan recovery.
  8. Net Salary (NS) is the in-hand pay, calculated as Total Earnings – Total Deductions.
  9. An absolute cell reference (e.g., $G$3) is used for constants like NODM and tax rates so they don't change when a formula is copied.
  10. The SLN function in Excel computes Straight Line Depreciation: =SLN(Cost, Salvage, Life).
  11. The DB function computes Written Down Value (Declining Balance) Depreciation: =DB(Cost, Salvage, Life, Period, Month).
  12. The 'Period' argument in the DB function must be calculated with an IF condition to account for assets purchased in the first half of the financial year.
  13. The PMT function calculates the periodic loan payment: =PMT(Rate, Nper, Pv, Fv, Type).
  14. In the PMT function, the loan amount (Pv) should be entered as a negative number to return a positive payment amount.
  15. For monthly loan payments, the annual interest rate must be divided by 12 and the loan period in years must be multiplied by 12 in the PMT function.

MCQs — 10 Questions with Answers

Q1. Which of the following is NOT a statutory deduction in payroll accounting?

  • A. Provident Fund (PF)
  • B. Tax Deduction at Source (TDS)
  • C. House Rent Allowance (HRA) ✓
  • D. Professional Tax (PT)

Answer: C — HRA is an earning/allowance, not a deduction; PF, TDS, and PT are all statutory deductions.

Q2. If an employee has 26 days in a month, takes 2 days LWP and 1 day unauthorised absence, what is NOEDP?

  • A. 23 days ✓
  • B. 24 days
  • C. 25 days
  • D. 26 days

Answer: A — NOEDP = NODM − LWP − Unauthorised Absence = 26 − 2 − 1 = 23 days.

Q3. Basic Pay is ₹30,000, NOEDP is 20 days, and NODM is 25 days. What is Basic Pay Earned (BPE)?

  • A. ₹20,000
  • B. ₹24,000 ✓
  • C. ₹25,000
  • D. ₹30,000

Answer: B — BPE = BP × NOEDP / NODM = 30,000 × 20 / 25 = ₹24,000.

Q4. Dearness Allowance (DA) is calculated as a percentage of which component?

  • A. Total Earnings
  • B. Basic Pay + Dearness Pay (if applicable) ✓
  • C. Net Salary
  • D. Basic Pay + HRA

Answer: B — DA is a compensation for price rise and is computed as a percentage of (Basic Pay + Dearness Pay, if applicable).

Q5. In a spreadsheet payroll template, which cells typically contain fixed parameters that apply to all employees?

  • A. Employee name and ID columns
  • B. Basic Pay and attendance columns
  • C. NODM, DA Rate, HRA Rates, PF Rate in top cells ✓
  • D. Net Salary calculation columns

Answer: C — Fixed parameters (NODM, DA%, HRA%, PF%) are entered once in top cells and used in formulae for all employees.

Q6. If BPE is ₹24,000 and DA rate is 8%, what is the Dearness Allowance amount?

  • A. ₹1,200
  • B. ₹1,600
  • C. ₹1,920 ✓
  • D. ₹2,400

Answer: C — DA = BPE × DA Rate (%) = 24,000 × 8% = ₹1,920.

Q7. Which statement about HRA is correct?

  • A. HRA rates are the same for all employees regardless of designation
  • B. HRA is calculated on Total Earnings, not BPE
  • C. HRA = BPE × Applicable Rate of HRA (%), with different rates for supervisory and non-supervisory staff ✓
  • D. HRA is a statutory deduction from salary

Answer: C — HRA is an earning allowance calculated on BPE with rates differing by employee designation (supervisory vs non-supervisory).

Q8. Total Earnings = ₹35,000 and Total Deductions = ₹8,000. What is Net Salary?

  • A. ₹25,000
  • B. ₹27,000 ✓
  • C. ₹28,000
  • D. ₹43,000

Answer: B — Net Salary (NS) = Total Earnings − Total Deductions = 35,000 − 8,000 = ₹27,000.

Q9. Both: (1) Provident Fund is calculated as a percentage of Basic Pay plus Dearness Pay. (2) Professional Tax is a deduction applicable in all states of India. Which statement(s) is/are correct?

  • A. Only statement (1) is correct ✓
  • B. Only statement (2) is correct
  • C. Both statements are correct
  • D. Neither statement is correct

Answer: A — Statement (1) is correct: PF = (BP + DP) × Rate. Statement (2) is incorrect: PT is applicable only in some states, not all.

Q10. HOTS — A spreadsheet template is designed with HRA rates of 12% for supervisory and 10% for non-supervisory staff. If an employee's BPE is ₹25,000 and designation is entered as 'Sup', what Excel formula would correctly calculate HRA?

  • A. =BPE*12%
  • B. =IF(Designation='Sup', BPE*G5, BPE*G6) where G5=12% and G6=10% ✓
  • C. =BPE*10%
  • D. =(BPE+DA)*12%

Answer: B — The formula uses IF logic to check designation and apply the correct HRA rate from parameter cells G5 (supervisory) and G6 (non-supervisory).

Flashcards

What does NOEDP stand for and how is it calculated?

Number of Effective Days Present = (Number of Days in a Month) − (Leave without Pay) − (Unauthorised Absence).

Define Dearness Allowance (DA) and state its basis.

DA is compensation for erosion in purchasing power due to price rise, calculated as a percentage of (Basic Pay + Dearness Pay if applicable).

How is Basic Pay Earned (BPE) different from Basic Pay (BP)?

BPE = BP × NOEDP/NODM, adjusting for actual days present, whereas BP is the fixed contractual pay.

What is the formula for House Rent Allowance (HRA)?

HRA = BPE × (Applicable Rate of HRA for the Month), with different rates typically for supervisory and non-supervisory staff.

Name three statutory deductions in payroll accounting.

Provident Fund (PF), Tax Deduction at Source (TDS), and Professional Tax (PT) are statutory deductions.

What is the purpose of a spreadsheet template in payroll?

A template specifies cell layout, formulae locations, and parameter cells so that values auto-calculate when data is entered.

How is Provident Fund (PF) calculated in payroll?

PF = BPE × PF Rate (%), where the rate is set by government under the Provident Fund Act.

What is the final payroll formula for Net Salary?

Net Salary (NS) = Total Earnings (TE) − Total Deductions (TD).

Why are HRA rates different for supervisory and non-supervisory employees?

HRA rates are differentiated based on employee designation and applicable pay band as per organisational policy.

What information is included in a bank advice generated from payroll?

Bank advice contains net salary to be transferred to individual employee accounts plus statutory payment details (PF, tax, etc.).

Important Board Questions

Define Basic Pay Earned (BPE) and write its formula. Why is it necessary to adjust Basic Pay for NOEDP? [2 marks]

BPE = BP × NOEDP/NODM adjusts salary for actual days present; without this, employees absent or on LWP would be paid for full month.

An employee has the following monthly payroll data: Basic Pay ₹30,000, DA Rate 8%, HRA Rate 12%, TRA ₹1,500, PF Rate 10%, TDS ₹3,000, Loan Recovery ₹500, NOEDP 20 days, NODM 25 days. Calculate Total Earnings, Total Deductions, and Net Salary. Show all working steps. [5 marks]

First calculate BPE = 30,000 × 20/25; then DA, HRA on BPE; sum TE = BPE + DA + HRA + TRA; sum TD = PF + TDS + Loan; finally NS = TE − TD.

Explain the purpose of a spreadsheet template in payroll accounting for an organisation with 14 employees. Describe the layout, identify which cells contain fixed parameters and which contain formulae, and explain how this design ensures consistency and efficiency in monthly salary computation. [6 marks]

A template standardises payroll structure; fixed parameters (NODM, rates) go in top cells; employee data rows use formulae that reference these parameters; ensures all employees calculated uniformly, saves time, and reduces manual errors.

Assertion-Reason Questions for Use of Spreadsheet in Business Applications

Q1. Assertion (A):

Basic Pay Earned (BPE) is calculated as BP multiplied by the ratio of NOEDP to NODM.

Reason (R):

This formula ensures that an employee is paid only for the days they actually worked, accounting for leave without pay and unauthorised absences.

Answer: A — Both A and R are true, and R is the correct explanation of A — Both the assertion and reason are true, and the reason correctly explains why the formula works this way — it prorates the salary based on actual working days. This is the fundamental principle of fair wage computation in payroll systems.

Q2. Assertion (A):

Dearness Allowance (DA) is a mandatory component of every employee's salary in India.

Reason (R):

DA is granted by the Government periodically to compensate for erosion in the purchasing power of wage earners due to price rise.

Answer: D — A is false but R is true — The assertion is false because DA is not mandatory for all employers (especially private sector organizations may not provide it), though the reason statement correctly describes what DA is when provided. The reason does not validate the assertion's universality claim.

Q3. Assertion (A):

Provident Fund (PF) and Tax Deduction at Source (TDS) are both statutory deductions calculated as percentages of earnings.

Reason (R):

PF is calculated as a percentage of Basic Pay Earned, while TDS is typically deducted as a fixed amount monthly and adjusted quarterly based on investment declarations.

Answer: B — Both A and R are true, but R is NOT the correct explanation of A — Both the assertion and reason are true, but the reason does not fully explain why they are both statutory. The reason clarifies the difference in calculation method (percentage vs. fixed amount) rather than explaining the common statutory nature asserted. They are independent statements.

Unlock more Assertion-Reason questions in Premium — unlock →

Case-Based Questions (CBQ) for Use of Spreadsheet in Business Applications

Case Study:

Mehta & Co., a manufacturing firm in Delhi with 50 employees, recently implemented a computerized payroll system using spreadsheets. The company pays salaries on the 25th of every month. For the month of June 2024, employee Rajesh Singh has the following details: Basic Pay = ₹18,000, Grade Pay = ₹2,000, DA Rate = 45%, HRA Rate = 25%, Transport Allowance = ₹600, PF Rate = 12%, TDS = ₹2,000 (fixed monthly), and Loan Instalment = ₹500. Rajesh attended work for 28 days out of 30 days in June (2 days LWP). The company wants to ensure accurate salary computation and generation of salary slips for all employees.

Q1. What is Rajesh's Number of Effective Days Present (NOEDP) for June 2024?

  • A. 28 days ✓
  • B. 27 days
  • C. 26 days
  • D. 29 days

Answer: A

Q2. Calculate Rajesh's Basic Pay Earned (BPE) for June 2024 (assuming NODM = 30).

  • A. ₹17,200
  • B. ₹16,800 ✓
  • C. ₹16,400
  • D. ₹15,600

Answer: B

Q3. What is Rajesh's Total Earnings (TE) if his BPE is ₹16,800?

  • A. ₹26,400
  • B. ₹26,088
  • C. ₹25,704 ✓
  • D. ₹25,200

Answer: C

Q4. Calculate Rajesh's Net Salary for June 2024, given TE = ₹25,704.

  • A. ₹19,504
  • B. ₹20,004 ✓
  • C. ₹20,404
  • D. ₹19,904

Answer: B

Unlock more case studies in Premium — unlock →

Previous Year Questions (PYQ) for Use of Spreadsheet in Business Applications

Real CBSE board exam questions from past papers — free to attempt.

CBSE 2026 · Set 67/3/1 · 3 marks

What are the three categories in which accounting entries can be classified while entering them in vouchers?

CBSE 2026 · Set 67/3/3 · 4 marks

List the necessary information to be filled by a user on company creation screen.

CBSE 2026 · Set 67/3/3 · 6 marks

[Question content not clearly legible - marked as incomplete]

CBSE 2026 · Set 67/4/3 · 1 mark

Fixed Asset Accounting sub-system generates information about:

  • Loans
  • Purchase expenses
  • Depreciation
  • Inventory purchased

CBSE 2026 · Set 67/5/1 · 1 mark

Which of the following is not a feature of Tailored accounting software?

  • Designed specially for large enterprises
  • Requires minimal or no support from system
  • Requires special training before use
  • Needs technical installation efforts

CBSE 2026 · Set 67/5/1 · 1 mark

Which of the following statements best describes the purpose of a data vault in an accounting software?

  • It helps in data analysis and reporting.
  • It is used to maintain audit trails.
  • It ensures data confidentiality and integrity through restricted access.
  • It is used to back up deleted vouchers automatically.

CBSE 2026 · Set 67/5/3 · 1 mark

The PMT function in MS Excel is used to:

  • Calculate the total interest on a loan.
  • Calculate the periodic payment for a loan or investment.
  • Calculate the future value of an investment.
  • Calculate the present value of a series of payments.

CBSE 2026 · Set 67/5/3 · 3 marks

What is meant by codification? Explain the meaning of mnemonic codes with the help of an example.

CBSE 2025 · Set 67/4/2 · 1 mark

It is a widely accepted security control. It uses binary coding format of storage to offer access to database. It is known as:

  • Data vault
  • Password security
  • Data audit
  • Data integrity

CBSE 2025 · Set 67/4/2 · 4 marks

(b) List eight uses of accounting softwares.

CBSE 2025 · Set 67/4/3 · 3 marks

Differentiate between tailored and specific softwares on any three basis.

CBSE 2025 · Set 67/4/3 · 6 marks

[Diagram/Map-based question — skipped]

See all 29 board PYQs for this chapter — open PYQ tab →

Class 12 Accountancy Formula Sheet

Every formula, ratio and format from all 10 chapters on one page, checked against the NCERT textbook and the CBSE 2026-27 syllabus. Free PDF download.

View the formula sheet →

Class 12 Accountancy Previous Year Questions, Chapter-wise

All 1230 CBSE board questions from 2022–2026, sorted by chapter and question type. Free PDF download.

See all past board questions →

Next chapterGraphs and Charts for Business Data →
Found this useful? Share it with your class group.

You've seen the free preview. Open the full interactive study kit — mind map, instant-graded quiz, progress tracking — free, no sign-in required.

Open Full Study Kit — Free →

Premium unlocks the full Question Bank, Daily Practice Papers, Chapter Quiz and Full Mock Papers for every chapter.