This article will walk you through the process to correctly bill the Colorado Department of Early Childhood for your monthly expenditures, as well as how to mark records in Sugar as successfully billed to the funder to ensure no records are missed. This process is new as of January 1st, 2021, and replaces the older, more manual way of doing reconciliation. For our more visual learners you can check out the Videos: Step by Step Monthly Reconciliation Process!
For a copy of the UPDATED TSQI/ESQI Invoice Template please contact your Program Officer!
Please note that if you need to differentiate between the "old" and "new" expenses and coaching within the same Funding Stream, we made a few reports to help with this. You can view them in the "Other Reports in Sugar you may find helpful with Reconciliation!" article.
If you have followed the Step by Step Guide below and are experiencing any issues try checking out the Reconciliation FAQ'S! first before you submit a Helpdesk Ticket!
*Bonus Tip: If you are having trouble seeing the screenshots in any of these articles click directly on the image to enlarge the picture for more clarity!
STEP 1: Receive Start of the Month Reminder!
- Grant Leads for each Funding Stream should receive an email from email@example.com on the 1st of each month. Please add to your Safe Sender List! (Instructions for how to do this can be found HERE!.) This is to remind you it is time for monthly Reconciliation!
STEP 2: Make sure you are not missing any Records!
- Check these reports to make sure you are not missing any records that you want to bill to the CDEC! They show all Spending or Coaching Records that were created this Fiscal Year that have not been marked as "Billed to Funder".
- #! Spending not ready to be billed to CDEC (shows Spending that has not yet been billed to the CDEC from the whole Fiscal Year)
- #! Coaching not ready to be billed to CDEC (shows any Coaching that has not yet been billed to the CDEC from the whole Fiscal Year)
STEP 3: Double Check your Reconciliation Reports
- Use these Reports to make sure what shows up in your final Invoice to the CDEC matches your financial records for last month. These Reports show Spending and Coaching records that are “Reconciled” or "Completed and Reconciled" / “Cancelled-billable” and have not yet been billed to CDEC.
- If there are Spending records on here that have incorrect amounts, simply go to the Spending record and select the Action "Please open for editing", which updates the status to “Needs Review (ordered)”. You can then update the amount or other errors and change the status back to reconciled, and it should now show up correctly.
- If you do not want to bill for a Record yet for any reason, you can go to the Spending record and select the Action "Please open for editing", which updates the status to “Needs Review (ordered)”. You can then update the status to whichever is appropriate (besides "Reconciled") and save it for now.
- If there are Spending records that are not on here that you want to bill this month, go to the records you want to add and make sure their status is "Reconciled", and their invoice date is before the first of the current month (i.e., if you are billing for the month of October, make sure the date is before Nov. 1).
- If there are Coaching records that are not on here that you want to bill this month, go to the records you want to add and make sure their status is "Reconciled" or "Cancelled - Billable".
- The Records contained in the Reports above will not show up again in those reports after you run the Summary Report from your Funding Stream (because that action changes the Status to "Billed to Funder" and the Filter on these reports is "Ready For Billing"), so if you need to update any Spending or Coaching Records make sure to do it before step 4!
STEP 4: Push the magic buttons!
- Navigate to your TSQI23 Funding Stream Record (which you can easily access from the Funding Stream Module or by clicking on the Funding Stream link in your Monthly Reconciliation Reminder Email), click on the Budget Summary tab, scroll down to the Actions field in the Reconciliation Reports Subpanel and select “Generate Summary Report and mark records as billed”.
- Once you select “Generate Summary Reports and mark records as billed” another field will become visible which asks you to confirm that you have reviewed and confirmed that the Records in the Spending and Coaching Reports are accurate and are ready to generate your Reconciliation Summary.
- ONLY IF you have thoroughly reviewed the #! Unbilled Spending, #! Unbilled Coaching AND the #! Spending Records for CDEC Reconciliation and #! Coaching Records for CDEC Reconciliation Reports then select "Yes" from the Dropdown option and SAVE!
(Please Note: this step may take a moment, so please do not navigate away, or refresh the page until it successfully saves!)
Selecting the “Generate Reconciliation Report and mark records as billed” action will do two things:
- It will produce a Summary Report Link in the Reconciliation Report field that looks similar to the CDEC Invoice Template. This report will now also have your indirect rate calculated in it automatically, so you will not need to adjust the Indirect rate on your CDEC Invoice Template! Click on the Reconciliation Report link to open it.
- It will ALSO produce two detail report links: one for Coaching in the Coaching Reconciliation Report Field and one for Spending in the Spending Reconciliation Report Field. These Reports show exactly which records are contained in the Summary Report! Click on the "Coaching Reconciliation Report link" and the "Spending Reconciliation Report link" to open them. And make sure you save them!
*Behind the Scenes: When you select " Generate Summary Report and mark records as billed"and save Sugar also automatically updates the "Billing Status" field on all Spending and Coaching records contained in this Summary Report to “Billed to Funder”, so there is no danger of forgetting to bill something or accidently double-billing an expense (please note this is a “view only” field for everyone except CDEC program officers).*
***Special Process: Please note that if clicking on the links in the "Reconciliation Report", "Coaching Reconciliation Report" or "Spending Reconciliation Report" fields do not work you can always scroll down to the "Reconciliation Reports" Subpanel and download the Reports from there!
Please Note: Once a Record is marked “Billed to Funder” (which happens when you Generate your Reconciliation Summary Report on your Funding Stream) it will no longer be editable by any Council Staff. If changes need to be made to a Spending or Coaching Record after you select "Generate Reconciliation Report and mark records as billed" on your Funding Stream the Council will have to contact their Program Officer at the Office of Early Childhood who can then unlock those records for editing. You can find the steps in Directions for unlocking a "Billed to Funder" record so Council can edit!
STEP 5: Copy/Paste!
- Copy the Reconciliation Summary Report (Notice the NEW ROWS for Reporting! You can read all about this HERE!):
and paste it into your CDEC Invoice template where it says "Paste Sugar Report Here!":
Be sure to select "Special Paste" and then "Values" to keep the formatting consistent!
- The columns in the Summary Report Sugar generates match the columns in the Invoice Template report. Column C shows the approved budget, column E shows the preschool expenditures, and column G shows the infant toddler expenditures. Remember, this report will now also have your indirect rate calculated in it automatically, so you will not need to adjust the Indirect rate on your CDEC Invoice Template!
- For a closer look at how you fill out your CDEC Template using your Reconciliation Summary check out this Video!
- The CDEC Invoice Template now has tabs for the Spending and Coaching detailed reports to be copied into, so everything can be in one place! Be sure to copy the two detailed excel reports (from step 4!) into the Spending and Coaching Tabs in your CDEC Invoice Template before you send off!
STEP 6: Send!
**Bonus Tip 2: You can always go back to see previous Summary and Coaching/Spending Detail Reports on your TSQI Funding Stream record. Simply scroll down to the “Reconciliation Reports” subpanel module!