Commercial Construction Budget Worksheet - Excel

Document Sample
Commercial Construction Budget Worksheet - Excel Powered By Docstoc
					Example/Template #1


ILLINOIS CHILDREN'S HEALTHCARE FOUNDATION
INTERACTIVE BUDGET PLANNING WORKSHEET
2009 ORAL HEALTH RFP, Strengthening the Capacity of the Network



Directions

Step 1       Open the Expenses worksheet. Complete the UNSHADED cells.

Step 2       Open the Revenue worksheet. Complete the UNSHADED cells.
             Remember that the percentage of encounters should equal 100%.

Step 3       Open the Summary- “The Bottom Line” worksheet. All figures
             entered into the Expenses and Revenue worksheets are
             automatically imported into this worksheet.

If you need to add or delete rows, make sure the formulas in any of the cells
with totals and subtotals capture the change. For example: In the Expenses
worksheet, you add a row between 17 and 18 under the Office Equipment
Category for another expense item. Check the Office Equipment Subtotal
formula in cell F19 has changed from =SUM(F12:F17) to =SUM(F12:F18)
                                 Interactive Budget Planning Worksheet - Summary

REVENUES                                                   EXPENSES
I. PATIENT CARE REVENUE                                    I. Start-up Costs (If Applicable)
Medicaid                                        $222,300   Construction/Remodeling Cost                   $627,000
SCHIP                                            $35,568   Large Equipment                                $266,586
Other Public Program Contracts                   $14,820   Supplies, Instruments and Small Equipment       $92,936
Self-Pay                                                   Office Equipment                                $57,600
   Full                                          $19,266   START-UP COSTS TOTAL                         $1,044,122
   Sliding Fee Schedule                          $19,019   II. Operating Expenses
  Minimum                                        $11,115   A. Personnel
Commercial Insurance                                       Salaries                                       $391,567
  Indemnity (Fee-for-service)                    $32,110   Fringe Benefits                                 $97,892
  Managed Care - PPO                              $9,633   Personnel Total                                $489,459
Capitation Patient Care Income                   $53,352
PATIENT CARE REVENUE TOTAL                      $417,183   B. Miscellaneous Operating Expenses
                                                           Contracts                                       $20,800
                                                           Supplies                                        $42,600
                                                           Office Supplies                                  $2,968
II. NON-PATIENT CARE REVENUE                               Equipment Maintenance                           $13,800
Grants & Contracts                              $245,000   Housekeeping                                     $6,600
Fundraising                                      $36,500   Utilities                                        $9,568
NON-PATIENT CARE REVENUE TOTAL                  $281,500   Rent/Mortgage                                   $28,200
                                                           Staff Training                                   $4,700
                                                           Lab fees                                        $40,000
                                                           Payroll service                                  $2,100
                                                           Laundry                                          $3,700
                                                           Communications                                   $5,500
                                                           Insurance                                       $17,500
                                                           Bad Debt                                        $33,000
                                                           Depreciation                                    $64,200
                                                           Miscellaneous Operating Expenses Subtotal      $308,486


                                               The Bottom Line
Non-Patient Care Revenue                       $281,500    Total Start up Expenses (If applicable)     $1,044,122
Patient Care Revenue                           $417,183    Total Annual Operating Expenses               $797,945
                                                                 Interactive Budget Planning Worksheet
                                              INSERT PROGRAM-SPECIFIC ESTIMATES IN UN-SHADED CELLS
REVENUES
I. Patient Care Revenue
Estimated number of encounters/year                                 4940
                                                                                                                                      Average
                                                                                                                                    Adjustment/         Total Charge       Adjusted
                                                                                                 Avg Charge/     Total Charges       encounter           Reductions        charge/    Amount Billed
Fee-for-Service                                            % of encounters   # of encounters      encounter          (D*E)              (E-I)              (D*G)          encounter      (D*I)
Medicaid                                                              45%              2223    $       130.00   $   288,990.00    $           30.00   $     66,690.00            $100     $222,300
SCHIP                                                                  8%                395   $       130.00   $     51,376.00   $           40.00   $     15,808.00             $90      $35,568
Public and Private-Nonprofit Program Contracts                         4%                198   $       130.00   $     25,688.00   $           55.00   $     10,868.00             $75      $14,820
Self-Pay
  Full                                                                 3%               148 $          130.00   $    19,266.00    $             -     $               -          $130       $19,266
  Sliding Fee Schedule                                                11%               543 $          130.00   $    70,642.00    $         95.00     $       51,623.00           $35       $19,019
  Minimum                                                             15%               741 $          130.00   $    96,330.00    $        115.00     $       85,215.00           $15       $11,115
Commercial Insurance
  Indemnity (Fee-for-service)                                          5%               247 $          130.00   $    32,110.00 $                 -    $               -          $130       $32,110
  Managed Care - PPO                                                   3%               148 $          130.00   $    19,266.00 $             65.00    $        9,633.00           $65        $9,633
