Skip to Main Content
NY.gov Portal State Agency Listing Search all of NY.gov
Commissioner Michael F. Hogan, PhD
Governor Andrew M. Cuomo

NYS OMH Clinic Projection Tool Change Log

Original Projection Tool Version 2 Was Posted on July 31, 2009.

June 4, 2010 Release Change:

Update:  Updated the Weight and Rate Schedule with the newest Hospital Based Rates

Instructions for change:  Download newest file from NYS OMH Clinic Restructuring website, copy and paste the whole page ‘Weight & Rate Schedule’ and replace your schedule.  Changed the page break to incorporate the new fields for printing purposes.

Update:  Update the APG code for Psychotherapy (90846) from 218 to 318 on Schedule 4.

Instructions for change:  Go to Cell C39 on Schedule 4.  Change the cell to read ‘318'.  Also, make sure cell AN26 reads “xxx”, if not change.

Shortcut:  The new model releases have a macro “ModelUpdate062010” that will make all the changes instantly.  To use shortcut macro, have both newest Clinic Model file (.xls) open along with your current Clinic model file. For 2003 Excel users, open your current Clinic model file and go to ‘Tools’, ‘Macro’, ‘Macros’ and select the ModelUpdate062010 macro and click ‘Run’.  For 2007 Excel users, go to ‘View’, ‘Macros’, select and ‘Run’.

March 21, 2010 Release Change:

Update: Updated the definitions for Psychotropic Medication Administration (H2010) and Psychotropic Medication Treatment (90862)

Instructions for change: Download newest file from NYS OMH Clinic Restructuring website, copy and paste the whole page ‘Weight & Rate Schedule’ and replace your schedule. Also copy and paste the cells C33 (H2010) and C35 (90862) from New file Schedule 3 and paste them into your file. Copy and paste cells E31 (H2010) and E33 (90862) from New file Schedule 4 and paste them into your file.

Update: Update the formula link Medicaid COPS (excluding COPS only) on Schedule 5.

Instructions for change: Go to Cell F37 on Schedule 5. Change the formula cell reference to read ‘='1 Financial Projections'!F17’.

Update: Update the service rate Psychiatric Consultation (99245) to $94.09.

Instructions for change: Go to Cell F23 on Schedule 4. Change the formula cell reference to read ‘='1 Financial Projections'!F17’.

December 23, 2009 Release Change:

The December 23, 2009 release includes blank and fully illustrated Clinic Projection Tools (including visually impaired versions).  The new release includes updates to CPT codes, services, rates, weights, indigent care and legacy revenue calculations.

The Excel formula is identical in all of the models and all the models include a current Weight & Rate tab for user reference.  The illustrated editions are populated with fictional data to display the layouts, linkages among the schedules and display how the calculations actually work.

Please download the blank updated Clinic Projection Tool to build a new model or to update your current model. The new Excel includes a macro which can be used to automatically update the majority of the information in your current Projection Tool.  You may:

  • Use the macro to update your current tool, following the instructions below.
  • Make the updates manually to your current too by following the ‘manual instructions’ below.
  • Copy the data from your current version of the Clinic Projection Tool into the new release, taking into consideration any format changes that you may have made.

Instructions for Schedule Changes Required to Automatically or Manually Update Your Current Projection Tool.
You do not need to perform these changes if you are constructing a new model or copying data from your current tool into the new release.

Perform the following updates to Schedules 1 and 3 in preparation for running the Macro or performing a manual update to your current Projection Tool.

Update: Schedule 1 Changes - Adds Revenue Links, Moved statistics from rows 93 – 96 to rows 53 – 56, and adds Header and Row headers.

Instructions for change: 

  1. Cell D10 "New APG" - add the formula "='7 Phase-in Revenue'!E12"
  2. Cell D11 "New Indigent Care" - add the formula "='7 Phase-in Revenue'!E13"
  3. Cell D12 "Legacy Medicaid FFS" -  add the formula "='7 Phase-in Revenue'!E17"
  4. Cell D13 "Legacy COPS Only" - add the formula "='7 Phase-in Revenue'!E18"
  5. Cell D14 "Medicaid CSP" is purposely left blank. (no action required)
  6. Insert Row 44: Inset text "Average Medicaid FFS Payment".  Columns F & H should be yellow for manual entry.
  7. Insert Row 45: Insert text "COPS Rate".  Columns F & H should be yellow for manual entry.
  8. Cut the numeric stats from rows 93 – 96 to paste them into rows 53 – 56.
  9. Insert text for header to cell B52 "Annual Stats (used in calculation of Legacy Payments)".
  10. Cell B53: Change text from "mcaid" to "Medicaid Visits"
  11. Cell B54: Change text from "xover" to "Medicare - Medicaid Cross Over Visits"
  12. Cell B55: Change text from "mmc " to "Medicaid Managed Care Visits"
  13. Cell B56: Change text from "other" to "Other Visits (Self-Pay, Medicare, Third Party, misc)"

Update: Schedule 2 - the new release makes no changes to Schedule 2.

*Special – Macro Updates*
A Macro file was created in the new release to automatically update Schedules 3, 4, 5, 6, 7 of your current model for all changes except to the comment boxes on Schedules 3 & 4.  If the Excel formats for schedules 3-7 of your current model have not changed from the original release, the macro will run updates correctly. Manual data entered on Schedules 3 – 7 will also not be affected if the format of the initial release has not been changed.

