Use Excel Goal Seek in Indirect Calculations

The Excel “goal seek” function allows you to to work backwards from a total. I needed to do this when I was calculating direct and indirect costs from a total, without the underlying spreadsheet. The question I wanted to answer was this: “Given a total consortium cost of $72,000, and a known indirect rate of 52%, what is the direct cost?”.

Goal Seek only deals with a single variable.

The formula for my calculation would be something like:
X + (X*.52) = 72,000

This looks like three cells in the Excel Spreadsheet

Amount

Rate

Total
47368

0.52

72000

Assuming these are cells M4, N4 ,and O4, on the spreadsheet enter the following

O4 = M4+(M4*N4)

N4 = .52

When you run the goal seeker, it asks for the “goal” value and the goal cell, (which is required to have the formula) It also asks for an “entry” cell, the cell that gets changed as you spin up toward the goal.

So, this shows, that given a total cost of $72,000, the direct costs allowable using a 52% indirect rate would be $47368.

Advertisements

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