Solve using the EXCEL attached and give the answer and if you use EXCEL$/$SPSS then provide the necessary information given in the EXCEL$/$SPSS output to justify the answer: A manufacturing company purchases three component parts $($A$,$B and C$)$ from three different suppliers $(1,2,$ and $3).$ the suppliers charge different unit prices for the components as provided:

Component A$,$ Supplier $1$: $$12$

Component A Supplier $2$: $$13$

Component A Supplier $3$: $$9$

Component B Supplier $1$: $$21$

Component B Supplier $2$: $$24$

Component B Supplier $3$: $$19$

Component C Supplier $1$: $$10$

Component C Supplier $2$: $$11$

Component C Supplier $3$: $$9$

Supplier $1$ Capacity: $1000$

Supplier $2$ Capacity: $1300$

Supplier $3$ Capacity: $700$

Demand Component A: $1,000$

Demand Component B: $900$

Demand Component C: $800$

Each supplier has a limited capacity in terms of the total number of components it can supply and no one supplier can meet all the company$$s needs. as long as the company can provide sufficient advance orders, each supplier can devote its available capacity to make any combination of the three components. the current available capacities for supplier $1,2,$ and $3,$ are $1000,1300,$ and $700$ units respectively. suppose that the company$$s production plan for the next period includes $1000$ units of Component A$,900$ units of Component B$,$ and $800$ units of Component C$.$ the company needs to determine the optimal quantity of each component to be purchased form the three suppliers so as to minimize its toal purchase cost.

The model paramets are in the excel sheet Book$5.$

You can use the following in quotes as a sample for the LP mentioned but do not use the values they are for a different problem and replace whatever is needed to make the LP correlate to this question$$ Linear Programming Formulation:

Unit profit for DRG$-1=2000(1*600+15*40+3*75)=575$

Unit profit for DRG$-2=3000(3*600+10*40+4*75)=500$

Unit profit for DRG$-3=3200(2*600+20*40+6*75)=750$

Decision variables:

x$1=$number of DRG$-1,$ x$2=$number of DRG$-2,$ x$3=$number of DRG$-3$

Objective:

To maximize profit function: $575$x$1+500$x$2+750$x$3$

Constraints:

Inpatient days: x$1+3$x$2+2$x$3<=120$

Nurse service: $15$x$1+10$x$2+20$x$3<=900$

Diagnostics: $3$x$1+4$x$2+6$x$3<=360$

Non$-$negativity: x$1>=0,$ x$2>=0,$ x$3>=0"$

a$)$Formulate a Linear Programming $($LP$)$ model to find the optimal mix of procedures. $($For simplification, you do not need to specify the number of units in the model as integers.$)$ Clearly define the decision variables, the objective function and the constraints of the problem in algebraic form.

b$)$

c$).$ If supplier $1$ can increase the capacity from $1000$ units to $1100$ units, how will this affect the optimal purcahse quantities and the corresponding total purchase cost?

d$)$if supplier $2$ is willing to lower its unit price of component B from $$24$ to $$18,$ how will this affect the optimal purchase quantities and the corresponding total puchase cost?