- Practice
- About 22 min
- 5 practice questions
MS Word and MS Excel practical for accounting
Carry out the textbook's MS Word tasks and shortcuts, and use Excel's statistical, text, logical and financial functions (including SLN, PMT and RATE) and charts on accounting data.
If something looks wrong, please tell us.
By the end of this lesson you can
- Create, save, format and lay out a Word document using menus and keyboard shortcuts.
- Use AVERAGE, MAX, MIN, COUNT, COUNTA, COUNTIF, CONCATENATE, UPPER, LOWER, AND, OR and IF in Excel.
- Calculate depreciation with SLN and a loan instalment with PMT, and present data in a chart.
The unit ends with practical work in two general purpose programs from MS Office. The steps below are written the way the Tamil Nadu textbook gives them. Menu names and their positions differ between versions of Office (newer versions use a ribbon, and some commands sit under different tabs), so if a step does not match your screen, look for the command of the same name.
MS Word: the main tasks
| Task | Steps or shortcut (as in the textbook; versions differ) |
|---|---|
| Create a file | Start, All Programs, Microsoft Office, Microsoft Word; then File, Save As, give a file name, Save |
| Open a file | File, Open, choose the file, Open |
| Save an existing file | File, Save, or Ctrl+S |
| Close a file | File, Close |
| Insert a table | Insert, Table, choose the number of rows and columns |
| Bold, italic, underline | Ctrl+B, Ctrl+I, Ctrl+U |
| Align left, right, centre, justify | Ctrl+L, Ctrl+R, Ctrl+E, Ctrl+J |
| Line spacing single, double, 1.5 | Ctrl+1, Ctrl+2, Ctrl+5 |
| Page layout | Margins (normal, narrow, wide), orientation (portrait, landscape), size (A4, A5), columns |
| Page numbers | Insert, Page Number, choose top or bottom of the page |
Worked example 1: formatting a short note in Word
Practice text (written for this lesson): A trial balance is a list of ledger balances. It checks the arithmetical accuracy of the books. Task: make trial balance bold, arithmetical accuracy italic, underline ledger, justify the paragraph and set 1.5 line spacing. Steps: type the text; select trial balance and press Ctrl+B; select arithmetical accuracy and press Ctrl+I; select ledger and press Ctrl+U; select the whole paragraph and press Ctrl+J, then Ctrl+5; save with Ctrl+S.
MS Excel: functions in the textbook
| Group | Function | What it returns | Form |
|---|---|---|---|
| Statistical | AVERAGE | The average | =AVERAGE(range) |
| Statistical | MAX / MIN | The largest / smallest value | =MAX(range), =MIN(range) |
| Statistical | COUNT | Number of cells containing numbers | =COUNT(range) |
| Statistical | COUNTA | Number of cells that are not empty | =COUNTA(range) |
| Statistical | COUNTIF | Number of cells meeting a condition | =COUNTIF(range,criteria) |
| Text | CONCATENATE | Several texts joined into one | =CONCATENATE(text1,text2) |
| Text | UPPER / LOWER | Text in capitals / small letters | =UPPER(text), =LOWER(text) |
| Logical | AND | TRUE only if all conditions are true | =AND(test1,test2) |
| Logical | OR | TRUE if any one condition is true | =OR(test1,test2) |
| Logical | IF | One value if a test is true, another if false | =IF(test,value_if_true,value_if_false) |
| Financial | SLN | Straight line depreciation for one period | =SLN(cost,salvage,life) |
| Financial | PMT | Periodic payment on a loan | =PMT(rate,nper,pv,fv,type) |
| Financial | RATE | Interest rate per period of a loan | =RATE(nper,pmt,pv,fv,type) |
Worked example 2: statistical and logical functions
Made-up data: daily cash sales of a shop for five days are entered in B2:F2 as 4,200; 3,800; 5,600; 2,900; 4,500. =AVERAGE(B2:F2) gives (4,200 + 3,800 + 5,600 + 2,900 + 4,500) ÷ 5 = 21,000 ÷ 5 = 4,200. =MAX(B2:F2) gives 5,600 and =MIN(B2:F2) gives 2,900. =COUNTIF(B2:F2,">4000") counts 4,200, 5,600 and 4,500 = 3. In G2, =IF(AVERAGE(B2:F2)>=4000,"Target met","Below target") shows Target met. =AND(B2>=3000,C2>=3000) is TRUE (4,200 and 3,800 are both at least 3,000); =AND(E2>=3000,F2>=3000) is FALSE because 2,900 is below 3,000, while =OR(E2>=3000,F2>=3000) is TRUE because 4,500 qualifies.
Worked example 3: SLN depreciation
Made-up data: a delivery van costs ₹4,80,000 plus ₹20,000 registration, so total cost = ₹5,00,000 (in Excel, =SUM of the two cells). Salvage value ₹50,000, life 5 years. =SLN(500000,50000,5) returns (5,00,000 − 50,000) ÷ 5 = ₹90,000 a year. Check: 90,000 × 5 = 4,50,000, and 4,50,000 + 50,000 = 5,00,000, the cost. As the textbook does, include installation and transport charges in the cost before applying SLN.
Worked example 4: loan instalment with PMT
Made-up data: a loan of ₹1,00,000 at 12% a year, repaid in 12 equal monthly instalments. Because payments are monthly, the rate per period is 12% ÷ 12 = 1%, and nper = 12. Enter the loan as a negative present value, as the textbook does: =PMT(12%/12,12,-100000,0,0) returns about ₹8,884.88 a month. Check: 12 × 8,884.88 = 1,06,618.56 repaid, so total interest is about ₹6,618.56, which is less than a full year's 12,000 on the whole loan, as it should be, since the balance falls each month. RATE works the other way: given nper, the payment and the loan, it returns the rate per period; multiply by 12 for a yearly rate when payments are monthly.
Charts
To draw a chart, as the textbook describes: enter the data, select the range including headings, go to the Insert menu and choose the chart type (column, line or pie), then right-click the chart and choose Add Data Labels; axis titles are added from the chart tools. A chart type can be changed later, for example from column to line. Use a column chart to compare amounts, a line chart to show change over time, and a pie chart to show shares of a whole.
Making a chart in Excel
- Enter data Labels and values
- Select range Include headings
- Insert chart Column, line or pie
- Add data labels Right-click the chart
- Name the axes From the chart tools
In PMT and RATE, match the rate to the payment period: monthly payments need the yearly rate ÷ 12.
Key takeaways: Word shortcuts save time (Ctrl+S save, Ctrl+B/I/U formatting, Ctrl+L/R/E/J alignment, Ctrl+1/2/5 spacing). Excel's statistical, text, logical and financial functions handle accounting data; SLN gives straight line depreciation, PMT a loan instalment and RATE a loan's interest rate. Menus differ between Office versions, but the commands keep their names.
Check your understanding
-
1. In MS Word, the shortcut to justify a paragraph is:
- Ctrl+J
- Ctrl+E
- Ctrl+L
- Ctrl+5
Show answer
Answer: A. Ctrl+J justifies, Ctrl+E centres, Ctrl+L aligns left, and Ctrl+5 sets 1.5 line spacing.
-
2. Made-up data: cells A1:A6 hold 120, 450, 'Cash', 980, blank, 1,250. What do =COUNT(A1:A6) and =COUNTA(A1:A6) return?
- 5 and 5
- 4 and 6
- 4 and 5
- 3 and 5
Show answer
Answer: C. COUNT counts cells with numbers: 120, 450, 980, 1,250 = 4. COUNTA counts non-empty cells: those four plus 'Cash' = 5; the blank cell is not counted.
-
3. Made-up data: a machine costs ₹1,20,000, salvage value ₹20,000, life 8 years. =SLN(120000,20000,8) returns:
- ₹15,000
- ₹17,500
- ₹10,000
- ₹12,500
Show answer
Answer: D. SLN = (cost − salvage) ÷ life = (1,20,000 − 20,000) ÷ 8 = 1,00,000 ÷ 8 = ₹12,500.
-
4. A cell holds 46. What does =IF(A1>=50,"Pass","Fail") show?
- Pass
- Fail
- TRUE
- 46
Show answer
Answer: B. 46 is not greater than or equal to 50, so the test is false and IF returns the second value, Fail.
-
5. For a loan repaid in monthly instalments at 9% a year, the rate argument in PMT should be:
- 9%
- 9%×12
- 9%/12
- 12/9%
Show answer
Answer: C. The rate must match the payment period. Monthly payments need the monthly rate, 9% ÷ 12 = 0.75%.
Sign in to keep track of the lessons you finish.