DOD PBP Tool _Version 2.0 - Excel 97-2003

Document Sample
DOD PBP Tool _Version 2.0 - Excel 97-2003 Powered By Docstoc
					Version 2.0 reflects the following changes:

1. In Version 2.0 the suggested win-win solution is set at the midpoint of the two break-even positions.
   In Version 1.0, the model performed "what-if" sensitivity analysis regarding event slippages in determining the suggested wi
   Users are strongly encouraged to perform "what-if" sensitivity on PBP event completion to determine the optimum win-win s
   "What-if" sensitivity analysis is explained in the Using the Model sheet.

2. On the Contract Summary sheet, in cell C14, the user will enter the objective profit dollars. The model will calculate the pro

3. The Data Input sheet displays all rows until the "Show / Hide Rows" button (which is in cell B127) is clicked.
   Clicking the "Show / Hide Rows" button causes the sheet to toggle between displaying all rows or just the rows used.

4. Formulas in the DD250 columns on the Win-Win Analysis sheet now reference DD250 lag times for clarity.

5. The formula for liquidated progress payments was corrected for accuracy but change to win-win results will be negligible.

6. Additional error checking and messages have been incorporated.
mining the suggested win-win solution.
e the optimum win-win solution.


del will calculate the profit rate.


st the rows used.



ults will be negligible.
DOD Performance Based Payment Analysis Tool (Version 2.0 - Excel 97-2003)
                       See Version Notes sheet for explanation of changes from Version 1.0

Steps:




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.
A "Data Input Sample" sheet is provided to show how data is to be entered on the "Data Input" sheet.


A "Data Input Sample" sheet is provided to show how data is to be entered on the "Data Input" sheet.
DOD Performance Based Payment Analysis Tool (Version 2.0 - Excel 97-2003)
                       See Version Notes sheet for explanation of changes from Version 1.0

Steps:

1. Perform a weighted guidelines analysis to determine a fair and reasonable profit rate assuming
progress payments , not performance based payments, will be used. Progress payments provide the
baseline cash-flow scenario for the calculation of a win-win Performance Based Payment arrangement.

2. On the "Contract Summary" sheet, enter the appropriate values in the shaded cells. The profit rate
from step 1 is entered in cell D14.

3. On the "Data Input" sheet, in cell A5, enter the first month the contractor will incur cost on the contract
(i.e. 5/31/2011). The remainder of the cells below that will be automatically populated with monthly date
values. The model will accomodate a period of performance of up to ten years. The 'Show / Hide Rows'
button at cell B127 can be used to toggle between showing all rows or just those rows used.

4. On the "Data Input" sheet, enter the monthly expenditure profile (forecast of contract costs to be
incurred by month) in Column B. Expenditures must appear in the first month and will normally occur in
every month through the final, priced CLIN delivery. The sum of the monthly expenditures must equal the
total cost entered in cell C12 on the "Contract Summary" sheet.
5. On the "Data Input" sheet enter the CLIN Prices (assuming progress payments ) in the month of
scheduled delivery in Column C. If more than one CLIN is scheduled for delivery in a month, the value
entered should be the sum of those CLIN prices. The sum of the CLIN prices must equal the total price
reflected in cell C16 on the "Contract Summary" sheet .

6. On the "Data Input" sheet, in Cloumn D, enter the value of each performance based payment event in the
month the event is scheduled to be completed. If more than one PBP event is scheduled for completion in
a month, the value entered should be the sum of those PBP event values. Note that the sum of the
Performance Based Payments cannot exceed 90% of the contract price.

7. The information on the "Using the Model" and "Assumption Explanations" sheets will explain the
features of the "Win-Win Analysis" sheet. It is recommended that you print the information on this
instruction sheet and those sheets to use as a reference when using this tool.


Important Notes:

Macros nust be enabled in order for the model to work.

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
doing so is disabled. Copy and Paste can be accomplished by using the menu toolbar or Ctrl-C and Ctrl-V
methods.
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:

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.


The "Win-Win Analysis" sheet will show error messages (#Div/0!, #Num!) until all data has been entered on
the "Contract Summary" and "Data Input" sheets.
A "Data Input Sample" sheet is provided to show how data is to be entered on the "Data Input" sheet.


A "Data Input Sample" sheet is provided to show how data is to be entered on the "Data Input" sheet.
97-2003)
                                                               Program Name / Contract Action Description



                                            Position                                                             Position
                                    Using Progress Payments                                                     Using PBP



