Docstoc

Welcome Electronic Business Proposal Spreadsheets Calculating and Structuring Your Potential Costs • Please help yourself to a handout ELECTRONIC BUSINESS

Document Sample
Welcome Electronic Business Proposal Spreadsheets Calculating and Structuring Your Potential Costs • Please help yourself to a handout ELECTRONIC BUSINESS Powered By Docstoc
					        Welcome!
Electronic Business Proposal
       Spreadsheets

Calculating and Structuring Your
         Potential Costs
   • Please help yourself to a
   handout.
   ELECTRONIC
BUSINESS PROPOSAL
  SPREADSHEETS
              Introductions
• Cindi L. Brown

 Accountant, Special Reviews Branch
 Division of Financial Advisory Services (DFAS),
 Office of Contracts Management, OD, NIH
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.
                 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.
                           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.)
     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.
How to utilize the electronic
  spreadsheet efficiently and
      effectively by:


   • Understanding how the
       spreadsheet is designed.

   • Understanding how to use it.
         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
  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
                      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
                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.
             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.
                     Direct Labor

                   What is your policy for
                 charging and recording time?

• Percent of Effort             • Number of Hours
• Usually for Universities      • Usually For-Profit
  and Non-profits.                entities.


   • Current base salary /hourly rate
      Column D
      – Use current payroll document      File
   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
         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
      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
     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.
            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.
                  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
       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.
           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
       Fringe Benefit Rates




• Generally internal accountants or
  independent CPAs prepare indirect
  rate proposals.
                Other Direct Costs
•      Materials            • Totals for these cost
•      Travel                 elements are carried
•      Equipment              forward to the
                              Summary sheet.
•      Consultants
•      Other Direct Costs
                            • No escalation is built
•      Patient Care Costs
                              into the formulas.

file
           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.
     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
           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.
          ADVANTAGES
• Significantly reduces math errors.

• Easier to do a math check…just check
  the formulas.

• Provides a detailed breakdown of cost
  elements
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.
               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.
       Disadvantages, but not
             Obstacles



• Contractor protects sheets and
  /or cells with a password!
       SOLUTION
• Call the contractor and ask for
  the password.

• To unprotect in Excel: click on
  tools, protections, unprotect, &
  type in password.
Disadvantages, but not Obstacles
• Contractor uses
  complex
  formulas or uses
  erroneous
  formulas.
             SOLUTIONS
• Ask contractor to explain rationale behind
  the formulas.

• OR

• Cut and paste unit costs into a blank
  spreadsheet file and analyze differences.
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
    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
Questions?????

				
DOCUMENT INFO
Description: Simple Business Proposal document sample