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. In Sage 300 ERP, open the largest company.
2. Open General Ledger > Financial Reporter > Statement Designer, and then click Start.
3. 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.

4. Review column C of the financial statement specification to make sure it does not suppress blank links. (You use a Z to suppress blank lines.)
5. Turn on manual calculation in Excel. More...
   1. Click File > Options.
   2. On the Formulas tab, in the Calculation options group, select Manual.
   3. Click OK.

   You can recalculate values at any time by pressing the F9 key.

6. For each company that you want to consolidate, copy the worksheet for the company to the workbook in which you are consolidating statements. More...
   1. Click the worksheet tab to select it.
   2. 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.

   3. From the To book list, select the workbook in which you are consolidating statements.
   4. From the Before sheet list, select (move to end).
   5. Select the Create a copy option.
   6. Click OK.
7. 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.)
8. 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.

9. 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.
10. Right-click the new worksheet, click Rename, and then type a name for the worksheet.

    Note: These instructions use Consol. as the name for this worksheet.

11. Generate and save statements for each additional company you want to consolidate. More...
    1. In Sage 300 ERP, open the company.
    2. Open General Ledger > Financial Reporter > Statement Designer, and then click Start.
    3. Use FR View to generate the statement, using the same options you specified for the first company.
    4. Save the workbook and close the Statement Designer.
12. 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. More...
    1. On the View tab, in the Window group, click New Window.
    2. On the View tab, in the Window group, click Arrange All, and then select Vertical.
    3. 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.
13. In the worksheet that contains the most accounts, select and copy the print area.
14. In the Consol. worksheet, right-click cell A1, and then select Paste Special > Values.
15. Without deleting any blank lines, use Excel to format the statement as needed.

    Note: You may have to insert lines for accounts that appear in one ledger, but not in others.

16. Clear all amounts from the report.
17. 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.

Related Topics