Replicating numbers instead of cascade

[labart]

I'm in the process of setting up a model based on a simple p&L centre. The idea is to model out one centre (something like a franchise store) and each time we open another store it starts at the beginning of the model. I could do a cascade (as illustrated below) but that is not very elegant and rather unwieldy as I'm looking on adding quite a few P&L lines.

I was hoping that there was some sort of array that I could use?

		year 1	year 2	year 3	year 4	year 5	year 6	year...
Profits		5	17	19	20	25	27
	Opens
Centre 1	year 1	5	17	19	20	25	27
Centre 2	year 3			5	17	19	20
Centre 3	year5					5	17
Centre 4	year6						5
Centre ...99

<tbody>
</tbody>

Any ideas?

 

[labart]

To clarify I have simplified the example above. Please help!

What I'm looking for is a way of eliminating the cascade.

		year 1	year 2	year 3	year 4	year 5
Profits		5	17	19	20	25
	Opens
Centre 1	year 1	5	17	19	20	25
Centre 2	year 3			5	17	19
Centre 3	year5					5
Centre 4	year5					5​
Total		5	17	24	37	54

<tbody>
</tbody>

What I'm looking for is an formula that skips the waterfall step and totals it. The actual number of stores is in the hundreds and there are quite a few P&L lines therefore making a cascade very cumbersome.

I was thinking of a some sort of sum product array, but I can't seem to get it.

Thanks in advance for your help.

 

[labart]

Here is one of my unsuccessful trials. I used the formula =SUMPRODUCT(D16:D22*OFFSET($D$2,,IF(D$1<$B5,-1,D$1-$B5))) -- It works for the first two stores, but it goes wrong after once a store with a different start date is added...

I've added both the cascade and the sumproduct equation to show that it does not work.

A1​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
2​	​	​	​	​	​	​	​	​	​	​	​
3​	​	​	​	1​	2​	3​	4​	5​	6​	7​	8​
4​	​	P&L	​	5​	34​	435​	32466​	7254​	4357​	4536​	43​
5​	​	​	​	​	​	​	​	​	​	​	​
6​	Store​	year Start​	​	OFFSET($D$2,,IF(D$1<$B5,-1,D$1-$B5))	​	​	​	​
7​	1​	1​	​	5​	34​	435​	32466​	7254​	4357​	4536​	43​
8​	2​	1​	​	5​	34​	435​	32466​	7254​	4357​	4536​	43​
9​	3​	3​	​			5​	34​	435​	32466​	7254​	4357​
10​	4​	4​	​				5​	34​	435​	32466​	7254​
11​	5​	5​	​					5​	34​	435​	32466​
12​	6​	5​	​					5​	34​	435​	32466​
13​	7​	7​	​							5​	34​
14​	​	​	​	​	​	​	​	​	​	​	​
15​	​	Total		10​	68​	875​	64971​	14987​	41683​	49667​	76663​
16​	​	​	​	​	​	​	​	​	​	​	​
17​	Store​	year Start​	​	SumProduct Trial	​	​	​	​	​	​
18​	1​	1​	​	1​	1​	1​	1​	1​	1​	1​	1​
19​	2​	1​	​	1​	1​	1​	1​	1​	1​	1​	1​
20​	3​	3​	​			1​	1​	1​	1​	1​	1​
21​	4​	4​	​				1​	1​	1​	1​	1​
22​	5​	5​	​					1​	1​	1​	1​
23​	6​	5​	​					1​	1​	1​	1​
24​	7​	8​	​								1​
25​	​	​	​	​	​	​	​	​	​	​	​
26​	​	​	​	SUMPRODUCT(D16:D22*OFFSET($D$2,,IF(D$1<$B5,-1,D$1-$B5)))	​	​
27​	​	Total		10​	68​	1305​	129864​	43524​	26142​	27216​	301​

<tbody>
</tbody>

 

[labart]

Sorry, I just noticed that I did not update the texted equation when I added the row and columns. Hence the equation should read: "=SUMPRODUCT(E18:E24*OFFSET($E$4,,IF(E$3<$C7,-1,E$3-$C7)))"

 

[gaz_chops]

It's not clear (to me at least) exactly what you want, which of your examples shows what you are trying to achieve?

 

[labart]

It's not clear (to me at least) exactly what you want, which of your examples shows what you are trying to achieve?

I'm new at posting. Apologies, if I wasn't clear. Let me try again.

I have quite a large P&L that is a master store. We are planning on opening new stores over time. Each new store will have the same P&L as the master store but starting on a different date. There may be hundreds of stores over time.

In the cascade above I was able to do this. But with hundreds of stores and numerous P&L lines the sheet becomes quite unwieldy. The toggle for the sum product was a way for me to do this (there might be a better way). I wouldn't mind having one cascade, or key, but would like to avoid having to do the same thing for every P&L line.

Hope that is clearer.

Thanks,

 

[gaz_chops]

It would help if you post an example of the results you want, not what isn't working.

 

[labart]

It would help if you post an example of the results you want, not what isn't working.

What I'm after is the Total in Row 15.

What I'm trying to avoid is having to do a full cascade for every P&L line...

Thanks for your help

 

[labart]

Thanks for the hint on using HTML Maker. Much easier than copying and pasting text.

Here is my example again. What I'm after is a one line equation that replicates d5:d11 and d13. But all in one equation.

Thanks,




Excel 2012

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	year			1	2	3	4	5	6	7	8
2	P&L			5	34	435	32466	7254	4357	4536	43
3	Cascade Trial
4	Store	year Start
5	1	1		5	34	435	32466	7254	4357	4536	43
6	2	1		5	34	435	32466	7254	4357	4536	43
7	3	3		0	0	5	34	435	32466	7254	4357
8	4	4		0	0	0	5	34	435	32466	7254
9	5	5		0	0	0	0	5	34	435	32466
10	6	5		0	0	0	0	5	34	435	32466
11	7	7		0	0	0	0	0	0	5	34
12
13		Total		10	68	875	64971	14987	41683	49667	76663
14	Not working
15	Store	year Start
16	1	1		1	1	1	1	1	1	1	1
17	2	1		1	1	1	1	1	1	1	1
18	3	3		0	0	1	1	1	1	1	1
19	4	4		0	0	0	1	1	1	1	1
20	5	5		0	0	0	0	1	1	1	1
21	6	5		0	0	0	0	1	1	1	1
22	7	8		0	0	0	0	0	0	0	1
23
24
25		Total		10	68	1305	129864	43524	26142	27216	301
Sheet1

Cell Formulas
Range	Formula
D5	=OFFSET($D$2,,IF(D$1<$B5,-1,D$1-$B5))
D13	=SUM(D5:D11)
D16	=IF(D$1>=$B16,1,0)
D25	=SUMPRODUCT(D16:D22*OFFSET($D$2,,IF(D$1<$B5,-1,D$1-$B5)))

 

[gaz_chops]

So it would look like this?

[TABLE="width: 715"]
<tbody>[TR]
[TD]year[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]P&L[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]435[/TD]
[TD="align: right"]32466[/TD]
[TD="align: right"]7254[/TD]
[TD="align: right"]4357[/TD]
[TD="align: right"]4536[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Cascade Trial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store[/TD]
[TD]year Start[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]49130[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]49130[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]44551[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]40194[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]32940[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]32940[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD][/TD]
[TD="align: right"]248924[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]