THU35 - Cash Flow Curve Considerations Can P6 Do a Cash Flow Curve? Jim Simons, PMP PSP EVP 1

Can P6 Do a Cash Flow Curve? • Primavera doesn’t really create a cash flow curve showing the timing of cash in/out. • However, the time-phased data that Primavera creates is the basis for creating an assortment of cash flow curves in Excel. • A cost processor can also utilize Primavera baseline to delay the planned value curve and create a cash flow curve. 2

Overview • The time-phased cash out and revenue in data touches many project members, each with unique uses and requirements. • Resource-loaded P6 activities can time-phase aggregate resource quantities across the dynamic network. • The P6 resource distribution layout is output to Excel and processed to reflect the timing delays.

Who uses Cash Flow? • Different functional entities have different uses and definitions of “Cash Flow” – A Project Manager might consider cash flow to be the value of work-in-place, or the Earned Value. – A Project Accountant might consider cash flow as the cost of transactions booked in the accounting system, or Actual Cost. – A Financial Manager might consider cash flow as an actual disbursement of funds from treasury, or Cash Out. – A Program Manager might consider the timing of disbursements against receiving revenue payments as extremely important. – A Project Controls Manager might consider cash flow as the Planned Value.

• The numbers are the same; the difference is timing. 4

How will they use the data? Support for a proposed project • Perhaps the Cash Flow Curve is expected to give management some idea of future cash demands. – These demands may be coordinated with other projects, to present the composite enterprise cash flow. – If the aggregate cash demands exceed the corporate resources, then projects are dropped/delayed until the curve falls within available resources. – These broad brush Cash Flow Curves are not expected to be definitive or exact. – Primavera can plot enterprise resources for this type of analysis, which is especially useful when both proposed and in-flight projects are displayed in an enterprise report. 5

How will they use the data? Forecasting Financial Cash Flow – When the Cash Flow Curve is expected to forecast the real cash demands of a real project, the matter becomes a bit more serious. – If we were able to predictably forecast cash demands, Finance would be very happy with us. – Finance is less concerned about when the work was performed, than the demands on treasury from invoices and the timing of revenue receipts. – The accuracy of the PV curve data compiled by P6, is dependent on the level of detail necessary to model the timing of discrete elements of the work. 6

Factors affecting Disbursement Timing • The Cost Type – Direct Labor – Paid weekly or every other week; Essentially instantaneous. – No delay, as the labor is already reported as an actual transaction.

– Direct Material – Purchase Order – Paid in accordance with the conditions of the purchasing document. – Usually 15 days after invoicing, with invoicing up to 30 days after delivery. – Thus, the delay is 15-45 days.

– Direct Material – Purchase Agreement – Similar to Purchase Order. – The PA is usually more specific about payment. – Usually 45 days after delivery.

– Subcontracts – – – –

Usually paid based on end of the month progress. Producing the Payment Application requires at least 2 weeks. Approval might be another 2-3 weeks. Payment might be 30 days after approval of the invoice. 7

The time sequence of cash flow • Calculated by the schedule network. – The schedule calculations produce early and late dates for every activity. – The loading values can be captured for any period from hours to years, but days and months are the more common. – Both early and late curves may be plotted. • Perhaps the most reasonable Planned Value curve would use median values.

8

Defining P6 Cash Flow Resources – P6 “resources” are anything that can be quantified for an activity; CY, MH, USD, etc. – For clarity and processing consistency… • Each cash flow resource should be either Cash or Revenue at the root level. • At the next level, the Cash resources should be whatever Cost Types are normal to the organization; Lab, Matl, SubC, for example. • Define a resource for each combination of cost type and assumed period from execution to disbursement. • Define a P6 filter to only display this set of cash flow resources. 9

Establish P6 Cash Resources • Cash$ – [as a parent level, with no reporting at this level] » » » » » » »

– – – –

Cost Type = Material Unit Price = $1/unit UOM = $ Default Units/Time = 0 Maximum Units/Time = 0 Calendar = 7-day, No Holiday Do not Auto-Compute Actuals

Lab$, under Cash$, same configuration Matl$, under Cash$, same configuration SubC$, under Cash$, same configuration Xxx$, as many more under Cash$ to define the period from execution to disbursement.

10

Establish P6 Revenue Resources – Rev$ • Cost Type = Material » » » » » »

Unit Price = $1/unit UOM = $ Default Units/Time = 0 Maximum Units/Time = 0 Calendar = 7-day, No Holiday Do not Auto-Compute Actuals

11

P6 Resource Dictionary • Cash$ • • • • • • •

Lab$ Matl$30 Matl$45 Matl$60 SubC$45 SubC$60 SubC$75

• Rev$ • When Revenue is from multiple sources, with different statutory payment values, then more revenue resources should be added. • More revenue resources could be added for different colors of money, such as WADs on certain government projects. • This has the advantage of allowing WAD funding projections, which is a matter of intense interest on those projects.

12

Load Resource Quantities – All the Cash$ should sum to the budget value. – All the Rev$ should sum to the contract value.

– When modifications occur, they should be defined as separate activities, with discrete resource Cash$ and Rev$ values to maintain integrity to the budget and contract values.

13

Structuring the CFC Data and Graph • Planned Expenditures [PE] – – – – –

Calculate the PV for the remainder of the project. Summarize the PV by Cost Type [Resource ID] Graph each Cost Type by the appropriate expenditure delay. Add the projected expenditures for any unpaid billings. This PE S-curve projects when funds should be required.

• Review and Revise – Each month plot the actual expenditures against the Planned Expenditures. – If the variance shows a diverging trend: • Analyze the trend by Cost Type • Calculate a factor to bring the variance within tolerance. – This tolerance is justified by the empirical nature of the data.

• Apply the factor, revisit the analysis next month.

– Include the variance on the graph, proving limit compliance. 14

Excel Calculations • Insert cells before the first period for each row to shift entire row right, based on the timing. – If the interval scale is monthly, and the shift is one cell for 30 days, two for 60, etc. – A weekly interval scale would give more granularity to the cash out projections, but would not affect the monthly Revenue In.

• Insert new Sum formulas for the shifted Cost and Revenue values. – The Planned Value totals were unchanged.

• Calculate cumulative totals, graph [PV, Cash and Revenue] then analyze. Share as appropriate. 15

P6 Copy/Paste to Excel Budget Distribution from P6 Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Total

177

311

400

410

365

159

Labor

12

26

35

24

10

6

Matl 30

20

25

25

16

10

8

Matl 60

35

40

45

50

30

10

SubC 30

60

100

135

180

195

65

SubC 60

50

120

160

140

120

70

16

Excel Shift Budget Distribution from P6 Month 1 177 12 20 35 60 50

Total Labor Matl 30 Matl 60 SubC 30 SubC 60

Month 2 311 26 25 40 100 120

Month 3 400 35 25 45 135 160

Month 4 410 24 16 50 180 140

Month 5 365 10 10 30 195 120

Month 6 159 6 8 10 65 70

Month 7

Month 8

Month 7

Month 8

Resources Shifted to Reflect Cash Out Delay Labor Matl 30 Matl 60 SubC 30 SubC 60 Cash Out

∑

Month 1 12 > > > >

Month 2 26 20 > 60 >

Month 3 35 25 35 100 50

Month 4 24 25 40 135 120

Month 5 10 16 45 180 160

12

106

245

344

411

Month 6 6 10 50 195 140

8 30 65 120

70

401

223

80

10

17

Excel Results Budget Distribution from P6

Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Planned Value 177 311 400 410 365 159 Cuml 177 488 888 1,298 1,663 1,822

Period Results Cash Out Revenue In

Month 1

Month 2

Month 3

12 -

106 204

245 358

Month 4 344 460

Month 5

Month 6

Month 8

Month 7

Month 8

411 472

401 420

223 183

80 -

Cumulative Results Cash Out Rev In

Net Flow

12

118

363

707

1,118

1,519

1,742

1,822

-

204

561

1,021

1,493

1,912

2,095

2,095

86

198

314

375

393

353

273

(12)

18

