Question

Reliable Construction Company Reliable Construction Company is deciding how much they will bid for a contract to build a new plant for a major manufacturer. Based on the specifications of the new plant, Reliable has estimated what the company’s total cost would be if it were to undertake the project. Reliable estimates that the project would cost $4.55 million (M) and are thinking about bidding $5.4 M in the proposal. Reliable also estimates that the cost of preparing a proposal would cost $50,000. Three other construction companies also were invited to submit bids for this project. All three have been long-standing competitors of the Reliable Construction Company, so the company has had a great deal of experience in observing their bidding strategies. A veteran analyst in the bid preparation office has taken on the task of estimating what bid each of these competitors will submit. Since there is so much uncertainty in this process, the analyst has determined that each of these estimates needs to be in the form of a triangular probability distribution. Competitor 1 is known to use a 30 percent profit margin above the total (direct) cost of a project in setting its bid. However, competitor 1 also is a particularly unpredictable bidder because of an inability to estimate the true costs of a project with much accuracy. Its actual profit margin on past bids has ranged from as low as minus 5 percent to as high as 60 percent. Competitor 2 uses a 25 percent profit margin and is somewhat more accurate than competitor 1 in estimating project costs, but it still has set bids in the past that have missed this profit margin by as much as 15 percent in either direction. On the other hand, competitor 3 is unusually accurate in estimating project costs (as is the Reliable Construction Co.). Competitor 3 has a history of also is adept at adjusting its bidding strategy, so it is likely to set its profit margin between 20 and 30 percent. By knowing this information, the analyst has been able to estimate three key numbers for each competitor: a minimum value (a), a most likely value (m), and a maximum value (b) for the profit margin. By knowing all of this information, Reliable would like to investigate if they should consider changing their initial bid offer of $5.6 M. 1. Given the cost of the project, the cost of preparing the proposal, and the currently proposed bid amount, compute the profit and the profit margin. a. Compute profit margin as profit over total cost. 2. Based on the analysis conducted by the analyst, use the following information in order to compute the minimum, most likely and maximum bid values for each competitor. a. NOTE: No work needs to be completed for this part. However, the information will be used to complete the next part. 3. Use the percentages in the previous part to determine the minimum, most likely and maximum bid values for each competitor. a. HINT: Base your calculations on the cost of the project. Do not include the cost of preparing the bid. 4. Generate a random number between 0 and 1. 5. Using the random number generated in the previous part, randomly generate three bid values for each competitor based on a triangular distribution. 6. Determine the minimum bid value from the competition. Assume that a minimum bid will win the proposal. Develop a formula that results in a “Y” if Reliable would have won the bid based on the minimum amount found from the competition and the current bid amount. If Reliable would not have won the bid under the current circumstance, the formula should result in an “N.” Be sure to make this formula dynamic. In other words, do not hard code the current bid amount. Finally, calculate the profit assuming Reliable has submitted a full proposal. Thus, if they do not win, their profit will be negative (i.e. cost of preparing the proposal). 7. Use a Data Table to generate 200 replicates of the simulation developed in the previous part. 8. In order to understand the most attractive competitive bid coming from the competition, calculate the various descriptive statistics based on the Data Table. 9. Create a histogram for the minimum competitor's bid from the Data Table. 10. Calculate the frequency and the percentage of how many times Reliable either lost or won the contract. 11. Create a pie chart based on the percentage of “Y” and “N,” where data labels show the Category Name, Percentage, and Legend Key. 12. Use the Scenario Manager to observe the change in Profit, Net Profit Margin, and likelihood of Reliable winning the proposal (i.e. % of “Y”) when the initial bid amount is varied from $5.5 M, $5.4 M, $5.3 M, $5.2 M, $5.1 M, and %5.0 M. 13. If the manager of Reliable Construction Company is worried that their current bid amount of $5.6 M is not competitive enough, what should the manager consider lowering the bid amount to if the company has a policy that they need to make at least 15% net profit margin?

Answer 1