VIEWS: 24 PAGES: 66 POSTED ON: 7/26/2011
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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -