Medicaid Analyst (Waterbury, Vermont)

Annual cost report files are saved into a year-specific folder located on an AHS server. Once saved, the cost data, days data, staffing data and revenue data from the cost reports are linked into several separate summary spreadsheets which provide quick access to all the data points across facilities. The Division also has review templates in Excel modeled on the cost report which are populated automatically from the cost report data and which are used by the audit staff as a tool in their review of the cost report information. The number of data fields and tabs in these spreadsheets is similar to the cost report file.

Other large spreadsheets are used to track the receipt of cost reports and progress of each desk review up to, and through, the appeal process. Separate large spreadsheets are used for the distinct rate setting calculations. Formulas in this rate spreadsheet apply the regulations which dictate how rates are to be set.

The Division also uses a large spreadsheet to track census data which is received at the Division monthly. This data goes back to 1998 and is saved in two separate files. One file has the data from 1998 to 2003 and the other contains the data from 2004 to the present. There are 11 tabs of data, each for a different type of resident day. In this same file, there are 11 tabs of calculations where the percentage of occupancy for each type of bed day and each home is calculated. There are about 3,100 data fields on each of these tabs. The data in these files comes in through links to the spreadsheet where each nursing home*s data is entered by Division of Rate Setting (DRS) staff.

There are numerous other spreadsheets used by the Division. Some are unique to a particular review and some, such as case mix score data, are provided to the Division each quarter by another department. The case mix data is not linked to the rate model. The rate model has a very limited number of links to other spreadsheets. The number of spreadsheets is fairly stable year to year and the same linking is repeated each time the group of spreadsheets is replicated to be used for the following year. Because the links depend on fixed paths, few options are available when files need to be relocated. As the needs for additional disk space grow, the disk storage hardware is upgraded, thus requiring moving the locations of the files resulting in breaking the linked formulas.

The tasks at hand are to analyze and document the data flow, find suitable alternatives for the use of Excel Spreadsheets, and provide the technical documentation for the new design or proposed solutions. This would include identifying options and estimating the effort by Contractor, by Division of Rate Setting (DRS) and by AHS IT to convert the data from current and past spreadsheets to the new solution.

Project Objectives
This Statement of Work will encompass documenting specific portions of the functions performed at DRS. It will be necessary to perform the following tasks.
1. Work with AHS IT and DRS staff to ensure that the spreadsheet repository is up to date before beginning project and after project is complete.
2. Work with AHS IT and DRS staff to identify any duplicate and obsolete spreadsheets and ensure they are not included in the new design.
3. Create the technical documentation for the Division of Rate Setting*s processes including;
a. Tracking receipt of the cost reports received from the nursing facilities, tracking steps in the progress of the review of the cost reports to draft findings, requests for work papers, informal conferences, official actions, requests for reconsideration up to the final findings date
b. Collection, organization and rapid access to all information contained in the providers* cost report submissions
c. Collection, including data entry, organization and reporting of census information, and occupancy percentage calculations with a list of stakeholders who receive monthly census and census trend data from the Division
4. Document the data elements, process and data flow.
5. Propose alternative business process or models to meet rate setting needs.
6. Recommend a suitable replacement for the use of MS Excel Spreadsheets where the functionality meets the requirements of the existing processes.
7. Include more than one recommendation if alternatives exist, limiting to the two most economic solutions.
8. Include report/document archiving and reprint functionality in recommendation based on requirements from DRS.
9. Vendor shall submit technical detail of proposed solutions for AHS IT and DRS to review adjust and approve prior to making any formal presentations.
10. The technical details should include all software recommendations, architectural designs and other technical recommendations and estimates for implementation including an estimate of DRS staff time, work interruption time at the Division of Rate Setting and training costs for DRS staff on the new application.
11. Provide options for converting data from existing spreadsheets to the proposed solutions.

Please forward your updated copy of resume to resumes@tscti.com, one of my senior recruiter will surely contact you.

22nd Century Technologies Inc is an equal opportunity employer and considers qualified applicants for employment without regard to race, gender, age, color, religion, disability, veterans status, sexual orientation, or any other protected factor. We offer an excellent compensation package which includes a generous salary with bonuses, insurance (medical, dental, vision, etc), competitive 401k plan, tuition reimbursement, and more..