This is an Excel based Project Data Storage System which is run by a Visual Basics operating system. Through various macros we are able to request multiple reports covering a wide variance of information.
The current database consists of a master document comprising 300 specifically named columns of data [landscape] and we currently have 385 sections of project information logged at various stages of progression [portrait]. The database also has a management tool which consists of multiple templates, when these are completed they are uploaded into the master document. We also have a reporting tool this enables us to retrieve information via reports. I hope this will give you an indication of the amount of data we retain.
This system has worked very well up to the present time but we have discovered that this system could be improved by making some amendments and additions which we have listed below.
• Automatically backup the manual inputs on the PDS
• Reduce the number of cells contained within the PDS to the number of columns that contain data
• From 2016 the England regions will be known as geographies and therefore the ‘Region / Nation’ column need to include the new geographies:
o North [+ keep the existing 3 x Celtic Nations]
o North West
o South West
o South East
(This is an inclusion of the geographies rather than a replacement so as to keep the history of the completed and withdrawn projects)
• Proposal templates to be updated to reflect the new geographies (‘Region / Nation’ column)
• The following additional columns to be added to the Programme section of the PDS and the Programme template:
o Period 1 ( January - April)
o Period 2 (May - August)
o Period 3 (September – December)
o Potential under spend / shortfall ( a formula would be used to calculate this figure on the PDS; Stage 1 + Stage 2 figure – the current year estimated budget) If there is a shortfall this figure should be in a red font
• An Inception Template is required
• A formula on the PDS that recognises the period (1,2,3) that a planned approval date is expected to fall within and populates the Capital Programme section of the PDS automatically
• Variation column required for the Stage 1 and Stage 2 figures in the Business Case and Pre construction sections on the PDS. The templates would need to be updated to reflect these additions
• A ‘Baseline’ column to be added to Feasibility to Operational sections of the PDS alongside planned and actual dates. For example Stage 1 Baseline Approval Date, Planned Approval Date, Stage 1 Approval Date.
• The PDS doesn’t recognise existing ps@mac or CE numbers when data is added retrospectively; for example if the 2014 programme was loaded the system would create another CE no with the same ps@mac number.
• On the PDS Master the 3rd tab needs to be corrected / amended to reflect the master template as there are missing columns
• Add ‘Planned Grant Agreement amount £’ and ‘Grant Agreement amount £’ under Business Case and Preconstruction stages
• If there is an easier way to format reports
• Rearrange order of columns as they are not in the right order
• Resolve any errors embedded on Project Data Storage [PDS] master
• To incorporate a CRM database in excel format
• To uniform drop down menus on the PDS Master
• Exportation of planned and actual dates from the PDS into a Gantt chart to show the project’s timeline.
• A report that provides
o Planned dates
o Actual dates
o Indicative MCS input
o The commitment period
o The RAG status for progress and programme finance
o Current stage comments
• RAG status to be added to each project stage from Viability to Operational
• Add new Framework template
This is not a comprehensive list of requirements but we would be prepared to discuss this further with you.