July 17, 2020
I'm hoping someone can help with a kind of dumb problem I need to solve. Every month I allocate product targets to different teams based on a top down forecast provided by someone else. The teams don't care about their product mix just the total target for the month, but I still need to break their target down into product targets. Usually I get the teams to agree to their total target (in green in the attached) and then allocate it to the products (blank orange cells) afterwards so that the product mix at the team level is somewhat similar YoY. Everything highlighted in yellow is info I would have before needing to complete this analysis.
Is there a formula anyone can come up with that would allow me to allocate their total target into product targets that "make sense", but that also adds up to the top down product targets? So the sum of cells D17, 25, and 33 need to add up to cell D9, but I also need cells D17, 18, 19, and 20 to add up to cell D2.
Thanks in advance for your help.
December 20, 2019