Linked Budget Worksheets - What to do when Project Shifts in Time?

[Guest]

I'm using Excel 2002 to track budgets, actual costs and burn rates on a
project which has 20 sub-projects under the main project office. The first
sheet is linked to over 20 sub-project sheets, and is a summary of totals by
month and year for each sub-project, and then has project grand totals at the
bottom. This is a multimillion dollar project which will take 3+ years to
complete. The sub-projects have varying start and end dates.

As with any project, the schedule keeps shifting, especially as we get
closer to project launch. Or, sometimes the accounting dept. requests
scenarios of costs, for hypothetical schedule shifts, as a financial
forecasting tool.

You're probably all wanting to tell me this process begs to be managed by MS
Project. And you're right, it probably should be, as the project schedule is
already in MS Project but the budget is tracked separately in Excel and
because of time and resource constraints, will not soon be changed.

Here's the question. Can I use a formula or is there a quick way to shift
line items of cost by say 3 months, and across years? Some sub-project
sheets have 20 line items that need to be shifted.

Thanks for any suggestions you have.

 

[Guest]

Remember that you can add dates just as with other numbers. If you set up
formulas based on related dates upstream, you can make adjustments at one
point in the stream and have all downstream dates adjust automatically. It
might be easier to set up 'paired' cells to make playing the what-if game
easier. Example:

Cell A1 on a sheet has project start date, as a date:
1/1/2007
a process/phase starts 42 days later 'downstream', you have the length of
the PREVIOUS process (one that started 01 January) in cell R1 as 42, and in
R2 you calculate the start date for that phase with the formula = A1+R1,
which will display as 2/12/2007. And continue on as appropriate, identifying
dependent paths and setting up similar formulas as necessary. This allows
you to play 'what-if' just by changing the value associated with the length
of a process/phase without having to go directly into cells and manipulate
formulas (which always introduces a risk of human error either in making the
mod or in forgetting to undo it later - at least this way you have more
visibility of the values involved).

Keep in mind that you can perform such calculations across sheets as needed
also.