Say that you are the buyer for a large company and need to procure 25,000 widgets at the lowest cost. You head over to the WidgetOverstock website, where other companies sell their excess widgets. The rules of the site say that you must buy 100% of the lot of widgets. There are currently 10 lots of widgets offered, so you need to determine what combinations of lots you can buy that will get you 25,000 or more widgets for the lowest overall cost.
There are a couple additional details to keep in mind. Your company can’t use more than 25,000 widgets. Any more than that will go to waste. Also, the users at the WidgetOverstock website aren’t rational players. Some price their widgets at bargain-basement prices, while others price their widgets as if they’re made of gold.
While using Solver might be the first option that comes to mind, that last fact makes it very difficult for Solver to find the optimal solution. Solver wants beautiful linear models where the pricing for every lot follows some rational mathematical equation.
With 10 lots available, you can do a quick calculation in Excel of =2^10 to see that there are 1,024 combinations of lots that you can purchase. It’s easy for Excel to run a model 1,024 times. Had there been 16 lots, then you’d have 2^16, or 65,536, combinations. Excel can still do that, but you might have some long calculation times. The practical limit is 2^19 lots or 524,288 combinations. Any more than that and you’ll run out of rows in Excel.
I set up a model to figure out the number of widgets purchased and the cost of those widgets. To use it, you simply plug in a number between 1 and 1,024 in cell E1. In cell E2, a formula of =BASE(E1,2,10) converts the number to 10-digit binary. You’ve probably never heard of the BASE function. Added in Excel 2013, BASE was marketed only to mathematicians. But it’s great for converting a number to a series of Yes/No choices.
Formulas in cells D4:D13 break the binary number from E2 into a series of 1’s and 0’s. The formula =MID(E$2,ROW(A1),1) actually gets you a text 1 or 0. Several methods can convert that text number to a real number. I’ve used =MID(E$2,ROW(A1),1)+0. Formulas in E4:E13 multiply each lot times 0 or 1. A SUM function in E14 tells you how many widgets you bought. And =SUMPRODUCT(C4:C13,D4:D13) tells you the cost of the widgets.
To get to Figure 1, I started entering numbers into cell E1. The first 14 numbers I tried didn’t get me to 25,000 widgets. When I typed in the magic number of 15, however, I ended up with 25,120 widgets at a cost of $21,839.20. This certainly meets the requirements of 25,000 widgets with few left over. But is $21,839.20 the lowest possible cost?
Rather than type the remaining 1,009 possible numbers into cell E1, you can have Excel try all possible numbers using the What-If Analysis Tool. Set up a table with the numbers 1 to 1,024 going down the side. Here’s an easy way: Type 1 in cell G6. Select the 1 and use Home, Fill, Series. Specify that you want to fill in columns and stop at 1,024.
Each of these numbers down the side of the what-if table will be plugged into cell E1 of your model. Across the top, in cells H5 and I5, use formulas to record the results of any given trial. If you plug 15 into E1, you want to know the total number of widgets from E14 and the total cost from E15. Use =E14 in H5. Use =E15 in I5.
Select G5:I1029. From the Data tab, select What-If Analysis, Data Table. In this case, specify that the numbers from the left column of the report should be plugged into cell E1. Leave the Row Input Cell box blank.
When you click OK, Excel will very rapidly run your model 1,024 times. Each time, it will plug in the next number from G6:G1029 and plug it into E1 virtually. The results from that trial will be written in the next row of the table. In less than a second, Excel will have run your model 1,024 times.
At this point, you can copy and paste the table as values. Use either sorting or filtering to get all of the trials with 25,000 or more widgets. It turns out that 875 of the combinations meet the 25,000-widget requirement.
Sort those rows by Cost ascending. The total cost ranges from $15,000 to $95,000. The best scenario, though, is trial #586. Entering 586 into cell E1 will show you that buying lots 1, 4, 7, and 9 will meet the 25,000-widget requirement but save you more than $5,000 compared to trial #15.
Because you have modeled all 1,024 possible combinations, you can be sure that this answer is the lowest possible cost. Using Solver’s Evolutionary Methods may or may not find this answer.
SF SAYS
The Data Table is the key to repeating the model logic more than 1,000 times.
August 2019
