Spreadsheet for grant portfolios

Grant Pipeline & PI Effort Tracker

Every proposal and award in one workbook, with pipeline stage, deadlines, requested and awarded amounts, and the effort each person has committed across all of them.

How it works

Pipeline

One row per proposal, up to 100: title, sponsor, mechanism, PI, deadline, stage, requested total, proposed start and end dates, award amount and a probability you can set for each proposal. Stages are Idea, Drafting, Internal review, Submitted, Under review, Awarded, Declined and Resubmit. Months, requested annual amount, days to deadline, an urgency flag and expected value calculate from what you enter.

Effort and Effort Check

The Effort tab has one row per person per proposal, up to 200. Enter percent or calendar months for each of five years; months are converted using the 12-month or 9-month basis you choose on Setup. Effort Check totals each person by year. Committed effort counts Awarded proposals only. A second set of columns adds Submitted and Under review proposals, so you can see what happens if they are funded, and anyone above your cap is flagged.

Awards and dashboard

The Awards tab fills in from Awarded proposals: dates, award amount, annual amount, active or ended status, months remaining, estimated amount remaining and the people on the award. The dashboard shows proposals and dollars by stage, weighted expected funding, deadlines in the next 30 and 60 days (both windows are editable), active awards and people over the effort cap, with a chart of the requested total by stage.

Companion to the Grant Budget Builder

The Grant Budget Builder prices a single proposal. This workbook tracks the whole portfolio and the effort behind it, and does not build budgets. The Grant Budget Builder is currently sold only as part of the Researcher Spreadsheet Bundle; the two files work side by side, and you do not need one to use the other.

What you get: one .xlsx workbook with the tabs Start Here, Dashboard, Setup, Pipeline, Effort, Effort Check and Awards, with eleven example proposals using invented sponsors, people and projects; Start Here lists the ranges to clear. Expected value is the requested amount times a probability you type in, so it is arithmetic on your own estimates and not a forecast. The workbook adds up the effort you enter and does not know your sponsor's or institution's effort rules, including how summer months are treated for 9-month appointments; check those with your sponsored programs office. It does not connect to a grants system or email.

Excel or Google Sheets

Open the .xlsx in Excel as it is. For Google Sheets, open a blank spreadsheet, choose File, Import, Upload, pick the .xlsx and choose "Replace spreadsheet". The chart can look slightly different in Sheets; the numbers are the same. Sponsors, mechanisms and people are lists you edit on the Setup tab. It also comes in the researcher bundle, six spreadsheets for $39.

Buy the spreadsheet, $12 All spreadsheets