Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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). |
#4
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charting data points and show a target range on the same chart. | Charts and Charting in Excel | |||
Splitting names from cells | Excel Discussion (Misc queries) | |||
How can I make a timesheet to figure my hours and payrate? | Excel Worksheet Functions | |||
how to type a minues figure | New Users to Excel | |||
Target line | Charts and Charting in Excel |