1. Project Specifications

You have been hired as a consultant by the Woodworks Company, which manufactures and sells custom-made wood furniture. Each month, the company must plan its production for the following month. The company wants to maximise its profit given limited resources, namely oak and cherry wood, senior and junior labour, and machine time. The product line includes twelve different types of tables, chairs, and bookshelves. The file Woodworks.xlsx contains all the data for this production problem, including the unit price and cost for each produce, resources required to produce them, and minimum and maximum production amounts for each product. A model representing this problem has already been developed. You are required to convert this model into a decision support system. More specifically, you should produce an application that enables a user to do the following.

1. Perform an optimisation run by pressing a button on the spreadsheet. At the end of the optimisation, a message box should display the optimal profit as well as the optimal number of products to produce.
2. The current model assumes that all products produced can be sold. The values in the range MaxUnits (E5:E16) have been set to a large value (10,000) to indicate that the maximum sales for each product are unlimited and, therefore, do not affect the optimal production plan. The Woodworks production manager would like you to develop a way of analysing how the optimal profit and production plan may change if customer demand (i.e. max units) for each product is limited. In particular, she wants you to analyse six different scenarios. The demand for products in each scenario are displayed in the range DemandScenarios (B34:G45). You should generate a table somewhere in your spreadsheet summarising the results of this sensitivity analysis. Specifically, for each scenario, you should replace the values in the range MaxUnits with the values in the corresponding column of the table Maximum sales, run the solver and record the results in a sensitivity table.
3. The production manager is also concerned about the usage of some of the raw materials that may become obsolete after the current production run. To this end, she would like to analyse the possibility of maximising the usage of raw materials while maintaining a certain level of profit. You should create a user form that enables the user to select the type of problem to be solved (i.e. profit maximisation or raw material usage maximisation). Regardless of the type of problem selected, maximum sales constraints can be ignored (set to 10,000). If the user selects the first problem, then the same optimisation as in part (1) should be run. If the user selects the second objective, however, another form should appear containing a text box where the user can specify the minimum profit required along with a list box or combo box with a list of the raw materials. The user should be able to specify only one raw material to be maximised. The optimisation model should then be modified so that it maximises the usage of the selected raw material subject to the normal resource constraints plus an additional constraint requiring that total profit be greater than or equal to the profit specified by the user. If the user enters a profit value that is larger than the optimal profit computed with the original model (the model where profit is being maximised), an error message should be displayed saying that there is no production plan which can produce that amount of profit.

Product mix