The Sheet of Sales is the most important sheet in the estimate as it centralizes data in a suitable mode and enables the bidder to compute extra expenses and coefficients on the basis of the overhead rate, the distance to the site, the custom taxes, the freight expenses etc.
Thru the scenario simulation, it provides a decision-making tool in the margin-risk assessment.
The Sheet of Sales is a user sheet, built on Excel formulas, that usually comprises:
▪The contributions of the sets to the dry cost broken down into the Kinds of the sheets Material, Workforce, etc; it is similar to the Summary
▪A management section
▪An Other expenses section
The Other expenses section include for example:
▪Overhead costs: it is what is not identified in the BoQ or WBS or Minutes appendix, such as interest (refer to Cash Data), accounting fees, advertising, depreciation, insurance, legal fees, rent, repairs, supplies, taxes, telephone bills, travel and utilities costs. Closely related accounting concepts are fixed costs versus variable costs, and indirect costs versus direct costs. The Overhead costs are usually expressed in percent of the selling price, hence the need of an iterative calculation
▪Cross-task expenditures
The margin is expressed in a percentage of the selling price. The margin can be imposed by the user or calculated from the selling price depending on the mode selected in the Summary sheet.
Example:
To colorize the cells, select Home>Font and . Usually the formula-free cells have a green background, meaning they are editable.
It is possible to display only what is useful. This can be done unconditionally or conditionally (that is with IF formulas).
UNCONDITIONALLY
Enter the text {SCANROWS} where the first row intersects a wholly empty column. At each refresh, QDV7 reads the whole column:
▪wherever there is the text {HIDEROW}, QDV7 hides the row
▪wherever there is the text {SHOWROW} or nothing, QDV7 shows the row.
The same applies to columns with {SCANCOLUMNS}, {HIDECOLUMN}, {SHOWCOLUMN} in a wholly empty row.
CONDITIONALLY
One sheet can show only the rows/fields used depending on the result of a test (IF formula).
Examples:
▪Expected Margin and Resulting margin rows: it is possible to show either according the Calculation mode
▪Not used elements: test the value of the related cell against 0, for example IF(C40=0 ; ‘’{HIDEROW}’’ ;’’{SHOWROW}’’)
▪Profile: insert in the formulas the SYS_ProfileType global variable (value: ADMIN or USER).
NOTES
- Insert {SCANROWS} only once as QDV7 reads only the first occurrence
- Activation:
Mode |
Access to locked cells |
Autohide |
---|---|---|
Normal |
No |
activated |
Edit |
Yes |
deactivated |
To define a visible area, switch to Edit Mode, select an area and click View>Show>Set Visible Area. If you load the settings of the entire Overhead workbook into another estimate, the Sheet of Sales of the latter will feature only the visible area. Refer to How to Import Data.
To test a sheet of sales, sum the amounts from each sheets MATERIAL/WORKFORCE and check the sum against the selling price in the summary. Issue a message in case of discrepancy.