The city of Beckley, West Virginia, has solicited bids from interested construction firms for five projects it wishes to complete during this fiscal year. Six firms have submitted bids on the projects, as indicated in the table. (An “X” means that the firm did not submit a bid for that project.) Since the projects will be ongoing simultaneously, no firm will be able to complete more than one project. Formulate this assignment problem as a linear program and use Excel Solver to solve it.
| Refurbish courthouse | Build new Library | Modernize Playground | Build Parking Structure | Improve city Park | |
| Millard Associates | $800k | $750k | $300k | $450k | $200k |
| QM Construction | $950k | $725k | X | $500k | $275k |
| Latham Brothers X | X | $200k | X | $250k | X |
| Beckley Engineering | $650k | $700k | $250k | $400k | $225k |
| WRT Inc | $700k | $800k | $175k | $300k | $300k |
| B&P Enterprises | $850k | $900k | $270k | $475k | X |
(adapted from Lawrence and Pasternack, Problem Chapter 4.2)
Hint: Define X<sub>ij</sub> = 1 when project j is assigned to firm i, 0 when project j is not assigned to firm i.
For example, X<sub>41</sub> = 1 or 0 as Refurbish Courthouse assigned to Beckley Engineering or not.
- The number of projects assigned to Beckley will be X<sub>41</sub> + X<sub>42</sub> + X<sub>43</sub> + X<sub>44</sub> + X<sub>45</sub>, which needs to be ≤ 1, instead of = 1. The reason is there are 5 projects and 6 firms. If you make it =1 for all firms, the problem becomes infeasible.
- So for the first set of constraints (number of projects each firm gets), it needs to be ≤ 1.
- For the second set of constraints (number of firms each project gets assigned to), it needs to be = 1.
Also, you need to set X<sub>31</sub> = 0 so that project one will not be assigned to firm three. Do so for all other crosses.
D2.3
Modify Question set 1 in the lecture by replacing style with location, use PivotTable to find the answers for the two revised questions.
(Please copy and save the pivot table as values first in Excel. Then copy the resulting table here so that the table can properly display.)
D2.4
- Create a bar chart combo based on number of houses grouped by column age, in which two separate bar chart are created, one for the Split level style, the other for Expanded ranch style.
Copy the chart and save it as a picture with Microsoft Paint or similar app for MacBook. Then post the picture here to share. - Create a line chart for the column Values, sorted first by Style (A-Z), then by LotSize (smallest to largest).
Save as a picture to post here. - Create a scatter plot for X=Values, Y=Taxes for the style of Cape and Colonial houses only.
Save as a picture to post here.