8.4 Writing Costing Formulas the Way AAT Credits Them

AAT Level 2 Principles of Costing PCTN

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the AAT Principles of Costing (PCTN) Assessment

2 Chapter 1: The Cost Accounting Framework

1.1 Purpose and Role of Cost Accounting 1.2 Cost Accounting versus Financial Accounting 1.3 Responsibility Accounting and Cost Centres

3 Chapter 2: Cost Recording, Sourcing, and Coding

2.1 Cost Units and Cost Objects 2.2 Cost Coding Systems and Principles 2.3 Source Documentation and Cost Data Collection

4 Chapter 3: Cost Classification, Behaviour and the Manufacturing Account

3.1 Cost Classification by Element, Nature, and Function 3.2 Cost Behaviour Patterns and Analysis 3.3 Product Costs, Period Costs and the Manufacturing Account

5 Chapter 4: Inventory Valuation and Control

4.1 Inventory Records and Control Procedures 4.2 FIFO and LIFO Inventory Valuation 4.3 AVCO Inventory Valuation and Method Comparison

6 Chapter 5: Labour Costing and Remuneration

5.1 Labour Remuneration: Time Rates, Basic Pay, and Overtime 5.2 Piecework Systems and Incentive Schemes 5.3 Labour Cost Accounting, Idle Time, and Payroll On-Costs

7 Chapter 6: Overheads and Overhead Absorption

6.1 Overheads: Collection, Allocation and Apportionment (Background) 6.2 Overhead Absorption Rates: Per Unit, Per Labour Hour and Per Machine Hour

8 Chapter 7: Budgeting and Variance Analysis

7.1 Budgetary Planning, Principles, and Functional Budgets 7.2 Fixed versus Flexed Budgets 7.3 Calculating Variances: Totals, Adverse or Favourable, and Percentages 7.4 Variance Investigation, Causes, and Management by Exception

9 Chapter 8: Product and Service Costs, and Spreadsheet Tools

8.1 Product Cost Build-Up and Mark-Up versus Margin 8.2 Costing Services: Calculating Costs in Service Organisations 8.3 Entering and Formatting Data in Spreadsheet Tasks 8.4 Writing Costing Formulas the Way AAT Credits Them

8.4 Writing Costing Formulas the Way AAT Credits Them

Key Takeaways Explain This Topic AI

PCTN accepts =SUM(first cell:last cell) for ranges and +, -, * and / between cell references, often combined in one formula.

AAT does not credit =SUM(B2,C2) with commas or =SUM(B1+B2+B3) with single cells in brackets; write =SUM(B2:C2) or =SUM(B1:B3).

AAT does not credit formulas that use PRODUCT, typed numbers in place of cell references, or unnecessary spaces.

AAT does not credit unnecessary signs such as =+C11-B11, or zero-padded references such as A01 instead of A1.

Brackets change the order of calculation, so a percentage change must be written =(C4-B4)/B4, not =C4-B4/B4.

Last updated: September 2026

8.4 Writing Costing Formulas the Way AAT Credits Them

Key Concept: PCTN learning outcome 4.2 expects you to build cost calculations from a short list of formula types: =SUM(A1:A10) for adding a range, and + , - , * and / between cell references. Often you will combine more than one in a single formula. AAT's specification also lists formula formats that will not be credited , even when they give the right answer. These include unnecessary spaces, commas in SUM, square or curly brackets, PRODUCT, and typed numbers in place of cell references. Learn the accepted styles and write every formula in them.

In AAT's review of the October 2023 examiner reports, tasks involving formulas were among the weakest in PCTN. Candidates often know the costing but lose marks because a formula is written in a style the marking scheme does not accept. This section fixes that.

The Accepted Formula Types

Every formula starts with an equals sign. Without it, the entry is just text. The specification lists these building blocks:

Operation Accepted format Example
Addition of a range =SUM(first cell:last cell) =SUM(A1:A10)
Addition of cells =cell+cell =A1+B10
Subtraction =cell-cell =A1-B10
Multiplication =cell*cell =A1*B10
Division =cell/cell =A1/B10

The specification adds that using formulas "will require students to combine more than one function for a calculation". For example, a unit cost could be =SUM(B4:B7)/B9 , which totals four costs and then divides by the number of units. Round brackets can be used to control the order of calculation, for example =(C4-B4)/B4 .

