Beware the Spreadsheet

Fatal Flaws in Conventional Project Reporting Practices

Spreadsheets—we all use them, especially as a way to report project status.

These reports are often mission-critical as owners rely on accurate, up-to-date project reports to make real-time decisions. Unfortunately, reporting is often inadequate. There may be too much or too little information, which confuses owners, executives and stakeholders. As well, data may be misrepresented, incomplete, unreconciled, not validated, or simply inaccurate.

OWNER OVERSIGHT OF PROJECTS REQUIRES ACCURATE, TIMELY, AND RELEVANT STATUS REPORTING.

This post identifies common pitfalls in project reporting, and provides guidelines for developing periodic, consistent, and useful reports to executives and stakeholders within your organization.

THE NEED FOR MEANINGFUL AND TIMELY REPORTING

Every construction project begins as a concept. Whether it exists due to a need to expand capacity, diversify, grow geographically, replace aging facilities, or one of many other justifications, that concept is subject to evaluation at some high level of management. The management team may be an executive committee, board of directors, or other grouping of decision-makers and stakeholders. The management team assesses the viability of the concept, makes the go/no go decision, approves funding, and paves the way for that concept to become a reality.

The PricewaterhouseCoopers (PwC) guide for effective capital project oversight noted that while capital projects are vital for continued growth and realization of a company’s strategy, they do require careful forethought, comprehensive planning, and vigilant monitoring. 

Further the authors note: “Capital project development is becoming more risky and challenging—as increased globalization drives entry into new and unfamiliar markets, compounded by economic conditions that motivate contractors to shift risks back to owners. And the increasing prevalence of mega-projects (generally understood to be single projects valued in excess of $1 billion) has turned project-level risks into enterprise-level risks. All of these factors have made board oversight of a company’s capital program more critical as well as more challenging.”

Responsibility for day-to-day decisions lies with the project management team, but it is up to corporate directors, to ask the right questions of management—questions that ensure project performance meets strategic goals while conforming to the company’s overall tolerance for risk. It is in the owner’s best interest to have reliable data upon which to make decisions that will guide the project, whether a large capital project or something smaller.

As the project proceeds through its lifecycle, executive decision-makers need to receive meaningful information, appropriately actionable at their level, and sufficiently timely to enable them to guide the project. Not all stakeholders and decision-makers are well versed in project controls; their needs in project reporting for top-level oversight are quite different when compared to the expectations of those close to the project, such as the director of construction.

“There are many project participants—either direct stakeholders or beneficiaries, as well as others, having oversight roles at corporate, non-project levels who better relate to and more easily understand cost as a measure and yardstick for performance. Theirs is the world of return on investment (ROI), the bottom line, or time to payback indices; the concepts of earned value, SPI [Schedule Performance Indicator], or CPI [Cost Performance Indicator] are lost on them.” (Tichacek, 2005)

With a multitude of decision-makers and stakeholders, the situation becomes even more complicated. “Finding a format that communicates complex information well to multiple levels of management presents…a challenge.” (Christiansen, 1997) The stakeholders themselves are a product of their experiences, and their external and internal environments, one of which is the context within which the project must be satisfactorily completed. What do these stakeholders and decision-makers expect from project status reports?

In the case of either a single megaproject or a diverse program, the construction activity is integral to the company’s strategic plan and requires appropriately scaled project governance and board oversight to protect shareholder value. [PwC, 2013] In many instances, “…conventional reporting systems provide executives with reactionary information related to crisis situations, rather than providing proactive data.” (Rahbar & Yates, 1990) In such a situation, the data provided is typically focused on what has been spent at that particular point in time. Additional information, such as forecasting of final costs, and detailed cost data for high-risk areas, is needed to satisfy the project controls mission, and enable instead “… management by exception, which requires the identification and isolation of important and critical information for a given situation, and channeling it to the proper person for immediate consideration, decision, and action.” (Rahbar & Yates, 1990) 

Early reporting of exceptions and potential exceptions/risks is critical to decision-makers. Thus, the fundamental difference between cost accounting (or financial accounting) and cost management (or project controls) is in the packaging and treatment of project cost data.

PITFALLS OF COST ACCOUNTING SYSTEMS 

Accounting systems are implemented to ensure accurate financial reporting for financial statement purposes. These systems often do not have the capability to provide all the information necessary for project reporting. For example, good project reporting requires accurate information about commitments. Commitments may be further broken down into the categories of projected, pending and approved commitments. Many accounting systems do not record such detailed information about commitments, which makes it necessary for the team to utilize a separate project management system to track commitments. Lacking adequate systems, the accounting team may implement workarounds with extra steps and special processes, in an attempt to approximate cost management and reporting while utilizing an accounting system that was never intended for such a purpose. 

Increasingly, companies are replacing stand-alone accounting software with Enterprise Resource Planning (ERP) Systems. “[ERPs]…are information management systems that take a holistic approach to the business of finance and operations. These systems were developed to run a company’s business operations and typically include accounts payable, accounts receivable, general ledger, inventory, human resources, time writing, and procurement modules.” (Kohl & Wulke, 2004) 

