Formula for sales estimate

[Yippy_Kiyey]

Hello sirs:

My annual sales estimate is say, 50,000.
I need to break this down into 12 months, where it shows a 5% increase each month.

My problem is finding out the 1st month. If possible, I need a single cell formula. If my estimate is in A1, i want to show the answer in A2.

Thank you.

 

[WinteE]

=(50000 / (1.05^12)) * 1.05 = 29,234

 

[Yippy_Kiyey]

Thanks for the quick response. However the 29234 for the first month gave me a total of 465,320 for the year. My estimate is only 50,000.

 

[Richard Schollar]

Hi

You need the formula for the sum of a geometric (finite) series given here:

http://en.wikipedia.org/wiki/Geometric_progression

Which equates to:

=A1*(1-1.05)/(1-1.05^12)

=3141.27

 

[Yippy_Kiyey]

Thank you Richard,

This is perfect and exactly what I am looking for. To be perfectly honest with you, I saw the same thing Wikipedia also but could not convert it into Excel formula. Thank you so much.

 

[Richard Schollar]

You're welcome

In all honesty, i didn't know how to calc this until I started searching. It's something I could well find useful for my own forecasting needs!

 

[Yippy_Kiyey]

Glad I can help. LOL

I am really thankful.

 

[ka_123!]

You need to do subtotals......

GO to

Data> subtotals >each change in (Month)>click OK

 

[jevi]

Hi All:

I am getting a little bit confused, ok we start with 3141.27 at January and then at the end of the year we are at our Target of 50,000. But the Growth rate at the end of each month is not 5%?? at least according to my calculations...so the formula for February would be: January*Growth Rate February+Dec'10=3141*105+3141=6440...sorry it looked interesting to me this formula.


<TABLE style="WIDTH: 845pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1131 border=0><COLGROUP><COL style="WIDTH: 65pt" span=13 width=87><TBODY><TR style="HEIGHT: 20.25pt" height=27><TD class=xl79 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: #1f497d" width=87 height=27></TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>5.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>105.00%</TD></TR><TR style="HEIGHT: 21pt; mso-height-source: userset" height=28><TD class=xl80 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 21pt; BACKGROUND-COLOR: #f2f2f2" width=87 height=28>Dec'10</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2" width=87>January</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2" width=87>February</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">March</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2" width=87>April</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">May</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">June</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">July</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">August</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">September</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">October</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2">November</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2f2f2" width=87>December</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl81 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21> 3,141 </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 3,141 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 6,440 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 9,903 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 13,539 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 17,357 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 21,367 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 25,576 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 29,996 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 34,637 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 39,511 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 44,627 </TD><TD class=xl72 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white"> 50,000 </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl82 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21>% of Growth</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>0.0%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>105%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>54%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>37%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>28%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>23%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>20%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>17%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>15%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>14%</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>13%</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: white" align=right>12%</TD></TR></TBODY></TABLE>

Thnx

 

[Richard Schollar]

I don't know how you have calculated your growth figures.

This is how I implemented the results:

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">50000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Period</td><td style=";">Sales Revenue</td><td style=";">Cumulative</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">1</td><td style="text-align: right;;"> 3,141.27 </td><td style="text-align: right;;"> 3,141.27 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2</td><td style="text-align: right;;"> 3,298.33 </td><td style="text-align: right;;"> 6,439.60 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">3</td><td style="text-align: right;;"> 3,463.25 </td><td style="text-align: right;;"> 9,902.86 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;"> 3,636.41 </td><td style="text-align: right;;"> 13,539.27 </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">5</td><td style="text-align: right;;"> 3,818.23 </td><td style="text-align: right;;"> 17,357.50 </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">6</td><td style="text-align: right;;"> 4,009.15 </td><td style="text-align: right;;"> 21,366.65 </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">7</td><td style="text-align: right;;"> 4,209.60 </td><td style="text-align: right;;"> 25,576.25 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">8</td><td style="text-align: right;;"> 4,420.08 </td><td style="text-align: right;;"> 29,996.33 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">9</td><td style="text-align: right;;"> 4,641.09 </td><td style="text-align: right;;"> 34,637.42 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">10</td><td style="text-align: right;;"> 4,873.14 </td><td style="text-align: right;;"> 39,510.56 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">11</td><td style="text-align: right;;"> 5,116.80 </td><td style="text-align: right;;"> 44,627.36 </td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">12</td><td style="text-align: right;;"> 5,372.64 </td><td style="text-align: right;;"> 50,000.00 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=A1*(<font color="Blue">1-1.05</font>)/(<font color="Blue">1-1.05^12</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=SUM(<font color="Blue">$B$5:$B5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=B5*1.05</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=SUM(<font color="Blue">$B$5:$B6</font>)</td></tr></tbody></table></td></tr></table><br />

Formula in B6:C6 are copied down to row 16.