Spreadsheets

Free FIA MA1 Notes

Click here to download

Introduction to Spreadsheets

This chapter looks at the use and functions of spreadsheets

The spreadsheet system

Spreadsheets present users with a grid. Each column is denoted by an alphabetical character and each row by a number. This system of co-ordinates allows a cell to be defined:

Cells can contain:

• Numbers: cells D3 and D4 above
• Labels (text): cells D2 and C3..C5 above
• Formulae: cell D5, although displaying 5,000 could actually contain the formula

=D3 – D4

Formulae give spreadsheets their great power because calculations can be automatically updated. Therefore, in the example above if the contents of cell D3 were changed to 21,000 then cell D5 would automatically display 6,000

Formulae can be complex and can contain ready-made functions such functions to average the contents of cells or to add up a column or row.

Formulae always start with ‘=’. This tells the program that what follows is a formula and not a word beginning D3…., in cell D5 above.

The role of spreadsheet in management accounting, and their advantages and limitations.

Spreadsheets can be used to great advantage in many parts of management accounting.

For example:

• Budgeting: budgets are always subject to estimates and changes. Because formulae automatically update calculations, new budgets can quickly be produced.
• What-if queries: for example, what-if sales decrease by 5%. Again these experiments can be carried out very quickly.
• Variance analysis: comparison of actual and budgeted results and teh highlighting of variances.
• Production of graphics: all spreadsheets allow fast production of graphs and charts.

Spreadsheets can be limited in the following areas.

• If incorrectly constructed they will give incorrect results
• Errors can be hard to discover.
• They are not suitable for large amounts of data, such as all the information held on customers. There, a database package would be more suitable.

The information that is needed in spreadsheets and how it should be structured.

Apart from labels (descriptions) the information needed in spreadsheets is of three types:

1) Estimates

2) Definite data

3) The relationships that hold between pieces of data

These three together will produce the results of the spreadsheet.

For example:

Presumably the sales volumes are estimates

The selling prices and GP% could be definite data (or estimates)

The sales revenues and gross profits are calculated data. Here the formulae underlying the numbers displayed are shown in small type in each cell, but the formulae would not be displayed like this on a real spreadhseet.

Rules for structuring the data are:

• Never enter the same figure more than once. So here, the GP% is recorded once only. That means it will be consistently used and will be easy to change.
• Do not type in any figure that could be calculated from others on the spreadsheet.
• Ensure the data is well-labelled so that it is understandable.

Information is usually entered by clicking on the cell then typing in the data.

However, spreadsheets allow information to be copied from one cell to others. So 1,000 can be entered into B7 then copied across to the other months.

Particularly useful is that when formulae are copied, their references are automatically updated. Therefore if =B6 – B7 is entered in B8 and copied to C8 and D8, the formulae will automatically become =C6 – C7 and =D6 – D7.

Information in cells can be easily changed by clicking on the cell and re-entering or amending its contents.

Whole ranges of cells can be specified for data entry, copying or formatting by their top left and bottom right corners. So copying a cell content to the range G3:J9 would copy into the 28 cells specified by those coordinates: G3, H3, I3, J3, all the way down to G9, H9, I9, J9.

Formatting tools

Correct formatting can greatly improve the usability of a spreadsheet and, indirectly its accuracy because errors should be easier to spot.

Typical formatting used is:

• Setting the number of decimal places
• Left, centre or right aligned contents in cells
• Bold or coloured text
• Coloured cell backgrounds
• Underlinings
• Size of text presented
• Different column widths and heights

Here is an example of formatting:

 Spreadsheet Formulae

The following formulae are shown in the spreadsheet extracts shown below

Here is the spreadsheet again, Showing the formulae behind the amounts displayed.

Spreadsheet Errors

Most spreadsheets have error reporting facilities. The more sophisticated tools allow formulae to be traced from cell to cell. However, even as data is entered into spread sheet cells some errors will be reported.

In the extract below from an Excel spreadsheet, the small triangles at the top left of B2, B4 and so on indicate that there could be a problem. The second spreadsheet shows the formulae that lie behind the problem cells.

Cell B2 Contains a calculation using cells that have no values yet; C2 and D2 are empty

Cell B4 Tries to perform a calculation on a label, the word ‘Sales’

Cell B6 Tries to divide by zero (Cell D6)

Cell B8 Contents too large to be shown in the cell. This is easily fixed by making the column wider.

Cell B10 Contains an unknown function name.
Should be =Sum(C10:D10)

Additionally, you might sometime be warned about circular references. For example, if you were to enter the following in Cell C12:

=C12 + D12

Then the answer to cell C12 depends on the contents of cell C12 and the spreadsheet would warn you about that, or not permit that formula to be entered.

Linking and embedding data from different sources

Often excerpts from a spreadsheet need to be incorporated into a report produced on a word processor. There are several ways in which this can be done:

1. Simple copying. In the spreadsheet highlight the range of cells you are interested in. Press Ctrl C to copy these. Then in the word processor document, press Ctrl V. This pastes a copy of the figures into the report, but there is no spreadsheet functionality there: they are just like typed figures. This can be inefficient if the spreadsheet figures change later and you have to make sure the report is brought up to date accurately.
2. Embedding. As before, copy from the spreadsheet, but select Paste Special in the word processor. This copies the selected range into the report but maintains the functionality of the spreadsheet.  It’s like having a mini-spreadsheet within the report. However, there are two copies of the cells – one in the spreadsheet and one in the word processor file. These are now quite separate and changes in one will not affect the other.
3. Linking.  As above but in Paste Special choose the link option. This keeps the two copies of the spreadsheet cells linked so that updating the spreadsheet can be automatically reflected in the word processor

In addition to linking spreadsheets to word processors it is possible to link several spreadsheets together. For example, there could be a spreadsheet for each of three branches, then a fourth where the results of the branches are added together.

Storing and retrieving spreadsheets

Selecting ‘Save’ for the first time will prompt you to enter a name for the spreadsheet. Thereafter, ‘Save’ overwrite the previous copy of the spreadsheet.

‘Save as’ allows you to select a different name for the spread sheet. This can be useful if you want to keep several editions of the spreadsheet.

Retrieving is a simple matter of navigating to the folder where the spreadsheet was stored and clicking, or double-clicking, on it.

Most systems also provide a list of recently opened spreadsheets.