A very large logistical problem arises in officer mobilization for the U$.$S$.$ Marine Corps. During an international emergency, officers have to be mobilized from their current positions into billets required for the emergency. Based on rank, training and experience, each officer is qualified for some of the billets. Problems involving tens of thousands of officers and billets have to be solved in minutes to provide a rapid mobilization plan. A data set of consisting of $10$ officer groups and $4$ emergency billets is given in $$Mobilization $-$ Shell.xlsx$.$ a$.$ Determine a mobilization plan that meets, as much as possible, the demand for officers at the billets while minimizing the total miles traveled. In so doing, you should also make sure that the shortage in the number of officers does not exceed $25\%$ for any of the billets. Create a spreadsheet model for this problem and solve it using Solver. Report the $($actual$)$ total miles that officers need to travel? b$.$ Now that you have found the optimal solution for the given number of officers, you have been asked to address the shortage issue. How many more officers are needed to address this issue? And to which officer group$($s$)$ should these new officers be added to fully meet the emergency billet demands? Explain your reasoning. $14$ Distance Matrix: Initial Groups $12$ # of Officers in Group $141220\mathrm{.}0207\mathrm{.}0$ Mobilization Billets $23103\mathrm{.}0129\mathrm{.}0200\mathrm{.}0190\mathrm{.}0127\mathrm{.}0129\mathrm{.}0225\mathrm{.}080\mathrm{.}0138\mathrm{.}0236\mathrm{.}0133\mathrm{.}01021283235\mathrm{.}0121\mathrm{.}04100\mathrm{.}01015139\mathrm{.}0208\mathrm{.}0111175\mathrm{.}0252\mathrm{.}0150\mathrm{.}0258\mathrm{.}0264\mathrm{.}084\mathrm{.}0+$ LD $000125\mathrm{.}0249\mathrm{.}01131037250\mathrm{.}090\mathrm{.}0248\mathrm{.}0200\mathrm{.}0293\mathrm{.}0147\mathrm{.}096305\mathrm{.}0300\mathrm{.}0185\mathrm{.}093910330\mathrm{.}0131\mathrm{.}0125\mathrm{.}0159\mathrm{.}0135\mathrm{.}0117\%$ Shortage Allowed $25\%$ # of Officers Required for Billet $278353328169$