Profit Bearing Cost                                                                                         $               -

Cost of Money(1)                                                                                            $               -

Total Cost                         $                       -                                                $               -

Profit                                                             #DIV/0!    Profit Rate(2)                $               -     1.00% Profit Rate
                                                                   #DIV/0!                                                      #DIV/0!
Total Price                        $                       -                                                $               -


(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
                                                                                                                     Govt Break Even
                                                          Assumptions :
          Progress Payment Rate                              80%       Progress Payment Lag Days         30          KTR Break Even
          Govt Cost of Money Rate (%)                       3.13%      PBP / DD 250 Lag Days             30
                                                                                                                      Approximate
          Contractor Hurdle Rate (%)                        8.27%      PBP Cost Limitation                           Win/Win Solution

                                                                                                                        RESET PBPs


                                          With Progress Payments                                                    With PBP


          Total Price                                                      #DIV/0!                 Total Price
          Profit Rate                                                      #DIV/0!                 Profit Rate
          IRR (Internal Rate of Return)                                    #DIV/0!                 IRR (Internal Rate of Return)
          Final Cost to Govt                                               #DIV/0!                 Final Cost to Govt
          KTR NPV @ Hurdle Rate                                            #DIV/0!                 KTR NPV @ Hurdle Rate
                                                                                                   PBP % of Price

                                                                          Prog Pay      Prog Pay
                         KTR Cash                                           Cash       Cum Cash
                        Expenditure             Prog Pay    DD 250          Flow         Flow           PBP       DD 250
May-11                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Jun-11                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
 Jul-11                                   -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Aug-11                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Sep-11                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Oct-11                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Nov-11                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Dec-11                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Jan-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Feb-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Mar-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Apr-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
May-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Jun-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
 Jul-12                                   -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Aug-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Sep-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Oct-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Nov-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Dec-12                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Jan-13                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Feb-13                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Mar-13                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Apr-13                                    -           -     #DIV/0!        #DIV/0!       #DIV/0!      #DIV/0!     #DIV/0!
Total                                     -           -     #DIV/0!        #DIV/0!                    #DIV/0!     #DIV/0!
         Govt Break Even

         KTR Break Even

          Approximate
         Win/Win Solution

           RESET PBPs


        With PBP


                            #DIV/0!
                              1.00%
rnal Rate of Return)         ERROR
                            #DIV/0!
V @ Hurdle Rate             #DIV/0!
                            #DIV/0!

                  PBP        PBP
                  Cash   Cum Cash
                  Flow     Flow                 Cum Cost       Cum Progress Pay       Cum PBP
               #DIV/0!      #DIV/0!   May-11               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Jun-11               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!    Jul-11              -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Aug-11               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Sep-11               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Oct-11               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Nov-11               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Dec-11               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Jan-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Feb-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Mar-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Apr-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   May-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Jun-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!    Jul-12              -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Aug-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Sep-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Oct-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Nov-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Dec-12               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Jan-13               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Feb-13               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Mar-13               -                      -        #DIV/0!
               #DIV/0!      #DIV/0!   Apr-13               -                      -        #DIV/0!
               #DIV/0!
Unliquidated        Liquidated    Unliquidated       Liquidated      Cost To Government
Progress Pay       Progress Pay       PBP               PBP       Prog Pay           PBP


               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
               -     #DIV/0!                     -    #DIV/0!                -             -
                     #DIV/0!                          #DIV/0!                -             -
Prog Pay Lag   PBP Lag   DD250 Lag                          Cum
                                              Cum Expenditures Progress Payments
     0            0          0       Jan-00          100,000           80,000
     1            1          1       Feb-00          200,000          160,000
     0            0          0       Mar-00          300,000          240,000
                                     Apr-00          400,000          320,000
                                     May-00          500,000          400,000
Cum PBPs      PBPs       Cum PBPs
 #DIV/0!             -          -   Govt_NPV_Diff    #DIV/0!
    200,000          -          -   KTR_NPV_Diff     #DIV/0!
    250,000          -          -   PP DD250 Formula #DIV/0!
    400,000          -          -   Cost_Limit                 0
    400,000          -          -   PBP_Cap_Adj      #DIV/0!       #DIV/0!
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                     -          -
                                       Financing Cash Flow Comparison