Formats AAT Will Not Credit

The specification states that students will not be credited for formulas that include any of the following:

Not credited Example of the problem Write instead
Unnecessary spaces = SUM(B4:B7) or =B4 * C4 =SUM(B4:B7) , =B4*C4
Commas in SUM =SUM(B2,C2) =SUM(B2:C2)
Square [ ] or curly { } brackets =[B4-C4]/B4 =(B4-C4)/B4
Brackets used to reference single cells =SUM(B1+B2+B3) =SUM(B1:B3)
PRODUCT for multiplication =PRODUCT(B4,C4) =B4*C4
Numbers in place of cell references =B4*14.50 when the rate is in C4 =B4*C4
Redundant references to cells not needed =SUM(B4:B9) when only B4:B7 hold costs =SUM(B4:B7)
Unnecessary positives or negatives =+C11-B11 or =-(B11-C11) =C11-B11
Adding 0s to cell references =A01+B09 =A1+B9

A useful habit: after typing any formula, read it back and ask three questions. Does it start with = and contain no spaces? Does it use only cell references, not typed numbers? Is every bracket round and actually needed?

Order of Calculation (BODMAS)

Spreadsheets follow the usual mathematical order: B rackets first, then D ivision and M ultiplication (left to right), then A ddition and S ubtraction (left to right).

=C4-B4/B4 calculates B4/B4 first (which gives 1) and then subtracts it from C4. That is wrong for a percentage change.

=(C4-B4)/B4 subtracts first and then divides, giving the change as a proportion of budget. That is correct.

Use brackets only when the order genuinely needs changing. =(B4*C4) has redundant brackets. Keep formulas as simple as the calculation allows.

Costing Formulas You Should Be Able to Write

Calculation Cell layout Formula
Total of four cost lines Costs in B4:B7 =SUM(B4:B7)
Unit cost Total cost in B8, units in B9 =B8/B9
Overhead absorption rate Budgeted overheads in B2, budgeted labour hours in B3 =B2/B3
Overhead absorbed by a job Job hours in C5, OAR in B4 =C5*B4
Basic pay Hours in B3, hourly rate in B4 =B3*B4
Overtime premium Overtime hours in B5, premium per hour in B6 =B5*B6
Production budget in units Sales in B2, closing inventory in B3, opening inventory in B4 =B2+B3-B4
Cost variance (actual above budget) Budget in B5, actual in C5 =C5-B5
Variance as a percentage of budget Variance in D5, budget in B5 =D5/B5 (formatted as a percentage)
Total variable cost Units in B2, variable cost per unit in B3 =B2*B3
Worked Example: A Unit Cost Schedule

A PCTN-style task gives a cost schedule for 2,500 units of a product:

A B
3 Cost element £
4 Direct materials 18,750
5 Direct labour 12,500
6 Direct expenses 1,250
7 Manufacturing overheads 7,500

8 Total manufacturing cost ?

9 Units produced 2,500

10 Cost per unit ?

B8: =SUM(B4:B7) gives £40,000. Writing =B4+B5+B6+B7 also follows the accepted addition format. =SUM(B4,B5,B6,B7) (commas) and =SUM(B4+B5+B6+B7) (brackets around single cells) would not be credited.

B10: =B8/B9 gives £16.00. Writing =B8/2500 would not be credited because it types a number in place of the cell reference.

If the task then asks for direct cost only in B11, the formula is =SUM(B4:B6) , which gives £32,500. Including B7 would add a cell not needed for the calculation.

Error Values Worth Recognising

These are not named in the specification, but they tell you instantly that a formula needs fixing:

Error Usual cause in a costing sheet
#DIV/0! Dividing by an empty or zero cell, such as unit cost before units are entered
#VALUE! A text entry (for example "TBC") inside a calculation
#REF! The formula points to a row or column that has been deleted
#NAME? A misspelt function, such as =SUMM(B4:B7)
### The column is too narrow to display the number. Widen it; the value is fine
Beyond PCTN

Real-world costing models also use functions such as AVERAGE, ROUND and IF, and absolute references such as $B$4 . They are valuable workplace skills, but they are not in the PCTN formula list. In the assessment, stick to SUM and the four arithmetic operators, combined as the calculation requires.