QPR User Manual - Texas Department of Aging and Disability
Contents
1. Using the information provided by the AAA the Department compiles statewide performance information The Department uses this information to Rev1 0 November2015 Page 3 of 39 e Prepare quarterly and annual reports regarding actual performance for the Legislative Budget Board LBB and Governor s Office of Budget and Planning GOBP including analyses of variance e Prepare annual reports regarding actual performance for the Administration on Community Living ACL e Prepare internal reports for o Evaluating agency programs to clearly accurately and timely report conditions and outcomes affecting agency programs o Conducting statistical sampling of Area Agencies on Aging records o Measuring the impact of existing and proposed Texas Administrative Codes TAC o Measuring the impact of existing and proposed state and federal legislation on Department programs o Defining problems and developing recommended solutions o Researching technical questions of regulatory compliance with applicable state and federal statutes and regulations The Department uses individual AAA reported information to prepare the Aging Services Analysis Workbook ASAW for use by the Department and the AAA to e Reconcile expenditures reported on the QPR to the approved budget funds requested on Request for Payments RfRs and notifications of funds available NFAs e Evaluate the services provided to ensure requirements are being met and to analyze numeri2. Department cannot ensure all reports will print correctly on all AAA printers AAAs may use the Excel page setup function to change formatting of reports for their printer requirements Quarterly Reports Preview Quarterly Report The Preview Quarterly Report Menu is used to update and view QPRs prior to submitting reports to the Department QPRs may also be submitted by e mail through the Preview Quarter Report Menu Selecting the Preview Quarter Report button brings up a pop up menu Preview Quarterly Performance Report wl Preview Quarter Performance Report Select Quarter OK Cancel Select the quarter from the drop down menu Preview Quarterly Performance Report Preview Quarter Performance Report Select Quarter ch PO ie ee ee Quarter 2 Due 03 20 15 Quarter 3 Due 06 20 15 Quarter 4 Due 09 20 15 Closeout Due 11 30 15 OK If you have accessed the QPR Fy yr workbook using the Update QPR function prior to previewing the quarterly report the quarterly report will open and update If you have not accessed the QPR Fy yr workbook using the Update QPR function selecting OK will display a decision box Rev1 0 November2015 Page 24 of 39 Microsoft Excel This workbook contains links bo other data sources IF you update the links Excel will attempt to retrieve the latest data e IF you don t update the links Excel will use the previous information Wote that data links can b
3. LMS Basic 04001707 03 5140 Final Oct October ba Title Ill D Medication Management State General Revenue Title I E Match State General Revenue Other State Highway Fund OO06 Transportation cb e mn a Tite HI Tite IRE pe pit IFAS pia EMS Basic 040 OF 03 81 1083 par Title D Medication Management z State General Revenue Title I E Match Lal State General Revenue Other z State Highway Fund 0006 Transportation E IW dk OFS Administration Ready The process for locking quarters is the same as unlocking Quarters can be locked and unlocked as needed by the AAA Rev1 0 November2015 Page 36 of 39 Exiting the QPR Menu System The QPR menu system should always be closed using the Exit button on the main menu This process properly saves and closes each open worksheet to make sure no modifications are lost Developer Add Ins 5 gt 2s Connections AJ K Clear g SN EP Soros gt Ff Properties Ye Reapply nom From From From ec EI Refresh H Sort Filter T Textto Remove Data Consolidate What If Access Web Text Sources Connections Ally S Edit Links wf Advanced Columns Duplicates Validation Analysis Get External Data Connections Sort amp Filter Data Tools fy14 version 1 0 DADS Access amp Intake AAA Quarterly Performance Report Ges ce SC Selecting the Exit button will generate a decision box for each worksheet that is open AS Do you want to save the
4. QPR Fy yr workbook e Main Menu returns the user to the main menu xx Mo G D a e QprMenu xls Compatibility Mode Microsoft Excel o B File Home Inset Pagelayout Formulas Data Review View Developer o E ei E SR sg C en a Delete v A x MK Number F Styles Cells Editing DADS Access amp Intake AAA Quarterly Performance Report Fiscal Only I Program Only OK Main Menu v M 4p M Man Neng Update QPR lt Maintenance lt amp 3 O oO oo Oeo M E ady 4 a D gt m Snapshot of QprMenu Excel document Update QPR tab Rev1 0 November2015 Page 13 of 39 Selections on the Maintenance Menu e Lock Unlock Quarter Totals used to lock or unlock quarters to prevent data entry to other than the current quarter e Add Clear Funding Source used to add a new funding source to the QPR Fy yr workbook and quarterly workbooks e Add Clear New Service used to add a new service to the QPR Fy yr workbook and quarterly workbooks e Main Menu returns the user to the main menu Ei hr File als 15 ac aye C a ajor 4d Ins E D E B Connections Dp Y WK Clear ES SN RM ee y a Properties Zia Yo Reapply El From From From From Other Existing Refresh Sort Filter Textto Remove Data Consolidate What If Access Web Text Sources Connections Ally Edit Links W Advanced Columns Duplicates Validation Analysis Get External Data Connections Sort amp Filter Data Tools v
5. Type Source and Row Number will b provided by DADS Select Fund Type Fund Source Marne Available Row Numbers H Ak WS MainMenu Update OPR Maintenance Annual Reporting Ready Selecting OK will display a pop up decision box to allow the user to ensure the information selected 1s correct before proceeding Add new Funding Source Eh Funding source Department Provided will be added to Row 26 Do you want to wt continue Rev1 0 November2015 Page 29 of 39 If No is selected the user is returned to the Add Clear Funding Source Menu If Yes is selected the information is added to all workbooks and an information box appears to confirm the action was completed successfully Microsoft Excel The user selects the OK button and is returned to the Add Clear Funding Source Menu The user can either add another funding source or exit the Add Clear Funding Source Section to the Maintenance Menu The Add Clear Funding Source Menu provides the user with the option of clearing a funding source added by the user The user will use this selection to correct errors to previously entered information by deleting the information The information can then be correctly entered Important Removing a funding source does not remove any information reported for the funding source through the Update QPR process If the User selects a row previously added a decision box is provided showing the row number and funding source informa
6. changes you made to QprFy14 xlsm If you dick Don t Save a recent copy of this file will be temporarily available Selecting Yes will save all changes made to the worksheet and close the worksheet Selecting No will close the worksheet without saving the changes Rev1 0 November2015 Page 37 of 39 A Do you want to save the changes you made to QprMenu xlsm If you dick Don t Save a recent copy of this file will be temporarily available Learn more keen Note Selecting Yes for the decision box to save changes to the QprMenu xls will save the current selections on each of the menus Attachment A QPR Service Activity Reporting Matrix Adult Day Care es es Assisted Transportation Ys o es Care Coordination Yes es Caregiver Education amp Training Yes Ys Caregiver Respite Care In Home Yes s Caregiver Respite Care Institutional Yes Ne O Caregiver Respite Care Non Residential Yes es Caregiver Respite Care Voucher Yes PN Caregiver Support Coordination Yes es O Chore Maintenance Ys es Congregate Meals Yes es Data Management No es EmergencyResponse Yes es Escot es es Evidence Based Intervention Yes es Health Maintenance Yes es Health Screening Monitoring Yes es Home Delivered Meals Yes es Homemaker es es Homemaker Voucher Yes LN Income Support Ys es Instruction and Training Yes es Legal Assistance Age 60 amp Over Yes es Legal Assistance Under Age 60 _ Yes es M
7. in the nutrition education columns of the QPR o All nutrition education units and persons regardless of how the expenses are reported must be reported in the nutrition education columns e Area Plan Performance Activity o Unduplicated Count of Total Persons or Estimated Audience Size On a monthly basis AAAs will report only clients who are receiving services paid for with funds received from DADS for the first time during the state fiscal year Client counts are required for all services that have a unit of service designated Unit of service designations may be found in the Service Definitions for Area Agencies on Aging on the DADS website For services for which the AAA does not maintain a client intake the client counts are estimated Attachment A QPR Service Activity Reporting Matrix of the QPR User Manual available on the AAA secure FTP site in the Shared FY16 QPR Templates folder designates which services require estimated counts The area agency on aging must have a process for estimating the number of clients Processes based on units of service must include a provision for reducing counts for follow up units o Total persons served with non Title I E funds is the unduplicated count of persons served with all funding sources other than Title M E o Total persons served with Title II E non GOECSC funds Grandparents and Other Elderly Caregivers Serving Children is the unduplicated count of caregivers serving care re
8. projected performance for the remainder of the state fiscal year This worksheet was developed to provide the information necessary to comply with this reporting requirement This worksheet can also be used by the AAA as a planning tool to compare planned performance to actual performance Quarterly Performance Report Fiscal Only senior Center Operations shopping Telephone Reassurance Transportation D R Transportation Fixed Route Visiting Volunteer Placement Voucher Caregiver Respite Care oucher Homemaker service Funding Source Summar Performance Projections Rev1 0 November2015 Page 22 of 39 The selection of the service Performance Projections does not require the AAA to select a month When the Performance Projections worksheet opens a pop up box will appear requiring the user to select whether or not to update the worksheet Allowing this update is necessary to link to the AAA performance projections The calculations on this worksheet are based on the number of quarters reported The AAA must enter the number of quarters completed each quarter 1 DADS AAA A amp I Performance Report 2 West Central 3 83128 4 Projections e Return to Menu The actual expenditures unduplicated persons and units shown on this worksheet are linked to information reported by the AAA on other worksheets in the QPR The Current Year DADS Accepted Projections are linked to a separate workbook AAAPerformance20nn 20nn xIs included
9. the quarterly state fiscal year and federal fiscal year totals Expenditures Allocated to DADS Funding Sources Current Fiscal Year Expenditures Title Ill E Tithe IC Title Il 22 Tithe Il EAP Title Vil OAG CMS Basic 04001 f0F 050541 06 MSIF ite f ES EH E EH WE ER EECH Ge WE EH EH BE BECH EN TitleM D P me o A A o EAP a a NEE EN AG o o y d e EH EH a es es BEEM jf Rev1 0 November2015 Page 20 of 39 The Service Funding Source Summary report is a listing of the total reported expenditures by month for each service OILLL Administration 9 Ombudsman Inform Ref amp Assist Care Coordination Legal assistance 60 amp Older Legal Awareness Caregiver Education amp Training Caregiver Program Development Caregiver Support Coordination Congregate Meals Home Delivered Meals 19 Transportation Demand Response Residential Repair Title III Homemaker Personal Assistance Health Maintenance Health Screening Nutrition Education Emergency Response Saving Performance Information The QPR Menu Exit button requires the user to decide if updated information in the open workbooks will be saved It is recommended in addition to saving information during the exiting process that the user save information on a regular basis while using the System This is accomplished by using the Microsoft Excel File Save
10. unlock quarters through the Maintenance Menu e Exit used to save and close all QPR system workbooks XJ a P al Home Insert Pagel Formulas B E D IE B Connections AJ BA K Clear BS Properties Yo Reapply Textto Remove Data Consolidate What If Group Ungroup From From From From Other Existing sh i Z Sort Filter Access Web Text Sources Connections S Edit Links W Advanced Columns Duplicates Validation Analysis Get External Data Connections Sort amp Filter Data Tools M fl fy14 version 1 0 Quarterly Performance Report lt Snapshot of QprMenu Excel document Main Menu tab Rev1 0 November2015 Page 12 of 39 Selections on the Update QPR Menu e Fiscal Only used to enter view and print the Expenditures Allocated to DADS Funding Source section on the QPR Fy yr workbook e Program Only used to enter view and print the Area Plan Performance Activity section of the QPR Fy yr workbook which includes total units served average unit rate calculations other funding sources and Title II E caregiver information e Select Service will allow the user to select the service for data entry viewing or report printing The drop down list of services is in alphabetical order The service list also contains selections for service funding source summary information and a total page e Select month allows the user to select the period to be updated e OK processes all of the above selections and opens the
11. Oct Cor Final Nov Cor Final Dec Cor Final Jan Cor Final Feb Cor Final Mar Cor Final Apr Cor Final May Cor Final Jun Cor Final Jul Cor Final Aug Cor September Federal FY Final in the QPR Fy yr workbook QPR Month Totals A separate workbook is provided that includes a report that will show State and Federal YTD totals by funding source and service The total values within the workbook are linked to the QPR Fy yr workbook This workbook was created at the request of the AAAs but is not accessed through the QPR Menu workbook like many of the other components Instead users should open the file directly and the linked information will populate automatically QPR System Menus All of the workbooks are accessed through the use of a series of menus in the QPR Menu workbook Open the QPR Menu workbook to use the system IMPORTANT When the workbooks are opened your computer system may require a response as to whether or not you want to enable macros For the QPR system to operate correctly you must allow the system to enable macros The QPR Menu workbook contains three worksheets or menus Main Menu Update QPR and Maintenance Rev1 0 November2015 Page 11 of 39 Selections on the Main Menu worksheet e Update QPR used to access the QPR Fy yr workbook e Preview Quarter Report will allow the user to select a quarterly report to view or e mail to DADS e Maintenance used to add a new funding source or new service and lock or
12. Texas Department of Aging and Disability Services Access amp Intake Area Agencies on Aging Performance Reporting Fiscal Year 2016 Rev1 0 November2015 Page 1 of 39 Table of Contents EEN 3 PUED OSC Orie WAMU alliance onsets acerca E 3 The Purpose of Performance EE EEN 3 How Reported Performance Information is use 3 Verification of Reported Performance Intormaton 4 Certilication of Reported Information viccak a asnteiciieken tiene ie eae 4 IMTOFMATON e E EE 5 Federal Fiscal Year FFY and State Fiscal Year GE S EE Reported Free ee EE 8 Storing and Managing Workbooks ssesssssseessssssssseeerssssssssseerrsssssssseerresssssseceressssssseeeeeeo 9 Work DOOK OVVIO Wee a Se Cee Re ee eR Pee 10 QPR System E 11 Enter Tote ee Ee E 15 First Time Wsecor the ME 15 Entering Performance ntormati oM asbl E iee sues berate sand Seed ed ERR e 16 Savine Performance Norma On E 21 COmMmOTIEGUASSEL Le EIERE a E 22 Per orimance ege EE 22 Repon e Ae LE 23 Quare rl YR PONS eoe R TR 24 Eeer 24 SE EI caine tee ce ere ne 26 Submitting Quarterly REPOIts lt 1 Sccnsaiers a E a E 26 end dee e E Te 21 Add Cleat Fundine SOUC Eet e 21 Add Clear New Servite E 31 Lock Unlock O arter Totals sioria a R a AE 33 Exiting the OPR Menu S VStem E 37 Attachment A QPR Service Activity Reporting Max 38 Rev1 0 November2015 Page 2 of 39 Performance Reporting Purpose of the Manual This manual has been developed to provide Are
13. a Agencies on Aging AAAs with an overall understanding of the Department s Quarterly Performance Reporting QPR System This manual seeks to e Identify the Department s expectations for AAA management s involvement with performance reporting e Explain the role of performance reporting within the current Budget Performance Payment process e Provide basic information about performance reporting such as o Required reporting information o Reporting due dates o Reporting process e Provide the user with instructions on using the QPR System This performance manual replaces all previously distributed performance reporting manuals issued by the Department The Quarterly Performance Reporting System referenced in this manual is a series of Microsoft Excel Workbooks created and maintained in the Microsoft Office 2010 platform It is assumed users of this manual and the QPR System files are proficient with Microsoft Office Excel versions 2003 2013 The Purpose of Performance Reporting Performance Reports are a report of the actual performance of the contract for each month of the contract year Performance reports are a vital component of the Department s state and federal reporting AAAs must report actual contract performance for each month of the contract regardless of when invoices are paid Units and the cost of the units must be reported within the same month and on the same QPR How Reported Performance Information is used
14. ary and Performance Projections worksheets When the QPR Fy yr workbook opens the cursor will be positioned in the title row of the column for the period selected E eee Loge eter ele algi i EN Pie ER fee freet Foni Iech Con Wirde Ee rz amp ceo ere pe ee ea aoe 8 DADS Access L maie ARA uated Perhmance Repo Ia 4 kb KN Pee hiie Inna aaee l raua prg Expenditures Allocated to Feb Q3 Feb Q4 Final Febj February State Federal 10 DADS Funding Sources Bical W i B i Wf eeng weng t current Fiscal Year Expenditures Title III B EE Title WC a a E S E Tite C2 Title LD Tite M E Ee Title VI EAPE P Title VIFKOAGE By selecting the OK button all of the above selections are processed and the QPR Fy yr workbook is opened Rev1 0 November2015 Page 17 of 39 Only the service and month selected will be displayed with the month state fiscal year to date and federal fiscal year to date totals Expenditures Allocated to Feb Q3 Feb Q4 Final Feb February State Federal DADS Funding Sources February Cor Cor Cor Total YTD YTD Current Fiscal Year Expenditures H a a Title III B 2 500 2500 2500 2 500 Tite W Ct nh Title IC S e S e S Title O Tile WE Pe Totals are immediately updated allowing the AAA to verify information is correct before proceeding LE GV ae TI AC TI ap BE BI epa E HEEN Expenditures Allocated to Feb Q3 Feb Q4 Final Febj Februa
15. ble to the AAA on the Department s SecureFTP website https sftp hhs texas gov in the Shared folder The following workbooks will be provided by the Department 1 QPR Menu 2 QPR Fyl6 3 QPR 2016 Quarter 1 4 QPR 2016 Quarter 2 5 QPR 2016 Quarter 3 Rev1 0 November2015 Page 9 of 39 QPR 2016 Quarter 4 QPR 2016 Contract Year Closeout Qpr Month Totals AAA Performance 2016 2017 oe EC The AAA must determine where the workbooks will be stored If several staff members will use the workbooks the AAA may choose to store the workbooks on a network drive If only one staff member will use the workbooks they may be stored on a stand alone computer Regardless of which method the AAA chooses for workbook storage DADS strongly recommends a separate directory be created and used exclusively for these workbooks and backed up on a regular basis The workbooks must not be stored in the same directory as prior fiscal years workbooks The naming of the directory will not affect the usage of the workbooks and any naming convention used by the AAA for the directory will be acceptable to DADS All nine 9 workbooks must be located in the same directory Failure to properly store workbooks may cause the automatic updating of worksheets to fail IMPORTANT The names of the nine files must not be changed Changing the name of the workbooks will cause a break in the linkage of the copy function between the workbooks DADS has named the four Quart
16. c information regarding service expenditures persons served and units provided Verification of Reported Performance Information The Department ensures the accuracy of reported information contained in the performance report on a regular basis through internal processes The internal process tests the accuracy and completeness of documentation at the time of reporting For this reason AAAs may not use estimates or projections when reporting performance AAAs are encouraged to keep complete documentation on file to support all reports submitted to the Department The Department may review for accuracy reported performance information supporting documentation and reporting procedures during on site monitoring Certification of Reported Information As part of the annual contract closeout process AAAs must certify the units and expenditures reported to the Department for Legislative Budget Board LBB reporting The certification is based on state fiscal year information Rev1 0 November2015 Page 4 of 39 Information Reported Expenditure and performance information is reported monthly based on actual contract performance for each month regardless of when invoices are paid Expenditures Allocated to DADS Funding Sources This section identifies all allowable funding sources as described in the instructions for preparing the budget for each allowable service On a monthly basis the AAA will report expenditures based upon contract perf
17. cipients 19 years of age or older o Total persons served using Title UL E GOECSC funds is the unduplicated count of caregivers serving care recipients 18 years of age or younger o Number of Entities Providing Service by Contract On a monthly basis AAAs will report only the entities providing the service by contract for the first time during the state fiscal year o Number of Entities Providing Service by Vendor On a monthly basis AAAs will report only the entities providing the service by vendor agreement for the first time during the state fiscal year Rev1 0 November2015 Page 6 of 39 o See Attachment A QPR Service Activity Reporting Matrix e Total Units Served Performance is reported as units inquiries contacts and placements Please refer to the Instructions for Preparing the Budget Summary for a description of allowable activity and unit definition o Total DADS Units Purchased with non Title III E Funds report only units supported with non Title II E funding source o Total Units Purchased with Title I E non GOECSC funds report only units supported with Title I E funding source that were not used for Grandparents and Other Elderly Caregivers Serving Children GOECSC o Total Units Purchased with Title IH E GOECSC funds report only units supported with Title HI E funding source that were used for Grandparents and Other Elderly Caregivers Serving Children GOECSC o Total Match Units Supported with Local Funds requi
18. e used to access and share confidential information without your permission and possibly perform other harmful actions Do nok update the links iF wou do nok trust the source of this workbook If Don t Update is selected the quarterly report will open without updating any changes made to the QPR Fy yr workbook since the last time the quarter was updated and saved If Update is selected the QPR will be updated with all changes made to the QPR Fy yr workbook The QPR must be reviewed to ensure all information for the quarter has been entered If all of the information is not complete return to the Main Menu select Update QPR and enter the appropriate information After updated information is entered select the Preview Quarter Report option and ensure the report is correct The header rows of the worksheet provide the option of returning to the Main Menu i H I ES G H l J K DADS AAA A amp I Performance Report Month Ending Septembe Return to Menu o dministration Sch Information coordination Caregiver Coordination Expenditures Allocated to DADS Funding Sources E ZS E Ka E E lt Ki E pe Title MB pif ge tel WEE pa Title ct Title A S T E o WE 6 Title WE Pe peep ewe G A S S A S a 18 Title VILOAG Te pif OMS Bassi 04001 07 osana PP ER See ee Se Se ees al o Je Medication Management Pe p22 State General Revenue Title LE Match sf et TI H Lal State Ge
19. ental Health Services Yes es NutritionCounseling Yes es O NutritionEducation Yes des Ombudsman Noes Participant Assessment A amp A Yes es Participant Assessment NS Yes es Personal Assistance es des Rev1 0 November2015 Page 38 of 39 Physical Fitness Yes es Yes po ves ResidentialRepar sf Yes es Senior Center Operations o Yes YO Telephone Reassurance Yes Yes Y Transportation D R ve es Transportation Fixed Route Uer Yes Transportation Voncher Lie E O Visiting Volunteer Placement Rev1 0 November2015 Page 39 of 39
20. er QPR and contract year closeout workbooks for direct uploading of data into DADS worksheets and databases Any changes in the names of these workbooks or worksheets within any of the workbooks will delay processing Workbook Overview The QPR System consists of a series of linked Microsoft Excel workbooks All of the workbooks are accessed through the use of a series of menus in the QPR Menu workbook IMPORTANT When the workbooks are opened your computer system may require a response as to whether or not you want to enable macros For the QPR System to operate correctly you must allow the System to enable macros QPR Menu The QPR Menu workbook is linked to the QPR Fy yr and to each of the quarterly and closeout workbooks This workbook is used to update QPR information view or print reports preview quarterly reports perform maintenance on the workbooks and exit the QPR System QPR Fy yr The QPR Fy yr workbook is linked to the QPR Menu workbook and is used to store the AAAs entered information The QPR Fy yr workbook will not be submitted to DADS unless there is a problem that requires the Department to verify the AAA s recording of the data The QPR Fy yr workbook contains one worksheet for each allowable service an additional worksheet that may be used for a new service a total worksheet and a service funding source summary worksheet Rev1 0 November2015 Page 10 of 39 QPR Fy yr Quarter n A separate workbook is p
21. erre Ier ees ES D BEE FE AC EE Web Page Preview Page 2et p Print Area is sh ORSO Spstem pealyst Conument Metten ae d D WAAVDOALAAAN AA Provider Information zb St Routing Bekset K i Odara Foder i i RSE Fee Ke ansas oe res E MT Lina alice e vw e v The other method requires the user to exit the QPR System and attach the appropriate QPR file to an e mail To use this method the user selects the appropriate QPR file s from the directory and attaches it to an e mail Rev1 0 November2015 Page 26 of 39 The AAA Report Due Date Schedule provides a listing of the QPR and closeout report due dates The schedule is provided to the AAA prior to the start of each contract year SFY Quarterly workbooks must be submitted by e mail to the Department s Reports mailbox at AAA Reports dads state tx us To readily identify the submission in the Reports Mailbox the e mail subject line should contain the AAA s identification number QPR and the quarter ending date e g 83104 QPR 5 31 07 Workbook Maintenance The Maintenance Menu is used to perform routine changes to the workbooks including adding or clearing a new funding source adding or clearing a new service locking or unlocking a quarter The Maintenance Menu is accessed by selecting the Maintenance button on the Main Menu Add Clear Funding Source The Department will notify the AAA when new funding sources are to be added The inf
22. ess amp Intake AAA Quarterly Performance Report Add Clear New Service Selecting OK will display a pop up decision box to ensure the information that was entered is correct before proceeding Rev1 0 November2015 Page 32 of 39 Add new Service d The new service name New Mame will be added to the QPR file and all Quarter Flles Do YOU want to continue Important The selection of the Yes button will update all of the worksheets and save the workbooks If you have entered information using the Update QPR process that you do not want saved select the No button If No is selected the user is returned to the Add Clear New Service Menu If Yes is selected all of the workbooks are updated with the new service name all of the workbooks are saved with the new information and an information box appears Microsoft Excel The user selects the OK button and 1s returned to the Add Clear Service Name Menu The Add Clear Service Name Menu allows the user to rename the spare worksheets repeatedly If the user has selected the wrong spare sheet or has incorrectly entered the service name the user repeats the process to rename the worksheet Important Renaming a service worksheet does not remove any information entered on the worksheet through the Update QPR process Lock Unlock Quarter Totals One of the most common data entry errors in the QPR process is the entering of information in the wrong quarter To reduce the p
23. i E Main Menu A27 DAD amp Intake AAA RR S Access Performance Lock Unlock Quarter Totals Add Clear Funding Source Add Clear New Service Snapshot of QprMenu Excel document Maintenance tab Rev1 0 November2015 Page 14 of 39 Entering Information First Time Use of the QPR The first time the QPR Menu workbook is opened the AAA must enter the AAA name and AAA index number on the Main Menu The AAA index number is five digits and begins with 831nn If you are unsure of the AAA index number please contact the Help Desk at AAA Help dads state tx us The AAA name and AAA index number entered on this menu will be used by the sub menus workbooks and reports DADS Access amp Iprake AAA Quarterly Perfofmance Report All of the quarters are locked in the QPR Fy yr workbook when released to the AAA AAAs are encouraged to only unlock the current quarter and to lock each quarter after submitting the quarterly report By doing this the AAA will prevent data entry to other sections than the current quarter Please refer to page 33 of this manual for instructions on locking and unlocking quarterly totals Rev1 0 November2015 Page 15 of 39 Entering Performance Information To enter performance information select Update QPR on the Main Menu This will access the Update QPR Menu The menu provides the user the option of limiting the visible area of the report This is accomplished by selecting either Fiscal Only
24. in the QPR package The AAA must enter monthly projection targets for every performance measure prior to submitting the first quarter QPR Totals are immediately updated allowing the AAA to verify information is correct prior to proceeding Each quarter after the AAA has entered the monthly actual performance data the performance projections for the quarter should be updated to match the actual performance The AAA should then review and if necessary revise the planned performance for the remaining months of the fiscal year The worksheet includes calculations of variances for each of the performance measures Calculations are immediately updated allowing the AAA to compare planned projection activity to DADS accepted projections The calculations show the expected variance based on actual year to date reported and activity planned for the remainder of the fiscal year This worksheet is linked to the quarterly workbooks and the information provided will be updated in the quarterly workbooks when the AAA updates the quarterly report Report Printing All of the screen views available through the Update QPR Menu have been formatted to print on letter size paper allowing multiple pages with a title column on the left of the Rev1 0 November2015 Page 23 of 39 page This eliminates the need for a separate report menu The reports can be viewed using the Excel print preview feature Because of variations in printer setups and requirements the
25. l year and the AAA contract year begin with the month of October and ends with the month of September The state fiscal year begins with the month of September and ends with the month of August To use the performance information for both federal and state reporting the performance workbook includes performance reports for thirteen months The performance workbook begins with the September report for the prior federal and AAA contract fiscal year which is the first month of the state fiscal year The September closeout report from the prior contract year must match the September information reported in the following year s performance report If after submitting the September report in the first QPR it is determined September must be adjusted to complete the prior year s contract closeout the Department will adjust the first QPR to match the year end closeout The AAA must also adjust the information for September in their Quarterly Performance Report workbook to match the final year end closeout The reporting process does not require the AAA to transmit a corrected September report to the Department The performance workbook ends with the September report for the end of the current federal and contract fiscal year Correcting Reported Information Rev1 0 November2015 Page 8 of 39 AAAs are required to correct reported monthly information when the variance between the actual performance and the reported performance for any service for any mo
26. neral Revenue Other JT L8l State Highway Fund 0006 Transportation A Aoo o o T A A A A EZE E EE A E SD G H e SS d A d H C a LL eee BEE RE RE EE EE ee EE H EE E RE E EG EE DE RE psf ll E WE eee ssf RE A es A A E E E eee RE ee eee eee Prin iceal Year Carter 1 Rev1 0 November2015 Page 25 of 39 Save Reports The QPR Menu Exit button requires the user to decide if updated information in the open workbooks will be saved It is recommended in addition to saving information during the exiting process the user save information on a regular basis while using the System This is accomplished by using the Microsoft Excel File Save process also accessed via the Ctrl S keystroke combination In all cases the user should ensure the QPR is reviewed and saved prior to submitting the report to the Department aE Ee EE Hat Ak rogoit Leoel JET RG POR er feat Demure LCekieres jie H E 2 AAA SOO AAA M AEA Prowler information ba e ee WENO E SETS Ke Kee SENT Submitting Quarterly Reports There are two methods for submitting QPRs The first method uses the Microsoft Excel File Menu to send the report as an attachment via e mail Depending on your agency s e mail Internet Service Provider ISP this option may or may not work CEEISEELEREL EE E Microsoft Excel OPR2006Quarter2 xls ROUPSIOASONS stem Analyst Oocumentation OPR Dr i slat tl a aw Loosing E D D E H D v
27. nths 5 The Performance Report workbook contains a separate column for each possible reporting period Each month may have one to five reporting columns The number of reporting columns is dependent upon how many quarters remain in which the month can be corrected This example shows the possible reporting columns for the month of October A e c WW E F G H 1 DADS AAA A amp l Performance Report 3 o Administration 5 Expand Columns Month Totals 6 Return to Menu Locked Locked Locked Locked Expenditures Allocated to Septembe Oct a2 Oct a3 Oct o4 Final Oct October 40 DADS Funding Sources r October Cor Cor Cor Cor Total 141 Current Fiscal Year Expenditures ES Title B III Et Title VIFEAPT a 18 Title VILOAG TEE i T O 23 Tite I E GOES EE EE 24 eh LS eS Seas Bea Saas ES ee el Fleeg 26 Le ET E ET E 27 a ee Mee Fee ee ee ee 28 Additional Special Awards E WE i le ee 30 SUA Titem B ff IN 4 H Administration 2 In addition to submitting corrected performance information on the QPR on the established due dates AAAs may submit corrected performance information as directed in technical assistance memo AAA TA 401 Storing and Managing Workbooks Prior to the due date of the first QPR the Department will provide the AAA with the workbooks required for performance reporting The workbooks will be availa
28. ocal Funds Total Other Eligible Units Supported with Local Funds Total Units Purchased with Program Income Funds Tetai fits Avg Unit Rate Calculation Average DADS Cost per Unit Average Cost per Match Unit Average Cost per Other Eligible Unit Average Cost per Program Income Unit All Program Income Collected Fy zooy All Program Income Expended F200 All Local Cash Match Expended All Other Local Funds Expended All In Kind Reported All Other Federal Espended 86 Tithe Ill E Caregiver Information Program Income Collected FY2007 Caregiver Serving Elderly 8S GrandparentfElderly Serving Children 30 Total Other Funding Source Expended Cash Ont Ein Caregiver Serving Elderly GrandparentfElderly Serving Children Page 16 of 39 The menu also requires the selection of a service This selection is a drop down list of all services in alphabetical order In addition to the current services a reserved worksheet is available for future use The service list also contains selections for service funding source summary information and a total page ieiz S Ge ER few Feet fyna Joch Gate Wii te aax J gid A j a Gios pp J S A D fe DADS A oe irtabke AAA ly Petterm p r Fisca Only I Pogan Only j Ki Mnou Update Gra Mantenance Annus Peportng The Update QPR Menu requires the user to select the period to be updated for all worksheets except the Total Page Service Funding Source Summ
29. or Program Only If neither option is selected all rows on the report are visible Fi Microsoft Excel QpeMcnuxts all x E Fle Edt Ven Insert Format Tob Data Window Help Select Service Irish Workshects Adobe PDF WE i 4 A e e E IS D e e H J sal a fm E s O W i I L E d a DADS Access A Intake AAA Quarterly Peilormance Repot C Prngram Cnty I Fisesl Oriy meleci ect e OK Mam Menu H A HI Man Meru Update QPR Maintenance Annual Reporting heady Fiscal Only selection Erptaditares Allocated to DADS Faeadiag Sa ii Current Fircal Teor Expenditure a Dellt aoo DESCH E aa Tie HIE a Tite I pag OMS Baris odeedtoT OFS pet pa Tithe ID Medication Management Skate General Rewenue Tithe I E Match pag Shake General Fievenue Other fe O eege pap Ei Delt pm Tite HCE pg PATO pm Tithe HE pe Tite VG pad PY Title I D Medication Management Ee O a GHZ Baris Todini 0g Ose st07 43 ng Bond Reridential Repair D tid0t Ose 21407 a sa Tatal DADS Expenditerar A DADE Capital Equipment o Obher Funds Kap ital Equipment Rev1 0 November2015 Select Serate Program Only Selection el Expenditures Allocated to A DADS Funding Sources Area Plan Performance Activity indicated oar ef Total Persons Total Units Served otal Units Purchased with Funds Received from DADS Total Match Units Supported with L
30. ormance Depending on the accounting method used by the AAA reported performance may or may not be the same as the Request for Payments RfR and the AAA General Ledger in the month the service was performed The Department recognizes many service providers do not provide required information to the AAA in a timely manner AAAs are required to report information they have approved verified and documented at the time the report is submitted For this reason the QPR System provides AAAs the option of updating previously reported information on subsequent QPRs DADS Capital Equipment The AAA contract with the Department requires prior approval of capital expenditures To track the expenditure of DADS funds for capital equipment and to accurately calculate unit costs the AAA must report the cost of capital equipment separately This cost must also be included under DADS Funding Sources Nutrition Education Cost Included in Congregate and or Home Delivered Meal Expense In accordance with Sections 331 42 USC 3030 e and 336 42 USC 3030 f of the Older Americans Act Amendments of 2006 congregate and home delivered meal nutrition service projects must provide nutrition education nutrition counseling and other nutrition services as appropriate based on the needs of meal participants or recipients Texas Administrative Code Title 40 Part 1 Chapter 85 85 302 q and AAA Program Instruction 313 requires nutrition education be provided annuall
31. ormation provided will include the type of fund the fund source name and the worksheet row number The AAA will select Add Clear Funding Source from the Maintenance Menu DADS Access amp Intake AAA Quarterly Performance Report Lock Unlock Quarter Totals Add Clear New Semice Main Menu Rev1 0 November2015 Page 27 of 39 A pop up menu is used to add the funding source Ee x sert Format Tools Data Window Help Select Service Unhide Worksheets Adobe PDF 2a x QPR Maintenance A a a a a OPR Maintenance ae Current FY Expenditures Special Awards Prior FY Carry Qver Prior Pr Continuation Award el sl a NC P LK III Revl O November 2013 Page 28 of 39 Enter the fund source name provided by the Department EJ Microsoft Excel QprMenu xls GI File Edit View Insert Format Tools Data Window Help Select Service Unhide Worksheets Adobe PDF a el id c Al 4 TOE gt D QPR Maintenance Add Clear Funding Source Fund Type Source and Row Number will be provided by DADS Select Fund Type Fund Source Name Available Row Numbers Main Menu M 4 E H MainMenu Update QPR Maintenance Annual Reporting Ready EJ Microsoft Excel QprMenu xls O x EN File Edit wiew Insert Format Tools Data Window Help Select Service Ufhide Worksheets Adobe PDF a ee IgA aia el EE LTE rm BKRSSSb P h x UV Maintenance Add Clear Funding bouge Fund
32. process also accessed via the Ctrl S keystroke combination L i ll o Oe ei H EJ Microsoft Excel QPR2006Quarter xls SO GG KM MEFE Oe eee oe ag i Arial s 12 z B zli A Tia Ae 75 MES SS C oe A Type a question For help e amp x SES File Edit wiew Insert Format Tools Data Window Help 3 3 _j New Ctrl ROUPSIOA y stem Analyst Documentation QPR Re GA Open Chr AS AT AU AN AW Ax ES Esl 3 a Save as Web Page N Permission Web Page Preview Page Setup Print 4rea Ctrl P Send To gt Properties 1 G O4S0 1 System Analyst Document QprMenu xls 2 D 144412004 444 N 1444 Provider Information xls E hd MEN ENNEN MME ONE NEEN ONE E tanne Gane ee ea ee a ee ee Rev1 0 November2015 Page 21 of 39 Controlled Asset reporting Controlled asset amounts between 500 and 4 999 are required to be reported on line 77 and 78 This amount is for DADS and other funding sources DADS Control Performance Projections AAAs are required to provide DADS with projected performance information for performance measures established by the LBB The projections are provided by the AAAs biennially and may be revised annually Information is reported by the AAA on the QPR supporting performance provided In addition to reporting the performance achieved the LBB requires DADS to report
33. r Funding Source Add Clear New Semice Main Menu Fuld version 1 0 A pop up menu is used to select the quarter to be unlocked Rev1 0 November2015 Page 34 of 39 Lock Unlock Quarter Totals Quarter 1 Due 12s2o 12 Quarter 2 Due 03 20 13 Quarter 3 Due 06 20 13 Quarter d Due 09720713 Closeout Due 11 30 15 Selecting OK will present an informational decision box Lock Quarter Totals d J Totals For Quarter 1 Due 12 20 12 will be unlocked Do you want to continue Yes No If No is selected the user is returned to the Lock Unlock Quarter Totals pop up menu If Yes is selected the quarter is unlocked and an informational box appears to confirm completion of the requested action Microsoft Excel Totals For Quarter 1 Due 12 20 12 has been unlocked Ok The selected quarter is now unlocked Rev1 0 November2015 Page 35 of 39 Eo Microsoft Excel OprFyO7 xls Ioj x Es Fie Edit Wiew Insert Format Tools Data Window Help Select Service Unhide Worksheets Adobe POF So ia Leake LA OOlo al Bl 4 m FI9 B 2s EB wee Dees ze ZS October We E Eege TTT d e Ee SE E EE E EC DER e Ce E La DADS AAA A amp I Performance Report 2 3 Li Administration Expand Columns Month Totals Returnte Menu Locked Locked Locked Locked H Expenditures Allocated to DADS Funding Sources Current Fiscal Year Expenditures Tithe WC Tithe IIl Tithe Ill Title V ll EA Tithe Il 4
34. ring 10 match report only units supported with local funding used for match purposes See technical assistance memo AAA TA 402 for additional information regarding match units o Total Match Units Supported with Local Funds requiring 25 match report only other eligible units supported with local funding for match purposes See technical assistance memo AAA TA 402 for additional information regarding other eligible units o All other eligible Units not eligible for match report all eligible units that are not eligible for match This information is for statistical reporting and is not reviewed for financial accountability o Total Units Purchased with Program Income Funds report only units supported by program income expenditures See program instruction AAA PI 305 for program income requirements e Other Funding Sources Under this section AAAs will report information from other sources used to support the identified service The AAA must maintain documentation to support the information reported o All Program Income Collected report all program income received for the service provided See program instruction AAA PI 304 for program income requirements o All Program Income Expended report all program income expended for the service provided See program instruction AAA PI 304 for program income requirements o All Local Cash Match Expended 10 requires a 10 match for services paid for with funds from services req
35. robability of this type of error a process of locking and unlocking quarters has been developed All of the quarters are locked in the QPR Fy yr workbook when released to the AAA Rev1 0 November2015 Page 33 of 39 E Microsoft Excel OprFyO T xls GI Fie Edit Wiew Insert Format Tools Data Window Help Select Service Ujk drksheeks Adobe POF o la X LIES WM prEN EErEE E oa 2 SS kB eiE C10 amp October SS 5 WEE D O E d ZE CG In TI E ET Ea DADS AAA A amp I Performance Report as Ey Administration Expand Colurnts Month Totals Returnte Venu Locked Locked Locked Locked Locked DADS Funding Sources ESS ES Pe eee EE EE Oct GQ2 Oct 03 Oct Q4 Final Oct October State Fed October Cor Cor Cor Cor Total YTO TI Current Fiscal Year Expenditures fe We e A EE E EE EE oo Expenditures Allocated to Wa e H Administration Jal i Ready MOM E It is recommended the AAA initially unlock only the first quarter After the first quarter QPR is submitted the first quarter should be locked and the second quarter unlocked This process of unlocking only the current quarter should continue as each quarter is closed Ma Pa ma m a a Cot are The AAA must unlock quarters using the QPR Maintenance menu Select the Lock Unlock Quarter Total button to complete this activity DADS Access amp Intake AAA Quarterly Performance Report Lock Unlock Quarter Totals Add Clea
36. rovided for each state fiscal year quarter and for the contract year end closeout submission The workbooks are linked to the QPR Menu workbook and the QPR Fy yr workbook The workbooks are protected and do not allow for any direct information entry Information is directly copied from the QPR Fy yr workbook l QPR 20 yr Quarter 1 workbook contains three worksheets that directly copy data from each of the service worksheets from the columns September prior year federal contract closeout October and November in the QPR Fy yr workbook QPR 20 yr Quarter 2 workbook contains five workbooks that directly copy data from each of the service worksheets from the columns December January February Oct Q2 Cor and Nov Q2 Cor in the QPR Fy yr workbook QPR 20yr Quarter 3 workbook contains eight worksheets that directly copy data from each of the service worksheets from the columns March April May Oct Q3 Cor Nov Q3 Cor Dec Q3 Cor Jan Q3 Cor and Feb Q3 Cor in the QPR Fy yr workbook QPR 20 yr Quarter 4 workbook contains eleven worksheets that directly copy data from each of the service worksheets from the columns June July August Oct Q4 Cor Nov Q4 Cor Dec Q4 Cor Jan Q4 Cor Feb Q4 Cor Mar Q4 Cor Apr Q4 Cor and May Q4 Cor in the QPR Fy yr workbook QPR 20 yr Contract Year Closeout workbook contains sixteen worksheets that directly copy data from each of the service worksheets from the columns Final
37. ry State Federal DADS Funding Sources ue a a Gees a a Current Fiscal Year Expenditures Title Ill 5 REENEN ER Tile Oe Title III C2 a ee a a EE Title WOREN EUR a E SG EE ER The header rows of the worksheet provide the AAA with additional options a DADS AAA A amp l Performance Report Chore Maintenance Expand Columns Month Totals Return to Menu Expenditures Allocated to Feb Q3 Final Feb February State Federal DADS Funding Sources February Cor Cor Total YTO YTO I Rev1 0 November2015 Page 18 of 39 The Expand Columns button will show all of the reporting and total columns for the Service Expenditures Allocated to Final Oct DADS Funding Sources September October Oct Q2 Cor Oct Q3 Cor Oct Q4 Cor Cor Current Fiscal Year Expenditures Title MB Title M C4 7 a Title C2 Title ILD Evidence Based lnterventionf CMS Basic 04013 038114 _ EE NSIPE o T T S State General Revenue Title IE Match J State General Revenue Other J Jo o o To o o y O Title WE GOECSC The Month Totals button will show only the monthly totals and state and federal fiscal year totals eee w S September Expenditures Allocated to February April May June July August State Federal FY Federal DADS Funding e Sources YY e Total Gees E E Gees Ges eH am aH Title HEB eT a SE SSS CT RTT Een U mal Tite C2 REENEN a SSP Tithe o RR a fief Tl The Return to Menu button will ret
38. tion Clear Funding Source 2 Row 26 already in use by Funding Source Department Provided Do you want to clear this row If No is selected the user is returned to the Add Clear Funding Source Menu If Yes is selected the information is deleted from all workbooks and an information box appears to confirm the action was completed successfully Microsoft Excel Row 26 cleared The user selects the OK button and is returned to the Add Clear Funding Source Menu Rev1 0 November2015 Page 30 of 39 Add Clear New Service The Department will notify the AAA when a new service is to be added The information will include the name of the service and which spare worksheet is to be used The AAA will select Add Clear New Service from the Maintenance Menu Lock Unlock Quarter Totals Add Clear Funding Source Add Clear New Service A pop up menu is used to add the new service st Tools Data Window Help Select Service Unhide Worksheets Adobe PDF GF I M x hl 100 vb seeh DADS Access A Intake AAA Quarterly Performance Report Add New Service to QPR CLC CELL Rev1 0 November2015 Page 31 of 39 Select the appropriate sheet name provided by the Department from the drop down menu DADS Access amp Intake AAA Quarterly Performance H Add New Service to QPR x Enter the appropriate service name provided by the Department in the Enter Service Name field DADS Acc
39. uiring a 10 match o All Local Cash Match Expended 25 requires a 25 match for services paid for with funds from services requiring a 25 match o All In Kind Local non cash Expended 10 requires a 10 match for services paid for with funds from services requiring a 10 In Kind match Rev1 0 November2015 Page 7 of 39 o All In Kind Local non cash Expended 25 requires a 25 match for services paid for with funds from services requiring a 25 In Kind match o All Other Federal Expended report all other federal funds expended for the service This information is for statistical reporting and is not reviewed for financial accountability o All other Funds Expended not eligible as match report all other non federal funds e Title II E Caregiver Information Federal reporting for the National Aging Program Information System NAPIS requires the reporting of separate information for the Title I E Caregiver Program The information is required for each service where Title I E funds are utilized o Program income collected from Caregivers serving Individuals 60 years of age and older Grandparents and other relative caregivers serving children 18 years of age or younger o Total other funding expended for Caregivers serving Individuals 60 years of age and older Grandparents and other relative caregivers serving children 18 years of age or younger Federal Fiscal Year FFY and State Fiscal Year SFY The federal fisca
40. urn the user to the Update QPR menu From the Update QPR Menu the user may change previous selections and continue updating services or select the Main Menu button DADS Access amp Intake AAA Quarterly Performance Report Test Agency 03101 W Fiscal Only Program Only Select Service Caregiver Information Services Select Month October Lk Vviain Menu Rev1 0 November2015 Page 19 of 39 The Select Service drop down list also contains e aworksheet for future use Reserved e Spare worksheets for new services that might be added after the start of the fiscal year e Two worksheets to provide the AAA with additional summary information Total Page and Service Funding Source Summary lal xi Gi File Edit wiew Insert Format Tools Data Window Help Type a question For hel z AX LGNM A AT a Sl il 4p 100 OF E e 4 U B HU e e SS SS ry Oy AB DADS Access amp Intake AAA Quarterly Performance Report l Fiscal Only T Program Only Select Senice Personal Assistance Select Month Physical Fitness Recreation Residential Repair Senior Center Operations OK Shopping Telephone Reassurance Transportation D R Transportation Fixed Route Visiting Volunteer Placement Voucher Caregiver Respite Care Voucher Homemaker Total Page IYOT vercion 10 Serice Funding Source Summar M 4 E H Main Menu Update QPR F Maintenance x Annual Reporting F Ready NUM The Total Page is a report of
41. y to each participant for both home delivered and congregate meals Based on rate setting requirements and guidance provided by DADS o If the nutrition providers included nutrition education in the rate setting process the AAAs must report the calculated total of the nutrition education s portion of the congregate and home delivered meal costs on this row in the congregate meals and home delivered meals columns The portion of the cost of nutrition education is determined by multiplying the calculated cost per nutrition education unit for each provider established on the provider s budget worksheet by the total number of nutrition education units provided by each provider and then summing the total for all nutrition providers Rev1 0 November2015 Page 5 of 39 The average unit costs for congregate meals and home delivered meals will not be impacted by the expenses reported in this row However the QPR will automatically sum the nutrition education costs reported in the congregate meals and home delivered meals columns and display the total in the Nutrition Education Cost Included in Congregate and or Home Delivered Meal Expense for nutrition education This total will be included in the Average DADS Cost per Unit for nutrition education o If the nutrition providers did not include nutrition education in the rate setting process the cost associated with the nutrition education DADS funds local cash and or in kind must be reported
Download Pdf Manuals
Related Search