Use of Spreadsheet in Business Applications Class 12: BRS, Depreciation, Loans and Ratios

What Use of Spreadsheet in Business Applications is about

Weightage: This chapter is in Part B Option II: Computerised Accounting, which a student takes instead of Option I (Analysis of Financial Statements). Computerised Accounting carries 20 of the 80 theory marks together with Overview of Computerised Accounting System, Spreadsheet and Graphs and Charts for Business Data.Source: CBSE Accountancy (055) syllabus 2026-27, Part B Option II: Computerised Accounting

In real board papers: asked in 2 of the 2 board papers we hold (2026 to 2026), on average 5 marks per paper. See the questions

Key concepts

Designing a spreadsheet application

NCERT section Chapter 3 summary

Every business application follows three steps:

• List the elements (columns) of the job, such as asset name, cost, salvage value, life, depreciation.
• For each element decide: typed value (input) or formula (output). Inputs go in cells of their own so they can be changed.
• Write each formula once, then copy it down; make references to fixed cells (year-end date, rate) absolute.

The IF function handles different conditions (for example, whether an asset was installed before or after March), and date functions (YEAR, MONTH, DATE) handle time.

Worked example

Suppose the year-end date is in F3 and each asset's installation date is in column C. Why must F3 be written as an absolute reference in formulas copied down the asset list? [1 mark]

Try it first, then show the solutionSolution

1. Every row needs the same year-end date.
2. A relative F3 would shift to F4, F5 ... on copying; ＄F＄3 stays fixed.

Answer: Because every row must refer to the same cell, ＄F＄3.

Bank reconciliation statement in a spreadsheet

NCERT section CBSE syllabus: bank reconciliation statement

A BRS explains the difference between the bank balance in the cash book and in the pass book (bank statement) on a date. In a spreadsheet, enter the starting balance and each reconciling item in its own row, with additions and deductions grouped, and compute the other balance with a formula. Starting from a cash book (debit) balance:

• Add: cheques issued but not presented; amounts credited by the bank but not in the cash book (interest, direct deposits).
• Less: cheques deposited but not credited; amounts debited by the bank but not in the cash book (bank charges, direct payments).

SUM over the add and less ranges keeps the formula short; changing any item updates the result.

Worked example

Suppose on 31st March 2026 the cash book shows a debit balance of ₹45,000. Cheques issued of ₹12,000 are not yet presented; cheques of ₹8,000 deposited are not yet credited; the bank charged ₹500 and credited interest of ₹1,200, neither recorded in the cash book. Prepare the BRS in spreadsheet form. [4 marks]

Try it first, then show the solutionSolution

1. Bank Reconciliation Statement as at 31st March 2026 (spreadsheet layout)

   Row	A: Particulars	B: Amount (₹)
   2	Balance as per cash book (Dr)	45,000
   3	Add: cheques issued but not yet presented	12,000
   4	Add: interest credited by bank, not in cash book	1,200
   5	Less: cheques deposited but not yet credited	8,000
   6	Less: bank charges debited, not in cash book	500
   7	Balance as per pass book: =B2+B3+B4−B5−B6	49,700

Answer: Balance as per pass book ₹49,700.

Asset accounting: cost to use and straight-line depreciation (SLN)

NCERT section 3.2, 3.2.2, 3.2.2.1

An asset register records each asset's purchase, installation, cost and depreciation. Assets are grouped (goodwill, land, buildings, plant and machinery, furniture and fixtures, vehicles, capital work-in-progress, others).

• Cost to use (acquisition cost) = purchase price + transport, installation and pre-operating expenses.
• Depreciable amount = cost to use − salvage value (the amount realisable at the end of the useful life).
• Straight-line depreciation = depreciable amount ÷ useful life; spreadsheet function =SLN(cost, salvage, life).

NCERT refers to the depreciation rates in Schedule XIV of the Companies Act 1956 (today Schedule II of the Companies Act 2013 sets useful lives instead); companies may report using either method.

Worked example

Suppose a machine costs ₹5,00,000, with installation ₹20,000 and pre-operating expenses ₹10,000; salvage value ₹30,000; life 5 years. Show the spreadsheet row and the yearly depreciation. [3 marks]

Try it first, then show the solutionSolution

