The in-class exercise will assess your ability to use Excel for optimisation. It is important within trade contexts that you can evaluate several options and make optimal decisions based on the available data. This assessment will help you demonstrate skills toward meeting Learning Outcome 3. As part of this assessment, you will demonstrate competence in obtaining relevant results through the appropriate use of models, quantitative techniques and the Excel Solver for analysis and decision making.

Use MS Excel and the Excel Solver to find a suitable solution to the following problem:

A furniture manufacturing company in Footscray produces two types of coffee tables, Bronte (B) and Noguchi (N) for export.

It takes 2 hours to create the components of one unit of Bronte, 1 hour to assemble and 2 hours to polish.

It takes 4 hours to create the components of one unit of Noguchi, 2.5 hours to assemble and 1.5 hours to polish.

Per month, 7000 hours are available for producing the components, 4000 hours for assembling the components and 5500 hours for polishing the tables.

The profit per unit of Bronte is $90 and $119 per unit of Noguchi.

How many of each type of tables should produce to maximise the total monthly profit?

Tasks

1. Formulate the problem of deciding how much of each product to make in the current month as a linear programming program (objective function and other relevant equations).
2. Formulate this problem in a format that is conducive to the use of the Excel Solver.
3. Use Excel Solver to produce a linear programming report of the solution that should maximise the profit for the furniture company's profit.
4. Interpret this report by clearly stating below the report, the maximum profit the company would make, the optimum number of each table that would be made per month, and the resources (input hours) remaining, if any, unused at the end of the month.
5. Explain briefly in the context of this problem, what you understand by the terms SLAK and binding constraints.
6. Submit to the DropboxinExcel format.