Date: 22 May 2011
Sage Accpac Financial Reporter is very easy and strong reporting tool that Sage includes in General Ledger of Accpac. Financial reporter install with the Installation of the Sage Accpac GL (General Ledger). Since financial reporter using Excel for all the reporting, its strength to generate and represent report increase widely. Apart from using formulas and parameters given by financial reported we can use all the functions, formulas, graphs and features that are in standard Microsoft Excel. Further there is option to include the drilldown so that we can get more from the same report and get into the details of the figures that a FR (Financial Report) is showing.
Let’s tell you an interesting fact. If I am not wrong then many Sage Accpac consultant and end users even having couple of years’ experience in Sage Accpac are not very familiar with Sage Accpac FR. On a contrary FR is so simple and user friendly that even end user that do not have any technical knowledge can build a FR according to his need and represent the required data in a representable manner. Since FR using Excel therefore it become very easy for a person to build FR, who is already using excel. And the fact is almost all the accountant and end user of Sage Accpac has already worked on the Excel. Therefore we can say it is easy for all consultant and end user to build and use FR.
When we install Accpac GL we get FR inside GL. There we can see two icons “Print Financial Statements” and “Statement Designer”.
“Print Financial Statements” is used to print the financial report that we have already build. Here we can give different parameters that can be used to get our desired data on the report.
Another on is “Statement Designer” that is used to generate the FR. Statement Designer opens the MS Excel with the additional add-on utility called FR. Where we can find tools that assist us in writing new FR.
The first and most important step to write a FR is to define report specification. Each financial statement is defined in a report specification. The specification tells Financial Reporter what data is included on the statement, and how it should be formatted. Because the specification is actually a worksheet, you can use standard worksheet formulas, formatting commands, and graphing abilities to embellish the report, and you can create multidimensional models to perform further analysis on the data. To define report specification in the Excel, define a new name “SPEC” under formulas after selecting the rows and columns of your excel sheet.
If there is no Spec range in the financial statement worksheet, the Financial Reporter recalculates the sheet and prints it when you print the report. Any Sage Accpac formulas will be updated with the latest values from General Ledger.
We can redefine the spec range in our worksheet after report generation, for that use FR Clear to clean up the output area, and then make changes to the spec range.
The first four columns of the Spec range (A through D) are reserved for control information for the Financial Reporter. All other columns form a template which determines the appearance of the report and the type of data which will appear. The Spec range is already defined in all financial report specifications that come with the Financial Reporter. Similarly, if you are converting a specification from ACCPAC Plus for DOS, the conversion macro will define the Spec range.
Column A to D in FR
The contents of column A of each row (the first column of the Spec range) determine the purpose of all the columns in the row.
There are five types of rows in a report specification:
• Comment rows
• “As is” rows
• Default rows
• Title rows
• Body specification rows
If column A starts with “..”, the row is a comment row.
If column A is blank, the row is part of the report and will be printed as is.
If column A starts with “\\”, the row sets up defaults for the remaining rows on the report. You can have more than one default specification row; each one applies to the rows between it and the next default row.
If column A starts with “\T”, the row defines a title line on the final report. You can have as many title lines as you want to appear at the top of each page of the report (immediately under the header lines).
If column A contains any other data, that data is assumed to be a reference to one or more account numbers. Furthermore, if an account number appears in column A, it generally means that all the printed columns in the row will contain data from that account.
Although you specify account numbers in column A of the report specification, you can use columns B, C, and D to refine the account selection.
Column A can specify a range of accounts; it determines what Financial Reporter does when it generates the financial report.
Column B can contain a filter to restrict the range of accounts in column A to a particular account group (or to an account type or account name). Column B can also refer to the filter for posted transactions if Column D refers to listing by posted transaction details or by posted transaction consolidated details.
• Column C can remove any lines from the report that have zero balances (use “Z” to omit zero‐balance lines) (or meet condition such as “balances less than $1,000”).
• Column D can consolidate the balances of the range of accounts, consolidate by account segment, list each account separately, consolidate all values from retrieved optional fields, or print the optional field value of the first retrieved record. This can also be consolidated by transaction date and posting sequence.
The result can look like this:
The report template actually starts in Column E, the fifth column of the Spec range. Any value or formula placed in the report template area is generally part of the final report. The following worksheet shows a report specification displayed in formula view:
The ʺ\ʺ in the right three columns indicates that each cell value will be generated from the default formula set for the column. (The Financial Reporter inserts the account reference in column A into any default formula that requires the account reference parameter.)
Further we can see that in column G14 to I14 we have used Excel formulas to calculate the total.
At last I can say that by using FR we can build much complex reports that might be difficult to achive through Crystal.