Lessons Lesson 101

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

By Ali, Founder, Excel Lessons

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.

Read more about this

Where this comes up again

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, type:

=B2*C2

This fills down to D5.

D2
Row A B C D E F G
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. 1

    In D2, type: =B2*C2. This fills down to D5.

    Hint. B2 times C2.

  2. 2

    In D7, total D2:D5 with SUM.

    Hint. Four lines.

  3. 3

    In D9, round the subtotal times B8 to pennies. Shape: =ROUND(value, n).

    Hint. ROUND to 2.

  4. 4

    In D11, round the VAT to pennies. Shape: =ROUND((value-value)*value, n). That is subtotal less discount, times the rate in B10.

    Hint. Brackets round the subtraction.

  5. 5

    In D12, total the subtotal minus the discount plus VAT.

    Hint. Subtotal, less discount, plus VAT.

  6. 6

    This shows what ROUND was for. Read =D7*B8 and say what it returns.

    Hint. No rounding this time.