Suppose you are an electrical contractor bidding for a job wiring a new office building. The contract will be awarded to the lowest bidder. If you win the contract, profit will be the amount you bid minus the cost of completing the job. You are uncertain about the cost as well as about the bids your competitors will submit. You base-case analysis indicates that the most likely cost to you is $450,000, but it could be significantly higher or lower. After analyzing some more cost scenarios, taking into account various outcomes for price levels, negotiations, with subcontractors and time to complete the job, you estimate the range of costs is $360,000 to $540,000. You will be bidding against two other companies. You feel that competitor #1 is most likely to bid around $600,000, but the possible range could be from $420,000 to $780,000. Competitor #2 is believed to have higher costs. You feel that they are most likely to bid around $650,000, but you feel the range for competitor #2's bid is $400,000 to $1,000,000.
Write formulas in excel that calculate the following:
1. Whether or not you win the contract. Put a 1 in the win/lose cell if you win and a 0 in the win/lose ell if you lose.
2. The associated profit given four inputs: your bid, competitor #1's bid, competitor #2's bid, and your cost. To compute profit if you win, subtract your cost from your bid and multiply the difference by the win/loss cell.