In this assignment you will first formulate $($Part A$)$ the problem by hand and then automate$/$optimize the work $($Part B$)$ on a spreadsheet. You will then reformulate by hand $($Part C$)$ and rerun the analysis $($Part D$)$ on the spreadsheet.To understand how to complete this assignment please read pages $320-322($Chapter$/$section $9\mathrm{.}1)$ and see the use of Solveras it is applied to the transportation example.Please read the scenario below before proceeding to part A$.$Scenario: Doll Crafters is planning on setting up a distribution network.Dolls from various factories must be transported to the various retail locations.Factories are as follows $($with the number of with number of doll production capacities$)$Briargate $(400)$Austin Bluffs $(1600)$Black Forest $(200)$Cimmaron $(1300)$Knob Hills $(500)$There are three retail locations $($with demand$)$Air Force Academy $(1000)$Colorado College $(1000)$PPSC $(1500)$The respective transportation cost $($$$)$ per doll is given as follows: Air Force AcademyColorado CollegePPSCBriargate $576$Austin Bluffs$537$Black Forest$855$Cimmaron$965$Knob Hills$1766$Part A$.$ Please formulate by hand:Identify the correct variables $($including labeling$).$Set up the objective function.Set up the respective constraints including non$-$negativity. Part B$.$ Please set up the problem onto your spreadsheet. You will need to locate Solver $($add$-$on QM is not needed$).$ Use solver to come up with the optimal transportation plan and provide a cost.Part C$.$ Additional to the scenario due to a grass fire near Air Force Academy this retail outlet will be closed and not be selling any dolls. Reformulate the problem by hand. Identify the correct variables $($including labeling$).$Set up the objective function.Set up the respective constraints including non$-$negativity. Part D$.$ Please set up the revised problem in Part C onto your spreadsheet. You will need to locate Solver $($add$-$on QM is not needed$).$ Use solver to come up with the optimal transportation plan and provide a cost. Explain difference in costs & flow patterns between Part B and D$.$ Grading$/$evaluation of Parts A & C is based on the following rubric:Correct variable definitionsCorrect formulationGrading$/$evaluation of Parts B & D is based on the following rubric:Clarity, color and ease of understanding the spreadsheet.Use of spreadsheet functionality. I need to see that you have built in equations rather than typing in the answers. Also show the equations you used. Correct answer.Separation of input variables from output variables in the spreadsheet. The input data should be in one place and referred by other cells.Explanations.