First the account groups must be set up with the desired ranges and formatting. Next, the desired set of account groups and ranges should be selected, then dragged and dropped in the blank spreadsheet.
You then get the opportunity of selecting which columns to include:
Now that you have determined which accounts and which information will appear on your statement, you get the opportunity of determining layout properties such as column order and other formatting:
If you choose to enable the Departmental Comparative option, you are allowed to select individual department codes, which will be presented on the statement in their own column. You may also include a company total column and also rearrange the column sequence:
The statement will then be generated:
The statement preview looks pretty nice even without any further modifications, but you may want to tailor the statement even further to conform to your needs:
There are two ways to open the Financial Reporter. Maintain Financial Reports (Maintenance menu) allows you to edit specifications and other setup options in addition to printing. Print Financial Reports (under the Reports menu) has some editing functions disabled. This is to allow users to print reports but not change them via the security options. The toolbar button will load the Financial Reporter in Edit mode unless security prevents this, in which case Print mode is used.
Use File | Open to load a statement. Statement names begin with ‘GL!’ and have the same extension as the accounting data.
Using the =ADGET() function
The main function for reading Account data is the ADGET function.
It is made up of 3 parts in two string parameters; =ADGET(“gldata.XXX”, ”account key”).
‘gldata’ is a link name and is required for the first parameter of this function.
‘.XXX’ is the field code, determining which information will print in the cell. A list of the available codes is accessible from the “Account Data” tree, or using File | Send to sheet | Account data.
Example: “gldata.M” represents the field code for Current Period Net Change in Balance.
‘Account key’ is a string containing a single acct-dept or an acct-dept range. It may be entered directly into the formula or referenced from a different cell.
Examples of valid account ranges:
An account range, reading the department from the toolbar (5000:5999-`)
An account range for a specific department (5000:5999-100)
A department range for a specific account (5000-100:300)
A range of accounts and departments (5000:5999-100:300)
Wildcards are allowed (5000-?00 or 5?00-100 or 5*-100)
View Account Data: For viewing which fields are available to select and their respective codes.
View Account Groups: For setting up account ranges and creating statements from scratch.
From here, you can pre-define cell and row formatting for each group when creating new specs.
View Departments: For creating Departmental Ranges, Masks and viewing defined departments.
View Chart of Accounts: For viewing defined accounts. You may drag and drop from the Chart onto existing Account Groups or a SmartSheet financial statement.
View Statement Groups: For selecting a number of completed financial statements to print in a single print run. The same statement may be included multiple times with different options.
If you want specific information to print on the header or footer of each page, there are special codes that are placed in the Header/Footer sections (accessible from the File menu).
APP NAME: $A SHEET TAB NAME: $R COMPANY NAME $C
DATE: $D FILE NAME: $F PATH TO FILE $X
PAGE #: $P # OF PAGES: $N
As with all functions, it doesn’t matter where the information for a function parameter comes from. It can be directly part of the formula or referred from a different cell. If you need to make changes, it may be better to use cell references; make one change rather than a change for every formula. You must use the F9 key after changing information to refresh formulas using cell references.
This is useful for department by column statements, or when copying rows/columns.
Use with STRCAT or the ‘&’ character to include hyphens and colons when necessary.
The $ character is used with cell references and copy/pasting. It determines if row/column references are updated after pasting or if they remain the same.
Examples of how cell references can be used:
“5000-“&E3 or STRCAT(“5000-“,$E$3) for reading the department from a specific cell
A10&”-100” or STRCAT(A10,”-100”) for reading the account from a specific cell
A10&”-“&E3 or STRCAT(A10,”-“,E3) for reading both the account and department from specific cells
Add columns or rows to a statement with far greater ease than manually entering each cell.
You can copy/paste individual cells, a range of cells, or entire columns.
When copying an individual cell, you can paste to multiple cells, rows, or columns.
Use E$3 when copying from one row to another in order to maintain the cell reference to the department code in row 3.
Use $A$5 when copying from any cell to another in order to maintain the cell reference to a cell not column or row specific.
Use Edit | Replace to change a value to another value, such as field code or account number.
For example, to create a new column that displays the Budget YTD amounts (code ‘.BY’), and you already have a column with YTD Net Changes (code ‘.Y’), then you only have to copy the existing column, paste it to a new column, then use Edit | Replace to change all occurrences of ‘.Y’ to ‘.BY’. All formatting, totals and cell references are maintained.
These are used to organize some or all of your accounts into related groups and sub-groups, each with their own titles, font styles, and subtotals.
An account group will be a higher-level organization, such as Assets or Income.
An account range will be the lowest level group of accounts, such as Current Assets or Revenue. Ranges are the place where particular accounts are actually assigned.
When assigning accounts to a range, you can either type in the ranges manually or drag accounts from the View Chart of Accounts window and drop them on the desired range.
You may choose to have account ranges only show a single row on the financial statement, subtotaling numeric amounts for all accounts within the range. Alternately, you may choose to have the account range list individual accounts on separate rows to show a detailed breakdown.
Right-click on the View titles and choose ‘Reset to default’ to copy group structure from the Ledger. Choose ‘Reset to Startup’ if you have made changes in this session you want to cancel.
Drag and Drop Statement Creation
From the View Account groups window, you can select groups and drop them onto a blank spreadsheet, saving you time instead of typing all those formulas and formatting all the cells.
Select the account groups and ranges you wish to base your statement on. If you select a higher-level account group, this will also select any groups or ranges contained within. Use Ctrl-click or Shift-click to select all appropriate account groups, and then drag to the empty spreadsheet.
Now that the accounts have been chosen, it is necessary to select which fields will be used as columns for the statement. It doesn’t matter the order you select the fields at this point, as the subsequent screen allows re-sequencing of the columns as desired.
Accounts will be sorted and subtotaled based on the layout defined in the Account group/range.
Typically your statement is not yet complete, and you may make additions or corrections as necessary. At this stage, there is no connection to the contents of the spreadsheet and the Account Groups layout. Editing your account groups will not affect the statement, or vice versa.
Posted Transaction Drill-Down
From within the Financial Reporter, you can drill down on any amount cell to see what transactions make up that amount. There are up to 4 levels of drill-down available. Found on the right-click pop-up menu, Details…
The first level is by account, listing all departments.
The second level is by account/department.
The third level is by fiscal period within the account/department.
The fourth level is the detailed posted transactions for the account/department/period.
Some screens allow you to select a different fiscal period.
Depending on the level of consolidation in the cell (i.e. the amount is based on one account only,
a range of accounts, etc), different levels of drill-down will be initially displayed.
Direct Export to Excel
On any statement sheet, you may click the Excel Direct export button and send the contents to a new Excel spreadsheet.
All cell formatting including fonts, positioning, lines and logos are maintained with this export. Only values are exported to Excel, not the underlying formulas.
You may wish to do this to take advantage of any features available in Excel that are not in the Financial Reporter, such as Print ‘fit to page’ or Email – Send as Attachment.
Rolling Income Statement
Lists the current period (as selected from the toolbar) in the right-most column, and the 11 prior periods in order to the left.
If any period other than 12 is selected, then periods that reach into the Prior Year are determined rather than Current Year amounts.
Lists the 12 current periods, plus a total of all 12 columns, the yearly budget and the difference.
In addition to a statement containing both a Balance Sheet and an Income Statement, this is followed by a list of common ratios calculated directly from the other statement amounts. Only a portion of the ratios in the report is listed above.
Financial Reporter – In Depth
SmartSheets – Expand/Collapse
A method of automatically generating rows for all accounts in an account range. If new accounts are added, this function will insert a new row and create all formulas in the defined columns.
The text ‘.SmartSheet’ must be in cell A1. This activates all of the SmartSheet functions.
Columns A and B are always hidden from printing in SmartSheets. Column A is reserved for generation information and Column B contains the account numbers. If you wish for the account numbers to print, you may type ‘.ShowAccounts’ into cell B1 in order to unhide column B.
An account range must be in any cell in column B except row 1. You can drag account ranges from the View Chart of Accounts window, and the correct range syntax will be automatically applied.
All other formulas must use cell references to column B to read the account key.
In the Collapsed view, you have a Summary display of your accounts. Typically you would define a range of accounts on a single row, such as all the asset accounts or expense accounts.
In the Expanded view, you have a detailed display of your accounts. Depending on the setup, you may have an account on each row (for all departments) or you may have every unique account-department on a separate row.
When Expanding a SmartSheet, the chart of accounts is read for all accounts specified in the range. For each account, a new row is generated from scratch. All formulas and formatting in the Collapsed summary row are copied to the generated rows and when applicable, cell references are updated. The summary row is not removed but instead is hidden. Since the summary row contains the totals for the range, it may be referenced when doing ratio calculations against the range total.
To Expand a row, you must include the text ‘.Range’ in Column A of that row. If this text is not present, expanding the SmartSheet will leave that row as a summary. This is useful if you wish to show details for some account groups but not others.
When Collapsing a SmartSheet, the generated rows are deleted and the summary row is unhidden. This means any changes you make in the Expanded view will be lost. All editing should be done while in the Collapsed view. Also, any formatting applied to a generated row will be lost when the sheet is refreshed, even if the same account number ends up in the row.
Insert Common Text
There are particular phrases that are standard to inclusion on a financial statement, and you may choose to auto-insert text rather than manually typing it. Right-click on any empty cell and from the pop-up menu highlight ‘Insert Formula ->’ and from there another fly-out menu appears with a number of choices.
Text that can be added includes Dates and Times, Company, Department or Fiscal Period information, or even a phrase such as ‘For the period ending Dec 31st, 2005’.
Freezing Rows and Columns
If you wish to have a set of rows – such as company information and column headers – or columns – such as account numbers and descriptions – remain on the screen while scrolling through a large sheet, this is possible by freezing the rows or columns. Select all the rows or columns you wish to freeze (not just the last row/column) and then select Format | Rows (Columns) | Freeze.
Freezing rows or columns have the added feature of printing this information at the top (for rows) or the left (for columns) of every printed page of your statement.
Because the Financial Reporter is a spreadsheet, you may create formulas to perform calculations on the amounts. Two commonly desired calculations are net variance and percentage of total.
Net variance (or difference) is simple: just subtract one cell from the other; e.g. = E10 – D10
% of total is more complicated because of two reasons: the total may be zero or the location of the total may change based on Expanding or Collapsing a SmartSheet. To solve the first situation, you need to use an IF function to test if the total is equal to zero; e.g. =IF(E12 <> 0, E10/E12, 0). For the second situation, there are 2 choices: 1) use a $ in front of the row number of the total cell in the formula – in order to make the cell reference expand properly: = IF(E$12 <> 0, E10/E$12, 0) or 2) as this is a SmartSheet, reference the hidden summary row for the total instead of the visible total amount: = IF(E8 <> 0, E10/E8, 0) where row 8 contains the hidden SmartSheet summary.
You might want to create a report comparing two or more departments side-by-side and the amounts for particular accounts. You can do this easily for drag-and-drop SmartSheets. Typically these types of statements will only include one or two fields, such as YTD actual or YTD budget.
When creating a new statement, choose the SmartSheet option ‘Departmental Comparative’. This allows you to select any or all of your defined departments, including any dept. ranges or masks.
Additional options allow you to insert a subtotal column for selected departments or all departments whether selected or not.
This is a process that determines if each account in a particular range appears in the report. Verify can be used with SmartSheets, but will usually be more useful with non-SmartSheets.
To set up, add ‘.Verify’ in a cell of column A. It belongs above the account rows you want to check. In the same row of column B, include the account range that needs to be verified.
To use, right-click on the cell in column A with ‘.Verify’. Choose the option to Verify Range. If all the accounts specified in column B are below, a message will appear indicating this. If any are missing, a screen indicates every account in the Chart of Accounts but not in the report.
This process will also check for duplicate accounts, verifying that any specific account is used only once in the statement.
Some statements are created in order to print information for a particular department, and the same statement needs to be generated for many or all of the existing departments. It may also need to be printed for a specific range of departments. Departmental Print is the feature to do this.
Departmental Print takes advantage of using the ` character to be replaced by the department code. When you use Departmental Print, a screen appears with the entire list of departments and ranges you have created; this is the same list that appears in the department drop-down on the toolbar.
Select as many departments you wish by clicking the right-arrow to add to the print list at the right.
You cannot preview what this will print – this is a print-only function. If you want to see what a sample statement will look like, exit Departmental Print and preview from the design screen.
Print Statement Groups
Allows you to print multiple financial statements in specified order with a single command.
Pre-define options for printing statements:
Current, prior or specific fiscal period
Specific / Consolidated departments, or department ranges.
Selected sheets from the workbook
Inactive account or zero suppression, expand SmartSheet
Include a ‘cover sheet’ specification at the beginning of the print run if desired.
An option exists to adjust page numbering between each statement or across all statements.
Print one or more groups from the File / Print Statement Groups menu.
Allows you to apply cell formatting conditionally to any row, based on data conditions. The statement must have SmartSheet enabled in order to use Row Formatting.
Create the format(s) you need from the Format / Styles menu. Examples are applying bold or italics, using a different font, changing font color, applying a background color, or cell borders.
In the cell in row 1 of the right-most column, add the text ‘.RowFormat’ and format the column to Hide When Printed – in order to suppress printing of the generated formula information.
For example, if the last column of the sheet is K, enter ‘.RowFormat’ in cell K1.
In the same column, if the cell of the row you wish to format contains the title of a font Style defined above, then that formatting will be applied to the row.
For example, to apply the Style ‘HighlightBold’ to row 13, type ‘HighlightBold’ in cell K13.
Most often, this will be done by creating a formula that uses an IF statement to test a data condition. For example, if you wish to make the text of a row with bold font if the value in one column is larger than the value in another column, use:
Column references update automatically when SmartSheet collapses or expands rows, or rows are either added or deleted.
You may ‘nest’ IF functions, thereby allow 2 or more potential formats:
The sheet name and cell reference are passed as strings.
While “B10” style cell references are permitted, it is recommended that a “Named Range” be used to specify the desired cell. Named ranges are updated as rows and columns move; “B10” as a reference for the cell is not automatically changed if it moved on the referenced sheet.
You can optionally pass a department code and fiscal period to the queried sheet, to make sure that it contains correct values for a given department or fiscal period. You need only include these values in the first call to =QUERYSHEET() (calculated from top left to bottom right).
You may not use QuerySheet to read data values from other workbooks or Excel spreadsheets.