Exponentially increasing Weekly Goal % where the sum at the end of the year equals 100%

K

kevsvette

New Member
Joined
Oct 5, 2015
Messages
24
This has been bugging me for some time now.

How do you calculate a exponentially increasing Weekly Goal %, over a 52 week year, that sums to 100% at the end of the year.

I don't have a particular starting point... just know the sum of the 52 week percentages has to equal 100%.

This % is used to calculate the weekly goal to meet the annual goal at year end. Lets say the annual goal is 10,000. You can't divide that by 52 weeks it needs to exponentially increase due to the type of work that were dealing with.

I unfortunately cannot add any attachments. Any help will be appreciated.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
I think you're mixing up some terminology, and your overall goal isn't clear. But let's say your starting weekly goal is x, and each week the goal must increase by y percent, and at the end of 52 weeks you want the sum of the weekly goals to be 10,000. This leads to an equation with 2 unknowns (x and y), so therefore results in an infinity of possible answers. You can set something up like this however:

ABCD

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]42.94497[/TD]
[TD="align: right"]0.05[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]45.09221[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]47.34682[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]49.71417[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]52.19987[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]54.80987[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]57.55036[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]60.42788[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]63.44927[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]66.62174[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]69.95282[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]73.45046[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]77.12299[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]80.97914[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]85.02809[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]89.2795[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]93.74347[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]98.43065[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]103.3522[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]108.5198[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]113.9458[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]119.6431[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]125.6252[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]131.9065[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]138.5018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]145.4269[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]152.6982[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]160.3332[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]168.3498[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]176.7673[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]185.6057[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]194.8859[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]204.6302[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]214.8618[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]225.6048[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]236.8851[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]248.7293[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]261.1658[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]274.2241[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]287.9353[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]302.3321[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]317.4487[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]333.3211[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]349.9872[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]367.4865[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]385.8609[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]405.1539[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]425.4116[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]446.6822[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]469.0163[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]492.4671[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]517.0904[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=SUM(A1:A52)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=A1*(1+B$1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put a guess for your first week goal in A1, then put the A2 formula in and drag down to A52. Put a guess for the percentage in B1, and put in the C1 formula. Now go to the Data tab > What-if Analysis > Goal Seek. Set Cell: C1, To value: 10000, By changing cell: A1. This will tell you what the starting point must be, given the percentage in B1. Or pick your starting point in A1, and run Goal Seek again, but by changing cell B1. This will give you the percentage to grow by given your starting point.

But this is still a guess as to what you want. This is geometric growth, not exponential. And with a 5% growth rate, this requires a goal of 43 in week 1, and a goal of 517 in week 52. Does the capacity really grow that much in a year? Perhaps if you could provide a few more details about what you want, we could come up with something.
 
Upvote 0
kevsvette said:
How do you calculate a exponentially increasing Weekly Goal %, over a 52 week year, that sums to 100% at the end of the year.
Click to expand...

The compounded weekly percentage is =(1+100%)^(1/52) - 1. That evaluates to about 1.34189906987003% (+3.99E-17, to be precise).

kevsvette said:
just know the sum of the 52 week percentages has to equal 100%.
Click to expand...

With the compounded rate above, the product -- that is, (1+weeklyRate)^52 - 1 -- is 100%, not the "sum" [sic].

If you want the sum to be 100%, you would be talking about a simple weekly rate of 100%/52.

But you say: ``You can't divide that by 52 weeks it needs to exponentially increase``

So I do not believe you do not want the "sum" per se to be 100%.

kevsvette said:
I don't have a particular starting point [....] Lets say the annual goal is 10,000
Click to expand...

With an ending balance of 10,000, the beginning balance is =PV(B1, 52, 0, -10000) in B2, where B1 is the compounded weekly rate.

That evaluates to about 4999.99999999999 (-9.09E-13, to be precise).

Then the weekly balances can be determined as follows.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]weekly rate[/TD]
[TD="align: right"]1.341899%[/TD]
[TD="align: right"]addition[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]beg bal[/TD]
[TD="align: right"]5,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]week 1[/TD]
[TD="align: right"]5,067.09[/TD]
[TD="align: right"]67.09[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]week 2[/TD]
[TD="align: right"]5,135.09[/TD]
[TD="align: right"]68.00[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]week 3[/TD]
[TD="align: right"]5,204.00[/TD]
[TD="align: right"]68.91[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]52[/TD]
[TD]week 50[/TD]
[TD="align: right"]9,736.93[/TD]
[TD="align: right"]128.93[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]53[/TD]
[TD]week 51[/TD]
[TD="align: right"]9,867.59[/TD]
[TD="align: right"]130.66[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]54[/TD]
[TD]week 52[/TD]
[TD="align: right"]10,000.00[/TD]
[TD="align: right"]132.41[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5,000.00[/TD]
[TD]total[/TD]
[/TR]
</tbody>[/TABLE]
Code: 
Formulas:
B1:  =(1+100%)^(1/52) - 1
B2:  =PV(B1,52,0,-10000)
B3:  =B2*(1+$B$1)
C3:  =B3-B2
C55: =SUM(C3:C54)
Copy B3:C3 into B4:C54

Note that the sum of the weekly additions is 100% of the beginning balance.

Note: The values shown in the tables are rounded, but the calculations are not.
 
Last edited:
Upvote 0
On second thought, I agree with EricW: your description is unclear.

kevsvette said:
How do you calculate a exponentially increasing Weekly Goal %, over a 52 week year, that sums to 100% at the end of the year.
Click to expand...

If you mean ``given an annual yield of some rate, what is the compounded weekly rate?``, you can use:

= (1 + annualRate)^(1/52) - 1

kevsvette said:
I don't have a particular starting point [....] This % is used to calculate the weekly goal to meet the annual goal at year end. Lets say the annual goal is 10,000.
Click to expand...

If you mean that given the compounded weekly rate calculated above, you want to determine the fixed weekly amount that, when added to a beginning balance, results in 10,000, you can determine the weekly amount as follows:

=PMT(weeklyRate, 52, begBalance, -10000)

where begBalance is zero or some positive amount.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Revenue Allocation by Week
Replies
3
Views
694
ChetanPuri
C
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
T
  • Solved
VBA - Problem with Coding controls for values based on Dates
Replies
3
Views
789
NdNoviceHlp
NdNoviceHlp
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
964
UnitedCloud01
U

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top