2014 Supplemental Forms: Automation Instructions

1. 2014 Supplemental Forms:Automation Instructions

2. Important notes • Forms will be sent no later than August 15, 2014, and will be due back October 24, 2014. • Rather than one workbook containing every form, institutions will receive workbooks dedicated to a single form or category of forms (e.g., enrollment, finance). • The Commission is in the process of reviewing and clarifying instructions. These will be posted in advance for institutions to review. • All forms except Utilities, O&M, and the various fund summaries are now modified for automation. Complete the excluded forms as you normally have in the past. • Please appoint an individual coordinator at your institution or system to oversee the collection and submission of the forms.

3. Purpose for automation • Ease of access • Automation will enable the storing and organization of Supplemental Forms data in a central database from which the Commission can retrieve information for surveys and reports more efficiently and accurately. • Improved data quality • The automated system will run a series of simple data validations to guard against potential errors before submission. • Conduct long-term data analyses • In its current state, the Supplemental Forms only allow for a small timeframe of evaluation. Establishing this year as a baseline and accumulating more data in our central database each year, the Commission will eventually be able to conduct long-term analyses of Supplemental Forms data.

4. How we are going to automate:Ageneral review of new features In order to accomplish our goal of establishing an automated system that will allow us to compile the annual Supplemental Forms data in a central database, we have made a few minor modifications to each form. Using the Student Fee Schedule form as an example, the ensuing slides illustrate the Commission’s method for automating the Supplemental Forms. The Commission appreciates your patience and cooperation in this process.

5. The status of this form reads “EDITED,” meaning that information has been entered in the corresponding form, but it has either not gone through or failed data validation. Thus, this form is not ready to submit. Each workbook has a “START” tab containing status of the particular form or forms.

6. The principal goal in automating these forms is to create a uniform structure for each form so the data can be easily transferred into a CSV format and eventually a central database. When the form is finished and validated, its data is reformatted and relocated to the “CSV” tab within the workbook.

7. If applicable, the form is populated with the previous year’s fields when you receive it. In the example on the right, the fees entered by the institution during the prior year are populated automatically for the current year.

8. At the bottom of each form is a section for comments and footnotes.

9. In the interest of maintaining uniformity, the forms are locked, prohibiting users from inserting columns and limiting the number of editable cells, among other restrictions.

10. Calculations are generated automatically when possible. These cells are highlighted grey.

11. Simple data type validations are built into each form, ensuring, for example, a string cannot be entered where a numeric value is expected. An error message populates immediately if error occurs. In the future, when we have accumulated more historic data, we may create restrictive data parameters based on previous years’ data.

12. Variable codes are now standardized on relevant forms. In the example on the right, when attempting to identify a rate for an individual fee, the user has a list of options from which to choose (SCH meaning per Semester Credit Hour). Creating a uniform list of variables limits the number of possibilities for which our database has to account and process. Detailed definitions of the variable codes for each form is included in this year’s instructions. Values in these particular cells are restricted to one of the variables given.

13. Depending on the form, you may need to add one or more field/row. In the example on the right, you may want to add a fee new to the current year. To add a row/field, simply click on the button at the top of the form labeled “Add [Data Element].” You may be prompted to answer a series of question so the program knows where to place the additional row in the form.

14. When you believe the form is complete, click the “Submit/Validate Data” button located at the top of each form.

15. Clicking the “Submit/Validate Data” button triggers another round of data validation. A message box detailing the type and location of the error populates if any errors are discovered. In the example on the right, the user failed to enter a PCS code for the Student Activity/Organizations fee. Errors must be remedied to submit data successfully.

16. When the form has been completed successfully, you are taken back to the “START” page. A message box populates confirming that the particular form has been validated. The status of the form on the “START” page reads “COMPLETE.” You can now send the workbook to the Commission. For your convenience, you can click the “Send Form Now” button, which, using your default email application, brings up a new email window with this workbook attached. Status must be “COMPLETE” to use this button.

17. When the form has been validated and completed successfully, the data is reformatted and transferred to the CSV tab. In the future, institutions may be able to simply submit data in a CSV format rather than completing forms.

18. Contact Information Content and use of forms: Gary Timm (402) 471-0020 gary.timm@nebraska.gov Technical issues: James Schiltz (402) 471-6506 james.schiltz@nebraska.gov Duncan Hsu (402) 471-0024 duncan.hsu@nebraska.gov