Sheet of Sales

Navigation:  OVERHEAD > Sheets for Finances and Planning >

Sheet of Sales

Previous pageReturn to chapter overviewNext page

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:

Sheet of Sales

To colorize the cells, select Home>Font and bucket. Usually the formula-free cells have a green background, meaning they are editable.

 

Autohide in Normal Mode

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.

 

ico_tipTo 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.