Insights / Grant reporting

How to automate grant reporting in Power BI

What can be automated, and what can’t?

The arithmetic can be automated. The certification cannot. Every figure on a federal financial report is a sum over transactions your agency already holds, so a model can compute it, reconcile it and show where it came from. Entering or uploading the report, and signing for it, stays with a named person in the funder’s system. Anyone who tells you otherwise is describing a risk, not a feature.

Which reports does this cover?

Under 2 CFR 200.328, the government-wide financial report for federal awards is the Federal Financial Report, the SF-425. Performance reports fall under 2 CFR 200.329. The uniform guidance also sets the outer deadlines:

• Annual reports are due no later than 90 calendar days after the reporting period.
• Quarterly and semiannual reports are due no later than 30 calendar days after the period.
• The final financial report is due no later than 120 calendar days after the period of performance ends, and a subrecipient owes its pass-through entity a final report within 90 days.

Your award terms can be tighter. That is why due dates should come from the award record, not be typed into a calendar by hand.

What data do you need?

• A ledger export by award and period from your finance system or ERP, with expenditures and obligations coded to the award.
• Drawdown history from whichever payment system your funders use, so cash received can be compared with cash spent.
• An award table: one row per award with its period of performance, its reporting frequency and the reports it owes.
• Match and subrecipient records, usually the spreadsheets your team already keeps.
• Program outputs, for the performance report, in whatever form your program staff track them.

How do you build it, step by step?

1. Start with the award table. Every due date is derived from it: period end plus 30, 90 or 120 days, depending on the report. Add a column for the person who owns each report.
2. Land the exports in one SharePoint folder. Power Query’s combine files feature reads every file in a folder with the same layout into one table, so each month’s export is picked up on the next refresh.
3. Define the measures once. Expenditure, obligation, federal share, recipient share, indirect, period-to-date and cumulative. Definitions belong in the model, not inside each visual. This is the step that makes the finance number and the program number stop disagreeing.
4. Build a reconciliation page before the report page. Prior cumulative plus this period should equal this cumulative. Draws received minus expenditures should equal cash on hand. When a check fails, the page should show which award and which period.
5. Lay out the report figures in the order the form asks for them, one award per page view, so whoever files can read across rather than hunt.
6. Schedule the refresh. Microsoft’s documentation allows up to 8 scheduled refreshes a day on a Power BI Pro license. There is no monthly refresh option in the scheduler, and Microsoft’s own tip is to use Power Automate when you need a custom cadence.
7. Automate the reminders. A Power Automate flow on a schedule can read the award table and email each owner a set number of days before each due date. What goes wrong with grant deadlines is rarely that someone forgot the rule. It is that nobody owned the date.
8. Send the leadership packet on a subscription. Power BI subscriptions email a snapshot of a report page on a schedule. Microsoft notes that subscribing other people requires a Pro or Premium Per User license, and attaching the full report as a PDF requires Premium capacity or Premium Per User.

What usually goes wrong?

• Mixing period and cumulative figures in the same column. Keep both as separate measures and label them.
• Typed due dates. One award amended, one date not updated, one missed report.
• Source files in a personal OneDrive. The refresh breaks the day that person leaves. Use a shared library.
• No audit trail. When a program officer asks how you got a number, the answer should be a drill-through to the source rows, not a search of someone’s inbox.

Do you need a grants management system instead?

Not necessarily, and Power BI does not replace one. Your system of record stays where it is. A model on top of it answers what the system often does not: reporting across awards, tying program numbers to financial numbers, projecting burn rate to the end of the period, and tracking who owes what by when. If your system already produces a report cleanly, keep using it for that report.

What does it cost and how long does it take?

Built in house, the cost is staff time, and most of it goes into agreeing definitions, not building charts. If you want it built, our grant reporting automation work is quoted from a written scope, each phase its own purchase, usually tuned through one live reporting cycle before handoff. You can click through a working example first: the grant command center demo models spend tracking, burn rate and compliance deadlines on synthetic data.

Our lead consultant led reporting on a $30M multi-jurisdictional federal initiative from 2019 to 2022, producing grant compliance reporting that passed federal funder review. That record was earned before Blue Peak, so we describe it as our people’s experience, not the firm’s past performance.

Frequently asked questions

Can Power BI submit the SF-425 for us?

No. Power BI can compute and reconcile every figure on it. Submission and certification happen in the funder’s system, by a person.

Do we need Power BI Premium?

Not for most small agencies. Pro covers scheduled refresh up to 8 times a day and email subscriptions. Premium capacity or Premium Per User matters if you want the full report attached to the email as a PDF.

How often should the model refresh?

As often as the ledger export changes. For most grant work that is daily or weekly, with a final refresh after month-end close.

What about subrecipient monitoring?

2 CFR 200.332 lists what a pass-through entity owes each subaward. Holding those fields in the same model means missing items and overdue subrecipient reports show up on their own rather than at closeout.