The use of ERPs as a system for managing a construction project and providing cost controls can also be problematic. While more comprehensive than accounting systems, most ERPs have the same limitations around project accounting and reporting. 

PITFALLS IN PROJECT REPORTING 

A number of additional challenges may occur when attempting to generate a useful and comprehensible project status report. The true status of a project may be misrepresented, intentionally or unintentionally. Unintentional causes include delays in data input, errors, or miscategorization of data. Similarly, the work breakdown structure (WBS) or code of accounts might not provide enough detail, effectively hiding areas of risk while creating a false sense of security by providing some information. And bias is not as uncommon as one might hope – project managers may withhold information for a period of time, in order to present a rosier picture of the project to senior executives. Good information received too late, likewise, serves no useful purpose.

Delays in data input are mentioned above; however, “…another aspect of timing is the lag time between the end of the reporting period and the preparation of the reports. The lag time should be as short as possible.” (Orczyk, 1991) Lag may be caused by efforts to validate data, or other challenges faced in gathering data and generating the report. “The level of efficiency is important in terms of using software in project management, especially for large developments… there is pressure to get things done quickly and easily” (Hegarty, McLoughlin, Chen, & Sarshar, 2008) For teams accustomed to monthly reports generated with punctuality, any irregularity in the timing of report generation will hamper communication and the continuation of any work that relies upon timely issuance of the report.

A project dashboard report may be developed, which contains more comprehensive project information in the form of a snapshot in time. The dashboard report may include information on schedule and significant areas of concern, possibly with comparisons to concurrent projects. In order to produce this report, data must be gathered from many sources. When considering the available data, “…there is an ever-present requirement for the joining of many parts into a systematic whole. Without the integrative function, often nothing would be done with the concepts originating in the analytical functions.” (John, Wardle, & Fairhurst, 2008) Just as reconciliation between cost systems poses a challenge, the assembly of data from different systems also poses a challenge in accuracy and timeliness of reporting. “Special or customized reports are more complicated to produce and involve logical data integrity among different sources.” (Baram, 1992) 

AUTOMATION OF THE PROCESS MAY STREAMLINE THE TEAM’S EFFORTS TO GATHER DATA FROM MULTIPLE SOURCES TO GENERATE THE COST REPORT. 

Just as providing too little detail hides areas of risk, providing too much information can also be problematic, confusing the recipient of the report, and effectively hiding risk by drowning the reader in details. “There are numerous reports that contain pertinent and useful information that is produced and frequently updated. However, many of the individuals on the report distribution list do not know how to interpret the report, thus the usefulness of the report is diminished. This indicates that a lack of communication exists, which complicates the issue of what cost and scheduling personnel are able to generate versus what managers find useful and results in a situation called ‘information overload.’ 

An example of information overload is when a manager desires a simple one-page summary that shows the status of a project, and in response to the manager’s request cost and scheduling personnel provides the manager with volumes of paper that contain the status of every activity that has transpired during the last two years. Therefore the real challenge is to produce reports that are useful to managers and at the same time address specific project objectives.” (Rahbar & Yates, 1990) “The magnitude of information that’s available to us has increased exponentially,” says e-Builder client, Phillip Jabour, Associate Vice President and University Architect at the Office of Planning, Design and Construction at Southern Methodist University. 

If the recipient of the report is not well versed in construction, the impact is compounded because the recipient might not understand the report and thus cannot use the data (or may misuse the data) when making a decision. “… Lack of understanding in monitoring progress coupled with poor quality, poor content and clarity of progress reports equates to poor communication no matter whether …software is adopted or not.” (Hegarty, McLoughlin, Chen, & Sarshar, 2008)

Finally, the presentation format itself can pose a problem. If there is a lack of consistency, in that the presentation format changes each time, this can be confusing to executives, who then need to read the report carefully in order to ferret out the information that is useful to them. This is further compounded when management is tasked with reviewing the status of multiple projects, or the project team attempts to create a one-size-fits-all report. As Jabour added, “before we migrated to construction program management software, we always had to reinvent the wheel every time somebody asked us for something.”

SYSTEM RECONCILIATION ISSUES

When assembling a project status report, where does the information come from? Often it is a combination of data from financial accounting and project controls. It is worth noting that “…cost management cannot be performed without having an adequate cost accounting process in place.” (Tichacek, 2005) Information must push and pull between accounting and project controls systems.

At a minimum, your project team will use spreadsheets and specialized software to track and control schedule and cost, and may take advantage of expanded software functionality such as estimating, bidding, management of Requests for Information (RFIs) and change orders, and document controls (the functions performed by project controls software vary according to the software package). The resulting project data may be used not only for decision making at the time of report generation but also later in the event of claims and litigation. For the purposes of this discussion, we will focus on the integration of cost management capabilities such as budget management and job cost accounting.

If data (such as project approval, requisitions, purchase orders, receiving and payment processes) resides in more than one system and the interfaces between the accounting and project controls systems are not automated, duplicate entry will be required. Duplicate entries contribute to process inefficiencies and errors, problems that may be resolved by automating the interface between software packages.

