Docstoc

Vehicle Purchase Agreement Template - Excel

Document Sample
Vehicle Purchase Agreement Template - Excel Powered By Docstoc
					Service Center:                                                        Date:


                                                                  The University of Kansas
                                                              Salary and Wage Included in Rate Calculation
                  Items To Be Filled In By Unit


                                                   Funding Source                                                                      Cost Allocation
                                                                                                  Less:
                                       KU                                                       University                             % of                   % of   Check
Employee Name     Title             Position #    Fund   Cost Center     Salary      Fringe      Subsidy     Total       Service #1    Time     Service #2    Time   Total    Comments
Example:
Jane Doe          Technician         1234567      700      7891234       34,500.00   9,660.00                44,160.00     33,120.00      75%     11,040.00    25%    100%
                                                                                                                  -             -                        -              0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                                                                                  -             -                        -               0%
                                                         Totals                -         -            -           -             -                        -
                                                                                                                           (carry these amounts to the summary)




                                                                                                                                           Service Center Rate Calculation Worksheet (Rev. 7/07)
Service Center:                                                                                Date:


                                                    The University of Kansas
                                            Other Direct Expenses Included in Rate Calculation
                                            Items To Be Filled In By Unit


                                               Funding Source                                Cost Allocation
                                                                                              % of                    % of     Check
Description of Service or Supply             Fund    Cost Center      Total       Service #1 Time Service #2          Time     Total   Comments
Example:
Equipment Maintenance Agreement               700       7891234        5,000.00     5,000.00    100%           -          0%    100%
Direct Supplies (e.g. equipment supplies)     700       7891234        1,200.00       600.00     50%        600.00       50%    100%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                                                        -                      -                  0%
                                                     Totals                 -           -                      -
                                                                                    (carry these amounts to the summary)




                                                                                                                     Service Center Rate Calculation Worksheet (Rev. 7/07)
Service Center:                                                                          Date:


                                                                                         The University of Kansas
                                                                                 Equipment Depreciation Included in Rate Calculation
                                        Items To Be Filled In By Unit


                                                                                           Funding Source                                                                        Cost Allocation
                                                      KU / KUCR KU / KUCR Acquisition                            Purchase      Accum.          Useful     Annual                  % of            % of              Check
Equipment Description                     Serial #    Asset ID #  Tag #      Date         Fund   Cost Center       Price       Depr.**          Life     Depr.**      Service #1 Usage Service #2 Usage             Total   Comments
Example:
Ultraviolet/Visible spectrophotometer     ABC12345      00499999   KU00088888   6/1/04    700       7891234        85,000.00       15,937.50     8.00     10,625.00     10,625.00   100%               -       0%    100%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                                             -                     -                   0%
                                                                                                                                                        Totals               -                     -
                                                                                                                                                                        (carry these amounts to the summary)
 REMINDER - Per KU Policy, the purchase cost of a capital item may be recovered through depreciation if equipment usage is a
 significant part of providing the service, the service center is responsible for providing funds to replace the equipment, an d the
 equipment was not purchased with federal funds. Capital items include assets with a purchase price greater than or equal to
 $5,000 and a useful life of at least one year.

** Per KU Policy, the Comptroller’s Office Financial Reporting Unit will provide all depreciation expense costs. Service centers
   requesting to include depreciation expense within their service center fee calculations are responsible for contacting the
   Comptroller’s Office Financial Reporting Unit.




                                                                                                                                                                                    Service Center Rate Calculation Worksheet (Rev. 7/07)
Service Center:                                                                         Date:


                                      The University of Kansas
                                   Overhead Expenses Included in Rate Calculation
                                      Items To Be Filled In By Unit


                                         Funding Source
                                                                                          Less:
                                                                                        University
Description of Service of Supply       Fund    Cost Center     Salary       Fringe       Subsidy       Total       Comments
Example:
Administrative Staff                    700       7891234       50,000.00   14,000.00    (40,000.00)   24,000.00
General Office Supplies                 700       7891234                                                 500.00
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                                                                                            -
                                               Totals                 -          -              -           -




                                                                                                       Service Center Rate Calculation Worksheet (Rev. 7/07)
