Overhead Spreadsheet

Document Sample
Overhead Spreadsheet Powered By Docstoc
					                                        METROPOLITAN PLANNING ORGANIZATION
                          STATEMENT OF DIRECT LABOR, FRINGE BENEFITS, AND GENERAL OVERHEAD
                                           CALCULATION OF OVERHEAD RATE
                                                      DL METHOD

                This spreadsheet is used to calculate the fringe benefit and indirect cost overhead rates for Metropolitan Planning
                Organizations.

                The DL Method calculates the overhead rates as follows:

                Fringe Benefit Cost Rate
                                                Numerator                                              Total Fringe Benefits Costs
                                               Denominator                                                 Total Labor Costs

                Indirect Cost Rate
                                                Numerator                                                 Total Indirect Costs
                                               Denominator                                              Total Direct Labor Costs

                Definitions
                         Total Fringe Benefits Costs
                              - the allowable employee fringe benefit costs related to direct and indirect salaries and wages. This includes
                              paid time off for employees such as holidays, vacation and sick days, as well as other fringe benefits costs such
                              as medical insurance, retirement, workers compensation, etc.
                         Total Labor Costs
                              - these costs include the salaries and hourly wages for direct and indirect labor but do not include the associated
                              fringe benefit costs.


                         Total Indirect Costs
                              - the costs incurred for a common or joint purpose benefitting more than one cost center. These costs include
                              allowable indirect salaries and wages, the fringe benefits for these indirect salaries and wages, and other
                              allowable indirect costs.
                         Total Direct Labor Costs
                              - these costs include the salaries and hourly wages for direct labor allocable to a cost center but do not include
                              the associated fringe benefit costs.


                Template Instructions

                   1     Update the titles section of the spreadsheet to reflect the name of the organization.

                   2     Update the titles section of the spreadsheet to reflect the most recently prior odd or even fiscal year.
                         i.e.: If you were proposing estimated rates for FY 2006 you would enter the beginning and ending dates for FY 2004.


                   3     Update the column headers to reflect the correct fiscal years.
                         i.e.: If you were proposing estimated rates for FY 2006 this would be the column header for the far right column, while
                         FY 2004 estimated and actual data would be presented in the columns on the left.

                   4     Enter the estimated and actual Indirect Labor Costs in the "Wages paid for time worked section".

                   5     Update the descriptions in the "Fringe Benefits" and "Other Fringe Benefits" sections to reflect those used by your
                         organization.
                   6     Update the descriptions in the "Indirect Costs" section to reflect those used by your organization.

                   7     Update the descriptions in the "Direct Labor Costs by Department" section to reflect those used by your organization.

                   8     Enter the estimated and actual costs for each of the sections in steps 5 through 7.

                   9     If using the Fixed Rate method, enter the carryforward adjustments for the fringe and indirect cost pools.
                         i.e.: The FY 2002 (Over) / Under recovery amount would have been carried forward to FY 2004 as an adjustment to
                         the estimated rates and should have also been posted in the accounting system as an appropriate credit or charge to
                         the fringe or indirect cost pool. If the carryforward adjustment was used to adjust the estimated rate but was not
                         posted in the accounting system then an entry should only appear in the estimated column.

                   10    Any extra rows in the sections covered in steps 5 through 7 may be hidden before printing the spreadsheet.

                         Notes:
                   A     All fields highlighted in yellow can be updated by the MPO.

                   B     Fields not highlighted, have been protected to ensure the integrity of the formulas in the spreadsheet.

                   C     If the estimated fringe and indirect rates for the completed year do not match the rates in the ICAP Agreement
                         the over/under recovery calculation will not be accurate. Please call for assistance.
                   D     If you have questions or need assistance, please contact Michael Miller in the ODOT Office of Auditing at
                         (614) 466-0588.




