Spreadsheets in Cost and Management Accounting

Spreadsheets are the everyday tool of the accountant. Budgets, cost tables, variance reports and break-even calculations are nearly all built in a grid of rows and columns. The same grid can then be turned into charts and summaries. This topic covers what a spreadsheet is, its main features, what it is used for in general, and how it supports data analysis, cost accounting and management accounting.

Examiners ask for a definition, the types of content a cell can hold, and lists of features or uses. Learn the lists below and add a one-line explanation for each item you give.

What a spreadsheet is

A spreadsheet is an electronic document that organises data in rows and columns so that users can calculate, analyse and store it in table form. It is the digital version of a paper ledger or worksheet. Spreadsheets are used in business, education, finance, research and personal tasks. Spreadsheet packages let you work with several sheets that refer to one another.

Rows, columns and cells

• Rows run across the sheet (horizontal) and are numbered 1, 2, 3 and so on.
• Columns run down the sheet (vertical) and are lettered A, B, C and so on.
• A cell is the box where a row and a column meet. Its address is its column letter followed by its row number, such as B3.
• The active cell is the one the cursor is in at the moment.

What a cell can hold

In Microsoft Excel every formula starts with an equals sign, followed by the items to be calculated and the operators. For instance, =D5/F8 divides the value in D5 by the value in F8. When the numbers in D5 or F8 change, the answer updates itself. This automatic recalculation is the main reason accountants prefer spreadsheets to hand calculation.

Features of spreadsheets

General uses of spreadsheets

• Budgeting and financial planning. Tracking income, expenses, savings and investments.
• Data analysis. Analysing sales, survey results or research data.
• Inventory management. Monitoring stock levels, orders and product information.
• Project management. Building timelines, task lists and progress trackers.
• Scheduling and timetables. Planning calendars, appointments or class schedules.
• Reporting. Producing professional reports and dashboards.
• Record keeping. Contact lists, attendance logs and membership databases.
• Academic use. Calculating grades and tracking assignments.
• Business forecasting. Predicting trends from historical data.
• Data entry and storage. Keeping large amounts of structured data for easy access.

Spreadsheets in data analysis, cost and management accounting

Spreadsheets are widely used in data analysis and in cost and management accounting because they are accessible and flexible, and they contain tools to clean, organise, manipulate and display data. The syllabus sets out the use in a series of steps.

The ideas in more depth

Relative, absolute and mixed references

When you copy a formula down a column, the cell references normally shift with it. This is a relative reference. If the formula in C2 is =B2*2 and you copy it to C3, it becomes =B3*2. A reference that must not move is made absolute by placing a dollar sign before the column letter and the row number, such as $F$1.

A mixed reference fixes only one part, such as $F1 or F$1.

Suppose B2:B4 hold prices and F1 holds a tax rate of 7.5%. The formula =B2*$F$1 can be copied down to C3 and C4, and every row still points to the single rate in F1. If the rate changes, you alter F1 once and every result updates.

Why a model beats hand calculation

A model is a spreadsheet built so that inputs sit in one place and calculations point to them. To test a different selling price, you change one input cell. This is how what-if analysis works. It is much faster than redoing the sums, and it reduces arithmetic mistakes, though it cannot remove errors in the logic of the formulas themselves.

Risks and good practice

• A wrong cell reference gives a wrong answer that looks neat. Check key formulas by working a sample by hand.
• Use data validation to stop impossible entries such as negative quantities.
• Label inputs clearly and keep them apart from calculations.
• Protect files with back-ups and controlled access, because a spreadsheet may hold sensitive cost data.

A cost model laid out in cells

The contribution per unit is ₦250 − ₦150 = ₦100. Dividing fixed costs of ₦600,000 by ₦100 gives 6,000 units. If you change B3 to ₦270, the contribution becomes ₦120 and B5 shows 5,000 units (₦600,000 ÷ ₦120). You can see the effect of a price change instantly. Break-even analysis is covered in detail later in the course.

Worked example: variance analysis in a spreadsheet

1. Variance formula.
   In D2 enter =C2-B2.
   The result is ₦515,000 − ₦480,000 = ₦35,000.
2. Interpret it. Actual cost is higher than budget, so the variance is adverse.
3. Percentage formula.
   In E2 enter =D2/B2.
   The result is ₦35,000 ÷ ₦480,000 = 0.0729, or 7.3% after formatting the cell as a percentage.