Service Center:                                                                                                                             Date:


                                                                                                      The University of Kansas
                                                                                                Forecasted Usage Included in Rate Calculation
                                              Items To Be Filled In By Unit
                                                                                                                      Internal                                            External - Academic                                     External - Market*

                                                                                                              KU Depts        Sponsored                                    Other                                   Other
                                                                                        Total Estimated        (Non-            Projects                                  Regents       Other      Other State (Foundations,      Other (Foundations,
Service Description                           Metric                                     Annual Usage         Federal)         (Federal)        Students   Faculty/Staff Universities Universities Agencies Corporations, etc.)   Corporations, etc.)
Example:
Service #1                                    Machine Hours                                    1500                     100          1000                                       200                                         195                         5
Service #2                                    Completed Test                                   300                       15           250                                                    15                              20
                                                                                                0
                                                                                                0
                                                                                                0

* External market rates may be established on a case-by-case basis -- market rates are currently limited to only research service center labs




                                                                                                                                                                                             Service Center Rate Calculation Worksheet (Rev. 7/07)
Service Center:                                                                                            Date:


                                                                           The University of Kansas
                                                                     Summary of Expenses and Calculation of Rate

                                           Items To Be Filled In By Unit


Allowable Costs:                                                                            EXAMPLE
                                                                           Service #1      Service #2         Total                          Service #1             Service #2                   Total

Salary and Wage Expenses (From Salary and Wage Worksheet)                      33,120.00       11,040.00           44,160.00 a                       -                         -                             -

Other Expenses (From Other Expenses Worksheet)                                  5,600.00         600.00             6,200.00 b                       -                         -                             -


Depreciation (From Equipment Worksheet)                                        10,625.00             -             10,625.00 c                       -                         -                             -


Total Direct Operating Costs                                                   49,345.00       11,640.00           60,985.00 d = a + b + c           -                         -                             -


CY Overhead Expenses (From Overhead Expenses Worksheet)                                                            24,500.00 e                                                                           -
Prior Year Deficit (Surplus) Adjustment                                                                             1,000.00 f


Total Allowable Overhead                                                                                           25,500.00 g = e + f                                                                       -
% of Allowable Overhead to Total Direct Operating Costs                                                                 42% h = g / d                                                                        0%


Overhead Allocation                                                            20,632.90        4,867.10           25,500.00 i = d x h               -                         -                             -


Total Allowable Costs                                                          69,977.90       16,507.10           86,485.00 j = d + i               -                         -                             -


Forecasted Units of Good or Service:
Total Number of Units Per Year (From Forecasted Usage Worksheet)                 1500.00          300.00                      k                      -                         -


Cost Per Unit:                                                                    46.65           55.02                       l=j/k                  -                         -




                                                                                                                                                    Service Center Rate Calculation Worksheet (Rev. 7/07)
Service Center:


                                                               The University of Kansas
                                                                        Summary of Proposed Rate(s)


                                                                                          EXAMPLE
                                                                                  Service #1   Service #2           Service #1                Service #2

Cost Per Unit: (From Expense Summary)                                                    46.65        55.02                    -                          -



Proposed Rate(s):
Internal                                                                                 47.00        55.00                    -                          -
 Rate Distribution:
 Operations                                                                              40.00        55.00                    -                          -
 Equipment Reserve                                                                        7.00          -                      -                          -


External - Academic                                                                      73.00        86.00                    -                          -
* typically internal rate plus KULC AOH (2%) + uncapped F&A overhead rate (48%)
 Rate Distribution:
 Operations                                                                              39.79        55.30                    -                          -
 Equipment Reserve                                                                        7.00          -                      -                          -
 KULC AOH                                                                                 3.65         4.30                    -                          -
 KULC F&A OH                                                                             22.56        26.40                    -                          -


External - Market
* market rates may be established on a case-by-case basis -- market rates are           100.00          -
currently limited to only research service center labs
 Rate Distribution:
 Operations                                                                              40.00          -                      -                          -
 Equipment Reserve                                                                        7.00          -                      -                          -
 Excess Revenue                                                                          29.50          -                      -                          -
 KULC AOH                                                                                 0.94          -                      -                          -
 KULC F&A OH                                                                             22.56          -                      -                          -




                                                                                                              Service Center Rate Calculation Worksheet (Rev. 7/07)
      Service Center:____________________________                                                     Date:________________



                                                                                           The University of Kansas
                                                                                              Summary of Projected Revenues

                                                                                                            EXAMPLE
                                                                                           Service #1      Service #2         Total             Service #1             Service #2                   Total