1. Asset register row (SLN)

   Column	Item	Entry
   D	Cost of purchase	5,00,000 (typed)
   E	Installation expenses	20,000 (typed)
   F	Pre-operating expenses	10,000 (typed)
   G	Cost to use	=D5+E5+F5 → 5,30,000
   H	Salvage value	30,000 (typed)
   I	Life in years	5 (typed)
   K	Depreciation per year	=SLN(G5,H5,I5) → 1,00,000

Answer: ₹1,00,000 per year (=SLN(G5,H5,I5)).

Written-down value depreciation with DB

NCERT section 3.2.2.2

The WDV (declining balance) method charges a fixed rate on the opening book value each year. Excel's =DB(cost, salvage, life, period, month) works out the rate itself as 1 − (salvage ÷ cost)^(1 ÷ life), rounded to three decimals, and applies it to the book value at the start of the period. period is the year for which depreciation is wanted; month is the number of months the asset was used in the first year (12 if omitted).

NCERT computes the period and months with IF, YEAR, MONTH and DATE: for example the first-year end date is 31st March of the same year if the asset was installed between January and March, otherwise 31st March of the next year.

Worked example

For the same machine (cost to use ₹5,30,000, salvage ₹30,000, life 5 years, used for full years), find the DB rate and depreciation for years 1 to 3. [4 marks]

Try it first, then show the solutionSolution

1. Rate = 1 − (30,000 ÷ 5,30,000)^(1/5) = 0.437 (43.7%).
2. Depreciation by the DB (WDV) function

   Year	Opening book value (₹)	Depreciation =DB(...) (₹)	Closing book value (₹)
   1	5,30,000	2,31,610	2,98,390
   2	2,98,390	1,30,396.43	1,67,993.57
   3	1,67,993.57	73,413.19	94,580.38

Answer: Year 1 ₹2,31,610; year 2 ₹1,30,396.43; year 3 ₹73,413.19.

Loan repayment schedule with PMT

NCERT section 3.3, 3.3.1, 3.3.1.1

=PMT(rate, nper, pv, fv, type) gives the equal instalment. rate is interest per period and nper the number of payments, in the same unit (monthly rate with months). pv is the loan amount; fv is 0 because nothing remains after the last payment; type is 0 for payments at the end of each period and 1 for the beginning.

A schedule shows for each period: opening balance, interest (opening balance × rate), principal repaid (instalment − interest) and closing balance. Interest falls and principal rises over time; the final closing balance is zero.

Worked example

Suppose a loan of ₹1,20,000 is repayable in 12 monthly instalments at 12% p.a., paid at the end of each month. Find the instalment and prepare the schedule for the first three months. [6 marks]

Try it first, then show the solutionSolution

1. =PMT(1%, 12, −120000, 0, 0) = ₹10,661.85.
2. Loan repayment schedule (first three months)

   Month	Opening balance (₹)	Interest =B×1% (₹)	Principal =PMT − interest (₹)	Closing balance (₹)
   1	1,20,000	1,200	9,461.85	1,10,538.15
   2	1,10,538.15	1,105.38	9,556.47	1,00,981.68
   3	1,00,981.68	1,009.82	9,652.03	91,329.65

Answer: Instalment ₹10,661.85; balance after three months ₹91,329.65.

Ratio analysis in a spreadsheet

NCERT section CBSE syllabus: ratio analysis

Put the balance sheet and statement of profit and loss figures in cells, then write each ratio as a formula that refers to those cells. ROUND keeps results to two decimals, and IF can add an automatic comment against a standard. Once built, the sheet recalculates every ratio instantly when the figures change, and the same template serves for the next year or another company.

Worked example

Suppose current assets are ₹8,00,000 (including inventories ₹3,20,000 and prepaid expenses ₹20,000) and current liabilities are ₹3,00,000. Set up the ratio sheet with a comment that says "Comfortable" if the current ratio is at least 2. [3 marks]

Try it first, then show the solutionSolution

1. Ratio sheet linked to balance sheet cells

   Cell	Content	Result
   B2	Current assets	8,00,000
   B3	Inventories	3,20,000
   B4	Prepaid expenses	20,000
   B5	Current liabilities	3,00,000
   B7	Current ratio =ROUND(B2/B5,2)	2.67
   B8	Quick ratio =ROUND((B2−B3−B4)/B5,2)	1.53
   B9	Comment =IF(B7>=2,"Comfortable","Watch liquidity")	Comfortable

Answer: Current ratio 2.67; quick ratio 1.53; comment "Comfortable".

Use of Spreadsheet in Business Applications in real life

Each concept, met in an everyday situation. Read the situation, answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Topics in this chapter

