Dynamic Trajectory

[holdaway]

I'm hoping that you might be able to point me in the right direction because I'm giving myself a migraine trying to work out if this is possible, and how to implement it.

Essentially I'm trying to map a trajectory against previous years' values, although these are adjusted to take into account an increased total (for example, 2020 total of 96 at 8 per month, but an expectation to complete 120 in 2021, theoretically 12 per month). The issue I have is that if this target is exceeded in any given month, it should reduce the following months accordingly, but while I can calculate this in a separate table or as a function between individual lines, I just can't get my head around how to refer to this dynamically, if it's even possible.

Apologies, I appreciate that this doesn't give you a lot to go on, but I'm struggling to think how to show this on a spreadsheet, so I thought I'd start with a description and take it from there.

I really appreciate any help or guidance you can provide - feel free to prompt me with any questions to clarify my ask!

 

[Chrisdontm]

Can you attach a dropbox link of an excel sheet with the problem on it...?

 

[holdaway]

Hi Chris,
Apologies for the slow reply - I've attached a draft 'working' sheet to explain my thinking / aim.

So to explain, I have data from previous years (2019 & 2020, Individual columns), along with a target for 2021 (750) which I've used to calculate approximate weekly forecast targets. However, what I'd ideally need to do is where the 2021 figure has exceeded the forecast, factor this into the rest of the forecast (e.g., forecast against 2019 figures was 194, and against 2020 was 76, but the actual figure was 225).

Writing this down, I'm wondering if I need to calculate each row individually, taking into account the accumulative numbers to date... going to have a tinker, but would appreciate your thoughts while I do!

Trajectory Calculations (working).xlsx
	A	B	C	D	E	F	G	H	I
1		2019		2020		2021
2		Individual	Accumulative	Individual	Accumulative	Forecast (2019)	Forecast (2020)	Individual	Accumulative
3	Week 1	180	180	80	80	194	76	225	225
4	Week 2	95	275	150	230	103	143	150	375
5	Week 3	100	375	125	355	108	119
6	Week 4	65	440	90	445	70	86
7	Week 5	20	460	50	495	22	48
8	Week 6	90	550	20	515	97	19
9	Week 7	20	570	105	620	22	100
10	Week 8	30	600	50	670	32	48
11	Week 9	35	635	40	710	38	38
12	Week 10	15	650	25	735	16	24
13	Week 11	20	670	30	765	22	29
14	Week 12	15	685	15	780	16	14
15	Week 13	10	695	5	785	11	5
16	TOTAL	695		785		751	749	375
17
18
19	2021 Target	750
20	2019 Gap	1.0791367
21	2020 Gap	0.955414
Sheet1

Cell Formulas
Range	Formula
F3:F15	F3	=ROUND(B3*$B$20,0)
G3:G15	G3	=ROUND(D3*$B$21,0)
B16,F16:H16,D16	B16	=SUM(B3:B15)
B20	B20	=B19/$B$16
B21	B21	=B19/$D$16

 

[holdaway]

I figured it out (I think). Probably not the most elegant solution, but it seems to work:

• In L1, if there is an entry in the equivalent 2021 'Individual' row (column P), it uses that. If not it multiplies the 2019 value by the ratio to bring it in line with the 2021 target
• In L2 and below, it also uses values in P if present, if not it calculates using an adjusted ratio (column D, which adjusts the weekly percentages in line with the amount of weeks remaining)

I think the next step is to calculate any 'gaps', where the figure in column P doesn't meet the forecast. Not essential but would help avoid identify weeks that are still expected to grow in terms of figures.

Trajectory Calculations (working).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		2019					2020					2021
2		Individual	%	Adj %	Accumulative		Individual	%	Adj %	Accumulative		Forecast (2019)	Acc (2019)	Forecast (2020)	Acc (2020)	Individual	Accumulative
3	Week 1	180	26%	26%	180		80	10%	10%	80		225	225	225	225	225	225
4	Week 2	95	14%	18%	275		150	19%	21%	230		150	375	150	375	150	375
5	Week 3	100	14%	24%	375		125	16%	23%	355		89	464	84	459		375
6	Week 4	65	9%	20%	440		90	11%	21%	445		58	522	61	520		375
7	Week 5	20	3%	8%	460		50	6%	15%	495		18	540	34	554		375
8	Week 6	90	13%	38%	550		20	3%	7%	515		80	620	14	568		375
9	Week 7	20	3%	14%	570		105	13%	39%	620		18	638	71	639		375
10	Week 8	30	4%	24%	600		50	6%	30%	670		27	665	34	673		375
11	Week 9	35	5%	37%	635		40	5%	35%	710		31	696	27	700		375
12	Week 10	15	2%	25%	650		25	3%	33%	735		14	710	17	717		375
13	Week 11	20	3%	44%	670		30	4%	60%	765		18	728	20	737		375
14	Week 12	15	2%	60%	685		15	2%	75%	780		13	741	10	747		375
15	Week 13	10	1%	100%	695		5	1%	100%	785		9	750	3	750		375
16	TOTAL	695	100%				785	100%				750		750		375
17
18
19	2021 Target	750
20	2019 Gap	1.0791367
21	2020 Gap	0.955414
Sheet1

Cell Formulas
Range	Formula
C3:C15	C3	=B3/$B$16
D3,I3	D3	=C3/1
E3,J3	E3	=B3
D4	D4	=C4/(1-C3)
E4:E15,J4:J15	E4	=B4+E3
D5,I5	D5	=C5/(1-SUM(C3:C4))
D6,I6	D6	=C6/(1-SUM(C3:C5))
D7,I7	D7	=C7/(1-SUM(C3:C6))
D8,I8	D8	=C8/(1-SUM(C3:C7))
D9,I9	D9	=C9/(1-SUM(C3:C8))
D10,I10	D10	=C10/(1-SUM(C3:C9))
D11,I11	D11	=C11/(1-SUM(C3:C10))
D12,I12	D12	=C12/(1-SUM(C3:C11))
D13,I13	D13	=C13/(1-SUM(C3:C12))
D14,I14	D14	=C14/(1-SUM(C3:C13))
D15,I15	D15	=C15/(1-SUM(C3:C14))
H3:H15	H3	=G3/$G$16
I4	I4	=H4/(1-I3)
M3,O3	M3	=L3
M4:M15,Q4:Q15,O4:O15	M4	=L4+M3
B16:C16,P16,N16,L16,G16:H16	B16	=SUM(B3:B15)
L3	L3	=IF(ISBLANK(P3),ROUND(B3*$B$20,0),P3)
L4:L15	L4	=IF(ISBLANK(P4),ROUND((750-M3)*D4,0),P4)
N3	N3	=IF(ISBLANK(P3),ROUND(G3*$B$21,0),P3)
N4:N15	N4	=IF(ISBLANK(P4),ROUND((750-O3)*I4,0),P4)
B20	B20	=B19/$B$16
B21	B21	=B19/$G$16