Guide for Resource Estimation spread sheet

Content

Introduction


A usual work flow
Work Sheet Title

Work Sheet Reference Data


How to define selection and sub-selection lists
How to define condition groups
Syntax for: <Define>
Syntax for: <Validate>List   <Validate>SubList
Syntax for: <ConnectWith>List   <ConnectWith>SubList
Syntax for: <ConnectWith>Cond   <ConnectWith>Sum
A more sophisticated example
Work Sheet Resource Estimation template and Budget Forecast template


How to set up a template

Introduction

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

• individual naming and arbitrary number of columns
• use of any possible cell functionality, cross references, conditional formatting, ...

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

• Title
• Reference Data
• Resource Estimation template
• Budget Forecast template

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

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

• Project ID
• Project Name
• estimation author
• date of estimation
• valid until
• space to define delimitations

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 Budget Forecast

Work Sheet Reference Data

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

CREATED_CELL
used on the estimation and forecast sheet to place the CREATED_STR
CREATED_STR
used on the estimation and forecast sheet to define the string to show before creation date
CREATED_DATE_CELL
used on the estimation and forecast sheet to store the creation date
LAST_REF_DATA_UPDATE_CELL
used on the estimation and forecast sheet to store the date of the last update according to the status of the reference data (compared to Reference Data last update)
DATA_DEFINITION_LAST_ROW
defines the maximum row number to search for the data definition area marker
DATA_DEFINITION_RANGE
defines the maximum range to search for data definition area marker
INITIAL_ESTIMATION_ROWS_NUMBER
defines the initial number of empty data rows to add
Resource Estimation_ss
defines the base name to be used for the SnapShot for the Resource Estimation work sheet
Budget Forecast_ss
defines the base name to be used for the SnapShot for the Budget Forecast work sheet

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

Command
defines how to use the data defined in the other columns. Valid commands are <Define>, <Validate> and <ConnectWith>
Name
stores values depended of the command as a constant name, a list name, a sub list name or a "work sheet::column name" reference as destination for data copied from source
Data
stores values depended of the command as a constant value, a list item, a sub list item or a "work sheet::column name" reference as a source for data
Remark
a short description for what the definition is about

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

<Validate>List	FY	19/20
<Validate>List	FY	19/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

<Validate>List	work category	Project Management
<Validate>List	work category	Development
<Validate>List	work category	Testing

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

<Validate>SubList	Project Management	overall PM
<Validate>SubList	Project Management	partial PM

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

<Validate>SubList	Development	implementation
<Validate>SubList	Development	module test

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:

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

<ConnectWith>Cond 1	Budget Forecast::FY	Resource Estimation::FY
<ConnectWith>Sum 1	Budget Forecast::Budget	Resource Estimation::effort

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

Resource Estimation			Budget Forecast
FY	effort	...	FY	Budget
20	5,000$	...
20	2,000$	...	20	7,000$
21	1,500$	...	21	1,500$

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>

(<Define>   [name of constant   value for constant])

name of constant       a string used as name to retrieve the value
value for constant   any number or string
for example: <Define>  INITIAL_ESTIMATION_ROWS_NUMBER  30

Syntax for: <Validate>List    <Validate>SubList

(<Validate>List      name of list       list item value)
(<Validate>SubList   name of sub list   list item value)

name of list
a string used as list name to refer to the list items
name of sub list
a list item value of a list which serves as the master for the sub list
list item value
a value used as list item to select from a list
for example: <Validate>List    master-list ml-item-1
          <Validate>SubList ml-item-1   sl-item-1

Syntax for: <ConnectWith>List     <ConnectWith>SubList

(<ConnectWith>List      Dest::ColName   name of list)
(<ConnectWith>SubList   Dest::ColName   Dest::MasterCol)

Dest
the work sheet specifying the destination to connect the list with
ColName
the name of an existing column of a work sheet, the list items should be connected to
name of list
a string used as list name to refer to the list items, the list has to be defined by former   <Validate>List master-list ml-item   definitions
MasterCol
the name of an existing column of a work sheet with a connected master list. A master list has to be connected by a former   <ConnectWith>List WS::MaCol master-list   definition. A sub list refers indirect by master list items to the sub list items. A sub list has to be defined by former   <Validate>SubList ml-item sl-item   definitions.
for example: <ConnectWith>List    WS::MaCol    master-list
          <ConnectWith>SubList WS::SubCol   WS::MaCol

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

