Mapping vintages to sales and different rates

  • Hello,

    I have a two part question. I need to make this spreadsheet more legible, more user friendly, and more professional.

    1) Formulas: Column F shows new stores each quarter. So, 14 new stores on Row F12 which is Q1 (quarter 1) of 2020 - the year is in A12 and the quarter in AB. Now, columns G through J on row 12 show the total sales per quarter for each year. The formulas can change too that I have. What I need to automate is that those 14 stores will have 60% of total sales in year 1, 87% of total sales in years 2-3, and then be mature at 100% In year 4 . By row 13, there are 6 new stores, but those 14 stores from the prior quarter are still in year 1. Also, of these total sales - so in 202Q1 in column M, it would multiply G by M6 or by 2% royalty in year 1 of the 14 new stores. Once these 14 new stores hit year 2, it is then multiplied by M5 which is a 3% royalty. So M would include the royalties received on these sales. Is there formulas that can automate this and recognize when stores hit each quarter and at what % of sales and what royalty rate to use?

    2) If there are any ideas how to make this page more user friendly and professional looking - more formatting - please advise.

    Kindly,

    Kooala

    Files

    • stores.xlsx

      (215.6 kB, downloaded 33 times, last: )
  • If this is too much -even just one piece would help. how build so the 14 stores after 4 quarters is multiplied by 87% from 60%. That would start in 1Q 2022.