The Monthly Reporting Challenge – Josh Part 2

THIS IS AN EXTENSION TO JOSH AMAROFF Budget vs Actual presentation on Danielle’s channel. This is the REQUIREMENT in an empirical research challenge format.

The board meets every month to review the trading results of the previous month. This presentation is highly sensitive and often charged with tension. Why? Because the performance of each operating unit—by product and by region—ultimately reflects on the managers responsible for those areas. This is expected, but the challenge arises when inaccuracies in the figures lead to incorrect assessments of performance. Mistakes can result in the wrong people being fired or promoted and can cast doubt on the accuracy of the financial reporting itself.

For the CFO and the finance team, any significant inaccuracies undermine the credibility of their reporting. However, they can only rely on the data flowing through the ERP system, and transaction allocations are often determined at the local level. Coding decisions may be influenced by individual users or automated rules and AI, leaving the finance team accountable for errors they did not create.

Each month, tens of thousands—if not hundreds of thousands—of transactions are recorded. Regardless of where errors originate, the responsibility for the final figures rests with the CFO and the finance team. This is the problem, and this challenge is about finding a solution.

Defining the Requirement

Currently, the primary concern is the Profit and Loss (P&L) statement, which is directly pulled from the ERP system, including any errors. To ensure accuracy, we need a mechanism that allows budget holders to review and validate the transactions contributing to the draft P&L figures.

Budget holders must be able to:

• Review all transactions affecting their assigned budget areas.
• Compare actual figures with the budget to identify discrepancies.
• Confirm that all recorded figures are correct.
• Flag incorrect allocations and notify the finance team.
• Report missing costs or charges that need to be accrued.
• Identify prepayments or other timing adjustments that need correction.
• Highlight any unknown or unexpected transactions requiring investigation.

The Solution

To address this challenge, we need a structured mechanism to facilitate the review process:

1. Transaction-Level Visibility – Budget holders need access to a detailed breakdown of transactions that contribute to their P&L figures.
2. Communication Channel – A seamless way to flag anomalies and communicate them to the finance team.
3. Action Mechanism – The finance team must be able to process corrections, make adjustments, and post journal entries efficiently.
4. Tracking System – A method to monitor the completion of these reviews so the finance team can follow up on outstanding validations before the reporting deadline.

The Challenge

How would you design a practical and scalable solution in Excel to accomplish this? Consider:

• Data Extraction: Pulling detailed transaction data from the ERP system.
• Review Interface: A structured and user-friendly way for budget holders to review their figures.
• Feedback System: A clear mechanism for submitting corrections.
• Audit Trail: A way to track completed reviews and outstanding actions.

The ultimate goal is to ensure organization-wide accuracy in the monthly P&L, confirmed by all responsible budget holders.

What are your ideas for solving this challenge using Excel? Let me know!