17 Aug Develop an optimization model that helps V-Mobile allocate its international calls in a cost-efficient manner.

Use the following excel to complete work( need excel solver)

Case 6.2. Selecting Telecommunication Carriers to Obtain Volume Discounts

During 2001, many European markets for mobile phones reached saturation. Because of this, mobile phone operators started to shift their focus from growth and market share to cutting costs. One way to do this is to reduce spending on international calls. These calls are routed through network operating companies called carriers. The carriers charge per call-minute for each destination, and they often use a discount on total business volume to price their services. A mobile phone operator must decide how to allocate destinations to carriers.

V-Mobile, a mobile phone operator in Denmark, must make such a decision for a T-month planning horizon when it has C carriers to choose from, D destinations for its customers’ calls, and there are I price intervals for a typical carrier. (These intervals define a carrier’s discount structure.) The inputs include the following:

• The price per call-minute for destination d from carrier c in price interval i in month t
• The (forecasted) number of call-minutes for destination d in month t
• The lower and upper limits for carrier c in price interval i
• The lower and upper limits on capacity (number of call-minutes) for carrier c in month t
• The penalty per call-minute (to discourage poor-quality options) for carrier c to destination d in month t

V-Mobile wants to find a least-cost way of routing its call-minutes through the various carriers. Of course, it hopes to take advantage of price discounts offered by the carriers.

The file C06_02.xlsx provides inputs for one version of V-Mobile’s problem. This version has T=2, C=3, D=5, and I=3. The decision variables should include the following:

• The number of call-minutes routed through carrier c to destination d in price interval i in month t
• A binary variable for each carrier c and price interval i combination that equals 1 if the total call-minutes for this carrier (over all destinations and months) falls in price interval i, and equals 0 otherwise.

Develop an optimization model that helps V-Mobile allocate its international calls in a cost-efficient manner. Then write a brief memo stating

• (1) how V-Mobile should implement your results for this particular version of the problem and
• (2) how the model would need to be modified for other potential problem parameters.