8.4 Writing Costing Formulas the Way AAT Credits Them
Key Takeaways
- 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.
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: Brackets first, then Division and Multiplication (left to right), then Addition and Subtraction (left to right).
=C4-B4/B4calculates B4/B4 first (which gives 1) and then subtracts it from C4. That is wrong for a percentage change.=(C4-B4)/B4subtracts 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+B7also 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/B9gives £16.00. Writing=B8/2500would 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.
Direct costs for a product are in cells B4 to B7. Which formula would AAT credit for totalling them in B8?
Total labour cost is in cell B3 and the number of units produced is in cell B4. Which formula for labour cost per unit follows AAT's accepted format?
Which of these formulas would NOT be credited under AAT's rules, even though it calculates the correct result?
The budgeted figure is in cell B4 and the actual figure is in cell C4. Which formula calculates the change as a proportion of the budget?
You've completed this section
Continue exploring other exams