TEACHING PROJECT CASH FLOW PROJECTIONS USING A SIMPLE SPREADSHEET

INTRODUCTION

The process of managing a construction project is complex. General contractors take responsibility for an entire project but subcontract out most or all of the actual construction to specialty contractors. The size and uniqueness of the product, the impermanence of relationships, and the overlapping control mechanisms of the participants require a considerable amount of management expertise to be exercised by the general. contractor. Management functions in the construction process must entail, therefore, a full range of contractual tasks such as purchasing, fabrication control, inventory control, personnel supervision, and contract management.

Despite the sophistication with which some of these firms perform managerial tasks, the size and complexity of construction projects often outpaces the capacity to manage all aspects of the construction process effectively. As a consequence, the construction industry experiences an inordinate number of business failures. It is well known that entry into the construction business is relatively easy, requiring little preplanning or capitalization. Exiting, frequently via bankruptcy, is also very easy. Business failure in the construction industry can usually be traced to poor management skills rather than to a lack of technical competence or to the failure to demonstrate mastery of the respective construction trades.

According to Dun and Bradstreet, the top three reasons for construction firm business failures are: lack of business experience, inadequate project estimating and/or cost control, and inadequate working capital. The third reason for failure, inadequate working capital, is mainly a problem of poor cash management. In poorly managed firms, cash management is frequently neglected and insuring that enough cash is available at the right time becomes a daily problem. This is unfortunate and unnecessary since, assuming that a project is priced and controlled properly, most contract payment terms enable a contractor to produce an adequate cash flow without the daily or weekly need to "scramble to make ends meet and pay bills".

The successful contractor plans for the maintenance of adequate cash balances so that his company can operate efficiently, pay debts on time, and take full advantage of cash discounts. In most cases a company's cash flow projections will determine the amount and quality of jobs that can be safely undertaken. Since most banks and sureties consider contractors relatively high risks, the amount of financing available to finance operations and the contractor's bonding limits are determined, in large part, by an analysis of working capital and cash flow.

CASH FLOW PROJECTION

The major purpose of projecting project cash flow is to determine how much and at what time in the construction cycle cash will be needed to finance operations. If money has to be borrowed to finance operations, the creditor wants to know why, when, and how much the contractor needs to borrow. In addition, the creditor will demand to know how and when the money will be repaid. If the contractor must supply bonding, the surety will demand to see cash flow projections that will allow him to determine if the contractor's line of credit is sufficient to finance the volume of work to be bonded.

In addition, most construction contracts, such as AIA 201, require the contractor to supply the owner with a schedule of values that will be used as a preliminary basis for payments to the contractor. Cash flow can be characterized as

"cash-in/cash-out". The schedule of values required by the contract is simply the contractor's estimate of "cash-in" timing.

There are two levels of cash projections that concern the contractor. The first level is project cash flow projection. This is an estimate of cash flow over the contract life of each project. The second level is company cash^, flow projection. This is a summary of the combined cash flow for all projects and the indirect, administrative, and financing expenses of the company. This paper will address the development of project cash flow projections.

Project Cash Flow Projection

The basis for plotting the cash-in and cash-out for a construction project is the project schedule or plan and the estimate summary sheet. At the very least the contractor must know the starting and ending dates of each activity and the direct and indirect costs of each activity. This information is essential to managing the cash that flows in and out of a project.

In addition to the schedule and estimate the contractor must know what conditions the contract has put on progress payments. This information will determine when and how cash flows into the project.

The estimate, project schedule, and progress payment schedule information must be combined with company policies concerning payment of labor, subcontracts, equipment, and materials. The project cash flow projection can then be prepared based on when the costs will be payed (cash-out) and when payments will be received (cash-in).

TEACHING PROJECT CASH FLOW PROJECTION

Teaching construction students the fundamentals of projecting project cash flow can be tedious and time consuming. Before computers became common classroom tools, cash flow analysis using manual computations was necessarily cursory. The use of computers allows the student and the instructor to focus on the concepts of managing a construction company's cash rather than focusing on developing procedures to prepare a cash flow budget. The following sections will describe how a simple computer generated spreadsheet can be used to teach cash flow concepts to construction students in a senior project management class.

