FLoSC - User Guide
Contents
1. gt Macros Alt Add Ins Record New Macro Options Security Data Analysis H Visual Basic Editor Alt F 11 A ka GS Microsoft Script Editor Alt ShiFt F11 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 Security Level Trusted Publishers I yery High Only macros installed in trusted locations will be allowed torun All other signed and unsigned macros are disabled High Only signed macros From trusted sources will be allowed to Fun Unsigned macros are automatically disabled Medium You can choose whether or not to run potentially unsafe eee eee ee ree eee teeter eee eee tere eee eee eee eee eee eee eee ee eee eee eee eee Low inot recommended You are not protected From potentially unsafe macros Use this setting only if you have virus scanning software installed or you have checked the safety of all documents WOU Open 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 Security Warning C Program Files FLOSC FLoSC Model xls contains macros Macros may contain viruses It is usually safe to dis2. 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 e 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 Invalid data x I There are invalid data values in the dataset Please check values marked in red and try again Clicking OK will exit the FLoSC interface and all cells with invalid data are highlighted in colour such as those shown in the following a O E F plos gender _ 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 5of9 FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide hAtm FLoSC Forecasting Length of Stay and Cost l _ N x Please specify the start and end of the data availability period From 2001 1999 To 12709 2007 HSCMG Care Services Efficiency Deli
3. there is a strong reason users can manually specify the structure in this screen 6 of 9 FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide hAtm FLoSC Forecasting Length of Stay and Cost Is Forecast For known commitment to be performed Sennen HSCMG Care Services Efficiency Del ivery Exit Prey Mest 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 FLoSC Forecasting Length of Stay and Cost 7 F xj Below is a summary of the model specification Data availability period From 20 01 1999 to 12 09 2007 Data subset criterion Gender both Type of care both Model selection mode automatic Perform Forecast For known commitment na HSCMG Please click Run to proceed to model Fitting Care Services Efficiency Del ivery Exit lt Prey R n gt This screen provides a summary of the options specified in the previous screens Click Run and FLoSC will start the model fitting process FLoSC Forecasting Length of Stay and Cost Comments Starting model Fitting Please wait Starting R Se
4. 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 FLoSC Forecasting Length of Stay and Cost a Eo xj This toolkit is developed by the Health and Social Care Modelling Group HSCMG of the University of Westminster for the Care Services Efficiency Delivery CSED programme of the Department of Health User guides together with tutorials can be accessed at http ww healthcareinformatics org uk Flosc Disclaimer This toolkit is provided to assist local authorities with their planning of social care It should be noted that the results obtained will be dependent on the completeness accuracy and validity of the input data Past patterns of LOS may change as a result of council actions or extraneous Factors Councils will need to make their own judgements on the credibility of the forecasts Risks and opportunities should be identified and demand should be monitored to detect changes in trends HSCMG st University of Westminster DH ean Care Services Efficiency Delivery Ex Please read the disclaimer At any stage you can exit the program by clicking Exit and a confirmation dialog box will appear MS x fis Are vou sure you would like to quit cn Once Next is pressed FLoSC conducts a check on the data
5. FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide hAtm Introduction Methods User Guide lt lt Tutorial Downloads Installation Links Contact Content ia 2 3 4 D 6 General approach Data requirement Data coding scheme Example data Macro security warnings Using FLoSC 1 General approach 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 Past _ Now gt F
6. able macros but if the macros are legitimate you might lose some Functionality Disable Macros More Info 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 back to top 6 Using 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 4of9 FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide hAtm 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
7. ciency Delivery Exit Brey Run 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 FLoSC Forecasting Length of Stay and Cost E E xj Is forecast For known commitment to be performed HSCMG Care Services Efficiency Delivery Exit lt Prev Next gt 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 FLoSC Forecasting Length of Stay and Cost xj Please specify the time period ta Forecast 1 financial vear Please specify the time interwal to Forecast 6 monthly HSCMG Care Services Efficiency Delivery Exit lt Prev Next gt 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 e
8. e 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 Table 3 id gender type of care plos los end reason 001 female residential care 160 1231 to nursing care 001 female nursing care 0 749 NA 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 diring the data availability period This data can then be recoded using codi
9. er this errar for the following reasons 1 Pamo security be set boy Very High and the aoplcetion encounters 4 signed macro but the macro wat automatically disabled Use the Foleeaing procedure bo enable the maoro 1 Select the Tools mani option and then select hero and Security In the resultng Security dalog set the security level bo High by clicking She High radio button zZ dez the file and ery other instances of the application currenti running on the computer close al aplication that alzo use the application wou are ourrenth running 3 pen the file agen and examine the certificate of trust details and set the Always brust macros From this publisher Dox if you trust the certificate issued by the publisher 4 ick the Enable button bo alow the macro to run 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 30f9 FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide htm Security as shown below E Microsoft Excel Booki Ba Eile Edit wiew Insert Format Data Window RExcel Help Adobe PDF DEURIATZ GB KY Sr 7 ez ao A ie a Research Alt Click EE e a E el a Shared Workspace Share Workbook Protection Online Collaboration Formula Auditing Solver
10. h 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 back to top 2 Data requirement 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 appible and destination of 10f9 FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide htm discharge including death if appible 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
11. los and los non negative number is expected and 999 is used to signify missing value throughout back to top 4 Example data 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 1 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 2 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 3 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 back to top 5 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 Picropolt Gece z i xj Macros are disabled because the security level is set to High and a digitali signed Trusted Certificate is not attached bo the macros To run the ee change the security lewel to 4 lower setting not recommended or request the macros be signed by the author using a certificate issued by a Certificate Authority Hide Help lt lt Open in Help Window You may encount
12. 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 Table 1 id gender date of type of care name of care date ol end reason admission home discharge 001 female 2001 10 23 residential emei 2003 06 29 Nange care care home 001 female 2003 06 30 residential home b 2005 08 14 Change type of care care 001 female 2005 08 15 nursing care home_b NA NA 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 in
13. nd 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 8 of 9 FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide hAtm only yearly forecast will be produced The availability of choice has been incorporated in FLoSC 1 Financial year 6 monthly end of Financial year quarter 6 monthly financial years earl 5 Financial years 4 financial years 5 Financial years Once the forecast period and forecast interval are set press Next to continue FLoSC Forecasting Length of Stay and Cost a xj Please specify the weekly price fin pounds of each type of care Residential Tare Nursing Care Current Financial year 400 500 Year 1 450 600 HSCMG Care Services Efficiency Delivery Exit lt Prey Next gt In thi
14. ng 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 2o0f9 FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide htm back to top 3 Data coding scheme FLoSC expects data such as those illustrated in Table 3 is coded using the following scheme gender code meaning 0 female 1 male 999 missing value type of care code meaning 1 residential care 2 nursing care 999 missing value end reason code meaning 1 to residential care 2 to nursing care 3 died 1 still in care 2 left the system alive 999 missing value 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 p
15. op Last updated 2007 10 10 9g of9
16. rver Loading Data Preparing Data Running Model Fitting WORKING May take upto 10 minutes to run depending on model settings HSCMG Care Services Efficiency Delivery Exit Prey Run gt 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 7 of 9 FLoSC Forecasting Length of Stay and Cost http www2 wmin ac uk hscmg FLoSC userguide htm FLoSC Forecasting Length of Stay and Cost F xj Comments Generating and Writing Fitted results Generating plot data Writing plot data For Residential Care Writing plot data For Nursing Hore Generating Reports Generating Report General Info Generating Report Summary Info Generating Report Fitted Results Generating Report Technical Info Model Fitting complete Please click Exit to Finish HSCMG Care Services Effi
17. s 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 warning ae A Please enter required unit cost amounts Once the required weekly prices for care are entered press Next to continue FLoSC Forecasting Length of Stay and Cost E i xj Below is a summary of the model specification Data availability period From 20 01 1999 to 12109 2007 Data subset criterion Gender both Type of care both Model selection mode automatic Perform Forecast for known commitment ves Forecast period 1 Financial year Forecast interval 6 monthly HSCMG Please click Run to proceed to model Fitting Care Services Efficiency Delivery Exit lt Prev Run gt 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 back to t
18. terested 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 Table 2 id gender date of admission type of care date of discharge end reason 001 female 2001 10 23 residential care 2005 08 14 to nursing care 001 female 2005 08 15 nursing care NA NA 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 th
19. uture ioe a0 do BO po l Time lt Data availability period gt lt Forecast period gt 1 4 2002 wO 2007 1 4 2008 Figure 1 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 approac
20. very Exit lt Prev Next gt 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 FLoSC Forecasting Length of Stay and Cost N al xj Please specify the data to perform the analysis Subset of the data Gender Type of care Both Both Male Residential Care Female Nursing Care HSCMG Care Services Efficiency Delivery Exit lt Prey Next 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 FLOSC Forecasting Length of Stay and Cost E i es Please specify the model structure Manual specification Residential Care Nursing Care One state amp amp One state Two states Two states HSCMG Care Services Efficiency Delivery Exit lt Prev Next gt It is recommended that the structure of the model is determined by FLoSC automatically during model fitting However if
Download Pdf Manuals
Related Search