Requested Rate(s)
Internal (From Expense Summary Worksheet)                                                         47.00           55.00                                 -                         -
External - Academic (From Expense Summary Worksheet)                                              73.00           86.00                                 -                         -
External - Market* (From Expense Summary Worksheet)                                              100.00              -                                  -                         -


Forecasted Units of Good or Service:
Internal (From Forecasted Usage Worksheet)                                                      1,100.00         265.00                                 -                         -
External - Academic (From Forecasted Usage Worksheet)                                            395.00           35.00                                 -                         -
External - Market* (From Forecasted Usage Worksheet)                                                5.00             -                                  -                         -


Total Forecasted Units of Good or Service                                                       1,500.00         300.00                                 -                         -


Forecasted Revenue:
Internal                                                                                       51,700.00       14,575.00                                -                         -
External - Academic                                                                            28,835.00        3,010.00                                -                         -
External - Market*                                                                               500.00              -                                  -                         -


Total Forecasted Revenue                                                                       81,035.00       17,585.00         98,620.00              -                         -                           -

Forecasted Revenue Distribution:
Service Center Operating Cost Center                                                           59,917.05       16,510.50         76,427.55              -                         -                           -
Service Center Equipment Reserve Cost Center (recovered depreciation)                          10,500.00             -           10,500.00              -                         -                           -
Service Center Excess Revenue Reserve Cost Center                                                147.50              -             147.50               -                         -                           -
KULC Administrative Overhead (2% of External Sales w/n Fund 700)                                1,446.45         150.50           1,596.95              -                         -                           -
KULC F&A Overhead (48% of External Sales -- transfer to Fund 700, CC 1000000)                   9,024.00         924.00           9,948.00              -                         -                           -
Total                                                                                          81,035.00       17,585.00         98,620.00              -                         -                           -

check total (service center operating & equipment revenue less operating expenses)               439.15             3.40           442.55               -                         -                           -
* goal is to be balanced to $0, however minimal differences allowed for rounding of fees


* External market rates may be established on a case-by-case basis -- market rates are currently limited to only research service center labs




                                                                                                                                                      Service Center Rate Calculation Worksheet (Rev. 7/07)
EXAMPLE SERVICE CENTER RATE CALCULATIONS
EXAMPLE #1 -- Service Lab Performing Analytical Test XYZ

UNIQUE ASSUMPTIONS:
- Analytical Test XYZ takes approximately 40 hours to complete each time it is performed
- Direct personnel are working on multiple projects within service center throughout the year
- Analytical Test XYZ uses the same amount of direct personnel level of involvement each time it is performed.
- Analytical Test XYZ uses the same amount of materials each time it is performed.

DIRECT PERSONNEL:
                                     Salary         Benefits            Total            Hourly
Jane Doe                         $     52,000          14,560            66,560             $32.00
John Smith                       $     29,000           8,120            37,120             $17.85

Fringe rate (average)                  28.00%
Hours per year                          2,080 * Based upon average regular work hours within a fiscal year

DIRECT MATERIALS:
Project Specific Supplies        $    15,000         Project
                                 $    15,000           $100.00

Projects per year                $        150 * Based upon forecasted # of projects to be completed in a fiscal year

GENERAL SUPPLIES & OTHER EXPENSES:

Maintenance Service Contract     $     5,000
General Lab Supplies             $    40,000
Other Supplies                   $     3,500         Hourly
                                 $    48,500            $12.13

Hours per year                          4,000 * Based upon forecasted project hours for fiscal year


                    RATE CALCULATION FOR ANALYTICAL TEST XYZ

                    DIRECT PERSONNEL:                                   Hourly      Hours Involved Project Cost
                    Joe Smith                                       $     32.00           15       $    480.00
                    Jane Doe                                        $     17.85           30       $    535.38

                    DIRECT MATERIALS                                                                     $    100.00

                    SUPPLIES & OTHER EXPENSES                       $     12.13            40            $    485.00

                    TOTAL                                                                                $ 1,600.38

                    PROPOSED RATE (Per Analysis)                                                         $      1,600
EXAMPLE #2 -- Service Lab Performing Analytical Test ABC

