Allocate products quantity according to each person's GMV share % and growth %

[Christine.]

how to allocate the 100000 products to A-N according to their Apr GMV share% and Apr GMV Growth% ？
Remark: no need to allocate products to the ones who are with very small GMV


Many thanks!!!

QUESTION.jpg

[Richard Buttrey]

Would you explain how the growth and share %s are used? And in relation to what - the Apr GMV?

And how do we decide what are 'very small'? Do you mean L, M & N.

Remember you understand exactly what you are trying to model and the terminology is 2nd nature to you. We are starting from zero knowledge and have no idea of what you are trying to achieve. The terms you've used could be interpreted in various ways.

[GeoffW283]

If I understand correctly, I think you may be asking us to make business decisions here as opposed to an Excel implementation of your business decisions.

You are asking how to allocate the target 100,000 product goal among 14 people or groups or whatever they are (A, B, C, . . .)

Presumably you want to do this taking into account projected May GMV growth (or decline) for each of the 14 groups. You have some factual history:

• April GMV for each of the 14 groups
• April GMV growth % for each of the 14 groups

Calculating expected May GMV growth will need some management judgement. For example:

• Why did group A's GMV more than double between March and April?
• Do you expect this rate of growth to be sustained through May? Why? / Why not?

This exercise needs to be repeated for each of the 14 groups.
So taking into account history through April and your business judgement regarding trends going forward for each of the 14 groups, you need to estimate May GMV growth% for each of the 14 groups and input that into col-H. I imagine you also want to capture a brief rationale for your col-H values. I have allocated this to col-I.
With this info, projected May GMV (col-J) can be calculated as follows. In J5 copied down:

Please Login or Register  to view this content.

Finally The allocated product quantity can be calculated as follows. In K5 copied down:

Please Login or Register  to view this content.

Here's how it looks. Blue shaded cells are data entry - particularly relevant for this exercise are cols H and I. Unshaded cols are calculated.

GMV.png

Attached is your updated workbook implementing the above.

Let us know if this is even remotely on the right track.

[Richard Buttrey]

If I understand correctly, I think you may be asking us to make business decisions here as opposed to an Excel implementation of your business decisions.

You are asking how to allocate the target 100,000 product goal among 14 people or groups or whatever they are (A, B, C, . . .)


Let us know if this is even remotely on the right track.

I agree. Those were my first thoughts, but if there's a history of monthly growth, we know March & April but are there previous months and if so wouldnt it be more reasonable to take account of all prior data to model forward growth?

[GeoffW283]

Agreed - the more data the better. Especially since there are such wild swings in productivity between March and April.