Lesson 1: Using Formulas to Retrieve G/L Data

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 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 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 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: