Business Analytics

Sheet1

Parameters										Model											Summary			Cost for Different Sulfur Reduction Levels?
	Name	Value	Unit							1	Objective Min Total Cost = Cost to Burn + Purchase Cost + Delivery Cost										What if Analysis			Reduction Level	Cost
	Coal plants																							50%
	Cost increase in %										Cost to Burn													60%
	Adam president BluePowerEnergy										Purchase Cost													70%
	Reduce Sulfur 50%-90%										Delivery Cost East James													80%
	Louis VP calculate cost due to the reduce																							90%
	John VP Operations									2	Decisions: How much coal to purchase from each supplier to each facility?
	Stations											Brown	Ajax	BlackHills	Farley	West River	Rockford	Dakota	Sandy Creek
	East James										East James purchase
	Henderson										Henderson purchase
										3	Constraints	LHS (calculate)	vs	RHS (given)
	Scrubbers for East James	$ 400,000	per year								1. Max Sulfur Emission
	Remove Sulfur East James	5,440,000	lbs/year								2. MBTU needed at East James
	Remove Sulfur East James	85%	of current level								3. MBTU needed at Henderson
											4. Tons available
	Scrubbers for Henderson	$ 100,000	per year								Brown
	Remove Sulfur Henderson	2,652,000	lbs/year								Ajax
	Remove Sulfur Henderson	85%	of current level								BlackHills
											Farley
	Legislator Sulfur Reduction Request	90%	of current level								West River
											Rockford
	Buy Low Sulfur Coal (extra cost)	$ 9	per ton								Dakota
	Coal Amount Purchased	92,000	tons								Sandy Creek
	Extra Total Cost (buying low sulfur coal)	$ 1,200,000	per year																					Purchasing Decisions at Different Sulfur Reduction Levels
											cost increase percent
	Sulfur rate (current)	5%	of coal																					Reduction Level		Brown	Ajax	BlackHills	Farley	West River	Rockford	Dakota	Sandy Creek
	Sulfure rate (low sulfur coal)	0.5%	of coal																					50%	East James purchase
																									Henderson purchase
	Current Spending on Coal	$ 3,000,000	per year																					60%	East James purchase
																									Henderson purchase
	MBTU needed at East James	1,280,000	MBTU/year																					70%	East James purchase
	MBTU needed at Henderson	696,000	MBTU/year																						Henderson purchase
																								80%	East James purchase
	Current Sulfur Emissions	9,520,000	lbs																						Henderson purchase
																								90%	East James purchase
																									Henderson purchase
		Current Coal Suppliers				Low Sulfur Coal Suppliers
		Brown	Ajax	BlackHills	Farley	West River	Rockford	Dakota	Sandy Creek
	heat content (mbtu/ton)	21	23	22	21	24	28	27	26
	sulfur content (%)	3.00%	8.00%	4.00%	7.00%	0.40%	0.70%	0.08%	0.01%
	cost to burn $/ton	$ 3.00	$ 3.45	$ 3.25	$ 3.80	$ 1.80	$ 1.20	$ 0.85	$ 1.10
	purchase cost per ton	$ 32.00	$ 28.00	$ 30.00	$ 27.00	$ 36.00	$ 37.00	$ 38.00	$ 42.00
	delivery cost to East James per ton	2.00	3.00	2.00	2.00	5.00	6.00	5.00	5.00
	delivery cost to Henderson per ton	3.00	1.00	2.00	2.50	7.00	8.00	7.00	7.00
	tons available	35,000	12,000	20,000	30,000	20,000	10,000	20,000	50,000
		Brown	Ajax	BlackHills	Farley	West River	Rockford	Dakota	Sandy Creek
	current East James purchase	10,000	- 0	20,000	30,000	- 0	- 0	- 0	- 0
	current Henderson purchase	20,000	12,000	- 0	- 0	- 0	- 0	- 0	- 0

Leave Comments about your findings here:

0.5 0.6 0.7 0.8 0.9