Guide for Resource Estimation spread sheet



The resource estimation spread sheet serves the project planning task to gather estimation results for tasks and / or items to estimate.
Each organization, sub organization or even project may have its own requirements to a budget estimation spread sheet. This depends on cost items or kind of work, which kind of costs to be estimated, how to transfer cost estimations into an accounting system and so on.
This Resource-Estimation spread sheet map is kept flexible to allow

by working with templates.
Templates give flexibility to adapt or create estimation sheets as needed. The generated estimation sheets then follow the structure (columns) defined in the template and take advantage from predefined values (list and sub-list items) defined in the reference data spread sheet.

The Resource-Estimation map base structure contains the work sheets

The spread sheet map macros support the usage of the template and reference data definitions. The main macros are available by buttons on the relevant work sheets.

A usual work flow

would be to adapt the example templates or to create new ones. In general all work sheets are protected. The cells to be filled with data are excluded from protection. This has to be defined already for the data rows of the templates.

So first step is to unprotect the template work sheet and perform changes. It is a good idea to protect the template afterwards to prevent accidentally changes.
As next selection lists and sub list are to be defined in the Reference Data work sheet. If all changes are done, the Resource Estimation work sheet can be created out of the Title work sheet by pressing the corresponding button.
There will always be only one Resource Estimation work sheet. So each time the button is pressed, a new empty Resource Estimation work sheet is generated. If one exists the user is asked whether to keep the old one as a snap shot. Otherwise it gets deleted.

The same steps have to be done for the Budget Forecast work sheet. In addition there is a possibility to define which data are taken from the Resource Estimation work sheet and how to compute sums for several estimation rows, like for some organizational unit, for a fiscal year, or other data related to an existing column in the estimation work sheet.
The Budget Forecast work sheet can be created as well out of the Title work sheet by pressing the corresponding button. There will always be only one Budget Forecast work sheet. The same procedure as for the Resource Estimation applies.

If later on changes become necessary due to the need of additional columns, new list items, some formula or something else, the templates and reference data can be adapted accordingly. The changes can then be applied to the existing work sheets by pressing the button [update selections]. This creates a complete new work sheet and copies the data from the existing old version to the new one. If columns have been deleted or renamed, data can not be copied. The old work sheet will then stay but renamed.

Snap shots from the current status of the estimation or forecast work sheet can be created at any time by pressing the button [new Snap Shot]. This preserves the current data in a new work sheet and can be used for versioning or as a source to copy data from and paste the data into a new created work sheet. Another way to save the data is to store them in a file by pressing the button [export data as JSON file].

Work Sheet Title

work sheet: Title

is the starting point for the usage as an estimation sheet. It is used to define the

These data can be used in the templates, which specially makes sense for the Project-ID and Project Name. Beside this, the data are not used any further.

button [add new „Resource Estimation“]
creates an empty estimation sheet according to the template and reference data definitions. Even the number of initial empty lines is defined by the reference data as INITIAL_ESTIMATION_ROWS_NUMBER and can easily be changed.
In case there exists already an estimation sheet, the user can decide whether to keep the existing one as a SnapShot. A SnapShot is a copy of the original one but with fixed values only. It is not meant to be used as an estimation sheet anymore, but as a reference for the old estimated data.

button [add new „Budget Forecast“]
creates an empty budget forecast sheet based on the related template and reference data definitions. It works the same way as adding a Resource Estimation work sheet. In addition the Budget Forecast work sheet can be defined to use data out of the Resource Estimation work sheet, if that one exists and has data already. Otherwise no data will be inserted.

example for Resource Estimation
example for Resource Estimation
example for Budget Forecast
example for Budget Forecast

Work Sheet Reference Data

work sheet: Title

is the source for data definitions to be used by formula, selection and sub-selection lists for data limitations and data connections. A data connection defines which data the Budget Forecast sheet uses from Resource Estimation sheet. In addition the Reference Data work sheet stores some general definitions as

The Reference Data work sheet has the following structure (columns) used for definitions:

The button [check]
performs some lexical and semantic checks of the reference data definitions like whether or not the work sheet exists, the column name exists, the <ValidateList> and <ConnectWith> definitions follow the correct syntax

The button [update]
calls the the function as [check] does and if OK, copies relevant data to the protected area, sorts the data to serve as correct selection and sub-selection lists as well as ordered <ConnectWith> groups. The last update date will be set to current day of execution, which serves the Resource Estimation and Budget Forecast work sheet button [update selections].

How to define selection and sub-selection lists

A selection list is build from all records defined as <Validate>List   LIST-NAME   LIST-ITEM   where the LIST-NAME is the same for all LIST-ITEMS belonging to one list.
For example a simple list FY (Fiscal Year) with two entries as 19/20 and 20/21

The next example defines a list called work category with three items as Project Management, Development and Testing and related sub lists for the list items PM and Development. A sub list is always defined relative to the master list, by defining sub list items for a master list item

The sub list definition for the master list item Project Management could look like

and a sub list definition for the master list item Development could look like

After connecting the list definitions with work sheet and columns for example by

<ConnectWith>List   WS-NAME::Category of Work   work category
<ConnectWith>SubList   WS-NAME::Scope of Work   WS-NAME::Category of Work

with first line for the selection list and second line for the sub list, the corresponding cells in a work sheet would offer selection lists like:

