ReMan
|
| posted on 28/1/10 at 06:23 PM |
|
|
Excel help
As a constant but not expert user can anyone help with this.
By way of setting progress targets for the year I need to project the monthly targets to reach a year end goal.
As per the example, is there an easy way, without manuall calculating
So the colums are rougly as below
JAN FEB MAR ETC ETC TARGET
1000 ? ? ? ? 500
|
|
|
|
|
twybrow
|
| posted on 28/1/10 at 06:32 PM |
|
|
What is the manual calculation you want it to do? Not really enough info to understand what you are after....
|
|
|
ReMan
|
| posted on 28/1/10 at 07:21 PM |
|
|
quote:
Originally posted by twybrow
What is the manual calculation you want it to do? Not really enough info to understand what you are after....
My bad explanation probably!
If we have 1000 in January, and by December i need to reduce to 500.
So what is the target value for the months in between, assuming a linear decrease?
|
|
|
StevieB
|
| posted on 28/1/10 at 08:14 PM |
|
|
Not entirely sure what it is you want, but does this work for you?
ETA
If you delete the 1 above december, it makes it completely linear.
Do you need to be able to show actual figures for each month so it will forecast the difference to year end?
I can probably doctor something up if you need something very specific.
[Edited on 28/1/10 by StevieB]
|
|
|
Miks15
|
| posted on 28/1/10 at 08:16 PM |
|
|
Steve, i think thats exactl what he needs, but i think you have your calculations very slightly wrong, the decrease between nov and dec is double all
the others,
Edit to say: Youve done it so it divides by 12, but there are 11 intervals, so it just needs that slight alteration but then its good
Edit again to say: Youve already made the change, nevermind my first edit 
[Edited on 28/1/10 by Miks15]
[Edited on 28/1/10 by Miks15]
|
|
|
StevieB
|
| posted on 28/1/10 at 08:17 PM |
|
|
quote:
Originally posted by Miks15
Steve, i think thats exactl what he needs, but i think you have your calculations very slightly wrong, the decrease between nov and dec is double all
the others,
Spotted and ETA how to make the correction...
|
|
|
Ivan
|
| posted on 28/1/10 at 08:20 PM |
|
|
I would draw a scatter chart of the months (1 to 12) in the first column and the amounts (1000 for month 1 and 500 for month 12 in your example) and
then draw a trendline selecting the type of curve you want - in this case a straight line and making sure I ticked the "Display Equation"
box.
In your examle the equation is:
Y = -45.45x + 1045
With the equation you can rapidly fill in the remainder.
The advantage of the above is that as the months pass and actual data becomes available you can test the trend against theory and maybe get a better
idea of what type of curve more closely matches your performance for fuutre forecasts and to revise the current one.
[Edited on 28/1/10 by Ivan]
|
|
|
twybrow
|
| posted on 28/1/10 at 08:31 PM |
|
|
Here you go.....
Bollox, too slow!
[Edited on 28/1/10 by twybrow]
|
|
|
ReMan
|
| posted on 28/1/10 at 08:46 PM |
|
|
quote:
Originally posted by StevieB
quote:
Originally posted by Miks15
Steve, i think thats exactl what he needs, but i think you have your calculations very slightly wrong, the decrease between nov and dec is double all
the others,
Spotted and ETA how to make the correction...
Thanks guys that's exactly what i want.
Just need to work out how to adjsut it for different periods etc
|
|
|
ReMan
|
| posted on 28/1/10 at 08:55 PM |
|
|
quote:
Originally posted by twybrow
Here you go.....
Bollox, too slow!
[Edited on 28/1/10 by twybrow]
Thats gtreat, I think I can understand the formula enough to add in extra months etc
Thanks again
|
|
|
