Solved

500 points if you create the Excel formula for this.

Posted on 2004-03-30
19
512 Views
Last Modified: 2012-06-27
Let's do some simple rounding, to make this rather complex question easier to answer.

I track my company's monthly income in an Excel spreadsheet where Column A contains the months (dating back to January of '03), and Column B contains the amounts.  For example, A1=01/03, A2=02/03, B1=2000, B2=2100, A72=12/08, etc.
Let's say my company made $5,000 in March (so B15=5000).
And let's say I have a goal for my company to make $1,000,000 in the calendar year 2008, so SUM(B61..B72) is 1,000,000.
By what consistent percentage will I have to increase my company's income every month to be able to hit $1,000,000 in 2008?

I want to be able to enter a formula into an Excel spreadsheet so that I can change the goal, and the target date to achieve that goal.

I look forward to your answers!
0
Comment
Question by:neilhedley
  • 5
  • 3
  • 3
  • +5
19 Comments
 
LVL 5

Accepted Solution

by:
PointyEars earned 74 total points
ID: 10717266
If the income of last month (in B15) was X and your goal is Y (1,000,000) in N months (72 - 15), it means that you need to extract the percentage P from the following formula:
   X * (1 + P)^N = Y

If you divide both sides by X, take the natural logarithms, and use the logarthm property that
  ln(a^b) = b * log(a)
you have:
  N * ln(1 + P) = ln(Y/X)

Now you need to move N to the right and take the exponential of both sides, because it is the inverse function of the natural logarithm:
  exp(ln(1 +P)) = exp(ln(Y/X)/N)
which finally gives you:
  P = exp(ln(Y/X)/N) - 1

In Excel this formula is written as follows:
  =EXP(LN(Y/X)/N)-1
where Y and X are the cells where your goal and the March revenue are stored.  Obviously, you can also store your goal directly in the formula:
  =EXP(LN(1000000/B15)/N) - 1

If you want to have a parametric N, you can use the function COUNTA(cell-range) which counts the non-empty cells in a range.  Therefore, COUNTA(A:A) would count the months.  Another useful function in this context might be ROW(reference) which gives you the row of a cell.  Therefore:
  COUNT(A:A) - ROW(B15)
would calculate for you the number of months left (72 - 15)

There are perhaps financial functions which would be useful, but I am familiar with the Math functions...
0
 

Author Comment

by:neilhedley
ID: 10717423
Thanks for the answer, and the explanation.  I appreciate the time you took in putting it together.

However, your formula gives me the percentage if I wanted to reach $1,000,000 in the month of December 2008, not $1,000,000 for the entire year.

While a million dollar MONTH would be *wonderful*, my example calls for an ANNUAL goal based on a consistent MONTHLY increase.

Is there a tweak?  Call me wacky, but I don't know that simply dividing or multiplying something (I don't know what) by 12 would throw the numbers off, even if only marginally.
0
 
LVL 84

Assisted Solution

by:ozo
ozo earned 71 total points
ID: 10718812
For the year
Y=X((1+P)^12-1)/P
0
 
LVL 84

Expert Comment

by:ozo
ID: 10718858
Sorry, you want the year  45 months from now;
Y=X((1+P)^57-(1+P)^45)/P
0
 
LVL 5

Expert Comment

by:PointyEars
ID: 10719320
You are right about my formula, 1M$/month would be much harder!

You could simply divide 1,000,000 by 12 and use that as a target in my formula.  It would tell you what monthly increase you should sustain till end 2008 in order to get a monthly income that would correspond to a 1,000,000/year.
Obviously this would mean that in 2008 you would not yet earn a 1,000,000.  Roughly, if you set to earn 8,333$/month for June 2008, you would get close to 1M$ for the whole year (just a bit less).

ozo, your formula is equivalent to saying that the increase in monthly earning in 2008 (i.e. the difference between the earnings of Dec 2008 and Dec 2007) equals Y * P.  I would like to understand it.  Can you explain it please?  Is it an approximation?
0
 
LVL 84

Expert Comment

