| Monte Carlo Simulation: Bidding for a
Government Contract |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Background:
Miller Construction Company is trying to decide whether to make a bid on a
construction project. It would cost the company $10,000 to complete the
project and costs $350 to bid for the project. |
|
|
|
|
|
| What
is a Monte Carlo Simulation? |
|
| Monte
Carlo simulation is a network model can be used as an aid in the scheduling
of large, complex projects that consist of many activities. When the
durations of activities are not known with certainty, Monte Carlo simulations
are used to estimate the probability that a project will be completed within
the desired time. |
|
|
|
| How
is Monte Carlo Simulation performed? |
|
| Monte Carlo was performed
by using modeling software called @Risk. This software allows for the user to
set the distributions they wish, calculate various iterations and simulations
as well as design different graphs. I found the @Risk tutorial to be helpful
and user friendly. As for generating the values of these inputs it is done by
clicking on the sensitivity icon. |
|
|
|
|
|
Range names used:
Bid Cost: B4
Project Cost: B5
Possible Bids - B13:K13
MillerBid - B15
CompBids: B19:E19
|
|
|
|
|
|
|
| Bidding Problem |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Inputs |
|
|
|
|
|
|
|
|
| Our cost to prepare a bid |
$350 |
|
|
|
|
|
|
|
| Our cost to complete project |
$10,000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Assumption on each competitor's bid |
|
|
|
|
|
|
|
| Each competitor bids a multiple of our bid, where the
multiple is triangularly distributed with parameters: |
|
|
| Min |
0.9 |
|
|
|
|
|
|
|
|
|
|
| Most likely |
1.3 |
|
|
|
|
|
|
|
|
|
|
| Max |
2.5 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Miller's possible bids |
$10,500 |
$11,000 |
$11,500 |
$12,000 |
$12,500 |
$13,000 |
$13,500 |
$14,000 |
$14,500 |
$15,000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Miller's bid |
$10,500 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Simulated competitor bids |
|
|
|
|
|
|
|
|
|
|
| Competitor |
1 |
2 |
3 |
4 |
|
|
|
|
|
|
|
| Competitor's bid |
$15,667
|
$15,667
|
$15,667
|
$15,667
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Simulation model for Miller |
|
|
|
|
|
|
|
|
|
|
|
Wins bid? |
Profit |
|
|
|
|
|
|
|
|
|
|
1 |
$150 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| When
you measure Sensitivity, one should know that if a value is zero, then you
should conclude that input variables had no effect on the output. |
|
| However,
if a positive value is represented, then you can also conclude that the input
has increased along with the output. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| The
graph demonstrated is a tornado graph. A tornado graph compares the magnitude of impact of |
|
| of each
input by the size of the bar, and whether the impact is positive or negative
by the direction of the bar. |
|
|
|
| X-axis
represents % change in output value |
|
| Y-axis
represents input values |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| *the above chart is a histogram |
|
|
|
|
|
| Scenario
Analysis is another way to measure the influence of input
values on the output. In addition, it is based on a conditional median
analysis. |
|
| *This link will demonstrate a scenario
analysis with actual, median/SD and percentiles of each simulation |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Cell |
Name |
B23 /
Wins bid? (Sim#1) Actual |
B23 / Wins bid? (Sim#2) Percentile |
B23 /
Wins bid? (Sim#7) Median/SD |
B23 / Wins bid? (Sim#7) Actual |
B23 / Wins bid? (Sim#7) Percentile |
B23 / Wins bid? (Sim#7) Median/SD |
B23 / Wins bid? (Sim#8) Actual |
B23 / Wins bid? (Sim#8) Percentile |
B23 / Wins bid? (Sim#8) Median/SD |
B23 / Wins bid? (Sim#8) Actual |
B23 / Wins bid? (Sim#8) Percentile |
B23 / Wins bid? (Sim#8) Median/SD |
|
| |
Scenario= |
>90% |
>90% |
<25% |
>90% |
>90% |
>90% |
>75% |
>75% |
>75% |
>90% |
>90% |
>90% |
|
|
|
| B15 |
Miller's
bid |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
|
|
| B19 |
Competitor's
bid |
-- |
-- |
-- |
1.696761 |
66.45% |
0.519849 |
-- |
-- |
-- |
1.703221 |
67% |
0.538842 |
|
|
|
| C19 |
Competitor's
bid |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
1.723976 |
68.70% |
0.599086 |
|
|
|
| D19 |
Competitor's
bid |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
1.717579 |
68.20% |
0.582436 |
|
|
|
| E19 |
Competitor's
bid |
-- |
-- |
-- |
1.707513 |
67.35% |
0.55097 |
-- |
-- |
-- |
1.747102 |
70.50% |
0.667348 |
|
|
|
|
|
|
|
|
|
Name |
Cell |
Sim# |
Minimum |
Mean |
Maximum |
x1 |
p1 |
x2 |
p2 |
x2-x1 |
p2-p1 |
|
| Output 1 |
Wins bid? |
B23 |
1 |
0 |
0.867 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
2 |
0 |
0.778 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
3 |
0 |
0.673 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
4 |
0 |
0.546 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
5 |
0 |
0.415 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
6 |
0 |
0.304 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
7 |
0 |
0.215 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
8 |
0 |
0.147 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
9 |
0 |
0.115 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 1 |
Wins bid? |
B23 |
10 |
0 |
0.08 |
1 |
0 |
5% |
1 |
95% |
1 |
90% |
|
| Output 2 |
Profit |
C23 |
1 |
-350 |
83.5 |
150 |
-350 |
5% |
150 |
95% |
500 |
90% |
|
| Output 2 |
Profit |
C23 |
2 |
-350 |
428 |
650 |
-350 |
5% |
650 |
95% |
1000 |
90% |
|
| Output 2 |
Profit |
C23 |
3 |
-350 |
659.5 |
1150 |
-350 |
5% |
1150 |
95% |
1500 |
90% |
|
| Output 2 |
Profit |
C23 |
4 |
-350 |
742 |
1650 |
-350 |
5% |
1650 |
95% |
2000 |
90% |
|
| Output 2 |
Profit |
C23 |
5 |
-350 |
687.5 |
2150 |
-350 |
5% |
2150 |
95% |
2500 |
90% |
|
| Output 2 |
Profit |
C23 |
6 |
-350 |
562 |
2650 |
-350 |
5% |
2650 |
95% |
3000 |
90% |
|
| Output 2 |
Profit |
C23 |
7 |
-350 |
402.5 |
3150 |
-350 |
5% |
3150 |
95% |
3500 |
90% |
|
| Output 2 |
Profit |
C23 |
8 |
-350 |
238 |
3650 |
-350 |
5% |
3650 |
95% |
4000 |
90% |
|
| Output 2 |
Profit |
C23 |
9 |
-350 |
167.5 |
4150 |
-350 |
5% |
4150 |
95% |
4500 |
90% |
|
| Output 2 |
Profit |
C23 |
10 |
-350 |
50 |
4650 |
-350 |
5% |
4650 |
95% |
5000 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
1 |
10500 |
10500 |
10500 |
10500 |
5% |
10500 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
2 |
11000 |
11000 |
11000 |
11000 |
5% |
11000 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
3 |
11500 |
11500 |
11500 |
11500 |
5% |
11500 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
4 |
12000 |
12000 |
12000 |
12000 |
5% |
12000 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
5 |
12500 |
12500 |
12500 |
12500 |
5% |
12500 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
6 |
13000 |
13000 |
13000 |
13000 |
5% |
13000 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
7 |
13500 |
13500 |
13500 |
13500 |
5% |
13500 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
8 |
14000 |
14000 |
14000 |
14000 |
5% |
14000 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
9 |
14500 |
14500 |
14500 |
14500 |
5% |
14500 |
95% |
0 |
90% |
|
| Input 1 |
Miller's bid |
B15 |
10 |
15000 |
15000 |
15000 |
15000 |
5% |
15000 |
95% |
0 |
90% |
|
| Input 2 |
Competitor's bid |
B19 |
1 |
0.920906 |
1.566693 |
2.472776 |
1.07838 |
5% |
2.187115 |
95% |
1.108735 |
90% |
|
| Input 2 |
Competitor's bid |
B19 |
2 |
0.920906 |
1.566693 |
2.472776 |
1.07838 |
5% |
2.187115 |
95% |
1.108735 |
90% |
|
| Input 2 |
Competitor's bid |
B19 |
3 |
0.920906 |
1.566693 |
2.472776 |
1.07838 |
5% |
2.187115 |
95% |
1.108735 |
90% |
|
| Input 2 |
Competitor's bid |
B19 |
4 |
0.920906 |
1.566693 |
2.472776 |
1.07838 |
5% |
2.187115 |
95% |
1.108735 |
90% |
|
| Input 2 |
Competitor's bid |
B19 |
5 |
0.920906 |
1.566693 |
2.472776 |
1.07838 |
5% |
2.187115 |
95% |
1.108735 |
90% |
|
| Input 2 |
Competitor's bid |
B19 |
6 |
0.920906 |
1.566693 |
2.472776 |
1.07838 |
5% |
2.187115 |
95% |
1.108735 |
90% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
