A Utility Company continued another approach

Suppose that the utility company in Example 2 in the Lecture Notes is considering the possibility of building a second (more efficient) plant. Recall that the Sales Response Curve was given as Q = 200 P/5 where P is a nonnegative number denoting the price ($ per kilowatt). Also, the cost of its existing plant, now called Plant 1, and the cost of the new plant, now called Plant 2, are given below:

Plant 1 10,050 + 5Q12

Plant 2 5,000 + 2Q22

where Q1 and Q2 denote the respective kilowatts produced at the respective plants. Thus, Q = Q1 + Q2.

(a) Suppose that company has decided to price electricity at $500 so that the demand is 100 kilowatts. Build an Excel model that computes the profit when Plant 1 produces 50% of the 100 kilowatts and Plant 2 produces the other 50%. (Submit answer in an Excel file)

(b) Consider the following 5 production alternatives at the $500 price. Construct a Decision Table with Excel without using Data Table. Which Alternative has the biggest profit?

Alternative 1 produce 0% of Q at Plant 1 (and, therefore, 100% at Plant 2)

Alternative 2 - produce 25% of Q at Plant 1 (and, therefore, 75% at Plant 2)

Alternative 3 - produce 50% of Q at Plant 1 (and, therefore, 50% at Plant 2)

Alternative 4 - produce 75% of Q at Plant 1 (and, therefore, 25% at Plant 2)

Alternative 5 - produce 100% of Q at Plant 1 (and, therefore, 0% at Plant 2)

(Submit answer in an Excel file)

(c) Repeat (b) above using Data Table. (Submit answer in an Excel file)

(d) Rather than simply using a Decision Table in column form, we will instead build a Decision Table with rows (the left side of the table) denoting possible prices ($100, $200,..., $1000) and with columns (the top of the table) denoting the % production at Plant 1 (0%, 25%, 50%, 75%, 100%). For each cell corresponding to a row and column, use Data Table to compute the corresponding profit. ((Submit answer in an Excel file)

(e) Use the Max function to find the maximum profit in (d). Use conditional formatting to indicate in red which objective value is optimal. What is (are) the corresponding optimal solution(s)? (Submit answer in an Excel file)