ProJeans Calculation

	A	B	C	D	E	F	G	I	J	K	L
1	Instructions - The statement of the problem is here: https://www.mipwise.com/use-cases/projeans. - You can't edit this Google Sheet, but you can go to File > Make a copy or File > Download to have a copy of it. - You should ONLY modify the values highlighted under Order Quantity. - If the cell turns pink, the value you entered violates the minimum or maximum order quantity. - Your goal is to reduce the total cost (Cell G16). - Make sure that you meet the demand exactly, which is the case when there are zeros under Deficit and Surplus. - If you have any questions or comments, reach out to us: https://www.mipwise.com/contact. OBS: We don't advise anyone to use this spreadsheet to make actual business decisions as it was created to run a simple experiment and may contain errors. Insights about this experiement can be found here: https://www.mipwise.com/blogs/solving-a-sourcing-problem-with-spreadsheets.
2
3	SKU ID	Supplier ID	Order Quantity	Min Order Quantity	Max Order Quantity	Total Cost
4	1	B	4,000	1,000	7,000	19,460.00
5	1	E	3,500	1,500	8,000	20,405.00
6	1	M	4,500	1,000	8,500	27,045.00
7	2	B	2,500	1,500	5,500	13,500.00
8	2	E	1,500	1,000	5,000	9,750.00
9	2	M	2,000	500	4,500	13,265.00
10
11
12	Supplier ID	Skus - Quantities	Procurement Cost	Number of Containers	Shipping Cost per Container	Total Shipping Cost	Total Cost
13	B	6,500	32,960.00	3	4,650.00	13,950.00	46,910.00
14	E	5,000	30,155.00	3	7,950.00	23,850.00	54,005.00
15	M	6,500	40,310.00	3	3,350.00	10,050.00	50,360.00	Optimal Cost*	Unlocked Saving**	Solution Time
16	Totals	18,000	103,425.00	9			151,275.00	125,590.00	16.98%	0.16 seconds	165445.64
17								* This value is only valid for the original input data. If you edit the data in any of the input tables, such as price_tiers, the optimal solution may change and the optimal cost will not automatically update here.
18
19	SKU ID	Demand	Quantities	Deficit	Surplus
20	1	12,000	12,000	0	0			** The Unlocked Saving percentage indicates how far the current solution is from the optimal solution (with the original data). For example, if the Unlocked Saving is 14.47%, it means that there is a better solution that will save you $21,255 in total cost.
21	2	6,000	6,000	0	0
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100