Frank Hartman is the procurement manager for Town & Country Toy, a chain of Toy companies, which has three plants in the area for which Frank is responsible. Over the years, Frank has developed relationships with six suppliers in the area, each of which has the ability to supply a certain number of the item, which Town & Country Toy has found most popular. Each supplier has a minimum order size of 5,000 units per month. Maximum order size varies from supplier to supplier, depending on their capacity to produce the desired item. The requirements (demands) at each plant, the unit shipping costs from each supplier to each plant, as well as the minimum and maximum order sizes for each supplier are given on the Procurement Data worksheet of theEXCELworkbook provided in Canvas in the link under this assignment.
Procurement cost for the month consists of the unit purchase costs plus the shipping cost. The unit purchase costs are given by the per-unit price for the unit times the number of units purchased summed across suppliers. The logistics shipping cost is just the usual sum of products encountered in the standard transportation problem. Sum these cost components, purchase and shipping, to get total procurement cost.
Step 0. Formulate Frank’s minimum procurement cost problem as a linear programming model (assuming that the applicable unit price is the first one given in the price break table). This is presented in class, so you just need to do the word processing and include it as an appendix to your Executive Summary.
Step 1. Familiarize yourself with the Named Ranges in the workbook by opening the drop-down list box in the upper left of your spreadsheet and selecting the listed names one at a time. Using the name for the unit purchase costs (UNIT PRICES)given in the first column of the unit cost table, and the name for the unit shipping costs in the shipping cost table (CIJ), complete the Procurement Cost function for the model using the provided SUMPRODUCT functions. Also complete the unit-weeks wait time formula using the provided SUMPRODUCT function. Specify the location of the Target cell, the Changing Cells, and the relevant constraints in the Solver Parameter dialog box, and solve the minimum Procurement Cost problem using the Solver. You can group similar constraints together by indicating a range of cells on both sides of the constraints. Hence there are upper and lower bounds for the amount purchased from each supplier, an equality constraint for the total amount purchased for each Plant. That will show as only three lines in the Solver Constraint list, but remember to check the Assume Linear Model and Assume Non-negative boxes in the Options dialog. Solve the model initially with a 0 time cost and save your result in the first row of your Scenario Results Table. Repeat the optimization but minimize total unit-week wait time the second time. This will give you the minimum unit-weeks solution (note the minimum unit-weeks obtained but do not save in table), and remember to reset the target cell to the Target cost after getting the minimum time result since all subsequent runs will be made using the combined procurement and time cost.
Step 2. In order to entice business away from the competitors, each supplier has developed a volume discount schedule which enables Hartman to reduce the unit cost if he agrees to buy more units. The volume discounts occur for all suppliers at 10,000 units, and 25,000 units, and 50,000 units. The schedule of volume discounts therefore has four unit prices for each supplier, decreasing from the price that applies when less than 10,000 are purchased, to the lowest price that applies when more than 50,000 units are purchased. The volume discount schedule of unit prices is also given in the Data worksheet provided for this analysis. He wants Procurement Cost to be computed, after the optimizations, taking account of these discount schedules. Note how the IF(cond,v1,v2) function has been used, nested three times, to compute the applicable unit price for each supplier’s volume in cells N3:N08, which re-compute procurement cost using the discounted prices, where applicable.
Step 3. Although cost is the main consideration, Frank is also concerned about how long it takes to deliver all of his order, considering the fact that lead times are different depending upon the particular plant-supplier combination in question. By multiplying the units shipped on each plant-supplier link times the lead-time for that link, and then summing over all possible links, Frank obtains a measure of the total time taken to deliver all orders. This is measured in unit-weeks of time. In order to penalize longer lead times, Frank introduces a fictitious “cost of waiting” coefficient WC to measure the monetary cost of having to wait one unit-week. Thus the objective function becomes procurement cost plus WC times wait time. Frank feels that by adjusting the size of WC, he can achieve the “perfect balance” between minimum cost and minimum time solutions. He tries out various “coin values” for WC, such as WC = $0.01, WC = $0.05, WC = $0.10, WC = $0.25, WC = $0.50 and W = $1.00 first, to make sure his intuition is correct about the impact of increasing WC on the relative magnitudes of procurement cost and waiting time. Frank tried this out himself, and tabulated the results in a four-column table showing WC and the corresponding values of wait time and procurement cost (both discounted and not discounted for volume purchases). Make a chart of these trial values to see that cost goes up as time comes down.
Step 4. Having confirmed his intuition that as WC increases, wait time decreases and procurement cost increases, Frank decides to fill in his table by trying a number of other intermediate values for WC in such a way as to identify more points along the full trade-off curve between time and cost. Of course, a brute force approach would be to define a regular grid of WC values and solve the problem repeatedly for each value in the grid. Frank realizes that if he only knew how to program in VBA, he could set up a loop in which the solver would be run repeatedly for each value of WC. But since he doesn’t know how to program in VBA yet, he decides on another heuristic which does not result in equally spaced WC grid, but could potentially involve many fewer values of WC. His idea is to let the next trial value of WC be the mid-point between the two values of WC already in the table for which the wait-time difference is the largest. This will often result in a new solution to be inserted into the table which will replace one interval with two smaller intervals. When all (or most) of the wait-time differences are less than 50,000 unit weeks (or as small as possible if greater than that), he stops and plots up his results, with wait-time along the horizontal axis and procurement cost up the vertical axis. He plots the discounted and non-discounted procurement costs versus wait time first on separate charts, and then, plots both series together in one chart. He uses the X-Y Scatter option with adjacent points connected by straight lines to obtain his charts using the ChartWizard in EXCEL.
Follow Frank’s procedure as described above, and comment on the results he obtains. Is there an interval on the right in which wait-time can be decreased substantially without incurring much of a rise in procurement costs? If so, what is the value for WC at which the “corner” on the curve occurs? Plot Procurement Cost and Discounted Procurement Cost on the same chart, noting the approximate difference between them that persists across almost the entire range of possible times. What qualitative difference between the two curves do you see? Does it appear that optimization of the model including the quantity discount price structure would lead to substantial savings over the LP solution?
As usual, the Executive Summary is to describe the problem(s) being solved, the method(s) being used, and the principal results. The Appendices are to contain the data and intermediate or supporting results from the computer runs. More specifically, questions that you should address in the Principal Results section of your Executive Summary are as follows:
1. What are the two extreme solutions you obtain when the two competing objectives, cost and time, are optimized separately. What is the cost difference between the minimum procurement cost solution and the minimum time solution? What is the unit-week time difference between them? What is the average cost per unit-week slope across the interval in question?
2. Show the table and the plot of the trade-off curve between time and cost obtained from your scenario analysis in which you varied the value of “WC” from 0 to 100 cents.
3. Note the “Virtually Free Time Savings” which are achieved for small WC values, and identify the WC* value which gives the left most point in that interval on the right tail of the trade-off curve. In locating WC* you should also take into account the discounted procurement cost results.
4. For W = WC* (which is your “base case”), print out the answer report and sensitivity report for the appendices. In addition, tabulate the Primal and Dual solutions in the executive summary. The Primal solution includes the positive shipments, the schedule of amounts purchased from each supplier, and the volumes delivered to each plant. Give the Procurement cost and unit-weeks for your WC* solution. For the Dual solution, show the shadow prices for the upper and lower bounds on the supplier totals in one table, and the shadow prices for the plant totals in another table. The tables should be the same shape and size as the RHS data tables that were provided for these constraints.
5. Based on the shadow prices presented, give recommendations regarding:
(a) Negotiations to eliminate the 5,000 minimum order size should be conducted with which supplier?
(b) Negotiations to increase capacity by 5,000 units should be conducted with which supplier?
6. Show the plots of the two trade-off curves the first using the LP procurement costs and the second using the volume discounted procurement costs. The volume discounted curve is more “ragged” than the undiscounted one is because the optimization criterion did not take into account the volume discounts. In order to get a smooth trade-off curve in the volume discounted case, the objective function would have to reflect the volume discounted costs by using binary variables in the model.
Appendices that you should include in your Executive Summary are as follows:
1. The data and network diagram for the analysis.
2. Mathematical formulation of the problems being solved as given in class (hint: use the Equation Editor on the Insert/Object menu to insert formula objects in Word).
Spreadsheets that you should include in your Excel workbook for the project include:
1. For your WC*, the spreadsheet solution obtained (the one which is tabulated in the executive summary).
2. For your WC*, the formula view of the part of your spreadsheet which has formulas in it.
3. For your WC*, the Answer Report and Sensitivity Report provided by the Solver (the ones which are tabulated in the executive summary).
4. Plots of time-cost trade-off curves using volume discounted Procurement costs in comparison with those obtained using the Procurement costs given by the LP model approximation.
EXTRA CREDIT (15 points)
Download the SolverTable add-in for EXCEL which automates the process of rerunning EXCEL to generate a table of solutions parameterized by changing values of the WC parameter. Since the SolverTable add-in resolves for a range of input parameters (cost of waiting in unit-weeks) on an equally spaced grid, you will want to vary the resolution of the grid, and run the SolverTable add-in several times for different interval widths, and then concatenate your results into a single table. The grid will be finer for smaller values for the cost of time, and courser for larger values for the cost of time.