Solver for complex Bundles

Hey fellow redditors,

The situation:

I am currently working on a solution to automatically calculate the input of goods in retail.

I am working for a company that delivers goods to said retailer and want to create an offer based on last years Input.

The challenge is that we provide a large variety of bundles/displays (consisting of up to 8 different items), which change from year to year.

My Plan:

- collect all data needed in a pivot for easy access

- create a main sheet, which combines the list of all bundles with last years output (on store level inclunding out of stock compensation).

- Use a solver that gets me the best possible outcome (~ Not more/less than 1.2 times of last years output, while still managing a positive development compared to last year).

What have I completed:

- A table containing: storenumber (~100 Stores), EAN, output, additional Input based on last years out of stock.

- A pivot table containingh to data from above (to use the getpivot function)

What I am Stuck on:

- Finding the right format for the main sheet

What needs to be in there:

- I do need to have each bundle including the arrangement of items.

- A row/ column for each store, which holds the amount of bundles/displays that the solver suggests

Would not be as hard if there werent a lot of bundles containing items that are present in others.

How would you arrange said sheet to make it as easy as possible for the solver and my mental health. Do I Need an extra column/ row to calculate?

We are talking about:

~ 100 stores

~ 25 different bundles

~ 80 different items

Edit: Fixed some auto correction errors