Table-1 Segment Table-2 Table-3 SURPLUS/DEFICIT for EACH SEGMENT AT GIVEN PRICES Conventional Green Co-Green Size (%) what is bought by Conventional Green Co-Green segment profit from segment 1 $2.0 $6.0 $3.75 20 ($4.00) $0.00 ($1.25) 2 $80.00 2 $3.0 $4.0 $3.50 30 ($3.00) ($2.00) ($1.50) 0 $0.00 3 $5.0 $4.0 $4.25 35 ($1.00) ($2.00) ($0.75) 0 $0.00 4 $6.0 $0.5 $3.00 15 $0.00 ($5.50) ($2.00) 1 $75.00 Assume 100 customers product type cost unit price unit profit 1 2 3 $1.00 $2.00 $1.50 $6.00 $6.00 $5.00 $5.00 $4.00 $3.50 Exam-Assignment SMUD, the local Sacramento utility wants to price energy supplied from conventional (coal/gas) and green sources (solar, wind). Customers can also buy both conventional and green together and SMUD offers this combination called "co-green". Assume there are four market segments whose size and valuations (maximum price customer is willing to pay per unit of energy) for each type of energy offering and the cost per unit are given above (Table-1). What unit prices maximize profits? Determine the price and unit profit cells with the values that maximizes profits (Table-2). The yellow cells contain tentative prices and the rest of the numbers in the greyed out cells are based on these prices. First, complete the greyed-out cells using appropriate formulas. Use Solver to maximize "Total Profit" green cell by changing the prices in yellow cells to maximize the objective "Total Profit". Table-3 values are derived by entering appropriate formulas (which were dicussed in chapter-5 in-class assignments). Total profit $155.00