What is a spreadsheet? Main features
A spreadsheet is a computer program that shows a big table (worksheet) made of columns (A, B, C…) and rows (1, 2, 3…). The box where a row meets a column is a cell; its name is its cell address, like C5. A file of many worksheets is a workbook.
Features
- Formulas: start with =, e.g. =B2*10%.
- Built-in functions: SUM, AVERAGE, IF, ROUND, PMT, SLN, DB, VLOOKUP, DATE.
- Automatic re-calculation: change one input and all results update.
- Cell references: relative (B2 moves when copied) and absolute ($B$2 stays fixed).
- Formatting: ₹ currency, bold headings, borders.
- Sort, filter and data validation (allow only numbers, only dates).
- Charts and graphs made from selected data.
- Protection: lock cells or sheets with a password.
Uses in accounting
Bank reconciliation, asset and depreciation schedules, loan repayment schedules, payroll (salary sheet with DA, HRA, PF), ratio analysis, budgets and graphs.
Bank reconciliation statement in a spreadsheet
Put the starting balance in one cell and the items in the next rows, with a + or − column. One formula gives the answer.
| Row | A (item) | B (₹) |
|---|---|---|
| 1 | Balance as per cash book | 25,000 |
| 2 | Add: cheques issued, not yet presented | 4,000 |
| 3 | Less: cheques deposited, not yet cleared | 6,000 |
| 4 | Less: bank charges not in cash book | 500 |
| 5 | Balance as per pass book =B1+B2-B3-B4 | 22,500 |
An IF function can decide the sign automatically, e.g. =IF(C2="Add",B2,-B2).
Asset accounting: depreciation schedule
Straight line method (SLM): =SLN(cost, salvage, life). For cost ₹1,00,000, scrap ₹10,000, life 5 years: (1,00,000 − 10,000) ÷ 5 = ₹18,000 a year. Book value: 82,000 → 64,000 → 46,000 → 28,000 → 10,000.
Written down value (WDV) method: depreciation = opening book value × rate. At 20% on ₹1,00,000: 20,000, then 16,000, then 12,800… In a sheet: =B2*20% and copy down. The DB function can also give declining balance depreciation.
Columns usually are: Year, Opening value, Depreciation, Closing value. Closing value of one year becomes the opening value of the next (=D2 in B3).
Loan repayment schedule
Equal instalment (EMI): =PMT(rate, periods, −loan). For ₹1,00,000 at 10% a year for 4 years, PMT(10%,4,-100000) = ₹31,547 (rounded) each year. Each year: interest = opening balance × 10%; principal repaid = EMI − interest; closing balance = opening − principal.
Equal principal repayment: repay ₹25,000 principal each year. Interest: 10,000 (on 1,00,000), 7,500 (on 75,000), 5,000, 2,500. Total paid falls each year.
The schedule shows how much of each payment is interest, which is an expense, and how much reduces the loan, which is a liability.
Ratio analysis with a spreadsheet
Keep balance sheet and profit and loss figures in one sheet and ratios in another, linked by formulas. Examples: current ratio =CA/CL; quick ratio =(CA−Inventory−Prepaid)/CL; debt-equity =Debt/Equity; gross profit ratio =GP/Revenue*100. If current assets are ₹2,00,000 and current liabilities ₹1,00,000, =B1/B2 gives 2, i.e. 2 : 1. Change a figure and every ratio updates, which is useful for comparing years.
Graphs and charts
Select the data and choose Insert → Chart. Common charts:
- Column/bar chart: compare items, e.g. sales of four years.
- Line chart: show a trend over time, e.g. monthly expenses.
- Pie chart: show parts of a whole, e.g. share of each expense in total expenses.
- Area and scatter charts for special comparisons.
Parts of a chart: title, axes, legend, data labels, gridlines. The chart is linked to the cells, so it changes when the data changes.
Key formulas and definitions
- =SUM(B2:B10) adds a range
- =IF(condition, value if true, value if false)
- =SLN(cost, salvage, life) → straight line depreciation per year
- =PMT(rate, periods, −loan) → equal instalment (EMI)
- =VLOOKUP(value, table, column, FALSE) → find matching data
- Current ratio = Current assets ÷ Current liabilities
Worked examples
1. B2 = 50,000 (sales), B3 = 30,000 (cost). Write a formula for profit in B4, then find B4 if B3 becomes 35,000.
=B2-B3. First 20,000; after the change 15,000, updated automatically.
2. Cash book balance ₹25,000; cheques issued not presented ₹4,000; cheques deposited not cleared ₹6,000; bank charges ₹500. Pass book balance?
=25000+4000-6000-500 = ₹22,500.
3. Machine cost ₹1,00,000, scrap ₹10,000, life 5 years. Find yearly depreciation by =SLN and the book value after 3 years.
=SLN(100000,10000,5) = 18,000. After 3 years: 1,00,000 − 54,000 = ₹46,000.
4. WDV at 20% on ₹1,00,000. Find depreciation for years 1, 2 and 3.
Year 1: 20,000 (BV 80,000). Year 2: 16,000 (BV 64,000). Year 3: 12,800 (BV 51,200).
5. Loan ₹1,00,000 at 10%, principal repaid ₹25,000 each year. Find interest for each of the 4 years and total interest.
10,000; 7,500; 5,000; 2,500. Total interest = ₹25,000.
6. Find the yearly EMI with =PMT(10%,4,-100000) and split year 1 into interest and principal.
EMI ≈ ₹31,547. Year 1 interest = 10,000; principal = 21,547; closing balance = 78,453.
7. Current assets ₹2,00,000 (inventory ₹50,000), current liabilities ₹1,00,000. Write formulas for current and quick ratios.
Current = 200000/100000 = 2 : 1. Quick = (200000−50000)/100000 = 1.5 : 1.
Common mistakes
- Forgetting the = sign. Without it the cell just shows the text B2-B3.
- Copying a formula that should always point to the rate cell without making it absolute ($B$1). Copies then point to empty cells.
- Entering the loan as a positive number in PMT and getting a negative EMI. Put −loan, or read the minus as money going out.
- Using a pie chart for a trend over years. Use a line or column chart for time; pie is for parts of a whole.