Hi folks,
I'm currently working on an excel sheet where I am attempting to summarize sales numbers.
In this scenario I have what I call "Account Behavior". Basically when an account is registered, I expect some sort of shopping behavior at a store. In month 1, they will spend $100. In month 2, they will spend $50. In month 3 they will spend $30 (so on and so forth).
Next I have another field I call "New account growth". This is a field that says our store expects 10 new accounts in month 1, 12 in month 2, 15 in month 3, etc.
What I need help with is summarizing this data. I want to create a third field called "Total Sales by month". In the example outlined above, my first month would simply be my first ten accounts making a purchase of $100 each (10*100=$1000). The second month would then have 10 accounts making a purchase of $50, and 12 new accounts making a purchase $100. My formula would then be 10*50+12*100 = $1700.
My biggest issue is that I'm working with 10 years of projections split out by month so writing out the formulas is extremely tedious. I came here hoping to find a faster way/method for summarizing this data.
Below I listed a short example. I hope the formatting comes through
<tbody>
</tbody>
I'm currently working on an excel sheet where I am attempting to summarize sales numbers.
In this scenario I have what I call "Account Behavior". Basically when an account is registered, I expect some sort of shopping behavior at a store. In month 1, they will spend $100. In month 2, they will spend $50. In month 3 they will spend $30 (so on and so forth).
Next I have another field I call "New account growth". This is a field that says our store expects 10 new accounts in month 1, 12 in month 2, 15 in month 3, etc.
What I need help with is summarizing this data. I want to create a third field called "Total Sales by month". In the example outlined above, my first month would simply be my first ten accounts making a purchase of $100 each (10*100=$1000). The second month would then have 10 accounts making a purchase of $50, and 12 new accounts making a purchase $100. My formula would then be 10*50+12*100 = $1700.
My biggest issue is that I'm working with 10 years of projections split out by month so writing out the formulas is extremely tedious. I came here hoping to find a faster way/method for summarizing this data.
Below I listed a short example. I hope the formatting comes through
Month on Book | 1 | 2 | 3 | 4 | 5 | 6 |
New Account Sales | $100 | $50 | $20 | $18 | $16 | $14 |
Calendar Month | Jan | Feb | Mar | Apr | May | Jun |
Account Openings | 10 | 12 | 5 | 8 | 11 | 7 |
Total Sales | 10*$100 | 10*$50+12*$100 | 10*$20+12*$50+5*$100 | ? | ? | ? |
<tbody>
</tbody>