Case Study: Multi-period Production Planning at Zeta, p.362
Attached is the case study. This needs to be completed in Excel and any diagrams need to be able to edited, they cannot be cut and pasted into the excel spreadsheet. ALSO please show your work in excel, utilizing solver. I need to be able to see how you came up with the answer. If completed correctly I will tip extremely well
1. Discuss the process of maximizing profits by determining how much of three products to produce each month given different demands, costs, and constraints.
*note there is one typo in the case - the first line of the second paragraph should read "The company can produce only up to 75,000..." not 7,500.
2. Create a linear programming that maximizes expected profits. Use Solver to determine how much of each product to make each month. Remember, there is no reason to make more than what is demanded and you can only make 75,000 total products each month. Calculate the total profit for the 6 months by subtracting the costs for each product by the sales. Assume you can sell everything you can make as long as it's less than the demand.
3. At a cost of 1 million dollars you can increase your maximum production from 75,000 a month to 80,000 a month. Is this investment worth it? Compare the maximum profit you found above with one in which the constraint of total production increases to 80,000 a month.
|
Month
|
LG
|
MG
|
HG
|
|
1
|
50,000
|
25,000
|
15,000
|
|
2
|
45,000
|
20,000
|
18,000
|
|
3
|
35,000
|
35,000
|
20,000
|
|
4
|
40,000
|
30,000
|
22,000
|
|
5
|
46,000
|
28,000
|
21,000
|
|
6
|
60,000
|
32,000
|
|