CECC logo
MONARCH

Monarch is the world's leading report mining and data analysis desktop tool. Monarch can easily extract, analyze and export data from existing reports, without programming. It also puts an end to flipping through thick reports and re-keying data into spreadsheets, or waiting for help to access needed data. Monarch offers all of the functionality of Monarch plus lets users combine and import data from additional data sources, such as databases or spreadsheets, as well as extract data from HTML and PDF files.

 

Reprinted from http://www.Datawatch.com

 

These models were created to extract the basic information from system reports. You may need to modify the model to customize it to your districts' needs. You must purchase Monarch to use these files. If you have not purchased Monarch, go to the Contact Datawatch section below for further information.

 

All Monarch models and instructions are located on our SharePoint server. You must have a SharePoint login to download the files. If you have any questions, please contact our office at (909) 386-9600 or send an email to MS_Helpdesk@sbcss.k12.ca.us

 

  • Financial Models

    Board of Trustees Payment Report Merge Project

    This project merges the information from the APY250 report with a more detailed description from the Board of Trustees Payment Report. The result is a spreadsheet with the information from both reports that can be sorted, filtered or summarized in a variety of ways to meet the needs of your Board members.

     

     

    GLD410 Out of Balance Resources

    The Monarch model developed will trap and report out in table format the individual resources that are triggering the out-of-balance message.

     

     

    Scattergram & J90

    The Scattergram is used to give total dollars spent and number of employees on each Range/Step of specified salary schedules. This information is used on interim reporting for certificated employees and is also commonly used in negotiations.

     

    The model is used against the 00-1 Version of the COR610 (D)etail format. Include All Eff Dates, Vacancies, Earnings =(N)o. You may 'Include Accounts' but the template is not trapping the account information.

     

    The end product is found in the Summary View titled "Scattergram". If you have extracted your report by a single salary schedule or designed a filter for one single salary schedule, there is a second summary titled "1 Schedule" that formats report the information horizontally rather than vertically. This model can also be used with the J90 report.

     

     

    FAR311

    The purpose of this model is to give the financial user a method for

    reporting Accruals to the State. You will need to run a Receivables/Payables Financial Activity Report (FAR311) and apply the model.

     

  • Payroll Models

    Frequency Validation

    This EQ/BQ model is to be used on the Tax/Ret Report in Payroll.NET. Open Monarch, then “F”ind the report. Since the report is in a PDF format, you will have to Convert the file by clicking OK when the file is opened. Once the file is displayed, open the model and view in the TABLE format.

     

     

    Duplicate Earning Lines

    This model is to be used against the Employee Payroll Earning Report on the HP. This can be run against a single cycle or all cycles to find employees that have earning lines with a Posn/Desc field with lower case values. These lower case values are causing data issues in .NET and we need these cleaned up as soon as possible. Once the model is applied, please use the TABLE view. Please fix any Posn/Desc field in any remaining open cycle.

     

     

    Pay History Report- Finding Employees Who Should Pay Additional Medicare Tax

    This model is to be used to assist Payroll Technicians when searching for employees who should begin paying the Additional Medicare Tax starting in 2013. This model is to be run against the Pay History Report (PYPHIN/PYR830) in the 'S'ummary format. The Table view is sorted by the Medicare Gross TOTAL in descending order so the employees with the highest Medicare Gross are at the top. Once employees have reached the limit and must start paying the Additional Medicare Tax, Payroll Technicians will have to manually enter that additional tax into the Employee Payroll Deduction screen (PYDEUP), using the Deduction Plan Code of 9XMEDI. This additional tax is for employeeís only and employerís will not have to make a matching contribution. Please refer to IRS website for additional information on this Additional Medicare Tax.

     

     

    PYR630 & COR660 Comparison Project for Health & Welfare Dollars

    This Monarch and Access Project is a comparison between the Payroll Deduction Report (PYR630) and the Employee HW Benefit Attachment Report (COR660) which will assist users in validating data between Payroll (what is actually hitting the GL) and MAGIC (what is being budgeted). Both the PYR630 and the COR660 should be run in the ìSîummary format with the appropriate models applied and the view is in the TABLE Window. The access database is also included which will display employees that exist in Payroll but NOT MAGIC, those who exist in MAGIC, but NOT Payroll as well as displaying employees who exist in BOTH areas, but the dollars do not match.

     

     

    Pay History Report- Finding Warnings

    This model is to be used to assist Payroll Technicians in finding Warnings on the Pay History Report (PYPHIN/PYR830). With various legislative changes, this will help Payroll narrow down Warnings which may need manual adjusting prior to Quarterly or W2 Processing. Once the model is applied, it is to be viewed in the Table Window. It will display the Employeeís SSN, Name and the Warning associated with that employee.

     

     

    Additional Earnings/Payroll Earning Report Comparison

    These models and access file are used to compare the annual stipends from the Additional Earnings (COEEIN/COR625) Report in MAGIC to the manually entered earning lines in Payroll (PYPEIN/PYR530). Once the models are applied and exported from the Table View into the Access file, it will display the employees, earning type and annual salary amounts for both Additional Earnings and Payroll. There are 2 queries available in Access to ensure that all data from both reports is accessed. If there are employees on one report and not the other, the Salary field will be BLANK. The model from the Employee Payroll Earning Report also includes the Earning Frequency on each earning line and the multiplication is done in the model itself. The model will multiply the Earning Frequency by the monthly earning to arrive at the Total Salary. Please keep in mind that the model does not know what each frequency in Earnings correlates to. So, if there is a Frequency 14 on an earning line, the multiplication will be 14 x the monthly amount. So, in some cases, the multiplication on the PYR530 may be off by a month or two.

     

     

    Work Calendar Balance Check for Fiscal Year End

    This model is used to ensure the Work Calendars are in balance in the new fiscal year. Once the Personnel Roll has taken place in EPICS and BEFORE Payroll is generated in the new fiscal year, this model should be applied to the Work Calendar Report (COWCIN/COR250). When the Table View is accessed, a listing of Work Calendars that are NOT in balance will be displayed. If all Work Calendars are in balance, the Table View will be blank. Any Work Calendar displayed will need to be audited and brought into balance before Payroll is Generated for the new fiscal year.

     

     

    DNP Balances Comparison

    These models and access file are used to compare the DNP balances between Employee Pay History (PYR830) and the DNP Balance Report (PYR535). Once the models are applied and exported from the Table View into the Access file, it will display the employees DNP balances. There are 2 queries available in Access to ensure that all data from both reports is accessed. If there are employees on one report and not the other, the DNP field will be BLANK and needs to be researched. Also, if the amounts are different, this will also need research.

     

     

    Employer Paid Benefit Distribution

    This model is to be used on the Employer Paid Benefit Distribution By Account Report (PYR320) to assist the Payroll Technician in finding employees who have a $0 Gross, but who have dollars in the Employer HW in Payroll. These might be employees who terminated, but who have not had their Employer HW dollars deleted. When viewed in the Table format, only employees with $0 Gross, but dollars in the ER HW column will display.

     

     

    Payroll Exception Report

    This model is for the Payroll Exception Report (PYR515) to assist Payroll Technicians in finding Errors on the Exception Report. When accessed in the Table format, the Error information will be displayed in an easily viewed format.

     

     

    Year End Reports

     

    COR960 Model

    This model is to be run against the Account Edit Report (COACIN/COR960) that is sent on the year end CD. When the model is applied and used in the Summary View, it will display a listing of Accounts to be linked or changed.

     

    PYU955 Model

    This model is to be run against the PYU955 Report sent on the year end CD. When the model is applied and used in the Window View, it will display a listing of employees with errors that occurred during the roll and should be audited.

     

    PYU956 Model

    This model is to be run against the PYU956 Report sent on the year end CD. When the model is applied and used in the Window View, it will display a listing of employees with errors that occurred during the roll and should be audited.

     

    Post Roll Gen-Detail Model

    This model is to be run against the Generation Reports sent on the year end CD. When this model is applied and used in the Window View, it will display detailed information regarding the employee's Salary Range, Step, FTE, Hours Per Day and Salary Information.

     

    COR610 Find Gen Earn Flags Model

    This model is to be run against an Employee Position Detail Report in MAGIC (COEPIN/COR610). This model will allow the user to quickly identify employees Generate Earn Flag in Employee Position Attachment. There are 2 filters included: 1 will display ONLY employees with a Generate Earn Flag of "Y" and 1 will display ONLY employees with a Generate Earn Flag of "N". This is to assist the Payroll and HR Technicians in determining which employees may need to have the flag modified for the new fiscal year prior to generating.

     

     

    Employee Tax/Retirement Report

    This model is to be applied against the Employee Tax/Retirement Report. It will display a listing of Earning - Benefit Frequencies to assist the Payroll Technician at Year End in determining the correct Roll frequencies.

     

     

    Generation Calcs

    Below is a Monarch Model to be used against the Generation Reports. This model, when used in the Table View, will display any employees who have a different Salary Calc (Annual calculation at the top of the report) compared to the Cycle Total Calc (the cycle by cycle distribution total). This will enable the Payroll Technicians to check the Mass Generations for potential problems and errors as soon as the Generation Reports are run. If problems are found, the Payroll Technician can contact the Payroll Helpdesk so we can hopefully identify and fix problems sooner and more efficiently. This model MUST be used with the "S"ummary version of the Generation Report only. The "D"etail format will not work with this model.

     

     

    Pers Reduction for New Payroll

    This project is used to display the Pers Reduction amount by employee and includes Account numbers. If you do not need the employee information, you can use the PYR335 report from your Payroll CD.

     

     

    Out of Balance

    This model captures the warning when someone is out of balance.

     

     

    Changing Earning & Deduction Frequencies Between Fiscal Years

    This process is to be used when District and/or County Offices are changing the Frequencies, or cycles employees are being paid. The documentation file will show step by step the process that must be followed and the models will assist in assuring only the specific Earning/Deduction lines are changed.

     

     

    Quarterly Payroll Report Project (Etiwanda)

    The Etiwanda School District participates in the Medical Administrative Activities/Billing Option Program through the Department of Health Services. For our quarterly billings, DHS needs to know the total paid and applicable employer benefits by employee, by quarter and by account code (mainly function, object and location). Time survey percentages are then applied to the quarterly payroll totals to arrive at the proper amount to be billed back to Medical/DHS. This model has solved our problem in that the model will combine the monthly PYR320 to quarterly totals (gross paid/er benefits) by employee and by account number. Run the PYR320 for each cycle of the quarter and apply the model. Go to the Summary view for the combined totals for each employee.

     

     

    Negative Pay Lines

    This model will use the PYR510 report to find negative salaries.

     

     

    Pers Gross for New Payroll

    The purpose of this model is to give the financial user a method for calculating PERS gross amounts. You will need to run a PYR320 report then apply the model. The Table view is listed by employee and the Summary view is summarized by account.

     

  • Personnel Models

    Government Compensation - State Controller/Public Records Act Requests

    This uses Monarch software and combines payroll deduction and earning postlists from Share2, salary schedule matrix from the Legacy System, and personnel information from Reports 2000 to create a spreadsheet that can be applied to the template to report Government Compensation to the State Controller. Additional Summaries in Monarch have been created to easily export data for any Public Records Act requests.

     

     

    Affordability Care Act Project

    The Affordability Care Act Compliance Monarch project combines Employee information from EPICS using Reports 2000 as well as Payroll and W2 reports from the HP Legacy system that are stored on the Share2 server. This project is designed to be used with Worxtime through American Fidelity to assist districts to remain in compliance with the Affordability Care Act. Although the data is formatted specifically for Worxtime, any district is able to use this project for compiling information and reporting purposes.

     

     

    CSEA Employee Data Reporting

    CSEA may request employee information from districts for all classified bargaining unit members. The information includes: Employee Name, Mailing Address, Home Phone Number, Work Phone Number, Job Title and Work Site. The following instructions will walk you through using Monarch and Microsoft Access to create an excel spreadsheet and password protect/encrypt the document to be sent via email to CSEA.

     

     

    Notice of Assignment

    The Notice of Assignment project is used on an annual basis as a way for districts to quickly generate notifications to send out to employees each year which includes information on their annual assignment. This process uses Reports 2000 for the employee information and a COR610 in Reflections for salary information. You will need to have Reflections, Monarch, Microsoft Word and Microsoft Access already installed on your computer before you begin this process.

     

     

    Health & Welfare Census

    Occasionally Personnel departments are asked to give a census report to insurance providers and brokers for insurance quotes.

     

     

    Vacation Tracking

    This project uses the ABS100 and COR610 reports to show which employees have exceeded their vacation limits.

     

     

    FTE Vacancies Bug

    This model is designed to identify the problems with partial FTE vacancies not reporting in COR/SAMS reports therefore creating a possible understatement of earning/benefit dollars in budget development. It is used against the 00-1 version COR610, (D)etail format, sorted by position, 'Include accounts'='Y', 'All Empe Eff Dates'='N', 'Include Earnings'='N', 'Include Vacancies'='N', with a specific date in the 'As of Date' field--recommended 6/30 of the current fiscal year.

     

    The model includes a summary that will summarize the Employee FTE against the Position FTE per position. If the FTE is NOT equal for employee and position, then a partial vacancy exists and should be reviewed to see if these vacancy dollars need to be included in the budget year. Once identified, the user may use SAMS Additional Account Detail Program to enter in the dollars, FTE, tax type, and 3xxx accounts associated with with these partial vacancies.

     

     

    WorkForce Parallel for Absences

    This project is used for Districts who are paralleling the absence balances between WorkForce and the HP.

     

     

  • Contact Datawatch

    Each of the districts in San Bernardino County as well as California Educational Computer Consortium members have been identified as eligible to participate in this offer. Any questions with regard to the use of the software should be directed to Melita Crowell, San Bernardino County Schools, Information Technology Department at: MS_Helpdesk@sbcss.k12.ca.us.

     

    Mark C. Burns

    State, Local & Education

    Enterprise Sales Manager

    O: (978) 275-8313

    C: (978) 420-7384

    www.datawatch.com