Fee-for-Service Patient Care Revenue Subtotal                                                                         $603,668                                 $239,837                    $363,831
Medicaid Prospective Payment or Cost-Based
(Fee/encounter)                                                        0%                 0                                                                                                     $0
Capitation/Managed Care (HMO) - (List individual numbers                                                                                                  Total Charge
and capitation rates if multiple contracts, whether                                                           Total Charges        Adjustment per          Reductions
commercial or Medicaid HMOs)                                                   # of patients Capitation Rate      (D*E)                   patient            (D*G)       $/patient/yr.
DentalPlan                                                             6%                296             360 $   106,704.00                   180                  53230        $180        $53,352
                                                                                           0                                                                                                     $0
                                                                                           0                                                                                                     $0
                                                                                           0                                                                                                     $0
Capitation Patient Care Revenue Subtotal                                                                                                                                                    $53,352
PATIENT CARE REVENUE TOTAL                                                                                                                                                                 $417,183

II. Non-Patient Care Revenue Sources
A. Grants and Contracts
   Federal                                                                                                                                                                                 $100,000
   State                                                                                                                                                                                    $50,000
   City/County                                                                                                                                                                              $20,000
   Foundation(s)                                                                                                                                                                            $75,000
   Other


Grants and Contracts Subtotal                                                                                                                                                              $245,000
B. Fundraising
   Individual Donations                                                                                                                                                                     $10,000
   Corporate Donations                                                                                                                                                                      $19,000
   Events                                                                                                                                                                                    $7,500
   Other
Fundraising Subtotal                                                                                                                                                                        $36,500
NON-PATIENT CARE REVENUE TOTAL                                                                                                                                                             $281,500
REVENUE (ALL SOURCES)                                                                                                                                                                      $698,683
                                                     Interactive Budget Planning Worksheet
                                         INSERT PROGRAM-SPECIFIC ESTIMATES IN UN-SHADED CELLS
EXPENSES
I. Start-up Costs
Construction/Remodeling Cost
# of square feet                                                              3000
Cost per square foot                                                  $     209.00                                                  $627,000
Dental Equipment Costs
Large Equipment (See Dental Clinic Comparison Chart in
Ch. 2) or enter your own figures per dental supply
company.                                                                                                                            $266,586

Supplies, Instruments and Small Equipment (See Dental
Clinic Comparison Chart in Ch. 2) or enter your own figures
per dental supply company. ($12,000-$13,000/operatory)                                                                               $92,936
Office Equipment
Modular Furniture                                                                                                                     $30,900
Record Filling System                                                                                                                  $2,800
Phone/intercom system                                                                                                                  $2,100
Computer/data/billing                                                                                                                 $15,800
Copier/fax                                                                                                                             $1,400
Supplies                                                                                                                               $4,600
Office Equipment Subtotal                                                                                                             $57,600
START-UP COSTS TOTAL                                                                                                               $1,044,122
II. Operating Expenses
Personnel
Salaries                                                              Annual Salary    % dental         FTE (40hrs/wk=1.0 FTE)
Executive Director                                                         $86,800                10%                        1.0      $8,680
Secretary                                                                  $28,800                15%                        1.0      $4,320
Billing Clerk                                                              $33,600                25%                        1.0      $8,400

Dental Director                                                           $135,200             100%                          1.0    $135,200
Clinical Dentist(s)                                                       $101,920             100%                          0.5     $50,960
Dental Hygienist(s)                                                        $62,895             100%                          0.8     $50,316
Dental Assistants                                                          $32,666             100%                          3.0     $97,998
Receptionist                                                               $35,693             100%                          1.0     $35,693
Salaries Subtotal                                                                                                                   $391,567
Total Fringe Benefit Rate (%):                                                                                             25%
Fringe Benefits                                                                                                                      $97,892
Personnel Total                                                                                                                     $489,459
Miscellaneous Operating Expenses
Contracts
Dentist                                                                   $104,000             100%                          0.2     $20,800
                                                                          QTY      Unit Price
Clinical Supplies (# of operatories x $/operatory)                               6 $        7,100.00                                 $42,600
Office Supplies                                                                                                                       $2,968
Equipment Maintenance (# of operatories x $/operatory)                           6 $       2,300.00                                  $13,800
Housekeeping                                                                                                                          $6,600
Other                                                                                                                                 $9,568
Rent/Mortgage (months/yr x $/mo.)                                               12 $       2,350.00                                  $28,200
Staff Training                                                                                                                        $4,700
Lab fees                                                                                                                             $40,000
Payroll service                                                                                                                       $2,100
Laundry                                                                                                                               $3,700
Communications (telephone, internet)                                                                                                  $5,500
Insurance                                                                                                                            $17,500
Bad Debt                                                                                                                             $33,000
Equipment Depreciation                                                                                                               $64,200
Equipment Reserve Fund                                                                                                               $13,250
Miscellaneous Operating Expenses Subtotal                                                                                           $308,486

TOTAL START-UP EXPENSES                                                                                                            $1,044,122
TOTAL ANNUAL OPERATING EXPENSES                                                                                                      $797,945

				
DOCUMENT INFO
Description: Commercial Construction Budget Worksheet document sample