Synthetic quotation: why the same three lines total 4.38 or 4.39 depending on the rounding rule

The invented inputs

All figures here are invented for the example. The 12.5 per cent add-on is a made-up rate chosen only to make the rounding visible. It is not a real or recommended tax rate and nothing here is tax or accounting advice; a real rate and its rule come from you.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

price list (invented)
code | description       | unit price
S01  | Spacer, plastic   | 0.26
W10  | Widget, standard  | 0.65
W20  | Widget, long      | 1.30

brief 1: S01 x 5, W10 x 2, W20 x 1
add-on rate for this example: 12.5%

The lines

Each line is quantity times unit price. All three come to the same figure, which is what makes the difference between the two rules show.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

line | code | qty | unit | net
1    | S01  | 5   | 0.26 | 1.30
2    | W10  | 2   | 0.65 | 1.30
3    | W20  | 1   | 1.30 | 1.30
net total               | 3.90

Two rules, two totals

Rule A rounds the add-on on each line to two decimal places and then adds the lines. Rule B adds the net lines and rounds the add-on once on the total. Both are consistent, but they are not the same, and a quote should say which one it uses.

• Rule A: 12.5% of 1.30 is 0.1625, which rounds to 0.16 on each line; three lines give 0.48; the total is 3.90 + 0.48 = 4.38.
• Rule B: 12.5% of 3.90 is 0.4875, which rounds to 0.49; the total is 3.90 + 0.49 = 4.39.
• Neither is wrong. The owner of the quote chooses and writes the rule down.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

rule | add-on          | add-on rounded | total
A    | 3 x 0.1625      | 3 x 0.16 = 0.48 | 4.38
B    | 0.4875 on 3.90  | 0.49            | 4.39

Where the workbook and the template come in

Excel stores 15 digits of precision and cannot hold some decimals exactly, so rounding should be applied with the ROUND function at the step the rule names, not left to cell formatting. Microsoft's ROUND page shows, for example, =ROUND(2.15, 1) giving 2.2. The merge template should then show the finished figures. Microsoft notes merged numbers arrive without currency symbols, so the symbol is added in the template next to the field.

Edge cases in a proof set

A real proof set includes cases like these, each calculated by hand beforehand.

• Brief 2 includes code S09, which is not on the price list: the proof sheet lists it as an exception and the quote has no priced line for it, never a blank or guessed price.
• Brief 3 includes a line with quantity 0: the written rule says whether it is omitted or shown, and the proof sheet records which.
• Brief 4 has a quantity at a price break: the break rule is applied and tested on both sides of the boundary.

Use it to specify an enquiry

A batch of up to 40 quotations from one template and one price list, with your written rules, is a one-off job from £495 (an untested proposal), accepted by hand-calculated test briefs like these, with payment after your sign-off. We never send quotations. Send your rules in words and the counts, not the price list or customers.