Be a Spreadsheet Superhero!

h84236603 YOU CAN BE A SPREADSHEET SUPERHERO.

It’s the heart of every budget – the spreadsheet. How it is written and constructed sets the course, not only of the few weeks it takes to submit the proposal – but of the years it takes to manage the project when it is funded. The spreadsheet starts off innocently enough, as a draft that one or two people (usually the PI and the research administrator) work to edit as the proposal is constructed. Carefully planned and well constructed – this can be a time when decisions are made, and documented to set the life of the project off on the right course. Or…not.

CREATING CALM OR CHAOS IN GOTHAM CITY

It’s quite common to learn research administration from the inside out – that is, you join a project mid-stream, and pick up the documentation that a previous team generated for you to work with. That means that your success in helping to administer the research in a department or division depends on the communication that has been left for you by the previous RA, and the internal budgets you’re responsible to manage. It’s more than just written communication – it’s the budget spreadsheets that allow you to help guide your investigators to make decisions about how their projects will be managed in the coming project period.

IT’S A BIRD…IT’S A PLANE….IT’S….EXACTLY THE ANSWER I WAS LOOKING FOR

So how does one become a spreadsheet superhero? It requires some practice and dedication, and a commitment to consistency – knowing that you’ll be serving your investigators long after you leave your position and move on to your next job if you take on this alter ego.

ATTAINING A SPIDEY-SENSE FOR SPREADSHEET SUCCESS

1. Always use formulas and references when budgeting in Excel. Hard enter numbers only when absolutely necessary. Make your spreadsheets work for you, and your investigators.

2. Ask your colleagues for the best budget spreadsheets out there – and if one doesn’t exist (rest assured it does) create one. Find one that works best for your investigators’ applications and use it consistently. Time and date stamp it. Improve it as you use it for each application.

3. Budget based on institutional base salary. If you don’t know what institutional base salary is for your institution, find out. If your spreadsheet doesn’t start or takes you off course from the IBS at your institution, you will be managing cost-sharing on the award if it is awarded.

4. Try very hard to help your investigators to break the habit of doing their own budgets. You can draft them and let them play with the numbers. Many investigators forget elements of budgets – and others don’t understand why certain costs need to be included. In all cases, they need to focus on the science, and that’s why we’re there, to help with the administrative aspects of the application. It’s hard for them to depend on us. Try to help them learn to depend on you.

5. Check your math. Have a colleague review your budget and justification, just to make sure. These things are often developed very quickly and often under pressure. It’s easy to make transposition errors, etc. Have someone with fresh eyes who has had a bit more sleep take a look at your work.

6. Spend a lot of time on the budget justification. Flesh it out for the PI, to help explain the nature of each expense. Include an explanation of the calculations involved if there is a question about how the funds are to be spent.

7. Include the names of individuals working on the project in your budget spreadsheet – even if they are not included in the official proposal documents. Simple details like this are very helpful when administering salary on funded projects.

8. When creating budget spreadsheets, put yourself in the recipient’s shoes – what does the reviewer need to see? Present the budget with that in mind and keep it clean and tight. Present supporting information in sheets behind the summary page of the workbook, linking totals to the summary page. Crowding everything into one page dilutes the information you are providing and adds to the reviewer’s job. (It can also increase the chance of error and cost-sharing.)

9. Use your spreadsheets consistently – for applications, award management (clinical trial tracking, grants management, contract management, salary and effort management, and reporting to investigators). Create roll up reports to identify areas of concern (projected deficits) and manage no-cost extensions prior to them coming due.

10. Create a shared repository for this information for the investigators you work with, as well as the business administration team and central office staff you interact with. As you maintain these records, they will serve to document the active management of your investigators’ activities, help you manage their awards, and hand off their accounts easily to the next person who steps into your role. And that’s customer service!

It’s not easy to keep up with these types of records, especially when things get busy – but its harder not to – especially when there is a need for the information. Take the time to learn Excel well, and share your knowledge and time with your colleagues. Spend the time to develop the systems and processes to support your investigators and develop the spreadsheets that will serve you – and them well – now and far into the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s