Computerised Accounting — Part 2: Electronic Spreadsheets in Accounting: visual lesson, CBSE Class 12 Accountancy

Lesson

Electronic spreadsheets in accounting

Part 2 — formulas and functions, and four accounting jobs a spreadsheet does well: bank reconciliation, depreciation, loan schedules and ratio analysis, plus charts.

In this lesson you will

Write spreadsheet formulas with relative references, locked references and names, and use SUM, IF, ROUND and VLOOKUP
Prepare a bank reconciliation statement in a spreadsheet
Prepare depreciation schedules with SLN, written-down value formulas and DDB, and a loan schedule with PMT
Compute accounting ratios with formulas and choose a suitable chart for accounting figures

Part 2 at a glance

Concept

Cells, formulas and results

Each formula cell shows the formula and, after the arrow, what the cell displays.
	A	B	C	D
1	Item	Quantity	Rate (₹)	Amount (₹)
2	Office bags	40	850	`=B2C2` → 34,000*
3	Belts	120	210	`=B3C3` → 25,200*
4	Wallets	90	260	`=B4C4` → 23,400*
5	Total			`=SUM(D2:D4)` → 82,600
6	Discount (2% above ₹50,000)			`=IF(D5>=50000,ROUND(D52%,0),0)` → 1,652*
7	Amount payable			`=D5-D6` → 80,948

Concept

Functions used in accounting

FALSE in VLOOKUP asks for an exact match.
Function	What it returns	Used in this chapter for
`SUM(range)`	Total of the numbers in the range	Adding items in a reconciliation
`IF(test, if true, if false)`	One of two results, by a test	Labelling a balance favourable or overdraft
`ROUND(number, digits)`	Number rounded half away from zero	Rupee interest; ratios to two decimals
`PMT(rate, periods, amount)`	Equal instalment per period	Loan instalments
`SLN(cost, salvage, life)`	Straight-line depreciation per year	Depreciation schedules
`DDB(cost, salvage, life, period)`	Double declining balance charge for a period	Depreciation schedules
`VLOOKUP(value, table, column, FALSE)`	Item from the matching row of a table	Finding a ledger from its code

Cookies