Reconciliation

[kwheelan]

Goal

Create a template for municipalities to show the reconciliation between the statement of net position governmental activities total and the balance sheet for governmental funds.

Background

• At a high level, the governmental activities column of the government-wide Statement of Net Position (first full tab on the template Excel document) and the Governmental Funds Balance Sheet (not yet in our template) are showing us the same information: the deficit or surplus for government-type activities for the year (Ie. money in minus money out).
• BUT, the Statement of Net Position uses an accounting basis called full accrual, and the Balance Sheet uses a basis called modified accrual. You don't really need to know the particulars here, but the main difference is that only the Statement of Net Position will include long-term assets (like land) and long-term obligations/debts (like pension payouts). If you want to read more, here is a comparison table, and this textbook chapter has an even more thorough explanation.
• For example, compare the $44,879,735 at the bottom of the first column for Clayton's Statement of Net Position (page 33 here) to the Total Fund Balance total of $17,172,907 on the Governmental Funds Balance Sheet (page 40 here)
• The reconciliation table (page 42 here) goes through each difference line item by line item until it fully accounts for the difference between Total Fund Balances - Governmental Funds ($17,172,907) and Net Position of Governmental Activities ($44,879,735).

Tasks

• We want to create a pretty general template, so look through examples of these tables in the ACFRs in this folder and any others you want to look up online (they're all publicly accessible).
• I've created a a branch to correspond to this issue (10-reconciliation), so you can clone this repo, checkout the 10-reconciliation branch and edit the Excel sheet (static/ACFR_template.xlsx).
• Create a new tab in the Excel sheet and follow the existing formatting to create a general table (only the yellow cells will be editable by the user). You can create section headers (ex. CAPITAL ASSETS) based on the headers you see in the example ACFRs.
• The net position at the bottom should sum up the fund balance and all subsequent line item adjustments. If possible, it would be great to implement conditional formatting so that it turns red if it does not equal the total net position for governmental activities in the Statement of Net Position and green if it is equal.

[lucakato]

Hi @kwheelan thanks for the detailed post. If you could check if I have permissions to push to the repo that would be great (unable to do so rn). I made a draft template so if you could have a look that would be great. I was also wondering whether I was allowed to modify the Label Dropdowns file (it appears to be locked?). The reason why I ask this is because for governmental funds we only have assets and not 'CurrentAssets' or 'NonCurrentAssets'.

[kwheelan]

Just upgraded your write permissions -- let me know if you still have issues. I can take a look at your draft when you push.

To unlock the label dropdowns, you can click on Format > Unprotect Sheet. If it helps, you can assume that everything under that ASSETS header on the balance sheet will be current assets. It's weird naming, but "current" in this setting means short-term and "noncurrent" means long-term. The balance sheet uses modified accrual, so it almost exclusively reports short-term ("current") assets and liabilities.

I'm not sure if you're starting to think about a template for the Balance Sheet, but I wanted to flag that the xbrl tags are going to be a little different than the ones already in the "Label Dropdowns" because of the modified accrual basis. (For example, acfr:CashAndCashEquivalents on the Statement of Net Position is instead acfr:CashAndCashEquivalentsModifiedAccrual on the balance sheet). We can talk more about this tomorrow.

[kwheelan]

This is a great first draft of the Balance Sheet! I thought you would start with the reconciliation tables, so I'm sorry for not giving you more guidance on the Balance Sheet format. I've outlined some tweaks to better fit the expected xbrl format.

When you make these changes, please push to the 10-reconciliation branch. I'm trying to reserve main for reviewed content because it links to the web app.

Some minor edits (mostly XBRL/formatting quirks):

• The scope should be Governmental Funds rather than Government-Wide
• The column names you added are Clayton's specific governmental funds, but we want to add flexibility for municipalities to add their own funds. You can just copy the column names from the GovFund Stmt of Rev Exp and Chg tab (rows 6 and 7)
• Make the total column (column I) gray and lock it (Format > Lock cell) so the user can't directly edit it
• Make sure there are ~10 rows in each section (some municipalities will have a bunch of line items). You can hide some of these extra rows (see the Statement of Net Position tab).
• B35 should be Total fund balances

Slightly more involved edits:

• In column A where you've left "acfr:" as placeholders, you can look up the right xbrl tag by using the Flint ACFR as an example. If you scroll to page 28, you can click on the numbers in the relevant rows to see the tag. (Ex. Total Assets has tag acfr:AssetsModifiedAccrual)
• Annoyingly, the xbrl tags are all slightly different from the tabs in the Label Dropdowns (which correspond to Statement of Net Position). Most of them have "ModifiedAccrual" at the end of the tag, but the taxonomy isn't consistent enough to just systematically add ModifiedAccrual to the end of the Statement of Net Position tag. You'll need to create a new tab with a look-up table for these tags. More on that once you've had a chance to finish the other edits...

Feel free to message me on Slack or comment here if anything is unclear!

[lucakato]

Thanks I will work on it this weekend and see how it goes!

[lucakato]

@kwheelan I tried the reconciliation table too could we go over it tomorrow, please? specifically not sure how to pull in the actual $ values from some other sheet(?)

[lucakato]

todo:

• pull in fund balance from balance sheet
• sum it
• make column D with modified accrual at end. Then compare it to list of XBRL:Item names from GovernmentalFunds tab in https://docs.google.com/spreadsheets/d/18LhDW7fYq5cQaqxheBgqMBNHYAvxyOfO/edit#gid=812073765

[lucakato]

@kwheelan in col J of label dropdown tab I figured out the list of labels with 'ModifiedAccrual' that exist in Governmental Fund tab in the Taxonomy spreadsheet. I only copied the names where 'Substitution Group' is marked xbrli:item.

Do you think you could have a quick look at the balance sheet, consolidation sheet, and also the filtered label markdown (col J). If col J looks good I can remove the cols I used to do the filtering and just keep the values of col J somewhere. Thank you.

[kwheelan]

Thanks for your work on this! The Reconciliation Sheet and Balance Sheet look perfect. For the Label Dropdowns, you can actually keep column H rather than column J. We want the xbrl tag to match the name in column B (which it does in column H).

Before you delete your extra columns, could you also identify any non-abstract tags that exist under modified accrual (column J) but not under full accrual (column C). Basically, you would identify any tags in the taxonomy that are not in column C of the Label Dropdowns. So:

1. Column E can be limited to just xbrl:item tags under the first section of GovernmentalFunds tab of the taxonomy spreadsheet. This is just rows 3-334 under the 300000 - Statement - Governmental Funds Balance Sheet, Other Funds (Unclassified) header. All rows after this section are duplicative or irrelevant, so taking just the first ~330 rows will make your life a little easier than dealing with the full 2000+ tags. (I'm sorry I didn't mention this sooner).
2. Columns F and J might change after you add the new column E values.
3. Look for any values in column F that are not in column C.
4. If you find any values in F but not C, they can become new rows in column H under row 522. Don't worry about filling in the other columns for these new rows.

[lucakato]

@kwheelan I just pushed again with col E limited to that tab. Regarding 3. I thought what we wanted to do was find out which labels (which we got by just appending 'ModifiedAcrrual' appear on the Taxonomy spreadsheet? Either way it would be great if we could go through this during our mtg tmrw! Thank you.

[lucakato]

@kwheelan I added the check for whether values in col F also exist in col D (the other direction). Seems like a bunch of thing do not exist.

Also, can I remove the item names I copied from taxonomy spreadsheet that don't have 'ModifiedAccrual' at the end? I highlighted them here:

[kwheelan]

Great! Yes, you can remove the highlighted items without "ModifiedAccrual" (they're category names). You can also remove anything with the word "Abstract" in it.

Please add these items you found that are in the taxonomy but don't have a full accrual version beneath the matched tags in column H. You can also add their corresponding labels in column B (the labels are in the Label column in the taxonomy). I will add the right categories in column A after you push.

Then, can you update the formulas in column A of the balance sheet to grab the modified accrual tags in the look up table? Right now, it pulls from column C in Label Dropdowns, but we want it to instead pull the correct tags from the column you just made with ModifiedAccrual tags (now in column H).

Let me know if you have any questions and/or let me know when you push your changes.

[lucakato]

@kwheelan Thanks. Just to confirm, when you say

Please add these items you found that are in the taxonomy but don't have a full accrual version beneath the matched tags in column H.

do you mean any row in column I marked as 'Exists', its corresponding cell in column F can be added to column H? So for example I2 is 'exists' so I can copy the value in F2, "acfr:CashCheckingModifiedAccrual" can be added to column H?

[kwheelan]

Yes -- anything that says "Doesn't Exist" in column I can go beneath row 522 in column H. (Although acfr:CashCheckingModifiedAccrual should exist -- it looks like it might have been accidentally deleted from F2).

If it helps, what we're trying to do here is a "full join". Basically, we want to match on the line item label so we have a list of all possible full accrual line items and all possible modified accrual items. If the same item exists in both systems, we want it linked in the same row. We also want to keep rows without matches.

So if we have two tables:

Label	Tag (full accrual)
Accounts Receivable	acfr:AccountsReceivable
Cash Equivalents	acfr:CashEquivalents

Label	Tag (modified accrual)
Accounts Receivable	acfr:AccountsReceivableModifiedAccrual
Allowance For Income Taxes	acfr:AllowanceForIncomeTaxesModifiedAccrual

We'd join them to be one table with 3 columns like this:	Label	Tag (full accrual)	Tag (modified accrual)
Accounts Receivable	acfr:AccountsReceivable	acfr:AccountsReceivableModifiedAccrual
Cash Equivalents	acfr:CashEquivalents
Allowance For Income Taxes		acfr:AllowanceForIncomeTaxesModifiedAccrual

If it's easier than using Excel, you can also do this in Python using pandas.

Let me know if anything is still unclear! I know it's a lot of accounting jargon....

[lucakato]

Yes -- anything that says "Doesn't Exist" in column I can go beneath row 522 in column H. (Although acfr:CashCheckingModifiedAccrual should exist -- it looks like it might have been accidentally deleted from F2).

@kwheelan I'm a little confused because column I for acfr:CashSavingsModifiedAccrual says exists I not `doesn't exist' but you still want it to be added to column H?

I'm going to try doing this in Pandas since the Excel is getting a little messy.

[kwheelan]

I was looking at an earlier commit, and your latest push doesn't have this issue, so don't worry about it. Sorry for the confusion!

[kwheelan]

Also @lucakato if you want a break from this Excel stuff, feel free to work on issue #25 and come back to this. The lookup table has become a lot more complicated than I intended

[lucakato]

@kwheelan Thanks. I'll prob leave it until the next time we meet because I don't really get how the table is supposed to look, visually.

[lucakato]

@kwheelan I modified column H to include ModifiedAccrual tags in the Taxonomy spreadsheet but not in the appended tags. Could you check if it looks good? Which columns would you like me to keep? And what would be a good name to rename column H too?

[kwheelan]

Perfect! You can just keep columns A-C and H and rename H to "Modified Accrual XBRL Tag."

If possible, could you also add the nicely formatted labels in column B for rows 523 through 602? These labels will be in the "GovernmentalFunds" tab of the taxonomy in column A. Or if it's easier, you can just use an Excel function to remove the "acfr:" and add spaces to the entries in H. Let me know you need more clarification.

[lucakato]

@kwheelan Thanks I'll add that soon. Is category not needed for rows 523-602?

[kwheelan]

If you feel comfortable categorizing them yourself, feel free! But I think the accounting categories might not be obvious, so I'm happy to add those in myself after you push the rest.

When you're finished, could you commit with a commit message that starts with "#10" to link it to this issue?

[lucakato]

@kwheelan just pushed!

[kwheelan]

Awesome! Thanks for your work on this! Feel free to move to #25 and comment any questions there

[lucakato]

Thanks, I'll close this issue for now.