Question
Offered Price $35.00

BUS190 - FALL 2013 - Linear Programming PROJECT #2 PROCUREMENT OPTIMIZATION with Time-Cost

Question # 00012468
Subject: Statistics
Due on: 05/12/2014
Posted On: 04/16/2014 03:46 AM

Rating:
4.1/5
Expert tutors with experiences and qualities
Posted By
Best Tutors for school students, college students
Questions:
4719
Tutorials:
4987
Feedback Score:

Purchase it
Report this Question as Inappropriate
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.

Attachments

Tags procurement optimization timecost project programming fall 0 linear bus190 cost procurement time unit costs table frank volume minimum values using discounted solution results units times supplier shipping wait purchased given tradeoff price grid curve plant provided prices order model

Tutorials for this Question
Available for
$37.00

BUS190 - FALL 2013 - Linear Programming PROJECT #2 PROCUREMENT OPTIMIZATION with Time-Cost

Tutorial # 00012019
Posted On: 04/16/2014 03:49 AM
Posted By:
Best Tutors for school students, college students vikas
Expert tutors with experiences and qualities
Questions:
4719
Tutorials:
4987
Feedback Score:
Report this Tutorial as Inappropriate
Tutorial Preview …100 xxxxx Wc xxxx Cost Disc xxxx 0 0000 xxxxxxx $1,272,680 xx xxxxxxxxxx 60 x 0100 1316000 xxxxxxxxxx 20 $1,012,751 xx 0 xxxx xxxxxxx $1,272,711 xx $1,012,751 20 x 1000 1316000 xxxxxxxxxx 20 xxxxxxxxxx xx 0 xxxx 1284000 $1,277,561 xx $1,006,081 70 x 1250 xxxxxxx xxxxxxxxxx 70 xxxxxxxxxx 70 0 xxxx 1284000 $1,277,561 xx $1,006,081 xx x 3218 xxxxxxx $1,279,973 20 xxxxxxxxxx 20 0 xxxx 1191000 xxxxxxxxxx xx $1,028,638 xx 0 3225 xxxxxxx $1,307,498 50 xxxxxxxxxx 50 x xxxx 1191000 xxxxxxxxxx 50 $1,028,638 xx 0 3750 xxxxxxx $1,307,498 xx xxxxxxxxxx 50 x 4000 1191000 xxxxxxxxxx 50 $1,028,638 xx 0 xxxx xxxxxxx $1,307,498 xx $1,028,638 50 x 4500 1129000 xxxxxxxxxx 50 xxxxxxxxxx xx 0 xxxx 1129000 $1,335,029 xx $1,044,929 50 x 5000 xxxxxxx xxxxxxxxxx 50 xxxxxxxxxx 50 0 xxxx 1129000 $1,335,029 xx $1,044,929 xx x 7500 xxxxxxx $1,335,029 50 xxxxxxxxxx 50 1 xxxx 1129000 xxxxxxxxxx xx $1,044,929 xx 3 Note xxx "Virtually Free xxxx Savings" xxxxx xxx achieved xxx small WC xxxxxxx and identify xxx WC* xxxxx xxxxx gives xxx left most xxxxx in that xxxxxxxx on xxx xxxxx tail xx the trade-off xxxxx In locating xxx you xxxxxx xxxx take xxxx account the xxxxxxxxxx procurement cost xxxxxxx Wc xxxx xxxx Disc xxxx 0 0000 xxxxxxx $1,272,680 60 xxxxxxxxxx 60 x xxxx 1316000 xxxxxxxxxx 20 $1,012,751 xx 0 0500 xxxxxxx $1,272,711 xx xxxxxxxxxx 20 x 1000 1316000 xxxxxxxxxx 20 $1,012,751 xx 0 xxxx xxxxxxx $1,277,561 xx $1,006,081 70 xxxx the table, xxxxxxxxx point xxxxxx xx WC=0 xx 4 W x WC*, answer xxxxxx and xxxxxxxxxxx xxxxxx for xxx appendices In xxxxxxxxx tabulate the xxxxxx and xxxx xxxxxxxxx in xxx executive summary xxx Primal solution xxxxxxxx the xxxxxxxx xxxxxxxxxx the xxxxxxxx of amounts xxxxxxxxx from each xxxxxxxxx and xxx xxxxxxx delivered xx each plant xxxx the Procurement xxxx and xxxxxxxxxx xxx your xxx solution For xxx Dual solution, xxxx the xxxxxx xxxxxx for xxx upper and xxxxx bounds on xxx supplier xxxxxx xx one xxxxxx and the xxxxxx prices for xxx plant xxxxxx xx another xxxxx The tables xxxxxx be the xxxx shape xxx xxxx as xxx RHS data xxxxxx that were xxxxxxxx for xxxxx xxxxxxxxxxx WC* xxxxxxxxxxx cost = xxxxxxxxxx 50 Procurement xxxxxxxxxx cost x…
Attachments
Project2_Workbook_S.xls (63 KB)
Preview not available.
PROJECT_2-LP_(2).docx (52.92 KB)
Preview: the xxxxxxxxx curve xxxxxxx time and xxxx obtained from xxxx scenario xxxxxxxx xx which xxx varied the xxxxx of "WC" xxxx 0 xx xxx cents xxxxxxxxxxxxxx cost0 00001366000$1,272,680 xxxxxxxxxxxx 600 01001316000$1,272,711 xxxxxxxxxxxx 200 xxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx 200 xxxxxxxxxxxxxxxxxxxxx 20$1,012,751 200 xxxxxxxxxxxxxxxxxxxxx 70$1,006,081 700 xxxxxxxxxxxxxxxxxxxxx 70$1,006,081 xxx xxxxxxxxxxxxxxxxxxxxx 70$1,006,081 xxx 32181276500$1,279,973 20$1,008,943 xxx 32501191000$1,307,498 50$1,028,638 xxx 32251191000$1,307,498 xxxxxxxxxxxx xxx 32341191000$1,307,498 xxxxxxxxxxxx 500 37501191000$1,307,498 xxxxxxxxxxxx 500 40001191000$1,307,498 xxxxxxxxxxxx 500 xxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx 500 xxxxxxxxxxxxxxxxxxxxx 50$1,044,929 500 xxxxxxxxxxxxxxxxxxxxx 50$1,044,929 500 xxxxxxxxxxxxxxxxxxxxx 50$1,044,929 xxx xxxxxxxxxxxxxxxxxxxxx 50$1,044,929 xxx 75001129000$1,335,029 50$1,044,929 xxx 00001129000$1,335,029 50$1,044,929 xxxxxx the xxxxxxxxxx xxxx Time xxxxxxxx which are xxxxxxxx for small xx values, xxx xxxxxxxx the xxx value which xxxxx the left xxxx point xx xxxx interval xx the right xxxx of the xxxxxxxxx curve xx xxxxxxxx WC* xxx should also xxxx into account xxx discounted xxxxxxxxxxx xxxx results xxxxxxxxxxxxxx cost0 00001366000$1,272,680 xxxxxxxxxxxx 600 01001316000$1,272,711 xxxxxxxxxxxx 200 xxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx 200 xxxxxxxxxxxxxxxxxxxxx 20$1,012,751 200 xxxxxxxxxxxxxxxxxxxxx 70$1,006,081 70From xxx table, xxxxxxxxx xxxxx occurs xx WC=0 01W x WC*, answer xxxxxx and xxxxxxxxxxx xxxxxx for xxx appendices In xxxxxxxxx tabulate the xxxxxx and xxxx xxxxxxxxx in xxx executive summary xxx Primal solution xxxxxxxx the xxxxxxxx xxxxxxxxxx the xxxxxxxx of amounts xxxxxxxxx from each xxxxxxxxx and xxx xxxxxxx delivered xx each plant xxxx the Procurement xxxx and xxxxxxxxxx xxx your xxx solution For xxx Dual solution, xxxx the xxxxxx xxxxxx for xxx upper and xxxxx bounds on xxx supplier xxxxxx xx one xxxxxx and the xxxxxx prices for xxx.....
PROJECT_2-LP_(2).docx (52.92 KB)
Preview: take xxxx account xxx discounted procurement xxxx results WcTimeCostDisc xxxxx 00001366000$1,272,680 xxxxxxxxxxxx xxx 01001316000$1,272,711 xxxxxxxxxxxx 200 05001316000$1,272,711 xxxxxxxxxxxx 200 10001316000$1,272,711 xxxxxxxxxxxx 200 xxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx 70From xxx table, left-most xxxxx occurs at xxxx 01W x xxxx answer xxxxxx and sensitivity xxxxxx for the xxxxxxxxxx In xxxxxxxxx xxxxxxxx the xxxxxx and Dual xxxxxxxxx in the xxxxxxxxx summary xxx xxxxxx solution xxxxxxxx the positive xxxxxxxxxx the schedule xx amounts xxxxxxxxx xxxx each xxxxxxxxx and the xxxxxxx delivered to xxxx plant xxxx xxx Procurement xxxx and unit-weeks xxx your WC* xxxxxxxx For xxx xxxx solution, xxxx the shadow xxxxxx for the xxxxx and xxxxx xxxxxx on xxx supplier totals xx one table, xxx the xxxxxx xxxxxx for xxx plant totals xx another table xxx tables xxxxxx xx the xxxx shape and xxxx as the xxx data xxxxxx xxxx were xxxxxxxx for these xxxxxxxxxxx WC* Procurement xxxx =$1,477,379 xxxxxxxxxxxxx xxxxxxxxxx cost x $ 1,152,139 xx Unit weeks x 1,129,000Microsoft xxxxx xx 0 xxxxxx ReportWorksheet: [Project2_Workbook_S14A-instructure xxxxxxxxxxxxxxxxxxx Cell (Min)CellNameOriginal xxxxxxxxxx Value$N$16unit-weeks xxxxxxxxx xx 1,129,000 xx Variable CellsCellNameOriginal xxxxxxxxxx ValueInteger$J$3S1 P1 xxxxxxxxxxxxxxxxxxxxxx P200Contin$L$3S1 xxxxxxxxxxxxxxxxxxxxxxxx xx 8300083000Contin$K$4S2 xxxxxxxxxxxxxxxx P300Contin$J$5S3 P1 xxxxxxxxxxxxxx P29000090000Contin$L$5S3 P300Contin$J$6S4 xx.....
Project2_Workbook_S.xls (63 KB)
Preview not available.
Purchase this Tutorial @ $37.00 *
* - Additional Paypal / Transaction Handling Fee (3.9% of Tutorial price + $0.30) applicable
Loading...