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.
