Rotating Schedule based on Unit Volume Share

[artikyulashun]

A

B

C

Week 1

Week 2

MFG A

MFG B

MFG C

MFG A

MFG B

MFG C

MFG A

MFG B

MFG C

Bananas

.6

.2

.2

ON

Apples

.1

.4

.5

ON

Oranges

.3

.5

.2

ON

<tbody>
</tbody>

Columns A,B and C represent Percentage of Unit Volume by MFG for the Fruits to the left.

Using these numbers, I would like to generate a rotating 13-week promotional schedule for each item. Maximum number of weeks in a row that an MFG can have any one specific item on sale is two weeks. However, at the end of the of the 13-weeks, I'd like the number of instances that, for example, MFG A has Bananas on sale equal or near their share or .6.

My initial thought was you could use a ratio to create a patterned binary list of 1 and 0 or "on" and "off" for each item, but perhaps it is easier with these percentages? Perhaps this is simply a manual task.

Your feedback is appreciated.

 

[Eric W]

Intriguing problem. Just to be sure I understand, every week every product will be on sale for one of the manufacturers? If so, I reorganized it a bit so each week has just 1 column, and instead of "ON", it shows the name of the manufacturer instead.

Given that, I started with this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		MFG A	MFG B	MFG C		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13
2	Bananas	0.6	0.2	0.2		MFG A	MFG C	MFG A	MFG C	MFG C	MFG A	MFG A	MFG A	MFG C	MFG A	MFG C	MFG C	MFG A
3	Apples	0.1	0.4	0.5		MFG A	MFG C	MFG C	MFG B	MFG C	MFG C	MFG C	MFG B	MFG B	MFG A	MFG B	MFG C	MFG C
4	Oranges	0.3	0.5	0.2		MFG C	MFG B	MFG B	MFG B	MFG A	MFG B	MFG A	MFG B	MFG B	MFG B	MFG C	MFG C	MFG B

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =INDEX($B$1:$D$1,MATCH(RAND(),SUBTOTAL(9,OFFSET($A2,0,0,1,{1,2,3})))) <tbody> </tbody>

<tbody>
</tbody>

Put the formula in F2, then drag it to the rest of the cells in the table.

The formula in F2 will randomly assign a manufacturer to each cell, based on the ratios in columns A:C. So each manufacturer should be listed roughly the right percentage, but it could vary some. The problem with this is that it does not take into account the maximum 2-week limit. That proved to be a tougher nut to crack. But I came up with this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		MFG A	MFG B	MFG C		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13
2	Bananas	0.6	0.2	0.2		MFG A	MFG A	MFG B	MFG A	MFG C	MFG A	MFG B	MFG A	MFG A	MFG B	MFG B	MFG A	MFG A
3	Apples	0.1	0.4	0.5		MFG B	MFG C	MFG C	MFG B	MFG C	MFG C	MFG B	MFG B	MFG C	MFG B	MFG B	MFG C	MFG C
4	Oranges	0.3	0.5	0.2		MFG A	MFG B	MFG B	MFG A	MFG C	MFG B	MFG A	MFG C	MFG B	MFG B	MFG A	MFG B	MFG A

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula F2 {=INDEX($B$1:$D$1,LARGE(LOOKUP(ROW(INDIRECT("1:10"))-1,SUBTOTAL(9,OFFSET($A2,0,0,1,{1,2,3}))*10,{1,2,3}*SIGN(((D2<>E2)+(E2<>$B$1:$D$1)))),RANDBETWEEN(1,SUM(10*($B2:$D2*SIGN(((D2<>E2)+(E2<>$B$1:$D$1))))))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the formula in F2, confirm with Control+Shift+Enter, then drag to the rest of the cells in the table. Also note that this works if your percentages are all 1 digit (.6). If you go to 2 digits, find the 10 in the formula (3 places) and replace it with 100.

This should prevent the same manufacturer more than 2 weeks in a row, and still keep the same ratios. If you want a different list, just press F9. This example shows no MFG A for apples, which is possible. Adding more constraints might push this into the realm of VBA though.

Let me know if this helps.

 

[artikyulashun]

Thank you Eric.

This is highly creative and effective. Greatly appreciate the work that went into this.