show work and if using excel show formulas used.

Your current problem is a "make vs. buy" decision with two potential suppliers in addition to the option of producing the component ourselves. The total annual cost under each alternative is as follows: Make: Annual fixed costs of $1,000,000 plus a per-unit production cost of $16.00 per unit Buy (Supplier A): Annual fixed costs of $50,000, plus a per-unit purchase price of $35.00/unit Buy (Supplier B): Annual fixed costs of $50,000, plus purchase price of $30.00/unit. However, in order to get this price, we need to commit to ordering a minimum of 40,000 units. (We will buy 40,000 units even if we need less than 40,000. We can sell the excess as described below.) We currently estimate the following probability distribution for total annual demand (D) d: 10,000 20,000 30,000 40,000 50,000 60,000 70,000 80,000 90,000 100,000 P(D = d): 0.180 0.200 0.190 0.150 0.100 0.070 0.050 0.030 0.020 0.010 To simplify the problem, we will assume we are only going to do this for one year. If we make the component in house or buy from supplier A, the quantity produced or purchased will equal demand. If we buy from Supplier B, (where we must buy at least 40,000 units) any unused items can be sold as scrap for $12.00 each when demand