4. Next step. Examine the cause: a price rise, higher usage, waste or a poor budget. Spreadsheets can split the variance into price and quantity effects with further formulas.
5. Tax example.
   For a price of ₦20,000 in B2 and a rate of 7.5% in F1, enter =B2*$F$1 in C2.
   The result is ₦20,000 × 0.075 = ₦1,500.

Cell references and formulas

A spreadsheet works through formulas that refer to cells. A formula starts with an equals sign. If cell B2 holds 120,000 and C2 holds 45,000, the formula =B2-C2 in D2 gives 75,000, and it updates whenever B2 or C2 changes. There are three kinds of cell reference.

Suppose the value added tax (VAT) rate sits in cell F1. A formula in C5 =B5*$F$1 can be copied down column C: each row uses its own sales figure (B5, B6, B7 …) but always the same rate in F1. Without the dollar signs, the reference would drift down to F2, F3 and give wrong answers or zeros.

Common functions for accountants

The statistical functions are especially useful in management accounting. The regression and correlation calculations done by hand in the cost behaviour topics take one formula each: SLOPE gives b, INTERCEPT gives a and CORREL gives r.

Building a simple financial model

A model separates the inputs (assumptions) from the calculations, so that a change to one assumption flows through all the results. A cash budget model for three months might be laid out like this.

With sales of 130 in February and 120 in January, the formula in C6 gives 130 × 20% + 120 × 80% = 26 + 96 = 122. If the collection percentages change to 30% and 70%, only B1 and B2 need editing: C6 becomes 130 × 30% + 120 × 70% = 123. The rest of the model updates itself.

What-if and sensitivity analysis

Because formulas recalculate instantly, a spreadsheet lets managers ask “what if?” questions. If the break-even model has the selling price in one cell, variable cost in another and fixed costs in a third, changing the price from ₦50 to ₦47 shows the new break-even at once. Tools such as Goal Seek work backwards: set the target profit cell to ₦300,000 and ask the spreadsheet which sales volume achieves it.

A data table shows results for a range of inputs at once, for example the profit at selling prices from ₦44 to ₦56. This is the sensitivity analysis described in the cost-volume-profit (CVP) topics.

Charts and presentation

Charts turn columns of figures into a picture. Choose the chart to suit the question. A line chart shows a trend over time (monthly sales), a column or bar chart compares items (sales by region), a pie chart shows shares of a whole (cost breakdown) and a scatter chart shows the relationship between two variables (cost against activity), which is where a regression line can be added.

Label axes, give a title and use a sensible scale. A chart with a truncated axis can mislead by exaggerating small differences.

Risks and controls in spreadsheets

Spreadsheets are easy to build, which is also their weakness. Studies of business spreadsheets regularly find errors in a large share of them, and an error in a budget model can lead to a poor decision. Common risks and controls:

Limitations of spreadsheets

• They do not enforce the accounting rules of a full accounting system, such as double entry.
• Large data sets become slow and difficult to audit.
• They are hard to share and update among many users at once without a controlled process.
• They rely on the skill and care of the person who builds them.
• For routine transaction processing, a dedicated accounting system is safer.

Common mistakes in the exam

• Defining a spreadsheet without mentioning rows, columns and calculation.
• Listing features as single words such as “charts” without saying what they do.
• Confusing a cell with a row or column. A cell is the intersection of the two.
• Forgetting that every formula begins with an equals sign.
• Giving only general uses when the question asks about cost and management accounting.
• Assuming a spreadsheet cannot be wrong. Errors in formulas and data still occur.

Quick revision

• Spreadsheet: electronic grid of rows (numbers) and columns (letters). Cell = intersection. Contents: text, values, formulae.
• Features: formulas and functions, formatting, charts, sort and filter, validation, pivot tables, collaboration, cell references, macros.
• Accounting uses: cleaning and summarising data, cost analysis, break-even, variance analysis, budgeting, what-if forecasts.
• Absolute reference uses $ signs, for example $F$1.

Practice questions

These are original questions written in the style of the Institute of Chartered Accountants of Nigeria (ICAN) exam. Try each one before you open the answer.

Section A style: multiple choice

Section B style: written questions

A further written question

Model answers

Spotted a mistake? Tell us and we will check it.