I have the following sales data for 2016, 2017, and 2018:
<tbody>
</tbody>
Based on this information, is there some formula or strategy here that I can use to break out how much money each of the companies would pay us by month all the way until Dec 2018? Specifically, is there a simple formula or set of formulas I can use to copy across and down (I have several hundred rows). For example, in column F row 2, it would be $0 since this contract doesn't start until 10/11/16. However, in column G row 5, it would be $1000 ($2000/2 = $1000 p/month). Column H row 5 would also be $1000. Then Column I row 5 would $0.
Column A | Column B <strike></strike> | Column C | Column D <strike></strike> | Column E | Column F <strike></strike> | Column G | Column H <strike></strike> | Column I | Column X | |
Row 1 | Opportunity Name | Opportunity Close Date (US date) | Opportunity Subscription Months | Amount | Opportunity Record | Jan 16 | Feb 16 | Mar 16 | Apr 16 | All months all the way until Dec 2018 |
Row 2 <strike></strike> | Company 1 | 10/11/16 | 12 | $13000 | Subscription | ? | ? <strike></strike> | ? | ? <strike></strike> | |
Row 3 <strike></strike> | Company 2 | 11/5/17 | 12 | $12300 | Subscription <strike></strike> | ? | ? <strike></strike> | ? | ? <strike></strike> | |
Row 4 <strike></strike> | Company 2 | 1/1/18 | 4 | $8900 | Pilot | ? | ? <strike></strike> | ? <strike></strike> | ? <strike></strike> | |
Row 5 <strike></strike> | Company 3 | 2/5/16 | 2 | $2000 | Pilot <strike></strike> | ? | ? <strike></strike> | ? | ? <strike></strike> | |
Row 6 <strike></strike> | Company 3 | 10/3/17 | 7 | $3051 | Subscription <strike></strike> | ? | ? <strike></strike> | ? | ? <strike></strike> | |
Row 7 | Company 5 | 12/1/16 | 6 | $5605 | Pilot | ? <strike></strike> | ? | ? <strike></strike> | ? |
<tbody>
</tbody>
Based on this information, is there some formula or strategy here that I can use to break out how much money each of the companies would pay us by month all the way until Dec 2018? Specifically, is there a simple formula or set of formulas I can use to copy across and down (I have several hundred rows). For example, in column F row 2, it would be $0 since this contract doesn't start until 10/11/16. However, in column G row 5, it would be $1000 ($2000/2 = $1000 p/month). Column H row 5 would also be $1000. Then Column I row 5 would $0.
Thoughts? Advice? Please help! Thanks!