• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Formula Help

Y

yvcamasia407

New Member
Hello,

I am building an Forecast Template. Please...I need help to create a formula for my premises. For example: if the COO wants to increase Gas sales by 5% over 12 months, all I have to do is put in 5% in my control sheet and it will automatically add the amount equivalent to 1/12 of the 5% to month 1, month2, up to 12. What is the most dynamic formula to use?

Thank you.


Becky
 
Hi, yvcamasia407!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Assuming your 5% is in cell B2 and 12 is in cell B3, if your months range from D11:D22, in D11 type and copy down thru D22:

=$B$2/$B$3


Is that what you were looking for or I misread you?


Regards!
 
Thank you SirJB7, and yes I've read the three first green sticky topics. I've been a member and a subscriber of chandoo for over a year now.


My mistake-I was not clear on my first post. I apologize. The number 12 I am referring to is Month 12. Meaning from Month1 to Month 12, the 5% will be added or spread equally to whatever number that already exist on any given month.


So if like you said my 5% is in B with existing data from C2 to N2, I need a formula that will add the 5%/12 months for every month to the existing number.


A possibility exist that the % will be a negative number.


Again, thank you for your quick response.


Becky
 
Hi, yvcamasia407!


Sorry for taking you as a new member, but your profile says you joined us two hours ago and that was your first post... I'm only a ninja, but still not a magician :)

If you enter now with another nick, then welcome again!


I still don't fully understand, but here goes a blind shoot (if it doesn't work, I apologize and would ask you to upload a sample file):


B2 : 5%

C2:N2 : original values

C3:N3 : updated values


formula for C3 (then copy across) is:

a) =C2*(1+$B$2) -----> each value +/- B2

b) =C$2+$B$2*SUM($C$2:$N$2)/12) -----> value +/- B2 distributed fixedly along 12 months


A blind shoot and a shoot in the dark.


Regards!
 
Hi, yvcamasia407!

Glad you solved it, even if I don't know which guess was the right. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!


EDIT: I now know which one, misread you "a)"
 
@Montrey

Hi!

Which one? Mine's, yvcamasia407's or her boss's one?

Please feel free to make the proper corrections.

Regards!

PS: as b(ut)ob(ut)hc says... still typing telegrams? :p
 
I guess the OP. If 5% is simply divided by 12 and added to each month, the actually %increase at the end of the year is greater than 5%. (Compounding).


One would have take 5% of last year $. then take the $ and divide by 12.
 
@Montrey

Hi!

There are many formulas to distribute a 5% (of what? of each monthly value, of year total, of...). It seemed strange to me too, so I wrote the two first I thought, and I hit with the very first and simple. If the user agrees and COO gives Ok, then we're all happy... If not, well, someone would define it.

Regards!
 
@ Monterey- thank you for your feedback. Now I am really confused. When you divide 5% by 12 =.41666%. to proof the total %.41666 X 12 = 5%. (at least in my calculator)


But the thing is if you have an existing data of 1,000 per month for 12 months that's = 12,000. If the annual forecast increase is 5%=that is an increease of 600 which makes the annual forecast total 12,600.


The monthly increase for 1,000 * .41666% = 4.2 X 12 months = 50


I used the formula of 1,000*5% every month and it works to 12,600. I had to ask because I just know my formula is incorrect, somehow I had it in my mind that I should divide the 5% by 12. I had to ask the Excel experts!

It is 525PM here--I think my calculator is giving me wrong numbers. :)


Thank you for taking time to help me.
 
Hi, yvcamasia407!


The main point is the 5%.


5% in percentage of every monthly value?

5% in amount over the whole 12 months sum?

5% (any of them) distributed equally over 12 new months?

5% (any of them) distributed proportional to each month value over whole year?


There are a lot of ways to interpret the 5%. If you're tuned with your boss, yours is the word; if not or if any doubts, I'd suggest to ask him, maybe his viewpoint is different.


Regards!


PS: I'm at GMT-3 so here it's 21:45 :)
 
Hi ,


I think the math is perfect.


To quote from the first post :

[pre]
Code: 
if the COO wants to increase Gas sales by 5% over 12 months
[/pre]
this , I think , means that at the end of 12 months i.e. over a 12 month period , the sales should have increased by 5 % ; if we start with 12,000 , after one year , the sales should be 12,600.


For the increase of 600 to happen over 12 months , there should be an increase of 50 every month ; so , if the 5 % increase is calculated for the whole year and the increase is distributed or spread over 12 months , this is not compounding.


Compounding is when the increase every month keeps increasing , since the extra every month is used to calculate the extra for the next month ; if we start with a base of 12,000 , and the increase is 50 in the first month , then the increase for the second month is calculated as 5 % of ( 12,000 + 50 ) ; if this happens for every month , this is compounding.


Narayan
 
You must log in or register to reply here.
Back
Top