Problem 1 - The management of Madeira Computing is considering the introduction of a wearable electronic device with the functionality of a laptop computer and phone. The fixed cost to launch this new product is $300,000. The variable cost for the product is expected to be between $160 and $240, with a most likely value of $200 per unit. The product will sell for $300 per unit. Demand estimates for the produce vary widely, ranging from 0 to 20,000 units, with an average of 4000 units.
a. Compute profit for the base-case, worst-case, and best-case scenarios.
b. Assume the variable cost is a uniform random variable between $16 and $24 and the product demand is an exponential random variable with a mean of 4000 units. Construct a simulation model to estimate the mean profit and the probability that the project will result in a loss.
Problem 2 - Grear Tire Company has produced a new tire with an estimated mean lifetime mileage of 36,500 miles. Management also believes that the standard deviation is 5000 miles and that tire mileage is normally distributed. To promote the new tire, Grear has offered to refund a portion of the purchase price if the tire fails to reach 30,000 miles before the tire needs to be replaced. Specifically, for tires with a lifetime below 30,000 miles, Grear will refund a customer $1 per 100 miles short of 30,000.
a. For each tire sold, what is the expected cost of the promotion?
b. What is the probability that Grear will refund more than $50 for a tire?
c. What mileage should Grear set the promotion claim if it wants the expected cost to be $2?
Problem 3 - South Central Airlines (SCA) operates a commuter flight between Atlanta and Charlotte. The regional jet holds 50 passengers, and currently SCA only books up to 50 reservations. Past data show that SCA always sells all 50 reservations, but on average, two passengers do not show up for the flight. As a result, with 50 reservations the flight is often being flown with empty seats. To capture additional profit, SCA is considering an overbooking strategy in which they would accept 52 reservations even though the airplane holds only 50 passengers. SCA believes that it will be able to always book all 52 reservations. The probability distribution for the number of passengers showing up when 52 reservations are accepted is estimated as follows:
SCA receives a marginal profit of $100 for each passenger who books a reservation (regardless whether they show up or not). The airline will also incur a cost for any passenger denied seating on the flight. This cost covers added expenses of rescheduling the passenger as well as loss of goodwill, estimated to be $150 per passenger. Develop a spreadsheet simulation model for this overbooking system and simulate the number of passengers that show up for a flight.
a. What is the average net profit for each flight with the overbooking strategy?
b. What is the probability that the net profit with the overbooking strategy will be less than the net profit without overbooking (50*$100 5 $5000)?
c. Explain how your simulation model could be used to evaluate other overbooking levels such as 51, 53, and 54 and for recommending a best overbooking strategy.
Problem 4 - A building contractor is preparing a bid on a new construction project. Two other contractors will be submitting bids for the same project. Based on past bidding practices and the requirements of the project, the bid from Contractor A can be described with a uniform distribution between $600,000 and $800,000, while the bid from Contractor B can be described with a normal distribution with a mean of $700,000 and standard deviation of $50,000.
a. If the building contractor submits a bid of $750,000, what is the probability that the building contractor will obtain the bid?
b. The building contractor is also considering bids of $765,000 and $775,000. If the building contract would like to bid such that the probability of winning the bid is about 0.80, what bid would you recommend? Repeat the simulation with bids of $765,000 and $775,000 to justify your recommendation.
Please answer questions using excel when requested. Show all work in Excel as well.