trytoexcelatexcel
New Member
- Joined
- Jun 4, 2015
- Messages
- 4
Hi Everyone,
So this is my first post here on Mr. Excel and I was hoping someone could help. I will try to explain the best I can.
Table 1
<tbody> </tbody>
So this above table is my base case that I would like to use so I can just change the 1's to 0's and have the rest of my tables change as well
Table 2
<tbody> </tbody>
Table 3
<tbody> </tbody>
<tbody>
</tbody>
This is what I am trying to achieve with a formula, so linking to table 1, products 1-5 will be coming in at different times of the year, some will arrive in January 15, some in Feb 15, but each year the product will increase by 3% or decrease by 5%, I'm trying to figure out a formula that allows table 2's products to know that 1 year has passed and it is time to increase/decrease. And I'm trying to achieve this by only altering the base case scenario.
The reason I am linking to table 1 is because I have more than 2 tables but the concept is the same. I would like to be able to change table 1's, 1 and 0's so they will affect table 2,3,4... and then in tables 2,3,4 have a formula know that a certain product has now reached the next year and needs to escalate or decrease independently of other products since each one will be arriving at different time periods and their yearly mature month/year will be different.
I really hope someone can help or else it will be back to manually changing each table when there is a change to the product arrival date :]
Thank you very much
So this is my first post here on Mr. Excel and I was hoping someone could help. I will try to explain the best I can.
Table 1
| Base Case | 1 | 2 | 3 | 4 | 5 | Total |
Jan-15 | 1 | | | | | 1 |
Feb-15 | 1 | 1 | | | | 2 |
Mar-15 | 1 | 1 | | | | 2 |
Apr-15 | 1 | 1 | 1 | | | 3 |
May-15 | 1 | 1 | 1 | | | 3 |
Jun-15 | 1 | 1 | 1 | | | 3 |
Jul-15 | 1 | 1 | 1 | | | 3 |
Aug-15 | 1 | 1 | 1 | | | 3 |
Sep-15 | 1 | 1 | 1 | | | 3 |
Oct-15 | 1 | 1 | 1 | | | 3 |
Nov-15 | 1 | 1 | 1 | | | 3 |
Dec-15 | 1 | 1 | 1 | | | 3 |
Jan-16 | 1 | 1 | 1 | 1 | | 4 |
Feb-16 | 1 | 1 | 1 | 1 | | 4 |
Mar-16 | 1 | 1 | 1 | 1 | | 4 |
Apr-16 | 1 | 1 | 1 | 1 | | 4 |
May-16 | 1 | 1 | 1 | 1 | | 4 |
Jun-16 | 1 | 1 | 1 | 1 | 1 | 5 |
Jul-16 | 1 | 1 | 1 | 1 | 1 | 5 |
Aug-16 | 1 | 1 | 1 | 1 | 1 | 5 |
Sep-16 | 1 | 1 | 1 | 1 | 1 | 5 |
Oct-16 | 1 | 1 | 1 | 1 | 1 | 5 |
Nov-16 | 1 | 1 | 1 | 1 | 1 | 5 |
Dec-16 | 1 | 1 | 1 | 1 | 1 | 5 |
Jan-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Feb-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Mar-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Apr-17 | 1 | 1 | 1 | 1 | 1 | 5 |
May-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Jun-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Jul-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Aug-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Sep-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Oct-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Nov-17 | 1 | 1 | 1 | 1 | 1 | 5 |
Dec-17 | 1 | 1 | 1 | 1 | 1 | 5 |
<tbody> </tbody>
So this above table is my base case that I would like to use so I can just change the 1's to 0's and have the rest of my tables change as well
Table 2
| Escalation 3% | 1 | 2 | 3 | 4 | 5 | Total |
Jan-15 | 1 | | | | | 1 |
Feb-15 | 1 | 1 | | | | 2 |
Mar-15 | 1 | 1 | | | | 2 |
Apr-15 | 1 | 1 | 1 | | | 3 |
May-15 | 1 | 1 | 1 | | | 3 |
Jun-15 | 1 | 1 | 1 | | | 3 |
Jul-15 | 1 | 1 | 1 | | | 3 |
Aug-15 | 1 | 1 | 1 | | | 3 |
Sep-15 | 1 | 1 | 1 | | | 3 |
Oct-15 | 1 | 1 | 1 | | | 3 |
Nov-15 | 1.03 | 1 | 1 | | | 3.03 |
Dec-15 | 1.03 | 1 | 1 | | | 3.03 |
Jan-16 | 1.03 | 1 | 1 | 1 | | 4.03 |
Feb-16 | 1.03 | 1.03 | 1 | 1 | | 4.06 |
Mar-16 | 1.03 | 1.03 | 1 | 1 | | 4.06 |
Apr-16 | 1.03 | 1.03 | 1.03 | 1 | | 4.09 |
May-16 | 1.03 | 1.03 | 1.03 | 1 | | 4.09 |
Jun-16 | 1.03 | 1.03 | 1.03 | 1 | 1 | 5.09 |
Jul-16 | 1.03 | 1.03 | 1.03 | 1 | 1 | 5.09 |
Aug-16 | 1.03 | 1.03 | 1.03 | 1 | 1 | 5.09 |
Sep-16 | 1.03 | 1.03 | 1.03 | 1 | 1 | 5.09 |
Oct-16 | 1.03 | 1.03 | 1.03 | 1 | 1 | 5.09 |
Nov-16 | 1.03 | 1.03 | 1.03 | 1 | 1 | 5.09 |
Dec-16 | 1.0609 | 1.03 | 1.03 | 1 | 1 | 5.1209 |
Jan-17 | 1.0609 | 1.03 | 1.03 | 1.03 | 1 | 5.1509 |
Feb-17 | 1.0609 | 1.0609 | 1.03 | 1.03 | 1 | 5.1818 |
Mar-17 | 1.0609 | 1.0609 | 1.03 | 1.03 | 1 | 5.1818 |
Apr-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1 | 5.2127 |
May-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1 | 5.2127 |
Jun-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1.03 | 5.2427 |
Jul-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1.03 | 5.2427 |
Aug-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1.03 | 5.2427 |
Sep-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1.03 | 5.2427 |
Oct-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1.03 | 5.2427 |
Nov-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1.03 | 5.2427 |
Dec-17 | 1.0609 | 1.0609 | 1.0609 | 1.03 | 1.03 | 5.2427 |
<tbody> </tbody>
Table 3
| Decrease 5% | 1 | 2 | 3 | 4 | 5 | Total |
Jan-15 | 1 | | | | | 1 |
Feb-15 | 1 | 1 | | | | 2 |
Mar-15 | 1 | 1 | | | | 2 |
Apr-15 | 1 | 1 | 1 | | | 3 |
May-15 | 1 | 1 | 1 | | | 3 |
Jun-15 | 1 | 1 | 1 | | | 3 |
Jul-15 | 1 | 1 | 1 | | | 3 |
Aug-15 | 1 | 1 | 1 | | | 3 |
Sep-15 | 1 | 1 | 1 | | | 3 |
Oct-15 | 1 | 1 | 1 | | | 3 |
Nov-15 | 0.95 | 1 | 1 | | | 2.95 |
Dec-15 | 0.95 | 1 | 1 | | | 2.95 |
Jan-16 | 0.95 | 1 | 1 | 1 | | 3.95 |
Feb-16 | 0.95 | 0.95 | 1 | 1 | | 3.9 |
Mar-16 | 0.95 | 0.95 | 1 | 1 | | 3.9 |
Apr-16 | 0.95 | 0.95 | 0.95 | 1 | | 3.85 |
May-16 | 0.95 | 0.95 | 0.95 | 1 | | 3.85 |
Jun-16 | 0.95 | 0.95 | 0.95 | 1 | 1 | 4.85 |
Jul-16 | 0.95 | 0.95 | 0.95 | 1 | 1 | 4.85 |
Aug-16 | 0.95 | 0.95 | 0.95 | 1 | 1 | 4.85 |
Sep-16 | 0.95 | 0.95 | 0.95 | 1 | 1 | 4.85 |
Oct-16 | 0.95 | 0.95 | 0.95 | 1 | 1 | 4.85 |
Nov-16 | 0.95 | 0.95 | 0.95 | 1 | 1 | 4.85 |
Dec-16 | 0.9025 | 0.95 | 0.95 | 1 | 1 | 4.8025 |
Jan-17 | 0.9025 | 0.95 | 0.95 | 0.95 | 1 | 4.7525 |
Feb-17 | 0.9025 | 0.9025 | 0.95 | 0.95 | 1 | 4.705 |
Mar-17 | 0.9025 | 0.9025 | 0.95 | 0.95 | 1 | 4.705 |
Apr-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 1 | 4.6575 |
May-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 1 | 4.6575 |
Jun-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 0.95 | 4.6075 |
Jul-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 0.95 | 4.6075 |
Aug-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 0.95 | 4.6075 |
Sep-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 0.95 | 4.6075 |
Oct-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 0.95 | 4.6075 |
Nov-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 0.95 | 4.6075 |
Dec-17 | 0.9025 | 0.9025 | 0.9025 | 0.95 | 0.95 | 4.6075 |
<tbody> </tbody>
| Product 1 is released on November of 2014 so that is why year starts November 2015 |
|
| So in this example my objective is to get these numbers linked to the base case schedule, so I can just change the numbers there and it will flow through to these two alternative schedules |
| The products are coming in at different times but they still need to either increase or decrease each year so right now I am just doing it manually |
| Would there be any formula I could use to fill in all cells in "escalation 3%" and "decrease 5%" so they will automatically change when I change the base schedule and also change year over year? |
| Thank you so much! |
<tbody>
</tbody>
This is what I am trying to achieve with a formula, so linking to table 1, products 1-5 will be coming in at different times of the year, some will arrive in January 15, some in Feb 15, but each year the product will increase by 3% or decrease by 5%, I'm trying to figure out a formula that allows table 2's products to know that 1 year has passed and it is time to increase/decrease. And I'm trying to achieve this by only altering the base case scenario.
The reason I am linking to table 1 is because I have more than 2 tables but the concept is the same. I would like to be able to change table 1's, 1 and 0's so they will affect table 2,3,4... and then in tables 2,3,4 have a formula know that a certain product has now reached the next year and needs to escalate or decrease independently of other products since each one will be arriving at different time periods and their yearly mature month/year will be different.
I really hope someone can help or else it will be back to manually changing each table when there is a change to the product arrival date :]
Thank you very much
