- General approach
- Data requirement
- Data coding scheme
- Example data
- Macro security warnings
- Using FLoSC
Essentially, FLoSC produces forecast of cost for meeting a council’s known commitment for a period of time based on a probabilistic model built using historical data on the movements of publicly funded residents in institutional long-term care. The term known commitment refers to the group of publicly funded residents currently in care. As mentioned in Introduction, this cost is of particular interest to local councils as it represents the cost that local councils can not escape, and it is the future resources already committed due to past admission decisions.
The following diagram (see Figure 1) gives a graphical representation of the basic modelling framework. Suppose today is 3/9/2007, and the local authority is interested in forecasting the cost due to their known commitment until the end of current financial year (i.e. 1/4/2008) using historical data dated back to 1/4/2002 (i.e. just over five years’ of data). In this case, the forecast period is between 3/9/2007 and 1/4/2008; whereas the data availability period is from 1/4/2002 to 3/9/2007.
For a given data availability 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 availability period; whereas resident 4, 5 and 6 were admitted before and part of their stays were inside the data availability 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 3/9/2007. Therefore, in this diagram, residents 3 and 6 are the known commitments today (i.e. 3/9/2007). 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 availability 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 (for more technical details of the modelling approach, please see the Methods section). This information is then combined with future development of the costs in LTC (costs from the local authority’s point of view) to produce the forecast of cost due to known commitment. Therefore, FLoSC is able to adapt to both the characteristics of a local population and corresponding unit costs of care in its forecast.
The data required for FLoSC is relatively simple and are generally derived from administrative data collected by most local councils routinely. Before using FLoSC, a suitable data availability period needs to be defined so that relevant data on residents’ stay during this period can be extracted from an information system. This process will require inputs from a local council’s technical team, who will have in depth knowledge of the structure of their database system.
The essential information needed to derived the working dataset for FLoSC are, for each publicly funded resident in LTC, id, gender, date of admission, type of care admitted to, date of discharge (if applicable) and destination of discharge, including death (if applicable).
It is important to note that FLoSC models the system of institutional LTC by two types of care – namely residential care (RC) and nursing care (NC). They can be provided in residential homes and/or nursing homes. In other words, FLoSC models the system by type of care rather than homes providing the care. Furthermore, it is the movements crossing this boundary defined by type of care that are of interest to FLoSC, and not the movements of a resident between care homes within the same type of care.
Therefore, idealy for FLoSC, data concerning residents are 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 useful analogy is the concept of consultant episode and spell for a patient’s hospital visit – a spell is made up by several episodes, and the length of stay in hospital is the length of the spell.
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):
In this case, the missing information (e.g. NA) on date of discharge and reason for discharge signify that the person is still living in home_b for nursing care currently. Since FLoSC 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:
That is: the female resident was admitted to residential care on 23/10/2002; transferred to nursing care on 14/08/2005 (i.e. the length of stay in residential care is 1391 days); and has remained in nursing care since (i.e. the length of stay in nursing care is 749 days so far on 3/9/2007).
For a local council, depending 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 will be different. Also, inputs from local technical team will be required. Nevertheless, once the procedure for data extraction and manipulation is set up (e.g. a set of SQL statements), future application of FLoSC should be rather straightforward.
Suppose, in this example, the data availability period has been set to be as illustrated in Figure 1, i.e. from 1/4/2002 to 3/9/2007. In this case, this person was admitted before the start of the data availability period and are present throughout the period, e.g similar to resident 6 in Figure 1. That is: this person had been in residential care for 160 days on 1/4/2002 (the start of the data availability period); during the data availability period, the person spent 1231 days in residential care and had been in nursing care for 749 days on 3/9/2007 (the end of the data availability period). The distinction of length of stay before and after the start of the data availability period is an important one for residents whose stay in the system partially overlap with the data availability period.
Ideally, FLoSC can make use of data such as those illustrated in Table 2. However, due to the rather ambiguous ways that Excel interprets date values in text strings (e.g. American date format or UK format), FLoSC requires the actual length of stay in days instead of the date of admission and date of discharge. We understand that this is not the most natural and convenient way of specifying the data, but for the sake of robustness, it remains necessary, at least for this release of FLoSC. We hope in future releases this will be addressed.
Therefore, the records in Table 2 is presented as the following in order to be analysed by FLoSC:
in which the column plos (stands for “previous los”) is the length of stay (in days) prior to the start of the data availability period; whereas the colum los is the length of stay (in days) during the data availability period. This data can then be recoded (using coding scheme outlined in the following section) for use in FLoSC. For a resident whose stay starts after the beginning of the data availability period, plos will be zero.
It is worth pointing out that the main issue with using FLoSC is obtaining the right data and reshaping the data into the right format. We appreciate that this process can be demanding as substantial input from a local council’s IT team is usually required. However, once the data extraction and manipulation process that has been developed to work with local information system, the continuing use of FLoSC will be very straightforward.
Furthermore, FLoSC expects the columns of the data are in exactly the same order as in Table 3.
Data coding scheme
FLoSC expects data, such as those illustrated in Table 3, is coded using the following scheme:
For end reason, “3″ represents death in system; “-1″ represents a resident is currently in system at the end of the data availability period; “-2″ represents a resident has left the system alive, e.g. move out of the area, etc. For both plos and los, non-negative number is expected and -999 is used to signify missing value throughout.
An example data file is provided here, which will give a better idea of the format of the data expected by FLoSC. In this example data, we have:
- Resident 0001 was female, admitted to residential care 50 days prior to the start of data availability period, stayed in residential care for 100 during the period before being discharged by death.
- Resident 0002 was female, admitted to nursing care during the data availability period, stayed in nursing care for 400 days before moving out of the area.
- Resident 0003 is male, stayed in residential care for 1500 days before being transferred to nursing care, where he has been staying for 750 days so far, and he is still in nursing care currently.
Macro security warnings
FLoSC is implemented as an Excel file. When starting FLoSC for the first time (by clicking from the Start Menu), you may encounter in Excel a warning message like the following:
This is due to the default macro security level being set to High. In order to run FLoSC, 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 again from the Start Menu. Then you will be presented with the following dialoge window. Click Enable Macros to allow FLoSC to run.
Alway read the warning message carefully before deciding whether it is from trusted source. For Excel files containing macros from untrusted souce, click Disable Macros to prevent it from running. Please note, by changing the macro security level to Medium, this warning dialoge 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.
The first step in using FLoSC is to obtain the relevant data as outlined in the Data requirement section. This will involve extracting 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 is fit for use in FLoSC. This process usually involves working with a database management system. For ease of use, we suggest saving the final data as a comma-separated value (CSV) file.
Once the data is in the right form (e.g. correct coding and the right order of the columns as shown in the example data), start FLoSC from the Start Menu, copy-and-paste the data into FLoSC (including column headings) starting from cell A1. Please be sure to delete all previous data before hand 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.
Then start FLoSC by clicking on the Run FLoSC button located to the right of the data. The user interface of FLoSC will appear.
Please read the disclaimer. At any stage, you can exit the program by clicking Exit and a confirmation dialog box will appear.
Once Next is pressed, FLoSC conducts a check on the data before carrying out any further analysis. The check is to ensure the data is consistent with the requirements, namely:
- column headings and the order of the columns are specified exactly as those shown in Table 3
- values in all columns are consistent with the coding scheme as specified in section 3
If any invalid data value is found, the following warning box will appear.
Users are required to review and correct the affending data values. Often the main causes for invalid data value are
- not following the coding scheme
- negative values in column los and plos (except “-999″, which signifies missing value)
Once the affending data values are dealth with, start the FLoSC interface again by pressing the “Run FLoSC” button and click Next. Again, a check on the data will be conducted. If the data passes the checking process, FLoSC will continue.
In this screen, users are prompted to specify the starting and ending date of the data availability period corresponding to the data used in FLoSC. As outlined in the Data requirement section, since only length of stay (e.g. los and plos) is given in the data, this screen informs FLoSC of the data availability period.
In this screen, users can specify whether the survival model should be fitted using all the data available or only a subset of it. By selecting male or female, a local authority can focus their attention on the different survival patterns that might exist between gender. Whereas specify one type of care (residential care or nursing care) will allow analysis concentrating on one part of the system.
It is recommended that the structure of the model is determined by FLoSC automatically during model fitting. However, if there is a strong reason, users can manually specify the structure in this screen.
As outlined in the Methods section, FLoSC first fits a survival model based on historical records observed during the data availability period, and then, if required, a cost model is constructed atop to provide forecasted cost for known commitment up to the forecast period. If the option is set to No in this screen, FLoSC will fit the survival model only without producing cost forecast for known commitments.
This screen provides a summary of the options specified in the previous screens. Click Run and FLoSC will start the model fitting process.
During the running of the model fitting process, various comments indicating the tasks FLoSC are engaging in will be posted so that users are kept informed of the progress.
Please note that FLoSC fits sophisticated stochastic models to capture the patterns of movement and survival of publicly funded residents in long-term care. Therefore, the model fitting process involves substantial computation, which may take time to complete. Several factors can affect the running time required, such as the speed of the CPU and size of memory of the computer, the amount of data and the complexity of the model, etc. It is recommended that FLoSC is run on a good modern computer.
Once the model fitting is completed (as indicated by the end of the comments), press Exit to exit the interface. The reports of the analysis are produced as additional worksheets.
Probably in many cases, users are utimately interested in the cost forecast of known commitment in addition to the patterns of length of stay. This is done by specifying the Yes option in this screen. Further screens will be presented for additional cost information to be specified.
In FLoSC, the forecast period is by default set to the end of multiples of financial year from the end of the data availability period for up to five years (as shown in the left screenshot of the following). The option “end of financial year” refers to producing forecast until the end of current financial year in the UK. The option “+1 financial year” refers to one extra financial year after the current one. For example, suppose that the end of the data availability period is 3/9/2007 (i.e. now, today), using the option “end of financial year” will produce forecast until 1/4/2008; whereas the option “+1 financial year” will forecast until 1/4/2009.
Within this forecast preiod, cost forecast can be produced at different intervals, such as quarterly, 6 monthly or yearly (as shown in the right screenshot of the following). Because of the amount of computation required to produce the forecast, specifying a long forecast period with small interval will take FLoSC a very long time to run. Therefore, the available choice of forecast interval will depend on the forecast period specified. For example, producing forecast on quarterly basis is only available for forecast period is less than “+1 financial year”; and beyond “+3 financial years”, only yearly forecast will be produced. The availability of choice has been incorporated in FLoSC.
In this screen, users are prompted to enter for each type of care the average weekly price (in pounds) that reflects local situation. The label “+1 year” refers to prices for the following financial year. Only numerical inputs are accepted, otherwise, a warning dialog as the following will appear.
A summary of the options selected is given in this screen. Press Prev to return to previous screens if any option needs to be changed. Press Run then FLoSC will start the model fitting and produce the forecast. During running, comments will be printed to indicate the task FLoSC is engaging in. Please be aware that the running time for producing forecast can be substantial. Please be patient.
Once FLoSC has finished running, press Exit to exit the interface. The reports of the analysis are produced as additional worksheets.