selection list sub list

Note: the data definition for a selection list, for example Development from the example above has to be unique in case a sub-selection list gets defined for such a value. Up to now it is not planned to consider the master list identifier (e.g. Category of Work for Development) as an additional differentiator like Category of Work::Development.

How to define condition groups

A condition group or even number of groups define how to connect certain values from the Resource Estimation sheet to the Budget Forecast sheet. A condition group exist of at least two definitions, one for the condition which can be one to many and one connecting the budget values, which is only one.
Rules belonging to one group are defined by a group number as part of the definition. In the example below the group number is one.
For example a simple connection is defined as

This connects the Budget Forecast column Budget with the Resource Estimation column effort and computes a sum for all records in Resource Estimation where the fiscal year column has the same value.
The Budget Forecast gets one record for each FY defined in Resource Estimation, where the two columns FY and Budget get filled by the data from Resource Estimation

The destination is defined by the work sheet and column name values in Name column (e.g. Budget Forecast::FY) , where each column name can be used only once within one group. It has to be unique for all definitions within one condition group.
The source is defined by the definition in Data column. The first part contains the work sheet name, the second part the column name and a third part defines the value to compare with.
The second part is optional, if the same column name as for the destination is used and no value to compare with is needed.
The definition as   <ConnectWith>Cond 1   Budget Forecast::FY   Resource Estimation   has the same meaning as the definition used in the example above with definition   Resource Estimation::FY.
The third value is optional. If no value is given, source records with the same value in the column defined are considered as to be summarized. Otherwise the defined value or condition is used to consider records fulfilling the condition only. The example above sums up the source values from effort column for FY equal to 20 for the first destination record and the source value for FY equal to 21 for the second destination record.

Syntax for: <Define>

Syntax for: <Validate>List    <Validate>SubList

Syntax for: <ConnectWith>List     <ConnectWith>SubList

Syntax for: <ConnectWith>Cond groupID     <ConnectWith>Sum groupID

A more sophisticated example

Probably a one to one relation between estimations and the forecast is not wanted, but to compute a sum for a certain group of estimations which belong to one account to track.
For example the estimation sheet shows rows for different fiscal years, for certain organizations for internal and external resource usage.
The Budget Forecast should show rows with summarized budgets for

In order to make this happen, two definition groups are needed,
one for external:

and one for internal:

Assuming the data are sorted by organization as first order, int./ext. as second order and fiscal year as third order a result could look like:

Resource Estimation Budget Forecast
org.FYPD ext.PD
A2010 5,000$...
A203 2,000$...A20ext7,000$
A213 1,500$...A21ext1,500$
A20 31,200$...
A20 52,000$...A20int3,200$
A21 31,200$...
A21 31,200$...A21int2,400$
B20 104,000$...B20int4,000$

Work Sheet
Resource Estimation template and
Budget Forecast template

A template defines the columns to use for the estimation or forecast process as well as restrictions to limit entered data to a predefined set of meaningful values. In addition, the template can be enriched with formula to compute sums, conditional formatting to present values depending on conditions or whatever is needed.

work sheet: Resource Estimation template

work sheet: Budget Forecast template

A template definition has to follow only a small set of restrictions, as

How to set up a template

  1. reserve the first row for data to be placed by the macros and to place the buttons
  2. use the second row to present data from the Title work sheet as Project-ID and Project-Name
  3. place as many additional header rows as meaningful, the last one has to contain the column names
  4. add descriptions for those columns where needed as a comment of the column naming cell
  5. add two empty rows to be used as data lines
  6. define conditional formating, formula etc. for those cells where needed for all exiting data rows (at least two)
  7. define the footer if needed
  8. place the three marker as described above
  9. place the buttons for [update Selection], [add rows], [delete rows], [new SnapShot], [export data as JSON file]
  10. add comments to the cells behind the buttons, with explanations of the purpose of the buttons
  11. set cell restrictions, allow editing for cells of the data rows, but exclude those with formula
  12. protect work sheet from being changed, works fine even with an empty password to prevent accidentally changes
    In case a real password is wanted, the password has to be set in the VBA script
    Module CommonModul as Private Const be_password_PC = "new-passwd"
    This is necessary, because Resource Estimation or Budget Forecast are created as a copy of the corresponding template and gets changed afterwards. The password is needed to perform changes.

button [update selection]
checks whether the Reference Data have recently be changed and updates the work sheet accordingly. This is about the definitions for selection and sub-selection lists in order to restrict the value range for certain cells to meaningful values.

button [add rows]
adds as many rows as vertical selected cells or rows below the first row of the selection. Rows can only be inserted below the first data row and above the last. This restriction ensures, that formula or other cell specific formats are copied and used formula in the footer will be adapted to the new range.

button [delete rows]
deletes as many rows as vertical selected by cells or rows. Rows to be deleted is limited to rows between the first and last data row. This restriction ensures, that formula in the footer will be adapted to the new range.

button [new SnapShot]
creates a copy of the work sheet, removes buttons, formula, conditions and selection lists. The copy is a plain work sheet with fixed values only but looks like the original one.

button [export data as JSON file]
creates a new file in the same file structure folder as the Excel file containing this macro. Up to now the folder selection and naming convention is hard coded. The file name uses the work sheet name, extends it by the current date an time and using the file extension „.json“.

The structure of an export file: