Project: An Invoice

About this lesson

An invoice is a line total filled down, and a SUM for the subtotal. The discount and the tax are each rounded to pennies with ROUND, as values. The total then adds exactly what the customer sees.

By the end you can

build an invoice whose pennies add up.
round a discount and a tax as values, not as formats.

Practises SUM ROUND

The idea

Six cells, in the order the customer reads them. The one rule that separates a correct invoice from a nearly-correct one is where the rounding happens. ROUND the discount and the VAT as values, and the total is the printed figures added up. Round with a cell format instead, and the total can be a penny off what is on the page. That is the email nobody wants.

The VAT reads the discounted amount, in brackets, because tax is charged on what is paid. The last step reads the discount without its ROUND. Then the reason for the rounding is on the sheet, not in a rule.

The mistake to watch for

Rounding with a cell format. The fraction of a penny stays in the arithmetic. The total drifts from the lines on the page, and the customer adds them up. ROUND the discount and the VAT as values. Charge the VAT on the discounted amount, with the brackets that make the subtraction happen first.

Where this comes up again

Percentages: Share, Change and Discount Everyday Math
The SUM Function Spreadsheet Basics
Fill Down and Fill Right Spreadsheet Basics
Relative References Cell References and Fill

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In D2, the desk lamp line: quantity times unit price. This fills down to D5.

To begin, type it exactly:

=B2*C2

Row	A	B	C	D
1	Item	Qty	Unit price	Line
2	Desk lamp	3	24.99
3	Cable	10	3.5
4	Monitor	2	189
5	Mouse	4	12.25
6
7	Subtotal
8	Discount rate	0.1
9	Discount
10	VAT rate	0.2
11	VAT
12	Total

Every step

1

In D2, the desk lamp line: quantity times unit price. This fills down to D5. To begin, type it exactly: =B2*C2.

Hint. Quantity times price.
2

In D7, the subtotal: SUM of the four lines.

Hint. Four lines.
3

In D9, the discount: the subtotal times the rate in B8, rounded to pennies with ROUND.

Hint. ROUND to 2.
4

In D11, the VAT: the subtotal less the discount, times the rate in B10, rounded to pennies.

Hint. Brackets round the subtraction.
5

In D12, the total to pay: subtotal minus discount plus VAT.

Hint. Subtotal, less discount, plus VAT.
6

One last reading, to see what step 3's ROUND was for. Read =D7*B8, the discount without the ROUND, and say what it returns.

Hint. Three decimal places.