6d0c6237-087f-4e48-87c3-5c08b4cadd6b.xls                                 Page 1 of 5                                                         Date Printed: 3/7/2011
                                                          SAMPLE
                                            METROPOLITAN PLANNING ORGANIZATION
                                  STATEMENT OF DIRECT LABOR, FRINGE BENEFITS, AND GENERAL OVERHEAD
                                                       (July 1, 2003 - June 30, 2004)

                                                     Estimated          Actual           Difference           ODOT                  Estimated
                                                      FY 2004          FY 2004          (Over Bdgt.)                                 FY 2006
                                                                                        Under Bdgt.         Adjustments

Wages paid for time worked:
   Direct Labor                                  $      558,644    $     527,623    $         31,021    $            -          $       543,452
   Indirect Labor                                $      103,301    $     105,069    $         (1,768)   $            -          $       108,222
Total Labor - base for fringe allocation         $      661,945    $     632,692    $         29,253    $            -          $       651,674

Fringe Benefits
  Holidays                                       $       30,230    $       24,982   $          5,248    $            -          $        25,731
  Sick Leave                                     $       42,791    $       28,832   $         13,959    $            -          $        29,697
  Vacation                                       $       47,700    $       43,461   $          4,239    $            -          $        44,764
  Other                                          $          -      $          -     $            -      $            -
  Other                                          $          -      $          -     $            -      $            -
Subtotal Fringe Benefit Wages                    $      120,721    $       97,274   $         23,447    $            -          $       100,192

Other Fringe Benefits
  PERS                                           $      106,030    $      97,136    $          8,894    $            -          $       100,050
  Hospitalization                                $      111,613    $     104,467    $          7,146    $            -          $       107,601
  Medicare                                       $        9,822    $       9,601    $            221    $            -          $         9,889
  Workers Comp                                   $          868    $       2,053    $         (1,185)   $            -          $         2,114
  Other                                          $          -      $         -      $            -      $            -          $           -
  Other                                          $          -      $         -      $            -      $            -          $           -
  Other                                          $          -      $         -      $            -      $            -          $           -
  Prior Year Rate Adjustment
  (use only with fixed rate)                     $        6,325    $         -      $          6,325                            $       (17,977)
Subtotal Other Fringe Benefits                   $      234,658    $     213,258    $         21,400    $            -          $       201,677

TOTAL FRINGE BENEFITS                            $      355,379    $     310,532    $         44,847    $            -          $       301,869

Indirect Costs
  Salaries - Indirect Only                       $      103,301    $     105,069    $         (1,768)   $            -          $       108,222
  Fringe Benefits for Indirect Salaries          $       55,459    $      51,569    $          3,890    $            -          $        50,131
     Advertising                                 $          526    $       2,129    $         (1,603)   $            -          $         2,193
     Central Services Cost Allocation            $      101,417    $     124,027    $        (22,610)   $            -          $       127,748
     Communications                              $        9,217    $       8,290    $            927    $            -          $         8,538
     Contract Services                           $          809    $         -      $            809    $            -          $           -
     Depreciation                                $        6,544    $       7,412    $           (868)   $            -          $         7,634
     Equipment Purchase                          $          -      $         -      $            -      $            -          $           -
     Maintenance & Repair                        $        4,013    $         835    $          3,178    $            -          $           860
     Materials & Supplies                        $        8,909    $      11,880    $         (2,971)   $            -          $        12,236
     Memberships, Subscriptions                  $        2,822    $       4,269    $         (1,447)   $            -          $         4,397
     Publications & Printing                     $        1,818    $       1,213    $            605    $            -          $         1,250
     Risk Management                             $        2,037    $       1,345    $            692    $            -          $         1,385
     Software / Technical Support                $          224    $         196    $              28   $            -          $           202
     Training                                    $        7,113    $       3,630    $          3,483    $            -          $         3,739
     Travel                                      $       11,590    $       3,653    $          7,937    $            -          $         3,763
     Other Expenses                              $            20   $         -      $              20   $            -          $           -
     Other                                       $          -      $         -      $            -      $            -          $           -
     Other                                       $          -      $         -      $            -      $            -          $           -
     Other                                       $          -      $         -      $            -      $            -          $           -
     Other                                       $          -      $         -      $            -      $            -          $           -
  Prior Year Rate Adjustment
  (use only with fixed rate)                     $      (14,175) $           -      $        (14,175)   $            -          $        26,448
