Difference between revisions of "Complex and combined structures"

Latest revision as of 15:45, 5 April 2014

Complex and combined structures[edit]

A complex structure is:

• A grid containing more than one sheet/tab. The number of lines must be the same through all tabs. Formulas can be created between the different sheets/tabs. A cost breakdown is one example of using this type of combined structure.

A combined structure is:

• Multiple grids attached to a questionnaire serving as a summary sheet. Formulas can be created between grids and questionnaires. This combined structure looks like a structure tree: the questionnaire being on the first level and the grids being on the lower level (e.g. 1.1). It is used to consolidate multiple grids i.e. total of each grid to one global total.

Grid with several sheets and its formulas[edit]

This type of combined structures allows the possibility to create a quotation grid containing several sheets to define formulas in between different sheets/tabs. In this example, the project leader needs a structure for a cost break down. The project leader has created four sheets in the grid editor:

1. • 1st tab is for the Molding cost
   • 2nd tab for the Machining cost
   • 3rd tab for the Cutting cost
   • 4th tab presents an overall total (sum of cost breakdown) named Total molding, machining and cutting.

Please refer to chapter Define number of sheets on how to create more than one sheet per grid by using the function.

Remember: Using this kind of complex structure, the number of lines has to be the same on all sheets. Adding/Deleting a line from one sheet will add/delete from all sheets. For example, the last sheet Total molding, machining and cutting, you can add a total column summing up the values from the other sheets.

This total column needs to be defined as a formula by selecting <Formula> in <Layout/type>. You can create the formula (by clicking tool icon in the section Preferences).

1. Select different tender elements in the same project i.e. New Grid 2).
2. Select the sheet you want to include in the formula from the tender element selected (i.e. sheet Total Moulding, Machining and Cutting).
3. Select field/column element, all columns defined as number, price or formula can be selected, select from the list and click .
4. Insert the operator by clicking. A total can be calculated by selecting <Select tab of tender element> the second tab <Machining> and in Select field/column element the field <Total>.

Continue in the same way with the remaining tabs until the formula is complete. Then click . An overall total has now been defined on the last tab <Total Moulding+Machining+Cutting>, presenting a sum of the total columns of the different tabs. The formula calculates the values on line level.

Structure tree with combined grids and questionnaires[edit]

This type of combined structures provides the possibility to create a group of grids not similar in structure and number of lines. In order to sum the totals of each grid to one global total, a summary is created in a questionnaire at the top level. We take the example of chapter 9.1 where the project leader needs a structure for a cost break down. 3 grids under 1 questionnaire are created:

Level 1: Questionnaire Total Molding, Machining and Cutting

Level 1.1: Grid named Molding cost

Level 1.2: Grid named Machining cost

Level 1.3: Grid named Cutting cost

After having created the questionnaire (summary sheet), you need to select this questionnaire and then click New grid. In this way, you create the grid on level 1.1.

1st step: select the questionnaire

2nd step: click New grid, enter a name, choose to start with empty grid or from a template and click  :

3rd step: the grid Cutting cost is defined as a sub-element of the questionnaire Total Molding, Machining and Cutting (level 1.3): If you then select the grid on level 1.1 and click New grid, you will create this new grid on level 1.2.

(If no structure (grid or questionnaire) is selected, you will create a new grid on level 2). It is possible to create formulas between questionnaires and grids It is not possible to create formulas between grids. Each of the grids contains a column named Total... cost. Each total of this column (total over all lines) needs to be integrated in the summary sheet. To do this, open the questionnaire and click New row in order to insert a new question. Select the question type Formula and click:<the corresponding icon> Enter a title and click:<the corresponding icon> In the formula editor you can select from the first drop-down menu (Select tender element) one of the grids that belongs to this complex structure (i.e. grid Molding cost) and from the third drop-down menu (Select field/column element) the column you want to include in the formula (i.e. column Total Molding cost). As a sum over all lines is required, click and click <the corresponding> to insert the variable in the formula; click <the corresponding>. Continue in the same way to build up a sum for the remaining grids. In the last step, create a new question of type formula to define an overall sum of the total sums of all grids. As you have transferred the sum of the total column of each grid already to the questionnaire, you just need to sum up those totals. The selected tender element is thus Total Cost (the current questionnaire you are working in), then you insert the different totals from the drop-down list (Select field/column element) connected by the operator. Click:<the corresponding icon> This is how your summary sheet (questionnaire) will look like. The values are filled as the supplier completes the fields.