Background :

Businesses are often faced with complex decisions that are not easy to solve using typical rules of thumb. In this project, you will find a solution for one of these complex decisions by creating a spreadsheet assignment in Excel and using Solver to find the best solution.

Learning goals:

1. Create a linear model in Excel

2. Use Solver to find an optimized solution

3. Understand and explain what makes a model linear

4. Understand the concept of shadow price and the effect of modifying constraints

5. Use shadow prices and reduced costs to find opportunities to add value

6. Learn about nonlinear models and their associated challenges (local vs global maxima)

7. See how to convert a nonlinear model to a linear model

Assignment questions:

1. If you were making only one batch, what is the best batch you could make? What is the profit associated with this batch? You will answer this question by creating a linear optimization model in Excel. Discuss your findings in a text box?

2. Did the regulatory constraint of 4,000 kg per batch of finished product hamper your ability to make more profit? Is it worthwhile to seek regulatory approval to increase that limit? Specifically discuss the shadow price and interpret its meaning?

3. CEL has used some amount of all seven raw materials in the past. A vendor may be somewhat unhappy if CEL does not order a particular type of raw material. How much profit will Akshay Mittal lose if he must use at least one unit of a raw material in a batch given he would otherwise not use that raw material?

4. What implications does your optimal batch from question one have on monthly contribution margin (profits)?

5. Do you have any further suggestions for how to improve monthly profits?

Table 1. Raw materials optimization.
	Rate / Ton (in rupees)	Recovery	Minimum per Batch (% of RM)	Maximum per Batch (% of RM)	Maximum per Month (in tons)
Tasla	17,000	0.84	0%	50%	800
Rangeen	13,600	0.74	0%	25%	500
Sponge	17,800	0.85	10%	50%	1,000
Local Scrap	20,000	0.94	15%	80%	1,000
Imported Scrap	23,000	0.97	0%	80%	1,500
HC	2,500	0.25	0%	20%	300
Pig Iron	20,400	0.95	5%	10%	500
Price / ton	29,000
Other consumables / ton	2,000
Salary / batch	3,000
Electricity unit cost	4.30
# of working days / month	25
# of hours / day	24
Maximum kg per batch	4,000
RM = raw materials