For organizations who are involved in the delivery of capital projects, regardless of whether they were the project owner, contractor, consultant, project management consultant, banks, or institutions providing project finance, they are all required to have a single spreadsheet that provides a single version of the truth about the project’s cost status. They are called spreadsheets because most organizations continue to use MS Excel to produce those reports. Those spreadsheets are also known as cost worksheets since they are reporting on the project or project’s cost status.
Nevertheless, using MS Excel in producing a cost worksheet not only is a time-consuming process but also lacks the needed transparency in identifying the source of the reported information, as it depends on manual input of this critical information. In addition, those MS-Excel-created cost worksheets usually have the cost data at a summary level and not at the transaction granular level, as this requires much more effort and time to add and verify the reported information. In most cases, separate MS Excel files capture the transaction details of each cost process, for example, change orders, progress invoices, and budget adjustments. Further, each project cost worksheet is saved as a separate MS Excel file that needs to consolidate with other projects’ cost worksheets to generate the consolidated cost worksheet for all projects.
Using Project Management Information System (PMIS) like PMWeb, there is almost zero effort in producing those cost worksheets as the complete project or project cost data are readily available to be reported on in the desired cost worksheet format. For an organization to have the best and most comprehensive cost worksheets, they need first to have a cost breakdown structure that can be standardized across their project’s portfolio as well as need to define the financial periods that a project’s financial performance will be periodically assessed at. Of course, those are recommended best practices but not a must to have to produce the cost worksheets.
The Cost Breakdown Structure (CBS)
It is highly recommended that the cost breakdown structure (CBS) is designed in a format that can be used to progressively elaborate a project’s scope of work and eventually the associated cost. Therefore, the CBS must be structured around a progressive breakdown structure like for example the UniFormat™ numbering system that enables elaborating the project cost as it evolves during the project life cycle design development stages. The cost breakdown structure will be created using the PMWeb cost accounts module.
The Project Financial Periods
Usually, every organization has its financial closeout periods predefined and aligned with the corporate financial reporting system. For each period, there is a start and end date with 12 financial periods per annum. Those periods map into PMWeb.
Capturing all Project-Related Cost Transactions
PMWeb cost management module comes ready with the forms needed to manage capital projects’ cost-related processes. These include cost estimates, budget, budget adjustments, commitments, potential change orders, change orders, progress invoices, miscellaneous invoices, actual issued payments, forecast to complete, income contracts, income change orders, income invoices, and receive payments. Each one of those forms has a detailed section to capture the particulars of each transaction. The detailed section always has a field for the cost breakdown structure (CBS) level that a cost line item belongs to as well as the financial period that this transaction should be posted. This ensures complete cost transparency to the required level of control.
For each one of those forms, PMWeb allows attaching all supportive documents which are usually uploaded into their designated folder in the PMWeb document management repository. In addition, all relevant PMWeb records such as meeting minutes, RFIs, site work instructions, and imported MS Outlook emails can be linked to those records.
A workflow can be assigned to each one of those cost forms where the review and approval steps can be defined. PMWeb workflow allows assigning approval authority levels to ensure that each transaction is reviewed and approved by the authorized team member thus ensuring complete accountability.
All those cost management processes can have their output form designed in any desired form and format. Those output forms are needed to formally communicate and share those cost-related transactions. The output form can be printed to be wet-signed and stamped or saved as a PDF so it can be digitally signed and shared.
In addition, transaction logs for each process can be created and presented in the desired form and format. Those registers or logs can be printed or saved as PDF files or MS Excel file format. The information in those logs can be sorted, grouped, and filtered in the needed format. Additionally, there is no limit on the types of logs that can be created for each cost process type. For example, the progress invoices register shown below is an example of one of those reports.
Consolidating, Aggregating, and Storing Cost Data
Every transaction line in PMWeb that contains a cost code is automatically posted to the cost ledger when the transaction is saved. The cost ledger line contains a copy of the data from a subset of fields in the original transaction line as well as additional metadata such as the time and date of posting and a hyperlink to the transaction. As transaction lines are edited the matching data in the cost ledger line is updated as well. If a transaction line is deleted its matching data in the cost ledger is deleted too. Lines can also be added directly to the cost ledger page as well as by using one of the PMWeb integration tools. The cost ledger serves as a fast, powerful, and easy-to-use data source for queries and reports.
Designing the Cost Worksheet
Unlike other PMIS applications where usually only a single cost worksheet can be created for a project, in PMWeb, the authorized user can create as many cost worksheet layouts as needed to show the project cost and financial information in any desired layout format. The PMWeb define worksheet command allows adding the needed PMWeb cost fields as columns as well as adding alias columns with formulas to calculate their values from other PMWeb cost processes. For each field, the user designing the layout can detail if the field is showing a currency value or a percentage, the width of the field, field header, field tooltip, and notes.
The Cost Worksheet
PMWeb cost worksheet module enables the user to visualize the latest financial transactions for each cost-related process. The user can apply the period filter to limit the displayed cost data for the selected period or span of periods. When clicking on any of the cells displayed in the cost worksheet, the detail of the transaction source record appears. The cost worksheet could be grouped by one or more of the desired cost breakdown structure (CBS) levels for which the subtotal for each group will be calculated automatically. Of course, different cost worksheet layouts that were created earlier can be selected and viewed. If the multi-currency option was enabled, the user can also select the currency to be used in displaying the cost worksheet. PMWeb also allows exporting the Cost Worksheet to MS Excel by selecting the “Export to Excel” command.
In addition, PMWeb allows editing the cost worksheet without using the relevant cost management process should this be needed for a quick cost worksheet if the details do not exist. Of course, this requires having the authority to do this.
Similar to other PMWeb modules, the cost worksheet can be printed in any desired form and format. The report can group cost data by the desired cost breakdown structure level which can be expanded or collapsed based on the desired level of cost reporting. In addition, it can be for a single project or a portfolio of projects. For all fields shown in blue, that are hyperlinked, the report allows the user the click on the field to view additional details. The report shown below is an example of the 150-plus forms and reports that come ready out-of-the-box with PMWeb.
Using business intelligence and data visualization tools like MS Power BI, Tableau, and Qlik among others, the organization can further analyze the cost worksheet information to identify variances and trends as well as provide senior management with an executive view of the project’s cost performance status.
