Question

Question 4 - What is the best solution? Asmall farm makes the similar animal fodies Product Are Sho whour. O material 4 minutes of mixing using a mix-machine product requires 6 hours of w a l e s of mixing and productores 4 hours of labour, Skg of raw matal i king The firm has 210 hours available during the next period for labour. 200 kg of w e aland 170 forming Product contributes $28.00 per unit to profit, product contributes 5253 por t es 5274 wit. Determine the optimal mix of products in terms of maximizing contribution to profits for the period Requirements a) Define the decision variables (1 mark) b) Show the business objective and objective function marks) c) Listalli ques (4 marks) d) Create an EXCEL working sheet for the car programming model: marks) e) Provide both Answer and Sentivity reports, marks) 1) Find the range of optimality for the profit coefficient of each variable using the ropets generated. (4 marks) Note: In order to achieve full marks for this question is essential that you explain what you are doing why you are doing it and the steps involved in providing a final solution Ensure your answer is not just a set of calculations as marks for this question are set aside for your explanation Response Explanation Total Marks Presentation 2 Question 4 16

Answer 1

a) Decision variables are the variables that help in decision making. Values of these variables changes as per conditions or equations. These are very important variables in context of linear programming.

b) Business objective is to have such a mix of products that maximize the overall profit of the firm combining profits of all 3 products.

Objective function is below:

Let number of units of product A be x

number of units of product B be y

number of units of product C be z

and objective is to maximse profit . so , equation is:

  

c) equation of all the constraints:

$5x+6y+4z\leq 210$

$10x+8y+5z\leq 200$

$4x+2y+5z\leq 170$

where $x\geq0, y \geq0, z\geq0$

d)

	B	C	D	E	F	G	H
2		x	y	z
3	Maximise	28.4	25.3	27.4		0
4						optimal solution	Availablity
5	constraints	5	6	4		0	210
6		10	8	5		0	200
7		4	2	5		0	170
8	Number of units	0	0	0	Total

Formulaes

C8,D8,E8 put 0 randomly

G3= =sumproduct(C3:E3,C8:E8)

G5= =sumproduct(C5:E5,$C$8:$E$8)

and drag it till G7

CLICK on G8 and from above tabs select SOLVER and put all the required information there.

you will get the solution.

As here excel sheet cannot show formulas , answer from solver is not visible here, try in excel to get solution