Navigation:  OVERHEAD > Pricing Sheets > Defining Return Values

Use Case

The values of a free column (numeric or text) can depend on another column's ones not thru a formula in the Field Manager, but thru a table.

This master column can be the (Kind) Id of a set sheet or of a list for overhead (refer to List for Overhead).

As a formula in a cell returns a value, the master column is said to return factors to a slave column in the minutes view; they are called "factors" by analogy with the selling factors that depend on the Kind Ids of Material or Workforce sheets:

 

When the returned value is actually a factor, its value in the Minutes view is not directly added to the cost/selling price; so its attribute 'Is part of cost/selling price' must be set to No. The contribution to cost (not only to the selling prices) is indicated by a flag The field influences costs, used to detect a dead lock.

 

Definition

The dependency is defined in the table of the master (set sheet or list sheet). The slave column is selected among the already defined free fields (numerical, usually factors, or text).

Proceed as follows:

1.Switch to Edit Mode

2.Select a column, right-click and select Insert field with factors (alternative: Administration>Columns>Insert column returning factors); this brings up a dialog box

3.Select the slave field; this fills the slave column with the values 1.0000 (numeric field only)

4.Enter the desired values (or enter manually a formula containing a global variable or a cell name).

 

If you intent to use in the step #4 formulas with a name of cell that contains total costs and to include the factor in a field that belongs to cost, this may be impossible to resolve, then, in the step #3, check the box The field influences costs. See below.

To change a choice, select the column and select Properties in the context menu.

 

Insertion into the Minutes View

5.Insert the master and slave columns in the Minutes view (refer to How to Add Columns)

6.Select the values of the master column in the Minutes view; the returned values show accordingly:

 

 

Evaluation in the Minutes View

The evaluation proceeds thru various steps depending on the used icon (Compute Costs or Compute All).

The cost calculation:

▪replaces the global variables with their current values

▪reads coefficients and return factors from the overhead workbook

▪calculates the selling prices

 

The selling price calculation:

▪gets the global variables from Management system (if applicable)

▪replaces the global variables with their current values

▪reads coefficients and return factors from the overhead workbook

▪calculates the costs

▪fills the cost cells in the overhead workbook

▪reads coefficients and return factors from the overhead workbook

▪calculates the selling prices

 

NOTES

- As soon as a free field is enslaved, its cells Column's type and Formula are grayed out in the Fields Manager for Minutes and Nomenclatures

- A list whose entry is allowed "only in WBS" or "WBS with priority to Minutes" can be a master column and return factors

- A field (numeric or text) can be the slave of only one master column

- Return factors are only given to normal rows of the Minutes, and not to any row in the Database of the Estimate (to rows referring to it either), nor to rows of the Overhead Task Section. In those cases the return factor is always 1

 

Example with a List

Custom duties generally depend on the sourcing. Various custom factors are applied to get the total cost.

1.Create a list with two values: off-shore, on-shore

2.Create a numeric field whose values are the Custom Factors to apply

3.In the overhead edit mode, select the Custom Factor as the target to receive the return factors

4.Enter custom factors in the newly created column

5.Create a numeric field whose values are the Tariff per unit based on a formula and part of the selling price

6.In the minutes view, insert the three columns and fill out the column On/off Shore; the two others autofill accordingly

 

 

Insert in the Minutes view the column Calculated selling price per unit.

To calculate the selling prices, refer to Details on Calculation Modes.

Using Overhead only or Overhead + Coefficients may cause circular references that QDV7 cannot resolve.

In the Calculation mode Fixed Factors or Calculated Factors, the prices cascade as follow:

 

 

When you change the mode, the custom levy is modulated because the Material/Workforce factors may change.

 

Cyclic Calculation

If the factors derive from a variable containing costs, it may cause circular references that QDV7 cannot resolve. In this case, it is recommended to check the box The field influences costs; then QDV7 will execute up to three iterative calculations.

If the costs resulting of the three calculation cycles differ, the following message pops up “There seems to be a cyclic cost calculation problem. Return factors from type cost seem to depend on costs and this is not solved within 3 iterations!  Please check your overhead workbook and formula's.”

This can be illustrated as follows: