Version 3.1 reflects the following changes:
1. Adds additional data entry checks to prevent users from attempting to use features on the Win-Win Analysis
sheet before entering all required data.
2. Instructions, explanations and messages have been revised to provide better information for the user.
3. In rare conversion situations, the prior version of the tool did not reflect the correct handling of lag times during
the pre-PBP time period. Although the impact would have been negligible in most situations, formulas have been
changed to ensure calcuations are accurate in all situations.
DOD Performance Based Payments Analysis Tool (Version 3.1 - Demo) - Conversion
Important Notes:
Macros nust be enabled in order for the model to work.
1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK.
Tip If Analysis Toolpak is not listed, click Browse to locate it.
DOD Performance Based Payments Analysis Tool (Version 3.1 - Demo) - Conversion
This a demonstration version of the DOD PBP Tool for a conversion to PBPs from some other financing
method. In Version 3.1 users can select the contract situation that applies : "New Awards" where no
financing has been provided yet or "Conversion" where the contract is already underway and the
contractor has been provided some financing to date. This demo version already has data in the Contract
Summary and Data Input sheets. For PBP actions, users should use the actual DOD PBP Tool (Version
3.1) to enter the applicable data for their specific contract action. The DOD PBP Tool can be found under
the Cost, Price and Finance section of the DPAP website. This demonstration version operates exactly
On the actual DOD PBP Tool, this sheet will contain the steps required to enter the necessary data and use
Users should read the information on "Using the Model" and "Assumption Explanations" sheets in order
to understand the purpose of the model and how it operates.
Important Notes:
Macros nust be enabled in order for the model to work.
This tool uses the XIRR and XNPV functions which are standard functions in Excel 2007 and later verisons.
If you are using an earlier version of Excel, Microsoft's Analysis Toolpak must be installed as follows:
Do not 'Cut and Paste' cells on the "Data Input" sheet as this will cause errors in protected cells which
cannot be reversed by the 'Undo' feature. 'Copy' and 'Paste' is allowed but the 'Right-Click' method for
1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK.
Tip If Analysis Toolpak is not listed, click Browse to locate it.
3. If you see a message that tells you the Analysis Toolpak is not currently installed on your computer,
click Yes to install it.
4. Click Tools on the menu bar. When you load the Analysis Toolpak, the Data Analysis command is added to the
Tools menu.
Conversion
Conversion to PBPs - Special Instructions
Important Reminder: Awarding of a contract or definitization of a contract cannot be a PBP event.
Conversion to PBPs - Special Instructions
Important Reminder: Awarding of a contract or definitization of a contract cannot be a PBP event.
If this action involves converting from progress payments or any other type of financing to PBPs there are
several important issues to consider:
Per FAR, PBPs cannot exceed 90% of the contract price. When the contract converts to PBPs, prior
financing payments must be added to the amounts that will be paid using PBPs to ensure that total
financing payments do not exceed 90% of the contract price. Whenever total financing would exceed the
90% limit, the model will adjust PBP values downward to ensure compliance with the 90% limit. When
that happens, you must make sure the contract reflects the adjusted event values. The values entered on
the Data Input sheet cannot be used as they are unadjusted values which would cause total financing to
exceed 90% of the win-win price. The model will remind you when this situation occurs.
The most common conversion situation is likely to be the definitiizatuion of an Undefinitized Contract
Action (UCA). There will also be contracts awarded with customary progress payments where the
contractor requests to convert to PBPs. In both cases the contractor will probably have been receiving
progress payments prior to the conversion to PBPs.
In establishing PBP values in those situations, the most common approach is for the initial PBP event to
be valued at the forecasted total cost (using the evaluated expenditure profile) at the time the event is
scheduled for completion. When this approach is used, the contract must make it clear that the amount
to be paid upon completion of the initial event will be the event value less financing already provided
(usually progress payments) through that date.
For example: assume that a UCA has been in place for six months and the contractor has incurred $20
million in cost and been paid $16 million in customary progress payments to date. The definitization price
has just been negotiated, including the PBP schedule. Based on contract clearance reviews, the contract
is expected to be issued 45 days from now and the first PBP event is scheduled to be completed 60 days
from now (in month 8). The contractor will continue to recieve progress payments under the UCA until the
definitized contract is issued. The contractor expects to incur $10 million over the next two months,
therefore the initial PBP event is valued at $30 million ($20 million incurred to date plus the $10 million
forecasted). Although the event will have a value of $30 million, the contract will state that upon
completion of the event, the contractor will be paid $30 million less total progress payments billed to date.
In entering the PBP data on the Data Input sheet in a conversion situation, the first event value should be
amount that will be due the contractor upon completion of the event. In the above example, if the value of
the first PBP event was set at $30 million and the contractor will have been paid $24 million via progress
payments, the value of the first PBP event entered on the Data Input sheet would be $6 million ($30M PBP
value less $24M already recieved in progress payments).
The model is also capable of handling those situations where the contractor has not received progress
payments against all allowable costs incurred or has been paid some other form of financing prior to
conversion to PBPs. The model will reflect the default progress payment rate in Column L on the Win-Win
Analysis sheet for each month prior to conversion. These cells will be yellow-shaded like all other user-
changeable cells. Users can modify the percentages in those cells to reflect the actual financing provided
to date. Note that the percentage entered for a month are applied to the Ktr Cash Expenditure for that
month in Column B. The month the progress payment will be received by the contractor will depend on
the Lag Time entered for progress payments.
Program Name / Contract Action Description
Position Position
Using Progress Payments Using PBP
Profit Bearing Cost $ 64,000,000 $ 64,000,000
Cost of Money(1) $ 250,000 $ 250,000
Total Cost $ 64,250,000 $ 64,250,000
(2)
Profit $ 7,680,000 12.00% Profit Rate $ 6,957,339 10.87% Profit Rate
0.119533 0.108285
Total Price $ 71,930,000 $ 71,207,339
(1)
Facilities Capital Cost of Money plus any other non profit-bearing costs such as interdivisional transfers at price.
(2)
Enter the objective profit dollars produced by the weighted guidelines analysis assuming progress payments, not performance based
payments for this contract action. The profit rate will be calculated using this profit amount.
Discounted Cash Flow /
Internal Rate of Return (IRR) Analysis
Assumptions :
Progress Payment Rate 80% Progress Payment Lag Days
Govt Cost of Money Rate (%) 3.13% PBP / DD 250 Lag Days
Contractor Hurdle Rate (%) 8.27% PBP Cost Limitation
With Progress Payments
Total Price $71,930,000
Profit Rate 12.00%
IRR (Internal Rate of Return) 31.02%
Final Cost to Govt $73,984,969
KTR NPV @ Hurdle Rate $4,719,152
Prog Pay Prog Pay
KTR Cash Cash Cum Cash
Expenditure Prog Pay DD 250 Flow Flow
Dec-11 (1,000,000) - - (1,000,000) (1,000,000)
Jan-12 (1,215,000) 800,000 - (415,000) (1,415,000)
Feb-12 (1,400,000) 972,000 - (428,000) (1,843,000)
Mar-12 (1,600,000) 1,120,000 - (480,000) (2,323,000)
Apr-12 (1,752,000) 1,280,000 - (472,000) (2,795,000)
May-12 (1,957,800) 1,401,600 - (556,200) (3,351,200)
Jun-12 (2,000,100) 1,566,240 - (433,860) (3,785,060)
Jul-12 (2,100,000) 1,600,080 - (499,920) (4,284,980)
Aug-12 (2,200,000) 1,680,000 - (520,000) (4,804,980)
Sep-12 (2,550,000) 1,760,000 - (790,000) (5,594,980)
Oct-12 (2,600,000) 2,040,000 - (560,000) (6,154,980)
Nov-12 (2,750,000) 2,080,000 - (670,000) (6,824,980)
Dec-12 (2,800,000) 2,200,000 - (600,000) (7,424,980)
Jan-13 (2,900,000) 2,240,000 - (660,000) (8,084,980)
Feb-13 (3,000,000) 2,320,000 - (680,000) (8,764,980)
Mar-13 (2,900,000) 2,400,000 - (500,000) (9,264,980)
Apr-13 (2,700,000) 2,320,000 - (380,000) (9,644,980)
May-13 (2,600,000) 2,160,000 - (440,000) (10,084,980)
Jun-13 (2,500,000) 2,080,000 - (420,000) (10,504,980)
Jul-13 (2,425,000) 2,000,000 - (425,000) (10,929,980)
Aug-13 (2,175,000) 1,940,000 - (235,000) (11,164,980)
Sep-13 (2,000,100) 1,740,000 - (260,100) (11,425,080)
Oct-13 (1,900,000) 1,600,080 - (299,920) (11,725,000)
Nov-13 (1,800,000) 1,520,000 - (280,000) (12,005,000)
Dec-13 (1,700,000) 1,440,000 - (260,000) (12,265,000)
Jan-14 (1,500,000) 1,360,000 - (140,000) (12,405,000)
Feb-14 (1,300,000) 1,200,000 - (100,000) (12,505,000)
Mar-14 (1,250,000) 1,040,000 2,053,000 1,843,000 (10,662,000)
Apr-14 (1,100,000) 1,000,000 2,053,000 1,953,000 (8,709,000)
May-14 (900,000) 880,000 2,053,000 2,033,000 (6,676,000)
Jun-14 (875,000) 720,000 2,053,000 1,898,000 (4,778,000)
Jul-14 (850,000) 700,000 2,053,000 1,903,000 (2,875,000)
Aug-14 (650,000) 680,000 2,053,000 2,083,000 (792,000)
Sep-14 (500,000) 520,000 2,053,000 2,073,000 1,281,000
Oct-14 (500,000) 400,000 2,053,000 1,953,000 3,234,000
Nov-14 (300,000) 400,000 2,053,000 2,153,000 5,387,000
Dec-14 - 240,000 2,053,000 2,293,000 7,680,000
Jan-15 - - - - 7,680,000
Feb-15 - - - - 7,680,000
Mar-15 - - - - 7,680,000
Apr-15 - - - - 7,680,000
Total (64,250,000) 51,400,000 20,530,000 7,680,000
w/
Govt Break Even
30 KTR Break Even
30
Approximate
Win/Win Solution
RESET PBPs
With PBP
Total Price $71,207,339
Profit Rate 10.87%
IRR (Internal Rate of Return) 52.63%
Final Cost to Govt $73,655,321
KTR NPV @ Hurdle Rate $4,987,427
PBP % of Price 89.60%
PBP PBP
Cash Cum Cash
PBP DD 250 Flow Flow Progress Payment Rate
- - (1,000,000) (1,000,000) 80%
800,000 - (415,000) (1,415,000) 80%
972,000 - (428,000) (1,843,000) 80%
1,120,000 - (480,000) (2,323,000) 80%
1,280,000 - (472,000) (2,795,000) 80%
1,401,600 - (556,200) (3,351,200) 80%
4,000,000 - 1,999,900 (1,351,300) 80%
- - (2,100,000) (3,451,300) 80%
2,000,000 - (200,000) (3,651,300) 80%
- - (2,550,000) (6,201,300) 80%
4,750,000 - 2,150,000 (4,051,300) 80%
- - (2,750,000) (6,801,300) 80%
5,350,000 - 2,550,000 (4,251,300) 80%
- - (2,900,000) (7,151,300) 80%
5,700,000 - 2,700,000 (4,451,300) 80%
3,000,000 - 100,000 (4,351,300) 80%
2,900,000 - 200,000 (4,151,300) 80%
2,700,000 - 100,000 (4,051,300) 80%
- - (2,500,000) (6,551,300) 80%
5,100,000 - 2,675,000 (3,876,300) 80%
- - (2,175,000) (6,051,300) 80%
5,600,000 - 3,599,900 (2,451,400) 80%
2,000,100 - 100,100 (2,351,300) 80%
1,900,000 - 100,000 (2,251,300) 80%
1,800,000 - 100,000 (2,151,300) 80%
- - (1,500,000) (3,651,300) 80%
3,200,000 - 1,900,000 (1,751,300) 80%
- 740,864 (509,136) (2,260,436) 80%
2,550,000 740,864 2,190,864 (69,572) 80%
- 740,864 (159,136) (228,708) 80%
2,000,000 740,864 1,865,864 1,637,156 80%
- 740,864 (109,136) 1,528,020 80%
1,725,000 740,864 1,815,864 3,343,883 80%
- 740,864 240,864 3,584,747 80%
1,150,000 740,864 1,390,864 4,975,611 80%
500,000 740,864 940,864 5,916,475 80%
300,000 740,864 1,040,864 6,957,339 80%
- - - 6,957,339 80%
- - - 6,957,339 80%
- - - 6,957,339 80%
- - - 6,957,339 80%
63,798,700 7,408,639 6,957,339
Cum Cum Cum Unliquidated
Cost Progress Pay PBP Progress Pay
1,000,000 - - -
2,215,000 800,000 800,000 800,000
3,615,000 1,772,000 1,772,000 1,772,000
5,215,000 2,892,000 2,892,000 2,892,000
6,967,000 4,172,000 4,172,000 4,172,000
8,924,800 5,573,600 5,573,600 5,573,600
10,924,900 7,139,840 9,573,600 7,139,840
13,024,900 8,739,920 9,573,600 8,739,920
15,224,900 10,419,920 11,573,600 10,419,920
17,774,900 12,179,920 11,573,600 12,179,920
20,374,900 14,219,920 16,323,600 14,219,920
23,124,900 16,299,920 16,323,600 16,299,920
25,924,900 18,499,920 21,673,600 18,499,920
28,824,900 20,739,920 21,673,600 20,739,920
31,824,900 23,059,920 27,373,600 23,059,920
34,724,900 25,459,920 30,373,600 25,459,920
37,424,900 27,779,920 33,273,600 27,779,920
40,024,900 29,939,920 35,973,600 29,939,920
42,524,900 32,019,920 35,973,600 32,019,920
44,949,900 34,019,920 41,073,600 34,019,920
47,124,900 35,959,920 41,073,600 35,959,920
49,125,000 37,699,920 46,673,600 37,699,920
51,025,000 39,300,000 48,673,700 39,300,000
52,825,000 40,820,000 50,573,700 40,820,000
54,525,000 42,260,000 52,373,700 42,260,000
56,025,000 43,620,000 52,373,700 43,620,000
57,325,000 44,820,000 55,573,700 44,820,000
58,575,000 45,860,000 55,573,700 40,720,000
59,675,000 46,860,000 58,123,700 36,580,000
60,575,000 47,740,000 58,123,700 32,320,000
61,450,000 48,460,000 60,123,700 27,900,000
62,300,000 49,160,000 60,123,700 23,460,000
62,950,000 49,840,000 61,848,700 19,000,000
63,450,000 50,360,000 61,848,700 14,380,000
63,950,000 50,760,000 62,998,700 9,640,000
64,250,000 51,160,000 63,498,700 4,900,000
64,250,000 51,400,000 63,798,700 (0)
64,250,000 51,400,000 63,798,700 -
64,250,000 51,400,000 63,798,700 -
64,250,000 51,400,000 63,798,700 -
64,250,000 51,400,000 63,798,700 -
Liquidated Unliquidated Liquidated Cost To Government
Progress Pay PBP PBP Prog Pay PBP
- - - - -
- 800,000 - 2,083 2,083
- 1,772,000 - 4,615 4,615
- 2,892,000 - 7,531 7,531
- 4,172,000 - 10,865 10,865
- 5,573,600 - 14,515 14,515
- 9,573,600 - 18,593 24,931
- 9,573,600 - 22,760 24,931
- 11,573,600 - 27,135 30,140
- 11,573,600 - 31,719 30,140
- 16,323,600 - 37,031 42,509
- 16,323,600 - 42,448 42,509
- 21,673,600 - 48,177 56,442
- 21,673,600 - 54,010 56,442
- 27,373,600 - 60,052 71,285
- 30,373,600 - 66,302 79,098
- 33,273,600 - 72,344 86,650
- 35,973,600 - 77,969 93,681
- 35,973,600 - 83,385 93,681
- 41,073,600 - 88,594 106,963
- 41,073,600 - 93,646 106,963
- 46,673,600 - 98,177 121,546
- 48,673,700 - 102,344 126,754
- 50,573,700 - 106,302 131,702
- 52,373,700 - 110,052 136,390
- 52,373,700 - 113,594 136,390
- 55,573,700 - 116,719 144,723
5,140,000 49,193,830 6,379,870 106,042 128,109
5,140,000 45,363,960 6,379,870 95,260 118,135
5,140,000 38,984,090 6,379,870 84,167 101,521
5,140,000 34,604,220 6,379,870 72,656 90,115
5,140,000 28,224,350 6,379,870 61,094 73,501
5,140,000 23,569,480 6,379,870 49,479 61,379
5,140,000 17,189,610 6,379,870 37,448 44,765
5,140,000 11,959,740 6,379,870 25,104 31,145
5,140,000 6,079,870 6,379,870 12,760 15,833
5,140,000 - 6,379,870 - -
- - - - -
- - - - -
- - - - -
- - - - -
51,400,000 63,798,700 2,054,969 2,447,981
Calculations Used in Model
Lag Time
Prog Pay Lag PBP Lag DD250 Lag
0 0 0
1 1 1
0 0 0
n Model
PBPs
Lag Adjustment
Other Calculations used by Model No PBP Cap Adjustment
0
Total Cost to Govt Difference (329,648) -
KTR_NPV_Difference 268,275 -
Actual Effective Progress Payment Rate 0.8000 -
Progress Payment DD250 Formula 0.28542 -
Cost_Limit (1 = Limit Active, 0 = No Limit) - -
PBP 90% Cap Adjustment Factor 1.0000 -
4,000,000
-
Total PBPs 58,225,100 2,000,000
Total Pre-PBP Progress Payments 5,573,600 -
Total PBPs plus Progress Payments 63,798,700 4,750,000
PBP Cap (90% of PBP Price) 64,086,605 -
Difference 287,905 5,350,000
Adjusted PBP Total 58,513,005 -
5,700,000
3,000,000
2,900,000
2,700,000
-
5,100,000
-
5,600,000
2,000,100
1,900,000
1,800,000
-
3,200,000
-
2,550,000
-
2,000,000
-
1,725,000
-
1,150,000
500,000
300,000
-
-
-
-
Cum PBPs Cum
Lag Adjustment Pre PBP Progress Payments Pre PBP Progress Payments
No PBP Cap Adjustment No Lag Adjustment No Lag Adjustment
- 800,000 800,000
- 972,000 1,772,000
- 1,120,000 2,892,000
- 1,280,000 4,172,000
- 1,401,600 5,573,600
- 5,573,600
4,000,000 5,573,600
4,000,000 5,573,600
6,000,000 5,573,600
6,000,000 5,573,600
10,750,000 5,573,600
10,750,000 5,573,600
16,100,000 5,573,600
16,100,000 5,573,600
21,800,000 5,573,600
24,800,000 5,573,600
27,700,000 5,573,600
30,400,000 5,573,600
30,400,000 5,573,600
35,500,000 5,573,600
35,500,000 5,573,600
41,100,000 5,573,600
43,100,100 5,573,600
45,000,100 5,573,600
46,800,100 5,573,600
46,800,100 5,573,600
50,000,100 5,573,600
50,000,100 5,573,600
52,550,100 5,573,600
52,550,100 5,573,600
54,550,100 5,573,600
54,550,100 5,573,600
56,275,100 5,573,600
56,275,100 5,573,600
57,425,100 5,573,600
57,925,100 5,573,600
58,225,100 5,573,600
58,225,100 5,573,600
58,225,100 5,573,600
58,225,100 5,573,600
58,225,100 5,573,600
PBP Formulas
( Combined Formulas needed when Progress Payments are made before start of PBPs)
PBP Formulas Combined Formula Progress Pay Combined Formula2
Only PBPs Cost Limit Prior to PBPs No Cost Limit
800,000 - - -
Progress Payment Formulas
are made before start of PBPs) Pre-PBP Formulas Post PBP Start
Combined Formula3 PP Formulas PP Formulas
800,000 80%
972,000 80%
1,120,000 80%
1,280,000 80%
1,401,600 80%
1,566,240 80%
1,600,080 80%
1,680,000 80%
1,760,000 80%
2,040,000 80%
2,080,000 80%
2,200,000 80%
2,240,000 80%
2,320,000 80%
2,400,000 80%
2,320,000 80%
2,160,000 80%
2,080,000 80%
2,000,000 80%
1,940,000 80%
1,740,000 80%
1,600,080 80%
1,520,000 80%
1,440,000 80%
1,360,000 80%
1,200,000 80%
1,040,000 80%
1,000,000 80%
880,000 80%
720,000 80%
700,000 80%
680,000 80%
520,000 80%
400,000 80%
400,000 80%
240,000 80%
- 80%
- 80%
- 80%
- 80%
- 80%
$10,000,000
$20,000,000
$30,000,000
$40,000,000
$50,000,000
$60,000,000
$70,000,000
$0
IRR
NPV
Profit
Dec-11
Jan-12
Feb-12
Mar-12
Apr-12 31.02%
12.00%
Prog Pay
May-12 $ 4,719,152
Jun-12
Jul-12
Cost
Aug-12
Financial Returns
Sep-12
Oct-12
PBP
56.12%
Nov-12
$ 5,015,318
10.80%
Progress Pay
Dec-12
Jan-13
Feb-13
PBP
Mar-13
Apr-13
May-13
Jun-13
Jul-13
Aug-13
Sep-13
Oct-13
Nov-13
Financing Cash Flow Comparison
Dec-13
Jan-14
Feb-14
Mar-14
Apr-14
May-14
Jun-14
Jul-14
Aug-14
Sep-14
Oct-14
Nov-14
Dec-14
Jan-15
Feb-15
Mar-15
Apr-15
Enter number of months c
financing prior to first PB
Contractor CLIN Deliveries @ Performance Based
Month Expenditure @ Cost Price Payments What if PBPs PBP CLIN Price
Dec-11 1,000,000 -
Jan-12 1,215,000 -
Feb-12 1,400,000 -
Mar-12 1,600,000 -
Apr-12 1,752,000 -
May-12 1,957,800 4,000,000 4,000,000 -
Jun-12 2,000,100 -
Jul-12 2,100,000 2,000,000 2,000,000 -
Aug-12 2,200,000 -
Sep-12 2,550,000 4,750,000 4,750,000 -
Oct-12 2,600,000 -
Nov-12 2,750,000 5,350,000 5,350,000 -
Dec-12 2,800,000 -
Jan-13 2,900,000 5,700,000 5,700,000 -
Feb-13 3,000,000 3,000,000 3,000,000 -
Mar-13 2,900,000 2,900,000 2,900,000 -
Apr-13 2,700,000 2,700,000 2,700,000 -
May-13 2,600,000 -
Jun-13 2,500,000 5,100,000 5,100,000 -
Jul-13 2,425,000 -
Aug-13 2,175,000 5,600,000 5,600,000 -
Sep-13 2,000,100 2,000,100 2,000,100 -
Oct-13 1,900,000 1,900,000 1,900,000 -
Nov-13 1,800,000 1,800,000 1,800,000 -
Dec-13 1,700,000 -
Jan-14 1,500,000 3,200,000 3,200,000 -
Feb-14 1,300,000 7,193,000 7,120,734
Mar-14 1,250,000 7,193,000 2,550,000 2,550,000 7,120,734
Apr-14 1,100,000 7,193,000 7,120,734
May-14 900,000 7,193,000 2,000,000 2,000,000 7,120,734
Jun-14 875,000 7,193,000 7,120,734
Jul-14 850,000 7,193,000 1,725,000 1,725,000 7,120,734
Aug-14 650,000 7,193,000 7,120,734
Sep-14 500,000 7,193,000 1,150,000 1,150,000 7,120,734
Oct-14 500,000 7,193,000 500,000 500,000 7,120,734
Nov-14 300,000 7,193,000 300,000 300,000 7,120,734
Dec-14 -
Jan-15 -
Feb-15 -
Mar-15 -
Apr-15 -
Total 64,250,000 71,930,000 58,225,100 58,225,100 71,207,339
Enter number of months contractor will receive
financing prior to first PBP event: 5
Cumulative Contractor Cumulative
Expenditure @ Performance Based Cumulative PBP What If CLIN
Cost Payments Cash Flow Deliveries
0 0
1,000,000 - (200,000)
2,215,000 - (443,000)
3,615,000 - (723,000)
5,215,000 - (1,043,000)
6,967,000 - (1,393,400)
8,924,800 4,000,000 648,800
10,924,900 - (1,351,300)
13,024,900 6,000,000 (1,451,300)
15,224,900 - (3,651,300)
17,774,900 10,750,000 (1,451,300)
20,374,900 - (4,051,300)
23,124,900 16,100,000 (1,451,300)
25,924,900 - (4,251,300)
28,824,900 21,800,000 (1,451,300)
31,824,900 24,800,000 (1,451,300)
34,724,900 27,700,000 (1,451,300)
37,424,900 30,400,000 (1,451,300)
40,024,900 - (4,051,300)
42,524,900 35,500,000 (1,451,300)
44,949,900 - (3,876,300)
47,124,900 41,100,000 (451,300)
49,125,000 43,100,100 (451,300)
51,025,000 45,000,100 (451,300)
52,825,000 46,800,100 (451,300)
54,525,000 - (2,151,300)
56,025,000 50,000,100 (451,300)
57,325,000 - (1,751,300) 7,193,000
58,575,000 52,550,100 (451,300) 7,193,000
59,675,000 - (1,551,300) 7,193,000
60,575,000 54,550,100 (451,300) 7,193,000
61,450,000 - (1,326,300) 7,193,000
62,300,000 56,275,100 (451,300) 7,193,000
62,950,000 - (1,101,300) 7,193,000
63,450,000 57,425,100 (451,300) 7,193,000
63,950,000 57,925,100 (451,300) 7,193,000
64,250,000 58,225,100 (451,300) 7,193,000
- - -
- - -
- - -
- - -
- - -
20 36 20 10 TRUE TRUE 5
- - - -
Using The Model
Introduction
Performance Based Payments
and the FAR
Purpose of the Model
Win
Tie
Lose
How the Model Works
Performance Based Payment Buttons
Gov
Govt Break Even
Ktr Break Even
KTRBreak Even
Win/Win Solution
Approximate
Win/Win Solution
RESET
RESET PBPs
Important Note:
PBP Event "What-ifs"
Event Completed Late
Event Completed Early
Comparison Graph
Performance Based Payments offer a unique opportunity for a real "Win-
Win" financial arrangement for the Government and the contractor. This
opportunity presents itself due to the Government and the contractor
having differing views of the time-value of money. The "Win" for the
contractor is better cash flow resulting in a better Internal Rate of Return
(IRR) and Net Present Value (NPV) of the cash flows even at a reduced
contract price. The "Win" for the Government is a lower contract price that
more than offsets the additional financing costs of providing a better cash
flow to the contractor. This tool employs a discounted cash flow analysis to
determine the "Win-Win" financial solution.
Performance Based Payments (PBPs), like progress payments, are a
customary type of contract financing. In fact, per FAR 32.1001(a) PBPs
are the "preferred Government contracting method when the
contracting officer finds them practical and the contractor agrees to
their use ." Government financing is provided to assist contractors in
paying the costs they incur in the performance of a contract. PBPs are
financing payments, not incentive payments.
FAR 32.1004(b)(2) states that "Total performance-based payments
must : (i) reflect prudent contract financing provided only to the
extent needed for contract performance and (ii) not exceed 90
percent of the contract price if on a whole contract basis, or 90
percent of the delivery item price if on a delivery item basis." It is
important to note that the 90% of price is the upper limit, not a mandated
financing level. The proper total and timing of financing payments should
be determined in light of FAR 32. 1004(b)(3)(ii) which states that the
contracting officer must ensure that PBPs "are not expected to result in
an unreasonably low or negative level of contractor investment in the
contract." A link to FAR Part 32 is provided below.
FAR Part 32
The amount and timing of contract financing has a direct impact on the
cost to the Govt and the financial rate of return or Internal Rate of Return
(IRR) achieved by the contractor. The purpose of this model is to
demonstrate the financial impact to both the Govt and the Contractor of
using Performance Based Payments versus Progress Payments.
Using the Govt and Contractor perspectives of the time-value of money
as represented by the "Govt Cost of Money Rate" and the "KTR Hurdle
Rate" respectively, the model will calculate the "Final Cost to Govt" and
"KTR NPV @ Hurdle Rate" values. "KTR NPV @ Hurdle Rate" reflects
the Net Present Value (NPV) of the cash flows to the contractor when
discounted at the contractor's Hurdle Rate.
Clearly it is in the Govt's interest to minimize the "Final Cost to Govt" and
it is in the Contractor's interest to maximize its return in terms of the IRR
and the "KTR NPV @ Hurdle Rate". If the only variable is profit, these two
financial interests are directly and unalterably opposed to one another.
However, by introducing the variable of contract financing into the equation,
it is possible to achieve a true Win-Win financial deal because of the
differing perspectives of the time-value of money between the Govt and the
Contractor.
The Progress Payment scenario is used as the benchmark for
determining a Win/Win arrangement for several reasons. First, it is the
financing method most likely to be used if a Performance Based Payment
arrangement cannot be agreed to or is determined to be impractical.
Second, it is the financing method most commonly utilized between the
Government and Industry. And third, it is considered by industry to be a
low-risk form of financing. For these reasons, the Progress Payment
scenario is the right financial benchmark for a risk/reward analysis. The
objective profit rate utilized for the Progress Payment scenario should be
the profit rate that the Government would expect to negotiate if Progress
Payments are the financing method.
Since Win-Win should always be the goal of any business deal, the
model is set up to visually indicate when the PBP arrangement is a
financial "Win" for both parties. The PBP cells for "IRR (Internal Rate of
Return)", "Final Cost to Govt” and "KTR NPV @ Hurdle Rate" will change
color to reflect a Win, Tie or Lose status. If the cell is Red it means that
the PBP financial outcome is worse than what would be experienced using
Progress payments. If the cell is Yellow it means that PBP and Progress
Payments financial scenarios are the same. If the cell is Green it means
that the PBP financial outcome is better than the outcome under Progress
Payments. The goal should be to construct a win-win deal where all three
cells are Green.
The "Win-Win Analysis" worksheet uses a Discounted Cash Flow
analysis technique to measure the financial impact of various contract
financing scenarios. The model uses a customary progress payment rate
scenario as the baseline against which to measure the financial
cost/benefits of a PBP financing arrangement.
The "Final Cost to Govt" is the sole measure of financial impact from the
Govt point of view. The model displays two ways that Contractors look at
financial opportunities. The first is the "IRR (Internal Rate of Return)"
which represents the annual, pre-tax rate of return represented by the
contract cash flows. The second is the "KTR NPV @ Hurdle Rate" which
represents the present value of the cash flows when discounted at the
Contractor's hurdle rate. (See the "Assumption Explanations" worksheet
tab below for more discussion of the hurdle rate)
nt Buttons
Clicking the "Gov Break Even" button will cause the model to search for
the PBP Profit Rate that results in the "Final Cost to Govt" to be equal
under both Progress Payments and PBPs.
Clicking the "Ktr Break Even" button will cause the model to search for
the PBP Profit Rate that results in the "KTR NPV @ Hurdle Rate" to be
equal under both Progress Payments and PBPs.
Clicking the "Approximate Win/Win Solution" button will cause the model
to find the suggested Win/Win solution which will be the midpoint between
the two break-even values. However, this suggested Win-Win solution
might not be the optimal solution. The timing and amount of the PBP
events will determine if the optimal Win/Win solution lies closer to one of
the break-even values than the other. (See the "What if" discussion below
to see how to test the risk/benefit aspects of the Win/Win solution.)
Clicking the "RESET" button will restore the PBP event timing to the
profile as it existed before running "what ifs" on the slipping or accelerating
of PBP events. This button should be clicked after running "what-ifs"
pertaining to event slippage or acceleration.
The model checks to make sure that total PBP amounts do not exceed
90% of the contract price per the FAR. As the model searches for a PBP
profit rate solution and the PBP Price is reduced, downward adjustments, if
necessary, are made to the PBP amounts on the Win-Win Analysis
worksheet in order to remain at or below the 90% limit. It does this by
reducing the PBP event amounts proportionally. Therefore, in order to be
consistent with the final Win-Win solution, the contract values for the
events should reflect the reduced values.
A Win-Win deal should result in a better financial outcome for the
contractor with PBPs versus progress payments, when the contractor
performs well . It should not be structured so that the contractor is
financially better off regardless of how the contractor performs.
Contractors may consider PBPs to be inherently more risky than
progress payments. In PBPs, an event payment is made only upon
successful completion of that event. If an event is not completed by the
date anticipated, the payment is delayed accordingly. Conversely, if an
event is completed earlier than anticipated, the payment is accelerated
accordingly. This model will calculate the financial impact of delays and
acceleration in event completion and payment. This allows for an objective
versus notional discussion of risk.
The model allows for "what if" exercises regarding the early or late
completion of PBP events.
Double-clicking on a PBP event payment in the "PBP" column on the
"Win-Win Analysis" worksheet, will move that payment out one month
(down one row) and the financial impact of that slip will be displayed in the
"IRR (Internal Rate of Return)", "Final Cost to Govt" and "KTR NPV @
Hurdle Rate" values. The PBP cell will turn red to indicate that this is a
slipped event. This will allow both sides to determine how delays in event
completion financially impact the contractor and at what point event
slippages would yield a less rewarding financial outcome to the contractor
than progress payments. The outcome under PBPs is less advantageous
to the contractor when the "KTR NPV @ Hurdle Rate" block for PBPs
becomes red. The user should try several event slippage scenarios
involving later events as well as early events to gauge the true risk
sensitivity of the PBP arrangement.
Right-clicking on a PBP event payment in the "PBP" column on the "Win-
Win Analysis" worksheet will move the payment in one month (up one row).
The cell will turn green to indicate that the event is completed ahead of
schedule. This will have a positive financial impact on the contractor IRR
and "KTR NPV @ Hurdle Rate". You must select the cell before right-
clicking in order for this feature to work.
This ability to analyze the financial impact of various "what if" scenarios
will allow the user to objectively structure a PBP arrangement that
appropriately balances the risk and benefits of PBPs.
NOTE: When running a "what-if" on a PBP event, the cell color will change
to green or red based on whether the user has right-clicked or double-
clicked the cell. If an event is double-clicked the value will move down one
row and the cell will turn red. If that red cell is then right-clicked, the event
value will move up one row and the cell color will become green even
though the event has been restored to its original position. To reset the
PBP events to the original position and colors, click the "Reset" button.
This worksheet contains a graphic comparison of the financing cash
flows resulting from PBPs versus Progress Payments. Within the graph is
also a summary of the financial returns acheived under each scenario.
The chart will reflect the latest data contained on the Win-Win Analysis
sheet. Therefore, if you go to the Comparsion Chart sheet after running a
"what-if" on the slipping of an event or changing the lag times for instance,
the chart will reflect the changed data.
Assumptions
Progress Payment Rate
Government Cost of Money Rate (%)
Contractor Hurdle Rate (%)
Progress Payment Lag Days
PBP / DD250 Lag Days
PBP Cost Limitation
Changes to any assumption will result in an automatic recalculation
of the approximate Win-Win PBP profit solution.
Enter the progress payment rate: The rate to enter will be based on the
contractor's business status:
Large Business 80% Small
Business 90% Small
Disadvantaged Business 95%
This value represents the "Time-Value" of money to the Government.
The value entered should be the applicable Nominal Treasury Rate
contained in the OMB Circular A-94, Appendix C based on the period of
performance for the action. This percentage rate is used to calculate the
"Final Cost To The Govt". The link below will take you to the OMB A-94,
Appendix C.
http://www.whitehouse.gov/omb/circulars/a094/a94_appx-c.html
This value represents the "Time-Value" of money to the contractor.
Corporations establish a threshold, expressed as an annualized
percentage rate, that all financial projects must achieve in order to be
considered economically viable. This is often called the "Hurdle Rate" and
is based on the corporation's Weighted Average Cost of Capital (WACC).
The WACC is a complicated formula that takes into account the two ways
corporations raise money: Debt (borrowing) and Equity (selling stock)
based on the Capital Asset Pricing Model (CAPM). The WACC or Hurdle
Rate for each corporation will be different. The contractor's corporate
WACC is the rate that should be entered here. If the contractor cannot
provide its WACC calculation, the link below will take you to a website that
will calculate a WACC for most publicly traded corporations. You will need
to enter the corporation's stock symbol. The website identifies the CAPM
formula and the corporation's financial data relied upon to populate the
CAPM. However, there are two variables within the CAPM that are not
based on a corporation's financial statements: Market Return and Risk
Free Rate both of which affect the Return on Equity portion of the WACC.
The website identifies the values used for both variables but does not cite
the basis for these values. Therefore, the WACC provided by this website
should only be considered to be an approximation.
http://thatswacc.com/
The purpose of this entry is to recognize the period of time from when the
contractor spends cash to pay its contract costs and when it recovers
some or all of that cash via progress payments.
The value entered should be the average number of days between
expenditure of cash and receipt of a progress payment from the
Government. This value should take into consideration the difference
between "costs" and "cash expenditures". Specifically, under the "paid
cost rule" for subcontract costs, prime contractors no longer need to have
paid their subcontractors in order to include the subcontract "costs" in their
progress payment vouchers. Therefore, prime contractors should
experience little or no lag time for subcontract costs which, in turn, should
reduce the average overall lag time.
The model accounts for the approximate impact of lag times by
assuming a 30 day month. For example, a 30 day lag time will push
payments out one month (down 1 row on the spreadsheet). A 15 day lag
time would move 50% of the payment into the next month. A 10 day lag
time would move 33% of the payment into the next month.
The model will only accept values between 0 and 60 days.
The purpose of this entry is to recognize the period of time from when a
contractor submits a PBP request (upon sucessfully completing a PBP
event) and when it actually recevies payment from the Government. This
lag time is also considered to be applicable to DD250 payment requests.
This lag time has nothing to do with cost incurred. PBP lag time is
intended to reflect the time from when a contractor has completed a PBP
event's completion citeria and when it receives payment. Requests
properly rejected due to failure to complete all completion criteria should
not be considered in determing lag times. The cognizant administrative
contracting officer should be able to assist in identifying the average lag
times associated with PBPs.
This entry will affect the timing of the DD250 payments for both the
Progress Payment and PBP scenarios as well as PBP event payments.
The model will only accept values between 0 and 45 days.
The purpose of contract financing is to assist the contractor in the
payment of costs incurred on the contract. FAR 32.104 states that
contracting officers must "provide Government financing only to the extent
actually needed to ensure prompt and efficient performance". At any point
in the contract, the contractor can never "need" financing that exceeds total
cost incurred at that time. FAR 32.1004(b)(3)(ii) further states that
Performance Based Payments should not be expected to result in an
unreasonably low or negative level of contractor investment in the contract.
In situations where the Government does not have a high degree of
confidence in the expenditure profile, the Government has used a cost
limitation provision in Performance Based Payment contracts in order to
eliminate the possibility of a negative level of contractor investment from
occurring.
In the absence of a consistent expenditure history for the item being
procured, determining a reliable expenditure profile can be extremely
difficult. Therefore, both sides, while agreeing on the total cost of the
contract action, may have significant differences on how that cost will be
incurred over time. In fact, it is significantly more difficult to predict the
monthly expenditures than it is to predict the total cost of a contract.
When using progress payments, this difficulty is irrelevant since the
Government will pay a percentage of the actual cost incurred each month,
not the forecasted costs. However, when using PBPs, the accuracy of that
expenditure profile can have a significant effect on the financial outcome to
both parties.
Including PBP Cost Limitation language in the contract may be the
simplest and most equitable solution to this problem. If a PBP cost
limitation is used, cumulative payments to the contractor will never be
allowed to exceed cumulative cost incurred. This can allow the
Government to be more flexible relative to the expenditure profile and the
resulting PBP event values since the possibility of a "negative contractor
investment" is eliminated. While this will preclude an "advance payment"
or windfall cash flow scenario to the contractor, it does not relieve the
Government from analyzing the expenditure profile and event values for
reasonableness. It does however provide the Government considerably
more flexibility in this area.
While the use of a PBP cost limitation eliminates the possibility of a
"negative contractor investment", or "advance payments", it is important for
the user to understand how such a situation can arise when a PBP cost
limitation is not included in the contract. The following scenarios are likely
to result in cumulative PBPs exceeding cumulative cost incurred:
1. The PBP payment schedule was based on a projected expenditure
profile that was not accurate. Specifically, if the expenditure profile
assumed significant costs would be incurred in the early phase of the
contract (front loaded) but actual cost turned out to be more evenly spread
or back loaded, PBP event payments in the early phase of the contract
could significantly exceed actual cost.
2. The assumed completion dates for PBP events were not accurate.
Specifically, if in generating the PBP schedule, PBP event completion
dates are assumed to happen later in the contract performance than is
warranted based on the master program schedule, simply performing the
events "on time" could result in cumulative payments that significantly
exceed actual cost.
3. The contractor may underrun the cost of performing the contract.
Depending on the size of the underrun and the level of PBPs provided (%
of total price), this is likely to result in payments that exceed actual cost at
some point in contract performance. The contractor's view of this situation
is that payments in excess of cost represents the payment of the additional
profit that the contractor's is "earning" via the underrun. The contractor
may describe this as an added incentive to underrun the contract.
However, PBPs are financing, not incentive payments. Additional profit
earned through cost underruns are properly paid to the contractor at the
time of DD 250, just as it is under a contract that uses progress payment
financing.
Contractor
Expenditure @ CLIN Deliveries Performance
Month Cost @ Price Based Payments What if PBPs PBP CLIN Price
Jul-09 1,000,000 -
Aug-09 1,215,000 2,215,000 2,215,000 -
Sep-09 1,400,000 -
Oct-09 1,600,000 3,000,000 3,000,000 -
Nov-09 1,752,000 -
Dec-09 1,957,800 -
Jan-10 2,000,100 5,300,000 5,300,000 -
Feb-10 2,100,000 -
Mar-10 2,200,000 4,300,000 4,300,000 -
Apr-10 2,550,000 -
May-10 2,600,000 5,150,000 5,150,000 -
Jun-10 2,750,000 -
Jul-10 2,800,000 -
Aug-10 2,900,000 8,400,000 8,400,000 -
Sep-10 3,000,000 -
Oct-10 2,900,000 -
Nov-10 2,700,000 8,600,000 8,600,000 -
Dec-10 2,600,000 -
Jan-11 2,500,000 -
Feb-11 2,425,000 7,525,000 7,525,000 -
Mar-11 2,175,000 -
Apr-11 2,000,100 -
May-11 1,900,000 6,075,000 6,075,000 -
Jun-11 1,800,000 1,800,000 1,800,000 -
Jul-11 1,700,000 -
Aug-11 1,500,000 3,200,000 3,200,000 -
Sep-11 1,300,000 -
Oct-11 1,250,000 2,550,000 2,550,000 -
Nov-11 1,100,000 -
Dec-11 900,000 -
Jan-12 875,000 2,875,000 2,875,000 -
Feb-12 850,000 -
Mar-12 650,000 17,668,750 17,490,618
Apr-12 500,000 17,668,750 2,000,000 2,000,000 17,490,618
May-12 500,000 17,668,750 17,490,618
Jun-12 300,000 17,668,750 17,490,618
Jul-12 -
Aug-12 -
Sep-12 -
Oct-12 -
Nov-12 -
Dec-12 -
Jan-13 -
Feb-13 -
Mar-13 -
Apr-13 -
May-13 -
Jun-13 -
Jul-13 -
Aug-13 -
Sep-13 -
Oct-13 -
Nov-13 -
Dec-13 -
Jan-14 -
Feb-14 -
Mar-14 -
Apr-14 -
May-14 -
Jun-14 -
Jul-14 -
Aug-14 -
Sep-14 -
Oct-14 -
Nov-14 -
Dec-14 -
Jan-15 -
Feb-15 -
Mar-15 -
Apr-15 -
May-15 -
Jun-15 -
Jul-15 -
Aug-15 -
Sep-15 -
Oct-15 -
Nov-15 -
Dec-15 -
Jan-16 -
Feb-16 -
Mar-16 -
Apr-16 -
May-16 -
Jun-16 -
Jul-16 -
Aug-16 -
Sep-16 -
Oct-16 -
Nov-16 -
Dec-16 -
Jan-17 -
Feb-17 -
Mar-17 -
Apr-17 -
May-17 -
Jun-17 -
Jul-17 -
Aug-17 -
Sep-17 -
Oct-17 -
Nov-17 -
Dec-17 -
Jan-18 -
Feb-18 -
Mar-18 -
Apr-18 -
May-18 -
Jun-18 -
Jul-18 -
Aug-18 -
Sep-18 -
Oct-18 -
Nov-18 -
Dec-18 -
Jan-19 -
Feb-19 -
Mar-19 -
Apr-19 -
May-19 -
Jun-19 -
64,250,000 70,675,000 62,990,000 69,962,471
Cumulative
Contractor Cumulative
Expenditure @ Performance Cumulative PBP
Cost Based Payments Cash Flow
1,000,000 - (1,000,000)
2,215,000 2,215,000 -
3,615,000 - (1,400,000)
5,215,000 5,215,000 -
6,967,000 - (1,752,000)
8,924,800 - (3,709,800)
10,924,900 10,515,000 (409,900)
13,024,900 - (2,509,900)
15,224,900 14,815,000 (409,900)
17,774,900 - (2,959,900)
20,374,900 19,965,000 (409,900)
23,124,900 - (3,159,900)
25,924,900 - (5,959,900)
28,824,900 28,365,000 (459,900)
31,824,900 - (3,459,900)
34,724,900 - (6,359,900)
37,424,900 36,965,000 (459,900)
40,024,900 - (3,059,900)
42,524,900 - (5,559,900)
44,949,900 44,490,000 (459,900)
47,124,900 - (2,634,900)
49,125,000 - (4,635,000)
51,025,000 50,565,000 (460,000)
52,825,000 52,365,000 (460,000)
54,525,000 - (2,160,000)
56,025,000 55,565,000 (460,000)
57,325,000 - (1,760,000)
58,575,000 58,115,000 (460,000)
59,675,000 - (1,560,000)
60,575,000 - (2,460,000)
61,450,000 60,990,000 (460,000)
62,300,000 - (1,310,000)
62,950,000 - 15,708,750
63,450,000 62,990,000 34,877,500
63,950,000 - 52,046,250
64,250,000 - 69,415,000
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -
- - -