Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Currently stuck in a scenario where I have to implement following bucket:
If (ABS(Budgeted – Actual))<0.25 AND Actual <0.25, THEN
“Miscellaneous”;
If Budgeted>0 AND Actual =0, THEN “Exhibit Not Yet Started”;
If Budgeted =0 AND Actual>0, THEN search for the Budgeted Value in subsequent quarters of that particular year, if the Budgeted Value is found in other quarters Then “Preponed”, Else “Unbudgeted”;
If Budgeted =0 AND Actual=0, THEN “No RM Cost –OH Allocated”;
If Budgeted<Actual, THEN “Budget Exceeded”;
If Budgeted>Actual, THEN “Exhibit Batch Ongoing/Positive Variance”;
Else ERROR.
Actual Values are coming from SAP at Month Year Level whereas Budget from Excel file at Quarter level.
Analysis & Calculations are to be shown at Project, Year & Quarter level.
Please find attached sample data.
May be this?
If(Fabs(Budgeted – Actual)<0.25 and Actual <0.25, 'Miscellaneous',
If(Budgeted>0 and Actual =0, 'Exhibit Not Yet Started;
If(Budget = 0 and Actual >0, If(Year = 2017, Budgeted, 'Preponed'), 'Unbudgeted'),
If(Budgeted = 0 and Actual = 0, 'No RM Cost –OH Allocated',
If(Budgeted < Actual, 'Budget Exceeded',
If(Budgeted > Actual, 'Exhibit Batch Ongoing/Positive Variance', 'ERROR'))))))
Dear Anil,
The above mentioned solution didn't worked as it calculates at line item level which is Quarter level in this case.
Are you talking about this part?
If Budgeted =0 AND Actual>0, THEN search for the Budgeted Value in subsequent quarters of that particular year, if the Budgeted Value is found in other quarters Then “Preponed”, Else “Unbudgeted”;
Can you brief on this?
Yes, that part. Have attached sample data & few cases. Pls chk.
Yes, Excel is there but i am expecting how this content need to be as calculated. I am not sure i understand over here
Please find below example:
Project | Year | Quarter | Actual | Budget | Bucket Values (For Understanding) |
---|---|---|---|---|---|
P1 | 2017 | Q1 | 10 | 0 | Preponed |
P1 | 2017 | Q2 | 0 | 0 | No Cost Allocated |
P1 | 2017 | Q3 | 0 | 20 | Exhibit Not Yet started |
P1 | 2017 | Q4 | 0 | 0 | No Cost Allocated |
P2 | 2017 | Q1 | 0 | 0 | No Cost Allocated |
P2 | 2017 | Q2 | 10 | 0 | Unbudgeted |
P2 | 2017 | Q3 | 0 | 0 | No Cost Allocated |
P2 | 2017 | Q4 | 0 | 0 | No Cost Allocated |
In above scenario, for Project P1 Actuals are defined in Q1 whereas Budget is defined in Q3. In this case, if Budget is not available in corresponding quarter, it should check for Budget value in subsequent quarter. If Budget is found in subsequent quarters As Q3 in this case, the bucket value should be "Preponed" that should reflecting across Q1 where Actual is defined.
Similarly for project P2, Actuals is defined in Q2 whereas there is no Budget Value in corresponding quarter & subsequent quarters. In this case "Unbudgeted" should reflect across Q2 Quarter of Project P2.
The case "Exhibit not yet started" is because there is Budget defined for P1 in Q3 but no Actual is defined for Q3.
The case "No cost allocated" is because there are no Actual & Budget values defined for respective Quarters..
Hope you would understand now.
some thing like below?
If((Match(Quarter = 'Q1') and Actual >0 and Budget >0) or
(Match(Quarter = 'Q3') and Actual >0 and Budget >0) , 'Preponed')
My Bad,
If((Match(Quarter, 'Q1') and Actual >0 and Budget >0) or
(Match(Quarter, 'Q3') and Actual >0 and Budget >0) , 'Preponed')