July 30, 2008
TO MAKE THE CS ENGAGEMENT FINANCIAL STATEMENTS WORK:
Set up the trial balance the way you like it. I prefer:
Leadsheet (summarizes amounts for the reports)
Grouping code 1 (defines program)
Grouping code 2 (defines expense type)
Grouping code 4 (defines donor restricted versus unrestricted)
Grouping code 5 (defines account type for income statement accounts)
Do this by opening the trial balance, right click on the heading, and choose ?view maintenance?. ?Edit?. Then add, remove, and rearrange the columns the way you want them. Hit ?enter? to save the new definition, then ?done? to exit. You have to do one more thing to the trial balance. You have to get rid of the subcode columns. They take up too much room. Click and drag the right border of each to eliminate it. Reset the size of each of the grouping code columns; make them smaller, so the word ?Grouping? just fits. If you need a wider screen to see all the columns, reset the ?Description? column to very narrow, eliminate the ?subcode? columns, then reset the ?Description? column so the trial balance fills your screen again.
Code all the accounts in the trial balance.
Lead schedule - Make sure every balance sheet and income account has a lead schedule assignment. Create a new lead schedule (Z) named ?zero balance lead schedule? (you?ll need it later).
Grouping code 1 ? is primarily for expense accounts to indicate what program an expense is coded to. If the client doesn?t code their expenses by program, code them all as unassigned (00) in grouping code 1. If the client does code their expenses by program, use program codes in grouping code 1 that match or closely resemble what the client uses. If we have to assign our own default numbers for grouping code 1, use 10, 20, 30, etc for programs, 98 for fund-raising, and 99 for management and general.
Grouping code 2 ? is primarily for expense accounts to indicate what type of expense they are.
If the client doesn?t code their expenses by program, you don?t need lead schedules for expenses.
If the client does code their expenses by program, then create lead schedules for every expense account. Expense lead schedules are a combination of grouping code 1 and grouping code 2, with grouping code 2 going first:
Grouping code 1 = 10
Grouping code 2 = 99
The Lead schedule code will be 9910
Grouping code 4 - is for income and expense accounts to indicate whether they are unrestricted, temporarily restricted, or permanently restricted (Use permanently restricted sparingly. It is rare for anything to be truly permanently restricted).
Grouping code 5 ? is for all the income and expense accounts to indicate their account type. Sometimes clients will mix the order of revenue and expense and being able to sort by this grouping code will make our lives easier.
If the client changed account numbers; reused an existing number; close out of engagement, reopen the client, go to chart of accounts, edit, renumber, and add a ?P? to the end of the account number. Then create the account again with the new description. This will allow us to code the new account correctly and preserve the historical information for our five-year graphs.
If you have account numbers with an extra digit (like a ?P?), you may not see them on the trial balance where you expect them to be. They may be way at the end of all the accounts. To see them in context, highlight the ?Number? column on the trial balance, right click it and choose ?sort ascending?, and it will sort them into the right order.
When you?re through with the trial balance, be sure you close the trial balance so the program will recognize the changes you just made.
TO FORMAT THE FINANCIAL STATEMENTS:
To edit the financial statements, go into ?engagement? (tree view), and select the financial statements to work on. (I like to put them in financial statement order first, balance sheet, income statement, functional expenses, cash flows, graphs, report, ML).
When you?re editing the financial statements, Save early and often. Never know when things are going to go south and you?ll need to fall back to the latest saved version.
File/page setup, 100%
View/page break preview
Set column widths
Description column 70. Number columns always 10, unless it needs to be 11 or more for large numbers (1,000,000). Whatever you add to the number column widths, subtract from the description column width so the page remains the maximum width possible given the settings above. Set the size on the menu bar to 100% to see if the columns are wide enough for all the numbers. If you have to make a column wider, take it from the description column.
Even for first year audits, set them up for two years at first.
(We can always move the extra column off the page after the exit conference. To move the extra column off the page, highlight the data and move it to the right by two columns. This preserves the headings and column widths. Then it will be much easier the next year to just move the data back without having to reformat everything.) If you have to eliminate anything out of the headings (like ?With comparative totals for 20xx?), just cut and insert it below the page so it will still be there when we need it again next year.
Row heights for all rows is 15 except the row-and-a-half lines which are 22. We do a row-and-a-half once in the heading, then after every underline.
To make the page length right, we adjust the row height of the line ?The accompanying notes are an integral part of these financial statements?. Add just enough row height to push the page number off the bottom of the page, then subtract one. The row height of the adjusted row should end up between 15 and 30. If it?s more than 30, make it less and add another 15 row above it. The row height of the adjustment line is probably already set, Jamie does it for each report before it goes out, so we should probably just leave it alone. The adjusted row height is specific to the printer the financials are being printed on so even if it doesn?t look perfect while you?re working on it, settle for ?close? on the draft report. Just add or subtract rows until it?s approximately right.
Fix the dates on the column headings and statement title (with comparative totals for ? if it?s a two year report).
The checksums for the balance sheet are easy; they?re just arithmetic to see if the balance sheet balances. If the check sums aren?t zero, you can create new ones below, for assets, liabilities, and net assets to narrow down where the problem is.
Go to cell A1 and hit F7 for spellcheck.
If you get stuck
If you?re having trouble making a particular financial statement work, do what you can and move on. Keep moving. Go do another one. You can always come back to it fresh later.
Same size, margins, and column widths as for the balance sheet, except the description column will probably be 45.
The checksums for the income statement are different. There is one at the bottom of column ?H? (assuming we?re showing unrestricted and temporarily restricted columns) to tell us if the net assets we?re beginning with this year agree to the net assets we ended with last year. It is simple arithmetic, but critically important. I try to resolve any issues here before I get too invested in the rest of the audit. If this is off by a material amount it can be a deal breaker.
The checksums in both the ?Total? columns are the same. We want to know if the ending equity amount is correct (and will agree to the balance sheet). We do a Creative Solutions formula for ?Total Equity? plus ?Net Income? for the year and subtract that from the ending equity in the column above. Since these are credit balance accounts, the formula will actually read ?Total Equity ?Net income Variable ?(cell reference). When it is necessary to make a formula negative, add the minus sign between the ?=? and ? CSAFormula? in the formula cell at the top of the Excel page.
The checksum for the ?Unrestricted? column does the same thing the ?Total? column checksum does; check to see if the ending equity amount is correct. It can?t be as global as the one for the total column though. It has be unrestricted equity plus unrestricted activity, minus the cell reference. ?Lead schedule ?UU?, ?Grouping code 4 ?U?, minus the cell reference.
The checksum for the ?Temporarily Restricted? column does the same thing as the checksum for the ?Unrestricted? column but compares temporarily restricted net assets plus temporarily restricted activity to the cell above. ?Lead schedule ?WW?, -Grouping code 4 ?T?, minus the cell reference.
To see if the checksums work, if the client didn?t code their expenses by program, change the code for program expenses from 10 to 00 (unallocated) temporarily so we can see the total expenses. We can change this formula back to a program code once the expenses get allocated.
Prior year amounts
Make sure the prior year numbers agree to the prior year report.
This can have one tab if the client allocates all their own expenses, or two tabs if we help them with the allocation.
If the client allocates their own expenses, each number cell in every row and column will have a lead schedule reference. The lead schedule reference will indicate the expense type and the program charged. Every lead schedule formula on a line will have the same leading digits. Every lead schedule formula in a row will have the same ending digits. This can be tedious to set up, but once it?s done it will repeat nicely from year-to-year.
If the client does not allocate their own expenses, the formulas on the first page will be references to the second page; the worksheet. We set up the worksheet by type of expense, using grouping code 2 in numerical order of grouping codes. Always leave them in numberical order on this page. It?s easier to figure out what you?re missing when they?re in numerical order.
The checksums at the bottom of each column compare the total expenses from the trial balance to the column total (for each year). In the past we?ve used a grouping code for this but it?s unreliable. If the formula hasn?t been switched over yet, switch it over now to compare the grouping code 5 ?E? (for expenses) to the cell above.
Back to the functional expenses. This worksheet won?t work until you go into cs engagement and run the rounding utility. You?ll have pennies on this worksheet and you need whole numbers. You can?t get at the rounding utility until you close every workpaper, close the client, and reopen it. Before you go into ?tree view?, you?ll get a ?utility? option at the top of the page. Click that and run the rounding function. Now open the functional expenses page and you?ll have whole numbers. You?ll also have checksums along the rows on the right side. For every checksum that is off by 1 or 2, go to the management and general column and add or subtract 1 to make any rounding difference go away. It is important that every line add across with no rounding difference. There will probably be a salary allocation below the list of accounts. You should get that updated each year.
Now the tab for the financial statement itself. It pulls numbers and descriptions from the worksheet. It is okay to change the order of these lines each year to put everything between ?payroll taxes and benefits? and ?all other? in descending order of amount for the current year. Fix any headings. Set the row heights to fifteen and fix the page length (view/page break preview). Column widths 10, description column as wide as you can make it without going off the page. The checksums in the total columns are the same as the ones on the worksheet page, (Grouping code 5 for total expenses less the total expense line). The checksums under each of the functional columns compare the grouping code 1 totals for each function to what shows up here. They will all have numbers in them until we go do a journal entry in cs engagement to record this allocation. We usually do that a little later in the process, but you can do it now if you want. The journal entry can be updated later as you proceed with the audit work and adjustments.
To change the order of the rows, we use the ?data/sort? function in excel. Highlight the rows you want to arrange, click data/sort, sort first by the current year column in descending order, then by the prior year column (descending)(often columns H and J).
If there are any large amounts in the ?all other? section at the bottom just off the page, move those lines up into the body of the statement by highlighting the row, right click ?cut?, go to the body of the statement highlight a row, and right click ?insert cut cells?. You can move any incidental lines down into ?all other? and reorder the lines any time you want.
On the ?cashflows? tab for the financial statement, type in the prior year amounts as hard numbers from the prior year statement. All of the current year numbers come from the ?detail? tab.
On the ?detail? tab, you may have to fix some formulas. The logic here is to create a balance sheet for each year that totals to zero. The ?difference? column shows the amounts we have to accommodate on the statement of cash flows. If we get every balance sheet lead schedule represented here, and get the formula right for net assets, each column should net to zero. The formula for net assets is ?account group for total equity plus ?net income variable? for activity.
Down below the balance sheet section, we break some of the differences out further. We split the change in fixed assets into depreciation expense, plus additions. Any change in investments is split out into additions, withdrawls, and the income components. Early on in an engagement, you can just put one number in ?interest and dividends? to make it balance, but you?ll have to come back after you?ve done the investments workpapers and put all the detail in.
Start with the totals graph. Make two blank rows between the balance sheet part and the income statement part.
There are two rows of checksums. The first is just to confirm that the balance sheet balances. The second is to confirm that net assets works from year to year. It?s nice when everything works and the checksums are all zero. Sometimes we just let small differences go and don?t worry about them. Sometimes the first time we do a job we have to adjust the beginning net assets and don?t go back to correct prior years so we just accept a difference in the equity checksum for a few years until those old years drop off the graphs.
To make the equity checksum work the first year, you?ll probably have to go to the functional expenses tab and change the program expense (grouping code 1) from 10 to 00 so you?ll get total expenses. Go through each tab and correct any coding issues (see below).
The tabs for the data
Each line is described by a lead schedule code (Mostly. Some are described by other codes). If you?ve already done the financial statements, you can look at them to help you debug any graphs that don?t work. If we haven?t done the job for a full four years, some of the older numbers are probably hard numbers typed in. You may have to look in the prior year graphs to make sure you have the right hard numbers in the right columns. (If your equity checksums on the ?Totals? graph don?t work, that?s a good clue that the prior year numbers need some work.)
The checksum is the account group code for total assets.
The checksum is the account group for total liabilities.
The checksum is the account group ?total equity? ? net income variable
The checksum is Grouping code 5 ?R? (for revenue).
Expense by function
The checksum is Grouping code 5 ?E? (for expense).
Expense by type
There are two checksums, one to see if the total by type agrees to the total by function. The other is Grouping code 5 ?E? (for expense).
The tabs for the actual graphs
These will be easier to work on if you reduce the size of everything to 75% so you can see the whole graph at once. You can?t reduce the size if you?re on the graph, you?ll have to click on an open spot in excel, off the graph, to get the size option. I usually select ?all tabs? then click on an open spot then set the size at the top of the excel page to 75%. Remember to click on a different tab before you do anything else so you don?t accidentally edit things you don?t mean to edit. I almost always remember to click on a single tab to deactivate the ?all sheets? selection before I start editing individual graphs.
To set the range on the left side, right click on the range, format axis, scale. We set minimum, maximum, and major unit. The minor unit doesn?t matter. The major unit matters a lot. I want it to be 10% of the maximum, but I also want it to be a number that divides well into 100. If the maximum is 100,000, the major unit can be 10,000. That works. If the maximum needs to be 120,000, we don?t want to make the major unit $12,000, leave it at 10,000. 10,000, 20,000, 25,000, 50,000, 100,000 all work well. The minimum is usually zero. If we need a negative minimum, it must be a multiple of the major unit. If the major unit is 10,000, the minimum could be (-10,000, -20,000).
We want the range on the left side of every side graph to include dollar signs. To change, right click on the range, select the ?number? tab, select ?currency?.
Check the headings (even the name). To edit, click once in the heading box to activate it, the click where you want to edit it. Be careful not to move it around on the page.
On the totals page, we have two lines of checksums. The first line is just arithmetic to confirm that the balance sheet information balances (=assets-liabilities-equity). The second checksum line is to confirm that the ending equity from each year carries forward as the beginning equity the next year (=prior year equity+current year excess(deficit)-ending equity. These checksums won?t work until each individual page works.
The graphs tab for each data tab has to include the appropriate range. The graph page for Totals may not have the right range. To adjust the range, right click on any bar on the graph and select source data. Select the ?series? tab. There are three cells on this page for which to select the source data for each year. The ones for name are probably right, we?ll fix the ones for ?X axis labels? and ?values? if we need to. Click the box to the right of the data description for ?X axis labels? and it will take you to the source. If you?re just setting this up for the first time, it won?t go anywhere, you?ll have to tell it where to go. But this time, we already have information in there, so we?ll just edit it. We?ve selected the data range for ?X axis labels? for the current year. Now Excel is showing us what data has been selected to show up on the graph (You might have to move a floating toolbar on the page so you can see it). We want to show everything from assets to excess(deficit). If that range has not been highlighted, click on the top cell and drag down to the bottom cell, then hit enter. You?ll be returned to the source data menu. It may be that nothing has changed on the graph yet, but that?s okay. Now, with the current year still highlighted, click on the box to the right of ?values?, and that will take you to the data range for values. Click and drag down to highlight the entire range for values and press enter. Now the graph looks different. Now it includes all the data for the balance sheet and the income statement. Repeat the ?values? data range selection for each year. You don?t need to reselect the X axis labels, that part will carry forward automatically to each year.
Assets tab ? These will be the same codes as on the balance sheet; leadsheet totals that add up to total assets. The checksum will be the account group ?total assets? minus the line above for the total of the lead schedules.
Liabilities ? pretty much the same stuff as for assets, except the sign is reversed with a minus sign all around. If any line is all zeros, we don?t really need it and should eliminate it. You might need it again the next year, so don?t delete it entirely, just cut it and put it down below out of the data range, but still within sight on the page.
A word about spacing. To make the bars on the graph look right, I like the total for the data range to be on about line 18 plus or minus a row. Any less than that and the bars get too large. On the liability graph, there are often only a couple things, so we have to put in lots of blank rows so the bars on the liability graph will be about the same size as the bars on the other graphs. If there are only three lines on any graph, you can just add empty lines between them until the total comes out about line 18. If there are only two lines, that spacing looks goofy, so we change the logic. We add an empty line above the first row and below the second(last) row. And put as many empty lines in the middle as we need to make the total line come out about row 18. Doing this is not as easy as it would first seem. If you just add rows, they?ll be outside the data range and then you?ll have to go correct that too. What I usually do is insert some extra rows below the two data rows, then copy the entire row (down one at the top, up one at the bottom). Then delete the original data at the top and bottom without deleting the row. This preserves the data ranges. Then add or subtract rows in the middle until the total comes out on line 18. Enough of spacing.
Net assets. The formulas are the same as on the equity section of the balance sheet. The line for fixed assets is a formula picking up the net fixed assets number from the assets schedule and subtracting any debt on fixed assets. The checksum should be the grouping code for total equity, plus the net income for the year, minus the total of the lines above. (Remember that these are credit balance accounts so you have to add a minus to each.) Make sure you have all the components of equity here. It?s easy to have something like board designated equity on the balance sheet and miss it here.
Revenue. Same as most of the other graphs; leadsheet totals; checksum is total revenue. But we get to do something new on this one. Instead of having the rows appear in order of liquidity, like we do on the balance sheet, we get to order these rows in descending dollar amount (with ?all other? and ?in-kind? at the bottom. Do this with the data/sort feature. Highlight the rows you want to sort (not including the top row, don?t want to mess up the data range), and not including All other or In-kind, and sort first by the current year column, then the next year, then the next.
Expense by function. Same as on the income statement. Program, management and general, fund-raising (for consistency we always use the dash in the middle of fund-raising). Use grouping code 1 to describe each functional category, total expenses for the checksum. I like to insert an extra line between the program amounts and the management and general row, just to separate program from supporting services visually on the graph.
Expense by type. These items are described by grouping code 2. Not very many lines will fit on the graph and still have room for descriptions below each expense type on the graph, but if you hyphenate cleverly, you can probably get the total on line 25 and still have all the descriptions show across the bottom. You won?t know if it?s going to work until you go to the graph, click on an open space at the top to highlight ?chart area?, then push the button for print preview (the page with a magnifying glass).
Every year, you?ll have to evaluate which lines get included on the graph and which get delegated to ?all other?. Generally, we put the largest items in data range for the graph. Always start with Salaries, then payroll taxes and benefits. Always end with ?all other? then depreciation if it?s material. Data sort the rows in the middle in descending order. Then go to all other. Always leave miscellaneous at the top of that. Data sort the rows below it. If there are any amounts in the current year column under ?all other? that are larger than amounts included in the actual graph section, consider exchanging them. Generally, we want ?all other? to be as small an amount as possible.
That?s it for graphs. Go back to the totals page and see if everything worked. Check the scales. The total graph can have any scale it needs to show everything. The minimum can be less than zero to show a loss for any year. The balance sheet graphs should all be on the same scale. The income statement graphs should all be on the same scale. It can be a different scale from the balance sheet graphs.
Same margins, .75 all around except on the right where it is .50 (file/page setup). The font is CG Times 11 except for the name on the cover which is generally CG Times 16. The name is usually on the 16th line down, but they?ll make sure it is centered in the cover stock cutout at the office.
Make sure the heading is right for audit/review. In that regard, watch the table of contents, the audit/review opinion, and the reference to the prior year opinion. It?s easy to mess up the reference to the prior year opinion when the client alternates between audits and reviews each year.
Check the ?years? in the page headings and text. They?re formulas. To edit them, click the ?fx? button toward the upper left of the toolbar. Once we get the dates all right, they should be right forever; except the audit/review opinion date. That is still typed in as a hard date; and the reference to the prior year opinion in the opinion letter; that is a hard date too.
Table of contents; line spacing 1 ½ lines after the name, single lines after that. ?Contents? on the 4th line down, ?Page? on the 4th line after that.
Opinion; date on the 8th line down, 4 lines after that for the heading, another 4 lines to the address. This will center the opinion vertically on the page unless there are extra paragraphs in it. Five lines down(five returns) from the end of the opinion to the signature block.
Make sure the dates are right in the opinion for current year, prior year references.
We have a bunch of blank pages in, with nothing on them but headings, just to use up page numbers, so everything will come out right on the printed report (where the page numbers show). A more sophisticated Microsoft Word user would delete these blank pages and make the page numbers come out right some other way. We haven?t taken the time to figure it out.
Footnotes; 1 ½ line between client name and ?notes?; everything else single lines.
Note 1, generally the client mission statement. Generally doesn?t get edited from year-to-year.
Note 2, Mostly boilerplate. Item 5, the capitalization limit doesn?t usually translate in the conversion from GoFund to CS Engagement. We used to use a formula for this but there is probably no benefit, so just look at what amount was there in the prior year report and type it in. Item 7, make sure the reference to C3/C4 is appropriate. Make sure the date is right in item 8. Make sure there is an item 9 if we are presenting prior numbers. Make sure the date is right in it.
The remaining notes are in order of appearance on the financial statements. Note 3 is generally for investments or fixed assets. Each footnote tends to explain a number that shows up on the financial statements. Each number on the financial statements is generally represented by a lead schedule. For almost all footnote tables, there is a column off to the right with the lead schedule total in it, that won?t show on the printed report, but will serve as the basis for a checksum so we know our footnote agrees to the number on the financial statements. Footnotes that don?t refer to any specific number on the financial statements go at the end of the list.
For the detail in the body of each footnote, we look for a way to link it to the trial balance (often this can be the account number). If we don?t find an easy way to link it, just type in the hard number. The checksum will tell us if we made a mistake or if a number changed. When you want a number in a footnote (whether in the body or for the leadsheet code on the right) from a credit balance account to show as a positive number, you have to make a multiple formula and use that leadsheet code ?Z? you created a long time ago for no apparent reason. Highlight a formula (click, it until it is gray, not black), punch the ?fx? button on the toolbar. Variable type-leadsheet code, variable name-Z, period-CY, year-C, Operator-?minus sign?. Then on the next line put in the leadsheet code (or any other number that you want reversed).
Now the only remaining challenge is the table formulas. They?re different from Excel. To get to them, click table/formula on the toolbar. The most convenient formula is ?=sum(above)?. That will total the column. When you don?t want to total the entire column, like when there are subtotals in the column, you have to very specific about what you want the formula to do. You have to specify exactly which cells are included in your formula, and these formulas will not update when you add or subtract lines so you have to remember to edit them each time you change something, preferably before you tell the footnotes to recalculate. If a table formula tries to recalculate and any reference within that formula no longer exists, the formula will crash and you?ll have to recreate it (or press the undo button) and start again. Recreating a formula is more work than just editing an existing one before you screw it up. The cells in a table are numbered just like in Excel, except the cell reference is not displayed anywhere. You?ll have to count letters for columns and numbers for rows. The checksum at the bottom is always a reference to the number right above minus the leasheet code off to the right.
Tables can be copied and pasted easily, so if you have the table structure you want somewhere else in another footnote.