Splitting a Target Figure
 

Hi All

I have a problem with splitting up the target figures for the year.

We have a number of objectives to meet over a year some are high figure
(150 cases per month) others are very low (as low as 4 cases per year.
I need to develop a spreadsheet which will allow me to present both the
expectations and results so that managers can see how close we are to
meeting our target figures.

I have come up with the formula
"=ROUND(($b$20*(3/12)),)-SUM(c20:d20)"
whe-
1. $b$20 represents the target figure for the whole year,
2. 3/12 represents the month to date ie 3rd month
3. C20:D20 represents the total of months 1 and 2.

This seemed to work okay at pproviding an even split throughout the
year but at this point a complications was introduced by one of the
managers - 'seasonal variation'. In short he cannot meet an even
spread of target because it is difficult to get work done in some
months - weather/ staff leave etc.. I had thought that I could get
round this with a slight variation
"=ROUND((($b$20*(3/12))*SEASONALFACTORS),)-SUM(c20:d20)"
where
SEASONALFACTOR would represent the a factor that would reduce the
target for that month to maybe 50% e.g. *0.5

However this just results in the figure for the next month being
increased to cover the shortfall in the previous month. This is not
what I need - I want the spread to go over the rest of the year.

I also for see it causing a problem if there is a difficult month
towards the end of the year.

Does anyone know of a better way of addressing this problem please.

Thanks

an old git!

One way is to create a list of relative production targets for each month.
Below is your formula modified:

=ROUND(($B$20*(SUM(B1:B3)/B13)),)-SUM(C20:D20)

This assumes B1 to B12 relate to the months and each contains the expected
percentage production (0 to 100). B13 is the sum of B1 to B12. So instead of
multiplying the target by 3/12, you are multiplying by the sum of the
relative targets to date divided by the total relative targets.

--
Ian
--
"oldgit99" wrote in message
oups.com...
Hi All

I have a problem with splitting up the target figures for the year.

We have a number of objectives to meet over a year some are high figure
(150 cases per month) others are very low (as low as 4 cases per year.
I need to develop a spreadsheet which will allow me to present both the
expectations and results so that managers can see how close we are to
meeting our target figures.

I have come up with the formula
"=ROUND(($b$20*(3/12)),)-SUM(c20:d20)"
whe-
1. $b$20 represents the target figure for the whole year,
2. 3/12 represents the month to date ie 3rd month
3. C20:D20 represents the total of months 1 and 2.

This seemed to work okay at pproviding an even split throughout the
year but at this point a complications was introduced by one of the
managers - 'seasonal variation'. In short he cannot meet an even
spread of target because it is difficult to get work done in some
months - weather/ staff leave etc.. I had thought that I could get
round this with a slight variation
"=ROUND((($b$20*(3/12))*SEASONALFACTORS),)-SUM(c20:d20)"
where
SEASONALFACTOR would represent the a factor that would reduce the
target for that month to maybe 50% e.g. *0.5

However this just results in the figure for the next month being
increased to cover the shortfall in the previous month. This is not
what I need - I want the spread to go over the rest of the year.

I also for see it causing a problem if there is a difficult month
towards the end of the year.

Does anyone know of a better way of addressing this problem please.

Thanks

an old git!

Hi Ian

Was a bit conerned that I was missing some obvious function intended to
do this job. Your solution sounds good to me - will give it a go.

Many Thanks.

Peter (Alias an Old Git).

No problem Peter

There may in fact be a function that I don't know about, but this method
seems to work.

--
Ian
--
"oldgit99" wrote in message
oups.com...
Hi Ian

Was a bit conerned that I was missing some obvious function intended to
do this job. Your solution sounds good to me - will give it a go.

Many Thanks.

Peter (Alias an Old Git).