Can you create an excel sheet for this case study?

Case Goal:

Determine how much we should bid on a construction (landscaping) project. This case focuses on setting a bid level in a competitive bidding setting. While there are many aspects of a bid to consider, not just the price, here we focus on price alone. It will be assumed that the minimum bid will be the bid that is selected.

Case Overview:

Landsky is considering a bid on a project to create another beautiful backyard. The general process is that company management visits the proposed job site to estimate the work, prepares, and submits the bid.

If the Landsky company is the low bidder or the only bidder, then they will win the project. The amount of profit they would receive is simply their winning bid minus all costs associated with the project.

Project Profit = Winning Bid - Cost to Execute Project - Cost to Prepare Bid

Note, that if they do not win the bid, they still must pay for preparing the bid. In this case,

Project Profit = - Cost to Prepare Bid

Therefore, it is up to each company who bids to determine how much to bid based on their estimates of the cost.

Landskys Management has estimated the cost of this project as well as the cost to prepare the bid. These values follow the normal probability distribution. The numbers are shown below:

Project Costs	Mean	Standard Deviation
Cost to Prepare Bid	$350	$50
Cost to Execute Project	$11,000	$1,500

In bidding for this project, Landsky may face up to 4 competing bids. Assume that the number of competing bids follows a binomial random variable with n = 4 trials, and p = 0.5 probability of success for each of the four trials.

Each of the competitors, who do place a bid, will bid between 90% and 190% of Landskys cost to execute the project. Assumes that these actual bid amounts are random and uniformly distributed over this range of 90% to 190% of Landskys cost.

As an example, suppose that Landskys randomly selected cost to execute the project was $10,500. Then, after computing how many competitors bid, compute the value for each of those bids by selecting a random number between $10,500*0.9 = $9450 and $10,500*1.9 = $19,950.

The lowest bid over all submissions wins the right to execute the project.

Landsky would like to know how much to bid to win the project for the company with the maximum expected profit. Potential values for bids that Landsky would like to consider include $10,000, $12,500, and so in increments of $2500.

They would also like to know the profit they should expect to obtain. One last challenge is that Landsky feels they need to win the bid at least 30% of the time to stay busy with new clients.

Case Technical Details:

Simulation should be used to model this case.