$1


$1                         Financial Returns

                       Prog Pay                   PBP
$1
     Profit             12.00%                      12.00%

$1   IRR                188.05%                    138.65%

     NPV           $ 6,785,678            $ 6,681,502
$1


$1


$0


$0


$0


$0


$0
                                                                       Jun-11




                                                                                Jul-11
              May-11




                            Cum Cost   Cum Progress Pay      Cum PBP
            Contractor Expenditure   CLIN Deliveries @   Performance Based
Month              @ Cost                  Price              Payments       What if PBPs


  May-11
  Jun-11
   Jul-11
  Aug-11
  Sep-11
  Oct-11
  Nov-11
  Dec-11
  Jan-12
  Feb-12
  Mar-12
  Apr-12
  May-12
  Jun-12
   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
 Jul-19
Aug-19
Sep-19
 Oct-19
Nov-19
Dec-19
Jan-20
Feb-20
Mar-20
 Apr-20
May-20
Jun-20
 Jul-20
Aug-20
Sep-20
 Oct-20
Nov-20
Dec-20
Jan-21
Feb-21
Mar-21
 Apr-21
Total     -   -   -   -
                 Cumulative
                  Contractor           Cumulative
                 Expenditure       Performance Based   Cumulative PBP   What If CLIN
PBP CLIN Price     @ Cost               Payments         Cash Flow       Deliveries
                                                                                   0
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
   #DIV/0!                     -                 -                  -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!   -   -   -
#DIV/0!
0   0   0   0
#DIV/0!   -   -
-
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 assumptions 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 model will only accept values between 2%
and 10%. 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 the WACC for most publicly traded corporations. You will
need to enter the corporation's stock symbol. The website identifies the
CAPM formula and the corporations 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.



                           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 new "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 or DD250 payment request and when it actually
recevies payment from the Government. This lag time has nothing to do
with cost incurred.

  This entry will affect the timing of the DD250 payments for both the
Progress Payment and PBP scenarios as well at the 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
timing and magnitude of 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.

     To better understand this situation, enter PBP event values that cause
the "Cumulative Performance Based Payments", at an early point in
performance, to significantly exceed the "Cumulative Contractor
Expenditures @ Cost" on the "Data Input" worksheet. On the "Win-Win
Analysis" worksheet, you will see that the result is added cost to the
Government and a significant benefit to the contractor. Clearly, FAR
requirements and sound business practice dictate that the Government
should not intentionally provide financing payments that exceed cost
incurred. Given the difficulty in accurately predicting expenditures over the
life of the contract, it is important for the Government to protect its interests
in a way that is also fair to the contractor.


   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 PBPs 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 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                                                               #DIV/0!
  Aug-09         1,215,000                           2,215,000            2,215,000      #DIV/0!
  Sep-09         1,400,000                                                               #DIV/0!
  Oct-09         1,600,000                           3,000,000            3,000,000      #DIV/0!
  Nov-09         1,752,000                                                               #DIV/0!
  Dec-09         1,957,800                                                               #DIV/0!
  Jan-10         2,000,100                           5,300,000            5,300,000      #DIV/0!
  Feb-10         2,100,000                                                               #DIV/0!
  Mar-10         2,200,000                           4,300,000            4,300,000      #DIV/0!
  Apr-10         2,550,000                                                               #DIV/0!
  May-10         2,600,000                           5,150,000            5,150,000      #DIV/0!
  Jun-10         2,750,000                                                               #DIV/0!
   Jul-10        2,800,000                                                               #DIV/0!
  Aug-10         2,900,000                           8,400,000            8,400,000      #DIV/0!
  Sep-10         3,000,000                                                               #DIV/0!
  Oct-10         2,900,000                                                               #DIV/0!
  Nov-10         2,700,000                           8,600,000            8,600,000      #DIV/0!
  Dec-10         2,600,000                                                               #DIV/0!
  Jan-11         2,500,000                                                               #DIV/0!
  Feb-11         2,425,000                           7,525,000            7,525,000      #DIV/0!
  Mar-11         2,175,000                                                               #DIV/0!
  Apr-11         2,000,100                                                               #DIV/0!
  May-11         1,900,000                           6,075,000            6,075,000      #DIV/0!
  Jun-11         1,800,000                           1,800,000            1,800,000      #DIV/0!
   Jul-11        1,700,000                                                               #DIV/0!
  Aug-11         1,500,000                           3,200,000            3,200,000      #DIV/0!
  Sep-11         1,300,000                                                               #DIV/0!
  Oct-11         1,250,000                           2,550,000            2,550,000      #DIV/0!
  Nov-11         1,100,000                                                               #DIV/0!
  Dec-11           900,000                                                               #DIV/0!
  Jan-12           875,000                           2,875,000            2,875,000      #DIV/0!
  Feb-12           850,000                                                               #DIV/0!
  Mar-12           650,000        17,668,750                                             #DIV/0!
  Apr-12           500,000        17,668,750         2,000,000            2,000,000      #DIV/0!
  May-12           500,000        17,668,750                                             #DIV/0!
  Jun-12           300,000        17,668,750                                             #DIV/0!
   Jul-12                                                                                #DIV/0!
  Aug-12                                                                                 #DIV/0!
  Sep-12                                                                                 #DIV/0!
  Oct-12                                                                                 #DIV/0!
  Nov-12                                                                                 #DIV/0!
  Dec-12                                                                                 #DIV/0!
  Jan-13                                                                                 #DIV/0!
  Feb-13                                                                                 #DIV/0!
  Mar-13                                                                                 #DIV/0!
