Blog · Switching

Taking over a half-billed contract from Excel: the opening position

You do not have to wait for every running job to finish before you change how you bill. A contract that is half billed in Excel or another system can carry on, if its position comes across with it.

By , founder of Mizarah, who ran a UAE joinery workshop for 13 years · 7 minute read · Published · Last updated

The problem in one line

Move a contract without its position and the next claim takes back too much advance, the 100% cap forgets half the work, and the old retention's VAT is charged the wrong way.

If you bill from Excel today, the worry about switching is the jobs in the middle. Six contracts half billed, each with an advance part recovered and retention held. Nobody wants to type two years of claims into a new system, and nobody should have to.

My view is simple: a contract needs its position on the day you switch, not its history. The history stays where it is. What moves is a handful of totals, checked once against the last certificate the main contractor signed.

The figures that carry a contract across

Take these from the last signed certificate and the old advance tax invoice, as they stand on the day you switch:

  1. Work billed to date, before VAT and before the advance and retention came off.
  2. How many claims have been issued, so the next one is numbered correctly.
  3. The advance received, the VAT declared on it, and the old advance tax invoice's number.
  4. The advance already recovered on the old claims, before VAT.
  5. The retention held, as the old claims withheld it: without VAT if they took deductions before VAT, with its VAT if after.
  6. Which of the two methods the old claims used.

The old claims' VAT stays where it is, in the returns for the periods they were issued in. Nothing about it is declared again. The open balances (the advance still to recover and the retention held) come into your new books through the opening trial balance, like any other balance on the switch date.

Most of these figures are already on the last certificate, in its "cumulative to date" column: the work certified, the advance recovered, the retention held. The advance received and its VAT come from the advance tax invoice. If your sheet and the certificate disagree, use the certificate, because that is what the main contractor will check your next claim against.

Pick the switch date with your accountant. The start of a VAT period is the cleanest, because then one return holds only old documents and the next holds only new ones.

The reference contract, with claim 1 billed in Excel

Same contract as in the other posts: 1,000,000 before VAT, a 30% advance, 10% retention, work billed 30%, 40% and 30%. The advance tax invoice and claim 1 went out from the old sheet. On the day you switch, the position is this:

Opening position, after claim 1
as at the switch date
FigureBefore VATAfter VAT
Work billed to date300,000.00300,000.00
Claims already issued11
Advance received300,000.00300,000.00
VAT declared on the advance15,000.0015,000.00
Advance already recovered90,000.0090,000.00
Retention held30,000.0031,500.00
Advance left to recover210,000.00210,000.00

After VAT the retention held includes its VAT (30,000 + 1,500), because the old claim declared it.

Mizarah contract panel: contract value 1,000,000, billed before this 300,000, this claim 300,000, left to bill after it 400,000, advance recovered to date 90,000, advance still to recover 210,000, retention held to date 30,000.
The contract panel beside the next claim on the 1,000,000 contract: 300,000.00 billed before it, 90,000.00 of the advance recovered and 210,000.00 still to recover, 30,000.00 of retention held. Local test data, invented names.

The next claim takes back only the advance that is left

Claim 2 bills 40% of the work, 400,000. It takes 30% of that as advance, 120,000, from the 210,000 left. Claim 3 takes the last 90,000. After that the advance is fully recovered, and a claim that tried to take more would be refused.

Claim 2, the first one in the new system
400,000 certified · 30% advance · 10% retention
LineBefore VATAfter VAT
Work certified400,000.00400,000.00
VAT on the work—20,000.00
Less advance (30%)−120,000.00−126,000.00
Less retention (10%)−40,000.00−42,000.00
VAT on the net12,000.00—
Payable252,000.00252,000.00

After VAT the claim declares 20,000 − 6,000 = 14,000 and names the old advance tax invoice, whose VAT it does not declare again.

Nothing on claim 2 shows that a system changed. That is the test I would use: if the main contractor cannot tell from the claim that you switched, the position came across right.

Work billed1,000,000.00 (100%)
  1. Claim 1, in the old system300,000.00
  2. Claim 2, in Mizarah400,000.00
  3. Claim 3, in Mizarah300,000.00