TOTAL INDIRECT COSTS                             $      301,644 $        325,517    $        (23,872)   $            -          $       358,745




     6d0c6237-087f-4e48-87c3-5c08b4cadd6b.xls                      Page 1 of 5                                           Date Printed: 3/7/2011
                                                                          SAMPLE
                                                            METROPOLITAN PLANNING ORGANIZATION
                                        STATEMENT OF DIRECT LABOR, FRINGE BENEFITS, AND GENERAL OVERHEAD
                                                             (July 1, 2003 - June 30, 2004)

                                                                    Estimated         Actual             Difference           ODOT                  Estimated
                                                                     FY 2004         FY 2004            (Over Bdgt.)                                 FY 2006
                                                                                                        Under Bdgt.         Adjustments

Direct Labor Costs by Department:
  County Planning Commission                                    $      309,700   $     238,952      $         70,748    $            -          $       246,121
  Community Development                                         $      136,264   $     166,481      $        (30,217)   $            -          $       171,475
  Transportation Planning                                       $      112,679   $     122,190      $         (9,511)   $            -          $       125,856
  Other                                                         $          -     $         -        $            -      $            -          $           -
  Other                                                         $          -     $         -        $            -      $            -          $           -
TOTAL DIRECT LABOR COSTS                                        $      558,643   $     527,623      $         31,020    $            -          $       543,452

Fringe Benefit Cost Rate Computation
  TOTAL Fringe Benefit Costs /                                  $      355,379 $       310,532                                                  $       301,869
  TOTAL Labor Costs (Direct & Indirect)                         $      661,945 $       632,692                                                  $       651,674
= Fringe Benefit Cost Rate                                              53.69%          49.08%                                                           46.32%

FY 2004
Fringe Benefit Cost Recovery Comparison                        (Direct Labor Portion Only)
  Should have recovered in fiscal year
  (Actual Direct Labor * Actual Fringe Benefit Cost Rate)                        $     258,963               49.08%                             of Direct Labor
  Amount actually recovered in fiscal year
  (Actual Direct Labor x ESTIMATED Fringe Benefit Cost Rate)                     $     283,265               53.69%                             of Direct Labor
  Prior Year Net (Over) / Under Recovery                                         $       6,325
  Prior Year (Over) / Under Recovery Posted to Cost Pool                         $           -
(Over)/Under Recovery of Fringe Benefits                                         $     (17,977)    A (over)/under

Indirect Cost Rate Computation
   TOTAL Indirect Costs /                                       $      301,644 $       325,517                                                         $358,745
   only DIRECT Labor Costs                                      $      558,644 $       527,623                                                         $543,452
 = Indirect Cost Rate                                                   54.00%          61.69%                                                           66.01%

FY 2004
Indirect Cost Recovery Comparison                              (All Indirect Costs, Indirect Labor & Indirect Labor Fringe Benefits)
  Should have recovered in fiscal year
  (Actual Direct Labor * Actual Indirect Cost Rate)                              $     325,517               61.69%                             of Direct Labor
  Amount actually recovered in fiscal year
  (Actual Direct Labor x ESTIMATED Indirect Cost Rate)                           $     284,894               54.00%                             of Direct Labor
  Prior Year Net (Over) / Under Recovery                                         $     (14,175)
  Prior Year (Over) / Under Recovery Posted to Cost Pool                         $           -
(Over)/Under Recovery of Indirect Costs                                          $      26,448     B (over)/under

FY 2004
  Fringe Benefit Cost (Over)/Under Recovery                                      $      (17,977)   A (over)/under
  Indirect Cost (Over)/Under Recovery                                            $       26,448    B (over)/under
Net (Over)/Under Recovery                                                        $        8,470

                                                                    Estimated         Actual                                                        Estimated
                                                                     FY 2004         FY 2004                                                         FY 2006
Summary
                 Fringe Benefit Rate                                    53.69%           49.08%                                                          46.32%
                 Indirect Cost Rate                                     54.00%           61.69%                                                          66.01%
