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

[kevsvette]

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.

 

[Eric W]

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:

	A	B	C	D
1	42.94497	0.05	10000
2	45.09221
3	47.34682
4	49.71417
5	52.19987
6	54.80987
7	57.55036
8	60.42788
9	63.44927
10	66.62174
11	69.95282
12	73.45046
13	77.12299
14	80.97914
15	85.02809
16	89.2795
17	93.74347
18	98.43065
19	103.3522
20	108.5198
21	113.9458
22	119.6431
23	125.6252
24	131.9065
25	138.5018
26	145.4269
27	152.6982
28	160.3332
29	168.3498
30	176.7673
31	185.6057
32	194.8859
33	204.6302
34	214.8618
35	225.6048
36	236.8851
37	248.7293
38	261.1658
39	274.2241
40	287.9353
41	302.3321
42	317.4487
43	333.3211
44	349.9872
45	367.4865
46	385.8609
47	405.1539
48	425.4116
49	446.6822
50	469.0163
51	492.4671
52	517.0904

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C1 =SUM(A1:A52) A2 =A1*(1+B$1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.

 

[joeu2004]

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

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

just know the sum of the 52 week percentages has to equal 100%.

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%.

I don't have a particular starting point [....] Lets say the annual goal is 10,000

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.

	A	B	C	D
1	weekly rate	1.341899%	addition
2	beg bal	5,000.00
3	week 1	5,067.09	67.09
4	week 2	5,135.09	68.00
5	week 3	5,204.00	68.91
52	week 50	9,736.93	128.93
53	week 51	9,867.59	130.66
54	week 52	10,000.00	132.41
55			5,000.00	total

<tbody>
</tbody>

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.

 

[joeu2004]

On second thought, I agree with EricW: your description is unclear.

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

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

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

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.

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.