Create a spread for actuals against plan

Hi

I'm trying to create a spread for actual values in comparison to the plan on a monthly basis.

Each unique ID-month line to be considered separately and aggregated on a monthly basis.

The catch is that distribution for planned value for next month should start only after distribution of values for first month are completed.

Also, any overage from actual less plan ( + any carry forward from previous months plan) is included as actual in the month it happened

The aim of this exercise is to find out if planned values were achieved, and at which points, also to understand our performance and to analyze how much backlog are we working against


Posted also on : https://stackoverflow.com/questions/...s-in-excel-365

Be patient.

I personally still don't understand the calculations based on your post 6 explanation...
Perhaps that is why others have not yet hopped on board...
I suggest an explanation in step by step format based on your data and explain exactly why your output has those values...

Hi sintek,

Sure, will try to explain the best way I can :

The aim of this exercise to re-distribute the actuals data and find out when the planned values for each month were achieved.

The output data table is formatted such as planned months are represented in rows and actual months in columns ( This is flexible and can be modified if required)

So in the attached sheet, Row-16 represents the plan for Jan-24, sum of this row is 100 which is equal to the total plan for month Jan-24, cell C16 represents the actuals done in Jan-24 against the plan for Jan-24

Similarly, D16 represents the actuals done in Feb-24 against the plan for Jan-24



1) For the re-distribution of actuals, first step is to allocate the actuals for the first month against its plan. As plan and actuals wont be same in most of the scenarios, there can be two outcomes: Actual > Plan and Actual < Plan.

For Actual > Plan, there is no need to do any further analysis. It will be allocated against the same months plan.

For Actual < Plan, it creates a plan deficiency for that month i.e. planned value for that month has not been achieved

2) If there is no plan deficiency from Step 1, next months allocation can proceed in the same way as Step 1

But if there is a plan deficiency, allocation of actuals for the second month should not start unless this deficiency is cleared.


So based on these conditions, the output in the table is obtained through the following steps:


* Total Plan for Jan-24 is 100, Total actuals for Jan-24 is 80.
So 80 is allocated against Jan-plan in C16, however there is a deficiency of 20 ( Plan 100 - Actual 80)
So no allocation for Feb-24 actuals can be done against Feb-24 plan unless this 20 is cleared.

* Total Plan for Feb-24 is 40, Total actuals for Feb-24 is 60.
As explained, before starting allocation for Feb, 20 for Jan deficiency should be allocated ( D16)
Now we start allocating the remaining feb-24 actuals which is 40 ( 60 - 20 used to cover Jan deficiency)
It is allocated in D17
There is no plan deficiency at this point

* Total Plan for Mar-24 is 0, Total actuals for Mar-24 is 20.
This is allocated directly against Mar-24 plan

* Total Plan for Apr-24 is 40, Total actuals for Apr-24 is 0.
This creates a plan deficiency of 40

* Total Plan for May-24 is 10, Total actuals for May-24 is 50.
So from 50, 40 is used to cover the previous deficiency for Apr and allocated in G19
Remaining 10 is plotted against the plan for May-24 in G20


So upon reading the output table, we infer that a part of the works done in Feb-24 was to cover the backlog from Jan-24. Also, that the target for Apr-24 was achieved only in May-24 and similarly many other insights


The original dataset comprises 100s of rows, hence its not easy to analyze the performance manually .

Hope its clear now

Thank you for your clear explanation...This works with sample data...
Please run a few scenarios and test...

Please Login or Register  to view this content.

please post expected result so that I can compare the two datasets...Am out for day but will check in later

Hi sintek

I have included the expected result in the attachment

This...
Untitled.png

cannot be accurate...200 plan for Jan..Only 60 Actual...Carry over of 140 to Feb???

chullan...I have not yet come accross a scenario where one could not find a solution..it all depends on the understanding of the requirement...
Perhaps, I just can't grasp the reasoning for your calculations...With this extra 140 actual...after the month table there is still a value remaining...what happens to that if there were June & July entries...

I think it would be best uploading a sample file with a few tables...perhaps 4 or so with expected results and explain the thought process of this result...
Surely this explanation should, if followed, allow for all the results to be accurately recorded...