Register Analysis Spreadsheet User Guide

This spreadsheet has 12 individual sheets. The following gives you a description of each sheet:

Note: This is intended to replace the summary page of the South Link Health ŒStage 2‚ reporting.  Details of the number of patients submitted, rejected and accepted for funding can be found on 4. Patient Numbers.

The content page identifies all the sheets in the spreadsheet.

It includes clickable links to each sheet (although not to those with graphs as you can't create a clickable link to a graph in Excel).

There are two separate tables on this sheet - together they give you a monthly analysis of the funding streams for the practice and the contribution from the practice to the PHO for this quarter and over time. All amounts are GST exclusive.

Monthly Payments (excl GST) for Current Quarter (01 mmm yy)

This shows the monthly financial calculations for this quarter. It includes First Contact Care (your core payment) and your VLCA or Under 6 VLCA payment, if applicable.

Monthly Totals (All Quarters)

This table gives the practice monthly totals for First Contact Care since NBPH started doing the register management for the PHO or since you joined the PHO. The data here is used as the basis for "2 Financial Graph". This table should be used to monitor changes over time.

This sheet is an optional spreadsheet for those practices that have requested it.  If you wish future reports to include this breakdown, please send an email to cbf@nelsonbayspho.org.nz to request this.
There are two tables in this spreadsheet:

1. A breakdown of the funded patients by provider.
2. A breakdown of the First Contact Care (FCC) payment by provider.

Please note: if you use the percentage of funded patients as the basis for payment it would makes sense to use the same percentage on the FFS deduction to calculate the actual payment. Conversely, if you use the actual FCC amounts then it would make sense to calculate the FFS deductions according to provider.

This is a graph of the Monthly Funding of your practice based on the data in "1 Financial Details" - use this graph to track financial changes over time.

Note: there are CPI (Consumer Price Index) adjustments every year on 1st July so there should be a small increase at this time each year. Otherwise you would be looking to see how any changes in the register or funding structure are reflected in the total payments your practice received.

There are two separate tables on this sheet - together they give you a quarterly analysis of the funding streams for the practice for this quarter and over time. All amounts are GST exclusive.

This sheet is identical to the first sheet but the amounts are quarterly, rather than monthly. Generally monthly will be more useful, but for budgeting or other purposes quarterly may be required - this sheet gives you the totals to spare you the trouble of calculating them.

There are two separate tables on this sheet - together they give you an analysis of the total numbers of patients submitted, funded and rejected (including the reasons for rejection) for this quarter and previous quarters. The key at the top of this sheet lists the HealthPAC codes with the explanation of the rejection code.

Numbers for Current Quarter (01 mmm yy)

This shows the total number of patients submitted and funded - the difference is represented by the total of the next 7 columns - use the key above or hold your mouse on the red triangle to find the reason for rejection.

The "tidier" your register is when it is submitted to NBPH the smaller the gap between the number submitted and number funded.

There will always be some patients rejected as "Enrolled Elsewhere" or "3003 Duplicate Patient Found" or sometimes "3004 Deceased Patient Found" - however it is possible by checking the register immediately prior to submission (which is in the second month of each quarter) to eliminate all the other reasons for rejection.

Totals (All Quarters)

This table gives the practice's total number of patients submitted, funded and rejected for each quarter since NBPH started doing register management for the PHO or since the practice joined the PHO. The data here is used as the basis for the graph on the next sheet. This table should be used to monitor changes over time.

This is a graph of the PHO Monthly Totals from the previous sheet -this graph illustrates changes in the patient numbers at your practice over time. For example, the data may indicate it is a good time to make decisions about accepting new patients.

This shows the breakdown of patients in the practice by Deprivation Index (or Quintile) and Maori, Pacific Island and Other categories. The percentage is the percentage of the total register in each cell.

This graph shows the number of patients in your practice in each quintile. Each quintile in the New Zealand population  is around 20%.

There are two tables on this sheet. The first table shows the number of patients broken down by age group and ethnic group (based on the priority ethnicity ranking of the three ethnicities in the patient register) - this table is used as the basis of the graph in the next sheet. The second table converts these numbers to percentages - this is useful for comparing the different population distributions for different ethnic groups!

This is a graph of the information in the table in the previous sheet. It is worth looking closely at the age and ethnicity distribution of your practice, as this will have a significant impact on the service utilisation (i.e. how often people visit) of your practice.

This table shows the number of patients broken down by age group and gender - this table is used as the basis of the graph in the next sheet

This is a graph of the information in the table in the previous sheet. It is worth looking closely at the age and gender distribution of your practice, as this will have a significant impact on the service utilisation (i.e. how often people visit) of your practice.

It is also important to notice whether you have nearly equal numbers of men and women enrolled in your practice - in particular whether "middle aged men" are enrolled in your practice.