This Assignment requires to use XLMiner(Analytical solver) to do Optimization. The assignment is 2 part. First part is to use XLMiner in Microsoft Excel to get the data. Then, write a memo based on those data.
Here below are some of the questions that needs to be answers in the report.
1. Find the profit for the current production plan (400 units of Model S and 1400 units of Model LX).
2. Use Solver to find the optimal production plan, and dont worry at first about who is right and who is wrong and why.
The decision variables are set up for you (B35:C35). Use these cells as your changing cells in Solver. Dont move them.
In the next few rows, calculate the profit, based on the values in B42:C42. Put the formula for the total profit in F45. Its fine to use other cells for intermediate calculations.
The only constraints you need are to not to exceed the capacity (hours) of each department. Enter those below row 48.
Use Solver (from Excel, no Analytical Solver this unit). Use Simplex LP as your solving method.
3. Produce both an answer report and a sensitivity report for this optimal solution. (Tech check: once you think youve gotten this far, send me your spreadsheet to check. Make sure Ill be able to find the optimal plan and final profit.)
4. Some hints to figure out which one of the managers is right (if either of them are).
Keep an eye on what happens to the ABC overhead allocation when you change your production plan. And on what happens to your department utilization.
Your discussion is likely to be strengthened if you look at some intermediate plans, that fall somewhere between what theyre looking at now and what the optimal plan suggests. Plans to investigate:
Make no Model S units (add a constraint that B35 = 0, and rerun your model).
Make no Model LX units (Ill leave you to figure out how by yourself)
Make an equal number of Model S and Model LX units
Look at the sensitivity report for your optimal solution, and include a subsection discussing these results in a form useful to the managers.