Automatic table generation and points allocation based on weight

Hello excel gurus!

I have a real difficulty solving one trivial problem. I want to automate a generation of a table based on several small ones. In the attached file you can see what I have for the input and a desired output.

The idea is as follows:

I have a number of points to allocate to several attributes. In the example attached there are 282 total points.

1. For cities I want to allocate points proportionally, so 47 for each city.
2. The same is for rotations, but now every city must have all rotations (as much as possible), so that for one the combination of rotations might be (8,8,8,8,8,7) for another (8,8,8,8,7,8) and so on, but the total for every city should always be (47,47,47,47,47,47) totaling 282. (see example in pivot table)
3. For brand I want to allocate points based on weight. So that for a particular city there should be 8% of brand 1 present, 63% of brand 2 and so on.
What is important is that in the end in a pivot table the total for rows should be the same for each row (47 in this example) and totals for columns (cities) should also stay the same.
4. All points in the final outcome should be integers. No decimal numbers.

I would really like to automate the process of creating this table… Ideally so that to only change tables on the incoming data sheet. It should be possible to change number or rows in any table as well as weight (%) for any table.

Basically in the end I want a pivot table shown in desired output sheet, even if the approach would be different from one that I use.

I would really appreciate if somebody can help me with this.