UNIQUE ASSUMPTIONS:
- There is a fixed cost for every time the equipment used within the analysis is turned on/off (i.e. set up cost)
- The length of time to complete Analytical Test ABC varies
- Direct personnel are working on multiple projects within service center throughout the year

DIRECT PERSONNEL:
                                       Salary         Benefits             Total          Hourly
John Smith                         $     26,000           7,280             33,280           $16.00

Fringe rate (average)                    28.00%
Hours per year                            2,080 * Based upon average regular work hours within a fiscal year

DIRECT MATERIALS:
                                   Related to
                                     Set Up                                                              Other Costs
Equip Set Up Supplies              $      500
                                                   Cost per Set
Project Supplies                            250        Up                                                        2,000           Hourly
                                   $        750           $3.00                                          $       2,000                 $2.00

Project setups per year            $        250 * Based upon forecasted # of         Hours per year              1,000 * Based upon forecasted project hours
                                                   projects to be completed in a                                         for fiscal year
                                                   fiscal year


SUPPLIES & OTHER EXPENSES:

Service Contract                   $     5,000
General Lab Supplies               $    15,500
Other Supplies                     $     3,500          Hourly
                                   $    24,000             $24.00

Hours per year                            1,000 * Based upon forecasted project hours for fiscal year


                   RATE CALCULATION FOR ANALYTICAL TEST ABC

                   SETUP RATE
                   DIRECT PERSONNEL:                                       Hourly    Hours Involved Project Cost
                   John Smith                                          $     16.00        0.5       $       8.00

                   DIRECT MATERIALS                                                                      $        3.00

                   TOTAL                                                                                 $       11.00

                   PROPOSED RATE (Per Project Set Up)                                                    $          11

                   PROJECT HOURLY RATE
                   DIRECT PERSONNEL:                                                                           Hourly
                   John Smith                                                                            $       16.00

                   DIRECT MATERIALS                                                                      $        2.00

                   SUPPLIES & OTHER EXPENSES                                                             $       24.00

                   TOTAL                                                                                 $       42.00

                   PROPOSED RATE (Per Project Hour)                                                      $          42
EXAMPLE #3 -- Rental of Motor Vehicle Equipment (e.g. Aircraft)

UNIQUE ASSUMPTIONS:
- There are both fixed and variable expense costs
- The length of rental time will vary

MATERIALS & SUPPLIES:
Fixed Cost
Supplies                        $       2,000        Hourly
                                $       2,000           $20.00

Hours per year                            100 * Based upon forecasted rental hours for fiscal year

Variable Cost
Fuel                            $        3.50 per gallon
Oil                             $       10.00 per gallon

Estimated Usage
Fuel                                 15 gal/hr
Oil                                 0.2 gal/hr

OTHER EXPENSES:

Maintenance Agreement           $       5,000
Annual Inspection               $         750        Hourly
                                $       5,750           $57.50

Hours per year                            100 * Based upon forecasted rental hours for fiscal year


                  RATE CALCULATION FOR MOTOR VEHICLE RENTAL

                  FIXED RATE ONLY - User Provides Fuel/Oil
                                                                                         Hourly
                  MATERIALS & SUPPLIES                                              $        20.00

                  OTHER EXPENSES                                                    $          57.50

                  TOTAL                                                             $          77.50

                  PROPOSED RATE (Per rental Hour)                                   $                78


                  FIXED & VARIABLE RATE - Service Center Provides Fuel/Oil
                                                            Cost        Est. Usage                            Hourly
                  MATERIALS & SUPPLIES
                  Fixed                                                                                   $      20.00
                  Fuel                                    $    3.50 15 gal/hr                             $      52.50
                  Oil                                     $   10.00 0.2 gal/hr                            $       2.00

                  OTHER EXPENSES                                                                          $      57.50

                  TOTAL                                                                                   $    132.00

                  PROPOSED RATE (Per rental Hour)                                                         $       132
EXAMPLE #4 -- Inventory Cost Using Weighted Average Method

UNIQUE ASSUMPTIONS:
- Inventory rates are established once a year
- Individual purchases are tracked throughout the year

INVENTORY PURCHASES DURING FISCAL YEAR
                                  Invoice Unit                         Shipping
