Solver for complex Bundles
Our take
In the evolving landscape of retail inventory management, the challenge of accurately forecasting and fulfilling demand is increasingly complex. The recent inquiry by a Reddit user highlights a common scenario: navigating the intricacies of bundle-based product offerings while striving to optimize stock levels. This situation is not unique; many professionals face similar hurdles. As articulated in our previous articles, such as Setting up pivot tables properly for inventory tracking purposes and Manipulating large data sets in an Inventory management scenario, the ability to manage and analyze data effectively is paramount to driving productivity and profitability in this sector.
The Redditor's plan to utilize a pivot table to collate data across multiple stores and products is a strategic approach. However, the real crux of the challenge lies in the complexity that arises from a diverse array of bundles—each containing various items that may overlap with others. This layering of products necessitates not only meticulous organization of the data but also a keen understanding of how to format that data for optimal solver functionality. Clear, accessible presentation of information is critical; the right format can significantly reduce cognitive load and facilitate better decision-making. By focusing on user outcomes, the Redditor is on the right path toward simplifying a convoluted process.
As the user explores potential solutions, it’s essential to consider how the structure of their main sheet can enhance their experience. A well-organized sheet that clearly delineates bundles, item arrangements, and store-specific data will not only support the solver's calculations but also contribute to the user's mental clarity. Adding auxiliary rows or columns for calculated values may be beneficial, particularly for tracking performance against last year's figures. This kind of proactive planning can empower users to leverage their data more effectively, leading to improved forecasting accuracy and inventory management.
Looking ahead, the integration of AI and machine learning into spreadsheet technology holds transformative potential for professionals grappling with similar challenges. As retailers increasingly adopt these advanced tools, the future of data management will likely shift toward more intuitive, user-friendly solutions that automate complex calculations and provide actionable insights. This evolution will not only ease the burden on users but also elevate the overall efficiency of inventory management processes. Retailers must remain vigilant in adopting innovative technologies that simplify their workflows while enhancing productivity.
Ultimately, the question remains: how can professionals continuously adapt their strategies to leverage these emerging tools effectively? As we observe the ongoing developments in AI and data management, staying informed and open to new methods will be key to navigating the complexities of retail inventory management in the years to come.
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
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Manipulating large data sets in an Inventory management scenarioHello. I am an Inventory Lead and I can utilize the basics, and have googled this question and cannot figure out the best way to approach it. It may be way of thinking more than my application of my skills. Scenario: I have a large data set of adjustments made for a given time frame. Goal: to not manually count the number of adjustments per item (we have over 2k). And also manipulate that data to see: how much, locations, reasons. This data would then be used to focus in on certain areas to improve inventory accuracy. Is this an application of pivot tables that I am misunderstanding? I tried some videos and I couldn't seem to make it work. Im sure this is an easy answer. I just can't piece it together. Thank you all submitted by /u/eaglessuc [link] [comments]
- Setting up pivot tables properly for inventory tracking purposesA bit of background: I’m former military, and currently I’m the head of inventory at a fabrication company. While I was in the military, our inventory was kept mostly on paper, and everything that was annotated in excel was done using basic formulas and a single sheet. I am using Excel on a work laptop and I would consider myself somewhere between beginner and intermediate level. My problem currently comes from the fact that this company I work at never had an actual inventory system before, which I’m currently building from the ground up by myself. Since I don’t have extensive Excel experience, I struggle to materialize my plan and thoughts into this spreadsheet. Currently I have a master list, which I separated by location, category, item ID, and amount in stock and I’m trying to make a separate sheet with a pivot table that can show each shop independently whenever I click a dropdown menu. The pivot table I did make is a bit hard to look at and I can’t figure out how to make it do what I need it to do. Additionally, whenever I update a number on the master list, which is where the pivot table is sourced from, the numbers on the pivot table don’t update with the information. I should also mention that we have 3 different “shops” that we supply within our company, which is the main purpose for me wanting to figure out the pivot table. I’ve looked up some tutorials on YouTube on how to do these seemingly advanced things, but I can’t seem to find one that fits my specific needs or that explains how to properly do this. If any of you have trouble understanding the issue, please let me know, I’m not very experienced with excel so I don’t know all the terms for everything To summarize this wall of text: Need pivot tables that I can separate individual shops through and update each shop’s stock individually. I would love some help on this. Thank you in advance submitted by /u/PhastasFlames [link] [comments]
- How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?Hello all, I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways. What is the problem So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good. Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal. For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" "). To get to the point I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k. These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands. These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet. 'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide. Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions: Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'. Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless. Alternatively: should I use LET whenever a repeated lookup is needed in the same formula? Really looking for to your answers! submitted by /u/EvolvedRevolution [link] [comments]