Cash Flow Graphics • Based on the 86 key activities for a $100m BFB boiler. • Define Cost Budget resources indicating Cost Type and payment delay interval after execution. • Load P6 activities with Cost Budget and Revenue Budget. • Open Resource Assignment view in P6. • Format the table for intervals and subtotals. – Group by resources, timing.

• Copy the entire table [Ctrl-A] and paste into Excel. • Delete the subtotal rows for cost and revenue resources. 19

Cash Flow using Early Dates 120,000,000

Early Planned Value 100,000,000

Early Cash Flow 80,000,000

Early Revenue 60,000,000

40,000,000

20,000,000

0

20

Cash Flow using Late Dates 120,000,000

100,000,000

Late Planned Value 80,000,000

Late Cash Flow 60,000,000

Late Revenue 40,000,000

20,000,000

0

21

Early / Late Planned Values 120,000,000

100,000,000

Late Planned Value 80,000,000

Early Planned Value 60,000,000

40,000,000

20,000,000

0

22

Cash Flow Delta 9,000,000

Cash Flow Delta = Revenue In less Cash Out

8,000,000 7,000,000 6,000,000 5,000,000

Early Cash Delta 4,000,000

Late Cash Delta 3,000,000 2,000,000 1,000,000 0

23

Composite Planned Value 120,000,000

Late Planned Value 100,000,000

Early Planned Value 80,000,000

Composite Planned Value 60,000,000

40,000,000

20,000,000

0

24

Composite Cash Flow Delta 9,000,000 8,000,000

Composite Cash Flow Delta = Average Early/Late Cash Flow Deltas Early Cash Delta

7,000,000

Late Cash Delta 6,000,000 5,000,000

Composite Cash Delta

4,000,000 3,000,000 2,000,000 1,000,000 0

25

Results May Differ – The PV is usually based on the Early Dates • Any particular item of work may be delayed by the Free Float, or even Total Float and still not technically compromise the network. • Basing the PE on the recalculated network [not PMB], will dampen much of this possible variation. • Large items with lump sum expenditures may skew the statistics. Thus, they should be clearly reflected in the budget loading to keep the PV as accurate as possible. • Deductions and hold-backs are individual management prerogatives on invoices that have been entered into the accounting system, are not addressed. If they were not in the accounting system, they were simply an accrual. 26

Results May Differ – By default, P6 spreads the resources evenly [linear distribution] across the activity period of performance. • Usually this is adequate, especially for short activities. • Each resource assignment may be assigned a resource curve when the resource is front-end loaded or another or custom configuration. This might be appropriate for longer activities with varying intensities. • As the curve applies to each resource in each activity, assigning a resource curve to activities less than 3 update periods long may be more effort than necessary to reach the result. 27

Results May Differ – P6 assigns values for each increment of a planning unit [day]. The related resource loading is much more detailed than necessary for reporting, so the output is usually summed for the end of the month. • So, the resource quantities are actually a series of monthly steps, as opposed to a curve. • With each element [budget, earned, billed, paid] at a 30-day interval, not the standard EOM, the results may show more variance than reality. • If the analysis period were days, then the curves would be more predictable, but daily reporting, billing, etc. would be impractical, or at least expensive. 28

Projections Change – At each update cycle the cash flow results should be reviewed to determine if the assumptions are still current, or will need adjustment. • Documenting these adjustments will allow the next project to be projected more accurately.

– As the Estimate to Complete [ETC] is reviewed and revised with each update, the changes may require updating the cost resource quantities, to keep the cash flow projections accurate. • The threshold for changes should be defined by management. • On a project with hundreds of changes, this could be a significant factor. 29

Looking over the Horizon • Start with a good PMB; – Clear, complete Scope – Comprehensive Estimate – Healthy Schedule

• Know the data, the contract and subcontracts. • Obtain and maintain Team Involvement, including Finance for requirements and data. • Analyze flow optimization alternates with the Team. • Revisit actual cash flow and progress at each update cycle. • Adjust projections for ETC, as necessary. 30

Questions?

• Jim Simons, PMP PSP EVP – [email protected] – 334-546-0224 Cell 31