Apr-13    #DIV/0!
May-13    #DIV/0!
Jun-13    #DIV/0!
 Jul-13   #DIV/0!
Aug-13    #DIV/0!
Sep-13    #DIV/0!
Oct-13    #DIV/0!
Nov-13    #DIV/0!
Dec-13    #DIV/0!
Jan-14    #DIV/0!
Feb-14    #DIV/0!
Mar-14    #DIV/0!
Apr-14    #DIV/0!
May-14    #DIV/0!
Jun-14    #DIV/0!
 Jul-14   #DIV/0!
Aug-14    #DIV/0!
Sep-14    #DIV/0!
Oct-14    #DIV/0!
Nov-14    #DIV/0!
Dec-14    #DIV/0!
Jan-15    #DIV/0!
Feb-15    #DIV/0!
Mar-15    #DIV/0!
Apr-15    #DIV/0!
May-15    #DIV/0!
Jun-15    #DIV/0!
 Jul-15   #DIV/0!
Aug-15    #DIV/0!
Sep-15    #DIV/0!
Oct-15    #DIV/0!
Nov-15    #DIV/0!
Dec-15    #DIV/0!
Jan-16    #DIV/0!
Feb-16    #DIV/0!
Mar-16    #DIV/0!
Apr-16    #DIV/0!
May-16    #DIV/0!
Jun-16    #DIV/0!
 Jul-16   #DIV/0!
Aug-16    #DIV/0!
Sep-16    #DIV/0!
Oct-16    #DIV/0!
Nov-16    #DIV/0!
Dec-16    #DIV/0!
Jan-17    #DIV/0!
Feb-17    #DIV/0!
Mar-17    #DIV/0!
Apr-17    #DIV/0!
May-17    #DIV/0!
Jun-17    #DIV/0!
 Jul-17   #DIV/0!
Aug-17                                           #DIV/0!
Sep-17                                           #DIV/0!
Oct-17                                           #DIV/0!
Nov-17                                           #DIV/0!
Dec-17                                           #DIV/0!
Jan-18                                           #DIV/0!
Feb-18                                           #DIV/0!
Mar-18                                           #DIV/0!
Apr-18                                           #DIV/0!
May-18                                           #DIV/0!
Jun-18                                           #DIV/0!
 Jul-18                                          #DIV/0!
Aug-18                                           #DIV/0!
Sep-18                                           #DIV/0!
Oct-18                                           #DIV/0!
Nov-18                                           #DIV/0!
Dec-18                                           #DIV/0!
Jan-19                                           #DIV/0!
Feb-19                                           #DIV/0!
Mar-19                                           #DIV/0!
Apr-19                                           #DIV/0!
May-19                                           #DIV/0!
Jun-19                                           #DIV/0!
          64,250,000   70,675,000   62,990,000   #DIV/0!
  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
           -              -                 -
           -              -                 -
           -              -                 -
           -              -                 -
           -              -                 -
           -              -                 -
           -              -                 -
           -              -                 -
           -              -                 -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -
-   -   -

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:24
posted:7/26/2011
language:English
pages:66