• Loan Repayment Schedule with PMT
• Depreciation Schedule with SLN and DB
• Pivot Tables

Use of Spreadsheet in Business Applications: key facts

Straight-line depreciation

=SLN(cost, salvage, life)

Cost = purchase + installation + pre-operating expenses.

Declining balance (WDV) depreciation

=DB(cost, salvage, life, period, month)

Rate = 1 − (salvage/cost)^(1/life), rounded to 3 decimals.

Loan instalment

=PMT(rate, nper, pv, fv, type)

fv = 0; type 0 = end of period, 1 = beginning.

Interest in a loan schedule

Opening balance × rate per period; principal = instalment − interest

BRS from cash book balance

Pass book balance = cash book balance + cheques not presented + bank credits not recorded − cheques not credited − bank debits not recorded

Ratio with rounding and comment

=ROUND(B2/B5,2); =IF(B7>=2,"Comfortable","Watch liquidity")

How the board asks Use of Spreadsheet in Business Applications

The question patterns to expect, and the method that works for each.

Tricks to remember Use of Spreadsheet in Business Applications

Short memory aids. Say each one aloud once, then use it on a question the same day.

• R-N-P-F-T

  PMT arguments: Rate, Nper, Pv, Fv, Type.

  Use it when: Loan formulas.

• Cost before depreciation

  Add every cost of getting the asset ready before applying SLN or DB.

  Use it when: Asset register.

• Input cells, formula cells

  Type only inputs; compute everything else.

  Use it when: Designing any application.

• Not presented: add; not credited: subtract

  When starting from the cash book balance.

  Use it when: BRS.

Use of Spreadsheet in Business Applications: board-level solved questions

Ordered from board level to HOTS. Cover the solution, write your own, then compare step by step.

Case-study practice (Section E style)

Read the situation, then answer every part. In the board paper this is a 4-mark question split into 1 + 1 + 2 marks.

Where the marks are

How a typical answer is split into step marks. Show every step: a right final answer alone rarely earns full marks.

Where students lose marks in Use of Spreadsheet in Business Applications

The slips examiners see most often, and what to write instead.

• ✗Mistake: Leaving out installation and pre-operating expenses from cost

  Using purchase price only.

  ✓Do this: Cost to use = purchase + transport + installation + pre-operating expenses.

  Usually costs: 1 mark.

• ✗Mistake: Using the yearly rate with monthly periods

  Copying rate as given.

  ✓Do this: Divide the yearly rate by 12 for monthly payments.

  Usually costs: 1-2 marks.

• ✗Mistake: Charging interest on the original loan every month

  Absolute reference to the loan cell.

  ✓Do this: Interest = this period's opening balance × rate.

  Usually costs: 1-2 marks.

• ✗Mistake: Adding cheques deposited but not credited when starting from the cash book

  Mixing up directions.

  ✓Do this: From cash book: add cheques not presented and bank credits; deduct cheques not credited and bank debits.

  Usually costs: 1-2 marks.

• ✗Mistake: Typing numbers into ratio formulas

  Faster for one calculation.

  ✓Do this: Refer to cells so the sheet recalculates.

  Usually costs: ½-1 mark in a practical question.

NCERT exercise guide: Use of Spreadsheet in Business Applications

What each exercise in the NCERT chapter tests, how to approach it, and a question of the same kind solved step by step. Questions here are written in our own words.

Use of Spreadsheet in Business Applications: previous year questions

• Asked in 2 of the 2 board papers we hold (2026 to 2026), on average 5 marks per paper; 4 questions in all, counting OR alternatives. In 3 of 4 sample papers.

Counted from the real papers on our previous year papers page, not estimated. Average marks are over every board paper we hold, with an OR pair counted once. Chapter tags are ours.

Solved board questions from Use of Spreadsheet in Business Applications

Real questions from recent papers. Try each one first, then open our step-by-step solution with marks per step.

Every Use of Spreadsheet in Business Applications question, by year

Practice questions on Use of Spreadsheet in Business Applications

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   Which Excel function computes straight-line depreciation?
   (a) DB
   (b) SLN
   (c) PMT
   (d) FV

   Hint

   Straight LiNe.

   Answer

   (b) SLN

2. Q2 · 1 mark · MCQBoard level

   In a financial function, the argument that shows interest per period is:
   (a) FV
   (b) PV
   (c) Nper
   (d) Rate

   Hint

   Per period.

   Answer

   (d) Rate

