Don't laugh, but, from time to time, my friends and I host a multiple-course KFC dinner, and I have a spreadsheet to optimise the order. This is to make sure we order the right combination of 'bucket'-type items (i.e. SKUs that contain multiple pieces, often of different types):
Here is the spreadsheet I currently use, and here's a screenshot:
To use it, you first specify the number of participants in A2
, and what you want each person to have in E2:E6
(we're really only interested in the chicken, so 'sides' are treated as generic to simplify).
Here's the manual part, that I'd like to improve.
The next step is to look at the ideal totals for each item (F2:F6
), and to try to set the right quantities (H12:H20
) of the 'bucket'-type SKUs that I have recreated (A11:G20
), so that the output totals (H21:M21
) match the ideal totals (F2:F6
).
The optimisation part is to get the deltas (H22:M22
) as close to zero as possible, and to get the total cost (N21
) as low as possible.
So, my question is: is there a way to do this better? I think Excel has some sort of Solver functionality, but I'm afraid I don't know how I'd go about even starting to use that, as my Excel skills are pretty rudimentary. Oh, and in case it makes a difference regarding functionality, I'm using Excel for Mac v16.37.
Any thoughts gratefully appreciated! :)
I can't take any credit for this, but am happy to say that GSerg left me a couple of comments that pointed me in the right direction, and I now have Solver set up to organise my chicken parties!