Replicating numbers instead of cascade

L

labart

New Member
Joined
Sep 4, 2015
Messages
14
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 1year 2year 3year 4year 5year 6year...
Profits51719202527
Opens
Centre 1year 151719202527
Centre 2year 35171920
Centre 3year5517
Centre 4year65
Centre ...99

<tbody>
</tbody>

Any ideas?
 
Sort by date Sort by votes
Thanks Gaz_Chops,

Not quite. Here is an example of what I'm after (using HTMLMaker, again thanks for the recommendation):

The highlighted area at the bottom is what I'm trying to replicate without having to do multiple cascades of summations.

Excel 2012
ABCDEFGHIJK
1Master Store P&L
2year12345678
3Sales56789101112
4Costs-3-3-3-3-3-3-3-3
5GM23456789
6Operating Costs-5-5-5-5-5-5-5-5
7EBITDA-3-2-101234
8
23Store opening "Key"
24Storeyear Start
251111111111
262111111111
273300111111
284400011111
295500001111
306500001111
317800000001
32
33Consolidated Stores P&Ls
34Sales1012192741475865
35Costs-6-6-9-12-18-18-21-21
36GM46101523293744
37Operating Costs-10-10-15-20-30-30-35-35
38EBITDA-6-4-5-5-7-129
Sheet1
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Are your consolidated numbers correct?

Code: 
[TABLE="width: 715"]
<tbody>[TR]
[TD="colspan: 2"]Master Store P&L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[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]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/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]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/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="colspan: 2"]Store opening "Key"[/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"]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="align: right"]2[/TD]
[TD="align: right"]1[/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="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/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]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/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]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/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="colspan: 2"]Consolidated Stores P&Ls[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-21[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I believe so.

Here is an example of the sales line using the cascade method:



Excel 2012
ABCDEFGHIJK
1Master Store P&L
2year12345678
3Sales56789101112
4Costs-3-3-3-3-3-3-3-3
5GM23456789
6Operating Costs-5-5-5-5-5-5-5-5
7EBITDA-3-2-101234
8
9
10
11
12Cascade Trial
13Storeyear Start
141156789101112
152156789101112
1633005678910
174400056789
185500005678
196500005678
207700000056
21
22Total1012192741475865
Sheet1
Cell Formulas
RangeFormula
D14=OFFSET($D$3,,IF(D$2<$B14,-1,D$2-$B14))
 
Upvote 0
OK somehow i had a different year start for store 7!

Try
In cell D25 copy down & across
=IF($B25<=D$2,1+C25,0)

In cell D34 copy down & across

=SUMPRODUCT((D$25:D$31=$D$2:$K$2)*($D3:$K3))

Code: 
[/FONT][TABLE="width: 715"]
<colgroup><col span="11"></colgroup><tbody>[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/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]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/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="colspan: 2"]Store opening "Key"[/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"]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="align: right"]2[/TD]
[TD="align: right"]1[/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="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/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]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/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]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/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="colspan: 2"]Consolidated Stores P&Ls[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-35[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Lucida Grande]
 
Upvote 0
Gz_Chops,

Sorry - my bad in year 7. I had different numbers in my cascade vs. the Key.

Either way, as Archimedes proclaimed on discovering how to measure volume while submerging himself in a bath: EUREKA!

Thank you ever so much for your assistance. This works perfectly. And that, without the need for an array. Perfect.


Now, I'll have to figure out how the equation works... :confused:

Thanks again
 
Upvote 0
You're welcome, glad we got there before the football starts :)

This is an array formula (enter with Ctrl, Shift Enter), it does away with data in the key table.

=SUMPRODUCT((IF($B$25:$B$31<=D$2,COLUMN(A1)-$B$25:$B$31+1,0)=$D$2:$K$2)*($D3:$K3))
Code: 
[TABLE="width: 715"]
<tbody>[TR]
[TD="colspan: 2"]Master Store P&L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[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]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/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]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/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="colspan: 2"]Store opening "Key"[/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][/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][/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][/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][/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][/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][/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][/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="colspan: 2"]Consolidated Stores P&Ls[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-35[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]


The formula works by matching the number in D25:D31 with the year in row 2, then multiplies each 1 by the value in Rows 3-7.
 
Last edited:
Upvote 0
Very interesting.

Would there be any way of making this into a 3D array as follows. Big stores and small stores. Perhaps with an indirect in the formula?

StoreTypeyear entry
111
221
323
415
516
617
717

<tbody>
</tbody>

Perhaps by having the model stores in identical tabs called store1 and store2. Then using indirect?
 
Upvote 0
gaz_chops said:
I don't follow what you mean!
Click to expand...


All right here is the same example as before. I have put in a cascade again to illustrate what I'm after. The only difference from the previous example is that I would like to sum take the P&L figure from the different tabs depending on whether the store is a big store or a small store.
Excel 2012
ABCDEFGHIJK
1Year
21234567
3StoreModelYear
41Small13456789
52Big1681012141618
63Big30068101214
74Big30068101214
85Small40003456
96Small50000345
107Small60000034
118Big70000006
12
13Sales9122737486076

<tbody>
</tbody>
Key​

Worksheet Formulas
CellFormula
E4=OFFSET(INDIRECT("Store"&$B4&"!$D$3"),,IF(E$2<$C4,-1,E$2-$C4))

<tbody>
</tbody>

<tbody>
</tbody>
Excel 2012
ABCDEFGHIJ
2year1234567
3Sales681012141618
4Costs-7-512345
5GM-131114172023

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


Excel 2012
ABCDEFGHIJ
2year1234567
3Sales3456789
4Costs-5-312345
5GM-2168101214

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
StoreSmall​
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Excel VBA Open Word Doc and Save as New Word Doc
Replies
1
Views
127
NdNoviceHlp
NdNoviceHlp
Cubist
  • Question
Pivot Table with Overlapping Row
Replies
0
Views
105
Cubist
Cubist
S
  • Question
Check if a previously drawn number is in a group.
Replies
1
Views
275
Excel Novice 1
E
J
  • Question
Sumproduct with different array sizes
2
Replies
19
Views
465
jkj115
J
Chris_010101
  • Question
Filter Data?
2
Replies
16
Views
1K
kevin9999
K

Forum statistics

Threads
1,215,193
Messages
6,123,560
Members
449,108
Latest member
rache47

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