Date                 Quantity        Cost            Sub-Total          Cost           Total Cost         Avg. Cost
Item #123456
1/1/XX                 150       $        2.50   $        375.00   $       25.00   $         400.00   $        2.67
3/15/XX                100       $        2.55   $        255.00   $       20.00   $         275.00   $        2.75
5/30/XX                200       $        2.45   $        490.00   $       28.00   $         518.00   $        2.59
9/1/XX                 250       $        2.35   $        587.50   $       30.00   $         617.50   $        2.47
11/1/XX                100       $        2.55   $        255.00   $       30.00   $         285.00   $        2.85
                       800                                                         $       2,095.50   $        2.62 Weighted Average
                        B                                                                  A                A/B

Item #789123
1/1/XX                 75        $        1.25   $         93.75   $       10.00   $         103.75   $         1.38
2/1/XX                 100       $        1.26   $        126.00   $       12.00   $         138.00   $         1.38
5/15/XX                25        $        1.35   $         33.75   $        5.00   $          38.75   $         1.55
9/1/XX                 100       $        1.25   $        125.00   $       12.00   $         137.00   $         1.37
12/1/XX                50        $        1.30   $         65.00   $        7.00   $          72.00   $         1.44
                       350                                                         $         489.50   $         1.40 Weighted Average



                 INVENTORY COST CALCULATION
                                                                                                          Avg. Unit
                                                                       Quantity    Inventory Cost          Cost
                 Item #123456
                 Beginning Inventory                                      75       $        195.00    $      2.60
                 Inventory Purchased                                     800       $      2,095.50    $      2.62
                                                                         875       $      2,290.50    $      2.62 New Rate
                                                                          D               C                C/D
                 Item #789123
                 Beginning Inventory                                      10       $         14.20    $        1.42
                 Inventory Purchased                                     350       $        489.50    $        1.40
                                                                         360       $        503.70    $        1.40 New Rate


                 NOTE: Any gains/loss that are incurred due to changes in the average inventory unit cost
                       throughout the year will be reflected as a component within the service center's
                       overhead cost calculation.

                 OTHER EXPENSES
                 Prior Year Deficit (Surplus)                                                         $      150.00
                 * Expense to be allocated based upon total "Cost of Goods Sold"

                 Cost of Goods Sold Calculation
                 Inventory Sales
                 Item #123456 - 750 units @ $2.60 plus 20% overhead                                        2,340.00
                 Item #789123 - 325 units @ $1.42 plus 20% overhead                                          553.80
                                                                                                           2,893.80
                                                                         Divided by (1 + OH Rate)               1.2
                                                                               Cost of Goods Sold          2,411.50
EXAMPLE #5 -- Overhead Rate Calculation

UNIQUE ASSUMPTIONS:
- Service center has administrative expenses that cannot be directly applied to good/service
Examples: Administrative Personnel, General Office Supplies, Staff Training, PY Surplus/Deficit
- Overhead rate is applied based upon percentage of direct expenses

ADMINISTRATIVE PERSONNEL:
                                                    Salary      Benefits            Total
Janet Doe        Stockroom Mgr.                       43,500      12,180               55,680

Fringe rate (average)                 28.00%

OTHER EXPENSES:
Office Supplies                                       15,000
Staff Training                                         3,500
                                                      18,500

Prior Year Deficit (Surplus):                         12,750

Cost of Goods Sold :                                775,000
(Total Customer Charges / (1 + Current OH Rate)) or
Total Direct Expenses (if Service Center does not maintain an inventory of goods)


                 OVERHEAD RATE CALCULATION
                                                                                                  FY200X
                                                                            FY200Y Costs           Costs      Combined

                 Administrative Personnel                                             55,680        52,650        108,330

                 Other Expenses                                                       18,500        19,750         38,250

                 Prior Year Deficit (Surplus)                                         12,750        (5,000)         7,750

                 Total                                                                86,930        67,400        154,330

                 Allocation of Expenses

                 Cost of Goods Sold                                                  775,000       737,500      1,512,500

                 Overhead Rate                                                           11%           9%            10%

                 Proposed Rate for FY200Z                                                                            10%



                 APPLICATION EXAMPLE:

                 Stockroom Item #1234
                 Avg Unit Cost                  $       2.00
                 OH Rate                                 10%
                 New Rate                       $       2.20

				
DOCUMENT INFO
Description: Vehicle Purchase Agreement Template document sample