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.