3. Q3 · 3 marks · Short answerBoard level

   List the steps in designing a spreadsheet application for an accounting job.

   Hint

   Elements, inputs vs formulas, copying.

   Answer

   List the elements (columns); decide which are typed and which are computed; write formulas once and copy them, using absolute references for fixed cells.

4. Q4 · 3 marks · Short answerBoard level

   Name the four business applications of spreadsheets in the 2026-27 syllabus and one function used in each.

   Hint

   BRS, assets, loans, ratios.

   Answer

   BRS (SUM); asset accounting (SLN or DB); loan repayment schedule (PMT); ratio analysis (ROUND or IF).

5. Q5 · 3 marks · Short answerHarder board level

   Suppose the pass book shows ₹30,000 while the cash book shows ₹25,000. Cheques of ₹7,000 issued are not presented and a cheque of ₹2,000 deposited is not credited. Lay out the BRS starting from the cash book and check it.

   Hint

   Add not presented; less not credited.

   Answer

   25,000 + 7,000 − 2,000 = ₹30,000, agreeing with the pass book.

6. Q6 · 3 marks · Short answerHarder board level

   Suppose an asset costing ₹2,00,000 (salvage ₹20,000, life 6 years) is installed on 1st October 2025 and the year ends on 31st March. What are the period and month arguments of DB for 2025-26?

   Hint

   Months used in the first year.

   Answer

   period 1; month 6 (October to March). Formula =DB(200000,20000,6,1,6).

7. Q7 · 1 mark · Very short answerHarder board level

   Suppose a home loan of ₹20,00,000 at 9% p.a. is to be repaid monthly over 20 years with payments at the end of each month. Write the PMT formula.

   Hint

   Monthly rate, months.

   Answer

   =PMT(9%/12, 240, −2000000, 0, 0)

8. Q8 · 3 marks · Short answerHOTS / competency

   Suppose a loan schedule shows the same interest every month. What has gone wrong in the formula, and how should it be fixed?

   Hint

   Interest should follow the balance.

   Answer

   Interest is probably calculated on the original loan (an absolute reference to the loan cell) instead of each month's opening balance. Use a relative reference to the opening-balance cell of the same row.

9. Q9 · 1 mark · Assertion-reasonHOTS / competency

   Assertion (A): Ratios in a spreadsheet should refer to cells rather than typed numbers.
   Reason (R): Cell references let ratios update automatically when the data change.
   (a) Both true and R explains A (b) Both true, R does not explain A (c) A true, R false (d) A false, R true

   Hint

   Recalculation.

   Answer

   (a)

10. Q10 · 3 marks · Short answerHarder board level

    Suppose a loan of ₹1,20,000 at 12% p.a. is repaid monthly over a year (instalment ₹10,661.85). Calculate interest and principal for month 1 and the closing balance.

    Hint

    1% of the opening balance.

    Answer

    Interest ₹1,200; principal ₹9,461.85; closing balance ₹1,10,538.15.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.Which function computes WDV depreciation?Show answer

   DB (declining balance).

2. 2.What does the month argument of DB mean?Show answer

   The number of months the asset was used in the first year.

3. 3.What is cost to use?Show answer

   Purchase price plus transport, installation and pre-operating expenses.

4. 4.What does type = 1 mean in PMT?Show answer

   Payment at the beginning of each period.

5. 5.How is interest in a loan schedule calculated?Show answer

   Opening balance × rate per period.

6. 6.Name the four applications in the syllabus.Show answer

   BRS, asset accounting, loan repayment schedule, ratio analysis.

7. 7.Is payroll accounting in the 2026-27 syllabus?Show answer

   No; it is in NCERT but not named in the syllabus.

8. 8.Why use ROUND in a ratio sheet?Show answer

   To show ratios to a fixed number of decimal places, such as two.

Quick revision checklist

The night before the exam: can you do each of these without looking?

• ☐Can you list the steps for designing a spreadsheet application?
• ☐Can you prepare a BRS layout with one formula?
• ☐Can you compute cost to use?
• ☐Can you write SLN and DB with correct arguments?
• ☐Can you set the period and month arguments for part-year use?
• ☐Can you write PMT with matching rate and periods?
• ☐Can you build interest, principal and balance columns?
• ☐Can you explain why fv is zero?
• ☐Can you write ratio formulas that refer to cells?
• ☐Can you add an IF comment to a ratio?

Use of Spreadsheet in Business Applications: frequently asked questions