| Worksheet Reports | Administering Group Working | |
This chapter describes the Revolve Enterprise Edition Estimation Engine, a general-purpose estimation spreadsheet that helps you get a quick, but accurate, estimate of the cost and effort of a transformation project. You will find it useful to have read the chapter Using Worksheets before using the Estimation Engine.
During the analysis phase of a Revolve Enterprise Edition project you produce a worksheet that contains the points of interest (POIs) in the project, each POI having a category assigned to it. You need to export the details of your worksheet to an Access reports database from which you extract the information needed by the Estimation Engine to produce an estimate of the cost and effort involved in your project.
You should be aware that, although the Estimation Engine helps you to derive estimates, it is not a substitute for rational thinking and coherent argument, rather a framework within which such thinking can be channeled to good effect and recorded. For example, the results of the Estimation Engine, when applied to a particular application, need validating (sense-checking) against the results of other tools and the cost of originally developing similar applications (where known).
You may then need to change the values assigned for coding and unit testing in the light of these comparisons.
The estimator workbook contains the data for calculating project estimates. It is an Excel workbook (efforttemplate.xls) and is supplied with the reports templates in the folder Tps\config\enterprise\reports.
Some of the data are provided as default values, some you must import from the project reports databases. The following data are held in the estimator workbook:
These details are imported from the project reports database.
This contains the default values supplied with your system.
These are exported to the Access project reports databases and imported to your estimator workbook. See the sections Exporting Project Data from Access and Importing Project Data to the Estimator Workbook.
These are exported to the Access project reports databases and imported to your estimator workbook. See the sections Exporting Project Data from Access and Importing Project Data to the Estimator Workbook.
This is generated once all the program and file-level sheets have been imported. See the section Creating the Project Summary Sheet
Once you have completed your project worksheet, you need to export the details to an Access reports database, then import them to the workbook. The number of points of interest assigned to each category are split into two parts:
These counts are imported into the workbook by the Load Files POI function.
These counts are imported into the workbook by the Load Programs POI function.
This is an Excel sheet within the estimator workbook efforttemplate.xls.
The category sheet contains the following, maintainable items:
These are imported from the project reports database. New category names and descriptions can be imported from one or more projects, using the Load Categories function and are merged into the category sheet.
These are provided for the coding and unit testing of modules and data files. Default initial values for new categories are taken from the Settings Sheet. You can change these in the categories sheet.
In addition to effort levels associated with category assignments, the workbook uses four values from the settings sheet (see the section The Settings Sheet). These are:
Figure 7-1 shows an example categories sheet once it has been populated with default effort-level values.
Figure 7-1: Example Categories Sheet
Values are assigned for modules and data files, giving eight effort levels in total. These are split into eight separate columns on your worksheet (columns C through J). The following table summarizes the eight effort levels.
| Column
|
Program/Data File Category |
Description
|
|---|---|---|
| C | Fixed coding overhead per module | The overhead incurred, on a per module basis, in
carrying out the programming effort required for a particular category
before you can begin to fix individual POIs in that category.
For example, you might want to add common windowing routines to a program before fixing each individual statement that requires date windowing logic to fix the year 2000 problem. Writing the windowing routines would be considered part of the fixed overhead for that module. |
| D | Per POI coding effort per module | The programming effort involved, on a per module basis,
to fix one POI for a given POI category.
For example, the effort required to add date windowing logic to fix the year 2000 problem to one statement in a program. |
| E | Fixed testing overhead per module | The overhead incurred, on a per module basis, in carrying out the testing effort required in a program for problems of a particular type, regardless of how many specific POIs need to be addressed. This effort is assessed once per category for a program, even if several POIs exist for a particular category. |
| F | Per POI testing effort per module | The effort involved, on a per module basis, in testing a single fix for one POI for a given POI category. |
| G | Fixed coding overhead per file | The overhead incurred, on a per file basis, in carrying out the programming effort required for a particular category. |
| H | Per POI coding effort per file | The programming effort involved, on a per file basis, to fix one POI for a given POI category. Typically this value would reflect the amount of code required for each converted field. |
| I | Fixed testing overhead per file | The testing overhead effort for a file conversion program, regardless of the number of specific POIs to be addressed. This effort is assessed only once for a file, even if several categories of POI are associated with that file. |
| J | Per POI testing effort per file | The effort involved in testing the new values for each changed value in a converted file. |
The initial values allocated to these eight effort levels are calculated using the default settings of various items found on the Settings tab on the estimator workbook.
Note: The effort levels entered into the category sheet should always be in man-hours. You can change the reporting units by selecting a new item in the main Merant form's listbox on the estimator workbook. The reporting unit chosen affects how the totals are displayed on the file, program and summary sheets.
This is an Excel sheet within the estimator workbook efforttemplate.xls. You use the button labeled Show Form on this sheet to open the main MERANT dialog box to import project data and reset your reporting units.
The settings sheet comes supplied with the following, maintainable defaults:
There are two types of effort levels:
These represent any overhead that is required before a first fix, or test of this type can be applied, for example, the amount of time it takes to check a program out of a source library. They are applied to each program or data file only once in their import sheet, regardless of the number category counts.
They are split into four types:
| Fixed Effort Level
Type |
Description
|
Effort Level Examples |
|---|---|---|
| Fixed Program Overhead: Coding | The effort required to change a program on a per program basis, regardless of the specific POIs to be fixed. | The time required to retrieve the source code from an archive. |
| Fixed Program Overhead: Testing | The effort required to test a changed program on a per program basis, regardless of the specific POIs that were fixed. | The time required to set up the test environment |
| Fixed Data File Overhead: Coding | The effort required to change a file on a per file basis, regardless of the specific POIs to be fixed. | The time required to write a conversion program. |
| Fixed Data File Overhead: Testing | The effort required to test a data file on a per data file basis, regardless of the specific POIs that were fixed. | The time required to set up the test environment. |
These represent the time it takes to fix or test one problem of this type.
These are the values in man-hours for the available reporting units, for example, 1 day = 8 man-hours, 1 week = 40 man-hours. You can change these and add further values up to a total of 12 reporting units. Once you have added new reporting units, you can select them by clicking Show Form and selecting the appropriate unit from the listbox on the main Merant dialog box.
The text in the print headings cell appears as the heading on the report when you print the workbook using the Excel File > Print option.
These are percentages of effort required for a full development life cycle. The program and data file sheets are used to calculate effort levels for the coding and unit test phases of the life cycle. The other life cycle phases are calculated using standard percentages from the settings sheet. The life-cycle totals appear on the summary sheet when you create it.
You can add or change life cycle phases, with the following restrictions:
This is the percentage of effort required for each development phase. The default values provided are:
| Development Phase |
Percentage of Effort |
|---|---|
| Design | 10% |
| Analysis | 10% |
| Coding + Unit Testing | 40% |
| System Testing | 30% |
| Acceptance Testing | 10% |
You may need to change these to suit your particular environment.
Figure 7-2 shows part of a settings sheet:
Figure 7-2: Settings Sheet
To create an estimate for the cost of a transformation project using the Estimation Engine you must:
See the section Creating the Project Worksheet.
See the section Exporting the Project Worksheet to an Access Reports Database.
See the section Exporting Project Data from Access.
See the section Importing Project Data to the Estimator Workbook.
See the section Creating the Project Summary Sheet.
The first step in your analysis for your Enterprise Edition project is to create a project worksheet, populating it with points of interest and allocating categories to them as described in the chapter Using Worksheets. All items must be categorized with your high-level categories that you want to appear in reports and in the estimator workbook.
To export details to Access:
Worksheet Reports and select Export worksheet details to
Access
After exporting the project worksheet to an Access reports database, this now contains your project details along with the categories that you need for your final estimate.
Now you are ready to export the data files which will be imported into the estimator workbook. For each project and each report database you must run three queries in Access to export your project data. To run these queries:
You can, of course, just export one query at a time.
Note: You can enter any location for this file. However, we recommend that you do not change the default file name assigned to this file.
Once created, you only need to update these sheets if, for example, you want to remove an existing category, add a new category or change the default value assigned to one of the existing categories. To update a sheet, open it in Excel and make your edits.
Having created the categories, files and programs sheets, you can close Access and import these details into the estimator workbook in Excel. To do this:
If you see a dialog box concerning macros, click on Enable Macros.
The details are automatically updated on the Categories tab of the estimator workbook.
This creates a new sheet in the workbook and applies the effort levels for each file.
This creates a new sheet in the workbook and applies the effort levels for each program.
This completes the importing of data to the estimator workbook.
Notes:
To create a summary total that includes all the total lines from all the worksheets that you have imported into the workbook:
If you see a dialog box concerning macros, click on Enable Macros.
This creates a Summary tab, a new sheet containing the project summary effort estimation.
Figure 7-3 shows an example of a summary sheet in a saved workbook efforttemp.xls.
Figure 7-3: Summary Sheet
If you want to change any of the factors used in calculating the various effort level totals on either the files or programs sheets (or both), such as the percentage of the whole project that one of the development phases should take, you can select the Categories tab and make the alterations you require.
As you make alterations the effort levels shown in the files and programs sheets are automatically re-calculated, and their display changes dynamically. Changes to the Categories tab do not affect your default effort-level workbook.
You can also re-set the reporting units used in your worksheets by selecting items in the Merant dialog box reached by clicking Show Form on the Settings tab in your workbook. All the values in the worksheets will automatically be re-calculated and re-displayed in the new unit.
The table below lists error codes and gives tips to aid you in troubleshooting any problems occurring while creating your estimates.
| Error Message |
Description |
|---|---|
| #1 Error #NA appears in File or Program cells | If your Programs or Files worksheets show #NA in any
cells you've loaded a category in the POI list that does not have a
corresponding entry in theCategories worksheet.
You should re-import a categories file with all the correct entries in it . |
| #2 Error #NAME appears in File or Program cells | If your Programs or Files worksheets show #NAME in any cells, the name LookupCategory associated with the categories sheet is not associated with the correct range. To adjust this range you should re-import a categories file to reset the LookupCategory range. |
If any #NAME errors occur in any of the other worksheets you should replace them with valid values once the LookupCategory name has been assigned correctly.
Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Worksheet Reports | Administering Group Working | |