Hi All,
I have a difficult (to me) excel question. Please see attached file.
In my job I sell t-shirts. We bring in new colors every business quarter. I am making a tool to track our forecasted inventory situation for each color.
Looking at the excel doc, I need help with the part highlighted in blue. This will track the inventory for each week based on sales. I have filled in the cells for Ebony as an example.
Conditions
1) If the current week is before the start week for the cycle, I want to use each color's 'planned sell%'. If the current week is after the start week for the cycle, I want to use each color's 'actual sell %' (in the example I just wrote in some #'s for actual sales %, this number will be 0 unless sales have actually hapenned).
2) Sometimes we pull up the markdown for a color. Hence, the 'Markdown Week' is user edit-able. The sales %'s for each color will need to adjust accordingly.
An Example
For Ebony: as the current week is June wk. 4 (before the start week of July wk. 1), we use the planned sell % of 35% for the weeks when all 3 colors are selling. As Indigo marks down August week 1, starting August week 2 Ebony will be doing 50% of the sales, and the grid reflects that change.
My issue is that I can't figure out how to efficiently/cleanly make this work without it becoming a gigantic if statement. I need to have up to 10 colors, each with their own potential markdown week, and for the sales %'s to automatically adjust when the user changes the markdown weeks (which happens often). Can anyone give me an idea of how they would approach this, or a starting example to work from? Everytime I try to make progress I end up with a big headache.
Thanks so much!
Bookmarks