In: Advanced Math
Winkler Furniture manufactures two different types of china cabinets A French Provincial model and a Danish Modern model. Each cabinet produced must go through three departments: Carpentry, Painting and Finishing. The table provided gives all of the production times per cabinet produced and production capacities for each operation per day along with net revenue per unit prodced.
The firm has a contract to produce a minimum of 60 cabinets per day. How many of each cabinet should the manufacturer make to maximize daily revenue.
Please show how to set up in excel and how to get the answer using solver in excel
Decision variables:
X1 = No of French Provincial cabinets produced each day
X2 = No of Danish Modern cabinets produced each day
Objective function is to maximize the Revenues
Max Z = 28 X1 + 25 X2
Constraints:
3 X1 + 2 Y2 <=360 (Carpentry hours available)
1.5 X1+X2 <= 200 (Painting hours available)
0.75X1 + 0.75X2 <= 125 ( Finishing hours available)
X1 >= 60(contract requirement on French Provincial(F.P) cabinets)
X2 >=60 (contract requirement on Danish Modern(D.M) cabinets)
X1, X2>= 0 (Non negativity constraints)
I am using excel solver to solve this problem. Below are the images for the solution
X1 = the number of French Provincial cabinets produced each day = 60
X2 = the number of Danish Modern cabinets produced each day = 90
Total revenues Z = $3930