DianaBanana
Board Regular
- Joined
- Mar 10, 2014
- Messages
- 71
Hi,
I would like to create a more elegant and automatic way to do this:
In column F you can see new stores being added in the quarter. These stores have different sales in the first four years and then at year 4 for the rest of their "life" have the same level of sales. So for the new stores it takes four quarters for one year. So in Q1 2020 (row 12) it becomes year 2 in Quarter 1 2021 (row 17). So in column M, I tried to show what the sum of total sales for the new stores would be in each quarter. Also, to calculate the sales it is using total sales in column Y dividing by 4 (for each quarter) and then multiplying by 60% in year 1 (G 5) and then 87% in year 2 (H5), also 87% in year 3 (I5) and in year 4 it's 100%. So that means they are immature stores in year 1 and then mature fully in year 4.
Now, I also need to create a column that adds in sales for the rest of the stores. So in row 18, Q2 - D18 there are 281 comp stores. All these stores will be multiplied by Y4/4 - they are all mature. So that's just in 2021. Then in 2022, whatever total sales number was for 2021 in each quarter is multiplied by the comp in column Z. So Q2 2022 is the first row where this needs to be calculated. You will take Q2 2021 (which was just the sum of the new stores and comp stores) and multiply by the royalty of 3% for the comp stores and 2% for the new stores in year 1.
Finally, there needs to be a column for what the franchisor charges these stores. So for new stores in year 1 they pay a royalty of 2% (P6) and then year 2 of 3% (P5). So somehow the formula needs to figure that out - maybe a look up.
I hope I was clear!
Kindly,
DB
I would like to create a more elegant and automatic way to do this:
In column F you can see new stores being added in the quarter. These stores have different sales in the first four years and then at year 4 for the rest of their "life" have the same level of sales. So for the new stores it takes four quarters for one year. So in Q1 2020 (row 12) it becomes year 2 in Quarter 1 2021 (row 17). So in column M, I tried to show what the sum of total sales for the new stores would be in each quarter. Also, to calculate the sales it is using total sales in column Y dividing by 4 (for each quarter) and then multiplying by 60% in year 1 (G 5) and then 87% in year 2 (H5), also 87% in year 3 (I5) and in year 4 it's 100%. So that means they are immature stores in year 1 and then mature fully in year 4.
Now, I also need to create a column that adds in sales for the rest of the stores. So in row 18, Q2 - D18 there are 281 comp stores. All these stores will be multiplied by Y4/4 - they are all mature. So that's just in 2021. Then in 2022, whatever total sales number was for 2021 in each quarter is multiplied by the comp in column Z. So Q2 2022 is the first row where this needs to be calculated. You will take Q2 2021 (which was just the sum of the new stores and comp stores) and multiply by the royalty of 3% for the comp stores and 2% for the new stores in year 1.
Finally, there needs to be a column for what the franchisor charges these stores. So for new stores in year 1 they pay a royalty of 2% (P6) and then year 2 of 3% (P5). So somehow the formula needs to figure that out - maybe a look up.
I hope I was clear!
Kindly,
DB