Real-time reporting of project data requires faith that the data is correct. This confidence is realized by the validation of the systems. One way of validating the data is to conduct an initial reconciliation between the project management software and financial system. The information logic in the two systems needs to be mapped to enable an apples-to apples comparison. If there is an electronic data interchange failure, the two software packages or systems will be unable to reconcile. “To be able to produce some meaningful reports showing relationships between tasks or activities between different trades and/or levels, areas of building, etc., one has to be able to connect the related database files with one another through either linking fields or codes.” (Baram, 1992) Once this mapping is achieved, reports from the two systems can be run and compared, with discrepancies identified and corrected on a periodic basis. “The two sets of books [should be] reconciled regularly to eliminate errors and omissions. As long as there is an established procedure and the invoices properly coded, figures can be compared and analyzed.” (Pressoir, 1988)

ONE SOURCE OF DISCREPANCY BETWEEN THE TWO SYSTEMS…

…maybe due to delays in entering data. Often, the project controls system contains information regarding anticipated commitments, change orders in progress, or invoices received but not yet approved and paid, whereas the accounting system tracks only completed transactions regarding contracts/purchase orders, and payment applications. In a project, “…there are two important requirements of the report function; the need for timeliness and the importance of accuracy.” (Kimmons, 1987)

When one system contains more information than the other, the system fails both the timeliness and the accuracy (or completeness) test, and an interface needs to be constructed to take this information into account for the project status report.

How is your Capital Improvement Program Performing?

MOVING TOWARD A SOLUTION

Thus project teams find themselves in a situation whereby they must create a report that is tailored to its audience so as to be concise, comprehensible, consistent, and to provide the information needed for management to make decisions and take action. “[The project controls] mission – real-time due diligence – [results in its] most essential product… timely, high-quality, unembellished information and recommendations to support informed business decisions.” (Michalak, 2001) 

The reporting function must also be dynamic, and responsive to changing project conditions, in order to support information sharing and informed decision making. In order to create the report, the following questions must be posed (Rahbar & Yates, 1990):

• Who will be the primary users of the report?

• What level of familiarity do the end-users of the report have with construction project controls?

• What are the expectations of the end-users of the report, with respect to report content?

• What decisions are expected to be made based on the report?

• What are the sources on which the report will be based?

• What sort of accuracy is achievable, given the information available?

• How is adequacy of reporting determined?

• Can the available data achieve the expected adequacy of reporting?

• How frequently will the report be produced?

WHAT DEFINES A SUCCESSFUL PROJECT?

Of the questions posed above, the last is the most important and likely the subject of much debate between stake-holders. “The ideas and expectations of a successful project are often subjective, implicit and contradictory.” (de Ridder & Vrijhoef, 2005) Ideally, during the planning stages of the project, the stakeholders will participate in a facilitated discussion to prioritize project cost, schedule, quality, safety, and other goals, thus defining project success.

Once the elements of a ‘successful project’ have been defined, the project manager in association with the project controls group can use the questions above to develop a report that satisfies the needs of the project stakeholders and decision-makers. The level of report detail will vary directly with the stakeholder level of involvement and may require the development of several reports targeted to specific stakeholder groups.

FOCUS ON FORETHOUGHT & DILIGENCE

Throughout the lifecycle of any construction project, stakeholders and management use project data to assess the status of the project and make decisions. It is the duty of the project team to provide these executives with expenditure data and forecasts that enable them to focus on high-risk areas, instead of merely reacting to crisis situations. The goal is to isolate critical project data, and promptly direct that information to the appropriate individuals for immediate action.

Issue management should enable upper management to focus on current risks and take appropriate strategic steps, instead of delaying the receipt of otherwise good information until time and circumstances narrow the authority and ability of the decision-makers to effect change.

In order to satisfy that need, the project team typically uses data from multiple sources, including information provided by financial accounting and project controls. However, challenges occur in project status reporting, because the data generated by accounting systems need to be manipulated, mapped, and validated before using it in conjunction with data from other sources. Care must be taken to ensure limitations in the cost accounting system do not preclude reporting at the expected level of detail. Additional steps must be taken to ensure the true status of the project is represented with timeliness, consistency, and clarity. When developing a useful and meaningful project status report format, the project team must consider the report’s intended audience – the information provided must be appropriate for and easily understood by the recipients.

With forethought and diligence applied at the start of the project in the development of an acceptable report format, stakeholders will thus be enabled to make decisions throughout the lifecycle of the project.

TALES OF PROJECT SUCCESS

“The magnitude of information that’s available to us has increased exponentially,” says Phillip Jabour, Associate VP and University architect at Southern Methodist University (SMU), referring to growth in the number of projects at SMU. “That’s what led us to try to find a way to manage the information in a centralized format. Before, we had to reinvent the wheel every time somebody asked for something.”

Originally written by – Alexia Nalewaik, PhD FRICS CCP CCA and Jeffrey Witt, CPA CIA MCSE CFE.