Run Down Contract Value with specific Milestones (Forecast)

[ats1312]

Hi,
I have an ever growing list of new projects that I am trying to forecast their revenue over a determined time frame. It getting quite manual so I was hoping there was a solution out there....im really struggling.

The problem is that the revenue is recognised in different %'s depending what phase it is in so Im finding it very difficult to use a formula.

Ive attached a sample file of how it should look, but getting a formula to do this is beyond me. ANy help is greatly appreciated.

[KOKOSEK]

Try this :

into AD2 and right.....


=IFERROR(INDEX($E$1:$AC$1,MATCH(1,IF(SUBTOTAL(9,INDIRECT("$E$2:"&ADDRESS(2,COLUMN($E$2:$AC$2),4)))*100/$D$2>10,1),0)),0)

that's array formula so accept it with CSE not tab or enter.

and also when you will drag it right, you have to edit bolded parameter for desired percentage, for 20,40 etc.

[ats1312]

Hi,

Thanks so much for replying. Maybe I didn't explain correctly but I'm trying to fill cells E2 to AC2, I filled these in to show what the numbers should be based on the parameters.

You say to put into AD2 which is a fixed date...apologies if I didnt explain correctly. Its almost like a depreciation schedule I need but with varying rates depending on the dates in Rows Ad to AJ.

Thanks

[KOKOSEK]

OK. I get it now.
You want to try to calculate values in E2:AC2 based on criteria from both tables (milestones/dates and milestones/percentage).

Sorry, English is not my native language.

[FlameRetired]

With Milestone dates in column E this in H11. Fill down as the actual figures come in for column G.

Please Login or Register  to view this content.

	E	F	G	H	I
7		Assumed Completion by
8	2/26/2019	Milestone 1	10%
9	4/23/2019	Milestone 2	20%
10	2/26/2020	Milestone 3	40%
11	5/21/2020	Milestone 4	70%	47%	=FORECAST(E11,$G$8:G10,$E$8:E10)
12		Milestone 5	80%
13		Milestone 6	90%
14	3/11/2021	End Date	100%

[ats1312]

OK. I get it now.
You want to try to calculate values in E2:AC2 based on criteria from both tables (milestones/dates and milestones/percentage).

Sorry, English is not my native language.

Yes, exactly!!!! The dates will vary from project to project but the completion % are fixed.
Thanks

[ats1312]

With Milestone dates in column E this in H11. Fill down as the actual figures come in for column G.

Please Login or Register  to view this content.

	E	F	G	H	I
7		Assumed Completion by
8	2/26/2019	Milestone 1	10%
9	4/23/2019	Milestone 2	20%
10	2/26/2020	Milestone 3	40%
11	5/21/2020	Milestone 4	70%	47%	=FORECAST(E11,$G$8:G10,$E$8:E10)
12		Milestone 5	80%
13		Milestone 6	90%
14	3/11/2021	End Date	100%

Hi, sorry I don’t understand???? Perhaps I didn’t explain the problem correctly in the first post. That table you posted is fixed. I’m expecting this % to be achieved by each milestone. The dates are the variable in columns ad to aj

[FlameRetired]

ats1312

Please don't quote whole posts. It's just clutter. A few lines of relevant text it sufficient.

And I still don't understand. Where do you want the output?
I'll stay "tuned".

[ats1312]

sure, apologies! Im looking for cells E2:AC2 to be populated based on 2 sets of criteria. 1 - milestones dates will vary from project to project and 2 - milestones completions percentage which is fixed.

So for every project, I'm expecting 10% of the contract to be "used" by Milestone 1 based on the agreed dates in the contract...this varies depending on what's agreed in the contract. By Milestone 2, 20% of the contract should be "used" again based on the agreed dates.

Its like a deprecation schedule with varying rates based on the 2 sets of criteria. I may have 100+ projects to forecast expected contract revenue based on these criteria.
Thanks for your help.

[FlameRetired]

Still no clearer.

[ats1312]

In the sheet I sent, E2:S2 are the values I would like the formula to compute, I put them in the show the values that should be returned. Based on a set of criteria specific to each Contract i.e. Dates of milestones and as a general rule of thumb i.e. Assumed Completion Table %

All these conditions will vary based on the Contract
- Contract is active from 1st Dec 2018 (Cell B2) and due to finish by 11th March 2021 (Cell AJ2)
- Contract Value is $4m (Cell D2)
- On a straight line basis, I should have a forecast of $4m divided by rounded 69 months which works out at approx. $58k per month over the life of the project. But this is not what I need.
- Expecting Milestone 1 to be completed by 26th Feb 2018 (Cell AD1), this works out to be 3 months for this project, so over those 3 months 10% of value should be forecast. This 10% applies to all projects, so if its 4 months or 5 months, we should only take 10% of the contract. These %'s are fixed regardless of length of Milestone
- The Milestone dates (lengths) will be different for each project, but the value of the milestone is set at that % over the life of that milestone. By Milestone 4, 70% of the contact vale should be forecast.

Hope that helps

[Bo_Ry]

I'm not sure about this but the result looks right.

Need to change 2 things
1. Insert column AD
2. change Milestone date at AD:AK from Text to date value

Try at E2 Press Ctrl+Shift+Enter and drag to the right

Please Login or Register  to view this content.

[KOKOSEK]

Bo_Ry,
If I've put Milestone 5 as 1/8/2020 and End date as 1/12/2020 I've got error in col Z (1/9/2020)?


I've a got other solution:
1. Add extra row (1) above header and put into E1 (above Dec2018):

Please Login or Register  to view this content.

then drag it right to AC1

2. add percentages below milestones dates like below: (it can be solve in different way but it should not be a big problem)

Przechwytywanie.JPG

3. And finally put into E4 (in row 3 I've left Bo_Ry's solution)

Please Login or Register  to view this content.

(no array, no CSE needed)
and drag it right to AC4.

I've attached file if something is not clear.

[Bo_Ry]

Adjusted method.
This one, need to fill blank Milestone date the same as the previous Milestone#, don't leave it blank.
If fill Milestone 5 and 6 date with Milestone4 date.

E3 Press Ctrl+Shift+Enter drag across

Please Login or Register  to view this content.

Thanks, KOKOSEK. I got this part from you.
MATCH(1,--(E$1<=$AE2:$AK2)

[ats1312]

This is incredible!!!! thanks you both so much, I am adding this to my Live sheet now and hopefully it works out as well as the sample I sent.

[KOKOSEK]

You welcome.
Use forum options and marked thread as SOLVED, please.