Enterprise-Level Excel Budget Review Solution – Explainer 1

1. Introduction

• Purpose of this explainer: A high-level deep dive into the methodology behind the solution without discussing technical implementation.
• Future explainer will cover how Excel actually implements this step by step.
• Why this solution?
  • Traditional enterprise solutions (IBM, Oracle, SAP) are expensive and complex.
  • This Excel-based solution offers a lightweight, scalable, and cost-effective alternative.

2. Core Data Architecture

2.1 The General Ledger (GL) Data Source

• The General Ledger (GL) in the ERP system contains all financial transactions.
• Direct querying of the ERP system is not feasible due to:
  • IT security policies.
  • Performance impact.
• Solution: GL Clone Table (GLC)
  • A replica of the GL database, updated periodically.
  • Ensures smooth querying without burdening the ERP system.

2.2 Understanding the Data Structure

• GL Clone Table (GLC) contains financial transactions:
  • Fields: Period number, Journal reference, Posting date, Account codes, Debit/Credit amounts.
• The Group Map Table structures business hierarchy:
  • Levels: Region → Country → City → Shop.
  • Enables multi-level drill-down and consolidation.
• Additional tables:
  • Comments Table: Stores user comments and potential journal adjustments.
  • Access Rights Table: Determines what data each user can see.
  • Users Table: Stores usernames and login credentials.

3. Budget Holder Review Process

3.1 Selecting a Shop for Review

• Budget holders (e.g., Paul in Japan) select a shop using cascading dropdowns.
• Click “Get”, triggering a query to retrieve filtered GL data for the selected shop.
• Multiple users can work independently without interference.

3.2 Identifying Anomalies in the Financials

• Budget holders review actuals for the period.
• If an unusual transaction is found:
  • Selecting a cell triggers a detailed query to retrieve transaction-level data.

3.3 Adding Comments or Adjustments

• Budget holders enter comments directly in Excel (e.g., accruals, prepayments, errors).
• Click “Put”, which uploads comments to the Comments Table.
• This provides a structured method for flagging transactions.

4. Finance Team Review and Adjustments

4.1 Retrieving Budget Holder Comments

• Finance team retrieves all flagged transactions using their own Excel workbook.
• Reviews and categorizes comments:
  • Errors → Correct in General Ledger.
  • Prepayments/Accruals → Make appropriate adjustments.

4.2 Making Journal Adjustments

• Adjustments are recorded in the General Ledger.
• Automated Journal Entry Creation:
  • Excel can generate a CSV file formatted for direct ERP import.
  • Eliminates manual journal entry, improving efficiency.

5. Final Validation by Budget Holders

5.1 Refreshing Updated Financials

• Budget holders refresh their reports to see adjustments reflected.
• Once validated, they mark their review as “Confirmed”.
• This status update is recorded in the Review Status Table.

5.2 Controller-Level Oversight

• The Controller’s dashboard provides an overview of review status across all shops.
• Color-coded indicators show real-time progress.

6. Consolidation and Multi-Level Review

6.1 Why Consolidation?

• Users may need to review aggregated data at different levels:
  • Shop level (Level 4) → City → Country → Region → Full Group.
• Selecting a higher level in the hierarchy triggers an aggregated query.
• The same mechanism, but with a modified query, retrieves summarized financials.

6.2 User-Specific Access Control

• Each user sees only the data they are authorized to access.
• The Access Rights Table filters data dynamically based on the username.
• Example:
  • John retrieving “Africa” data will see different figures than Chris retrieving “Africa” data, based on assigned regions.
  • CEO (Top Man) has full access to consolidated group-level financials.

7. The Five Key Database Tables

1. GL Clone Table (GLC): Stores transactional financial data.
2. Group Map Table: Defines organizational structure.
3. Comments Table: Stores budget holders’ flagged transactions.
4. Access Rights Table: Determines data visibility for each user.
5. Users Table: Stores login credentials.

Understanding these five tables is critical for understanding how data is retrieved, filtered, and updated.

8. Summary of Key Concepts Learned

• Database Fundamentals:
  • Understanding tables, fields, records, and relationships.
• Querying at Different Levels:
  • Retrieving data at shop level or aggregated at higher levels.
• Dynamic Filtering with User Access Rights:
  • Ensuring users only see what they are authorized to see.
• Bidirectional Data Flow:
  • Retrieving GL data (GET).
  • Submitting comments and adjustments (PUT).
• Status Updates and Monitoring:
  • Tracking review progress using the Review Status Table.

9. What Comes Next? (Future Explainers)

• We haven’t discussed Excel implementation—just the principles behind the system.
• Next step: How Excel interacts with this architecture.
  • No Power Query, no dynamic arrays, no Excel tables.
  • Simple yet powerful “Get” and “Put” functionality.
• Comparison with traditional methods (Power Query, Dynamic Arrays, etc.).
  • Why those approaches are clunky and inefficient for enterprise use.
  • Why this solution is easier, faster, and more scalable.

10. Conclusion

• A streamlined, enterprise-ready budget review system using Excel and a database backend.
• Key benefits:
  • Lightweight, efficient, and cost-effective.
  • Real-time collaboration without performance issues.
  • Audit trail and accountability at every stage.
• Next step: A technical deep dive into how Excel actually implements this process.

This final outline provides a complete structure of the entire process. Let me know if you want to tweak any sections! 🚀