Hello. I am having problems getting the correct optimal solution using excel. Can you help me please.

Problem:

Using aggressive mail promotion with low introductory interest rates, Yeager National Bank (YNB) built a large base of credit card customers throughout the continental United States. Currently, all customers send their regular payments to the bank's corporate office located in Charlotte, North Carolina. Daily collections from customers making their regular payments are substantial, with an average of approximately $600,000. YNB estimates that it makes about 15 percent on its funds and would like to ensure that customer payments are credited to the bank's account as soon as possible. For instance, if it takes five days for a customer's payment to be sent through the mail, processed, and credited to the bank's account, YNB has potentially lost five days' worth of interest income. Although the time needed for this collection process cannot be completely eliminated, reducing it can be beneficial given the large amounts of money involved.

Instead of having all its credit card customers send their payments to Charlotte, YNB is considering having customers send their payments to one or more regional collection centers, referred to in the banking industry as lockboxes. Four lockbox locations have been proposed: Phoenix, Salt Lake City, Atlanta, and Boston. To determine which lockboxes to open and where lockbox customers should send their payments, YNB divided its customer base into five geographical regions: Northwest, Southwest, Central, Northeast, and Southeast. Every customer in the same region will be instructed to send his or her payment to the same lockbox. The following table shows the average number of days it takes before a customer's payment is credited to the bank's account when the payment is sent from each of the regions to each of the potential lockboxes:

Customer Location of Lockbox Daily Collection

Zone Phoenix Salt Lake City Atlanta Boston ($1000s)

Northwest 4 2 4 4 80

Southwest 2 3 4 6 90

Central 5 3 3 4 150

Northeast 5 4 3 2 180

Southeast 4 6 2 3 100

Dave Wolff, the vice president for cash management, asked your recommendations for the number of lockboxes and the best lockbox locations. Mr.Wolff is primarily concerned with minimizing lost interest income, but he wants you to also consider the effect of an annual fee charged for maintaining a lockbox at any location. Although the amount of the fee is unknown at this time, we can assume that the fees will be in the range of $20,000 to $30,000 per location. Once good potential locations have been selected, Mr. Wolff will inquire as to the annual fees.

Linear Programming Model:

Show the definition for variables as follows: P = 1 if a lockbox is in P; 0 otherwise SLC =1 if a lockbox is in SLC; 0 otherwise A = 1 if a lockbox is in 4; 0 otherwise B = 1 if a lockbox is in B; 0 otherwise PNW = 1 if the Northwest region is assigned to P; 0 otherwise PSW = 1 if the Southwest region is assigned to P; 0 otherwise PC = 1 if the Central region is assigned to P; 0 otherwise PNE = 1 if the Northeast region is assigned to P; 0 otherwise PSE = 1 if the Southeast region is assigned to P; 0 otherwise SLCNW=1 if the Northwest region is assigned to SLC; 0 otherwise SLCSW = 1 if the Southwest region is assigned to SLC; 0 otherwise SLCC =1 if the Central region is assigned to SIC; 0 otherwise SLONE =1 if the Northeast region is assigned to SLC; 0 otherwise SLCSE = 1 if the Southeast region is assigned to SLC; 0 otherwise ANW =1 if the Northwest region is assigned to A; 0 otherwise ASW=1 if the Southwest region is assigned to A; 0 otherwise AC =1 if the Central region is assigned to A; 0 otherwise ANE = 1 if the Northeast region is assigned to 4; 0 otherwise ASE = 1 if the Southeast region is assigned to A; 0 otherwise BNW =1 if the Northwest region is assigned to B; 0 otherwise BSW = 1 if the Southwest region is assigned to B; 0 otherwise BC =1 if the Central region is assigned to B; 0 otherwise BNE = 1 if the Northeast region is assigned to B; 0 otherwise BSE =1 if the Southeast region is assigned to B; 0 otherwiseShow the objective of the function as follows: 48PNW + 27PSW +112.5PC+135PNE +60PSE + 24SLCNW +40.5SLCSW +67.5SLCC +108SLCNE +90SLCSE +48 ANW Minimize +54ASW + 67.5AC+81ANE+30 ASE +48BNW +81BSW +90BC+ 54BNE + 45BSE Show the constraints for the linear programming as follows: PNW + SLCNW + ANW + BNW =1 PSW + SLCSW + ASW + BSW =1 PC+ SLCC+ AC + BC=1 PNE + SLONE + ANE + BNE =1 PSE + SLCSE + ASE + BSE =1 PNW - P