Export to Excel
The BoQ as a workbook with live formulas, priced or for tender, that reads back into HodariQ unchanged.
The export writes the BoQ to an Excel workbook in which every amount and total is a formula. The client can audit the arithmetic, and in the tender version a tenderer types rates and sees Excel add them up.
Export
- 1Open the BoQ's Details.
- 2In the gear menu choose Export to Excel.
- 3Under Version choose Priced or Unpriced, for tender (rates blank, formulas kept).
- 4Press Download. The file is BoQ (name).xlsx, or BoQ (name) (for tender).xlsx.
The workbook
| Sheet | Contents |
|---|---|
| Bill 2, Bill 3... | One sheet per bill. At the top the firm, the contract title and BILL No. 2 - Substructure; then the columns Item, Description, Unit, Qty, Rate, Amount TZS. Then a COLLECTION block and the Total of Bill No. 2 carried to Grand Summary. |
| Grand Summary | Each bill with its amount, linked to that bill's sheet; Sub-total; Add VAT 18%; Total carried to Form of Tender. |
The formulas
| Cell | Formula |
|---|---|
| Amount of an item (and of a lump sum) | Qty x Rate of the same row |
| Amount of a % addition | Amount of the line it is added to x the % / 100 |
| Each Collection row | SUM of the amounts of one section (or of the whole bill when it has no sections; lines above the first section are totalled under the bill title) |
| Bill total | The Collection rows added up |
| Grand Summary amount | A link to the bill total on its sheet |
| Sub-total | SUM of the bill amounts |
| VAT | ROUND(Sub-total x VAT % / 100, 0): whole shillings |
| Total carried to Form of Tender | Sub-total + VAT |
In the unpriced version, the rates of items and the % of % additions are blank, while the Provisional Sums, PC Sums and Contingencies keep their rate, as on the tender print. The formulas are all there, so the totals fill in as the tenderer types.
Layout
- Calibri 10, black on white, every cell bordered, whole shillings, zero shown blank.
- A4, fitted to the page width, the column titles repeated on every printed page and frozen on screen, the contract number, page number and sheet name in the footer.
- A sheet name that would be too long or used twice is shortened or numbered, as Excel requires.
Back into HodariQ
Each bill sheet is written so that the import reads it back: a BILL No. row, a title row, the lines, and a COLLECTION block that the import skips. A hidden column G holds the line type in words (Measured item, Prime Cost Sum, % addition...), which the import reads, so an exported file comes back exactly as it left. This is how a tenderer's priced copy of your tender export is read into the tender analysis (see Tenders).
Good to know
Excel does not lock anything: a tenderer can change a quantity or a fixed sum in the file. The tender analysis reads only the rates (and the amounts written) and prices every line on your quantities: a changed quantity shows up as an arithmetic error on that line, and a changed fixed sum is ignored, the QS's sum being kept.
Something unclear? Ask us on WhatsApp: we answer the same day.