Total Overhead Cost Rate                                               107.68%          110.78%                                                         112.33%




     6d0c6237-087f-4e48-87c3-5c08b4cadd6b.xls                                    Page 2 of 5                                             Date Printed: 3/7/2011
                                                METROPOLITAN PLANNING ORGANIZATION
                                 STATEMENT OF DIRECT LABOR, FRINGE BENEFITS, AND GENERAL OVERHEAD
                                                      (July 1, 2003 - June 30, 2004)

                                                            Estimated             Actual            Difference               ODOT               Estimated
                                                             FY 2004             FY 2004           (Over Bdgt.)           Adjustments            FY 2006
                                                                                                   Under Bdgt.

Wages paid for time worked:
   Direct Labor                                         $               -    $             -   $                  -   $                 -   $               -
   Indirect Labor                                       $               -    $             -   $                  -   $                 -   $               -
Total Labor - base for fringe allocation                $               -    $             -   $                  -   $                 -   $               -

Fringe Benefits
  Holidays                                              $               -    $             -   $                  -   $                 -   $               -
  Sick Leave                                            $               -    $             -   $                  -   $                 -   $               -
  Vacation                                              $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
Subtotal Fringe Benefit Wages                           $               -    $             -   $                  -   $                 -   $               -

Other Fringe Benefits
  PERS                                                  $               -    $             -   $                  -   $                 -   $               -
  Hospitalization                                       $               -    $             -   $                  -   $                 -   $               -
  Medicare                                              $               -    $             -   $                  -   $                 -   $               -
  Workers Comp                                          $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
  Other                                                 $               -    $             -   $                  -   $                 -   $               -
Prior Year Rate Adjustment (use only with fixed rate)   $               -    $             -   $                  -   $                 -   $               -
Subtotal Other Fringe Benefits                          $               -    $             -   $                  -   $                 -   $               -

TOTAL FRINGE BENEFITS                                   $               -    $             -   $                  -   $                 -   $               -

Indirect Costs
  Salaries - Indirect Only                              $               -    $             -   $                  -   $                 -   $               -
  Fringe Benefits for Indirect Salaries                     #DIV/0!              #DIV/0!             #DIV/0!          $                 -       #DIV/0!
     Advertising                                        $               -    $             -   $                  -   $                 -   $               -
     Central Services Cost Allocation                   $               -    $             -   $                  -   $                 -   $               -
     Communications                                     $               -    $             -   $                  -   $                 -   $               -
     Contract Services                                  $               -    $             -   $                  -   $                 -   $               -
     Depreciation                                       $               -    $             -   $                  -   $                 -   $               -
     Equipment Purchase                                 $               -    $             -   $                  -   $                 -   $               -
     Maintenance & Repair                               $               -    $             -   $                  -   $                 -   $               -
     Materials & Supplies                               $               -    $             -   $                  -   $                 -   $               -
     Memberships, Subscriptions                         $               -    $             -   $                  -   $                 -   $               -
     Publications & Printing                            $               -    $             -   $                  -   $                 -   $               -
     Risk Management                                    $               -    $             -   $                  -   $                 -   $               -
     Software / Technical Support                       $               -    $             -   $                  -   $                 -   $               -
     Training                                           $               -    $             -   $                  -   $                 -   $               -
     Travel                                             $               -    $             -   $                  -   $                 -   $               -
     Other Expenses                                     $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
     Other                                              $               -    $             -   $                  -   $                 -   $               -
Prior Year Rate Adjustment (use only with fixed rate)   $               -    $             -   $                  -   $                 -   $               -
TOTAL INDIRECT COSTS                                        #DIV/0!              #DIV/0!             #DIV/0!          $                 -       #DIV/0!




     6d0c6237-087f-4e48-87c3-5c08b4cadd6b.xls                               Page 3 of 5                                            Date Printed: 3/7/2011
                                                             METROPOLITAN PLANNING ORGANIZATION
                                         STATEMENT OF DIRECT LABOR, FRINGE BENEFITS, AND GENERAL OVERHEAD
                                                              (July 1, 2003 - June 30, 2004)

                                                                     Estimated             Actual            Difference               ODOT               Estimated
                                                                      FY 2004             FY 2004           (Over Bdgt.)           Adjustments            FY 2006
                                                                                                            Under Bdgt.

