(Solved): Suppose that each day, Northern, Central, and Southern California each use 100 billion gallons of wa ...

Suppose that each day, Northern, Central, and Southern California each use 100 billion gallons of water. Also assume that Northern California and Central California have available 120 billion gallons of water, whereas Southern California has 40 billion gallons of water available. The cost of shipping 1 billion gallons of water between the three regions is as follows. Also, you will not be able to meet all the demand for water, so assume that each billion gallons of unmet demand incurs the following shortage costs: Suppose that each day, northern, central, and southern California each use 100 bil- lion gallons of water. Also assume that northern California and central California have available 120 billion gallons of water, whereas southern California has 40 billion gallons of water available. The cost of shipping 1 billion gallons of water between the three regions is as follows: You will not be able to meet all demand for water, so assume that each billion gallons of unmet demand incurs the following shortage costs: How should California's water be distributed to minimize the sum of shipping and shortage costs? How should California’s water be distributed to minimize the sum of shipping and shortage costs and what is the total cost? MUST ONLY USE SOLVER. EXPLAIN EACH STEP and attached pictures of the excel file please!