Projected dates

[K-Linerz]

Hello.

I have a project and I am working towards a delivery date and an expected volume of orders each week.

I have 297 orders and on each Week Commencing I am expected to deliver a number of orders per week until the end of the project. The project is due to end 03/08/2017.
I need to track the orders week on week. If I hit the amount of orders expected to complete each week then the project expected completion date will always stay as 03/08/2017, however, if I deliver less orders for that week then the expected completion date will go up and beyond the project end date.

Is there a formula that I can put in column E of the attached that can show the projected end date?

Thanks

Nath'

[AliGW]

The crucial bit of information missing for a formula solution is the ratio: how will the date increase? By a day? By a week? How many orders behind do you need to be to trigger each increase? There needs to be a rule.

[Glenn Kennedy]

and your expected completion date is in August 2017... two months before the orders were placed??!!

[Roger Govier]

Hi

I think we need another factor to be able to predict this.
What is the maximum Orders that could be completed in a week?
I have assumed it to be 8, and placed this value in cell D2
Then the formula becomes in cell E8

=IF($D8="","",($B$3-INT((SUM(C5:C8)-SUM(D5:D8))/$D$2)*7))

Basically, take the sum of Orders Completed to date away from the sum of Expected, and take the Integer of that number divided by maxprod ($D$2)
Then multiply this value by 7 (days) to add to the Original Expected Date to give the new Forecast

Of course, if you do complete orders in the week where expectation is Zero, then this will help to bring the date back to or ahead of expectation.

[K-Linerz]

Yeah i just saw, it should have been 2018 not 2017.

[K-Linerz]

We are projecting to work towards 8 per week however if a scenario arises that the more can complete then we will go for that.

[AliGW]

But how can we provide a forecasting formula if we don't know the ratio? You have to decide on something for forecasting purposes, so I'd suggest you stick with 8.

[Roger Govier]

Hi
Then the formula I proposed should suit you fine.