Does your system use the standard data model?
some one suggested Montecarlo Optimization but those formulas are for rocket scientists, not for me.
Monte Carlo methods can easily be computationally expensive, but they are among the easiest to understand. If you don't feel comfortable with that, you may want to consider hiring outside help. Send me a private message if you're interested in that.
Can you share any more details about what it is that you're trying to optimize? What outcome are you looking at, and are you trying to maximize or minimize it? What variables can you manipulate to affect that outcome?
I want to be able to optimize future purchase quantities based on the historic data, so I can see what are the, let's say, first 100 SKUs.
Are you saying that you want to see the 100 best sellers (whether by quantity or by $ total)? A basic sub-summary report can do that without any sophisticated optimization.
Do you want to predict what orders are likely to come in (or go out; it isn't clear whether these are incoming or outgoing orders from your perspective)? That's a forecasting problem rather than a more general optimization problem.
thank you for your comments and the time taken. The optimization I am after is for future purchases so inventory stays plenty. Like I said, the database shows me the top ordered sku's (selling sku's), from the most used on down. The orders were taken from an Excel report, so there's only one table. SKU, QTY, ORDERID, UNIT PRICE AND TOTAL PRICE. There's a need to know how to optimize the purchasing of future sku's (for reselling, if you wish). But I do not want to waste your time. If there isn't a clear formula to do it, I am fine. Thank you again
The thing you're trying to do is a well-studied problem called demand forecasting. There are many solutions with varying degrees of complexity, and which works best will ultimately depend on the particulars of your customers' behavior.
The simplest thing you could do would be to sum up the total quantity sold for each SKU in some period of time — let's say a week — then calculate the average and standard deviation of sales per week for each SKU. Now you need to weigh the consequences of being out of stock with the consequences of being overstocked, and decide what frequency of going out of stock is acceptable for you. Then you plug that into Chebyshev's inequality to figure out how much of each SKU to stock. Supposing that you're comfortable with each SKU going out of stock in 5% of weeks (2-3 times a year), and a SKU that sells an average of 100 per week with a standard deviation of 10, you'd need to stock that SKU at 100 + Sqrt ( 1 / .025 ) * 10 = 163.245..., rounding up to 164. Less extreme overstocking can usually be achieved with more information about how sales vary from week to week.
More sophisticated calculations are necessary if you want to account for things like seasonality or projected growth.
Hi, Jeremy. I passed your comments to the client. I really appreciate the time you took to comment. Thank you