Budget vs Actual Analysis

Also is there a formula that would state if the payment was made or not on time?

you need to explain further

any reason to have previous years ?

you seem to have the info
all you need is a conditional formatting
where you could use a RAG status for %

you could have a Year To Date - to compare previous years , if that would be useful

When i worked in that type of environment - the question to ask - is what action would be taken as a result of these results

for the RAG status

what values % would you apply for each colour

Green >=0 %
Amber < 0 >= 5% over budget
Red > 5%

then you would setup three rules

if you give the number range - i can add to the sheet

I was comaring budget to actual

and so I dont know what this means

Let's say a nominal increase in fees year to year shouldn't be more than 5% ideally.

as you know the data better then I do
What cells would you compare for that

also is the data seasonal ?

Let's say a nominal increase in fees year to year shouldn't be more than 5% ideally.

how would that show against a budget actual

budget 100
Actual 105
would that be a 5% increase in fees ?

have a look here
BE3 : BE9

Change the numbers and over 5% will turn red

you could add another rule for 0-5%
Amber
0 or under green

if the red is OK,
I will add the other rules

see attached

you can have the conditional format in what ever column you want
I was showing the calculation

so instead of
=(($BE3-$AY3)/$AY3)>0.05
applied to the range BE
=$BF3/$AY3 >0.05
applied to the range BF

not really - the budget should have been based on a number of factors for the year - so variance against budget is probably the best way
and having a % allowance - so that action can be taken
The budget should also have taken into account changes from last year to this year - so a comparison to the previous year maybe useful -
But things like - loan rate, inflation, currency exchange rates may have changed more than was anticipated when setting the budget - so that should also be factored in

you could show a summary sheet - if needed
and then an Arrow could be shown with conditional formatting
if up/down or the same as last year

But any of this is only worth while - if people are going to take some action
otherwise its just pretty pictures

you could just have the

-----------------2012 ------------- 2013 ------------- 2014 -------
-------------Bud -- Act ------- Bud -- Act ------- Bud -- Act -------
Items --------X-----Y------------X------Y------------X------Y-----

with the RAG status on the actuals
or even the Arrows showing status from previous year