Need Excel Help – Investor Distribution Comparison (High Visibility Project)

Hi everyone,

I’m an accountant in the real estate industry, and I’ve been given a high-visibility project that I really want to knock out of the park. For the past ~2 years, I’ve been manually calculating and distributing investor returns using a QFR-based Excel process. This feeds into our accounting system (Sage Intacct) and ultimately into our ACH distributions.

Recently, our company developed new portal functionality that allows investor distributions to be processed automatically with the click of a button. Before this goes live, I’ve been asked to validate it by comparing historical distributions (January & February) against what the portal generates.

What I Have So Far

My initial thought was to create a basic comparison like:

• Historical Amount
• Portal Amount
• Variance (=B2 - C2) But that feels way too basic for something this important. I want to elevate this into something management can actually gain insight from—not just a simple variance check.

What I’m Trying to Build

I’d love help or ideas on how to make this spreadsheet more robust and “presentation-ready.” Specifically:

1. Comparison Tab

• Clean layout comparing historical vs. portal data
• Meaningful variance analysis (not just raw differences)
• Flags or indicators for material discrepancies
• Anything that helps quickly identify issues at a glance

2. Summary / Dashboard Tab

• High-level view for management
• Total distributions (historical vs portal)
• Total variance and % variance
• Count of mismatches or exceptions
• Any visual elements (charts, conditional formatting, etc.) that improve clarity

3. Edge Cases / Notes Tab

I also need a third tab that outlines nuances the developers need to consider before production, such as:

• JE import creation requirements
• Rounding issues
• Wire vs. ACH needs for custodians
• Investor-specific scenarios

I understand the logic behind these items, but I’m struggling with how to present them in a clean, structured way.

My Skill Level

I’d say I’m beyond a beginner in Excel, but definitely not advanced—I know the basics well but haven’t fully leveraged things like dashboards, advanced formulas, or more polished presentation techniques.

What I’m Looking For

• Specific formulas or features I should incorporate
• Layout or structure suggestions
• Ideas to make this more insightful for management
• Anything that would make this feel like a “senior-level” deliverable

This is a big opportunity for me to stand out, so I really appreciate any advice you can share. Thanks in advance, I'm excited to dive into the community! 🙏