If you choose not to use the Update Macro, a set of Manual updates is available.

Note:  The Excel macro security setting may be set to ‘Very High’ and not allow the macros to run.  To adjust the security setting, click ‘Tools’, ‘Macro’, ‘Security", and adjust the setting to ‘Medium’ to allow the macros to run.  Users with Microsoft Vista or Windows 7 may see a ‘caution’ message in the formula bar area and may need to open the caution message and ‘enable’ the macro option.

To Run the Update Macro:

  1. Download the new Clinic Projection Tool, it includes the Update Macros that can be used to update on your current Tool as described above.
  2. Open both the new Clinic Projection Tool (includes Update Macro) and your current Projection tool, and go to Schedule 4 of your current tool.
  3. Click on ‘Tools’ in your tool bar at the top of the page, click ‘Macros’, then ‘Macros’ (with play symbol). For or Microsoft Vista and Windows 7 users – Select the ‘View’ tab, then click the ‘Macro’ tab at the top right, and then click the Run command to launch the macros (‘run all’ or on an individual basis).
  4.  The "run all’ option is recommended to update Schedules 3-7 in a single operation. Select the ‘Run’; ‘12-23-09 Clinic Model V3 1 (blank or illustration).xls'!AllSchUpdateDec4 – will run all 5 macros for schedules 3, 4, 5, 6, & 7.
  5. Select the following options should you choose to run the macros to updates Schedules 3-7 individually:
    • '12-23-09 Clinic Model V3 1 ___.xls'!Sch3UpdateDec4 – will run macro for schedule 3.
    • '12-23-09 Clinic Model V3 1 ___.xls'!Sch4UpdateDec4 – will run macro for schedule 4.
    • '12-23-09 Clinic Model V3 1 ___.xls'!Sch5UpdateDec4 – will run macro for schedule 5.
    • '12-23-09 Clinic Model V3 1 ___.xls'!Sch6UpdateDec4 – will run macro for schedule 6.
    • '12-23-09 Clinic Model V3 1 ___.xls'!Sch7UpdateDec4 – will run macro for schedule 7.

    After running the Macro:

  6. Copy CPT the codes and comments in cells C13 – C57 from Schedule 3 of the new release and paste them into Schedule 3, cell C13 of your current model.
  7. Copy CPT codes and comments in cells E11 – E54 from Schedule 4 of the new release and paste them into Schedule 4, cell E13 and E64 of your current model.
  8. Congratulations, you have completed the macro updates!!!  Please remember to save your updated file under a new file-name.

Manual Instructions for Updating Schedules 3 – 7

October 2, 2009 Release Change:

Update:  Need to turn on the modifiers for service ‘Outreach and Engagement’ on Schedule 4.  The formulas were already built in anticipation of such a change, so all I had to do was Fill the cell with Yellow and enter ‘0’.
Instructions for change:  On Schedule 4, go to cells AF13 and AG13, copy those cells and paste them into cells AF11 and AG11 for Outreach and Engagement H0023.

Update:  2nd Visit Discount should be available to all services.
Instructions for change:  On Schedule 4, copy cell AP15 and paste into cells AP11, AP 13 AP19, and AP20.  Note: please adjust the % to 0 or any other appropriate percentage.

Update: 2nd Visit Discount calculation for (4) Psychiatric Consultation
Instructions for change:  On Schedule 4, for cells AW19 and AW20 modify the highlighted cell reference in the following formula:  ‘If (AQ19="xxx",0,AQ19*AS19*$AW$7)’ to ‘If (AQ19="xxx",0,AQ19*AV7*$AW$7)’  and for cell AW20  ‘If (AQ20="xxx",0,AQ20*AS20*$AW$7)’ to ‘If (AQ20="xxx",0,AQ20*AV7*$AW$7)’

Update:  Schedule 7, Added a row for COPS only in row 24 and a table for ‘Per Visit Comparisons’ in rows 31 – 39.
Instructions for change:  Download newest version of the ‘Clinic Tool’.  On Schedule 7, Copy and Insert the COPS Only row on row 24. Copy and paste the table from rows 31 - 39 table into your current file.  Update the following links:
COPS Only cell ‘C24’:  Link to Schedule 1 cell ‘F18’
Visits cell ‘E31’: Link to Schedule 5 cell ‘G13’
Base Medicaid cell ‘C37’:  Link to Schedule 1 cell ‘F16’
COPS cell ‘C38’:  Link to Schedule 1 cell ‘F17’

September 10, 2009 Release Change:

Update: On Schedule 4, columns V, W, & X have incorrect formulas references. These rows 11 - 51 for visit distribution are hard coded to reference column ‘U’ and they should be reflective of V, W, & X.
Instructions for change: On Schedule 4, one at a time, highlight rows 11 – 51 for each individual column and press 'Cntr' + 'H' to bring up Find and Replace. Enter Find: $U   Replace: $V    Repeat this step by changing the Replace: with $W and then $X , respectively.

August 14, 2009 Release Change:

Update:  Weight change to 90846 Psychotherapy- Family without patient changing it from 1.00 to .67.
Instructions for change:  Schedule 4, change cell AC34 from 1.00 to .67.

Comments or questions about the information on this page can be directed to the Bureau of Financial Planning.