Weekly tracker to show if on target for income

[Sionos]

Hi all,

I wonder if you can help.

I have been given an annual budget for income of a service e.g. £50,000 and 50 weeks (£1000 per week) in order to achieve that target.

I want to record all 50 weeks where I can input that weeks revenue and I would like it to give me the following results:

1, The total of that current week. 2, Whether I am ahead of my weekly target (so if in week three my running total since week 1 is £2,500 I want it to show I am behind by £500). 3 The running total against the annual target of £50,000.

I am not sure where to begin with this one and I am sure that it's simple but I cannot fathom it.

Many thanks in advance.

[Ace_XL]

See if the attached helps

You can select Week no. in highlighted cell

[rcm]

could this help?

[Sionos]

That is simply awesome! Thanks very much for being so comprehensive and swift!

[Sophie.Durrant]

For some reason I am unable to upload example sheets, but I will try my best to explain what I would do...

Have week number 1-50 in column A
Have your weekly revenue in column B, which you will input as you go through the year

Column C will be your running total of revenue for the year, in the first week enter =b2 and then in all cells below it to the bottom of the range enter =IF(B3="","",C2+B3). This second formula is saying that if the weekly revenue is blank, return blank or if not add this week to last week.

Then you could have, a couple of columns over Your total annual budget in cell F1 (£50k or whatever, you could change it if it increases / decreases) and below that =MAX(C:C)-(COUNTA(B:B)-1)*($F$1/MAX(A:A)).

This is what it's doing...

MAX(C:C) is giving you the biggest number in your running total column
(COUNTA(B:B)-1)) is giving you the number of non-blank values in the weekly revenue column (minus 1 for the header), let's say we have completed 3 weeks so far
($F$1/MAX(A:A)) is giving you your total budget for the year divided by the number of weeks to get your weekly average budget (£50k / 50 = £1k)

So you have the running total - (number of weeks*1000)

I hope that helps. I am going to try and find out why I can't upload a sample because I have done it before