Forecasting sales volume totals

[Art]

Hi,

I know this is pretty basic, and yet I'm stumped!

I've got a sales volume worksheet. I want to project quarterly sales
volume using a MONTHLY growth rate. I'd like to do this without
creating separate cells for each month--I just want the quarterlies.

EXAMPLE:

A1= Month 1 Sales = 25
A2= Monthly Growth Rate = 5%
A3= Total sales at end of month 3 = ?
A4= Total sales at end of month 6 = ?

Thanks for your help!

 

[Annabelle]

Although there may be a cleaner way, but try this:

A1= $25.00
A2= 5.00%
A3= =SUM((A1*A2)+A1)*3 [$78.75]
A4= =SUM((A1*A2)+A1)*6 [$157.50]

 

[Guest]

Hi Annabelle,

Thanks very much for your response!

This would be perfect if I wasn't compounding the sales increase *each* month. The total sales after month 3 should be 78.81, and I can easily figure this out if I calculate each month in a separate cell:

Month 1 = 25
Month 2 = 25 X 1.05 = 26.25
Month 3 = 26.25 X 1.05 = 27.56
Total = 78.81

My challenge here is to figure each quarter's sales in just one cell, instead of three (I could use hidden cells, I know, but I want to see if there's some nice clean mathmatical way to do this). I know there must be!

Have any idea about how I'd factor in a monthly increase of X% into my quarterly sum?

Thanks!

Art

 

[Arthur]

Art, try the FV function. For example:

A1 25 (rangename: Sales1)
A2 .05 (rangename: int)
A5 "Qtr"
B5 "Sales"
A6 1
A7 2
A8 3
A9 4

B6 =-FV(int,A6*3,Sales1,0,0)
B7 =-FV(int,A7*3,Sales1,0,0)-SUM(B$6:B6)

Copy down B7 to the rest of column B.

I'm a Mooney driver in Portland. How about you?

Arthur

-----Original Message-----
Hi Annabelle,

Thanks very much for your response!

This would be perfect if I wasn't compounding the sales

increase *each* month. The total sales after month 3
should be 78.81, and I can easily figure this out if I
calculate each month in a separate cell:

Month 1 = 25
Month 2 = 25 X 1.05 = 26.25
Month 3 = 26.25 X 1.05 = 27.56
Total = 78.81

My challenge here is to figure each quarter's sales in

just one cell, instead of three (I could use hidden cells,
I know, but I want to see if there's some nice clean
mathmatical way to do this). I know there must be!

 

[Guest]

Hi Arthur,

Sure enough, that does the trick! I had played with the FV function, but wasn't using your clever method!

Many thanks!

I'm alas, not a flyboy myself, just a wishful onlooker. All my cockpit hours come in the form of time spent behind the controls of MS Flight Simulator, I'm afraid!

Which Portland do you fly out of--ME, or OR? Until this June I lived in Medford (OR) for about 10 years, but have returned to the Bay Area for work, family, etc. I miss having the airport eight minutes from my home!

Thanks again!

Art