Please answer question A and B
with Excel and screen shoots to include formulas and solver input and output details
Der Pref. Rating 6.68 17.81 13.31 hat deviations from the optimal cost value are weighted twice as heavily Con the optimal preference value. Find the solution that minimizes the weighted percentage deviations. What cost and preference rating is asso- ciated d. Suppose that di as those from th maximum weigh ciated wita What other factor ted with this solution? factors or constraints might you want to include in this analysis if you d to eat the resulting diet? CASE 7.3 24.5 15.9 14.88 822 9.43 4.4 0.28 5.52 4.9 9.03 25.67 17.34 22.72 17.1 44.58 39.24 57.53 8.18 7.83 Sales Te 35 108 6.2 26 Caro-Life is a financial insurance to reside planning to expand wants to open a seto residents of the stat an adjacent county, 7 will be regarded as the sa ered to be adjacent to its this book) shows a adjacencies throu (in square miles) for NO. les Territory Planning at Caro-Life financial services firm that specializes in selling life, auto, and home residential consumers in the state of North Carolina. The company is expand and offer its services in South Carolina as well. The company n a set of 10 offices throughout the state in such a way to ensure that all the state can access at least 1 office in either their county of residence or county. The set of counties adjacent to the county containing each office Orded as the sales territory for that office. (Note that a county is consid- din cent to itself.) Figure 7.21 (and the file Caro-Life.xlsm that accompanies shows a portion of an Excel spreadsheet with a matrix indicating county he throughout the state, and the estimated population and geographic size miles) for each potential sales territory. (Values of 1 in the matrix indicate Counties that are adjacent to one another.) es of insurance products in a given area tend to be highly correlated with the her of people living in the area. As a result, agents assigned to the various offices nt their sales territories to contain as many people as possible (to maximize sales potential). On the other hand, territories containing large amounts of people may also be comprised of a large geographic area that may require lots of travel on the part of the num 809400 4216.38 812600 6053.86 635400 3648.24 529300 2400.47 208700 2857.77 335800 301291 369950 52723 352508 96542 383 38 556508 5 Twitory Planning Data agents. So the goal of having a territory with lots of people is sometimes in conflict with having a territory that is compact in size. It is important for Caro-Life to design its sales territories in as equitable a manner as possible (i.e., where the territories are similar in terms of geographic size and sales potential). a. Assume Caro-Life wants to maximize the average sales potential of its 10 offices. Where should it locate offices and what is the population and geographic area associated with each office? b. Assume Caro-Life wants to minimize the average geographic area covered by each of its 10 offices. Where should it locate offices and what is the population geographic area associated with each office? c. Determine the solution that minimizes the maximum percentage deviatic the optimal objective function values identified in parts a and b. According solution, where should Sam Lif 1 percentage deviation from d b. According to this Der Pref. Rating 6.68 17.81 13.31 hat deviations from the optimal cost value are weighted twice as heavily Con the optimal preference value. Find the solution that minimizes the weighted percentage deviations. What cost and preference rating is asso- ciated d. Suppose that di as those from th maximum weigh ciated wita What other factor ted with this solution? factors or constraints might you want to include in this analysis if you d to eat the resulting diet? CASE 7.3 24.5 15.9 14.88 822 9.43 4.4 0.28 5.52 4.9 9.03 25.67 17.34 22.72 17.1 44.58 39.24 57.53 8.18 7.83 Sales Te 35 108 6.2 26 Caro-Life is a financial insurance to reside planning to expand wants to open a seto residents of the stat an adjacent county, 7 will be regarded as the sa ered to be adjacent to its this book) shows a adjacencies throu (in square miles) for NO. les Territory Planning at Caro-Life financial services firm that specializes in selling life, auto, and home residential consumers in the state of North Carolina. The company is expand and offer its services in South Carolina as well. The company n a set of 10 offices throughout the state in such a way to ensure that all the state can access at least 1 office in either their county of residence or county. The set of counties adjacent to the county containing each office Orded as the sales territory for that office. (Note that a county is consid- din cent to itself.) Figure 7.21 (and the file Caro-Life.xlsm that accompanies shows a portion of an Excel spreadsheet with a matrix indicating county he throughout the state, and the estimated population and geographic size miles) for each potential sales territory. (Values of 1 in the matrix indicate Counties that are adjacent to one another.) es of insurance products in a given area tend to be highly correlated with the her of people living in the area. As a result, agents assigned to the various offices nt their sales territories to contain as many people as possible (to maximize sales potential). On the other hand, territories containing large amounts of people may also be comprised of a large geographic area that may require lots of travel on the part of the num 809400 4216.38 812600 6053.86 635400 3648.24 529300 2400.47 208700 2857.77 335800 301291 369950 52723 352508 96542 383 38 556508 5 Twitory Planning Data agents. So the goal of having a territory with lots of people is sometimes in conflict with having a territory that is compact in size. It is important for Caro-Life to design its sales territories in as equitable a manner as possible (i.e., where the territories are similar in terms of geographic size and sales potential). a. Assume Caro-Life wants to maximize the average sales potential of its 10 offices. Where should it locate offices and what is the population and geographic area associated with each office? b. Assume Caro-Life wants to minimize the average geographic area covered by each of its 10 offices. Where should it locate offices and what is the population geographic area associated with each office? c. Determine the solution that minimizes the maximum percentage deviatic the optimal objective function values identified in parts a and b. According solution, where should Sam Lif 1 percentage deviation from d b. According to this