Course Description and Environment

The class in which cash flow concepts are taught at East Carolina University is a senior level class in project management. The class is the third in a sequence of courses designed to simulate the construction phase of the building process. The course emphasis is on the application and practice of management techniques to control the manpower, materials, money, and machinery of a construction project.

The students entering the class are typically seniors in the Department of Construction Management who have completed most of the management and construction courses required in the program. They should have completed estimating and scheduling as well as management courses such as accounting, finance, and personnel management. In addition, the students have usually completed at least one computer course which introduced them to the use of a personal computer and related software.

Since the class format is primarily lecture/discussion, all of the hands-on computer time is spent outside of class. The Construction Management program is not blessed with unlimited funds and facilities. Consequently, the class must use computer facilities located in another building. These facilities are very adequate and readily available at most times. If the computer rooms are to be used for classroom instruction they must be reserved ahead of time.

Computers and Software

The computers available to the students are primarily IBM PC's and Apple IIe's and Macintosh's. The students are allowed to choose the type of computer they wish to use. This decision is usually based on which computer was used in the required computer course; usually IBM PC's. However, some students either own other makes of computers or would rather use an alternate computer.

There is a wide range of software available to the students. If the students have completed the required introductory computer course, they have already purchased an integrated software package written for the IBM PC. In addition, the student may check out, many different types of commercially available spreadsheet software.

The Assignment

Cash flow concepts are usually introduced in the fourth to fifth week of the semester. After approximately two hours of lecture devoted to describing the cash flow projection process, the students are asked to develop a spreadsheet template which will allow monthly cash flow projections for a simple project. This template is produced manually and checked for accuracy and its ability to produce the desired results efficiently. Table 1 is an example of the assignment given to the students.

When development of the template is complete, the students are assigned a more complicated project that requires them to use spreadsheet software to determine cash flow projections. The assignment consists of an Estimate Summary and Preliminary Project Schedule (start and end dates only) of a small office complex that priced out at $497,000 The assignment is done outside of class and is due in two weeks.

To complete the assignment the students are required to turn in a cash flow analysis that displays when costs are to be paid, when payments are received and what financing must be available. The only manual calculations required to complete the assignment are used to determine when the student wishes to pay for labor, materials, subcontracts, and equipment. In order to keep the analysis simple, certain assumptions must be made. These assumptions are:

1. Payroll is paid weekly.

2. Materials are purchased at the start of an activity. They must be paid for by the 10th of the month after installation in order to receive a 2% cash discount.

3. Subcontracts must be paid by the 15th of the month for work completed the preceding month.

4. Equipment rental is paid by the 10th

of the month after use. 5. Company overhead is 15%. 6. Profit is 10%.

7. Interest is 10%.

Table 2 presents the typical analysis sheet turned in by students.

The final unit of instruction describes how to project the cash flow analysis to monthly balance sheets, statements of earnings, and company cash flow projections. Since time is a factor, this procedure is demonstrated.

CONCLUSION

There are many advantages to teaching project cash flow projections using computers and related software. Probably the most important advantage is that it requires the student to solve problems by creating solutions (spreadsheets) rather than simply determining the correct number to plug into a prepackaged solution. Though there are many excellent software packages available, many require little of the student except to "walk through" the program. Many software packages that claim to estimate, plan and schedule construction projects are, by necessity, designed to fit a broad range of situations. A student using this software spends most of his time trying to mold his situation into the parameters of the software rather than molding the software to solve the problems created by his situation.

Another important advantage is that the student can use the spreadsheet skills he has learned analyzing cash flow to solve other construction related problems. The analysis of equipment needs and crew level planning are two of the areas readily adaptable to spreadsheet analysis.

Finally, the students' and the instructor's time is used more effectively. Students can spend more time determining the solution to the problem rather than performing lengthy, though simple, calculations. The instructor saves valuable time in grading and creating new assignments. It is very easy to modify the assignments and the templates required to solve problems. The time saved by the instructor is well worth the investment in learning to teach cash flow using computers and spreadsheets.