Advance recovered, before VAT300,000.00
  1. On claim 1, in the old system90,000.00
  2. On claim 2120,000.00
  3. On claim 390,000.00
The work and the advance across both systems. The old claim counts toward 100%, and the advance comes off once in total.

The 100% cap counts the old work

On the day you switch, this contract has 700,000 left to bill, not 1,000,000. Claims 2 and 3 bill exactly that. A fourth claim is refused, because the cap adds the work billed in the old system to the work billed since.

Skip the opening position and the new system believes the whole contract is still open. It would let you bill another 1,000,000, and the job would end at 130%.

The certificate keeps counting as well. Claim 2 is numbered 2, and its "previously certified" line shows the 300,000 billed before the switch, the same as it would have in the old sheet.

The old retention follows the old claims' method

Before VAT: the 30,000 held on claim 1 was never taxed. When it is released, it goes on the release tax invoice with its 1,500 of VAT, the same as the retention from the new claims. Releasing everything on this contract is 100,000 plus 5,000 of VAT: 105,000.

After VAT: the 31,500 held on claim 1 had its VAT declared on the old claim. It comes back as 31,500 with no VAT. Together with the 42,000 and 31,500 from the new claims, the release is 105,000 and declares nothing.

Releasing all the retention
after the defects period
LineBefore VATAfter VAT
From claim 1, billed in the old system30,000.0031,500.00
From claims 2 and 370,000.0073,500.00
VAT declared on the release5,000.000.00
Payable105,000.00105,000.00

This is why the old claims' method matters more than any other figure on the list. Get it wrong and the old retention either goes out without its VAT or has its VAT declared a second time.

Check it once, then leave it alone

Agree the figures before you issue anything new on the contract. After that, the position should not change, because every later claim is worked out from it. Four checks catch most mistakes:

  • The work billed to date is not more than the contract.
  • The advance recovered is not more than the advance received.
  • The advance recovered plus the retention held is not more than the work billed.
  • You know which method the old claims used. If the old sheet mixed the two, sort that out with your tax agent before you carry anything across.

Before you switch, it helps to work your next claim out by hand once, so you know what the new system should produce. The free progress bill calculator does one claim under either method.

HOW MIZARAH HANDLES ITTRUE OF THE PRODUCT TODAY

How Mizarah handles it.

In Mizarah this is "Billed before Mizarah" on the project: the date the position is as at, the claims already issued, the work billed to date, the method the old claims used, the advance received with its VAT and the old invoice number, the advance already recovered and the retention held. A position whose figures cannot be true is refused, and it is frozen once Mizarah issues anything on the contract.

From then on the 100% cap counts the old work, the next claim recovers only the advance left, the certificate numbers on and shows what was certified before, and a release treats the old retention by the old claims' method. The open advance and retention themselves come in with your opening trial balance.

QUESTIONS5 ANSWERS

Common questions.

Do I have to re-enter my old claims?
No. The opening position carries the contract: work billed to date, claims issued, the advance received and recovered, the retention held and the old claims' method.
Will the old advance's VAT be declared again?
No. It was declared on the old advance tax invoice. Before VAT, the new claims take the advance back at its net value. After VAT, they take it back with its VAT and subtract that VAT from what they declare.
Do the old claims go into my next VAT return?
Their VAT belongs to the returns for the periods they were issued in, worked out from the old records. If you switch in the middle of a period, that period's return needs both the old documents and the new ones.
What if my old sheet mixed the two methods?
Then the old figures follow neither method. Agree with your tax agent how to correct them before you carry the position across, so the new claims start from figures that are right.
Can I change the opening position later?
Until the new system issues a document on the contract. After that it should stay fixed, because every claim since has been worked out from it.

Official sources

Read next on this site

Practical guidance, not tax advice. The figures are worked examples, not anyone’s real contract. Check how the rules apply to your own contracts with your tax agent.

TRY IT ON YOUR OWN CONTRACT14 DAYS FREE · NO CARD

Put one of your own claims through it.

Start the free 14-day trial at mizarah.com and bill a contract you already know the answer to. Or work one bill out first with the free progress bill calculator, either VAT method, no sign-up.