To provide a general methodology (not an actual template), here is how I would advise anyone to approach this problem.
The Problem with One Report
I will say off the bat--most PIs want a single report, and offices like mine absolutely will not do that for you. The reason is that legally, we have terms and conditions specific to every award (even gifts). You cannot mix the money together. Indirect cost rates can vary, what's allowable on one fund is not allowable on another, etc. Some money may expire earlier--you need to know if 80% of your portfolio expires in 2 months and a solution is needed for that specific problem--that won't be obvious if all the money is mixed together. You can, however, keep a workbook where each tab is dedicated to a single project, and then a face page summarizes the data for the portfolio. Formulas like XLOOKUP are helpful for this. That is what most portfolio managers do. They screen for risk and develop a dashboard that highlights risk to the PI across all the projects.
Goals of the Report
First, write out the goals of your reporting system. Do you need to know where people are spending time? Does that need to be tracked to an effort certification cycle (monthly, quarterly, annual)? Do you need to know acknowledgment text for publications based on where their funding is? Is there a certain materials and supplies (M&S) budget that you allocate per FTE, and does that vary by position type (postdoc rate vs. grad student rate)? Do you keep track of appointment dates so that you can let folks know when they need to make arrangements for the purposes of making new arrangements for visas? Do you need to come up with a spending rate per month for each funding source in order to spend on time? Are no-cost extensions allowed (NCEs)? Can you spend early (pre-award spending)? Are there variances in indirect cost (IDC)/facilities and administration (F&A)/administrative fee rates? Are any cost categories not allowed on your funding? Do you have effort commitments you need to meet?
What is this report going to solve for you?
Generating the Insights
The second step is to figure out how to get the data that will generate the conclusions. What do you have access to? You should be able to talk to research administrators or folks in the finance department to find the necessary data. Most places have a data warehouse. If you are not allowed to directly pull data, find who can pull the data for you, and then you need to learn how to use the data to output a visual or KPI (key performance indicator). As an example, one of the major challenges is the idea of a "burn rate". The main problem with a burn rate is that it may go up quickly when you buy a single piece of equipment, or you move 6 people suddenly onto a grant -- that is not representative of sustained spend. DOD famously has a post-award template with a line chart that responds to an area where you type in total award ceiling by month, total spending, and total projected spending. The three lines create an image for you to see when you will eventually spend to the total amount of the award. This is called a "spending plan". DOD is trying to figure out when they will have to send you more money because they don't send it all upfront like NSF or NIH.
Data Shaping and Data Cleaning
If you are trying to do this on a regular basis, it is a lot of work. My folks regularly spend about 10+ hours on a monthly portfolio analysis depending on portfolio size. If you are serious about learning dashboarding, I recommend a course from Leila Gharani or Chris Dutton. They are both on LinkedIn Learning and also have their own companies. One feature of Excel that they teach is Power Query. It's an incredible "hidden" part of Excel that automates the data cleaning/shaping and is "low code". Once you set up your system, you just replace the data file every month and hit the refresh button. This is easier for people to learn than Python, and no additional software needs to be installed. To my knowledge, there is no Google Sheets equivalent.
Generating Ideas for Templates
This will maybe be a surprise, but I recommend asking this question to ChatGPT/Claude. I use AI a lot in my work as a thought partner. In this case, you can ask it to generate example reports. As you are the end consumer, decide if it's helpful or not, and then continue with my process above to find the data to generate/fill out the report. I don't outsource all of my work to an AI, but I get good ideas from the AI and implement them. I also make nice buttons with macros attached. I ask the AI to write the macros for me, and then I implement those. An example would be to open a file picker so that I can select the file with the transaction data. I use that file path in a query that I write using Power Query -- all automated. Again, start with a basic idea, and build over time as your needs grow. My folks do this full time, and it takes them a few years to get the hang of this work--the industry is hard and the regulations and needs change every day.
Other Considerations
In the end, the process is iterative. When it comes to the goals and what you should include, you respond to the world in front of you. When I started, I didn't know what to track either. PIs wanted balances, so I did that. Then I learned they spent money that didn't hit the ledger for months -- the vendors didn't pay the POs quickly. So then I added a column for commitments/encumbrances. Later I learned that some sponsors don't send income quickly, and I then added a mechanism to track that to make sure we get our money. I am sorry to say, that in some ways you have to get burned to learn what is important. If you know the terms and conditions for your funding, write your reporting to make sure you will never violate the terms--that's the most important thing to track. Spend on time; spend ethically; ensure the sponsor actually cuts the check and you receive the funds; make sure your people are paid and reappointed, and that you don't run out of supply money. Always apply for funding 2 years before you run out of money. In order to avoid a funding cliff, you need to plan a year where you don't get any new awards.
Finally, all of this work is a form of project management. If you are ever interested in ARPA projects, those require PIs to display the highest forms of project management, and it comes as a huge surprise to most people. You may find it helpful to take a class on project management to learn about risk management, resource management and distribution, etc. I also recommend asking a finance person to help you learn things. I have known some PIs who dig in and want to learn the math--learn the rules. Regardless of lab size, these are always my most successful PIs--they know how everything works and they use that knowledge every day. While not every administrator wants to train PIs, in general, it's much easier to work with someone who wants to understand things than with those who relegate all of the management to the administrative team. Be curious and ask questions. If you are able to learn the finance and risk management part of portfolio management, you'll be able to take on more of the portfolio management yourself. This will strengthen your ability to make executive decisions and lower your portfolio risk.
