Store Sales

[mjp]

Str1 Str2 Str3 Str4 Str5
Week 1 1
Week 2 1 1
Week 3 1 1 1
Week 4 1 1 1 1
Week 5 1 1 1 1 1

Wk1 Wk2 Wk3 Wk4 wK5
Markets $20 $10 $8 $7 $6

I have information above in my spreadsheet. The store opens in week 1
and stores in their first week make $20. In week 2, I have one new
store that makes $20 and another and an existing store that makes $10.
In week 3, I have 1 new opening at $20, 1 store open for 1 week at $10,
and 1 store open for 2 weeks which now makes $8. This continues in the
same fashion for week 4 and week 5.

This is easy to calculate if the charts stay in this form, but I want
to be able to experiment with when stores open and close. So whether I
open a store in week 1 or week 5 the first week it is open it makes $20,
the second 10, the next $8, etc.

Any suggestions?

Thanks!
Mark.

 

[Roger Govier]

Hi
One way
Insert a new row above your data.

Insert 2 columns in front of your existing data. Label Cell A2 Total
Revenue, label cell B2 Store Revenue
Insert Week1 in cell D1 and copy across to incrment to Week2, Week3 etc.
Insert Store1 in cell D2 and copy across to increment to Store2, Store3
etc.
Insert Week1 in cell C3 and copy down to cell C7 to increment to Week3
through Week5
Insert your values 20 through 6 in cells B3:B7
In cell A3 enter
=SUMPRODUCT($D3:Z3*$B3)
Copy down through cells A4:A7
In cell A8 enter
=SUM(A3:A7)

Now add your data, but not going down the page as you have, going across
the page
.......... Wk1 Wk2 Wk3
.......... Str1 Str2 Str3 Str4 Str5
Week 1 1 1 1 1 1
Week 2 1 1 1
Week 3 1 1 1
Week 4 1 1
Week 5 1

This allows for up to 23 stores to be added, in columns D to Z. If you
want more than 23, then amend the Z in formula above to the new column
letter that represents the last store. You can enter any number of
stores opening in any week.

 

[mjp]

Thanks for the response! It still isn't quite right, however, in week 5
for instance the total revenue for all 5 stores in WEEK 5 would be as
follows:

WEEK 5

Store 1 Open for 5 Weeks $6
Store 2 Open for 4 weeks $7
Store 3 Open for 3 Weeks $8
Store 4 open for 2 Weeks $10
Store 5 open for 1 Week $20
Total FOR WEEK 5 $51

Any other suggestions?

 

[Pete_UK]

Instead of putting 1's in your original sheet, why not put 20 if it is
the first week for that store, 10 for the second week, 8 for the 3rd
week etc. Then it will be quite easy to sum the income. What do the 1's
represent?

Pete

 

[mjp]

That's a very good suggestion. Maybe I could use some kind of defined
variable so that it is easy to change my assumptions? It would be too
much information to go through to easily update otherwise.

 

[Roger Govier]

Hi
If you put the formula
=SUMPRODUCT($B$3:$B$7,D37)
in cell D8, you will get 51.
If you copy it across through cells E8:H8 you will get 45, 38, 30 and
20.
The sum of all these(51+45+38+30+20)=184 is the cumulative sum of
earnings up to week 5, and this is the figure showing in cell A8.
Perhaps I should have shown the headings staring with Week5 in D1, going
down through Week4 etc as you progress to H1.
If you wanted more weeks, you would have to insert a new column at D
each time to achieve that.
The end result is the same, no matter which way you do it.