(<ConnectWith>Cond gID  Dest::ColName  Source[::ColName[:#Expression]])
(<ConnectWith>Sum gID   Dest::ColName  Source[::ColName])
#Expression: { #BaseExpr
               #AND(#BaseExpr; #BaseExpr)
               #OR(#BaseExpr; #BaseExpr)
             }[::Default=Value])
#BaseExpr:   { #=Const #>Const #>=Const #<Const #<=Const #<>Const }

gID
an integer value >0; used as group ID to connect condition and sum definitions to one group
Dest
the work sheet specifying the destination to write data to
Source
the work sheet to use as the source to read data from
ColName
the name of an existing column of a work sheet
Expression
an expression defining which data a condition-sum group considers,
one of { #BaseExpr #And(...) #Or(...) }
BaseExpr
a basic expression where the # gets replaced by a value read from source,
one of: { #=Const #>Const #>=Const #<Const #<=Const #<>Const }
Const
a constant value as number or string
Default=Value
defines a default value as number or string to overwrite source value
for example:
<ConnectWith>Cond 1  WS-D::FY   WS-S::FY:#=20
<ConnectWith>Cond 1  WS-D::ID   WS-S::ID:#>99::Default="0100"
<ConnectWith>Sum 1   WS-D::Sum  WS-S::Budget

Get all rows from source where FY column has the value 20 and the ID column a value greater 99; sum up for all matching rows the value stored in column Budget; store computed sum in destination work sheet column Sum; the fiscal year value for all records is 20 and gets stored in destination work sheet column FY; the destination work sheet column ID gets the default value "0100"

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

each fiscal year – for each organization for external budget usage
each fiscal year – for each organization for internal budget usage

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

<ConnectWith>Cond 1	BF::FY	RE
<ConnectWith>Cond 1	BF::org.	RE
<ConnectWith>Cond 1	BF::int/ext	RE::PD ext:#>0::Default=“ext.“
<ConnectWith>Sum 1	BF::Budget	RE::effort

and one for internal:

<ConnectWith>Cond 2	BF::FY	RE
<ConnectWith>Cond 2	BF::org.	RE
<ConnectWith>Cond 2	BF::int/ext	RE::PD int:#>0::Default=“int.“
<ConnectWith>Sum 2	BF::Budget	RE::effort

Note: the example above uses BF for Budget Forecast and RE for Resource Estimation

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:

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.

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

• A column name has to be in one cell only (no connected cells) without a line break (chr(10) chr(13)).
• The column header cells containing the column names, have to be in one row only, additional header lines need to be placed upon the column naming row.
• Cells defined in the Reference Data work sheet to be used within the estimation or forecast work sheet (for example TITLE_NAME_CELL) can not be used as column naming cells
  solution: reserve at least the first row for such data.
• The template has to have at least two rows below the column header, serving as data rows.
• If a footer is needed it has to be placed below the data rows.
• The last column of a template is used for special markers used by macros for controlling purposes. This last column does not need a column name. There are three markers to be placed:
  
  DATA_START_MARK     placed on the first data row
  DATA_END_MARK         placed on the last data row
  BOTTOM_END_MARK    placed on the last footer row
  (if no footer is used to be placed below DATA_END_MARK)
  
  These markers are used to find the column naming row, to limit deletion and inserting of rows, to define the lines containing data for the data export, creating snap shots and updating selections as well as for the creation of a Resource Estimation or Budget Forecast sheet out of the template definition.

How to set up a template

(easiest by copying an existing one and adapt it as needed)

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.

Text for the cell comment:
update
Updates validation list items according to current Reference Data settings.
This is important in case of reference data changes to list items. Without an update the selection lists may be corrupt.

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.

Text for the cell comment:
add
Adds rows below a selected cell or row.
Adding rows before the first or below the last data rows is not possible.
The number of added rows depends on the selected range of vertical cells or rows.
The number of rows to insert at once is limited to 50.

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.

Text for the cell comment:
delete
Deletes rows of the selected data area (range of cells or lines).
The first and last data rows are excluded from possible deletion, as well as the header and Bottom rows.

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.

Text for the cell comment:
Snap Shot
A new copy of the current work sheet will be created.
The copy includes only data.
No formula or BUTTONS to call macros are included.

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“.

Text for the cell comment:
JSON formated file
A new file will be created. The file name corresponds to the work sheet name extended by current date and time.
The file will include data from
– Title work sheet (ID and Name)
– cell „B1“ (date)
– the data lines (from DATA_START to DATA_END)
– the bottom lines (from DATA_END+1 to BOTTOM_END)

The structure of an export file:

• first data definition, name: name of current work sheet
• one header-line which is hard coded. It contains Project-ID and Project-Name from the Title work sheet and the creation date from the current work sheet as key value pairs
• 2 to many data-line records which are presented as list of records. Each record contains pairs of column name and value for all columns present.
• 0 to many footer-line records which are presented as list of records. Each record contains pairs of column name and value for those columns where the cell is not empty.