Navigation:  OVERHEAD > How to Build a Sheet > Spread Sheets

Applicability: all sheets except Summary, Custom Columns, Cash Data, WBS and User sheets.

 

The spread sheets define a Name field used to categorize the minutes in the same way as the Kind Ids (refer to Overhead); in addition, they can offer database views using this field as a key.

The first columns (name, and ID if any) are "frozen": they stay displayed as you scroll horizontally.

If the content is hidden, roll the mouse wheel.

 

The count of entries of the field is limited to 1,000; in event of overflow, an entry “<Other>” is appended which contains the sum of all the extra entries (there the Negotiation is meaningless).

Any total amount can be distributed across rows for analysis purpose using extra keys (Breakdown columns).

Unlike the Currencies, Material and Workforce sheets, the Families, Manufacturers, Suppliers and User-defined field sheets have no columns of their own. The values of the fields of the Material and Workforce sheets can be reshuffled across the families, manufacturers, suppliers or user-defined names.

 

 

Inserting Columns

For example, in a Set Sheet, a total can be distributed across the Kind IDs (rows). Each fraction of the total can be broken down across the discrete values of a field, such as Kind ID of another set; this results in a pivot table:

 

PROCEDURE

Tool: edit mode, bar Administration>Columns

 

To insert a new column, there is no need of an insertion point; the columns are inserted on the right-hand side:

1.Switch to Edit Mode

2.Select Administration>Insert Field(s), or right-click anywhere and select Insert Field(s); this brings up a dialog box

3.Select the total to distribute

4.To add conditions, select a filter/key among the proposed fields with discrete values; all the corresponding mnemonics show

5.Check boxes to sum only for defined values of the filter (check the blank box to collect the amounts with no value being entered for the filter)

6.Check "Display a field with sums" to just insert a column with the sum of filtered items or "Multiple Fields" to insert a pivot table

 

NOTES

- The Time frame Summary Total Cost and Total Time are eligible as Values to breakdown

- To show in the Values drop-down list, a free field must have its attribute Show in totals set to Sum; refer to How to Create Free Columns with Custom Behavior

- To show in the Break down drop-down list, a free list must have its attribute Ignored from database of estimate set to Yes; refer to How to Create Free Columns with Custom Behavior

- There is no point selecting a key, checking all the boxes and selecting the option 'Display a field with sums'; the effect would be the same without breakdown

- When breaking down the values across the amounts in the currency, it does not make sense to check the empty box

- To break down the values across the amounts in the currency with which they have been entered in the Minutes view (instead of the currency of the estimate), select the key Material/Workforce / Currency (of forced price) (Native); of course the option Display a field with sums is disabled

 

Click the blinking icon in the right bottom corner ; this distributes the value of each row across the columns:

The main header is named after the selected fields: <Column name> for<Key name>.

If 'Multiple Fields' has been selected, as many columns as checked boxes are created, with column headers named after the checked discrete values (see figure above).

 

To return to the dialog box, right-click any cell in the header and select Properties.

To delete a column, select it and click Delete Selected Column  (alternative: context menu>Delete selected fields).

To change a column width, select it and click Home>Edit>Format>Column Width.

 

Inserting a Set of Fields or a Time Frame

Each minute belongs to a Material kind and a family, and features a cost per unit and quantities.

Example: once the Material view is filled, a view whose key is the family name can be created by checking Multiple Fields:

 

The fields are detailed in Set Sheets.

NOTE If one of these sheets is empty, it is visible only in Edit mode ({HIDESHEET} in cell A2).

 

PROCEDURE

To insert a new set or time frame in a sheet Families, Manufacturers, Suppliers or User Fields:

1.Switch to Edit Mode

2.Right-click the "Select set of columns..." cell and select Properties; this brings up a dialog box

3.Select the set MATERIAL/WORKFORCE/TIME FRAME... and the desired additional fields (defined in Set Sheets), then click OK; QDV7 fills out the header accordingly

 

 

CASCADED SELECTIONS

- Rebate => Public cost and Cost after rebate, because they are needed to calculate Rebate (per family or else), which is done by a formula in the current sheet

- Negotiation => Cost after rebate

- Efficiency => Gross time and Total time, because they are needed to calculate Efficiency (per family or else), which is done by a formula in the current sheet

 

NOTE If upstream columns are deleted as described below, then these Rebate, Negotiation or Efficiency show #N/A.

 

 

To insert a new set or time frame in another sheet, there is no need of an insertion point; the columns are inserted on the right-hand side:

1.Switch to Edit Mode

2.Select Administration>Insert Set Of Field(s) or right-click anywhere and select Insert Set Of Field(s); this brings up a dialog box

3.Select the set MATERIAL/WORKFORCE/TIME FRAME... and click OK; QDV7 inserts on the right hand five predefined columns

 

To return to the dialog box, right-click the header and select Properties.

To change a column width, select it and click Home>Edit>Format>Column Width.

To delete a column, select it, then click Administration>Delete Selected Column (alternative: context menu>Delete column).