by:ozo
ID: 10719422
It is not an approximation.  It comes from the formula for geometric progressions:
Sum(N=1..M:X*(1+P)^N) = X * (1-(1+P)^N)/(1-(1+P))
0
 

Author Comment

by:neilhedley
ID: 10719813
Here's what I've done so far:

I built out the spreadsheet I described so that I set a cell as "rate", then I multiply B1..B72 by rate, using SUM(61..72) as the total for calendar year 2008.  Then I use "trial and error" in the "rate" cell to arrive at the correct percentage, and I've figured out the correct percentage to 7 decimal places.

But that only works for one month.  I want a formula I can plug in each month, so I can set the bar for the following month at a glance.

PointyEars, dividing by 12 sets me up for a 2008 where I would have no "target growth" from Jan-08 thru Dec-08.

ozo, your suggestion sounds intriguing, but putting that into an Excel formula is over my head.  Can you help me with that end of it, and I'll plug it in and see if it works?
0
 

Author Comment

by:neilhedley
ID: 10719825
By the way, folks, feel free to tell me that what I'm looking for is completely circular and can't be solved in a simple Excel statement, but I have a hunch that's not the case.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 5

Expert Comment

by:PointyEars
ID: 10721720
ozo, silly of me to ask.

The formula for the sum of a geometric series is from N=0 to N=M-1, not from N=1 to N=M as you said.  But even if we had to slightly modify your formula, we still wouldn't have helped him: we should extract P from it and, I confess, I don't know how to do that.

Further, the fact that neilhedley calculated something to 7 significant digits suggests to me that [s]he wouldn't be satisfied with an approximate formula (say, expanding the powers to polynomial and only take the terms to P^2)...

I think I give up...  Sorry...
0
 
LVL 16

Assisted Solution

by:PaulCaswell
PaulCaswell earned 71 total points
ID: 10721770
How about this,

We have this months figure, say 'x'. We want to achieve a year sometime in the future (say 'n' months) a total of a value 'G', your goal.

To keep it simple we will cut out compound growth. If we consistently increase by 'p' percent of THIS months figure every month from now on, the amount we will get 'n' months from now should be x + (n * ( x * p )). So the year starting with that month will total to 12 * the average for that year. We are assuming linear growth so the average for that year should hit on month 6 so we can get the years total from (x + ((n + 6) * ( x * p ))) * 12. We therefore want to choose p such that your goal is achieved.

(x + ((n + 6) * ( x * p ))) * 12 = G

my schoolboy maths suggests

p = (((G/12)-x)/(n+6))/x

If you want compound growth ask someone else.

0
 
LVL 6

Assisted Solution

by:Sergio_Hdez
Sergio_Hdez earned 71 total points
ID: 10722091
first I will put name to some amounts allready used in this discussion, and as it has been stated how to get them from the excel sheet, I suppose it is allready available to your formula:

Initial month you are earning X$ (5000 in your example), then you pass m months (61 - 15 = 46 in your example) multiplying incomes by P (P is unknow, but P>1 luckly! having P like a factor >1 avoid using (P+1) everywhere), then we reach beginning of the year we want to sum incomes, in the following 12 months, you still increase incomes by P and sum of incommings must be Y ( = 1,000,000$ on the example)

OK, now that variables are clear, lets follow this:

Initial monthly incomes are X$, following month increase to X*P, next to X*P*P, and so on, so after waitting for m month, monthly incomes are (X*P^m) on january 2003.

So we start year 2008 earning (X*P^m) , and end up earning (X*P^(m+11)) at the last month (we will call it I as I-nitial amount of the 2008 year) of this year, so the sum is calculated as any other geometrical serie, calling I = (X*P^m) and being P the factor, so SUM(I, I*P, ... , I*P^11) equals I*(1-P^12)/(1-P).

Now we have the problem clearly stated:

I:=  (X*P^m) (having X = 5000)
I*(1-P^12)/(1-P) = Y (having Y=1,000,000)

Melting both xpressions we come to our finall equation (remember X and Y are constants):

