Advanced Spreadsheets and Reporting Documentation
Index

Consolidation Scenarios

This guide provides practical examples and guidance for using Advanced Spreadsheets and Reporting to create consolidated financial reports across multiple Business Central companies.

Consolidation Overview

What is Consolidation?

Consolidation combines financial data from multiple legal entities into a single set of financial statements, as if they were one company.

The App's Role in Consolidation

Advanced Spreadsheets and Reporting helps with:

  • Combining data from multiple companies
  • Currency conversion
  • Creating consolidated views

Note: Advanced Spreadsheets and Reporting provides reporting consolidation. For full accounting consolidation with eliminations and adjustments, use Business Central's built-in consolidation features alongside it.

Common Consolidation Patterns

Pattern 1: Side-by-Side Comparison

Show each company in separate columns:

              Company A   Company B   Company C   Total
Revenue         1,000       800         600      2,400
Expenses          700       500         400      1,600
Profit            300       300         200        800

Setup:

  • Column Definition: One column per company + Total
  • Row Definition: Standard G/L account rows
  • Totals: Formula columns

Pattern 2: Stacked Detail

Show each company as separate rows:

Revenue
  Company A Revenue        1,000
  Company B Revenue          800
  Company C Revenue          600
  Total Revenue            2,400

Expenses
  Company A Expenses         700
  ...

Setup:

  • Column Definition: Standard (Net Change, etc.)
  • Row Definition: Rows for each company with Company field set
  • Totals: Formula rows

Pattern 3: Single Consolidated View

Show only consolidated totals:

Consolidated Income Statement
------------------------------
Revenue                 2,400
Expenses               1,600
Net Income               800

Setup:

  • Multiple reports or hidden detail rows
  • Formula rows to sum across companies
  • Show only total rows

Step-by-Step: Income Statement Consolidation

Step 1: Design the Report Structure

Decide on your approach:

  • Side-by-side columns (Pattern 1)
  • Or stacked rows (Pattern 2)

For this example, we'll use side-by-side columns.

Step 2: Create Column Definition

Name: CONSOLIDATED

Col Header Type Company
A Main Net Change (blank)
B USA Net Change CRONUS USA
C Europe Net Change CRONUS EU
D Consol Formula (blank)

For Column D, Formula = A+B+C

Step 3: Create Row Definition

Row Description Type Totaling
10 REVENUE
20 Sales Posting Accounts 40000..40999
30 Services Posting Accounts 41000..41999
40 Total Revenue Formula 20+30
50
60 EXPENSES
70 Cost of Sales Posting Accounts 50000..50999
80 Operating Expenses Posting Accounts 60000..69999
90 Total Expenses Formula 70+80
100
110 NET INCOME Formula 40-90

Step 4: Create Financial Report

  1. Create a new Financial Report
  2. Link to your row definition
  3. Link to your column definition
  4. Name it appropriately (e.g., "Consolidated Income Statement")

Step 5: Test and Validate

  1. Run the report
  2. Compare column totals to individual company reports
  3. Verify currency conversion if applicable
  4. Check that formulas calculate correctly

Step-by-Step: Balance Sheet Consolidation

Design Considerations

Balance sheet consolidation must consider:

  • Inter-company balances
  • Elimination entries
  • Equity accounting

Basic Consolidated Balance Sheet

Row Definition:

ASSETS
  Current Assets
    Main Company        Posting Accounts, Co: (blank)
    US Company          Posting Accounts, Co: CRONUS USA
    EU Company          Posting Accounts, Co: CRONUS EU
    Total Current       Formula: sum of above
  
  Fixed Assets
    (similar structure)
  
TOTAL ASSETS            Formula

LIABILITIES & EQUITY
  (similar structure)

Handling Eliminations

For inter-company eliminations:

Row 200: IC Receivable - Main    Posting, Co: (blank)
Row 210: IC Payable - US         Posting, Co: CRONUS USA
Row 220: IC Elimination          Formula: 200+210  (should = 0)

Adding KPIs to Consolidated Reports

Example: Consolidated KPI Dashboard

FINANCIAL SUMMARY
  Consolidated Revenue        (Formula)
  Consolidated Profit         (Formula)
  
OPERATIONAL METRICS
  Open Orders - Main          Indicator, Co: (blank)
  Open Orders - US            Indicator, Co: CRONUS USA
  Open Orders - EU            Indicator, Co: CRONUS EU
  Total Open Orders           Formula
  
  Customer Count - Main       Indicator, Co: (blank)
  Customer Count - US         Indicator, Co: CRONUS USA
  Customer Count - EU         Indicator, Co: CRONUS EU
  Total Customers             Formula

Indicator rows are not currency-converted. Counts and quantities consolidate cleanly; an indicator that returns amounts from a company with a different LCY stays in that company's currency, so avoid totaling such rows across companies.

Currency Considerations

Understanding Currency Conversion

When consolidating companies with different LCY:

  • Standard G/L-based cells are converted automatically using exchange rates
  • Uses the rate effective on the WorkDate, read from the reporting company's Currency Exchange Rate table
  • Converts to the reporting company's LCY
  • Indicator rows are not converted

Translation Methods

The app uses a simple current rate method. For other methods:

  • Historical rates: Custom indicators
  • Different rates for different account types: Multiple rows

Checking Currency Settings

Verify in each company:

  1. Go to General Ledger Setup
  2. Check LCY Code field
  3. Ensure it's set correctly

Maintaining Exchange Rates

  1. Go to Currency Exchange Rates
  2. Add rates for each foreign LCY
  3. Update rates regularly

Best Practices for Consolidation

Report Design

  1. Clear Labels: Identify which company each row/column represents
  2. Consistent Structure: Use same accounts across companies
  3. Document Assumptions: Note currency rates, elimination methods

Data Quality

  1. Reconcile First: Ensure individual company data is accurate
  2. Check Currency Rates: Verify rates before running reports
  3. Review Eliminations: Ensure IC eliminations net to zero

Maintenance

  1. Regular Updates: Keep exchange rates current
  2. Version Control: Track changes to report definitions
  3. Periodic Validation: Compare to accounting system consolidation

Limitations and Workarounds

Limitation: Complex Eliminations

The app doesn't automatically generate elimination entries.

Workaround:

  • Create manual elimination rows
  • Use formulas to calculate eliminations
  • Combine with BC's consolidation for complex scenarios

Limitation: Historical Exchange Rates

The app uses the work-date rate only.

Workaround:

  • Create custom indicators for historical rate conversion
  • Use separate reports for different rate needs
  • Document the rate used

Limitation: Ownership Percentages

The app doesn't handle partial ownership.

Workaround:

  • Create formula rows that apply percentage
  • Example: Row = Company B * 0.60 (for 60% ownership)

Troubleshooting Consolidation

Totals Don't Balance

Causes:

  • Rounding differences
  • Missing companies
  • Incomplete data

Solutions:

  • Add rounding adjustment row
  • Verify all companies are included
  • Check data completeness

Currency Amounts Wrong

Causes:

  • Incorrect exchange rates
  • Wrong LCY code
  • Rate date issues

Solutions:

  • Verify exchange rates
  • Check G/L Setup in all companies
  • Review rate effective dates

Performance Issues

Causes:

  • Too many companies
  • Large datasets
  • Complex formulas

Solutions:

  • Split into multiple reports
  • Add filters
  • Schedule overnight generation