Accounting Application of Electronic Spreadsheet — Notes

An electronic spreadsheet is a program that arranges a worksheet of columns (A, B, C …) and rows (1, 2, 3 …); each cell holds a label, a value or a formula, and formulas recalculate automatically when a figure they use changes.

Utility in accounting: payroll; sales, profit and commission; journals with totals and ledger balances; debtors with due dates; budgets and what-if analysis; depreciation and loan schedules; reports and charts.

Merits: automatic recalculation and accuracy; what-if analysis; ready-made functions; formatting, sorting, filtering and charts; one design reused every period; compact storage, easy correction and sharing.

Demerits: no built-in double-entry control; silent spread of a wrong reference or formula; formulas can be overwritten; weak security and no record of changes; unsuited to very large volumes or many users; needs training and backups.

A workbook is the file; it holds worksheets, each with a tab. Columns run A … Z, AA, AB …; a cell address is column then row (B3); a range is written first cell:last cell (B2:E9).

Entering and editing: type and press Enter (down) or Tab (right); text aligns left, numbers right; double-click or F2 to edit; the fill handle continues series and copies formulas.

Navigating: arrow keys; Ctrl + Home (to A1); Ctrl + arrow (edge of the data); the name box (go to an address).

Saving: Save overwrites the same file; Save As creates a copy with a new name, place or format.

Worksheets: insert, rename, delete, move (references still work), copy (a duplicate with the same formulas), and move or copy into another open workbook. A reference to another sheet is the sheet name, an exclamation mark and the cell: =May!B6.

Formatting cells: font, size, bold and colour; alignment, wrap text, merge and centre; number formats (comma style, decimals, percentage, currency, dates such as dd-mm-yyyy or dd-mmm-yyyy); column width and row height (auto-fit; #### means the column is too narrow); borders and fill colours; conditional formatting.

A format changes only the display; formulas use the stored value (two cells showing 10.00 can add to 20.01). A date is stored as a count of days, so dates can be added to and subtracted.

Page set-up and printing: orientation (portrait or landscape); paper size and margins; scaling (fit to one page wide); print area; print titles (rows to repeat at the top of each page); header and footer; gridlines; print preview before printing.

A formula begins with =. Order: brackets, then × and ÷, then + and −. Comparison operators (> < >= <= = <>) give TRUE or FALSE; & joins text.

References: relative (B2) moves when copied; absolute (a dollar sign before the column letter and before the row number) and named cells (DARate, NetTotal) do not; mixed locks one part.

Functions: SUM, AVERAGE (skips blanks), MAX, MIN, COUNT, COUNTIF, ROUND(number, places); percentage = part ÷ whole × 100, often =ROUND(B3/B7*100,2); IF(test, value if true, value if false), nested IF for slabs and grades, with AND / OR for joint tests; dates: DATE(year, month, day), DAY, MONTH, YEAR, TODAY(); date − date = days, date + days = a later date.

Freeze panes: select the cell below the rows and right of the columns to keep; those rows and columns stay on screen while scrolling (screen only; use print titles for paper).

Gross pay = basic pay + DA + HRA + TA + CCA (+ other allowances). Net pay = gross pay − deductions (provident fund, income tax deducted at source, others).

Design: employees in rows; rates and limits in their own named cells (DARate, HRARate, PFRate, TaxLimit …); formulas in row 2 copied down; a totals row.

Formula patterns (Sarangi Spice Exports): DA =ROUND(B2*DARate,0); HRA =ROUND(B2*HRARate,0); TA =IF(B2<30000,1600,2400); gross =SUM(B2:F2); PF =ROUND((B2+C2)*PFRate,0); income tax =IF(G2>TaxLimit,ROUND((G2-TaxLimit)*TaxRate,0),0) (a simplified rule for this course; real income-tax rules are annual and slab-based); net =G2-J2.

Check: net pay + deductions = gross pay, row by row and in total.

Sales sheet: profit = sales − cost of goods sold; profit on sales (%) = ROUND(profit/sales*100,2) (on cost would be a mark-up). Commission by IF: a single rate by a test (=IF(B4>=Target,ROUND(B4*3%,0),ROUND(B4*1%,0))) or slabs (2% up to the target, 5% on the excess: =IF(B4>Target,ROUND(Target*2%+(B4-Target)*5%,0),ROUND(B4*2%,0))); a status label by IF; COUNTIF for the number who met the target.

Student marks sheet: total =SUM(B2:E2); percentage; result by the lowest mark: =IF(MIN(B2:E2)>=33,"Pass","Fail") (or AND over each paper); grade by a nested IF from the highest cut-off down; COUNTIF for passes, MAX and AVERAGE for class figures.

Layout: Date, Account, Debit, Credit, with one line per account in each entry (debit line first, then the credit line; a narration column may follow).

Totals and check: =SUM(C2:C13) and =SUM(D2:D13); =IF(C14=D14,"Agreed","Check entries"); the difference =C14-D14 helps trace a slip (a figure keyed as 1,800 instead of 18,000 leaves 16,200).

Ledger balances by SUMIF: =SUMIF(B2:B13,"Cash",C2:C13)-SUMIF(B2:B13,"Cash",D2:D13) gives the cash balance (positive = debit balance).

The spreadsheet itself accepts an unbalanced entry; only the check built by the user catches it.

Charts: select the data with its labels, insert a chart, add a title, axis titles, legend and data labels, and format it; it redraws when the figures change. Column or bar to compare; line for a trend; pie for parts of one whole; stacked column for parts across periods; scatter for two related measures.

Database (list): one heading row of field names; each row a record; no blank rows or columns; one kind of entry per column.

Sort: select the whole list; sort by one or more keys, ascending (A to Z, smallest first) or descending.

Filter: hides records that fail the conditions (AutoFilter, custom conditions such as Amount ≥ 40,000 and Status = Due); nothing is deleted; clear the filter to see all.

Query: a question answered from the list, by a filter or by COUNTIF / SUMIF, for example =SUMIF(E2:E9,"Due",D2:D9).