Home

Sage 300 ERP 2014 Financial Reporter User's Guide

1. 0 00 0 c cece eee e eee e cece cece cece cece cease ees 12 Exercise 2 Applying the FRAMT Function 220 2200 22 200c cece cece ce ceecceceeeeeeeee 14 Lesson 3 Creating a Financial Report Specification 200 22 20 2220 cece e eee cece eeeeee 15 Exercise 1 Adding a Comment 2 00 200 c cece cece cece cence eee ec eee ee cee oaa aaraa 15 Exercise 2 Creating a Default Specification Row 2200 22002 c cece cece ee eeeeeeeee 16 Exercise 3 Adding Column Headings 220 022 20 ccc cece cece ec ceec cece ececceeeeeeees 17 Exercise 4 Adding Account Reference Lines to the Statement 2 22 222 2222 18 Exercise 5 Setting the Spec Range for the Statement 2 2220 22 222 22 eeee ee eee ee 20 Exercise 6 Generating the Statement 0 0 0 0 e eee eee ness nsn 21 More Suggested Exercises l l u 20 22 ccc cece cece ccc cece cece ccc e bccn ee see se esse se esee sone iu 22 Where To NOW oo 2 eee e ccc ee eee eee a cece eE e e L E onari e EEEE 24 User s Guide iii Contents Chapter 3 Designing Financial Statements 00000000 cc eee 25 Information Included On Financial Statements 2 00 2 0 cece eee e cece cece eee eene 25 Data Selection Criteria 2 2 2 0 00 20 c cece cece ccc cece cece eee eee ss cee esr se sess 26 Consolidating ACCOUNTS secius ese a ele n ececebr cda cli e a e erra cua 26 About Financial S
2. Important e The sorting order must be POSTTRAN if the consolidation value in column D is by P POSTTRAN e The sorting order must be POSTDATE if the consolidation value in column D is by P POSTDATE e The sorting order must be POSTSEQ if the consolidation value in column D is by P POSTSEQ e Anything that appears in column D on a default specification line will be used by all subsequent lines unless you specifically override it Examples of T and D Consolidation Settings The following examples show the effect of T and p settings in column D of a report specification Displaying Account Balances on Separate Lines Specifying D in column D lists each of the accounts between 1100 and 1200 on separate lines User s Guide 57 Chapter 3 Designing Financial Statements A B c D E F I FRACCT ACCTDESC FRAMT BALP Current Balance 1100 1200 D LI Current Balance Petty cash 274 57 Bank account 8 437 54 Bank account CAD dollars 4 372 96 Total 13 085 07 Displaying Account Totals on a Single Line Specifying T in column D indicates that all accounts in the range are consolidated A B C D E F IM FRACCT ACCTDESC FRAMT BALP Current Balance 1100 1 200 T Liquid assets The resulting report totals the balances of all the accounts between 1100 and 1200 and reports the total on a single line Liquid assets 13 085 07 Displaying Accounts in Segment Order Consolidate
3. Invalid consolidation of posted transactions on specification in row lt Row R gt Consolidated details cannot be generated using the combination of detail level and sorting order You entered an incorrect consolidation switch in this cell Follow the criteria in column D for consolidating account ranges This is a general message indicating that a formula or expression at the stated cell location cannot be interrupted by FR in generation Invalid spec line definition at cell location L with value V not a comment line or valid default line or body line Generated lines are almost over spreadsheet limit Report generation has been stopped The specified consolidation method for posted transactions on the spec lines in row R must match with a specific sorting order For more information see About Columns in Report Specifications page 39 Error Messages when Starting G L Statement Designer or Running Print Financial Statements Error Messages when Starting G L Statement Designer or Running Print Financial Statements Error in creating Excel object Make sure Microsoft Excel is installed Page Break Preview is not available in Print Financial Statements GL9100 Please go to Statement Designer GL9200 to use this function There are no printers installed One is required when print destination is Printer or Preview FileSystem Object object model cannot be initiated Please make sure that S
4. 174 Sage 300 ERP Financial Reporter Appendix F FAQ and Troubleshooting How Dol Create an FR report with drilldown that doesn t show the FR code If multiple users are viewing financial reports and the report specifications are not to be edited you use the G L Print Financial Statements screen to print the report and select the DrillDown Information option on that screen to include drilldown information in the report Enlarge the Spec range In some reports you might find that information is cut off the report because it extends beyond the right edge of the spreadsheet print area You can correct this by adjusting column width or by redefining the Spec range For more information about working with Spec ranges see About the Spec Range page 71 Exclude accounts from a range in FR You can specify selection criteria on a default specification line on individual specification lines and in formulas With all these possibilities for account selection you must be very clear which criteria are actually in effect For more information about the rules that apply when specifying criteria see About Generating Reports from Specifications page 28 and About Selection Criteria for Filtering Accounts page 62 Generate reports from specifications For information about generating financial statements see About Generating Reports from Specifications page 28 Turn off comments when Drilldown is selected I
5. 11 01 2009 FRFISCAL Start O U 20081101 FRFISCAL Start Li VALUE FR 11 01 2008 Period F User s Guide 143 Appendix D FR Function Reference FRPOST Syntax This function retrieves account and transaction detail information from posted G L transactions It can also retrieve transaction optional fields and account optional field data for the account specified in the transaction record Details can be consolidated or not consolidated That is if the result of FRPOST or FRACCT page 110 is derived from more than one transaction and if the result is from an optional field of type Integer Number or Amount you can print a consolidated total that sums up all values from the retrieved optional fields or print the value of the first retrieved record that has an optional field value e If an optional field name is specified as is or C is appended to the field name a consolidated total is printed e If U is appended to the specified optional field name only the first optional field value is reported FRPOST field name account reference criteria formatted The function can retrieve data from all G L account fields and account optional fields defined for FRACCT page 110 It can also reference transaction optional fields defined in G L Setup and the following G L transaction fields Transaction Field Name sce 144 Sage 300 ERP Financial Reporter Arguments Financial Reporter
6. Nothing happens when I click OK on the Financial Statement Generator If nothing happens when you run FR View check whether you have defined a spec range For more information see About the Spec Range page 71 and Setting the Spec Range page 73 I can t view some account descriptions in financial reports If some account descriptions are not appearing in financial reports check to see if the individual account descriptions have any common description for 176 Sage 300 ERP Financial Reporter Troubleshooting accounts that are being consolidated in the report To include these account descriptions in a printed report you may need to edit the descriptions Example Account number 6200 100 and 6200 200 are being consolidated into account 6200 If the descriptions are as follows e 6200 office expense total e 6200 100 office expense Richmond e 6200 200 office expense Toronto the description on the printed financial report will be office expense which is the common name for all three accounts If account 6200 used the description miscellaneous account instead the description would be blank in the financial report there are no common elements in the three descriptions The report date appears in short date format even when Excel cell format is set to long date If you have changed cell format in Excel to display long date formats but Financial Reporter still displays dates in short format you can us
7. This provides you with a report that is dynamically linked to your general ledger At any time you can see updated versions of a report with formulas simply by recalculating the spreadsheet To generate a statement 1 On the Excel ribbon on the Add Ins tab click the arrow beside FR and then click FR View The G L Financial Statement Designer screen appears User s Guide 21 Chapter 2 Financial Reporter Tutorial E BM G L Financial Statement Designer Fiscal Option Report Type Report As Include Report Options Y ear Period A 12 amp Actual C inis e 3 Audit Information C Provisional Consolidated Formulas M DrillDown Information Sort By C Account No Account L Segment From Q Account Group To ezz o qg Select Account Groups By Sort Code Range From Account Group a To Account Group zzz Q Segment Name Division zz Consolidated Region rae Consolidated OK Help Cancel 2 Click OK to accept the default print options The report is generated and appears in the worksheet 3 To display values instead of formulas in the worksheet on the Formulas tab in the Formula Auditing group click the Show Formulas button 4 To preview the document click File gt Print 5 Save your work and close the statement More Suggested Exercises Now that you have produced a simple financial report here are a few other ways to explore the capabilities of Financial Rep
8. nth hal year to date n half years ago nSA n half years ago to date Preceding half year 6 periods gt Preceding half year n periods ago 1pq handles any three periods providing you with revolving quarters The preceding 3 periods include the current period plus the two previous ones 2The NET for a group of periods that crosses a year end boundary will also include the adjustments in period 14 but not include the closing entry otherwise the net would include the zeroing entries in income and expense accounts 3This code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to date figures relative to the current half year If you are now in period 2 of the current half year half year TD will give you the first 2 periods of any previous half year nSATD lets you go up to 2 half years ago If you need to specify more use nSATD L1 5PS handles any six periods providing you with revolving half years The preceding six periods includes the current period plus the five previous ones User s Guide 159 Appendix D FR Function Reference Total year net change Field Description Reporter Code Suffix NET net changes Preceding year 12 or 13 periods NETPY C Note e You can print year end adjustments and transactions to close income accounts to retained earnings with NETADJ and print closing figures with NETCLOSE and BALCLOSE e All fiscal field set values
9. About Financial Statement Specifications Overview Use the Statement Designer to create or edit a report specification for generating financial statements When you open the Statement Designer screen and then click the Start button Sage 300 ERP starts Microsoft Excel with the Financial Reporter add in Important This add in is available only when you start Excel from the Statement Designer The Financial Reporter add in is a set of commands that you select from the FR menu on the Microsoft Excel ribbon For more information see FR Menu Commands page 93 Tip Turn on manual calculation in Excel so that formulas are not updated automatically You can recalculate FR commands at any time by pressing the F9 key Using the Statement Designer to Design Financial Statements For information on using the Statement Designer to create custom financial statements see Designing Financial Statements page 25 For a series of short lessons on using the Statement Designer see Financial Reporter Tutorial page 7 For information on the fields that you can use in financial statements see e Account Master Fields page 171 e Transaction Fields page 173 Including Information in Printed Financial Statements You can include the following options when printing financial statements from the G L Financial Statement Designer by clicking FR FR View on the menu bar e Report Options e Audit Information e Formulas e Dril
10. Account processing and sorting order Account selection ranges On the G L Print Financial Statements and Statement Designer screens you can select the DrillDown Information option to add drilldown link comments to the generated report The Drilldown Information option is available only when you select File or E Mail under Print Destination For more information see About FR Drilldown page 94 For information about the Audit Information and Formulas options see G L Statement Designer page 30 7 Under Sort By specify the order in which to sort records in the printed financial statement If you select Account No you can use the From and To fields to specify a range of account numbers to include or leave the default values in these fields to include all account numbers If you select Segment select a segment in the list that appears You can use the From and To fields to specify a range of account numbers to include or leave the default values in these fields to include all segments 8 Specify account groups or sort code groups to include If you want to select account groups by sort code range select the Select Account Groups By Sort Code Range option You can use the From Sort Code Account Group and To Sort Code Account Group fields to specify a range of sort codes to include or leave the default values in these fields to include all sort codes If you did not select the Select Account Groups By Sort Code Range
11. Lesson 1 Using Formulas to Retrieve G L Data This lesson introduces Statement Designer functions and demonstrates how to construct basic statements that extract financial data from your general ledger Before you start 1 Sign in to a sample company database Sage 300 ERP includes two sample databases you can use SAMINC and SAMLTD SAMINC has a single currency general ledger with a US dollar functional currency SAMLTD has a multicurrency general ledger with a Canadian dollar functional currency a Open Sage 300 ERP b Select SAMINC or SAMLTD c Enter ADMIN in the User ID and Password fields and then click Open 2 Open the Statement Designer a Open General Ledger gt Financial Reporter gt 8 Sage 300 ERP Financial Reporter 3 Lesson 1 Using Formulas to Retrieve G L Data Statement Designer b On the screen that appears click Start to open a new Excel spreadsheet Turn off spreadsheet recalculation in Excel If spreadsheet recalculation is on Excel will query the Sage 300 ERP database every time you enter a formula in the worksheet or navigate away from the FR View screen a Click File gt Options b On the Formulas tab in Calculation options select Manual c Click OK Exercise 1 Adding Basic Account Information In this exercise you will enter formulas manually in a spreadsheet to create a basic financial statement with account numbers descriptions and balances Tip To avoid losing you
12. balances or include rollup amounts or totals Syntax FRTRN field name account reference criteria currency Note FRTRN returns O if no transactions are retrieved from transaction history Arguments jAroument Deerphon field name The name of a field from the General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal year suffix separated from the field name with a period The fiscal designator prefix can be e A Actual amount e Q Quantities e QA Actual quantities Note If no prefix is supplied the default report setting is used actual The fiscal year suffix can be YYYY A specific fiscal year such as 2010 Ln n years ago relative to the current fiscal year for example L1 is last year and L2 is 2 years ago Nn n years in the future relative to the current fiscal year for 150 Sage 300 ERP Financial Reporter Financial Reporter Functions gumet Description 00000 example N1 is next year and N2 isthe year after next Note If no suffix is supplied the current fiscal year is used account A string specifying a single account or a range of accounts See About reference Account Number References page 59 criteria An expression that imposes selection criteria on the accounts and on Optional transaction fields including account and transaction optional fields The account reference and expression together determine the accounts and ad
13. processes 1 Designing the chart of accounts 2 Creating a financial statement specification 3 Printing the financial statement Designing the Chart of Accounts When you set up the ledger decide on an account numbering and classification scheme to suit your financial reporting needs A well designed chart of accounts will simplify the job of designing and maintaining financial statement specifications User s Guide 5 Chapter 1 Financial Reporter Overview Important Make sure that the account segment is the first segment in the account structures you create Financial Reporter can select accounts more easily when the account segment is first For more information on designing the chart of accounts see Designing a Chart of Accounts in Chapter 1 of the General Ledger User s Guide Creating a Financial Statement Specification A financial statement specification defines the format and contents of a financial statement Financial Reporter includes several sample report specifications that you can use to print standard financial statements If you want to create special statements for your company we suggest that you customize one of these standard statements For an overview and a few short lessons on creating financial statements see the Financial Reporter Tutorial page 7 For more detailed information on creating specifications see Designing Financial Statements page 25 Printing Financial Statements
14. sample 39 48 selection criteria used in 62 tutorial on creating 7 15 reports See also financial statements printing financial statements 81 87 retrieving detail optional fields 144 retrieving net amounts and quantities 156 rounding on balance sheets 34 rows adding to a financial report 18 hiding 69 S sample report specification 39 segment description in statements 52 149 selecting accounts in statements 62 64 selection criteria expression 64 sort by account group 59 sorting 52 59 61 66 sorting order 41 spec range 73 in statements 28 73 setting 73 specification files definition 8 specifying account numbers in statements 59 61 66 standard account references 101 statement columns column A 39 column B 40 column C 40 Index column D 40 column E 42 statement footers 70 statement headers 70 statements 4 31 87 financial 87 structure 60 61 66 subtotals in statements 33 suppressing zero values 63 syntax for account selection criteria 64 T testing information in statements 37 text parameters 65 title lines in financial statements 68 totals in statements 33 transaction details consolidating by 40 transaction fields 173 transaction optional Fields printing on FR reports 38 troubleshooting Financial Reporter 175 U Use Budget Opening Balance 93 Use Quantity Opening Balance 93 V verifying account data 37 W what if analyses 92 User s Guide 185 Index Z Z in statements 63 zer
15. About Financial Statement Specifications Note The rounded total to which we want to balance is the total in G8 The total in G9 contains the cumulative effect of all rounding differences 3 Calculates the difference between the rounded totals G8 and G9 in G10 of the spreadsheet 4 Adds the difference between the totals to the last range of accounts in row 7 of the spreadsheet By adding the rounding difference to the last group of figures we have forced the figures in column G to equal the total in G8 You handle the credit side of the balance sheet using a similar approach summing all liabilities with owner equity and then deciding where to put the difference between the sums of the rounded and unrounded balances The generated statement includes rows and columns that would be hidden on the final report shown in bold Rows and Columns Hidden on Report cash 38 000 Receivables 90 000 Fixed assets Po 356 000 Total assets ee 484 000 About Verifying Account Data You can use two methods in Financial Reporter to test account information to decide whether it should appear in a report e Use a formula in column C to test the contents of a line You can specify conditions for excluding generated lines from a report based on the contents of the line For more information see About Columns in Report Specifications page 39 User s Guide 37 Chapter 3 Designing Financial Statements e Use FR functions with spr
16. Fields and Controls Buttons Close User s Guide 81 Chapter 4 Printing Financial Statements Click Close to close the screen Print Click this button to open the Print Financial Statements screen where you e Specify the printer on which to print the statement e Select the size of paper that best accommodates the statement e Specify the orientation of the statement portrait or landscape e Specify the number of copies to print Fiscal Option Year Period Specify the year and the fiscal period for the report You can print financial reports as of any past period for which you have data in General Ledger Include You can choose to include the following information when you print statements using the G L Print Financial Statements screen e Report Options When you select Report Options the first page of the financial statement displays the following information Statement name Fiscal year and period Report type actual or provisional Account processing and sorting order Account selection ranges e Drilldown Information Note The Drilldown Information option is available only after you select File or E Mail in the Print Destination area of the screen The recipient of an e mailed statement must be using the same database with General Ledger installed to drill down to underlying transactions in the report 82 Sage 300 ERP Financial Reporter G L Print Financial Statements Screen Print Destin
17. Functional equivalent of the source amount e F Functional currency total including functional equivalents of all source amounts R Reporting currency total includes reporting equivalents of all source amounts Note This option is only available if you select the Euro as your functional currency and specify a different reporting currency in Company Profile Note If no currency is supplied functional home currency is used Fiscal Set Field Names Financial Reporter can retrieve the following balance and net change fields Note In the Reporter Code Suffix n is a number that can refer to the fiscal period from 1 to 13 or to the quarter from 1 to 4 Field Description Current period Last period nth period BALnP NETnP n periods ago BALnPA NETnPA 1BALP BALnP BALYTD etc include adjustments from period 14 only if the balance is for the final period of the year BALY includes period 14 The closing entries are not included To include closing entries use BALCLOSE To print the balance from the final fiscal period without the adjustment amount use FRAMT BALP FRAMT NETADJ 2NETP NETnP NET4Q NETPQ will also include adjustments from period 14 if the net is requested for the final period of the year NET works like BAL 130 Sage 300 ERP Financial Reporter Financial Reporter Functions Quarter balance and net change Field Description Reporter Code BAL NET Suffix balance net changes aren
18. Functions Transaction Field Name TaN Tan Tasan wien rene cow icons jargument Description 000 fieldname Thename of a field from the G L account record including account and transaction optional fields Note e Account optional fields have an a prefix e Transaction optional fields have a T prefix account A string specifying a single account or a range of accounts See About reference Account Number References page 59 criteria An expression that imposes selection criteria on the accounts The account reference and expression together determine the accounts that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 formatted Indicates whether the return value should be formatted according to Optional the presentation defined in the view If omitted the default is Formatted Use one of the following constructs e FORMATTED F or FMT e UNFORMATTED U or UNFMT User s Guide 145 Appendix D FR Function Reference Examples FRPOST Retrieves unformatted transaction amounts for Account TRANSAMT 1000 that have a posting sequence number of 100 1000 POSTINGSEQ 100 U FRPOST Retrieves the unformatted transaction amount from TRANSAMT Account 1000 from January 10 2010 1000 JRNLDATE 20100110 U FRPOST Retrieves the account description from Account 1000 ACCTDESC 1000 FRPOST Retriev
19. IE ANA RU crece M ARIA ANDE M 135 FREISGAL 12 35 sci occi Aet A tesa anue Acer s fe Lis ais nee ere BE Res Cele CUN DU mug ner 142 duc 144 ERRATE T Pee 147 ERSDESG eese Shen keke oie obs e c Rte Del ee EE e Me tala acea IST ER 149 gud e LED 150 FRIERINA conos teeta cert rt eo teh eta A dedit eS Een attic dnte sia toy 156 FRIRNGR CUR 161 FRERNDR 4 Beacon ata etl As eek test eer s e eset ens DU cps ate daa ines 166 Appendix E FR Field Reference onenarena 171 Account Master Fields tertie nee onde ebore doe dhise Mi i Ea 171 Transaction Fields gt eie ee EU e D ei esce dept did ee e RUD ween abe 173 Appendix F FAQ and Troubleshooting aana 175 FOWS DOD UM 175 Troubleshooting imi RS LEE boc e pe Seen Soe se asd oe eee ir 176 Financial Reporter Error Messages 22 2 00 2200 c cece cece cece c cc eecceeeeeeeeeececeteeeeeseees 178 Error Messages Printed onFinancial Statements 22 2 200 22 200ccceeee cece eeeeeees 178 Error Messages when Starting G L Statement Designer or Running Print Financial Statements viet tout eA oli ots eee et iN ecSL RI SI A Ate fe Re eerste ts st sho 179 Error Messages when Starting Financial Reporter _ 0 0 200 2220 2 eee ceeee ee eee eee 180 TI M A 181 vi Sage 300 ERP Financial Reporter Introduction Gen
20. L Print Financial Statements screen in Sage 300 ERP make sure that Include Report Options is not selected Note For Excel based charts the default position of data labels varies by Excel version To add or remove data labels or modify their position refer Financial Forecasts Use financial forecasting to assess your current operating performance and project how your business will perform in the future Forecast xls creates a forecasted income statement and balance sheet The statements include actuals for the current and previous years and projected amounts for the next three years based on the percentage of sales revenue growth Note The forecasting method assumes that certain expenses assets and liabilities maintain a constant relationship to the level of sales To perform what if analysis enter projected percentages for revenue growth and other line items in the Adjustments area of the statement specification 92 Sage 300 ERP Financial Reporter Appendix B FR Menu Commands When you use the Statement Designer to open a Microsoft Excel worksheet an Add Ins tab appears on the Excel ribbon On this tab the FR menu displays commands you can use to create and edit financial reports and specifications FR Menu Commands FR Menu Commands LIC M FR Clear Clears the report range e You usually use FR Clear before saving from a previously a final version of a specification after generated financial developing and testin
21. Reporter Functions Argument 0 Dewrplon O account reference criteria Optional currency Optional e QA Actual quantities e QP Provisional quantities e 1 to 5 Budget set number The number of available budget sets varies by product edition 2 in Sage 300 Standard ERP 3 in Sage 300 Advanced ERP 5 in Sage 300 Premium ERP Note If no prefix is supplied the default report setting is used either actual or provisional depending on the type of report Similarly if the fiscal prefix is Q or RQ actual or provisional quantities are returned depending on the type of report The fiscal year suffix can be e YYYY A specific fiscal year such as 2010 e Ln n years agorelative to the current fiscal year for example L1 is last year and L2 is 2 years ago e Nn n years in the future relative to the current fiscal year for example N1 is next year and N2 isthe year after next Note If no suffix is supplied the current fiscal year is used Example For the field name argument ABALP 2008 ABALP is the combination of fiscal set prefix and field name referencing the actual balance for the current period and 2008 is the fiscal year suffix A string specifying a single account or a range of accounts See About Account Number References page 59 An expression that imposes selection criteria on the accounts and account optional fields The account reference and expression together determine the accounts and account opt
22. Up Amounts option The formula to be pasted into the spreadsheet appears below the Functions list 14 Sage 300 ERP Financial Reporter Lesson 3 Creating a Financial Report Specification G BM FR Paste Function Functions FRAMT field name account reference criteria currency J Field Name NETHOTD Net Quarter to Date l tt FRAMTA m pcm Fiscal Designator A v Year Account Reference Ri LN Account Number 1000 Q SHINES Account Range 1023 Qa m PAT 3 C Selection Criteria Currency Options lt account ref gt criteria Use Rolled Up Amounts Cell 4910 FRAMT ANET1QTD 1000 1023 Next Row Next Column Close Help Prev Row Prev Column Click the Paste button to paste the formula into cell G10 Click Close to close the FR Paste Function screen In the spreadsheet the net quarter to date amount appears in cell G10 You may need to press F9 to recalculate Save your work and then close Excel Lesson 3 Creating a Financial Report Specification This lesson introduces the FR Paste Function screen and demonstrates how to use it to create a financial report specification and then generate a report that compares current assets to current liabilities in the sample General Ledger Exercise 1 Adding a Comment Enter a comment to explain the purpose of the report In the Excel spreadsheet you used for the previous two lessons click File gt New
23. accounts with an account segment value between 400 and 600 and with a Department segment value from A to C ACCT indicates that the accounts are ordered by account segment User s Guide 61 Chapter 3 Designing Financial Statements e 400 600 ACCT specifies account segment values from 400 to 600 no matter what the value of other segments Again the accounts are ordered by account segment e ACCT specifies all account numbers ordered by the account segment To specify a range or account segments add a criterion in column B such as ACCTSEGVAL gt 4000 AND ACCTSEGVAL gt 6000 The last example is the most flexible because it specifies a range of account segment values no matter where the account segment lies in the account structure This is especially useful if you have more than one account structure About Selection Criteria for Filtering Accounts You use the selection criteria feature in Financial Reporter to include account figures in a report based on settings in the General Ledger account master For example you could use the selection criteria expression ACCTTYPE B AND ACTIVESW ACTIVE to select all accounts with an account type of B for balance sheet and an active switch set to currently active When you place a selection criteria expression in a formula or on a statement line Financial Reporter 1 Looks up the account identified by the account number in column A of the line
24. any previous quarter 3nQATD lets you go up to four quarters ago If you need to specify more use nQATD L1 168 Sage 300 ERP Financial Reporter Financial Reporter Functions Field Description Reporter Code Suffix NET net changes Preceding quarter 3 periods Po NETPQ Preceding quarter n periods ago PQnPA NETPQnPA Half year net change Field Description Reporter Code Suffix NET net changes Current half year to end Current half year to date STD Last half year to date nth hal year to date n half years ago nSA n half years ago to date Preceding half year 6 periods gt Preceding half year n periods ago 1pq handles any three periods providing you with revolving quarters The preceding 3 periods include the current period plus the two previous ones 2The NET for a group of periods that crosses a year end boundary will also include the adjustments in period 14 but not include the closing entry otherwise the net would include the zeroing entries in income and expense accounts 3This code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to date figures relative to the current half year If you are now in period 2 of the current half year half year TD will give you the first 2 periods of any previous half year nSATD lets you go up to 2 half years ago If you need to specify more use nSATD L1 5PS handles any six periods providing you wi
25. called the Print range Consolidating the Financial Statements of Multiple Companies in Microsoft Excel These instructions assume that the statement you are consolidating consists of a single worksheet in Excel To consolidate financial statements 1 2 In Sage 300 ERP open the largest company Open General Ledger gt Financial Reporter gt Statement Designer and then click Start Open the financial statement that you want to consolidate and save it as a new workbook Tip Save the workbook with a name that identifies it as a consolidated statement Review column C of the financial statement specification to make sure it does not suppress blank lines You use a z to suppress blank lines Turn on manual calculation in Excel a Click File gt Options b On the Formulas tab in the Calculation options group select Manual c Click OK You can recalculate values at any time by pressing the F9 key For each company that you want to consolidate copy the worksheet for the company to the workbook in which you are consolidating statements 74 Sage 300 ERP Financial Reporter 10 11 Consolidating the Financial Statements of Multiple Companies in Microsoft Excel a Click the worksheet tab to select it b On the Home tab in the Cells group click Format and then under Organize Sheets click Move or Copy Sheet The Move or Copy window appears c From the To book list select the workbook in which
26. criteria Cell F 10 FRACCT ACCTDESC 1000 1023 Next Row Next Column Paste Close Help Prev Row Prev Column Click the Paste button to paste the formula into cell F10 Click Close to close the FR Paste Function screen In the spreadsheet the word Sales appears in cell F10 Save your work User s Guide 13 Chapter 2 Financial Reporter Tutorial Exercise 2 Applying the FRAMT Function In this exercise you ll use the FR Paste Function screen to create a formula that uses the FRAMT function to calculate the net change for the second quarter of the current year so you can view sales for the quarter 1 2 Select cell F10 On the Add Ins tab of the Excel ribbon click the arrow beside FR and then click FR Paste On the FR Paste Function screen click the Next Column button The cell reference below the Functions list changes to 6 10 Specify a range of amounts to search a Onthe Functions list click FRAMT b Inthe Field Name field select NETZQTD Net Quarter to Date c Onthe Fiscal Designator list select A for Actual monetary figures d In the Account Reference group select the Account Range option e Specify a range of accounts i Inthe first field in the Account Reference group enter 1000 ii In the second field in the Account Reference group enter 1023 f If any accounts in the range are members of a rollup group and you want to view rolled up amounts select the Use Rolled
27. date figures relative to the current half year If you are now in period 2 of the current half year half year TD will give you the first 2 periods of any previous half year nSATD lets you go up to 2 half years ago If you need to specify more use nSATD L1 5PS handles any six periods providing you with revolving half years The preceding six periods includes the current period plus the five previous ones User s Guide 153 Appendix D FR Function Reference Total year net change Field Description Reporter Code Suffix NET net changes Preceding year 12 or 13 periods NETPY C Note e You can print year end adjustments and transactions to close income accounts to retained earnings with NETADJ and print closing figures with NETCLOSE and BALCLOSE e All fiscal field set values are calculated relative to the fiscal period set for the report with the exception of NETADJ and NETCLOSE and fields that reference a specific time period NETADJ is the amount posted to the adjustment period NETCLOSE is the amount posted to the closing period e If there are no records for a future period zero is returned e You can use a fiscal set prefix with NET to return specific values For example NETP provides the net amount for the current fiscal period ANETP provides the actual net change for the current fiscal period INETCLOSE provides the closing entry only 2NETADJ provides the amount from period 14 154 Sage 300 ERP
28. dollars or USD for US dollars and T is the currency type The currency type can be S Source currency E Equivalent source amount in the functional currency F Functional currency total including functional equivalents of all source amounts R Reporting currency total If the currency code is functional R includes reporting equivalents of all source amounts If the currency code is not functional R provides equivalent amounts in the reporting currency Note If no currency is supplied functional home currency is used Fiscal Set Field Names Financial Reporter can retrieve the following net change fields Note In the Reporter Code Suffix n is a number that can refer to the fiscal period from 1 to 13 or to the quarter from 1 to 4 User s Guide 167 Appendix D FR Function Reference Period net change Field Description Reporter Code Suffix NET net changes Quarter net change Field Description Reporter Code Suffix NET net changes Current quarter to end Qo NETQ Current quarter to date NETOTD eC INETP NETnP NET4Q NETPQ will also include adjustments from period 14 if the net is requested for the final period of the year NET works like BAL 2This code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to date figures relative to the current quarter If you are now in period 2 of the current quarter QTD will give you the first 2 periods of
29. eee HAE PSSA NUM AY FRACCT ACCTDESC FRAMT BALP Account Description Current Balance 1020 1400 This example demonstrates how Financial Reporter lets you define a report line that applies to a range of accounts At print time or when using FR View in the Financial Statement Designer the single line will be expanded to as many lines as there are accounts in the range In other words a report that prints the current balances of all accounts in the general ledger can be defined by a single line that specifies the range of all accounts Note e The default column specifications line begins with two backslashes e The default column specification assigns a particular formula to the backslash In our example the formulas have used only Financial Reporter functions Alternatively you could assign a value to or make use of spreadsheet functions in your formulas You can follow with or to print the inverse or the absolute value of the formula result e The default column specifications are exactly the same as the formulas in the first example except that they do not include the account reference 46 Sage 300 ERP Financial Reporter About Columns in Report Specifications When you generate the statement Financial Reporter inserts the account references for the line into the default formula e The account reference is for a range of accounts The single line in this example will be expanded to as many l
30. for the current period and 2008 is the fiscal year suffix account A string specifying a single account or a range of accounts See About reference Account Number References page 59 criteria An expression that imposes selection criteria on the accounts and Optional account optional fields The account reference and expression together determine the accounts and account optional fields that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 currency A string containing the currency reference A currency reference has Optional the form CCC T where CCC is a 3 character currency code for example CAD for Canadian dollars or USD for US dollars and T is the currency type also known as the currency code qualifier The currency type can be e S Source currency e E Functional equivalent of the source amount e F Functional currency total including functional equivalents of all 122 Sage 300 ERP Financial Reporter Financial Reporter Functions jargument 0 Deren O source amounts R Reporting currency total includes reporting equivalents of all source amounts Note This option is only available if you select the Euro as your functional currency and specify a different reporting currency in Company Profile Note If no currency is supplied functional home currency is used Fiscal Set Field Names Financial Reporter can retrieve the following balanc
31. functional currency when the Euro is the functional currency RPACCT Rounding account defined in G L Options for the reporting currency when the Euro is the functional currency Runtime parameters and print options eo OO ReportType If unformatted returns 1 for actual 2 for provisional report If formatted returns Actual or Provisional ReportAs If unformatted returns 1 for consolidated 2 for separate If formatted returns Consolidated or Separate SortBy If unformatted returns 1 for Account ID 2 for Segment ID 3 for Account Sorted Group If formatted returns AccountID SegmentID or Account 108 Sage 300 ERP Financial Reporter Financial Reporter Functions Name Contents of the field SortByAcSeg e Ifunformatted returns the number of the segment by which you are sorting e Ifformatted returns the name of the segment by which you are sorting From AcctID Starting Account ID in the print range To AcctID Ending Account ID in the print range From Starting Account Group in the print range AcctGroup To AcctGroup Ending Account Group in the print range AcSegNamen Name of the nth account segment for example Division or Department AcSegReportAs If ReportAs 2 separate this is the consolidation setting of the n nth segment FromAcSegn Starting value of the nth segment in the print range ToAcSegn Ending value of the nth segment in the print range CurAcSegValn The current
32. not include the closing entry otherwise the net would include the zeroing entries in income and expense accounts User s Guide 117 Appendix D FR Function Reference Field Description Reporter Code BAL NET Suffix balance net changes SA STD BALLSTD ae BALASTO BALASA BALnSATD s hth half year to date nSTD n half years ago nSA n half years ago to date nSATD preosdind half year 6 periods PS BALPS NETPS Preceding half year n periods PSnPA BALPSnPA NETPSnPA ago Total year balance and net change Field Description Reporter Code BAL NET Suffix balance net changes BALYTD NETYTD BALPY NETPY BALPYnPA NETPYnPA Total year Y Year to date YTD Preceding year 12 or 13 PY periods Preceding year n periods ago PYnPA Beginning of year OPEN 1This code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to date figures relative to the current half year If you are now in period 2 of the current half year half year TD will give you the first 2 periods of any previous half year 2nSATD lets you go up to 2 half years ago If you need to specify more use nSATD L1 3PS handles any six periods providing you with revolving half years The preceding six periods includes the current period plus the five previous ones 118 Sage 300 ERP Financial Reporter Financial Reporter Functions Field Description Reporter Code BA
33. reference specifies a range using a colon this reference will be processed in account number order unless you set an order for the reference using the character no matter what order is set in the Print dialog box or in the default row specification As you can see from these examples the way you reference accounts can have a substantial impact on performance For information on specifying account references see About Account Number References page 59 About Consolidating Account Ranges You can perform two extremely useful operations on a group of accounts You can e Consolidate all the lines of an account range instead of printing out the details e Print subtotals for groups of accounts according to changes in particular account segments Grouped Separate or Summarized Data You can generate separate lines for each account in an account range consolidating the account reference in a single line consolidating account data User s Guide 55 Chapter 3 Designing Financial Statements by account group or you can consolidate the account data by account segment code You specify this choice in column D of a report specification In addition you can use the column D setting to override the account order processing ona line by line basis Column D Valid Values Vehe Demnpion Do Foret Pintsone tne freak account number ui nal well as accounts not in rollup groups D ACCT Consolidates by the account
34. replaced by audit information in the report range when you use the FR View command Financial Reporter prints a smaller area of the worksheet than the Report range because it excludes columns A to D e The Print range is set by the Spec range About Generating Reports from Specifications If you print a financial statement using the G L Print Financial Statements screen you do not see the report specification Instead Financial Reporter opens the financial specification in Microsoft Excel processes the specification commands in the Spec range of the spreadsheet and prints the report In contrast if the spreadsheet contains no Spec range Financial Reporter recalculates the spreadsheet for the ranges of accounts account groups or segments specified on the G L Print Financial Statements screen and then prints the whole spreadsheet or the print area defined in the spreadsheet 28 Sage 300 ERP Financial Reporter About Financial Statement Specifications Depending on the print destination you specify on the G L Print Financial Statements screen you can e Preview the report before you print it e Send the report via e mail to a recipient e Print directly to a printer or print to a file Generating Statements Using the Financial Statement Designer When you edit a report specification using the Statement Designer you can also test the statement specification you are editing To generate the final report use FR View Fo
35. respect the account type Debit balances in debit accounts and credit balances in credit accounts are returned as positive numbers credit balances in debit accounts and debit balances in credit accounts are returned as negative numbers Although FRAMT is similar to FRAMTA page 121 FRAMTA returns all debit balances as positive numbers and all credit balances as negative numbers regardless if the account is a debit or credit type FRAMT is also similar to FRTRN page 150 but FRAMT retrieves totals from account history whereas FRTRN retrieves totals from transaction history Syntax FRAMT field name account reference criteria currency Note The function returns 0 if the account reference is missing the account does not exist or if no accounts exist in the specified range Arguments Argument Desripion field name The name of a field from the General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal year suffix separated from the field name with a period The fiscal designator prefix can be e A Actual amount e R Rollup amounts R must be the first character and can be used with other prefixes Tip In Excel if you use the FR PASTE screen to add the FRAMT function to a statement select the Use Rolled Up Amounts check box P Provisional amounts Q Quantities either actual or provisional depending on the report type 114 Sage 300 ERP Financial Reporter Financial
36. round account totals You always have to force a balance by adding a rounding difference to one of your accounts or account sections The next two spreadsheet examples show two ways to ensure that your balance sheets will balance e Example 1 shows a simple mechanism that rounds all balances on the statement and then forces the totals of rounded assets to balance rounded liabilities e Example 2 produces a more theoretically correct balance by summing all unrounded account balances and then forcing the statement to balance to this sum Example 2 makes use of a hidden column of unrounded account data and so will require a larger spreadsheet and more memory to run Example 1 Forcing a Rounded Balance Sheet to Balance This sample statement rounds and sums all printed figures on each side of the balance sheet and then forces liabilities and owner equity to balance with the 34 Sage 300 ERP Financial Reporter About Financial Statement Specifications assets Any rounding differences are lumped with owner equity The specification for the statement includes a few rows that must be hidden before generating the final report row 11 and row 14 A a F WACCT D FRACCT ACCTDESC ROUND FRAMT BALP 3 Description Current Balance Assets 1000 1199 Total assets SUM F5 Liabilities 3000 3899 Total liabilities SUM F9 3900 3999 T A Equity SUM F11 F14 Total liabilities amp equity F6 F6 F10 F11 This speci
37. segment code setting the account number sorting order to account segment order for this single account reference D Consolidates by the account group ACCTGROUP D Consolidates by account segment number n ACSEGVALn D AcctSegID Consolidates by the named account segment for example D REGION LNNNCT ILE DT In each case where consolidation is performed on the basis of a property of the account number Financial Reporter processes the account number in the order required for consolidation For example if consolidation is by account group the account numbers are processed in account group order 56 Sage 300 ERP Financial Reporter Using Formulas to Retrieve General Ledger Account Information Transactions can also be listed by consolidated account segments and account groups when listing by accounts If you are consolidating by a specific account order in a list of transactions the value in Column D must begin with a P For example e P ACSEGVALO2 returns transaction records consolidated by account segment 2 e D ACSEGVALO2 returns account history records consolidated by account segment 2 FR requires a correct sorting order in Column D in order to list details by transaction through field consolidation as follows Sorting Order for Consolidating Transactions POSTTRAN By posted transaction in account order POSTDATE By date order POSTSEQ By posting sequence order POSTACCT By account posting sequence
38. specification Before you can generate a report from a report specification you must define the Spec range for your spreadsheet To define the Spec range in a Financial Reporter spreadsheet 1 With the statement spreadsheet open in Excel select the complete report specification from the first row and column to the last row and column 2 On the Excel Ribbon click Formulas gt Define Name 3 Type Spec as the range name then click OK Important You must type exactly these letters although the capitalization doesn t matter Note You do not define a Report range for the spreadsheet Financial Reporter defines the Report range when it generates the report from a specification About the Report Range and the Print Range The Report range is generated from the Spec range when you print the financial report It contains data that is generated by the report as well as data extracted from the General Ledger The template portion of the specification expands in the final report to display the actual general ledger data User s Guide 73 Chapter 3 Designing Financial Statements The control information in the first four columns of the specification is replaced by audit information in the report range the numbers of the accounts from which the information was taken When you print the report Financial Reporter prints all but the first four columns of the Report range omitting the audit information The area it prints is
39. spreadsheet files that contain the functions required to read general ledger data You can print using one of the sample statements that come with General Ledger or you can use a custom report specification that you create using the G L Statement Designer For more information about the sample statements that are included with General Ledger see Sample Financial Statements page 87 For instructions on printing financial statements see G L Print Financial Statements Screen page 81 G L Statement Designer The G L Statement Designer is an add in program that works with Microsoft Excel You use it to create customized statement specifications and you can use it to generate the statements When you open the Statement Designer from the Financial Reporter folder and then click Start Excel opens with the Financial Reporter functions automatically loaded These functions let you retrieve and work with data from your Sage 300 ERP company database Note The spreadsheet add in is available only when you start Excel from the Statement Designer screen inSage 300 ERP Microsoft Excel is not included with Sage 300 ERP General Ledger If you want to use Excel to create financial statements you must purchase it User s Guide 3 Chapter 1 Financial Reporter Overview separately You can install Excel before or after installing General Ledger For more information see G L Statement Designer page 30 After you create a report
40. the View tab in the Window group click New Window b On the View tab in the Window group click Arrange All and then select Vertical c In each window select a different tab and scroll to align the contents of each window so that the first description in each generated report aligns with the top cell in the window In the worksheet that contains the most accounts select and copy the print area In the Consol worksheet right click cell A1 and then select Paste Special Values Without deleting any blank lines use Excel to format the statement as needed ledger but not in others Clear all amounts from the report Sum the accounts for like accounts across all worksheets so that totals for all companies appear in place of the amounts you erased in the previous step 76 Sage 300 ERP Financial Reporter Chapter 4 Printing Financial Statements Important Printing using the Statement Designer is not the same as printing using the Print Financial Statements screen When you print using the Statement Designer e You can generate an audit information tab as well as account audit information in the first column of the report The audit information tab lists information such as the print ranges and any specified processing order e You can create formulas for each value that appears in the report range instead of the values themselves This means that you can update the report simply by recalculating the spr
41. the consolidation of transactions with account segment 2 retrieving records from a transaction while D ACSEGVALO2 is the consolidation of account history with account segment 2 retrieving records from an account To list the details by transaction the spec must provide a correct sorting order as illustrated in the following table Sorting Order Exclusive Sorting Order POSTTRAN By Posted Transaction in account order POSTDATE By Date Order POSTSEQ By Posting Sequence Order POSTACCT By Account Posting Sequence Note e If the account reference is specified with exclusive sorting order at column A from a spec line and the order is an account order for example 1000 9000 ACCT Financial Reporter does not generate the transaction details of the spec line e If the consolidation value in column D is P POSTTRAN the sorting order must be POSTTRAN User s Guide 41 Chapter 3 Designing Financial Statements e If the consolidation value in column D is P POSTDATE the sorting order must be POSTDATE e If the consolidation value in column D is P POSTSEQ the sorting order must be POSTSEQ e If the consolidating total is retrieved by any defined account switches like P ACSEGVALO2 it can be sorted by any defined account order similar to listing by accounts e Listing by transaction is only limited to the inquiry of transaction records from the inquired period e Use T to generate a single
42. total line for an account range If you leave column D blank or specify a p Financial Reporter lists the details for each account in the range Column E 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 part of the final report The following example shows a report specification displayed in formula view A Belcio E F G H l 1 Specification UNIV 2 This specification will produce an expense 3 schedule for the current fiscal period 4 quarter and year to date E 7 5 D FR CON 6 Schedule a Forthe 8 g 2T FRAMTA EFRAMTA FFRAMTA NETP NETO NETYTD 10 Current Quarter Year 11 Period ToDete ToDate 12 6000 6999 D FRACCT ACCTDESC b 13 14 SUM G12 SUM H12 5UM 112 i In this example the first three lines of column F that appear on the report are e FR CONAME a Financial Reporter function that displays the company name on the statement 42 Sage 300 ERP Financial Reporter About Columns in Report Specifications e The text Schedule of Expenses e The text and function For the period ending amp FR END The function inserts the period end date from the database Row 9 of the specification sets the default contents of statement columns Column A of this row starts with two bac
43. tutorial 7 Financial Reporter functions See FR Financial Reporter menu commands 93 FR Clear 93 FR Drilldown 93 FR Help 93 FR Options 93 FR Paste 94 98 FR View 94 Financial Reporter screens Print Financial Statements 81 Statement Designer 30 Financial Reporter spreadsheet Excel 7 financial statements accounts in 52 59 columns in 39 consolidating 55 74 formatting 68 70 how to produce 7 28 launching from Sage 300 ERP 7 printing 79 81 selection criteria for 62 66 Specrange 73 specifications for 27 Statement Designer 30 title lines 68 Fiscal Designator 97 fiscal period dates in statements 142 fiscal set field names in statements 114 footers in statements 70 forecasting 92 formatting financial statements grid lines 69 page layout 70 Index formulas in statements about using 59 overview 8 12 FR Clear menu command 93 FR Drilldown menu command 93 94 FR error messages 178 FR functions 51 59 61 66 105 criteria 64 FR 107 FRACCT 110 FRAMTA 121 compared with FRAMT 114 FRCREDIT 128 FRDEBIT 135 FRFISCAL 142 FRPOST 144 FRRATE 147 FRSDESC 149 FRTRN 150 FRTRNA 156 FRTRNCR 161 FRTRNDR 166 overview 105 text parameters 65 FR Help menu command 93 FR Options menu command 93 FR Paste menu command 94 96 97 rolled up amounts 96 using 98 FR View menu command 31 82 84 85 94 Frequently Asked Questions 175 functions in FR statements See FR Functions User s Guide 183 Index G G L Print Financial Statemen
44. value of the segment for the current report This value is only appropriate when ReportAs 2 separate Acctgroupsort Sorting by Account Sorted Group Acctgroup Sorting by Account Group Examples FR Coname The Garden Inc User s Guide 109 Appendix D FR Function Reference FRACCT This function retrieves data from G L account records including account optional fields It cannot retrieve data from transaction optional fields Syntax FRACCT field name account reference criteria formatted Note The function returns a blank if the account reference is missing the account does not exist or if no accounts exist in the specified range Arguments Argument Desipon field name The name of a field from the G L account record including account optional fields Tip Account optional fields have an A prefix account A string specifying a single account or a range of accounts See About reference Account Number References page 59 Note e Forarange of accounts the function returns only the data that is common to all accounts e Ifthe account does not exist FRACCT returns a hyphen criteria An expression that imposes selection criteria on the accounts The account reference and expression together determine the accounts that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 formatted Indicates whether the return value should be formatted accordi
45. will produce odd results and you should order accounts by a particular segment or by account group If you choose to print by the account segment the order for processing is first by that account segment and second by the complete account number as defined by its structure If you print by the account group code the order is first by account group second by account segment code and third by account number The default sort order remains in effect until you specify another sort order or unless it is overridden in a specific account reference see the next section 54 Sage 300 ERP Financial Reporter Using Formulas to Retrieve General Ledger Account Information Specifying Account Order for One Account Reference You can choose to process a single account reference in a different order than indicated by a default row specification or by the report options on the Financial Statements screen You specify a processing order for an account reference by placing it immediately after the account reference Account reference Order Example e 1000 ACCT uses account order so processes only account 1000 Segment 2 will appear in any order e 1000 ACSEGVAL2 uses segment 2 order so will scan all account numbers However segment 2 will be in order e 1000 3000 REGION ACCTID uses account number order for accounts that use the REGION account structure Will only process particular accounts in the range Note If the account
46. 13 760300 FRRATE USD ATS AV DATEVALUE FRFISCAL Start RF 88274 Note This function returns the floating exchange component between the Austrian schilling and the US dollar In this case this is the euro to US dollar exchange rate 148 Sage 300 ERP Financial Reporter Financial Reporter Functions FRSDESC This function returns the account segment name from the General Ledger segment codes table given a segment number and code Syntax FRSDESC segment number segment code Arguments Argument Description segment number G L segment number from 1 to 10 segment code G L segment code Examples In the following examples segment 1 is the department segment The third example shows how to use the FR function to return the segment code for the current report FRSDESC 1 USEAST Eastern United States FRSDESC 1 SALES Sales Department FRSDESC 1 FR CurAcSegVali Sales Department User s Guide 149 Appendix D FR Function Reference FRTRN This function retrieves net amounts and net quantities from posted transactions for a specified period It retrieves actual values and summary totals by both account and transaction fields and allows drill down to associated transactions FRTRN is similar to FRAMT page 114 but FRTRN retrieves totals from transaction history whereas FRAMT retrieves totals from account history Note This function cannot retrieve budget or provisional amounts calculate
47. 39 Total 13 085 07 9 956 29 If this was a spreadsheet you would place a formula such as SUM B3 B5 in each of the total cells This formula would add together the values in cells B3 B4 and B5 and display the total in B6 Bank account US dollars 8 437 54 5 932 90 Totaling a column of figures with Financial Reporter is just as easy All you do is refer to the cells or range of cells in the specification and Financial Reporter will translate the cell references in the final report You may refer to a single cell a list of cells or a cell range User s Guide 33 Chapter 3 Designing Financial Statements In the following example the formula SuM F3 will be translated when you generate the report to the appropriate range of cells in the Report range to add the balances of all accounts in the range 1100 to 1300 I FRACCT ACCTDESC FRAMT BALP Current Balance 1100 1300 Total SUM F3 Note Excel does not allow you to specify a negative range of cells as a parameter of SUM For example the following SUM formula will not work in Excel SUM G10 G14 You can fix the formula by moving the minus sign like this SUM G10 G14 In summary to perform any mathematical operations within the financial report use standard spreadsheet formulas and functions About Rounding on Balance Sheets If you have spent much time working with financial statements you know that balance sheets do not balance when you
48. 4000 4999 C 4000 4999 D In this example you would print reports specifying a segment range of A of B and so on Turning off Report level Account Range Filtering A prefix turns off report level account range filtering for the particular account number reference If you use a prefix any account numbers that match the account reference you specify and match the selection criteria in column B will appear on the report regardless of the restrictions imposed by Financial Reporter print settings You can also use the prefix with the wildcard characters to include any account in the ledger allowed by the criteria in column B for the row is an account number reference that includes all accounts no matter what their account structure Specifying Ranges of Accounts when the Account Segment is not First in the Account Structure Account references work best when the account segment is the first segment of your account numbers Not only is it easier to specify account ranges but Financial Reporter processes them more quickly as well If you do not have the account segment first you can still specify a range of account segment codes and print the accounts in account segment order For example e 4000 8000 specifies all accounts with an account segment code from 4000 to 8000 no matter how many segments are in the number and no matter where the account segment is in the account structure e A C 400 600 ACCT specifies all
49. ACCTGRPCOD 02 OR 19 Total current assets 0 00 0 00 21 Fixed assets 22 ACCTGRPCOD 05 23 ACCTGRPCOD 06 T Less accumulated depreciation Y 24 0 00 0 00 26 Other assets 2T ACCTGRPCOD 0 28 0 00 0 00 29 0 00 0 00 31 LIABILITY AND SHAREHOLDER S EQUITY 33 Current liabilities 34 1 ACCTGRPCOD 08 OR ACCTGRPCOD 09 OR Y Y 35 Total current liabilities 0 00 0 00 The first four columns of the Spec range A through D are reserved for control information for Financial Reporter The other columns form a template that determines the appearance of the report and the type of data that it retrieves 72 Sage 300 ERP Financial Reporter About Formatting Financial Statements Note A specification can begin in any column of a worksheet but the first column of the Spec range must be the first column of the specification We always refer to the first column of the specification as column A There is no reason why you should start a specification anywhere else Redefining the Spec Range If you want to redefine the spec range in your worksheet after generating a report use the FR Clear command to clear up the report area and then make your changes to the spec range Setting the Spec Range The Spec range is a named range of spreadsheet cells that begins with the first row and column of the report specification and includes all cells that are part of the
50. After creating specifications use the Print Financial Statements screen to select the specification you want to print To print a statement you select the report you want to print select from a variety of print options and then click Print Excel gets the data prints it and returns you to the Company Desktop For more information about printing financial statements see Printing Financial Statements page 77 6 Sage 300 ERP Financial Reporter Chapter 2 Financial Reporter Tutorial In this tutorial you will learn 1 How to use the Financial Statement Designer to construct financial statements 2 How specification files generate financial statements 3 How to use tools such as FR Paste and FR View Before you create new statements or modify existing specification files such as those included with General Ledger read and perform the exercises in this tutorial to familiarize yourself with the product Note e The lessons in this tutorial use sample data included with System Manager For information about how to install and refresh sample data see the System Manager help and documentation e The lessons in this tutorial assume that you are using Microsoft Excel 2007 or later About the Statement Designer You use the Financial Statement Designer to create edit and test financial statements and financial statement specifications It provides add in programs that you use in Microsoft Excel to retrieve and manipula
51. Code Suffix Current quarter to end Current quarter to date Last quarter Last quarter to date nth quarter nth quarter to date n quarters ago n quarters ago to date Preceding quarter 3 periods Preceding quarter n periods PQnPA ago BAL balance net changes BALQTD BALLQ BALLQTD BALnQATD BALPQnPA NETPQnPA BALnQ BALnQTD Half year balance and net change Field Description Suffix Reporter Code BAL NET balance net changes 1This code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to date figures relative to the current quarter If you are now in period 2 of the current quarter QTD will give you the first 2 periods of any previous quarter 2nQATD lets you go up to four quarters ago If you need to specify more use nQATD L1 3pQ handles any three periods providing you with revolving quarters The preceding 3 periods include the current period plus the two previous ones The NET for a group of periods that crosses a year end boundary will also include the adjustments in period 14 but not include the closing entry otherwise the net would include the zeroing entries in income and expense accounts 138 Sage 300 ERP Financial Reporter Financial Reporter Functions Field Description Reporter Code NET Suffix balance net changes Current half year to date BALSTD NETSTD Last half year CE BALLS NETLS Last hal
52. Example 22 00 00 cece cece cece cece cc ccccccceceeecececceecetcceceeetttececeeeetnes 95 Formulas that Support FR Drilldown oaaao aaa ooo cece cece oaaao oaa PLLA LLA LLa Laana 95 About FR Paste a EE Dc advent coved Si tek ake eris 96 Pasting Single Values and Default Column Specifications sse 97 Pasting Column Formulas 2 200 200 2 cece cece cece ccc ec cece cece cece eeeeeeeeeeseeeseeeeees 97 About the Fiscal Designator _ 0 2 2 0 2 22 coon cence cece cece eect eee eeeeeeceeeeeeeeeees 97 Using FR Paste to Insert Information in Financial Reports 00 000 2 20 222 e eee eee 98 Appendix C Account Reference Syntax and Examples 101 Standard Account References ssssssssssssssssssssssssesses sess ele s le ee lernen 101 User s Guide v Contents Appendix D FR Function Reference onnon 105 Financial Reporter Function Notation 2 0 0 0 0 cece ccc cece ccc ccccccccceececcccccceeeecccceeeeeees 105 Financial Reporter Functions sssssussssssssssssss cccecececceeeetcccecccettcccceceeetseseees 105 EROS Fete ead Re eatery A ial ca DIET CIN ITE Une 107 FRAGT p I c c 110 FRAMES i uem e EA Umi t ANR 114 ERAMTArS Mea ERLLDISORM ee D RR casa RALIS S Ad LE LI IR RT eR RADI L DIIS ORA ALI LA nM EL DNE 121 FREREDEIT eimi tures tara sem etti eoe t uet D A EA ARE UA IS Se nt EUM Da no ebd 128 ERDEBEEA2 3 cro saeua Mu rc amt NY Scr de Sar c eee
53. FR View command to generate a report before you can use FR DrillDown FR DrillDown Example The sample spreadsheet quickinc4 xls uses the transaction function FRTRNA to demonstrate drilldown In the sample spreadsheet the formula FRTRNA NETYTD produces a statement of earnings with one column of figures showing current year to date results The comment displayed explains how to drill down to the transaction To see how FR Drilldown works perform the following steps in the sample spreadsheet quickinc4 xls 1 Onthe Excel ribbon on the Add Ins tab click FR FR View 2 Click OK to run FR View The report is generated Results cells where FR DrillDown is available appear with a red mark in the top right corner of the cell 3 Right click a report cell and then then click FR DrillDown The original transaction details appear in a new window Formulas that Support FR Drilldown FR DrillDown works only with formulas that contain the following transaction functions e FRTRN e FRTRNA e FRTRNCR e FRTRNDR FR DrillDown is not available for formulas that contain edited versions of the following transaction functions e FRTRN e FRTRNA User s Guide 95 Appendix B FR Menu Commands e FRTRNCR e FRTRNDR The following are examples of edited commands e FRTRN NETP 100 e ROUND FRTRN NETP 0 For information about other menu commands in the Financial Statement Designer see FR Menu Command
54. Financial Reporter Financial Reporter Functions Examples FRTRN NETLQ 1000 Net transactions for the last quarter for account 1000 FRTRN Net transactions for the quarter to date for account NETQTD 1000 1000 FRTRN NET P 1000 Net transactions for inquired period for account 1000 User s Guide 155 Appendix D FR Function Reference FRTRNA This function retrieves net amounts and net quantities from posted transactions for the period that you specify It retrieves actual values and summary totals by both account and transaction fields and allows drill down to associated transactions Like FRAMTA page 121 it displays debit amounts as positive numbers and credit amounts as negative numbers Note FRTRNA does not calculate balances and does not include provisionally posted transactions Syntax FRTRNA field name account reference criteria currency Arguments Co field name The name of a field from the General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal year suffix separated from the field name with a period The fiscal designator prefix can be e A Actual amount e Q Quantities e QA Actual quantities Note If no prefix is supplied the default report setting is used actual The fiscal year suffix can be YYYY A specific fiscal year such as 2010 Ln n years ago relative to the current fiscal year for example L1 is last year an
55. Financial Statement Designer see FR Menu Commands page 93 Pasting Single Values and Default Column Specifications The following are typical single values you will paste in a financial report e Company name e Period end date or today s date e Division or department for which the report is printed e Asingle account balance For each of the above items you will paste a complete formula and you will probably not have to edit the result Pasting Column Formulas If you are pasting a formula to use as a default column specification do not enter an account reference because you will be supplying that in column A of each line that uses the default column specification For example the formula for a single cell might be FRAMT BALP 1000 whereas a default column formula would be FRAMT BALP For instructions on using FR Paste see Using FR Paste to Insert Information in Financial Reports page 98 About the Fiscal Designator When you use the Statement Designer to open a Microsoft Excel worksheet an Add Ins tab appears on the Excel ribbon On this tab the FR menu displays commands you can use to create and edit financial reports and specifications User s Guide 97 Appendix B FR Menu Commands The FR Paste command appears on this menu On the FR Paste Function screen the Fiscal Designator is a drop down list that appears for the FRAMT FRAMTA FRDEBIT FRCREDIT FRTRN FRTRNA FRTRNCR and FRTRNDR functions T
56. I11 197 12 Current Year of Total Previous Year of Total LET as E o 14 SUM G13 SUM I13 Note Financial Reporter recalculates all formula cell references for the generated report User s Guide 49 Chapter 3 Designing Financial Statements Example 5 Current Period and Year to Date Expenses Compared to Last Year This example produces a comparative expense schedule with six columns of data e Column G lists the current period expense e Column H lists the expense for the corresponding period in the previous year e Columns J and K list the year to date expenses e Columns I and L compare each amount as a percentage of the amount in the previous year G aac FE sad Ep Ese e apre eere nmn eterna ies opcs 18 FRAMTA NETP FRAMTA NETP LI G18 H18 FRAMTA NETYTD FRAMTA NETYTD LI J18 K18 19 Current Same Month of 20 Month Last Year Last Year LX of Last Year Note The range of expense accounts is defined in row 21 of the report specification but the formulas in I18 and L18 reference the default formula cells that are used in row 21 Financial Reporter recalculates these cell references for the generated report Example 6 Side by Side Departmental Statements This example produces a side by side departmental comparative balance sheet with three columns of data e The processing order is set in column A to Account order e Column D indicates that each t
57. L NET Suffix balance net changes End of year CLOSE BALCLOSE NETCLOSE Note e You can print year end adjustments and transactions to close income accounts to retained earnings with NETADJ and print closing figures with NETCLOSE and BALCLOSE e All fiscal field set values are calculated relative to the fiscal period set for the report with the exception of BALOPEN NETADJ and NETCLOSE and fields that reference a specific time period BALOPEN is the opening balance of the account for the year NETADJ is the amount posted to the adjustment period NETCLOSE is the amount posted to the closing period e If there are no records for a future period zero is returned e You can use a fiscal set prefix with NET and BAL to return specific values For example BALP provides the actual balance or provisional balance for supported functions for the current fiscal period ABALP provides the actual balance for the current fiscal period 1BALP provides the balance from budget set 1 for the current fiscal period for functions that can reference budget sets 1BALCLOSE acts like BALP in every period except the last period of the year when it includes the adjustments and the closing entry NETCLOSE provides the closing entry only 2NETADJ provides the amount from period 14 To print the balance from the final fiscal period without the adjustment amount use FRAMT BALP FRAMT NETADJ User s Guide 119 Appendix D FR Fun
58. LP Balance SUM F5 ACCTGRPCOD 5 r F6 F9 Financial Reporter processes all columns and rows of a spreadsheet within a range you specify called the Spec range Before you can print a financial report you must define the Spec range for the report To define the Spec range 1 Select cells A1 through cell F12 The entire area of the specification you created is selected 2 On the Formulas tab in the Defined Names group click Define Name The New Name screen appears 3 Inthe Name field type Spec P New Name 4 Click OK 5 Save the report You have now created a simple statement specification and are ready to generate the report 20 Sage 300 ERP Financial Reporter Lesson 3 Creating a Financial Report Specification Exercise 6 Generating the Statement Use the FR View command to generate a financial statement from the specification you created You can use FR View to generate two kinds of final reports one with values and one with formulas e Values If you generate values Financial Reporter uses the formulas in the specification to extract the appropriate data from the General Ledger and inserts the data in the cells on the final report e Formulas If you generate formulas by selecting the Formulas check box Financial Reporter creates all the rows required by the report but places a formula in the spreadsheet for each piece of data required from the General Ledger
59. Preceding year n periods ago year n periods ago PYnPA BALPYnPA NETPYnPA PA Beginning of year OPEN BALOPEN BENEEY 1This code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to date figures relative to the current half year If you are now in period 2 of the current half year half year TD will give you the first 2 periods of any previous half year nSATD lets you go up to 2 half years ago If you need to specify more use nSATD L1 3PS handles any six periods providing you with revolving half years The preceding six periods includes the current period plus the five previous ones User s Guide 125 Appendix D FR Function Reference Field Description Reporter Code BAL NET Suffix balance net changes End of year CLOSE BALCLOSE NETCLOSE period 14 adjustments ADD S CS S Note e You can print year end adjustments and transactions to close income accounts to retained earnings with NETADJ and print closing figures with NETCLOSE and BALCLOSE e All fiscal field set values are calculated relative to the fiscal period set for the report with the exception of BALOPEN NETADJ and NETCLOSE and fields that reference a specific time period BALOPEN is the opening balance of the account for the year NETADJ is the amount posted to the adjustment period NETCLOSE is the amount posted to the closing period e If there are no records for a futur
60. Sage 300 ERP Sage 300 ERP 2014 Financial Reporter User s Guide This is a publication of Sage Software Inc Copyright 2013 Sage Software Inc All rights reserved Sage the Sage logos and the Sage product and service names mentioned herein are registered trademarks or trademarks of Sage Software Inc or its affiliated entities All other trademarks are the property of their respective owners Microsoft SQL Server Windows Vista and the Windows logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and or in other countries The names of all other products and services are property of their respective owners Your use of this document and the Sage product s described herein is governed by the terms and conditions of the Sage End User License Agreement EULA or other agreement that is provided with or included in the Sage product Nothing in this document supplements modifies or amends those terms and conditions Except as expressly stated in those terms and conditions the information in this document is provided by Sage AS IS and Sage disclaims all express implied or statutory warranties of any kind including but not limited to the warranties of merchantability fitness for a particular purpose or of non infringement No implied license is granted to you under any Sage intellectual property or trade secret right Sage reserves the right to revise supplement or remove informa
61. User s Guide 47 Chapter 3 Designing Financial Statements Financial Reporter looks only at the numbers on a line to decide if zero suppression should take place In this example the description in column E does not affect whether the line is printed If you do not want to suppress zeroes on a single line enter the N in column C on the line that you want to print For details on specifying selection criteria and suppressing lines see About Consolidating Account Ranges page 55 About Excluding Lines from Reports page 63 and About Selection Criteria for Filtering Accounts page 62 For more information about Financial Reporter functions see Financial Reporter Functions page 105 Sample Default Column Specifications The following examples illustrate standard financial report formats and the column specifications you use to produce these reports Example 1 Balance This Period and Last Period This example produces a comparative balance sheet e Column E lists the current balance e ColumnF lists the balance in the previous period Se aed E F md V FRAMTA BALP FRAMTA BALLP 2 Current Balance Balance Last Month gx 1100 1300 4 Example 2 This Year Last Year Balances with Percentage of Total Columns This example produces a comparative balance sheet with four columns of data e Column G lists the current balances e Column I lists the balances for the corresponding period in the previous y
62. a financial report and it affects the report processing efficiency For more information see About Account Order in Financial Statements page 52 Select Accounts Fields and Table Use the From and To fields to restrict the accounts included on a financial statement to e Arange of account groups unless the report is ordered by account group e Ranges of account segment codes such as a range of departments and or range of regions You can also print separate reports by account number segments For example to print separate reports for each department in each region of your company you select Sort by Region and then when selecting accounts in the table select Report as Separate by double clicking Consolidated Print for Financial Statements Screen To open this screen 1 2 3 4 Open General Ledger gt Financial Reporter gt Print Financial Statements Specify the statement name Select printing options Click Print Related tasks Printing a Financial Statement page 79 User s Guide 85 Chapter 4 Printing Financial Statements Overview Use the G L Print for Financial Statements screen when printing statements in Financial Reporter to e Specify the printer on which to print the statement e Select the size of paper that best accommodates the statement e Specify the orientation of the statement portrait or landscape e Specify the number of copies to print 86 Sage 300 ERP Finan
63. add or remove features and then confirm that Visual Basic for Applications is installed as part of Office Shared Features Failure to start Statement Designer Failure to start Print Financial Statements Write down the message exit applications and Windows restart your computer and then try the task again Most error messages pertaining to incorrect procedures arise when other tasks have locked the system or company database Wait for the other task to complete and then try again in statements 59 in statements 59 in statements 59 X in statements 45 in statements 45 48 52 _ in statements 59 in statements 52 59 rJ in statements 52 59 A A column in statements 39 69 account consolidation in statements 55 account data verifying 37 Index account master fields 171 account numbers in formulas 59 61 account optional fields printing on FR reports 38 account ordering in statements 52 59 61 66 account processing in statements 52 59 61 66 account ranges in statements 52 59 66 overriding 30 account references examples 101 symbols 101 syntax 101 account segment description in statements 149 account selection in statements 62 64 account sorted group 59 account structures 60 61 66 ACSEGVALn 52 adding rows to a financial report 18 amounts in statements 135 B column in statements 40 balance sheets about rounding on 34 balances in
64. ae fo foment ecsinganerO nen o feara m Preceding quarter n periods PQn PA BALPQnPA NETPQnPA ago Half year balance and net change Field Description Reporter Code BAL NET Suffix balance net changes 1This code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to date figures relative to the current quarter If you are now in period 2 of the current quarter QTD will give you the first 2 periods of any previous quarter 2nQATD lets you go up to four quarters ago If you need to specify more use nQATD L1 3pQ handles any three periods providing you with revolving quarters The preceding 3 periods include the current period plus the two previous ones The NET for a group of periods that crosses a year end boundary will also include the adjustments in period 14 but not include the closing entry otherwise the net would include the zeroing entries in income and expense accounts User s Guide 131 Appendix D FR Function Reference Field Description Reporter Code BAL NET Suffix balance net changes SA STD BALLSTD ae BALASTO BALASA BALnSATD s hth half year to date nSTD n half years ago nSA n half years ago to date nSATD preosdind half year 6 periods PS BALPS NETPS Preceding half year n periods PSnPA BALPSnPA NETPSnPA ago Total year balance and net change Field Description Reporter Code BAL NET Suffix balance net chan
65. age of total liabilities e Common size balance sheets that also include graphical summaries statements for specified periods comparisons of current and previous year amounts and comparisons of current year and budget amounts User s Guide 87 Appendix A Sample Financial Statements Balance Sheet Statements General ledger includes the following sample statements e QuikBali xls Standard Balance Sheet single column e QuikBal2 xls Comparative Balance Sheet Current Year Previous Year e QuikBal3 xls Comparative Balance Sheet Current Period Previous Period Balance Sheet Summaries General ledger includes the following sample statements e BalsumO01 xls Common Size Balance Sheet Summary A summary as amounts and as a percentage of total assets and liabilities printed on separate pages e Balsum02 xls Balance Sheet Summary with Graphs A summary as amounts and a graph as a percentage of total assets and liabilities Asset summaries and liability summaries are printed on separate pages Important Financial Reporter does not generate this report Rather the report uses data from fixed cells in the Excel spreadsheet and may not print the correct information if the report is generated with the Include Report Options setting If you are printing the report from the G L Print Financial Statements screen in Sage 300 ERP make sure that Include Report Options is not selected Note For Excel based charts the default p
66. alance to the Rounded Total of Unrounded Account Balances The following statement example shows another way to set up a rounding mechanism in balance sheets to handle rounding differences This method has two main advantages over the previous example 1 It more accurately reflects an unrounded balance sheet because the final balance is derived by summing unrounded balances instead of by summing rounded ones 2 Itis self auditing In example 1 you are forcing assets to balance liabilities and equity so even if you leave accounts off the statement the report will balance In example 2 the rounded statement will not balance if you leave out an account Before generating the final report you must hide column F as well as rows 6 9 and 10 See the final report example below For information about hiding columns and rows see About Formatting Financial Statements page 68 A Eile E E G WACCT T FRAMT BALP ROUND F1 3 Description Current Balance 1000 1199 Cash A 1200 1299 Receivables 1300 1350 A A Fixed assets SUM G6 G10 Total assets SUM F4 F5 F6 SUM G4_ G5 G6 G8 G9 This specification performs the following actions 1 Inserts the unrounded balances of all accounts in column F of the spreadsheet and inserts the rounded balances of all accounts in column G 2 Calculates a rounded sum of column F in G8 and column G in G9 of the spreadsheet 36 Sage 300 ERP Financial Reporter
67. ame with a period The fiscal designator prefix can be e A Actual amount e Q Quantities e QA Actual quantities Note If no prefix is supplied the default report setting is used actual The fiscal year suffix can be YYYY A specific fiscal year such as 2010 Ln n years ago relative to the current fiscal year for example L1 is last year and L2 is 2 years ago Nn n years in the future relative to the current fiscal year for example N1 is next year and N2 isthe year after next Note If no suffix is supplied the current fiscal year is used User s Guide 161 Appendix D FR Function Reference umet 0 Deeripon account A string specifying a single account or a range of accounts See About reference Account Number References page 59 criteria An expression that imposes selection criteria on the accounts and on Optional transaction fields including account and transaction optional fields The account reference and expression together determine the accounts and additional data that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 currency A string containing the currency reference A currency reference has Optional the form CCC T where CCC is a 3 character currency code for example CAD for Canadian dollars or USD for US dollars and Tis the currency type The currency type can be e S Source currency e E Equivalent source amount in the f
68. any lines from the report that have zero balances use z to omit zero balance lines or meet a condition such as balances less than 1 000 e 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 Use T to generate a single total line for an account range If you leave column D blank or specify a p Financial Reporter will list the details for each account in the range Column D constants for use in the spec area are the indicators for listing by transaction details by transaction consolidated total and by transaction total Values must begin with P 40 Sage 300 ERP Financial Reporter About Columns in Report Specifications Column D Constants List By Cell Value of Column D Transaction details in posted transaction order Transaction consolidated total Transaction consolidated by account P POSTRAN Transaction consolidated by journal date P POSTDATE Transaction consolidated by posting sequence P POSTSEQ Transactions can also be listed by consolidated account segments and account groups as listings by accounts When consolidating by a specific account order in listing by transactions the column D value must start with P instead of with Example P ACSEGVAL02 is
69. are calculated relative to the fiscal period set for the report with the exception of NETADJ and NETCLOSE and fields that reference a specific time period NETADJ is the amount posted to the adjustment period NETCLOSE is the amount posted to the closing period e If there are no records for a future period zero is returned e You can use a fiscal set prefix with NET to return specific values For example NETP provides the net amount for the current fiscal period ANETP provides the actual net change for the current fiscal period INETCLOSE provides the closing entry only 2NETADJ provides the amount from period 14 160 Sage 300 ERP Financial Reporter FRTRNCR Syntax Arguments Financial Reporter Functions This function is similar to FRCREDIT page 128 and retrieves net totals credit amounts only from transactions It retrieves actual values and summary totals by both account and transaction fields and allows drill down to associated transactions Note This function cannot retrieve budget or provisional amounts calculate balances or include rollup amounts or totals FRTRNCR field name account reference criteria currency Tip Use the criteria argument to restrict this function to credit CR transactions Araument Description field name The name of a field from the General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal year suffix separated from the field n
70. arts with the row is used for setting up defaults for the remaining rows on the report You can have more than one default specification row each applies to the rows between it and the next default row e Title row If column A starts with NT the row is used to define title lines on the final report You can have up to five title rows which will appear at the top of each tab of the report immediately under the header lines User s Guide 39 Chapter 3 Designing Financial Statements Body specification row Rows that start with account number references are called body specification rows Note e If column A contains any other data Financial Reporter assumes that the data is a reference to one or more account numbers e If an account number appears in column A it generally means that all Columns B C and D Columns B C and D filter accounts specified in column A columns 2 through 4 of the Spec range to refine the account selection For example e Column A can specify a range of accounts e 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 For more information see About Selection Criteria for Filtering Accounts page 62 e Column C can remove
71. ation For Financial Reporter statements you use the Print Destination settings on the Print Financial Statements screen These statements do not use the destination selected for other Sage 300 ERP reports from the File gt Print Destination menu on the desktop At print time you select one of the following destinations e Printer Print to a physical printer e Preview Display the report on your computer screen e File Save the report as a file e E mail Send the report as an attachment in an e mail message If you select File as the print destination e You specify whether to save the file in Excel format or in text format Financial Reporter then saves all the values in the report s Print range in that file format e When you print Financial Reporter assigns new file names based on the original Financial Report spreadsheet name followed by a sequence number For example if the spreadsheet was named BALANCE XLS the print file spreadsheet would be named BALANCE 001 or BALANCE 001 xls depending on the format you chose If statement printing generates a series of reports for example a departmentalized report the separate reports will be numbered BALANCE 001 BALANCE 002 BALANCE 003 and so on Report As You can print a single consolidated report or you can print separate reports for different account number segments For example if one of your account number segments is a department code you can print a separate
72. cial Reporter Appendix A Sample Financial Statements Financial statements are Microsoft Excel spreadsheets that use special functions to extract data from the Sage 300 ERP company database General Ledger includes the following standard financial statements English language statements are located in the ENG subfolder of the General Ledger program folder If are using another supported language version of Sage 300 ERP you ll find your statements in a language specific folder For example French statements are located in the FRA subfolder and work with French language Excel Important The sample statements are designed for Sage 300 ERP sample data and retrieve data using standard account groups To use the statements with your own data you must either e Assign the same standard account groups to your accounts e Customize the sample statements to match the account groups that you have set up for your company To modify a sample statement first copy the file and then save it with a new file name Balance Sheets General Ledger includes standard balance sheet statements and summaries The sample summaries include e Aregular size balance sheet that summarizes the company s assets liabilities and equity at a specific point in time e Acommon size balance sheet which includes a regular size balance sheet and separate reports listing asset line items as a percentage of total assets and liability line items as a percent
73. cripting type library is properly installed in Windows System The Print Financial Statements feature does not support Page Break Preview Use the G L Statement Designer instead You must install a printer before you can view output or choose a print destination Cannot find Microsoft Scripting Runtime Object required when starting FR User s Guide 179 Error Messages when Appendix F FAQ and Troubleshooting Starting Financial Reporter Error Messages when Starting Financial Reporter Message Description cmdStart lt error message gt Initializing FR Error lt error id gt J Failure in starting Statement Designer Initializing FR Error lt error id gt Failure in starting Print Financial Statements Internal error Insufficient memory or System error Incorrect procedure 180 Sage 300 ERP Financial Reporter If you receive error messages that are in this format you may need to reinstall Microsoft Excel or General Ledger or System Manager After reinstalling restart the system and then check to see if the problem has been resolved If the problem cannot be resolved by reinstalling these programs contact your system administrator Tip Before reinstalling Microsoft Excel confirm that Microsoft Office is installed correctly From the Programs and Features screen of Windows Control Panel right click Microsoft Office and then click Change Select the option to
74. ction Reference Examples FRAMT The provisionally posted balance of Deutsche Marks in PBALP L1 1000 33 source currency to account 1000 33 248 for the 248 DEM S current period last year FRAMT QBALP 2000 The quantity balance of the current period for account 2000 FRAMT The total actual balance for all balance sheet accounts ABAL 2010 1000 with account numbers starting with 1000 for the ACCTTYPE B year 2010 120 Sage 300 ERP Financial Reporter FRAMTA Syntax Arguments Financial Reporter Functions This function is similar to FRAMT page 114 It has the same arguments as FRAMT and returns the same data The difference is that the sign of the return value is dependent on the account type for FRAMT but not for FRAMTA FRAMTA returns all debit balances as positive numbers and all credit balances as negative numbers making it easy to sum a range of accounts without regard for the account type By contrast FRAMT returns debit balances in credit accounts as negative numbers and credit balances in credit accounts as positive numbers FRAMTA field name account reference criteria currency Note The function returns 0 if the account reference is missing the account does not exist or if no accounts exist in the specified range jargument Description field name The name of a field from the General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal y
75. d Benchmarks with Graphs A graph comparing the company s financial ratios to the benchmark Important Financial Reporter does not generate this report Rather the report uses data from fixed cells in the Excel spreadsheet and may not print the correct information if the report is generated with the Include Report Options setting If you are printing the report from the G L Print Financial Statements screen in Sage 300 ERP make sure that Include Report Options is not selected Note For Excel based charts the default position of data labels varies by Excel version To add or remove data labels or modify their position refer to the help or documentation provided with your installed version of Excel Finratio04 xls Comparative Financial Ratios Current Year Previous Year A comparison of the company s financial ratios for the current and previous years and the variance between the ratios Finratio05 xls Comparative Financial Ratios and Benchmarks with Graphs Current Year Previous Year User s Guide 91 Appendix A Sample Financial Statements A graph comparing the company s financial ratios for the current and previous years to the benchmark Important Financial Reporter does not generate this report Rather the report uses data from fixed cells in the Excel spreadsheet and may not print the correct information if the report is generated with the Include Report Options setting If you are printing the report from the G
76. d L2 is 2 years ago Nn n years in the future relative to the current fiscal year for example N1 is next year and N2 isthe year after next Note If no suffix is supplied the current fiscal year is used A string specifying a single account or a range of accounts See About 156 Sage 300 ERP Financial Reporter Financial Reporter Functions Argument Beseipion Account Number References page 59 criteria An expression that imposes selection criteria on the accounts and on Optional transaction fields including account and transaction optional fields The account reference and expression together determine the accounts and additional data that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 currency A string containing the currency reference A currency reference has Optional the form CCC T where CCC is a 3 character currency code for example CAD for Canadian dollars or USD for US dollars and T is the currency type The currency type can be e S Source currency e E Equivalent source amount in the functional currency e F Functional currency total including functional equivalents of all source amounts e R Reporting currency total If the currency code is functional R includes reporting equivalents of all source amounts Ifthe currency code is not functional R provides equivalent amounts in the reporting currency Note If no currency is sup
77. d by Segment Specifying D ACCT in column D indicates that all accounts in the range are to be listed in account segment order and consolidated by the account segment A B e D E F IM FRSDESC 1 FRAMT BALP Current Balance 1100 1200 D ACCT Petty cash 274 57 58 Sage 300 ERP Financial Reporter Using Formulas to Retrieve General Ledger Account Information Two lines are printed in the above example because there are two account numbers with an account segment code between 1100 and 1200 The first account segment code 1100 is for petty cash and the second account segment code 1200 is for Bank Accounts Two separate account numbers have an account segment code of 1200 but these two accounts shown in the previous example have been consolidated D ACCT summarizes by account segment code all other segments that happen to be within the ranges specified when printing the report For subsequent rows the order will revert to the order that was in effect prior to this specification row About Account Number References An account number reference specifies the accounts from which you want to retrieve General Ledger data Tip In the Statement Designer use FR Paste to specify accounts and account ranges without manually entering formulas For more information see About FR Paste page 96 Specifying Accounts and Ranges of Accounts You can specify a single account number or a range of account numbers
78. de 163 Appendix D FR Function Reference Field Description Reporter Code Suffix NET net changes Preceding quarter 3 periods PQ NETPQ2 Preceding quarter n periods ago PQnPA NETPQnPA Half year net change Field Description Reporter Code Suffix NET net changes lpo handles any three periods providing you with revolving quarters The preceding 3 periods include the current period plus the two previous ones 2The NET for a group of periods that crosses a year end boundary will also include the adjustments in period 14 but not include the closing entry otherwise the net would include the zeroing entries in income and expense accounts 3This code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to date figures relative to the current half year If you are now in period 2 of the current half year half year TD will give you the first 2 periods of any previous half year nSATD lets you go up to 2 half years ago If you need to specify more use nSATD L1 5PS handles any six periods providing you with revolving half years The preceding six periods includes the current period plus the five previous ones 164 Sage 300 ERP Financial Reporter Financial Reporter Functions Total year net change Field Description Reporter Code Suffix NET net changes Preceding year 12 or 13 periods NETPY emm ON Note e You can print year end adjustments and
79. ditional data that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 currency A string containing the currency reference A currency reference has Optional the form CCC T where CCC is a 3 character currency code for example CAD for Canadian dollars or USD for US dollars and T is the currency type The currency type can be e S Source currency e E Equivalent source amount in the functional currency e F Functional currency total including functional equivalents of all source amounts e R Reporting currency total If the currency code is functional R includes reporting equivalents of all source amounts Ifthe currency code is not functional R provides equivalent amounts in the reporting currency Note If no currency is supplied functional home currency is used Fiscal Set Field Names Financial Reporter can retrieve the following net change fields Note In the Reporter Code Suffix n is a number that can refer to the fiscal period from 1 to 13 or to the quarter from 1 to 4 User s Guide 151 Appendix D FR Function Reference Period net change Field Description Reporter Code Suffix NET net changes Quarter net change Field Description Reporter Code Suffix NET net changes Current quarter to end Qo NETQ Current quarter to date NETOTD eC INETP NETnP NET4Q NETPQ will also include adjustments from period 14 if the net is
80. e and net change fields Note In the Reporter Code Suffix n is a number that can refer to the fiscal period from 1 to 13 or to the quarter from 1 to 4 Period balance and net change Field Description Reporter Code Suffix BAL NET balance net changes Current period BALP NETP2 BALLP NETLP Last period n periods ago BALnPA NETnPA 1BALP BALnP BALYTD etc include adjustments from period 14 only if the balance is for the final period of the year BALY includes period 14 The closing entries are not included To include closing entries use BALCLOSE To print the balance from the final fiscal period without the adjustment amount use FRAMT BALP FRAMT NETADJ 2NETP NETnP NET4Q NETPQ will also include adjustments from period 14 if the net is requested for the final period of the year NET works like BAL User s Guide 123 Appendix D FR Function Reference Quarter balance and net change Field Description Reporter Code Suffix Current quarter to end Current quarter to date Last quarter Last quarter to date nth quarter nth quarter to date n quarters ago n quarters ago to date Preceding quarter 3 periods Preceding quarter n periods PQnPA ago BAL balance net changes BALQTD BALLQ BALLQTD BALnQATD BALPQnPA NETPQnPA BALnQ BALnQTD Half year balance and net change Field Description Suffix Reporter Code BAL NET balance net chang
81. e consolidated on posting Yes or 1 if quantities are used with account No or O if not The unit of measure if quantities are used ALLOCSW If account can be reallocated Yes or 1 is yes No or O is no Multicurrency switch Yes or 1 is yes No or 0 is no If posting is in all or specific source currencies All currency or 1 is all Specific curency or 0 is specified If account is a control account Control account or 1 is yes Non control account or 0 is no field 172 Sage 300 ERP Financial Reporter Transaction Fields ACCTSEGVAL Account Segment Code ACCTGRPCPY Account group COPY A copy of the group code that is a string instead of an integer ROLLUPSW Rollup switch If unformatted 1 if account is a rollup account O if not If formatted Yes or No Transaction Fields Transaction fields include account master fields and account optional fields defined in G L Setup and the following transaction fields and transaction optional fields defined in FRPOST To retrieve account optional fields add the prefix A before a field name for example A ACCTID To retrieve transaction optional fields add the prefix T before the field name for example T QTY Be sure to include the period after these prefixes For more information see Account Master Fields page 171 and FRPOST page 144 wm O 0 emm sce Tan User s Guide 173 Appendix E FR Field Reference ee 0 emeneetened
82. e period zero is returned e You can use a fiscal set prefix with NET and BAL to return specific values For example BALP provides the actual balance or provisional balance for supported functions for the current fiscal period ABALP provides the actual balance for the current fiscal period 1BALP provides the balance from budget set 1 for the current fiscal period for functions that can reference budget sets 1BALCLOSE acts like BALP in every period except the last period of the year when it includes the adjustments and the closing entry NETCLOSE provides the closing entry only 2NETADJ provides the amount from period 14 To print the balance from the final fiscal period without the adjustment amount use FRAMT BALP FRAMT NETADJ 126 Sage 300 ERP Financial Reporter Financial Reporter Functions Examples The following examples show the differences between the return values for the FRAMT and FRAMTA functions AccountType FRAMT FRAMTA Return Value Return Value mec aoc i User s Guide 127 Appendix D FR Function Reference FRCREDIT This function is the same as FRAMT page 114 except that it returns values for only those accounts that have credit amounts balances or net changes regardless of whether the account is a debit or credit account Syntax Eig account reference Criteria DU a Tip Use the criteria argument to restrict this function to credit CR account types FRCREDIT returns t
83. e spec Specify the order in which records are sorted in the report Specify whether to create a consolidated report or separate reports by account segment Select the Audit Information option so you can see which account numbers Financial Reporter selected Restrict the report to ranges of accounts based on account groups account segments or account numbers User s Guide 23 Chapter 2 Financial Reporter Tutorial Where To Now Now that you are familiar with the Statement Designer and financial report specifications read Chapter 3 Writing Report Specifications for more information about using Financial Reporter to create financial report specifications 24 Sage 300 ERP Financial Reporter Chapter 3 Designing Financial Statements You define a financial statement using 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 a Microsoft Excel spreadsheet you can use standard worksheet formulas formatting commands and graphing abilities to modify the report and you can create multidimensional models to perform further analysis on the data Note Financial Reporter includes a set of standard report specifications that are compatible with any chart of accounts that uses the standard account group classifications and user defined account groups You can print financial statements using these standard repor
84. e the following Excel DATEVALUE formula to change the fomat DATEVALUE FR SessionDate The bottom of the report is missing when printed If some data is missing from the bottom of a report when printing you may need to change the size of the report as follows 1 On the Formulas tab of the Excel ribbon click Define Name 2 Inthe Name field select Spec 3 Inthe Refers to field type Sheet1 4 Click OK For more information see About the Spec Range page 71 and Setting the Spec Range page 73 User s Guide 177 Appendix F FAQ and Troubleshooting Financial Reporter Error Messages Financial Reporter error messages fall into two general categories e Error messages printed on financial statements These messages report problems in formulas and report specifications such as an account reference with too many account code segments If Financial Reporter encounters a problem when generating a financial statement it inserts a message in the report and then continues to generate and print the rest of the statement e Error messages displayed by Financial Reporter These messages appear when you attempt to open Financial Reporter screens or when report generation is canceled and may appear under the following conditions If Sage 300 ERP cannot start Microsoft Excel If the computer runs out of memory RAM If Financial Reporter cannot continue to generate the report For example this may
85. eadsheet e If you generate a series of statements for a group of departments the separate statements are generated in consecutive sheets in an Excel workbook When you print a set of statements using the G L Print Financial Statements screen Financial Reporter e Repeats the report generation process for each account segment e Prints a series of separate statements For instructions see Printing a Financial Statement page 79 Tip Keep financial ratios and graphs in a formula sheet The most common type of formula only report is one that keeps financial ratios User s Guide 77 Chapter 4 Printing Financial Statements There are two main advantages of a report that is composed only of formulas 1 The report has the same cell references each time you use it 2 The report area remains intact when you recalculate the report If you generate a report from a specification the old report is cleared before the new one is generated Because the spreadsheet remains the same each time you use it you can include graphs in your reports and be sure of the data you are referencing Printing Reports With and Without a Spec Range If a spreadsheet has formulas only with no Spec range you cannot use FR View to calculate and print the report Instead e You press F9 to calculate the values of the formulas e Financial Reporter recalculates the spreadsheet when you print the report e If you do not want to print the whole spr
86. eadsheet you can use Excel s Set Print Area command to define the area you wish to print If the spreadsheet has a defined Spec range Financial Reporter automatically regenerates the financial statement before printing it All the columns included the Spec range are included on the printed statement except columns A B C and D Preparing to Print Reports If report information extends beyond the right edge of the spreadsheet print area it may be cut off You can modify a report specification to make sure that report information is not truncated by e Widening the spreadsheet column that contains the extra wide result e Redefining the Spec range to include the columns over which the information extends This approach works because the right print area boundary of a financial statement that is generated from a specification is the same as the Spec range boundary 78 Sage 300 ERP Financial Reporter Printing a Financial Statement Printing a Financial Statement To print a financial statement in Financial Reporter you can use either of the following methods e Use the G L Print Financial Statements screen to specify print options and print the financial statement e Open a financial statement specification in the Statement Designer use FR View to generate the statement and then print The following procedure describes how to use the G L Print Financial Statements screen For information about using the Statement Design
87. eadsheet functions Financial Reporter functions can replace parameters of spreadsheet functions so you can test the contents of fields before printing the report or manipulate them as you wish To specify that a balance is printed only if it exceeds 6000 use IF FRAMT BALP 1000 gt 6000 FRAMT BALP 1000 0 To print the average value of two accounts use AVERAGE FRAMT BALP 1000 FRAMT BALP 2000 About Optional Fields in Financial Reports You can print account optional fields and transaction optional fields detail using Financial Reporter e You can retrieve account optional fields using FRACCT or FRPOST field name with the prefix A You must include the period following the letter A e Youretrieve transaction optional fields using FRPOST Note A field name for a transaction optional field is the original optional field name with the prefix T You must include the period following the letter T If an FR command retrieves data from more than one transaction you can consolidate optional field data for Amount Integer and Number optional field types in the following ways e You can print the optional field value of the first retrieved record that has optional field values If the field specified in the first parameter of the FRACCT or FRPOST command is an amount integer or number field and is appended with v only the first optional field value is reported e You can print a cons
88. ear e Columns H and J compare each account as a percentage of the totals for the corresponding year 48 Sage 300 ERP Financial Reporter About Columns in Report Specifications Pe a ac ee ees ers ger 1 FRAMTA BALP G1 G4 _ FRAMTA BALP L1 11 14 em Current Year of Total Previous Year of Total 3 LL a 4 SUM G3 SUM I3 Note The formulas in columns H and J illustrate that the range of accounts is defined in row 3 of the report specification and the total calculation is in row 4 Financial Reporter recalculates these cell references for the generated report Example 3 Period and Year to Date Expenses This example produces a comparative expense schedule with two columns of data e Column E lists the expenses this period e Column F lists the expenses year to date Ix arua seg EH ESSE EET F T N FRAMTA NETP FRAMTA NETYTD 2 Expenses This Period Expenses Year to Date 3 6000 8999 Example 4 Current Period and Year to Date Expenses as a Percentage of Sales This example produces a comparative expense schedule with each current period and year to date expense listed as a percentage of sales e The range of expense accounts is defined in row 13 and uses the default formulas defined in row 11 of the report specification e The total sales calculation is in row 9 SS e G H J g SUM G8 SUM I8 1 11 FRAMTA NETP G11 G97 FRAMTA NETYTD
89. ear suffix separated from the field name with a period The fiscal designator prefix can be e A Actual amount e R Rollup amounts R must be the first character and can be used with other prefixes function to a statement select the Use Rolled Up Amounts check box P Provisional amounts Q Quantities either actual or provisional depending on the report type QA Actual quantities QP Provisional quantities User s Guide 121 Appendix D FR Function Reference Argument _Desertpton e 1 to 5 Budget set number The number of available budget sets varies by product edition 2 in Sage 300 Standard ERP 3 in Sage 300 Advanced ERP 5 in Sage 300 Premium ERP Note If no prefix is supplied the default report setting is used either actual or provisional depending on the type of report Similarly if the fiscal prefix is Q or RQ actual or provisional quantities are returned depending on the type of report The fiscal year suffix can be YYYY A specific fiscal year such as 2010 Ln n years ago relative to the current fiscal year for example L1 is last year and L2 is 2 years ago Nn n years in the future relative to the current fiscal year for example N1 is next year and N2 isthe year after next Note If no suffix is supplied the current fiscal year is used Example For the field name argument ABALP 2008 ABALP is the combination of fiscal set prefix and field name referencing the actual balance
90. elect Row Height and then set height to 0 e Click and drag the top or bottom edge of the row until its height is O About Page Layout The File Page Setup command controls the page layout and allows you to set headers footers and margins in a spreadsheet Printing Column Headings with Options Set Print Titles To print recurring column headings at the top of each page use the Financial Statement Designer s Set Print Titles feature Setting Page Breaks If you set a page break in the report template Financial Reporter treats the page break as an As is row and inserts it in the corresponding point in the final report The main use of page breaks is to begin new sections of a statement at the top of a page or to ensure that all columns to the right of the page break appear on a separate page Page breaks create a horizontal and a vertical division where you insert them in a spreadsheet 70 Sage 300 ERP Financial Reporter About Formatting Financial Statements Page Breaks SS 858 8 5 L8 D 2T ER 3 ET 80 De Ae The above diagram shows the effect of setting two page breaks one in cell A11 which creates a horizontal break and one in cell H23 which also creates a vertical break Page breaks are not very useful for statements containing account references that are expanded into many rows They work best for starting sections of a report on a new page Generally for multi page reports you can either leave the pagina
91. er for column G and then click Format Cells a Onthe Category list click Accounting b Accept the default selections for decimal places and currency symbol or specify different settings if you prefer c Click OK Amounts in column G are formatted as currency using the decimal places and currency symbol you specified Click and drag the edges of column headers to adjust the width of columns as needed to display information Save your work User s Guide 11 Chapter 2 Financial Reporter Tutorial The statement now displays two lines and the total balance for those lines with balances formatted as currency E F G Sample Company Inc Account Description Balance 1000 Petty cash 1100 Accts receivable intercompany 259 781 86 259 781 86 After creating your financial statement e Continue to experiment with formatting commands by formatting and aligning column labels e Click File gt Print to see a preview of the printed statement Lesson 2 Using FR Paste to Insert Formulas This lesson introduces FR Paste an extremely useful command that you can use to quickly create formulas look up account numbers or define account selection criteria Exercise 1 Applying the FRACCT Function In this exercise you will use the FR Paste Function screen and the FRACCT function to search a range of accounts for words appearing in the account description 1 Select cell F10 2 Onthe Add Ins tab of the Excel r
92. er to print financial statements see About Generating Reports from Specifications page 28 and G L Statement Designer page 30 To print a financial statement 1 Open General Ledger gt Financial Reporter gt Print Financial Statements 2 Click Browse and then select a financial statement file For a list of sample financial statements included with Sage 300 ERP see Sample Financial Statements page 87 3 Under Fiscal Option specify the year and period for which to create the statement 4 Under Report Type specify whether to include actual or provisional amounts in the report Actual Includes only amounts that have been permanently posted to general ledger accounts Provisional Includes amounts that have been provisionally posted to general ledger accounts as well as permanently posted amounts 5 Under Report As specify whether to print a single consolidated report or to print separate reports for different account number segments the Sort By area of the screen and then enter the range of segments This option is not available if you select the Account segment 6 Under Include select the Report Options and or DrillDown Information options as needed User s Guide 79 Chapter 4 Printing Financial Statements When you select Report Options the first page of the financial statement displays the following information Statement name Fiscal year and period Report type actual or provisional
93. eral Ledger includes Financial Reporter a powerful tool that uses Microsoft Excel to read manipulate format graph and print general ledger data The Financial Reporter User s Guide contains all the information you need to design and print financial statements for your business It contains the following chapters e Chapter 1 Financial Reporter Overview This chapter provides an overview of Financial Reporter including some basic concepts and explains how Financial Reporter works with Microsoft Excel to extract and print your General Ledger data e Chapter 2 Financial Reporter Tutorial This chapter guides you through the design of a basic financial statement using the G L Statement Designer The lessons will familiarize you with the product before you begin working with specification files e Chapter 3 Designing Financial Statements This chapter includes detailed information about using the G L Statement Designer to create custom financial statements e Chapter 4 Printing Financial Statements This chapter includes information about using the G L Print Financial Statements screen to print financial statements e Appendix A Sample Financial Statements This appendix provides information about the sample specification files included with Sage 300 ERP and about the financial statements they produce e Appendix B FR Menu Commands This appendix lists menu commands that appear on the FR menu in the G L Statement Designe
94. ers and selection Information in Financial Reports page criteria 98 FR View Opens the G L Financial For more information see G L Statement Statement Designer Designer page 30 screen which you use to generate financial statements from report specifications About FR Drilldown On the G L Print Financial Statements and Statement Designer screens you can select the DrillDown Information option to add drilldown link comments to the generated report Drilldown instructions are stored in the comment object of a cell After generating a report you can right click a results cell and then click FR DrillDown to view transaction details Note The recipient of an e mailed report must be using the same database with General Ledger activated if they want to drill down to underlying transactions in the report Warning A spreadsheet containing a large number of drilldown links may be slow to open or fail to open in the Statement Designer and in Microsoft Excel When you use the Statement Designer to open a Microsoft Excel worksheet an Add Ins tab appears on the Excel ribbon On this tab the FR menu displays commands you can use to create and edit financial reports and specifications The FR Drilldown command appears on this menu You use it to view original 94 Sage 300 ERP Financial Reporter About FR Drilldown transactions after a report has been generated in the Financial Statement Designer Note You must use the
95. es 1This code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to date figures relative to the current quarter If you are now in period 2 of the current quarter QTD will give you the first 2 periods of any previous quarter 2nQATD lets you go up to four quarters ago If you need to specify more use nQATD L1 3pQ handles any three periods providing you with revolving quarters The preceding 3 periods include the current period plus the two previous ones The NET for a group of periods that crosses a year end boundary will also include the adjustments in period 14 but not include the closing entry otherwise the net would include the zeroing entries in income and expense accounts 124 Sage 300 ERP Financial Reporter Financial Reporter Functions Field Description Reporter Code NET Suffix balance net changes Current half year to date BALSTD NETSTD Last half year CE BALLS NETLS Last half year to date LSTD BALLSTD NETLSTD nth half year BALnS NETnS hth half year to date BALnSTD NETnSTD n half years ago SA BALnSA NETnSA n half years ago to date nSATD BALnSATD NETnSATD preceding half year 6 periods eo BALPS NETPS Preceding half year n periods PSnPA BALPSnPA NETPSnPA ago Total year balance and net change Field Description Reporter Code BAL NET Suffix balance net changes Nextdxe C no MOT WD Preceding year 12 or 13 BALPY NETPY ETEA
96. es the transaction optional value for QTY for Account T QTY 4000 4000 for the inquired period Note Account numbers and transaction date are shown in the examples however this information can be set in the report specification the account number in column A and other criteria such as the transaction date in column B 146 Sage 300 ERP Financial Reporter Financial Reporter Functions This function returns the exchange rate as a multiplier for converting amounts to functional currency If the exchange rate is defined as a divisor Sage 300 ERP provides its reciprocal FRRATE from source Syntax FRRATE fun option Arguments functional currency source currency rate type rate date Optional option Optional ctional currency source currency rate type rate date Functional home currency code Source currency code Rate type code If the period end date is not specified Financial Reporter will use e The period end date defined in the report specification e Thesession date if the report is not generated from a report specification The date is specified as DATE YYYY MM DD Note that the DATE spreadsheet function returns the serial number of the date This argument is only required for handling exchange rates for euro member currencies that use the Euro Conversion Rates table such as francs or schillings Use one of the following parameters to choose which par
97. eves one piece of information from the General Ledger database You can save the spreadsheet and then view updated information the next time you open it In cell E6 type 1100 Select cells F5 and G5 and then click and drag the bottom right corner of the selection border to copy the contents of F5 and G5 into F6 and G6 Account Description Balance 1000 1100 The Statement Designer updates the copied formulas so they use account 1100 cell E6 instead of account 1000 cell E5 Press F9 to update the information in the copied formulas Save your work 10 Sage 300 ERP Financial Reporter Lesson 1 Using Formulas to Retrieve G L Data The statement now appears as follows E F G Sample Company Inc Account Description Balance 1000 Petty cash 0 1100 Accts receivable intercompany 259781 86 Exercise 2 Adding Column Totals and Formatting Columns In this exercise you will add column totals and format columns to prepare your financial statement for review and printing 1 2 Select cell G7 On the Home tab in the Editing group click the Autosum x button Excel selects cells G5 and G6 Press Enter Cell G7 displays the sum of cells G5 and G6 Select cell G7 again On the Home tab of the Excel ribbon in the Font group click the arrow beside the Borders button and then click Top Border At the top of the spreadsheet click column G to select all cells in that column Right click the column head
98. f the source amount F Functional currency total including functional equivalents of all source amounts R Reporting currency total includes reporting equivalents of all source amounts Note This option is only available if you select the Euro as your functional currency and specify a different reporting currency in Company Profile Note If no currency is supplied functional home currency is used Fiscal Set Field Names Financial Reporter can retrieve the following balance and net change fields Note In the Reporter Code Suffix n is a number that can refer to the fiscal period from 1 to 13 or to the quarter from 1 to 4 Period balance and net change Field Description Reporter Code Suffix BAL NET balance net changes Current period BALP NETP2 BALLP NETLP Last period n periods ago BALnPA NETnPA IBALP BALnP BALYTD etc include adjustments from period 14 only if the balance is for the final period of the year BALY includes period 14 The closing entries are not included To include closing entries use BALCLOSE To print the balance from the final fiscal period without the adjustment amount use FRAMT BALP FRAMT NETADJ 2NETP NETnP NET4Q NETPQ will also include adjustments from period 14 if the net is requested for the final period of the year NET works like BAL User s Guide 137 Appendix D FR Function Reference Quarter balance and net change Field Description Reporter
99. f year to date LSTD BALLSTD NETLSTD nth half year BALnS NETnS hth half year to date BALnSTD NETnSTD n half years ago SA BALnSA NETnSA n half years ago to date nSATD BALnSATD NETnSATD preceding half year 6 periods eo BALPS NETPS Preceding half year n periods PSnPA BALPSnPA NETPSnPA ago Total year balance and net change Field Description Reporter Code BAL NET Suffix balance net changes Nextdxe C no MOT WD Preceding year 12 or 13 BALPY NETPY ETEA Preceding year n periods ago year n periods ago PYnPA BALPYnPA NETPYnPA PA Beginning of year OPEN BALOPEN i 1This code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to date figures relative to the current half year If you are now in period 2 of the current half year half year TD will give you the first 2 periods of any previous half year nSATD lets you go up to 2 half years ago If you need to specify more use nSATD L1 3PS handles any six periods providing you with revolving half years The preceding six periods includes the current period plus the five previous ones User s Guide 139 Appendix D FR Function Reference Field Description Reporter Code BAL NET Suffix balance net changes End of year CLOSE BALCLOSE NETCLOSE period 14 adjustments ADD S CS S Note e You can print year end adjustments and transactions to close income accounts to retai
100. f you select the Drilldown Information option when generating a report every cell with drilldown displays a comment Right click and choose FR If the spreadsheet has many columns this can fill the spreadsheet User s Guide 175 Appendix F FAQ and Troubleshooting To collapse comments so that only a content indicator appears 1 Click File gt Options 2 Inthe Excel Options window click the Advanced tab 3 Inthe Display group select the Indicators only and comments on hover option 4 Click OK Print recurring column headings at the top of each page when the report has multiple pages When a report has multiple pages you can use title lines to print recurring column headings at the top of each page For more information see Printing a Financial Statement page 79 Set grid lines and tab breaks You set all formatting for financial reports using the formatting commands in the Financial Statement Designer For more information see About Formatting Financial Statements page 68 Set the Spec range Before you can generate and print a financial report from a report specification you must define the Spec range which is a named range of cells in the spreadsheet For more information see About the Spec Range page 71 and Setting the Spec Range page 73 Troubleshooting I m getting error messages For a list of Financial error messages and descriptions see Financial Reporter Error Messages page 178
101. fication performs the following actions 1 Prints the rounded balances of all asset accounts it retrieves from the general ledger using ROUND FRAMT BALP 3 The 3 indicates the number of digits to the left of the decimal place to which the value is rounded in this case thousands Because D is specified in column D each account is printed on a separate line This is on row 5 2 Totals the rounded balances of all asset accounts Row 6 3 Prints the rounded balances of all liability accounts it retrieves from the general ledger using the same formula Again each account is printed ona separate line Row 9 4 Totals the rounded balances of all liability accounts Row 10 5 Retrieves a single balance for equity This amount is calculated on a hidden row because the difference between the rounded assets and the rounded liabilities plus equity will be added to the equity section Row 11 6 Calculates the difference between the balances Row 14 7 Sums the difference between the balances and the equity amount Row 12 8 Displays the balance for total liabilities and equity Since this balance must match assets the specification uses the same total Row 13 User s Guide 35 Chapter 3 Designing Financial Statements Note If any accounts are missing from this statement it will still balance because we have forced a balance between the assets and the liabilities plus equity Example 2 Forcing a B
102. from the Financial Statement Designer s FR menu to insert a Financial Reporter function into a financial statement From the FR Paste screen you can select the function its arguments and additional report options For more information see About FR Paste page 96 106 Sage 300 ERP Financial Reporter Financial Reporter Functions FR This function can retrieve G L Option company and fiscal calendar data runtime parameter values and statement print options Syntax FR option formatted Arguments jArgument Description option The name of a field from the Company Profile Fiscal Calendar or G L Options screens or a specified runtime parameter or print option formatted Indicates whether the return value should be formatted according to Optional the presentation defined in the view If omitted the default is Formatted Use one of the following constructs e FORMATTED F or FMT e UNFORMATTED U or UNFMT Option can be one of the following G L Options fields name Orgid 6 character database ID for the company for example SAMINC Address4 Fourth address line Address3 Third address line City Province Province User s Guide 107 Appendix D FR Function Reference name Zip Zpede 00 code Ledger contact name ppm functional currency HRPCurr rr Reporting currency code when the Eurois the functional currency currency Reporting currency code when the Eurois the
103. g it statement You do not need to use FR Clear before using FR View to generate a report However by clearing the report range first you save a smaller spreadsheet and one that will take less time to load whenever you print the financial report FR Drill down to transactions For more information see About FR Drilldown after generating a financial Drilldown page 94 statement that contains formulas for transactions FR Help View help for the Financial Statement Designer FR Options Specify whether the e Use Budget Opening Balance Select opening budget or opening this option to display the budget amount quantity for an account on carried forward from the previous year a financial statement should be carried forward Note If there is budget information for from the previous year the current year this option is ignored andthe information for the current year is displayed Use Quantity Opening Balance Select this option to display quantities carried forward for accounts that use the Maintain Quantities option User s Guide 93 Appendix B FR Menu Commands ommand bescipton ee Note If there is quantity information for the current year this option is ignored and the information for the Tip If you change a selection run FR View to update your report FR Paste Inserts Financial Reporter For more information see About FR Paste functions account page 96 and Using FR Paste to Insert numb
104. ge and the Print Range 2 2 20 2200 cece cece cece cece ceeeceeeeeee 73 Consolidating the Financial Statements of Multiple Companies in Microsoft Excel 74 Chapter 4 Printing Financial Statements 00000000000 77 Printing Reports With and Without a Spec Range 2 2 20 0c cece eee c cece e cece eeeeeceeeeeees 78 Preparing to Print Reports oooi raia c ccc cece s esse e cece eeeeeeeeceeeeeeeeeeeeeerees 78 Printing a Financial Statement 02 22220 o occ cece ence cece cece eee ec cece ee eeeeeeeeeeeeeees 79 G L Print Financial Statements Screen 0 0 00 20 cece c nec e cece eee eeeeeeeeeeeeenees 81 OVERVICW course onde tudo uc ee eden eset tet ec LR Acute UII LO Merits 81 Print for Financial Statements Screen sssssssssssssssssesssessss sss ss essen rlns 85 Appendix A Sample Financial Statements 00000000000 eee 87 Balance Sheets c coto ee USA REESE 87 Income Statements occae El dn ERR GL DNE DIOE a a c CUNCTI DR eee 89 Financial Analysis Statements 0 2000 00 oaaao ccc cece cece cee ccc cece ceeececcceeettcceceetetttenseeeees 91 Financial Forecasts 0 0 00 220 c cece ccc ec e ec c eee e ec cece cece eee esee se lese ese see ee esee leer 92 Appendix B FR Menu Commands ssssss 93 ER Ment Commands y espasas ee e ic Mu DDR az eee eA Seles babe acid 93 About FR Brilldowh aieo ELIDMRIeRuciredendclne Spi ber ilicigi otetl aa a AC 94 FR DrillDown
105. ges BALYTD NETYTD BALPY NETPY BALPYnPA NETPYnPA Total year Y Year to date YTD Preceding year 12 or 13 PY periods Preceding year n periods ago PYnPA Beginning of year OPEN 1This code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to date figures relative to the current half year If you are now in period 2 of the current half year half year TD will give you the first 2 periods of any previous half year 2nSATD lets you go up to 2 half years ago If you need to specify more use nSATD L1 3PS handles any six periods providing you with revolving half years The preceding six periods includes the current period plus the five previous ones 132 Sage 300 ERP Financial Reporter Financial Reporter Functions Field Description Reporter Code BAL NET Suffix balance net changes End of year CLOSE BALCLOSE NETCLOSE Note e You can print year end adjustments and transactions to close income accounts to retained earnings with NETADJ and print closing figures with NETCLOSE and BALCLOSE e All fiscal field set values are calculated relative to the fiscal period set for the report with the exception of BALOPEN NETADJ and NETCLOSE and fields that reference a specific time period BALOPEN is the opening balance of the account for the year NETADJ is the amount posted to the adjustment period NETCLOSE is the amount posted to the closing peri
106. gt Blank workbook 2 Select cell A1 User s Guide 15 Chapter 2 Financial Reporter Tutorial 3 Type This is a short financial report and then press Enter Exercise 2 Creating a Default Specification Row Most financial reports contain the same kind of information in each row of the report To make it easier to define statements in Financial Reporter you can define a default row specification that will be used throughout the report The default row specification stays in effect until you define another default row specification In this exercise you will create a default row specification that will produce a report with two columns one containing account descriptions the other containing the current account balance 1 On the Formulas tab in the Formula Auditing group click the Show Formulas button Instead of calculated values the spreadsheet now displays formulas you enter Tip To toggle between showing formulas and calculated values press Ctrl the grave accent key located to the left of the number 1 key at the top left corner of the keyboard In cell A2 type Two backslashes indicate that the spreadsheet row contains default column functions to be used in subsequent rows In cell D2 type D and then press Enter D instructs Financial Reporter to print the details of each account on a separate row of the spreadsheet Select cell E2 On the Add Ins tab of the Excel ribbon click the arr
107. happen if the text or formula in a specification cell is too long The following tables describe these error messages and possible solutions Error Messages Printed on Financial Statements Error Messages Printed on Financial Statements Message Description Invalid account reference in cell The account reference you entered in column A lt cell location L gt lt cell value V gt is incorrect because it does not match the requirements for column A cells Invalid selection criteria in cell You entered an incorrect selection criteria in this cell location L gt cell value V gt cell Follow the criteria in column B to refine the account selection for example a filter that restricts the range of accounts to a particular account group account type or account name Invalid zero suppression formula You entered an incorrect definition for a zero in cell suppression formula in this cell Follow the cell location L gt cell value V gt criteria in column C to determine which lines are to be excluded 178 Sage 300 ERP Financial Reporter Financial Reporter Error Messages Message Description Invalid detail level in cell lt cell location L gt lt cell value V gt Invalid formula or expression in cell lt cell location L gt lt cell value V gt Invalid specification line type in cell lt cell location L gt lt cell value V gt FR cannot further generate any line for the worksheet
108. he General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal year suffix separated from the field name with a period The fiscal designator prefix can be e A Actual amount e Q Quantities e QA Actual quantities Note If no prefix is supplied the default report setting is used actual The fiscal year suffix can be YYYY A specific fiscal year such as 2010 Ln n years ago relative to the current fiscal year for example L1 is last year and L2 is 2 years ago Nn n years in the future relative to the current fiscal year for example N1 is next year and N2 isthe year after next Note If no suffix is supplied the current fiscal year is used 166 Sage 300 ERP Financial Reporter Financial Reporter Functions Argument Beseipion account A string specifying a single account or a range of accounts See About reference Account Number References page 59 criteria An expression that imposes selection criteria on the accounts and on Optional transaction fields including account and transaction optional fields The account reference and expression together determine the accounts and additional data that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 currency A string containing the currency reference A currency reference has Optional the form CCC T where CCC is a 3 character currency code for example CAD for Canadian
109. he account with the following exceptions e Returns blank if the value of a credit account is zero or a credit amount e Returns O if the value of a debit account is zero e Returns O if the account reference is missing or if a specified account cannot be found Araument Description field name The name of a field from the General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal year suffix separated from the field name with a period The fiscal designator prefix can be e A Actual amount e R Rollup amounts R must be the first character and can be used with other prefixes Tip In Excel if you use the FR PASTE screen to add the FRAMT function to a statement select the Use Rolled Up Amounts check box e P Provisional amounts User s Guide 135 Appendix D FR Function Reference Argument Description e Q Quantities either actual or provisional depending on the report type e QA Actual quantities e QP Provisional quantities e 1 to 5 Budget set number The number of available budget sets varies by product edition 2 in Sage 300 Standard ERP 3 in Sage 300 Advanced ERP 5 in Sage 300 Premium ERP Note If no prefix is supplied the default report setting is used either actual or provisional depending on the type of report Similarly if the fiscal prefix is Q or RQ actual or provisional quantities are returned depending on the type of report The fiscal year s
110. he credit value of the account with the following exceptions e Returns blank if the value of a debit account is zero or a debit amount e Returns O if the value of a credit account is zero e Returns 0 if the account reference is missing or if a specified account cannot be found Arguments S HENMNMENNNN field name The name of a field from the General Ledger fiscal sets with an optional fiscal designator prefix and optional fiscal year suffix separated from the field name with a period The fiscal designator prefix can be e A Actual amount e R Rollup amounts R must be the first character and can be used with other prefixes Tip In Excel if you use the FR PASTE screen to add the FRAMT function to a statement select the Use Rolled Up Amounts check box P Provisional amounts Q Quantities either actual or provisional depending on the report 128 Sage 300 ERP Financial Reporter Financial Reporter Functions gumet 6 Dewrplon 0000 account reference criteria Optional currency Optional type e QA Actual quantities e QP Provisional quantities e 1 to 5 Budget set number The number of available budget sets varies by product edition 2 in Sage 300 Standard ERP 3 in Sage 300 Advanced ERP 5 in Sage 300 Premium ERP Note If no prefix is supplied the default report setting is used either actual or provisional depending on the type of report Similarly if the fiscal prefix is Q
111. he list shows the fiscal set options you can select e Budget sets 1 2 3 4 and 5 e A Actual monetary figures e P Provisionally posted monetary figures Q Quantities either actual or provisional e QA Actual quantities e QP Provisionally posted quantities The FRTRN FRTRNA FRTRNCR and FRTRNDR functions include only the following options e A Actual monetary figures e Q Quantities either actual or provisional e QA Actual quantities Whether actual or provisional figures or quantities appear depends on the default you select on the Preview Financial Statements screen at the time you print a report Using FR Paste to Insert Information in Financial Reports When you use the Statement Designer to open a Microsoft Excel worksheet an Add Ins tab appears on the Excel ribbon On this tab the FR menu displays commands you can use to create and edit financial reports and specifications The FR Paste command appears on this menu You use it to paste formulas into spreadsheet cells to retrieve and display General Ledger data For more information see About FR Paste page 96 To insert information using FR Paste 1 In the Statement Designer select the cell where you want to insert General Ledger data 98 Sage 300 ERP Financial Reporter About FR Paste On the Excel ribbon on the Add Ins tab click FR gt FR Paste The FR Paste Function window appears Below the Functions list and available selections a ce
112. ibbon click the arrow beside FR and then click FR Paste 3 Onthe FR Paste Function screen specify a range of account descriptions to search a On the Functions list click FRACCT The syntax for FRACCT field name account reference criteria formatted appears to the right of the Functions list b Onthe Field Name list select ACCTDESC Description 12 Sage 300 ERP Financial Reporter Lesson 2 Using FR Paste to Insert Formulas c Inthe Account Reference group select the Account Range option d Specify a range of accounts i In the first field in the Account Reference group enter 1000 ii In the second field in the Account Reference group enter 1023 e Leave the Formatted option selected You have now created a formula that instructs Financial Reporter to scan the descriptions of every account in the range you specified and to return any words that are common to all account descriptions in that range In this example the word that appears in every account description is Sales The formula to be pasted into the spreadsheet appears below the Functions list c5 BM FR Paste Function a Functions FRACCT field name account reference criteria formatted FRACCT FRAMT Field Name ACCTDESC Description FRAMTA FRDEBIT FRCREDIT FRPOST FRTRN Account Reference Rm C Account Number I 000 a FRIRNGR Account Range I 023 Qa FRFISCAL FRSDESC v Formatted account ref
113. ies you use the following syntax Financial Information Syntax Sage 300 ERP Financial Information Account balance If BALP is specified without an A or P prefix for actual or provisional amounts it will default to the report setting chosen at print time Provisional balance Dp LP Actual quantity LP Provisional You can specify budget sets according to the edition of quantity Sage 300 ERP you use e Sage 300 Standard ERP includes budget sets 1 and 2 e Sage 300 Advanced ERPincludes budget sets 1 2 and3 e Sage 300 Premium ERP includes the numbers 1 to 5 for the budget sets for example 1BALP or 2BALP User s Guide 51 Chapter 3 Designing Financial Statements Reporter function at each point where you want to print financial information However identifying each item individually can be a time consuming and unnecessarily rigid way to create a statement Developing a statement specification with default formulas for each column indicating the group of accounts to which the column formulas will apply is a more flexible and efficient way to create your statement About Account Order in Financial Statements In financial statements Financial Reporter lists ranges of accounts in the order set in the financial report specification or if not set in the specification in the order you specify when printing You can sort accounts by any account segments and you can specify a range of values for each segment y
114. ifications 0 2200 20 cece cece cece cece ee eeceeeeeeeee 48 Using Formulas to Retrieve General Ledger Account Information 2 02 22 2202 51 About Account Order in Financial Statements ssusesusssesssesssssssess cece eee 52 About Consolidating Account Ranges sssssssssssssssssssssssssss e es ss esee sensns 55 About Account Number References 0 00 2202 ccc cece e cece cece eee sse se sse ese serre 59 About Selection Criteria for Filtering Accounts 2 0 0 0 0 00 c ccc cece cece cece e eee ce eee ee eee eaes 62 About Wildcard Characters in Account Numbers 22 2 0 2 0 20 e cece eee eee 66 About Formatting Financial Statements 020 0200 ooo ccc ccc cece eect e cece cee eeeeeeees 68 Adding Tithe IMSS iio Po on tet eee dno ne Weeds eo ES nau Niele Ac ne ton b tid aeui 68 Adding Explanatory Comments 2 20 2 202 cecccccecccccecceceeeeceeceeeeeceeeeteeeeeee 69 Grid Lines and Other Report Formatting 0 2 200 2220 cece cece cece cece cceeceeeeeeeeees 69 Hiding Columns and ROWS 220 220 2 cece cece cece cee ee cece cece eeeeeeeeeeeeeeeeeeseeeeeees 69 iv Sage 300 ERP Financial Reporter Contents About Page Layout 000 220 cece ccc eee cece cece cece eee sse ese esses ese esse test nes 70 About the Spec Range 222 222 cece eee cee ccc cee eee cece cece eee cece cece ee eeceeeeeeees 71 About the Report Ran
115. ight alignment number appearance cell borders and patterns you use the commands in the Font and Alignment groups on the Home tab of the Excel ribbon e To turn the spreadsheet grid on or off for printing and turn on or off the row and column labels you select options in the Show group on the View tab of the Excel ribbon e To force a tab breaks in the printed report use the Set tab Break command in the corresponding position of the report specification Set the tab break in column A of the spreadsheet Note that the cell containing the tab break creates a vertical tab break as well as a horizontal one All formatting applied to rows or columns in a report specification is applied to the rows and columns of the generated report To see the effect of all tab formatting in the Financial Report Designer click File Print to see a preview after you have used FR View to generate the report Hiding Columns and Rows It is often useful to have statement columns and rows that you don t want printed on your final report For example you may have statement rows that User s Guide 69 Chapter 3 Designing Financial Statements contain intermediate rounding calculations To hide a column you can do either of the following e Right click the column select Column Width and then set width to 0 e Click and drag the edge of the column until its width is 0 To hide a row you can do either of the following e Right click the row s
116. ime the account number changes Financial Reporter should create a new line e Column E lists the name of each account e Column F provides the current balance of accounts that meet the criterion of Department A Department B Column G provides the balance of accounts that meet the criterion of A Bic 5 E lala 1000 2999 50 Sage 300 ERP Financial Reporter ri G MACCT D ACCT FRACCT ACCTDESC FRAMT BALP ACSEGVALO02 A J J FRAMT BALP ACSEGVALU2 B Department B Using Formulas to Retrieve General Ledger Account Information Using Formulas to Retrieve General Ledger Account Information In the Financial Statement Designer you use Excel formulas to insert account record information or fiscal set values anywhere in a financial statement Example FRACCT ACCTDESC 1000 333 inserts the account description from account 1000 333 for example Cost of goods sold FRAMT ABALP 2019 1000 333 inserts the actual balance of account 1000 333 for the current fiscal period for the year 2019 You can also insert company information in a financial statement For example FR Coname inserts the name of the currently open company The easiest way to insert Sage 300 ERP financial information is by using the FR Paste function which you can use to create a formula quickly For more information see About FR Paste page 96 To specify balances and quantit
117. in consolidated single row or detailed one row per account format Example The formula FRAMT BALP 1200 1500 retrieves data from a range of accounts into a single spreadsheet cell When specifying account groups you can use these formulas e acctgroup sort by account group e acctgroupsort account sorted group The rules for specifying an account number reference are the same whether the reference is in a function or in column A of a specification row Example Column A of a specification row specifies account data to use for the row eo E 9 User s Guide 59 Chapter 3 Designing Financial Statements In this example is a wildcard character that takes the place of one or more characters For more information see About Wildcard Characters in Account Numbers page 66 In column D you can enter p to specify that each account is listed For more information see Account Reference Syntax and Examples page 101 Account References and Performance Financial Reporter can generate reports faster when it does not have to look up information for a substantial number of extra accounts For this reason check that the account processing order in the report matches the way you specify account references For more information see About Account Order in Financial Statements page 52 Restricting Account Selection Financial Reporter selects which account data is to appear in a financial report by evaluati
118. indicate Actual Provisional Quantity Actual Quantities Provisional Quantities or any of the five budget sets For example ABALP provides the actual balance at the current fiscal period 1BALP provides the balance from budget set 1 for the current fiscal period BALP provides either the actual balance or the provisional balance depending on the report type you select at print time e If you do not specify a prefix or if you select Q the default is either actual or provisional depending on the report type Sort by You can order accounts on financial statements by account number by a particular segment of the account number by the account group or by account group sort code You may then specify a range for the item by which you are ordering the report 84 Sage 300 ERP Financial Reporter G L Print Financial Statements Screen The sort order you select does not override account sorting orders that are specified in the report If the sorting order is crucial to the contents of the report you should build it into the report specification Note To print separate reports you must print by any segment except the account segment In other words if you have three segments in your account numbers Region Department and Account you must sort by the Region segment or the Department segment before you can select Separate for the Report as option Sorting order affects the order in which accounts within a range will appear on
119. ined default column specification you simply type backslash into a cell as in the last line of the example above Note e You can follow with or to print the inverse or the absolute value of the formula result e Remember that the actual report area starts from column E Advantages of Using Default Column Specifications Both of the following examples print exactly the same information The first example uses a complete formula to get each value the second example uses default column specifications and an account reference to a range of accounts For each of the following examples assume that accounts 1100 1200 and 1400 are consecutively numbered accounts in the general ledger User s Guide 45 Chapter 3 Designing Financial Statements Example 1 Using Formulas to Place Values In this example you require one formula to print each piece of information A By Cc DI E l F Account Description Current Balance FRACCT ACCTDESC 1020 FRAMT BALP 1020 FRACCT ACCTDESC 1200 FRAMT BALP 1200 FRACCT ACCTDESC 1400 FRAMT BALP 1 400 To print the current balance of 200 accounts along with their descriptions you would require 400 formulas and you would have to change the specification each time you added to the chart of accounts Example 2 Using Default Column Specifications to Place Values In this example you use a default column specification to place values EA 8 A
120. ines as there are accounts in the range You can use to specify all accounts in the ledger For more information see About Account Number References page 59 Columns B C and D may contain selection criteria that affect the range of accounts and whether the final value will be printed at all Adding Default Information for Columns B C and D The following example illustrates default values for columns B C and D as well as for the printed columns of the report Ac B c1CIDi E F 1 I ACCTTYPE B Z T FRACCT ACCTDESC FRAMT BALP Account Description Current Balance 1020 1400 Cash Note that you do not have to specify a backslash in columns B C or D to inherit the defaults If you want anything but the default values you must specify the information you want to display Financial Reporter evaluates the last line of the above specification and performs the following actions 1 3 Evaluate all accounts in the account number range specified in row 3 to see that they are type B accounts Get the account balance of each account in the range that is a balance sheet account Total the balances because T in column D specifies that a single total line will be generated from the account range rather than one line for each account to list the details Check whether the amount in column F is zero because of the z in column C If itis Financial Reporter does not print the line
121. ional fields that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 A string containing the currency reference A currency reference has the form CCC T where CCC is a 3 character currency code for example CAD for Canadian dollars or USD for US dollars and T is the currency type also known as the currency code qualifier The currency type can be e S Source currency User s Guide 115 Appendix D FR Function Reference Araument 0 Deepon e E Functional equivalent of the source amount e F Functional currency total including functional equivalents of all source amounts e R Reporting currency total includes reporting equivalents of all source amounts Note This option is only available if you select the Euro as your functional currency and specify a different reporting currency in Company Profile Note If no currency is supplied functional home currency is used Fiscal Set Field Names Financial Reporter can retrieve the following balance and net change fields Note In the Reporter Code Suffix n is a number that can refer to the fiscal period from 1 to 13 or to the quarter from 1 to 4 Period balance and net change Field Description Reporter Code Suffix BAL NET balance net changes BALP NETP2 BALLP NETLP Current period Last period BALnPA NETnPA n periods ago 1BALP BALnP BALYTD etc include adjustments from pe
122. kslashes The next row with text contains the headings for columns G H and I These headings are printed as is Note You use formatting commands in Excel to underline these column headings The next row with text will be expanded on the printed report to include all accounts in the range between 6000 and 6999 that meet other selection criteria specified in cells B C and D or entered at print time on the Print Statements screen In the spreadsheet s formula view the row looks like this 12 6000 59994 D FRACCT ACCTDESC N The backslash in the three columns on the right indicates that each cell value will be generated from the default formula set for the column Financial Reporter inserts the account reference in column A into any default formula that requires the account reference parameter For example the default formula for column G is FRAMTA NETP FRAMTA is a Financial Reporter function that prints the account period balances or net amounts from the account fiscal sets In this case the formula specifies the net amount for the current period When Financial Reporter processes this cell of the statement template it expands the formula with the account reference from column A For example FRAMTA NETP 6000 The remaining two columns also use a default formula in this case bringing in the net changes for the quarter to date and the year to date The final row in our example is the to
123. lDown Information User s Guide 31 Chapter 3 Designing Financial Statements Report Options When you select Report Options the first page of the financial statement displays the following information e Statement name e Fiscal year and period e Report type actual or provisional e Account processing and sorting order e Account selection ranges Including DrillDown Information On the G L Print Financial Statements and Statement Designer screens you can select the DrillDown Information option to add drilldown link comments to the generated report For more information see About FR Drilldown page 94 Including Audit Information The Audit Information option is available only when using FR View in the Statement Designer it is not available on the G L Print Financial Statements screen When you select Audit Information the statement includes information from the control information columns of the spreadsheet columns A through D as well as the report columns that are usually printed Including Formulas The Formulas option is available only when using FR View in the Statement Designer it is not available on the G L Print Financial Statements screen Usually when you generate a report from a financial report specification Financial Reporter inserts balances and net amounts from the database into the generated financial report However if you select Formulas Financial Reporter creates a financial report fro
124. ll reference Such as Cell A 1 and formula such as FRACCT appear As you select options this area is updated to display the current formula In the bottom left corner of the screen the function builds as you select options From the Functions list select a function account reference or selection criteria expression When you select a function available options and parameters for the function appear to the right of the list Depending on your selection these may include account numbers account ranges selection criteria currency type and other options Specify options for the function account reference or expression For more information see Financial Reporter Functions page 105 If you are specifying account numbers you can specify individual account numbers or a range of account numbers If you want to filter an account or account range by specific criteria click the Selection Criteria button and then specify criteria to use When you have created the formula paste it into your report or specification a To paste the formula into the selected cell listed in the bottom left of the screen click Paste b To select a different cell click Next Row Prev Row Next Column or Prev Column to navigate to the desired cell and then click Paste The formula you created is pasted into the financial report or specification User s Guide 99 Appendix C Account Reference Syntax and Examples Accoun
125. lt functions and formulas to columns of the spreadsheet and to specify that a range of accounts will be expanded to many rows on a final statement Note You do not need to create a statement specification to extract and print information You can extract and print balances from General Ledger by placing a few formulas in a spreadsheet recalculating the spreadsheet and then printing from Excel You can also save the spreadsheet and then use General Ledger s Print Financial Statements screen to print the values in future If the worksheet does not contain a statement specification Financial Reporter simply recalculates the spreadsheet and prints it out Layout of a Report Specification Spreadsheets that contain statement specifications have two separate regions e The Spec range is the region containing the report specification e The Report range is the region containing the financial statement produced from the report specification The Report range contains a third range called the Print range which excludes all audit information that can be generated in the first columns of the Report range Tip Statements that are not generated from specifications have no Spec range but you can set a print range to restrict the size of the worksheet for printing The statement specification and the report it creates both reside in a single worksheet When you generate a financial statement from a specification the Financial Reporter proces
126. m the specification with a formula for each general ledger balance or net that appears in the final report You can then update that financial report with current database values by recalculating the spreadsheet pressing F9 The most common type of report that is composed only of formulas is one that keeps financial ratios 32 Sage 300 ERP Financial Reporter About Financial Statement Specifications Note Spreadsheets with formulas are much larger than standard report specifications and they are less flexible Financial Reporter automatically refreshes General Ledger data the next time you load the linked spreadsheet in the Statement Designer You can also use the G L Print Financial Statements screen to recalculate and print the report If you want to change the report for example by adding new accounts in the chart you must regenerate the report to incorporate the changes in your statement Tips e Tosave the linked spreadsheet save the file under another name and then remove the report specification from the renamed spreadsheet About Totals and Subtotals Financial statements contain columns of figures that are subtotaled and totaled according to account type The most common arithmetical operation involves summing the balances or net changes of groups of accounts at a moment in time as in the following example Current Balance Last Year Balance Petty cash 274 57 358 00 Bank account CAD dollars 4 372 96 3 665
127. matter where the account segment Standard Account References ECGEMCC RNC RN aaaa ACCT bbbb cccc A MANU aaaa bbbb cccc A B gggg D specify no matter how many segments are in the number and no matter where the account segment is in the account structure This allows you to specify a range for one segment rather than a range of complete account numbers and is particularly useful when the account segment is not the first segment in the account number An individual account number and a range of account numbers The square brackets indicate the account structure codes used by the account numbers A set of ranges and individual accounts with each specification separated by a comma is in the account structure For example results will include 10 4000 20 5000 and 10 6000 if those accounts exist in General Ledger User s Guide 103 Appendix D FR Function Reference Use Financial Reporter functions to retrieve data from General Ledger including transaction and optional field information Financial Reporter Function Notation Financial Reporter functions use the following typographic conventions Convention Description UPPERCASE Function names are in uppercase letters Function arguments are listed in parentheses All arguments are separated by a comma Most argument parameters are text so are either e In quotation marks e Areference to a spreadsheet cell that c
128. mpler way of specifying all accounts with the same segment A range of General Ledger account numbers with a fixed second segment Financial Reporter retrieves information from each account within the range that includes the second segment code A range of General Ledger account segment codes Financial Reporter retrieves all accounts matching the account segment you 102 Sage 300 ERP Financial Reporter Accounts with an account segment value between 100 and 200 and with a Department segment value between A and F for any order other than ACCTID order If the order is ACCTID account 150 H will be included because it falls between 100 A and 200 F However for non ACCTID order Financial Reporter takes only those accounts that fall within both ranges the range of accounts and the range of departments so that 100 A 200 F is the same as 100 200 A F Shown below If the order is ACCTID the constraint is ACCTID gt 100 A and ACCTID lt 200 F 100 0r 100 100 All accounts that start with 100 regardless of account structure 100 200 F Accounts in department F with an account segment value between 100 and 200 and with a department segment code of F 100 200 A F Accounts with an account segment code between 100 and 200 and with a department segment code from A to F 4000 8000 Accounts with an account segment code from 4000 to 8000 no matter how many segments are in the number and no
129. must meet restrictions and criteria specified on the FR View screen The first restriction you place on account selection is with the FR View screen or with the G L Print Financial Statements screen Report level account range filtering is in effect for any account reference that specifies a range of accounts For more information see About Selection Criteria for Filtering Accounts page 62 Overriding Account Ranges You can override FR View screen ranges in two ways e By specifying segment codes that are outside the ranges defined on the FR View screen e By using the prefix in individual account number references Unless you override the FR View screen accounts must both fall within the specified range and meet other criteria specified on the FR View screen If you specify any selection criteria in column B of a default specification line that criteria is in effect unless you override it You can override the criteria on a single line by placing a new criteria expression in column B The criteria expression is in effect only for the line on which you define it If you specify a criteria for a single formula in the line the line criteria is ignored G L Statement Designer To open this screen 1 Open General Ledger gt Financial Reporter gt Statement Designer 2 Click Start Related tasks e Setting the Spec Range page 73 e Printing a Financial Statement page 79 30 Sage 300 ERP Financial Reporter
130. n Value me e 9 Wem E User s Guide 141 Appendix D FR Function Reference FRFISCAL This function returns a date as a string from the fiscal calendar in Common Services Syntax FRFISCAL option fiscal year fiscal period formatted Arguments Araument Deerpon option The period or quarter date Valid parameters are e Start Start date of the period e End End date of the period e QStart Start date of the quarter e QEnd End data ofthe quarter fiscal year Valid parameters are MEPHORQM e YYYY Aspecific 4 digit fiscal year such as 2010 Ln n years ago relative to the current fiscal year for example L1 is last year and L2 is 2 years ago Nn n years in the future relative to the current fiscal year for example N1 is next year and N2 isthe year after next If not specified assumed to be the current year fiscal Fiscal period number from 1 to 12 If not specified the current period is period used Optional T Note This field is an integer so is not enclosed in quotation marks formatted Indicates whether the return value should be formatted according to Optional the presentation defined in the view If omitted the default is Formatted Use one ofthe following constructs e FORMATTED F or FMT e UNFORMATTED U or UNFMT 142 Sage 300 ERP Financial Reporter Financial Reporter Functions Examples Statement Return value FRFISCAL Start
131. n one set of title lines Financial Reporter uses the last set in the specification The Universal Corporation Balance Sheet As of October 31 1999 Petty cash 274 57 Bank account CAD dollars 4 372 96 If you want the title to appear only on the first tab of the report leave the first column for these rows blank The rows are transferred as is to the beginning of the Report range Note If you use the Financial Reporter T line you cannot separately mark a group of cells as title lines Title lines must be consecutive and cannot be split by a tab break 68 Sage 300 ERP Financial Reporter About Formatting Financial Statements Adding Explanatory Comments We suggest you begin every specification with a few comment lines describing it and add comments throughout to explain important sections of your specifications You can make any line of a report specification a comment line by starting column A with two periods For example the following four lines are in cells A1 through A4 of a report specification Specification name INCS1 m This specification file will produce a statement of earnings with one l column of figures showing current year to date results Grid Lines and Other Report Formatting You specify all formatting for financial reports in the Financial Statement Designer s using Microsoft Excel formatting commands For example e To select the character font bold type left or r
132. nancial Reporter can use exchange rates maintained in Sage 300 ERP so you can create financial reports in any currency you maintain using the exchange rates from any fiscal period To retrieve exchange rate information you use the FRRATE function to get exchange rate information For more information and for examples of how to use this function see FRRATE page 147 44 Sage 300 ERP Financial Reporter About Columns in Report Specifications Note The rate returned by the FRRATE function is always used as a multiplier to convert source currency to functional even if you defined the rate as a divisor If the rate is defined in the System Manager as a divisor Financial Reporter returns the reciprocal of the rate About Default Column Specifications Ina financial statement specification default column specifications are defined in the first line and then used with a range of accounts in the last line The following example illustrates default column specifications and specification lines that apply to a range of accounts G H l g JT FRAMTA FRAMTA NETP NETO NETYTD 10 Current Quarter Year 11 LLLI Period To Date ToDate 12 6000 6999 D FRACCT ACCTDESC The line starting with two backslashes in column A contains default column specifications These default column specifications are in effect until you define a new set of defaults To use the currently def
133. ned earnings with NETADJ and print closing figures with NETCLOSE and BALCLOSE e All fiscal field set values are calculated relative to the fiscal period set for the report with the exception of BALOPEN NETADJ and NETCLOSE and fields that reference a specific time period BALOPEN is the opening balance of the account for the year NETADJ is the amount posted to the adjustment period NETCLOSE is the amount posted to the closing period e If there are no records for a future period zero is returned e You can use a fiscal set prefix with NET and BAL to return specific values For example BALP provides the actual balance or provisional balance for supported functions for the current fiscal period ABALP provides the actual balance for the current fiscal period 1BALP provides the balance from budget set 1 for the current fiscal period for functions that can reference budget sets 1BALCLOSE acts like BALP in every period except the last period of the year when it includes the adjustments and the closing entry NETCLOSE provides the closing entry only 2NETADJ provides the amount from period 14 To print the balance from the final fiscal period without the adjustment amount use FRAMT BALP FRAMT NETADJ 140 Sage 300 ERP Financial Reporter Financial Reporter Functions Examples The following examples show the differences between the return values for the FRAMT and FRDEBIT functions Account Type FRAMT Retur
134. ng three separate account number filters e The account number reference in column A of the report line e The account filtering expression in column B if there is one e The print range options for the entire report Normally an account has to satisfy the requirements of all three filters in order to be included in the report However Financial Reporter also provides a mechanism for specifying a report range and for including accounts which lie outside that range The easiest way to add account numbers to a financial statement is using FR Paste For more information see About FR Paste page 96 Specifying Account Structures To restrict account number selection to a particular account structure use a formula such as aaaa ACCT bbbb cccc A MANU which specifies an individual account number and a range of account numbers The square brackets indicate the account structure codes used by the account numbers 60 Sage 300 ERP Financial Reporter Using Formulas to Retrieve General Ledger Account Information Specifying a Segment to Override a Segment Range You can override a segment range specified on the Print Financial Statements screen by including the segment values you want on the line in the column A account number reference Example If you want to compare the performance of each department to the total sales for the organization you can specify the total organization s sales ona line as follows 4000 4999 A 4000 4999 B
135. ng to Optional the presentation defined in the view If omitted the default is Formatted Use one of the following constructs e FORMATTED F or FMT e UNFORMATTED U or UNFMT Field names for G L account data You can use the following G L account field names To retrieve optional fields add the prefix A before the field name include the period 110 Sage 300 ERP Financial Reporter Financial Reporter Functions ACCTTYPE Account type e Ifunformatted I B orR e Ifformatted Income Balance sheet or Retained earnings ACCTBAL Normal balance e Ifunformatted 1 for debit or 2 for normal credit e Ifformatted Debit or Credit ACTIVESW Account status Active or Inactive e Ifunformatted 1 for active or O for inactive e Ifformatted Active or Inactive CONSLDSW Consolidation setting e Ifunformatted 1 if transactions are consolidated on posting 0 if they are not If formatted Consolidate journals or Do not consolidate journals QTYSW Quantity setting e Ifunformatted 1 if quantities are used with account 0 if not e If formatted Yes or No The unit of measure if quantities are used ALLOCSW Allocation setting e If unformatted 1 if account can be reallocated O if not e Ifformatted Yes or No ACCTOFSET Account number for reallocation ACCTSRTY Allocation transaction source code ROLLUPSW Rollup setting User s Guide 111 Appendix D FR Function Reference e If unfo
136. o suppression in statements 45 63 using Z and N 45 186 Sage 300 ERP Financial Reporter
137. od e If there are no records for a future period zero is returned e You can use a fiscal set prefix with NET and BAL to return specific values For example BALP provides the actual balance or provisional balance for supported functions for the current fiscal period ABALP provides the actual balance for the current fiscal period 1BALP provides the balance from budget set 1 for the current fiscal period for functions that can reference budget sets 1BALCLOSE acts like BALP in every period except the last period of the year when it includes the adjustments and the closing entry NETCLOSE provides the closing entry only 2NETADJ provides the amount from period 14 To print the balance from the final fiscal period without the adjustment amount use FRAMT BALP FRAMT NETADJ User s Guide 133 Appendix D FR Function Reference Examples The following examples show the differences between the return values for the FRAMT and FRCREDIT functions Account Type FRAMT FRCREDIT Return Value Return Value 134 Sage 300 ERP Financial Reporter FRDEBIT Syntax Arguments Financial Reporter Functions This function is the same as FRAMT page 114 except that it returns values for only those accounts that have debit amounts balances or net changes regardless of whether the account is a debit or credit account FRDEBIT field name account reference criteria currency FRDEBIT returns the debit value of t
138. olidated total that sums up all values from the retrieved optional fields If the specified field name is not appended by anything or is appended by c a consolidated total is printed 38 Sage 300 ERP Financial Reporter About Columns in Report Specifications About Columns in Report Specifications Most of a financial report specification is a template for the report it produces For example all report formatting such as column widths fonts highlighting and underlining appears on the financial report specification The first four columns of the report specification columns A through D contain important control information Note You can begin a specification in any column as long as the Spec range begins with the same column Sample Report Specification Column A In this example row 1 is a default row To use the default formulas in subsequent rows place a backslash in the appropriate spreadsheet cells A B C D E E 1W FRACCT ACCTDESC FRAMT BALP 2 Account Description Current Balance 3 1020 1400 The contents of column A of each row the first column of the Spec range determine the purpose of all columns in the row There are five possible types of rows in a report specification e Comment row If column A starts with two periods the row is a comment row e As is row If column A is blank the row is part of the report and will appear as is on the final report Default row If column A st
139. ontains a text value ora formula that returns a text value Note Excel will return a VALUE error if you specify an argument that does not exist Example FRACT field name account number FRACCT field name account number criteria format Financial Reporter Functions Accounts in General Ledger G L contain two main types of data e Master information Account name account type and other information regarding the account e Fiscal set values Balances and net amounts for each general ledger account You can use the following functions in Financial Reporter to retrieve data from General Ledger including transaction and optional field information User s Guide 105 Appendix D FR Function Reference FR page 107 G L Option data company data and statement print options FRACCT page 110 Account data including account optional fields FRAMT page 114 FRAMTA Fiscal set data page 121 FRCREDIT page 128 FRDEBIT page 135 FRFISCAL page 142 Fiscal calendar dates FRPOST page 144 Accounts and transaction detail data and account and transaction optional field data from posted transactions FRSDESC page 149 Segment code descriptions FRTRN page 150 FRTRNA Transaction detail data including net totals page 156 FRTRNCR page 161 and account and transaction optional fields FRTRNDR page 166 but not provisional amounts Tip In Excel use FR Paste available
140. option you can use the From Account Group and To Account Group fields to specify a range of sort codes to include or leave the default values in these fields to include all sort codes 80 Sage 300 ERP Financial Reporter G L Print Financial Statements Screen 9 Inthe table specify optional ranges of segments If you selected Separate under Report As in step 5 you can double click the Report As column to select Separate or Consolidated 10 Select a print destination 11 Click Print G L Print Financial Statements Screen Overview To open this screen Open General Ledger gt Financial Reporter gt Print Financial Statements Secondary screens e Print for Financial Statements Screen page 85 Related tasks e Printing a Financial Statement page 79 Use the G L Print Financial Statements screen to print standard or custom financial statements for your general ledger data You can select one of several statement specifications included with General Ledger to print a standard financial statement You can also select a statement specification that you have created and saved using the Financial Statement Designer The Print Financial Statements screen displays a variety of options that you can use to print a report Important Printing using the Statement Designer is not the same as printing using the Print Financial Statements screen For more information see Printing Financial Statements page 77
141. or RQ actual or provisional quantities are returned depending on the type of report The fiscal year suffix can be e YYYY A specific fiscal year such as 2010 Ln n years ago relative to the current fiscal year for example L1 is last year and L2 is 2 years ago Nn years in the future relative to the current fiscal year for example N1 is next year and N2 isthe year after next Note If no suffix is supplied the current fiscal year is used Example For the field name argument ABALP 2008 ABALP is the combination of fiscal set prefix and field name referencing the actual balance for the current period and 2008 is the fiscal year suffix A string specifying a single account or a range of accounts See About Account Number References page 59 An expression that imposes selection criteria on the accounts and account optional fields The account reference and expression together determine the accounts and account optional fields that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 A string containing the currency reference A currency reference has the form CCC T where CCC is a 3 character currency code for example CAD for Canadian dollars or USD for US dollars and Tis the currency type also known as the currency code qualifier The currency type can be User s Guide 129 Appendix D FR Function Reference Araument Deerpon e S Source currency e E
142. or by a formula 2 Checks to see that the account master information matches the selection criteria you have specified You can include selection criteria in column B of a specification to restrict the accounts selected from the Account Code reference specified in column A or in formulas that retrieve account balances net amounts and quantities from posted transactions or net amounts from General Ledger Tips e The easiest way to define selection criteria is to use FR Paste For more information see About FR Paste page 96 e Generally you should not use selection criteria if you can achieve the same result with a more selective account number reference e You can improve report performance by being more precise in your account references to restrict the number of initial account lookups 62 Sage 300 ERP Financial Reporter Using Formulas to Retrieve General Ledger Account Information e You can choose report criteria variables you select from the Preview Financial Reporter screen while you are in a worksheet Financial Reporter recalculates FR commands according to the criteria you have saved For example if you have created two workbooks for different years you can switch between the workbooks without losing the value of FR commands after running FR View Using Selection Criteria in Column B You can use selection criteria in column B of a specification to restrict the accounts selected from the Account Code reference
143. orter e Restrict the range of accounts Use the FR View screen to restrict the range of accounts in the report 22 Sage 300 ERP Financial Reporter Lesson 3 Creating a Financial Report Specification e Specify a range of accounts On one of the rows of your report enter a range of accounts 1000 3000 e Use wildcards to specify criteria for accounts Use the wildcard character to replace one or more characters in an account number or use to replace one or more segments Remember that you previously used to specify all accounts and used column B of the report specification to restrict the range of accounts to a single group Example Try entering 4000 to specify all accounts starting with 4000 no matter how many segments they have or try 4000 100 e Add a new column Add an additional column G to the report To do so use FR Paste to insert a formula in the default row specification Select FRAMT and remove all but the first parameter including quotation marks after pasting the formula in cell G2 Note Remember to type V in the rows of your report specification for example G5 G8 and any other rows you have added e Explore options on the G L Print Financial Statements screen Experiment with selecting different options For example you can Change the fiscal year and period for the report Specify whether to print actual or provisionally posted figures unless one is specifically selected in th
144. osition of data labels varies by Excel version To add or remove data labels or modify their position refer to the help or documentation provided with your installed version of Excel e BalsumO03 xls Balance Sheet Summary for Specified Period A summary of the balance sheet for a specified period e Balsum04 xls Comparative Balance Sheet Summary 88 Sage 300 ERP Financial Reporter Income Statements Current Year Previous Year A comparison of current and previous year amounts and variance by amount and percentage Balsum0O05 xls Comparative Balance Sheet Summary Current Year Budget A comparison of actual and budgeted amounts for the current year and variance by amount and percentage Income Statements General Ledger includes standard income statements and income statement summaries The sample summaries include e Aregular size income statement that summarizes the company s profit or loss for a specified period A common size income statement which includes a regular size income statement and separate reports listing line items as a percentage of sales revenue Common size income statements that also include graphical summaries statements for specified periods comparisons of current and previous year amounts and comparisons of current year and budget amounts Tip You can use Excel formulas and functions to display amounts as percents or ratios Standard Income Statements General ledger includes
145. ou sort by You can also sort by account group using the formula acctgroup or by account sorted group using the formula acctgroupsort Example If your accounts have two segments the account reference 100 120 A B generates two possible account listings depending on the sort order you specify e If accounts are sorted by segment 1 they are listed like this 100 A 100 B 120 A 120 B e If accounts are sorted by segment 2 they are listed like this 100 A 120 A 100 B 52 Sage 300 ERP Financial Reporter Using Formulas to Retrieve General Ledger Account Information Specifying Order at Print Time or in the Default Row Specification The default order for processing accounts is the order specified at print time on the Financial Statements screen or the FR View screen when editing specifications You can also enforce the order of processing accounts in ranges for all or part of a report by specifying the order in the first column of the default row specification immediately after the backslashes You may process account numbers in any of the following orders e ACCTID account number order e NNACCT account segment order e ACCTGROUP account group order e ACSEGVALn account segment n order e Segment desc to list accounts by the segment with description Segment desc For example REGION The order you specify determines e The order in which accounts in expanded account references are prin
146. oup click the Underline button User s Guide 17 Chapter 2 Financial Reporter Tutorial 5 Click the column heading for column F to select the column 6 On the Excel ribbon on the Home tab format the selected cells a inthe Alignment group click the Underline button b Inthe Number group on the Category list select Accounting Note If you are viewing formulas the content of the selected cells does not appear right aligned You can press Ctrl to toggle between viewing formulas and calculated values which appear right aligned Your report specification now includes default row formulas and column headings A B C D E E 1 Thisis a short financial report 2W D FRACCT ACCTDESC FRAMT BALP 3 Account Description Balance Exercise 4 Adding Account Reference Lines to the Statement In this exercise you will add five reference rows to display the following information in the report e Current asset accounts e Total current assets e Current liability accounts e Total current liabilities e Net of assets and liabilities To add reference rows 1 In cell A5 type is a wildcard account reference that instructs Financial Reporter to query all accounts 2 In cell B5 type ACCTGRPCOD 1 This expression restricts the accounts to the account group for Current Assets 18 Sage 300 ERP Financial Reporter 9 10 11 12 13 Lesson 3 Creating a Financial Report Specification Note The acco
147. ow beside FR and then click FR Paste On the FR Paste Function screen select the account description a Onthe Functions list select FRACCT b Inthe Field Name field select ACCTDESC Description 16 Sage 300 ERP Financial Reporter Lesson 3 Creating a Financial Report Specification c Click Paste and then click Close Cell E2 displays this formula FRACCT ACCTDESC Note This formula does not refer to a specific account number Financial Reporter will add account references when it uses this default specification to view the report Select cell F2 On the Add Ins tab of the Excel ribbon click the arrow beside FR and then click FR Paste On the FR Paste Function screen select the current period balance a On the Functions list select FRAMT b Inthe Field Name field select BALP Balance Current Period C Click Paste and then click Close Cell F2 displays this formula FRAMT BALP You have now created the default specification row A B C D E E 1 Thisis a short financial report a D FRACCT ACCTDESC FRAMT BALP Exercise 3 Adding Column Headings To create column headings or any text that you want in the final report simply leave cell A of the row containing the headings blank This row will appear as is on the report 1 2 3 4 In cell E3 type Account Description In cell F3 type Balance Select cells E3 and F3 On the Excel ribbon on the Home tab in the Font gr
148. p To get the best performance from Financial Reporter you should use specific account references and specify account processing orders that complement the references For more information see About Account Order in Financial Statements page 52 Note also that you cannot combine account reference ranges with wildcard characters For example A123 B567 is not allowed However you can combine account references in a list such as 1000 1099 1100 1199 B 1250 A User s Guide 67 Chapter 3 Designing Financial Statements About Formatting Financial Statements All formatting of financial statements is controlled by the Financial Statement Designer s formatting commands For more information about controlling tab size tab headings font size and style refer to the help and documentation for Microsoft Excel The most efficient method for formatting the area and the text within a cell is to create a style that includes various attributes such as bold text currency format right alignment and so on and then apply the style to spreadsheet cells Adding Title Lines The first five lines of the report specification are the title lines which are repeated at the top of each tab of the report You can specify up to five lines that will appear at the top of each tab of a financial statement You can only define one set of title lines in a specification All title lines must be together contiguous If you have defined more tha
149. plied functional home currency is used Fiscal Set Field Names Financial Reporter can retrieve the following net change fields Note In the Reporter Code Suffix n is a number that can refer to the fiscal period from 1 to 13 or to the quarter from 1 to 4 User s Guide 157 Appendix D FR Function Reference Period net change Field Description Reporter Code Suffix NET net changes Quarter net change Field Description Reporter Code Suffix NET net changes Current quarter to end Qo NETQ Current quarter to date NETOTD eC INETP NETnP NET4Q NETPQ will also include adjustments from period 14 if the net is requested for the final period of the year NET works like BAL 2This code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to date figures relative to the current quarter If you are now in period 2 of the current quarter QTD will give you the first 2 periods of any previous quarter 3nQATD lets you go up to four quarters ago If you need to specify more use nQATD L1 158 Sage 300 ERP Financial Reporter Financial Reporter Functions Field Description Reporter Code Suffix NET net changes Preceding quarter 3 periods Po NETPQ Preceding quarter n periods ago PQnPA NETPQnPA Half year net change Field Description Reporter Code Suffix NET net changes Current half year to end Current half year to date STD Last half year to date
150. r User s Guide 1 Introduction e Appendix C Account Reference Syntax and Examples This appendix lists syntax and examples for account references you can use in the G L Statement Designer to retrieve information from General Ledger e Appendix D FR Function Reference This appendix lists the Financial Reporter functions you can use in spreadsheets to retrieve data from General Ledger including transaction and optional field information e Appendix E FR Field Reference This appendix lists the account master fields and transaction fields from which you can retrieve data using FR functions e Appendix F FAQ and Troubleshooting Use this appendix to find information on the error messages you may encounter when using Financial Reporter as well as some suggestions to resolve the most common types of problems 2 Sage 300 ERP Financial Reporter Chapter 1 Financial Reporter Overview Financial Reporter is a powerful reporting tool that you can use to manipulate format graph and print general ledger data You can use Financial Reporter to create financial statements and statement specifications in Microsoft Excel that display data from Sage 300 ERP General Ledger Financial Reporter includes the following screens for working with financial statements e G L Print Financial Statements You use the G L Print Financial Statements screen to print financial statements Note All Sage 300 ERP financial statements are Excel
151. r details on using FR View see FR Menu Commands page 93 FR View generates the report using the same print options that you use to print a report from the Print Financial Statements screen However it provides two additional Include options e Audit information If you select this option Financial Reporter lists the results of the account number references in column A of the report range of the spreadsheet This information allows you to check the numbers of all accounts included in your statement as long as you enter D in column D of the report e Formulas If you select this option Financial Reporter inserts a formula in each cell of the final report where you need to insert General Ledger information You can then update the report at any time by recalculating the spreadsheet Important Spreadsheets with formulas are much larger than standard report specifications Generating statements that include formulas also takes longer because Excel calculates the value of each formula as it generates them After you generate a report you can preview it by clicking File Print in Excel About Using Selection Criteria You can specify selection criteria on a default specification line on individual specification lines and in formulas With all these possibilities for account selection you must be very clear which criteria are actually in effect User s Guide 29 Chapter 3 Designing Financial Statements Important Accounts
152. r work click File gt Save As enter a file name and save the file Remember to save your work often as you work through the lessons in this tutorial Du d Y In cell F1 type FR Coname When you press Enter or select another cell the company name appears in F1 In cell E3 type Account Note If the content of a cell begins with a letter the Statement Designer interprets that content as text However if you want to enter a number such as 1999 you must type 1999 so the program can interpret it correctly In cell F3 type Description In cell G3 type Balance Select cells E3 F3 and G3 On the Home tab of the Excel ribbon in the Font group click the Borders button to add a bottom border User s Guide 9 Chapter 2 Financial Reporter Tutorial vA 10 11 12 13 14 In cell E5 type 1000 Note This is an account number so you must enter it as a text string so the cell contents can be inserted in other formulas and interpreted correctly by Financial Reporter In cell F5 type FRACCT ACCTDESC E5 The cell displays the account description for the account listed in cell E5 In cell G5 type FRAMT BALP E5 The cell displays the current balance for the account listed in cell E5 Press F9 to update the worksheet You have now created the beginning of a statement with the headings for three columns and the information from one General Ledger account Each formula you enter retri
153. racters when specifying account numbers e The underline character is a placeholder for one character e The percent character is a placeholder for one or more characters Example Using Wildcard Characters Account Description number specification A123 sc Specifies all single segment account numbers starting with the characters A123 D_6834 Specifies all single segment accounts sharing the same first character D followed by one character of any value and ending with the same four characters 6834 66 Sage 300 ERP Financial Reporter Using Formulas to Retrieve General Ledger Account Information Note You cannot use the Finder with FR Paste to insert wildcard characters You must type in the wildcard characters yourself 0 is Not a Wildcard Character Although resembles a wildcard character it is used in only two very specific ways e specifies all account numbers in the ledger See the QUIKINC and QUIKBAL statements that come with General Ledger e 6000 specifies all account numbers starting with 6000 no matter how many segments they have This specification has the same effect as 6000 6000 Example In this example account number selection is based solely on the account group specified in column B Financial Reporter will list all Current assets then all Fixed assets and finally all Other assets Unless you process accounts by ACCTGROUP Financial Reporter must scan Ti
154. report for each department Note Before you can select Separate you must select Segment and specify a segment that is not the Account segment To print separate reports you must print by any segment except the account segment In other words if you have three segments in your account numbers Region Department and Account you must sort by the Region segment or the Department segment User s Guide 83 Chapter 4 Printing Financial Statements Tip To print separate reports by Region and Department you choose the Region or Department segment for the Sort by selection then in the segment table choose Separate for the other segment When using the Print Financial Statements screen to print financial statements the report is generated for each account segment code printing each separate report in turn Report Type Financial Reporter allows you to print one of two groups of data e Actual Includes only amounts that have been permanently posted to general ledger accounts e Provisional Includes amounts that have been provisionally posted to general ledger accounts as well as permanently posted amounts This option affects only formulas that are not explicitly set as Actual or Provisional in the statement specification The report specification can also have the following effects at print time e Prefixes used for NET and BAL determine the fiscal set NET and BAL can have a prefix A P Q QA QP 1 2 3 4 or 5 to
155. requested for the final period of the year NET works like BAL 2This code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to date figures relative to the current quarter If you are now in period 2 of the current quarter QTD will give you the first 2 periods of any previous quarter 3nQATD lets you go up to four quarters ago If you need to specify more use nQATD L1 152 Sage 300 ERP Financial Reporter Financial Reporter Functions Field Description Reporter Code Suffix NET net changes Preceding quarter 3 periods Po NETPQ Preceding quarter n periods ago PQnPA NETPQnPA Half year net change Field Description Reporter Code Suffix NET net changes Current half year to end Current half year to date STD Last half year to date nth hal year to date n half years ago nSA n half years ago to date Preceding half year 6 periods gt Preceding half year n periods ago 1pq handles any three periods providing you with revolving quarters The preceding 3 periods include the current period plus the two previous ones 2The NET for a group of periods that crosses a year end boundary will also include the adjustments in period 14 but not include the closing entry otherwise the net would include the zeroing entries in income and expense accounts 3This code allows you to compare the current half year TD to the half year TD of a previous half year by providing you with to
156. riod 14 only if the balance is for the final period of the year BALY includes period 14 The closing entries are not included To include closing entries use BALCLOSE To print the balance from the final fiscal period without the adjustment amount use FRAMT BALP FRAMT NETADJ 2NETP NETnP NET4Q NETPQ will also include adjustments from period 14 if the net is requested for the final period of the year NET works like BAL 116 Sage 300 ERP Financial Reporter Financial Reporter Functions Quarter balance and net change Field Description Reporter Code BAL NET Suffix balance net changes arenae fo foment ecsinganerO nen o feara m Preceding quarter n periods PQn PA BALPQnPA NETPQnPA ago Half year balance and net change Field Description Reporter Code BAL NET Suffix balance net changes 1This code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to date figures relative to the current quarter If you are now in period 2 of the current quarter QTD will give you the first 2 periods of any previous quarter 2nQATD lets you go up to four quarters ago If you need to specify more use nQATD L1 3pQ handles any three periods providing you with revolving quarters The preceding 3 periods include the current period plus the two previous ones The NET for a group of periods that crosses a year end boundary will also include the adjustments in period 14 but
157. rmation see Account Master Fields page 171 Left to Right Evaluation Selection criteria expressions are evaluated strictly from left to right unless brackets are added In other words the above example will be evaluated as ACCTTYPE I AND QTYSW Yes OR ACCTDESC LIKE sale The expression will select all accounts that contain the word sale in their description and it will select all income statement accounts that also store quantities 64 Sage 300 ERP Financial Reporter Using Formulas to Retrieve General Ledger Account Information Note Criteria are case sensitive so a description with Sale will be excluded Brackets You may use brackets to change the order of evaluation For example ACCTTYPE I AND QTYSW Yes OR ACCTDESC LIKE sale will select income statement accounts that store quantities or that contain the word sale in their description All the relational operators work with all the field types except for switches like QTYSW where only the and not equal operators apply Spaces You use spaces to separate field names operators and constants in expressions For this reason if you are comparing a string that includes spaces you must enclose the entire string in quotation marks Quotation Marks LIKE Remember that criteria are text parameters If you place criteria in formulas you must enclose criteria text in quotation marks If you pa
158. rmatted 1 if account is a rollup account 0 if not e If formatted Yes or No MCSW Multicurrency setting e Ifunformatted 1 if multicurrency O if not e Ifformatted Yes or No SPECSW Specific source currencies setting e Ifunformatted 0 if all currencies 1 if specified e Ifformatted All currency or Specific currency CTRLACCTSW Control account setting e Ifunformatted 1 if account is a control account O if not e Ifformatted Control account or Non control account Reserved field example AR or AP ACSEGVALn n 01 Account segment code 01 to 10 to 10 112 Sage 300 ERP Financial Reporter Financial Reporter Functions Examples FRACCT Cost of goods sold ACCTDESC 1000 333 FRACCT QTYSW 1000 333 F FRACCT ACCTTYPE Income 1000 333 FMT FRACCT Sales A ACCTCLASS 4000 FRACCT Prints a description that includes only those words that ACCTDESC 1000 are common to each account in the account range 333 1000 400 For example if the accounts in the range have the following descriptions the word Receivable would be the return value e Accounts Receivable customers e Accounts Receivable employees e Receivable over 90 days User s Guide 113 Appendix D FR Function Reference FRAMT This function retrieves actual budget and quantity balances and net amounts from General Ledger accounts for a specified period All amounts returned by FRAMT
159. s page 93 About FR Paste When you use the Statement Designer to open a Microsoft Excel worksheet an Add Ins tab appears on the Excel ribbon On this tab the FR menu displays commands you can use to create and edit financial reports and specifications The FR Paste command appears on this menu You use it to paste formulas into spreadsheet cells to retrieve and display the following General Ledger data e Account numbers For more information see About Account Number References page 59 e Account selection criteria query by example expressions For more information see About Selection Criteria for Filtering Accounts page 62 e Financial Reporter functions For more information see Financial Reporter Functions page 105 e Rolled up amounts Note On the FR Paste Function screen use the Rolled Up Amounts option to specify whether or not to paste rolled up balances or quantities into reports for rollup accounts This option is available for FRAMT FRAMTA FRCREDIT and FRDEBIT only Take care when creating the rollup group to avoid ending up with doubled amounts in the final report 96 Sage 300 ERP Financial Reporter About FR Paste Select the Rollup Members Account option to include balances or quantities for rollup member accounts whose balances or quantities are also being rolled up to their rollup account in the same paste function in the report For information about other menu commands in the
160. s with a value of zero User s Guide 63 Chapter 3 Designing Financial Statements If you do not want to suppress zeros on an individual line but you specified Z in the default specification type N in column C of the line you want printed e Using logical value formulas to include and exclude lines You can place a logical value formula TRUE FALSE in column C If the formula in column C returns TRUE Financial Reporter does not print the line If the formula returns FALSE the line is printed For example G12 F12 10 will check if the value in column F is less than 10 of the value in column G If the statement is true the line will be omitted from the report Note Anything that appears in column C on a default specification line will be used by all subsequent lines unless you specifically override it About Expression Syntax for Selection Criteria The easiest way to create a selection criteria expression is to use FR Paste For more information see About FR Paste page 96 You can also enter expressions directly into spreadsheet cells A simple criteria expression looks like this ACCTTYPE I A more complex one looks like this ACCTTYPE I AND QTYSW Yes OR ACCTDESC LIKE sale ACCTTYPE and QTYSW quantities switch are fields in the account master file All other fields in this file are listed in the next section Selection criteria use account master fields only For more info
161. ses all the specification information in the Spec range and creates the final report in the Report range User s Guide 27 Chapter 3 Designing Financial Statements If you opt to create a separate report for each account segment when you generate the report Financial Reporter inserts a separate worksheet for each segment into a new workbook Rules for Defining the Spec Range Report Range and Print Range The following principles apply to the ranges in a financial statement specification e The Spec range can occupy any columns A specification can begin in any column of a spreadsheet The only requirement is that the first column of the Spec range must be the first column of the specification For more information about the Spec range see About the Spec Range page 71 and Setting the Spec Range page 73 e The Report range is created when generating the report The Report range is generated from the Spec range when you print the financial report It contains data that is generated by the report as well as data extracted from the General Ledger e The Report range starts in column E The first four columns of the Spec range A through D are reserved for control information for Financial Reporter All other columns form a template that determines the appearance of the report and the kinds of data that will appear e The Print range starts in column E The control information in the first four columns of the specification is
162. sing a number of powerful features The first set of criteria you can use is in the report specification itself e You can restrict the accounts specified on a financial report by using selection criteria For example you can select only those accounts in a specified range that have ACCTTYPE z Income AND ACTIVESW Yes Alternatively you can select only those accounts with a debit or credit balance e You can exclude report lines based on their contents For example you could remove a line from your reports if the value in each column on the line is zero or you could exclude a line if it represents less than 5 of the total of column G e You can use the FRPOST FRTRN FRTRNA FRTRNDR FRTRNCR commands to select by posted fields For more information see Financial Reporter Functions page 105 Consolidating Accounts Financial Reporter lets you specify whether particular accounts or groups of accounts are listed separately consolidated or subtotaled whenever the value of a particular account segment changes 26 Sage 300 ERP Financial Reporter About Financial Statement Specifications About Financial Statement Specifications A financial statement specification is a structured description of a statement you want to produce Financial Reporter creates a report based on the statement specification and the options you select when you print or view the final statement Statement specifications allow you to assign defau
163. specified in column A Defining a default expression for column B is similar to defining other default report columns you place the default selection criteria expression in column B of a default specification line If you enter criteria on a default line it is used for all subsequent lines that do not have a criteria defined In other words if you do not want the default expression to apply to a line you must override it on the line Note You do not need to type on lines for which you want to use the default selection criteria Using Selection Criteria in Formulas You can also use selection criteria in formulas that use FRAMT FRAMTA FRCREDIT FRDEBIT FRTRN FRTRNA FRTRNCR and FRTRNDR functions to retrieve account balances net amounts and quantities from posted transactions or net amounts from General Ledger If you paste an expression into a formula the selection criteria acts on the formula only If you paste the expression into column B of a statement line the selection criteria further refines the account number reference specified for the current line About Excluding Lines from Reports You can specify conditions to exclude generated lines from a report based on the contents of the line You define the criteria for excluding a report line by adding a formula to column C of the line e Suppressing lines with zero values To specify zero line suppression you type Z in column C Financial Reporter suppresses all line
164. statements 114 121 128 135 User s Guide 181 Index C C column in statements 40 calculations in statements 44 cell formulas in statements 45 51 column A in statements 39 42 69 column B in statements 40 column C in statements 40 column D in statements 40 55 column specifications in statements 39 44 45 column A 39 column B 40 column C 40 column D 40 column E 42 default information for 47 sample defaults 48 column totals in statements 33 columns hiding on statements 69 comments in statements 69 consolidating on FR reports account ranges 55 account segments 41 multiple statements 74 optional field amounts 38 transaction details 40 creating two column financial reports 16 currency exchange rates 44 custom fields See optional fields D D column in statements 40 55 182 Sage 300 ERP Financial Reporter dates in statements 142 default column specifications 45 48 default lines overriding 30 design of statements 27 drilldown 31 error messages 178 Excel and the Statement Designer 3 printing statements 3 30 setting up 30 expression syntax brackets 65 LIKE 65 optional fields 66 quotation marks 65 spaces 65 F fields retrieved in statements 114 G L account masters 171 filtering accounts in statements 62 64 financial analysis reports 87 See also financial statements financial report specification tutorial on creating 15 Financial Reporter overview 3 30 troubleshooting 175
165. ste criteria in column B you will notice that FR Paste inserts them as formulas containing text For example ACCTTYPE I AND QTYSW Yes FR Paste adds an extra pair of quotation marks around text values to accommodate spaces within the text strings If you paste a formula with criteria it looks like this ERACCT ACCTDESCTM T ACCEL TYPE pm If this is a default line column specification the middle parameter in the formula will take on the account reference specified in column A The LIKE operator is similar to the operator except the second operand can contain the wild cards and User s Guide 65 Chapter 3 Designing Financial Statements e matches any group of characters e matches any single character SO ACCTDESC LIKE sale is true for any accounts with descriptions that include the word sale This would include Product B sales as well as Cost of sales Optional Fields Selection criteria can also include optional fields For example the following expression restricts the results to accounts with account optional field ACCTCLASS Sales A ACCTCLASS Sales And the following expression restricts the results to transactions with transaction optional field QUANTITY lt 0 T QUANTITY lt 0 For more information see About Optional Fields in Financial Reports page 38 About Wildcard Characters in Account Numbers You can use two wildcard cha
166. t number references can use the following symbols Account Reference Symbols Pypliin An account segment separator defined on the G L Options screen in General c EE SR The hyphen is the default separator Colon Separates account numbersin arange 0 account numbers in a range Tilde Separates segment codes in a segment range Note All examples assume that segment 1 is the account segment and segment 2 is the department segment Standard Account References Standard Account References EGENT AN aaaaaaaa A specific General Ledger or account number composed Account 100 department C 823273333 of one or two segments Account numbers can be entered with or without the segment separator However if you are specifying ranges of accounts we suggest you include separators to remove ambiguity from your specifications A range of General Ledger 100 200 accounts and departments Accounts between 100 and 200 When creating a search from any department statement from the e 100 A 200 F information you enter User s Guide 101 Appendix C Account Reference Syntax and Examples aaaa aaaa aaaa bbbb A aaaa bbbb meepen Emp Financial Reporter adds Zs to the last number in the range In other words it searches for account numbers between aaaa A and bbbb BZZZZZZZZZZZZZZZ A range of General Ledger account numbers The wildcard notation is a si
167. t of the currency exchange rate from euro member currency to non euro member currency you want the function to provide e RF returns the floating portion value of the exchange rate the portion not fixed between the euro and non euro member currencies RF returns a 1 if the currencies are euro member and the year is greater or equal to 1999 RC returns the composite exchange rate the effective rate between a non euro currency and a euro member currency This is the default if no value is specified User s Guide 147 Appendix D FR Function Reference Araument Deerpon Note e Fornon euro currencies RC will look up the rate in the Currency Rate table If the source and functional currencies are both euro member currencies RC will return the rate from the Euro Conversion Rates table If the functional currency is the euro and the source is not a euro member currency RC will return the effective exchange rate between the two currencies Examples Some of the following example use the spreadsheet function DATEVALUE This function converts a textual representation of a date such as the date string returned by FRFISCA to a serial number which FRRATE accepts Return Value FRRATE USD CAD AV FRRATE USD CAD SP 75 DATE 2009 10 31 FRRATE USD CAD SP DATEVALUE FRFISCAL Start FRRATE ATS EUR AV DATEVALUE FRFISCAL Start RC
168. tal line SUM G12 SUM H12 SUM I12 Notice also that the total formula for each column refers to a single cell or list of cells in the specification range In our example the single cell in the specification range formula will be replaced in the report range formula by a User s Guide 43 Chapter 3 Designing Financial Statements range reference that contains as many rows as there are accounts in the account range specified on line 12 About Calculated Columns Calculated columns contain figures resulting from calculations on data in other columns of the statement To define a calculated column use standard spreadsheet formulas in the default column specification row For example Current Last Year Curr LYear Balance S E Pettycash Pettycash 274 57 358 of 077 Bank account US dollars 8 437 54 5 932 90 Bank account CAD dollars 4 372 96 3 665 39 In this statement the default formula for the last column column H ona report specification is F1 G1 The formula states that this column contains the value of column F divided by column G In SESERSERI ea a ee Pee T Ww FRACCT ACCTDESC FRAMT BALP FRAMT BALP L1 F1 G1 2 Current Last Year Curr i3 Balance Balance LYear 1100 1300 X X 5 Total SUM F4 SUM G4 F5 G5 When the report is generated Financial Reporter adjusts the relative cell references on each line Using Currency Exchange Rates in Column Calculations Fi
169. tatement Specifications sssuuuusssssssssssssssssssss sss sss sss s rne llz 27 Layout of a Report Specification 00 2000 c cece ccc c cence cece cceeeceeeeeceeeeees 27 Rules for Defining the Spec Range Report Range and Print Range 28 About Generating Reports from Specifications _ 0 2 20 22000 c cece eee cece ec eeee cece 28 G L Statement Designer 2 20 2200 c cece cece cece cece c cence cece e cece eeeeeeeeeeeeeeeeeeees 30 About Totals and Subtotals 2200 00 ee cece eee se s ee cee ee e see le le lese rns 33 About Rounding on Balance Sheets suuusuuusussssssssssesssessses esses es sene se ne 34 About Verifying Account Data sssssssssssssssssssssssslsllsss cence ec eeceeeeeeeeeeeeeeeeeee 37 About Optional Fields in Financial Reports 0 0 2 cc cece ccc cc cee ceccccceeeeceeeeees 38 About Columns in Report Specifications 0 loc e cece cece cecececcccceeececeeeeeeees 39 Sample Report Specification 2 0 2 00 c cece ccc cee c cece eee ee eee eeeeeeeeeeeeseeees 39 COMI UN te feo se SI O sot als ede etes tan desee deme dale ee Tun d to Ae ado 39 Golurnns B C and D eese ERR uve E ple esset 40 Colum PRSETER A MORTE 42 About Calculated Columns ao ne ener Rei e ep coil i nece mi Pici 44 About Default Column Specifications 2 0 00 02 c cece ence esses s rrr esr llli 45 Sample Default Column Spec
170. te Sage 300 ERP data Note Financial Reporter relies on the capabilities of Microsoft Excel to create financial statements using Sage 300 ERP data For more information about using Excel refer to the Excel help or documentation The Financial Statement Designer can interpret a report specification a set of instructions for creating a report and generate a financial report When you open the Financial Statement Designer Sage 300 ERP opens an Excel spreadsheet using a macro that adds Financial Reporter commands and functions For more information see G L Statement Designer page 30 User s Guide 7 Chapter 2 Financial Reporter Tutorial About Report Specification Files A report specification file is a spreadsheet with specific commands and functions that Financial Reporter uses to produce a financial statement For example it can specify the following information e Data to include in each column of a financial statement e Which accounts will be reported in each row or group of row e Print layout for the statement Statement specifications are generalized so the same specification file can be used over and over again to produce financial statements for different departments and different time periods For example a single specification file can print balance sheets for any department in your company as of the last day of any fiscal period in your fiscal year For more information see Financial Reporter Overview page 3
171. ted e The ease and consequently the speed with which the accounts can be retrieved by Financial Reporter In the following example account numbers have two segments a department segment and an account segment The department segment is first not recommended and there are only two department codes in the ledger but a large number of accounts The first account segment in the ledger is 1000 If Financial Reporter processes accounts in order by the account segment it will only have to process accounts 1000 through 1100 specified like this ERN NNNM WENN pwcer The accounts will appear in the following order on the report by account segment then by department User s Guide 53 Chapter 3 Designing Financial Statements e A 1000 e B 1000 e A 1100 e B 1100 If on the other hand Financial Reporter processes accounts in account number order which lists the department segment first it will have to scan each account in the ledger to see if it matches the range eT a wem The final report will also be in account number order which in this case will be in department order for the indicated range e A 1000 e A 1100 e B 1000 e B 1100 The order of segments in an account number is defined by the account s structure code not by the order of segments shown on the G L Options screen in General Ledger Consequently if you have different structures that have different first segments ACCTID order
172. teria expression with any of the fields in the general ledger account master listed below Many values can be expressed in more than one way For example the quantity switch can be 1 or Yes if the account keeps quantities or 0 or No if it does not A criteria expression for this field can look like either of the following e OTYSW 1 e OTYSW Yes FR Paste always supplies the text value of G L account master fields Yes in the above example If you want to see the non text values and the text values of these fields see the Object Information for the GLACCOUNT view in the Macro Editor Note If a field s value is text you must enclose field values in quotation marks If the text contains a space it is best to enclose values in a second set of quotation marks ACCTDESC Accounts Receivable To retrieve account optional fields add the prefix A before a field name for example A ACCTID Be sure to include the period after the prefix The following table shows G L account data field names Field Contents ACCTID Account number including all segments CREATEDATE Date the account was created Dates are specified as yyyymmdadd ACCTDESC Account description User s Guide 171 Appendix E FR Field Reference mame 0 Fe comtens 0000 sheet or retained earnings account CONSLDSW Do not consolidate journals or 0 if transactions are not consolidated Consolidate journals or 1 if transactions ar
173. th revolving half years The preceding six periods includes the current period plus the five previous ones User s Guide 169 Appendix D FR Function Reference Total year net change Field Description Reporter Code Suffix NET net changes Preceding year 12 or 13 periods NETPY C Note e You can print year end adjustments and transactions to close income accounts to retained earnings with NETADJ and print closing figures with NETCLOSE and BALCLOSE e All fiscal field set values are calculated relative to the fiscal period set for the report with the exception of NETADJ and NETCLOSE and fields that reference a specific time period NETADJ is the amount posted to the adjustment period NETCLOSE is the amount posted to the closing period e If there are no records for a future period zero is returned e You can use a fiscal set prefix with NET to return specific values For example NETP provides the net amount for the current fiscal period ANETP provides the actual net change for the current fiscal period INETCLOSE provides the closing entry only 2NETADJ provides the amount from period 14 170 Sage 300 ERP Financial Reporter Appendix E FR Field Reference Use selection criteria expressions with account master fields and account optional fields defined in G L Setup and with transaction fields and transaction optional fields defined using FRPOST Account Master Fields You can use a selection cri
174. the default position of data labels varies by Excel version To add or remove data labels or modify their position refer e Incsum03 xls Income Statement Summary for Specified Period A summary of the income statement for a specified period e IncsumO04 xls Comparative Income Statement Summary Current Year Previous Year A comparison of current and previous year amounts and variance by amount and percentage e IncsumO05 xls Comparative Income Statement Summary Current Year Budget A comparison of actual and budgeted amounts for the current year and variance by amount and percentage 90 Sage 300 ERP Financial Reporter Financial Analysis Statements Financial Analysis Statements Analysis statements display financial ratios used by financial managers to evaluate a company s performance Financial Ratios For some of the following sample statements you must enter financial ratios for a benchmark company an average company in your industry on the Benchmarks worksheet General ledger includes the following sample statements FinratioO1 xls Financial Ratios A summary of the financial ratios for the current period grouped into the following categories Profitability Asset Utilization Liquidity Debt Utilization and Growth Finratio02 xls Financial Ratios and Benchmarks A comparison of the company s financial ratios to the benchmark and the variance between the ratios Finratio03 xls Financial Ratios an
175. the following sample statements QuikInci xls Standard Income Statement single column QuikInc2 xls Comparative Income Statement Current Year to Date Last Year to Date QuikInc3 xls Comparative Income Statement Current Year to Budget QuikInc4 xls Comparative Income Statement Current Year with Drilldown User s Guide 89 Appendix A Sample Financial Statements Use this statement to drill down to an originating transaction For more information see About FR Drilldown page 94 Income Statement Summaries Income statement summaries display each account as a percentage of sales revenue allowing financial managers to see how the company s profit is affected by income statement components General ledger includes the following sample statements e IncsumO01 xls Common Size Income Statement A summary as amounts and as a percentage of sales revenue printed on separate pages e IncsumO02 xls Income Statement Summary with Graphs A summary as amounts and a graph as percentages of total revenue Important Financial Reporter does not generate this report Rather the report uses data from fixed cells in the Excel spreadsheet and may not print the correct information if the report is generated with the Include Report Options setting If you are printing the report from the G L Print Financial Statements screen in Sage 300 ERP make sure that Include Report Options is not selected Note For Excel based charts
176. tion in this document at any time without notice to you or others Sage End User License http na sage com sage na eula Contents Hine ges UCHOM MR RR 1 Chapter 1 Financial Reporter Overview aeann 3 Key Financial Reporter Concepts 200 2000 2 cece cece ccc cc ccc 000a Aaa 00 Aaaa o arara 4 Producing Financial Statements 00 00 020 0o ccc eee ec eeeceecceeec eee e eee sse se sse serere sns 5 Designing the Chart of Accounts 0 2 00 0 0 cece cece ccc cece cece cece cece eee eceeeeeeeeeeeeeeeeeaees 5 Creating a Financial Statement Specification 00 200 c cece ccc cece eee eee eee eee 6 Printing Financial Statements 22 20200 202 cece cence eee ence eee ee cece ee eeeeeeeeeeees 6 Chapter 2 Financial Reporter Tutorial 0000an 7 About the Statement Designer susussssssssssssssssssssssslsslssl se lll e cece ee eeeeeeeeeeeees 7 About Report Specification Files 2 2 0 0 200 220 occ cece cece cece cece cece cece ee ceeeceecceeseeeseees 8 Lesson 1 Using Formulas to Retrieve G L Data 200 22 eee eee eee cece eee ees 8 Exercise 1 Adding Basic Account Information 2 22 00 220 2 c cee e eee cece ee eec eee 9 Exercise 2 Adding Column Totals and Formatting Columns 2 222 222 22 11 Lesson 2 Using FR Paste to Insert Formulas 20 0 2000 2 220222 cec ccc eececececeeeeeeeeeeeees 12 Exercise 1 Applying the FRACCT Function
177. tion to the Financial Statement Designer or you can print the report to a file or copy it to a word processor then add whatever embellishments you want in the report file before printing a final report About the Spec Range The Spec range is a named range of spreadsheet cells that begins with the first row and column of the report specification and includes all cells that are part of the specification Before you can generate a report from a report specification you must define the Spec range for your spreadsheet User s Guide 71 Chapter 3 Designing Financial Statements Note The Spec range is already defined in all financial report specifications that come with Financial Reporter To check for a Spec range 1 On Excel s ribbon click Formulas gt Name Manager 2 If Spec is not listed in the Name Manager window click New and then define the Spec range Layout of the Spec Range Columns with control information Columns that form the statement template A D E H A B Lex e OR E F G H Specification name Quikbal2 This specification file will produce a balance sheet with columns for the current year balance and the previous year balance as atthe current fiscal period Sample Company Limited Balance Sheet August 31 2018 and August 31 2018 O NONA UON 10 Unaudited 12 ASSETS 13 WACCTGROUP Zz D ACCT 0 00 0 00 15 Current Previous 16 Year Year 17 Current assets 18 ACCTGRPCOD 01 OR
178. transactions to close income accounts to retained earnings with NETADJ and print closing figures with NETCLOSE and BALCLOSE e All fiscal field set values are calculated relative to the fiscal period set for the report with the exception of NETADJ and NETCLOSE and fields that reference a specific time period NETADJ is the amount posted to the adjustment period NETCLOSE is the amount posted to the closing period e If there are no records for a future period zero is returned e You can use a fiscal set prefix with NET to return specific values For example NETP provides the net amount for the current fiscal period ANETP provides the actual net change for the current fiscal period INETCLOSE provides the closing entry only 2NETADJ provides the amount from period 14 User s Guide 165 Appendix D FR Function Reference FRTRNDR This function is similar to FRDEBIT page 135 and retrieves net totals debit amounts only from transactions It retrieves actual values and summary totals by both account and transaction fields and allows drill down to associated transactions Note This function cannot retrieve budget or provisional amounts calculate balances or include rollup amounts or totals Syntax FRTRNDR field name account reference criteria currency Tip Use the criteria argument to restrict this function to debit DR transactions Arguments Araument Deerpon field name The name of a field from t
179. ts or you can customize them Information Included On Financial Statements Financial statements can include as many columns of data as you need and can contain the following account and other information Current or historical balances for a single account or range of accounts Net changes for a fiscal period quarter half year or year Balances or net changes in functional currency or any of the source currencies defined in a multicurrency ledger Balances or net changes in the reporting currency if you specified the euro as your functional currency and selected a reporting currency in the Company Profile Comparative figures from complete or to date time periods Budget figures from fiscal sets Quantities Company information such as the name address telephone number and company contact The start and end dates of a given fiscal period User s Guide 25 Chapter 3 Designing Financial Statements e The exchange rate given a particular currency rate type functional currency and date You can select a variety of options for printing a financial statement including e The fiscal year and period e Whether to print separate reports for each individual segment or account group or a single consolidated report e Whether to include provisionally posted transactions and rollup amounts Data Selection Criteria Financial Reporter not only gives you access to data it allows you to select data at print time u
180. ts easier to create and more flexible but you do not have to use them You can view an account balance 4 Sage 300 ERP Financial Reporter Producing Financial Statements just by inserting a formula in a single cell and then recalculating the worksheet It is our assumption that most of the statements you create will be generated from statement specifications For more information about financial statement specifications see About Financial Statement Specifications page 27 e Flexible Data Selection Financial Reporter lets you choose and restrict data at print time by account segment range account group fiscal period and by fiscal set actual or provisional amounts Note When designing statements keep in mind that a generalized financial statement can be printed with the actual or provisionally posted amounts of any particular department or division However the transaction related commands FRTRN FRTRNA FRTRNDR and FRTRNCR do not handle provisional amounts If you use these commands only actual amounts are included in the report If you include rollup accounts be sure you designed the rollup group in such a way that you do not end up with doubled amounts in the report When creating a financial report you can enter formulas that allow you to drill down to associated transactions in the resulting report Producing Financial Statements Producing financial statements from General Ledger data involves three broad
181. ts screen 81 grid lines 69 H headers in statements 70 hiding rows and columns 69 I Income Statement summaries 90 invalid account reference in cell 178 invalid detail level in cell 179 invalid formula or expression in cell 179 invalid selection criteria in cell 178 invalid specification line type in cell 179 invalid zero suppression formula in cell 178 L line criteria overriding in statements 28 long date formats 177 N net amounts in statements 114 121 128 135 O optional fields printing on FRreports 38 retrieving for FRreports 38 ordering in statements 52 59 61 66 184 Sage 300 ERP Financial Reporter overriding account ranges 30 overriding default lines 30 overriding line criteria 30 P page layout in statements 70 headers and footers 70 page breaks 70 page numbers 71 pasting functions 98 period dates in statements 142 print destinations for Financial Reporter statements 83 Print Financial Statements screen using 79 printing consolidating optional field amounts on FRreports 38 optional field values on FR reports 38 ranges in statements 28 73 statements 31 79 82 84 85 processing order in statements 52 59 61 66 R ranges in statements 52 59 61 66 report columns 39 report generation 28 report range in statements 28 73 report specifications 3 account information 52 55 59 66 columns and rows 39 69 designing 27 28 30 51 73 formatting 68 70 functions used in 98 105
182. uffix can be e YYYY A specific fiscal year such as 2010 Ln n years ago relative to the current fiscal year for example L1 is last year and L2 is 2 years ago Nn n years in the future relative to the current fiscal year for example N1 is next year and N2 is the year after next Note If no suffix is supplied the current fiscal year is used Example For the field name argument ABALP 2008 ABALP is the combination of fiscal set prefix and field name referencing the actual balance for the current period and 2008 is the fiscal year suffix account A string specifying a single account or a range of accounts See About reference Account Number References page 59 criteria An expression that imposes selection criteria on the accounts and Optional account optional fields The account reference and expression together determine the accounts and account optional fields that will be included in the calculation See About Selection Criteria for Filtering Accounts page 62 currency A string containing the currency reference A currency reference has Optional the form CCC T where CCC is a 3 character currency code for example CAD for Canadian dollars or USD for US dollars and T is the currency type also known as the currency code qualifier 136 Sage 300 ERP Financial Reporter Financial Reporter Functions wgumet Deaserigtion O The currency type can be S Source currency E Functional equivalent o
183. unctional currency e F Functional currency total including functional equivalents of all source amounts e R Reporting currency total If the currency code is functional R includes reporting equivalents of all source amounts If the currency code is not functional R provides equivalent amounts in the reporting currency Note If no currency is supplied functional home currency is used Fiscal Set Field Names Financial Reporter can retrieve the following net change fields Note In the Reporter Code Suffix n is a number that can refer to the fiscal period from 1 to 13 or to the quarter from 1 to 4 162 Sage 300 ERP Financial Reporter Financial Reporter Functions Period net change Field Description Reporter Code Suffix NET net changes Quarter net change Field Description Reporter Code Suffix NET net changes Current quarter to end le NETQ Current quarter to date NETQTD mame NG INETP NETnP NET4Q NETPQ will also include adjustments from period 14 if the net is requested for the final period of the year NET works like BAL 2This code allows you to compare the current QTD to the QTD of a previous quarter by providing you with to date figures relative to the current quarter If you are now in period 2 of the current quarter QTD will give you the first 2 periods of any previous quarter 3nQATD lets you go up to four quarters ago If you need to specify more use nQATD L1 User s Gui
184. unt group code for Current Assets is 1 in sample data but may be different for your company In cell E5 type A backslash instructs Financial Reporter to use the current default formula for this column with the account number specified in column A subject to the criteria specified in column B In cell F5 type In cell E6 type Total Current Assets In cell F6 type SUM F5 This instructs Financial Reporter to expand cell F5 on the report to include as many rows as there are Current Assets accounts This cell reference F5 will refer to all of the generated rows In cell A8 type 969 In cell B8 type ACCTGRPCOD 5 This expression restricts the accounts to the account group for Current Liabilities Note The account group code for Current Assets is 5 in sample data but may be different for your company In cells E8 and F8 type In cell E9 type Total Current Liabilities In cell F9 type SUM F8 In cell E11 type Assets Minus Liabilities In cell F11 type F6 F9 Your report specification now includes five reference rows User s Guide 19 Chapter 2 Financial Reporter Tutorial A B 1 This is a short financial report 2M 3 4 5 9696 ACCTGRPCOD 1 6 7 8 9696 ACCTGRPCOD 5 D Cc D E D FRACCT ACCTDESC Account Description Total Current Assets Total Current Liabilities Assets Minus Liabilities Exercise 5 Setting the Spec Range for the Statement F FRAMT BA
185. using the Statement Designer you can print it at any time using the Print Financial Statements screen or using the FR View command from the FR menu in Excel Note There are some differences when printing a statement using the Print Financial Statements screen compared with using the Statement Designer Key Financial Reporter Concepts Financial Reporter is based upon a few simple concepts e Excel Worksheets with Financial Reporter functions Financial statements are Microsoft Excel spreadsheets that use special functions to extract data from the Sage 300 ERP company database You can place Financial Reporter functions anywhere in a spreadsheet The spreadsheet program looks up the General Ledger values when it recalculates the spreadsheet For information on Financial Reporter functions see Financial Reporter Functions page 105 You can also use any other spreadsheet functions you want to further manipulate that data e Financial Statement Specifications A financial statement specification is a structured description of a statement you want to produce Financial Reporter creates a report based on the statement specification and the options you select when you print or view the final statement Statement specifications allow you to assign default functions and formulas to columns of the spreadsheet and to specify whether a range of accounts will be expanded to many rows on a final statement Statement specifications make statemen
186. you are consolidating statements d From the Before sheet list select move to end e Select the Create a copy option f Click OK Rename each worksheet for the company it represents saving the workbook frequently To rename the worksheet right click the worksheet tab and then click Rename When you have created one worksheet for each company click the sheet for the company you opened in Sage 300 ERP and then use FR View to generate the financial statement For more information about FR View see FR Menu Commands page 93 Right click the tab for the first sheet in the workbook click Insert and then click Worksheet insert a blank worksheet which you will use to create the consolidated statement Right click the new worksheet click Rename and then type a name for the worksheet Generate and save statements for each additional company you want to consolidate a In Sage 300 ERP open the company b Open General Ledger gt Financial Reporter gt Statement Designer and then click Start c Use FR View to generate the statement using the same options you specified for the first company d Save the workbook and close the Statement Designer User s Guide 75 Chapter 3 Designing Financial Statements 12 13 14 15 16 17 Open the Consol worksheet you created and then open a new window for each worksheet arranging them so they are all in view and their contents are aligned a On

Sage 300 ERP 2014 Financial Reporter User's Guide

Contents

Download Pdf Manuals

Related Search

Related Contents