Making Consolidated Financial Reporting Engine

Making Consolidated Financial Reporting Engine
by Using VSTACK, HSTACK

MultiNational Corp "UniGlobal" consolidates financial reports from 12 subsidiaries. Build an engine that stacks and combines data from multiple sheets/sources into unified reports.
Dataset Structure:

Consolidated Financial Reporting Engine
Function Focus:VSTACK, HSTACK
Scenario:
MultiNational Corp "UniGlobal" consolidates financial reports from 12 subsidiaries. Build an engine that stacks and combines data from multiple sheets/sources into unified reports.
Dataset Structure:
• 12 subsidiary sheets: Each with identical structure (Revenue, Expenses, Profit, Month)
• Need consolidated P&L, Balance Sheet, and Cash Flow statements
Tasks:
1.Use VSTACK() to vertically combine monthly data from all 12 subsidiaries
2.Apply HSTACK() to horizontally merge related metrics (Actual vs Budget vs Variance)
3.Build dynamic range references that automatically include new subsidiary data
4.Create summary dashboards with cross-sheet consolidation using spilled arrays
Expected Output:
Unified financial reports that auto-consolidate data from multiple sources with dynamic expansion.
Evaluation Criteria:VSTACK multi-range stacking, HSTACK horizontal merging, dynamic range handling

Subsidiary Month Rev_Actual Exp_Actual Rev_Budget Exp_Budget Cash_Flow
UniGlobal-NA Jan-23 120000 85000 115000 80000 35000
UniGlobal-NA Feb-23 125000 82000 120000 82000 43000
UniGlobal-NA Mar-23 130000 90000 125000 85000 40000
UniGlobal-NA Apr-23 110000 88000 118000 88000 22000
UniGlobal-NA May-23 145000 95000 130000 90000 50000
UniGlobal-NA Jun-23 150000 98000 140000 95000 52000
UniGlobal-NA Jul-23 140000 92000 135000 92000 48000
UniGlobal-NA Aug-23 155000 100000 145000 98000 55000
UniGlobal-NA Sep-23 160000 105000 150000 100000 55000
UniGlobal-NA Oct-23 135000 94000 140000 96000 41000
UniGlobal-NA Nov-23 170000 110000 160000 105000 60000
UniGlobal-NA Dec-23 200000 125000 180000 115000 75000

Subsidiary Month Rev_Actual Exp_Actual Rev_Budget Exp_Budget Cash_Flow
UniGlobal-EU Jan-23 90000 65000 95000 68000 25000
UniGlobal-EU Feb-23 92000 63000 93000 66000 29000
UniGlobal-EU Mar-23 98000 70000 96000 70000 28000
UniGlobal-EU Apr-23 85000 68000 90000 69000 17000
UniGlobal-EU May-23 105000 72000 100000 71000 33000
UniGlobal-EU Jun-23 110000 75000 105000 74000 35000
UniGlobal-EU Jul-23 108000 74000 102000 73000 34000
UniGlobal-EU Aug-23 115000 78000 108000 76000 37000
UniGlobal-EU Sep-23 118000 80000 110000 78000 38000
UniGlobal-EU Oct-23 100000 71000 105000 75000 29000
UniGlobal-EU Nov-23 125000 82000 115000 80000 43000
UniGlobal-EU Dec-23 145000 90000 130000 85000 55000

Subsidiary Month Rev_Actual Exp_Actual Rev_Budget Exp_Budget Cash_Flow
UniGlobal-APAC Jan-23 75000 55000 80000 60000 20000
UniGlobal-APAC Feb-23 78000 54000 79000 58000 24000
UniGlobal-APAC Mar-23 82000 60000 82000 60000 22000
UniGlobal-APAC Apr-23 70000 58000 76000 59000 12000
UniGlobal-APAC May-23 88000 62000 85000 61000 26000
UniGlobal-APAC Jun-23 92000 65000 88000 64000 27000
UniGlobal-APAC Jul-23 90000 64000 86000 63000 26000
UniGlobal-APAC Aug-23 95000 68000 90000 66000 27000
UniGlobal-APAC Sep-23 98000 70000 92000 68000 28000
UniGlobal-APAC Oct-23 85000 62000 88000 66000 23000
UniGlobal-APAC Nov-23 102000 72000 95000 70000 30000
UniGlobal-APAC Dec-23 120000 80000 110000 75000 40000

=LET( block1, A2:G13, block2, A16:G27, block3, A30:G41, stacked_raw, VSTACK(block1, block2, block3), FILTER(stacked_raw, INDEX(stacked_raw, , 1) <> "") )