welcome electronic business proposal spreadsheets calculating and structuring your potential costs

1: Welcome!Electronic Business Proposal SpreadsheetsCalculating and Structuring Your Potential Costs Please help yourself to a handout.

3: Introductions Cindi L. Brown Accountant, Special Reviews Branch Division of Financial Advisory Services (DFAS), Office of Contracts Management, OD, NIH

4: What can you expect today from this training??? Where to find the electronic spreadsheet. When to use the electronic spreadsheet. How to utilize the electronic spreadsheet efficiently and effectively by: - Understanding how the file is designed. - Understanding how to use it. Advantages and disadvantages Answers to your questions�anytime.

5: Caveat There will always be more than one way to create and modify spreadsheets, so do what works for you. This spreadsheet is the collaboration of 2 NIH accountants and 3 NIH contracting officials. We attempted to keep it simple, yet effective. This spreadsheet is designed to be modified.

6: Where to find the electronic file. DFAS Other Resources &Links Electronic Contract Business Proposal http://ocm.od.nih.gov/dfas/resources.htm NIH RFP Directory Home Page http://www4.od.nih.gov/ocm/contracts/rfps/mainpage.htm Forms, Formats, & Attachments Business Proposal Cost Information Cost Proposal (located at bottom of page) filename = spshexcl (This file was created in Excel.)

7: When to use the electronic spreadsheet. When the RFP requests you to submit an electronic copy of your business proposal (on diskette) in addition to the hard copies. To assist you in calculating and structuring your proposal costs.

8: How to utilize the electronic spreadsheet efficiently and effectively by: Understanding how the spreadsheet is designed. Understanding how to use it.

9: Instruction Page This is the first worksheet of the file. It contains basic instructions for the subsequent worksheets. If applicable, specific instructions are included on the individual worksheets to add clarification or to emphasize a point. file

10: Summary of Proposed Costs Totals from all cost elements are automatically carried forward to this page. Set up for 7 periods. Start date of contract is in cell c5 and the formulas automatically calculate 12 month periods. Adjust the formulas for periods less than 12 months. file

11: Indirect Costs Use the rate(s) in your negotiated agreement. If you do not have negotiated rates, you can utilize the blank spreadsheet in this file for your indirect rate proposal. If you have questions regarding how to calculate indirect rates, please visit our internet address for a simple example and definition: http://www4.od.nih.gov/ocm/dfas/idcsubmission.htm

12: Indirect Costs This spreadsheet demonstrates a 4 tier structure: fringe benefits, overhead, G&A, and other rate. file IMPORTANT: use the rate structure that is applicable for your company.

13: Indirect Costs Indirect rates are located on the Summary sheet starting in cell B23. file Starting in cell C23, check the formula to ensure that a correct base is used.

14: Direct Labor What is your policy for charging and recording time? Percent of Effort Usually for Universities and Non-profits. Number of Hours Usually For-Profit entities.

15: Adjusted base salary Column E 4 components : 1. Annual salary/rate 2. Annual Increase Factor (cell E47) 3. Annual Increase Date (cell E50) - What is your increase policy? Note: The formulas do not reference the direct work year cell E47 or annual increase date cell E51. 4. Number of Months at Next Annual Increase (cell E52) file

16: Adjusted base salary Number of Months at Next Annual Increase For example: Start date of the contract = 6/1/99 Contractor increase date = 7/1/99 = 11 months at next annual increase. (7/1/99 to 5/31/2000) Insert 11 in cell E52. file

17: Adjusted base salary Column E So the formula in the adjusted base salary column, prorates the annual salary/rate For example: $50,000 x 1/12 = $ 4,167 $50,000 x 1.03 x 11/12 = 47,208 $51,375 file

18: Increases Prior to Start Date!!! Modify the formula in the annual salary column (D) file For example��� If the start date of the contract is 9/1/99 and the increase date is 7/1/99, the formula would be =round (($50,000 x 1.03),0). Then the number of months at next increase would be 3 and the increase date would be 7/1/2000.

19: Direct Labor Make sure to manually change the number of months columns if the periods are not 12 months. file The to- be-hired or to-be-named individuals are not eligible for increases in the first year. Increases in subsequent years are effective the first month. See formulas in column N.

20: Direct Labor For individuals subject to the $125,900 ceiling: Override the formula in the adjusted salary column by typing $125,900. AND Remove (1+E47) from the formulas in all subsequent years. This is the cell reference for annual escalation. The 1999 executive schedule is located: http://www.opm.gov/oca/99tables/Execses/html/99excsch.htm file

21: Fringe benefits Fringe benefit percents are entered starting in column I for period 1 and costs are calculated in column J. file Use your rate(s) from your negotiated rate agreement.

22: Fringe benefits If you do not have a negotiated fringe benefit rate, you can use the fringe benefit sheet to calculate a rate. File If you have questions regarding how to calculate fringe benefit rates, please visit our internet address: http://www4.od.nih.gov/ocm/dfas/idcsubmission.htm

23: Fringe Benefit Rates Generally internal accountants or independent CPAs prepare indirect rate proposals.

24: Other Direct Costs Materials Travel Equipment Consultants Other Direct Costs Patient Care Costs file Totals for these cost elements are carried forward to the Summary sheet. No escalation is built into the formulas.

25: Other Direct Costs Utilize source documents such as current paid invoices, vendor quotes, or purchase orders to support unit costs. Consult the appropriate technical personnel or RFP for recommended number of units.

26: Do a math check! On all cost elements. Check the formulas. Are they correct? Have they been overridden with a value? Are your worksheets linked? We are only human!! 1+1 = 3

27: ADVANTAGES Eliminates a substantial amount of questions to the contractor (ie. How did you come up with that amount?) Saves time; that is, changes are automatically recalculated and carried forward to the summary page.

28: ADVANTAGES Significantly reduces math errors. Easier to do a math check�just check the formulas. Provides a detailed breakdown of cost elements

29: Disadvantages, but not Obstacles The contractor overrides formulas and inserts values. This destroys the ultimate purpose of utilizing the electronic spreadsheet; that is, the sheets are not linked.

30: SOLUTION Ask contractor to send you the version of the file which contains formulas. Or Add formulas yourself. Or Cut and paste unit costs and # of units into a blank file which has formulas.

31: Disadvantages, but not Obstacles Contractor protects sheets and /or cells with a password!

32: SOLUTION Call the contractor and ask for the password. To unprotect in Excel: click on tools, protections, unprotect, & type in password.

33: Disadvantages, but not Obstacles Contractor uses complex formulas or uses erroneous formulas.

34: SOLUTIONS Ask contractor to explain rationale behind the formulas. OR Cut and paste unit costs into a blank spreadsheet file and analyze differences.

35: Determine Financial Capability For Universities, Non-profits, and possibly For-Profit entities... Review the A-133 audit reports for findings regarding going concern For -Profits entities not subject to the A-133 Audit requirements� Calculate ratios to determine liquidity Review financial statements Obtain line of credit information Review Dun & Bradstreet reports

36: Accounting System Review Review the A-133 audit reports for findings regarding possible system deficiencies If A-133 Audit report is not applicable� Contact cognizant audit agency for comments regarding the adequacy of the contractor�s accounting system

37: Questions?????