1 / 24

Saving and Preparing School Finance Data from GEMS

Saving and Preparing School Finance Data from GEMS. Basic Setup & Revenues Paul Taylor, OPI School Finance. http://opi.mt.gov/Index.html. http://gems.opi.mt.gov/Pages/Default.aspx. Choose TFS Reports then Reported Revenues by School District. Determine your parameters and click View Report.

tad-higgins
Download Presentation

Saving and Preparing School Finance Data from GEMS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Saving and Preparing School Finance Data from GEMS Basic Setup & Revenues Paul Taylor, OPI School Finance

  2. http://opi.mt.gov/Index.html

  3. http://gems.opi.mt.gov/Pages/Default.aspx

  4. Choose TFS Reports then Reported Revenues by School District.

  5. Determine your parameters and click View Report

  6. Choose the dropdown next to the Save icon and choose Excel. Then open the saved file.

  7. 3. Choose the Ungroup dropdown and clear outline 2. Choose the Data tab 1. Choose 2 to expand all data

  8. 2. Right click and choose Format Cells 1. Select the “all box” 3. Clear the box Merged Cells 4. Click OK

  9. 2. Hover between 2 columns until you get the two-way arrow and slide the column width to a reasonable width. 1. Select the all box

  10. 1. Then highlight columns and rows that you don’t need and delete them.

  11. 1. Type this formula into cell A:2 if your data is in cell C:2; otherwise adjust this formula =IF(C2<>"",C2,A1) 2. Then pull this formula over to cell B:2 and pull it down to the bottom of your data.

  12. 1. Hardcode the two columns with the formulas, label them and delete columns C and D.

  13. 1. Sort on Source code and delete the blank rows.

  14. 1. Click in cell A:1 3. Choose Pivot Chart 2. Choose Insert 4. Choose OK

  15. Set up your Pivot Table like this by dragging items from the Fields area. A bigger version is on the next slide

  16. If the values for FY20XX are not Sum use the dropdown and choose Value Field Settings and choose Sum.

  17. Close the Pivot Table Fields list

  18. Right click in the chart area and choose Change Chart Type. 2. Pick Line graph and OK.

  19. Right click in the chart area and choose Select Data Source. 2. Pick Switch Row/Column and OK.

  20. Click on the data table. 3. Choose Slicer 2. Choose Insert 4. Choose Fund Name and Source Code then OK.

  21. Arrange slicers and graph by moving and holding the left button down to lock them in place.

  22. Next year, download the newest set of data and prepare it like slides 1-13 2. Once prepared paste the new data over your current data on sheet 1.

  23. 3. Click on the data table choose Pivot Table Tools and Change Source Data. 4. Change the range to include the new data and choose OK.

More Related