X* (P^m) * (1-P^12) / (1-P) = Y

Now I try to  make it simplier (hard stuff):

(P^m) * (1-P^12) / (1-P) = Y/X (divide by X both sides)

(P^m) * (1-P^12) = (Y/X) * (1-P)

P^m - P^(m+12) = (Y/X) - (Y/X)P

P^m - P^(m+12) + (Y/X)P - (Y/X)  = 0

Finally we have our problem as a (m+12) grade polinomic to be solved (find the point where it becomes zero), and there is no generic way to calculate it directly (as m+12 is always > 5, the biggest number for witch there is a direct formula for polinomials).

So I think THERE IS NO direct way to solve the problem, unless this particular polinomic formula can be solved in some "intuitive way"... but I can't figure out how to solve this formula directly by now!

The most simple way to look at this formula is like this (changing all variables by the real values and name variable as X to look more familiar to all):

X^46 - X^58 + 200*X -200 = 0        

And here is where race ends... I can't solve this at first glance... may be if you give that formula to mathematica or mapple and ask it to simplify, may be you get a solution, other way, you need to use solver or similar in excel.
0
 
LVL 5

Expert Comment

by:PointyEars
ID: 10724821
I have to agree with Sergio_Hdez.  I tried to approximate the formula by re-introducing (1 + p) with p < 1 and neglecting the powers of p greater than 2, but there are too many months between now and the end of 2008 and the exponential growth "runs away" from my approximation.

Therefore, I join Sergio_Hdez in saying that no solution is possible!
0
 
LVL 11

Assisted Solution

by:lbertacco
lbertacco earned 71 total points
ID: 10725571
Sergio Hdez, I arrived at your same final formula. I prefer to keep at as (P^m) * (1-P^12) / (1-P) - Y/X =0.
Then to solve this just use the numerical method of execl (goal seek).
Put a guessed valued of P in cell, e.g. in C1 enter 1,05
then enter (e.g. in C2) the formula but replacing P with refences to C1
finally do menu->tools->goal seek
set cell C2
to value 0
by changing cell C1
After this you get the proper growth rate in C1 and you can use it to calculate each month expected income.


0
 
LVL 5

Assisted Solution

by:ChadClancy
ChadClancy earned 71 total points
ID: 10726347
See my proposed solution that describes how to automate Goal Seek at:

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_20938891.html
0
 
LVL 2

Assisted Solution

by:nsstone
nsstone earned 71 total points
ID: 10797288
The graphic flexibility of Excel is the key here.  Let Excel do the work.
  1. Start by projecting out the years in the first row, month by month, leaving a couple of columns free at the beginning.
  2. In the first open row of the March 2004 column, place the formula:
       = 1*A2
  and extend the formula across the row for as many years as you want.
  3. In A2 enter "1.0558", or any other estimate.
  4. In each December column, create a cell that subtotals that year alone. ( If you want to make 1M, from 5000, then you want the year 2008 subtotal to equal 200).
  5. Tweak A2. I also created a second row so I could look at differences between estimates. It took about four minutes to get 1000000.004 for 2008 (@1.0557902558/month).
  6. This method allows you to insert values anywhere in the calendar. You just have to project out from any hard-value insertion point.
  7. Obviously, if in E2 you used the formula
             =5000*A2
you would be looking for a 1000000 subtotal in 2008.
0
 
LVL 5

Expert Comment

by:PointyEars
ID: 10976258
I would just split the points amongst all those that have given a direct contribution to answering the question (in order of first comment): PointyEars, ozo, Sergio_Hdez, lbertacco, nsstone, and perhaps ChadClancy.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Complex Numbers are funny things.  Many people have a basic understanding of them, some a more advanced.  The confusion usually arises when that pesky i (or j for Electrical Engineers) appears and understanding the meaning of a square root of a nega…
Lithium-ion batteries area cornerstone of today's portable electronic devices, and even though they are relied upon heavily, their chemistry and origin are not of common knowledge. This article is about a device on which every smartphone, laptop, an…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now