Forecast Model for Construction Projects

I've created a forecast model for my company's construction projects. The goal is to forecast billings and estimated costs for the next few years by only having to input the start dates and end dates. Our project start and end dates change every month when we get updated schedules, so it's not easy to keep up with without something to calculate it. I set it up to calculate based on a weekly basis and total up the months, but it does not take into account partial weeks and I am not sure how to fix that.


Please, please, please help!!

Thank you so much

This solution makes two changes.
1) Since there is a realization of partial weeks it changes the formula used to calculate column K to read: =(J37-I37)/7
2) The formula that populates the weekly estimates now reflects that the last week may be a partial payment using the formula:

Formula:

Please Login or Register  to view this content.

Note: The above mentioned changes have been applied to the 'S2 - DST-2 Baffle Drop' project billing, rows 36 and 37.
Let me know if you have any questions.

Yes $L36:L36 is correct. SUM will ignore the text so that it is the same as adding zero to the accumulating sum.
Try this in row 40 and see if the result will work for you.

Formula:

Please Login or Register  to view this content.

I am not a finance person so I don't know if it is acceptable that the remaining costs (or billings) are added at the end.
Let me know if you have any questions.

Here is a modification to the formula that will stop the front loading:

Formula:

Please Login or Register  to view this content.

I tested for 'S-1', rows 28 and 32, however it should work for any of the projects.
Let me know if you have any questions.

Here's something else you might want to consider. Take a look at the S1 project, to me it seems be a more understandable way to show the relationship between the rows.
Let me know if you have any questions.

You're Welcome, thank you for the feedback and for marking the thread as solved. I hope that you have a blessed day.

Since you mention that you are bidding more work want to let you know that I also made a change in the formulas for cells M5:M8 (file attached to post#8) that will make it easier to add new projects. You should be able to copy these cells to each month, hope it will be helpful.
Let me know if you have any questions.