Lesson 1: Using Formulas to Retrieve G/L Data

Sage 300 ERP

Home > Financial Reporter > Financial Reporter Tutorial > Lesson 1:  Using Formulas to Retrieve G/L Data

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.

  1. Sign in to a sample company database.  More...

    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.

    1. Open Sage 300 ERP.
    2. Select SAMINC or SAMLTD.
    3. Enter ADMIN in the User ID and Password fields, and then click Open.
  2. Open the Statement Designer. More...

    1. Open General Ledger > Financial Reporter > Statement Designer.

    2. On the screen that appears, click Start to open a new Excel spreadsheet.
  3. Turn off spreadsheet recalculation in Excel. More...

    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.

    1. Click File > Options.
    2. On the Formulas tab, in Calculation options, select Manual.
    3. Click OK.

    Note: To update the worksheet with information from Sage 300 ERP, press the F9 key.

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 your work, click File > 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.

  1. In cell F1, type =FR("Coname").

    When you press Enter or select another cell, the company name appears in F1.

  2. 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.

  3. In cell F3, type Description.
  4. In cell G3, type Balance.
  5. Select cells E3, F3, and G3.
  6. On the Home tab of the Excel ribbon, in the Font group, click the Borders Borders button button to add a bottom border.
  7. 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.

  8. In cell F5, type =FRACCT("ACCTDESC",E5).

    The cell displays the account description for the account listed in cell E5.

  9. In cell G5, type =FRAMT("BALP",E5).

    The cell displays the current balance for the account listed in cell E5.

  10. 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 retrieves 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.

  11. In cell E6, type ="1100".
  12. 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.

    The Statement Designer updates the copied formulas so they use account 1100 (cell E6) instead of account 1000 (cell E5).

  13. Press F9 to update the information in the copied formulas.
  14. Save your work.

The statement now appears as follows:

In this exercise, you will add column totals and format columns to prepare your financial statement for review and printing.

  1. Select cell G7.
  2. On the Home tab, in the Editing group, click the Autosum Autosum button button.

    Excel selects cells G5 and G6.

  3. Press Enter.

    Cell G7 displays the sum of cells G5 and G6.

  4. Select cell G7 again.
  5. On the Home tab of the Excel ribbon, in the Font group, click the arrow beside the Borders Borders button button, and then click Top Border.

  6. At the top of the spreadsheet, click column G to select all cells in that column.
  7. Right-click the column header for column G, and then click Format Cells.
    1. On the Category list, click Accounting.
    2. Accept the default selections for decimal places and currency symbol, or specify different settings if you prefer.
    3. Click OK.

    Amounts in column G are formatted as currency, using the decimal places and currency symbol you specified.

  8. Click and drag the edges of column headers to adjust the width of columns as needed to display information.
  9. Save your work.

The statement now displays two lines and the total balance for those lines, with balances formatted as currency:

  • Continue to experiment with formatting commands by formatting and aligning column labels.
  • Click File > Print to see a preview of the printed statement.

Next topic: Lesson 2: Using FR Paste to Insert Formulas