Direct Labor Costs by Department:
  County Planning Commission                                     $               -    $             -   $                  -   $                 -   $                 -
  Community Development                                          $               -    $             -   $                  -   $                 -   $                 -
  Transportation Planning                                        $               -    $             -   $                  -   $                 -   $                 -
  Other                                                          $               -    $             -   $                  -   $                 -   $                 -
  Other                                                          $               -    $             -   $                  -   $                 -   $                 -
  Other                                                          $               -    $             -   $                  -   $                 -   $                 -
  Other                                                          $               -    $             -   $                  -   $                 -   $                 -
  Other                                                          $               -    $             -   $                  -   $                 -   $                 -
TOTAL DIRECT LABOR COSTS                                         $               -    $             -   $                  -   $                 -   $                 -

Fringe Benefit Cost Rate Computation
  TOTAL Fringe Benefit Costs /                                   $               -    $             -                                                $                 -
  TOTAL Labor Costs (Direct & Indirect)                          $               -    $             -                                                $                 -
= Fringe Benefit Cost Rate                                            #DIV/0!             #DIV/0!                                                         #DIV/0!

FY 2004
Fringe Benefit Cost Recovery Comparison                         (Direct Labor Portion Only)
  Should have recovered in fiscal year
   (Actual Direct Labor * Actual Fringe Benefit Cost Rate)                                #DIV/0!            #DIV/0!                                 of Direct Labor
  Amount actually recovered in fiscal year
   (Actual Direct Labor x ESTIMATED Fringe Benefit Cost Rate)                             #DIV/0!            #DIV/0!                                 of Direct Labor
  Prior Year Net (Over) / Under Recovery
                                                                                      $             -
  Prior Year (Over) / Under Recovery Posted to Cost Pool
                                                                                      $             -
(Over)/Under Recovery of Fringe Benefits                                                  #DIV/0!       A (over)/under

Indirect Cost Rate Computation
   TOTAL Indirect Costs /                                             #DIV/0!             #DIV/0!                                                         #DIV/0!
   only DIRECT Labor Costs                                       $               -    $             -                                                $                 -
 = Indirect Cost Rate                                                 #DIV/0!             #DIV/0!                                                         #DIV/0!

FY 2004
Indirect Cost Recovery Comparison                               (All Indirect Costs, Indirect Labor & Indirect Labor Fringe Benefits)
  Should have recovered in fiscal year
   (Actual Direct Labor * Actual Indirect Cost Rate)                                      #DIV/0!            #DIV/0!                                 of Direct Labor
  Amount actually recovered in fiscal year
   (Actual Direct Labor x ESTIMATED Indirect Cost Rate)                                   #DIV/0!            #DIV/0!                                 of Direct Labor
  Prior Year Net (Over) / Under Recovery
                                                                                      $             -
  Prior Year (Over) / Under Recovery Posted to Cost Pool
                                                                                      $             -
(Over)/Under Recovery of Indirect Costs                                                   #DIV/0!       B (over)/under

FY 2004
  Fringe Benefit Cost (Over)/Under Recovery                                               #DIV/0!       A (over)/under
  Indirect Cost (Over)/Under Recovery                                                     #DIV/0!       B (over)/under
Net (Over)/Under Recovery                                                                 #DIV/0!

                                                                     Estimated             Actual                                                        Estimated
                                                                      FY 2004             FY 2004                                                         FY 2006
Summary
  Fringe Benefit Rate                                                 #DIV/0!             #DIV/0!                                                         #DIV/0!
  Indirect Cost Rate                                                  #DIV/0!             #DIV/0!                                                         #DIV/0!
Total Overhead Cost Rate                                              #DIV/0!             #DIV/0!                                                         #DIV/0!




      6d0c6237-087f-4e48-87c3-5c08b4cadd6b.xls                                       Page 4 of 5                                            Date Printed: 3/7/2011

				
DOCUMENT INFO
Description: Overhead Spreadsheet document sample