Article #109 The Great Value of Cost Worksheets in Analyzing, Monitoring, Evaluating, and Reporting a Single Version of Your Capital Projects’ Portfolio Cost Performance

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, and institutions providing project finance among others, 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 will require much more effort and time to add and verify the reported information. Actually, in most cases, there will be separate MS Excel files to capture the transaction details of each cost process, for example, change orders, progress invoices, and budget adjustments among others. Further, each project cost worksheet will be 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 they 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 will be a start and end date, and there will be 12 financial periods per annum. Those periods will be mapped 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 will always have 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 will ensure 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 among others, 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 that can be 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 as well as 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 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 will enable 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 will appear. 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.


About the Authorfounder

Bassam Samman, PMP, PSP, EVP, GPM is a Senior Project Management Consultant with more than 35-year service record providing project management and control services to over 100 projects with a total value of over US $5 Billion. Those projects included Commercial, Residential, Education, and Healthcare Buildings and Infrastructure, Entertainment and Shopping Malls, Oil and Gas Plants and Refineries, Telecommunication, and Information Technology projects. He is thoroughly experienced in complete project management including project management control systems, computerized project control software, claims analysis/prevention, risk analysis/management (contingency planning), design, supervision, training, and business development.

Bassam is a frequent speaker on topics relating to Project Management, Strategic Project Management, and Project Management Personal Skills. Over the past 35 years, he has lectured at more than 350 events and courses at different locations in the Middle East, North Africa, Europe, and South America. He has written more than 250 articles on project management and project management information systems that were featured in international and regional magazines and newspapers. He is a co-founder of the Project Management Institute- Arabian Gulf Chapter (PMI-AGC) and has served on its board of directors for more than 6 years. He is a certified Project Management Professional (PMP) from the Project Management Institute (PMI), a certified Planning and Scheduling Professional (PSP), and Earned Value Professional (EVP) from the American Association of Cost Engineers (AACE) and Green Project Management (GPM).

Bassam holds a Masters in Engineering Administration (Construction Management) with Faculty Commendation, George Washington University, Washington, D.C., USA, Bachelor in Civil Engineering – Kuwait University, Kuwait and has attended many executive management programs at Harvard Business School, Boston, USA, and London Business School, London, UK.


Contact us