Question;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?;TURN-INS;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.
Paper#62216 | Written in 18-Jul-2015Price : $42