I am working on a Financial Model to be used to keep track of a project financials and would appreciate your help.

Design

1) Assumptions - Durations, scope, and categorical descriptors that can be adjusted
2) Resource List - Input the resources that have been assigned on the project. The resource will also have a start and end date.
3) Forecasting - Allocate a percentage for each of the resource defined in 2
4) Populating - The main sheet will then run a based on the allocation of the resource. Actual figures will need to be manually updated once invoices are received. I will use a reference point which will determine if actuals or forecasts will be used in the overall calculation.


Just curious to know your thoughts on how you would approach this.

In tab 2, the resource will have a defined start and end date that I would like to incorporate into the model. That is if the resource starts in jan 2013 and ends feb 2013, then the resource will be inactive.

The issue I have with this, is if we restart the resource in April 2013 with a different daily rate then my vlookup will reference the resource with jan 2013 to feb 2013 and not the one with the new rate.