Calculate phase costs from monthly forecasts

[Sanben]

I have a table which contains the monthly costs for each resource on a project. I now need to calculate how much of those monthly costs is attributed to each phase of the project, based on the start and end date of each phase. Can this be done? I am quite proficient in Excel, but this has be stumped.

Here is a screenshot of apportion of my data table:

My phase date ranges

and the report I want to generate

I want to be able to populate the Forecast (ETC) column in the report with the total forecasts from the data table (1st screenshot) broken out by the phases listed in the first column (named Gate), based on the gate dates.

Any help would be greatly appreciate.
Thank you.

 

[awoohaw]

Hi, welcome to the Mr. Excel forum.
One thing that can help you get the fastest response from forum members is to post your data using the xl2bb add in (link below). If you cannot use that then please post the data as a clearly labeled table, and describe any formulas you have. Expected results are also a good idea.

While images can help "see" the problem, you're asking the forum to completely recreate your scenario which takes time and can include many errors.

I'm sure the forum wants to help you. So please help the forum help you.

Thanks in advance.

 

[felixstraube]

Ok first some questions:

1) Why is Gate 0 - Strategic Assessment not in your report list?
2) When do your phases start and end. For example Gate 1 - Business Justificacion / Idea Generation, does it start on 18-sep-2023 and end on 16-feb-2024 (which is the last friday before the start of the next phase).
3) How do you want to distribute your monthly cost if in one month you work on more than 1 phase. For example in february 2024, Gate 1 phase ends 16-feb-2024 (assuming the answer to question 2 is yes) and 19-feb-2024 starts the Gate 2 phase. So you will have to divide the cost of, for example, Ozorowski, and add a portion of it to each phase. How is that going to work? Counting the working days used for each phase and divide that numbers in the total working days in the month. For february you have 21 working days (if there aren't any holidays which I will come back to in the next question) and for phase Gate1 you will use 12 working day and for phase Gate2 you will use 9 working days. Now do you calculate the cost for each phase like this?: Cost of Gate1 = 12/21*1690.5, and cost of Gate2 = 9/21*1690.5?
The same for the first month, because you don't start the first working day of the month but on the 26-jun-2023.
4) Are you considering Holidays? If so, do you have a list of those anywhere in your workbook?
5) Will every phase consume all of the resources available in your Monthly Cost Table? By available I mean any value different form 0.
6) Why is there a empty Cost Category column in your report table? Are you going to input something there which shall be used in the Forecast calculation?