Explore BrainMass

EXCEL Formulas to Calculate Profit For Electrical Contractor

I need help in developing the formulas in excel for the attached problem. I need the response to be in MS Excel with the appropriate formulas there for me to see.

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.


Solution Preview

Attached is the Excel file containing the solution. I have coded the formulas to show the ...

Solution Summary

Attached is the Excel file containing the solution. I have coded the formulas to show the win/loss and profit given your bid/cost, competitors(1 and 2) bids. I have already generated some random cases based on the suggested ranges. Take a look at how that is done. You can enter your own estimate of your cost and bid to see if it might win!