Tricky % of sales issue

[Dynamo418]

Hi guys!

I have a pretty tough problem for you (please see attached example doc. for clarity). I have 3 colors of a t-shirt, and I need to be able to forecast the sales by color by week. Sounds simple right? However, there are complications:

1) There are updates in the color palette we carry throughout the year. Some color 'carry-forward' into the next time cycle, some don't.
2) The start dates of the colors are fixed. However, the end dates are flexible (if a color performs poorly, we might move out the end date to work through inventory, etc.).
3) I need the chart to reflect the planned % for a color if it is before the color starts, and the actual % once it starts. If the color does not carry forward into the next cycle, once we get actual results I want to use those results as a predictor for the sales % until the end of the cycle. If a color carries forward, I want to use the actual % results as a predictor for the sales until the end of the cycle as well, reverting to the planned % for the next cycle.

I tried to write a long if statement to cover the different scenarios, but it is cumbersome and unscalable. I am hoping for input on how to make it simpler and cleaner, perhaps using VBA or an array or something.

To walk through an example for a single color to explain my purpose:

Color A

If current week is Jan wk. 4 (after start week): Sales % for Jan wk. 1 through Jan wk. 3: 0 (sales have already happened and have been accounted for). Sales % for Jan wk. 4 - March wk. 5: 30% (the actual %, based on the data from the first 3 weeks). Sales % for April wk. 1 - April wk. 4: 50% (the planned % for cycle 2, as color A carries forward into Cycle 2).

If the current week is December wk. 3 (before the start week): Sales % for Jan wk. 1 - March wk. 5: 35% (Planned %). Sales % for April wk. 1 - April wk. 4: 50% (planned %).

Easy enough so far. However, the tricky part:


If the current week is December wk. 3 (before the start week), and the user changes the end date of color B from March wk. 5 to March wk. 1, I need the planned sales % for color A for March wk. 2 - March wk. 5 to be updated to reflect their only being 2 colors for those weeks. So, instead of 35%, it will now be 47% (35%/75%) for those 4 weeks.

If the current week is January wk. 4 (after start week), and the user changes the end date of color B from March wk. 5, to March wk. 1, I need the actual sales % for color A to be updated to reflect their only being 2 colors for those weeks. So, instead of being 30%, it will now be 37.5% for March wk. 2 - March wk. 5.

Does that make sense? There are a lot of moving parts here, but given a good framework in my head it doesn't seem that difficult... but I'm stumped.

Can anyone give me a hand? I'm pretty stuck!!!