|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
A PDF version of this page is provided here for printing purpose.
The following diagram gives a graphical representation of the basic data preparation approach. Suppose today is 01/02/2008, and the local authority is interested in forecasting the cost due to their known commitment until the end of next financial year (e.g. 01/04/2009) using historical data dated back to 01/04/2004 (i.e. just over four year data). In this case, the data analysis period is from 01/04/2004 to 01/02/2008. The data analysis period is the data period to analyse. It will enable to query subsets of the data. This dataset will be extracted from the data availability period which contains the local authority historical data. In the following example, the data availability period is from 03/02/2000 to 01/02/2008.
For a given data analysis period, various types of residents' stay will be included. In Figure 1, a resident's stay is represented by a line, which starts with a circle and ends by a solid square for death or a slash for having left the system alive. Therefore, in Figure 1, resident 1, 2 and 3 were admitted during the data analysis period; whereas resident 4, 5 and 6 were admitted before and part of their stays were inside the data analysis period. Furthermore, resident 1 and 4 were discharged by death; resident 2 and 5 left the system sometime during the period; and resident 3 and 6 are still present in the system on 1/2/2008. Therefore, in this diagram, residents 3 and 6 are the known commitments today (i.e. 1/2/2008). Obviously, for residents 3 and 6, their stay in the system will continue into the future. However, there is uncertainty regarding the further length of time of their stay and the possibility of moving from residential care to nursing care if needed. FLoSC
uses historical observation from the data analysis period to build a stochastic
model on the pattern of movement of residents in LTC, which is then used
to forecast future movement of the known commitments and to produce the
forecast of cost due to known commitment.
The data required for FLoSC/FLoSC-PAD is relatively simple and is generally derived from administrative data collected by most local councils routinely. Before using FLoSC, a suitable data analysis period needs to be defined so that relevant data on residents' stay during this period can be extracted from local authority dataset using FLoSC-PAD. The essential information needed to derive the working dataset for FLoSC-PAD are, for each publicly funded resident in LTC, id, gender, date of admission, type of care admitted to, date of discharge (if applicable), destination of discharge, including death (if applicable), date of birth and weekly cost (if applicable). As FLoSC-PAD considers types of care rather than homes providing the care, data concerning residents must be recorded at the change of type of care level. However, in practice, a resident's movements in the system are often recorded at the change of care home level. As a result, some data manipulation is often required to derive the data in the required form. An example will further illustrate the idea. For instance, suppose we have the following records for a resident's stay in LTC so far (the column headings and text for entries are for illustrative purpose): Table
1
In this case, the person is still living in home_b for nursing care currently. Since FLoSC-PAD is interested in the starting and ending date for a type of care, these three records are used to derive the following information concerning this resident for the purpose of FLoSC-PAD: Table
2
That
is: the female resident was admitted to residential care on 23/10/2001;
transferred to nursing care on 14/08/2005; and has remained in nursing
care since. Table
3
In this case, the person left the LTC system alive. As the latest information concerning residents is more valuable for the analysis, FLoSC-PAD is interested in information concerning the latest records essentially if the date of admission for that record is sufficiently apart from the date of discharge for the previous record (e.g. at least two months, or whatever is deemed suitable by the user). The number of records to be considered in the analysis is limited to 2 records: Table
4
The user has to make sure that the episodes are collapsed into spells if necessary before removing the past spells. Also it is important to bear in mind that the records kept in the working dataset are with distinct type of care. In this example the male resident had 4 different spells, each one lasts a few months to one year. As the two last records are with distinct type of care, we kept both. In the case of records with the same type of care, we keep only the latest record. Here an example illustrating the case: Table
5
In this case, only the second record is kept in the working dataset: Table
6
Please note that FLoSC-PAD handles exactly two types of care– e.g. residential care and nursing care – that have to be specified by the user. For
a local council, depending on the structure of their information system
and the type of data recorded, the exact manipulation required in order
to derive this type of data for use with FLoSC-PAD will be different.
FLoSC-PAD is implemented as an Excel file. When starting FLoSC-PAD for the first time, you may encounter in Excel a warning message like the following:
Or
This is due to the default macro security level being set to Very High (for the former) or High (for the latter). In order to run FLoSC-PAD, the macro security level needs to be set to Medium. Here are the steps to change it. Within Excel, select Tools from the menu bar, then Macro, then Security..., as shown below.
Then change the setting to Medium as shown in the following screenshot. Click OK, and then close Excel. Please note that setting macro security level to Medium does not in itself compromise your security settings. It simply gives the flexibililty of allowing you the user to choose which macros are allowed to run.
Start FLoSC-PAD again. Then you will be presented with the following dialogue window. Click Enable Macros to allow FLoSC-PAD to run.
Always read the warning message carefully before deciding whether it is from trusted source. For Excel files containing macros from untrusted source, click Disable Macros to prevent it from running. Please note, by changing the macro security level to Medium, this warning dialogue will always appear whenever macros are present in an Excel file. In other words, you will need to click on Enable Macros every time you use FLoSC-PAD. If you are working with Excel 2007 make sure to enable macros as shown below.
Click on “Options…” in the “Security Warning” bar. A dialogue box will appear, select “Enable this content” and then click “OK”.
Note that the user’s file path can be different.
The first step in using FLoSC-PAD is to obtain the relevant data as outlined in the Data requirement section. This will involve collecting data on residents in institutional long-term care (during the data availability period), and carry out the necessary manipulation and recoding so that the data are fit for use in FLoSC. Once the data are in the right form (e.g. the right order of the columns as shown in the example data), start FLoSC-PAD, enter the data related to residents’ records record by record or copy-and-paste them into it. Please be sure to delete all previous data before if necessary. A step-by-step working tutorial is available in the Tutorial section, which will also show how to interpret the results produced by FLoSC-PAD. Then start FLoSC-PAD by clicking on the Run FLoSC-PAD button located to the upper-right corner of the worksheet “Raw data”. Once Run FLoSC-PAD button is pressed, the tool conducts a check on the data before extracting a subset of data and carrying out any further analysis. The check is to ensure the data is consistent with the requirements, namely:
If any empty cell is found, the following warning box will appear.
Click OK to check if any value is missing. All empty cells are highlighted in green, such as those shown in the following.
If any invalid data value is found, the following warning box will appear.
Click OK to check invalid values (e.g. incorrect labels, negative costs). All cells with invalid data are highlighted in red, such as those shown in the following.
If the Data Analysis Period is missing or invalid (e.g. text instead of dates or Data Analysis Period is not within the Data Availability Period), the following warning box will appear.
Click OK to check missing or invalid dates. An example of invalid dates is shown in the following figure.
The user is prompted to specify the Starting and End date of the data analysis period corresponding to the data to be used in FLoSC. If the Threshold value is not entered, the following warning box will appear.
Click OK and enter a Threshold value. The Threshold is the difference between changing types of care. For one resident, if the difference in between changing types of care is greater than the Threshold value indicated, the earliest spell (i.e. first record) will be deleted.
If the Threshold value is invalid (e.g. text, negative value), the following warning box will appear.
Click OK to check the Threshold value. When the user selects cells in the columns “Gender”, “Type of care” and “End reason”, a drop-down list will be displayed so the user can choose an item from the list. Once selected, the item will be automatically entered into the active cell.
If the Gender label (typed directly) is invalid, the following warning box will appear.
The user can click “Retry” to try again to validate the label or “Cancel” to select a label from the drop-down list. The same warning box will pop-up if the user enters an invalid label for “Type of care” and “End reason”. These messages appear only when data is typed directly into the cells. The "date of admission", the "date of discharge" and the "date of birth" should be in the following date format "dd/mm/yyyy". However If date is missing, the user should indicate "Missing" in the appropriate cell. If the resident is still in care at the date of using FLoSC-PAD, the user should indicate "Still in care" in the "date of admission" and "End reason" cells. The "weekly cost" must be a non-negative number. In the case of missing value, the user should indicate "Missing" in the appropriate cell. If a resident has more than 2 records or her/his records are related to the same type of care, the following warning box will appear.
Click OK to check spells. For residents with multiple records and/or records with the same type of care, the ID cells are highlighted in yellow, such as those shown in the following.
Users are required to review and correct the data values. Once it is done, start FLoSC-PAD again by pressing the "Run FLoSC-PAD" button. Again, a check on the data will be conducted. If the data passes the checking process, FLoSC-PAD will continue. During the running of the data preparation/analysis process, various comments indicating the tasks FLoSC-PAD is undertaking will be posted in the status bar so that users are kept informed of the progress: “Starting data preparation…”, “Checking missing/invalid values…”, “Checking spells…”, “Calculating PLOS and LOS…x out of N records…”, “Coding FLoSC data…y out of K records…”, “Determining statistics on residents’ costs and ages at admission…” as in the following screenshot.
Several factors can affect the running time required, such as the amount of data, the speed of the CPU and size of memory of the computer. Once FLoSC-PAD has finished running, the reports of the analysis are produced as additional worksheets.
Pointing to cells with a red corner, a comment will appear providing further information about the data that should be entered by the user.
Residents’ data can be saved in this spreadsheet, and then updated for example by changing the end reason, adding the date of discharge for some residents or adding new records. In this way, it becomes easier for the user to analyse the data.
|
|
Last updated: 2011-03-30 |
