SMART Energy, an electricity company, sells its electricity production to GOOD. Each megawatt (MW) of electricity generated

Fantastic news! We've Found the answer you've been seeking!

Question:

SMART Energy, an electricity company, sells its electricity production to GOOD. Each megawatt (MW) of electricity generated requires 100,000 tons of coal to burn. SMART 's coal supplier is GENERAL ENERGY (GE) in California. To bring in the coal, SMART has to pay for order preparation, transportation, loading and unloading, demurrage, etc. an average of $25.000.

Before being burned in the boiler to produce electricity, the coal is stored in the stockpile. To store coal before entering the boiler, SMART has to pay for security, administration, crane, insurance, stockpile maintenance an average of 20% of the value of the stored coal. The price of coal per ton FOB (seller franco) is $5000. The power output of all SMART 's boilers over the past two years (in MW) is as follows:

Month	Demand (MW)			Month	Demand (MW)
Month	2014	2015	2016 (forecast)	Month	2014	2015	2016 (forecast)
Jan	125	178		Jul	105	200
Feb	128	92		Agu	123	213
Mar	142	180		Sep	115	220
Apr	158	198		Okt	138	235
Mei	136	205		Nov	145	245
Jun	98	208		Des	165	248

Previous experience, the time required to fulfill coal orders by GE is as follows :

order to:	1	2	3	4	5	6	7	8	9	10
Lead Time (weeks)	1.7	1.2	1.5	2	1.1	1.6	1.3	1.5	2	1.3

SMART’s experience also requires him to provide coal in sufficient quantities so that production is not disrupted so that service to users is also in accordance with the Service Level Agreement (SLA) with GE as stated in the contract. However, SMART also doesn't want to be too wasteful with inventory costs. For that, he set a 95% service level.

As a company engaged in providing electricity for the consumption/living of the people, SMART must balance between a fully profit-oriented business goal, and compliance with various regulations, environmental, social, and even political issues.

As the Supply Chain and Customer Relationship Manager of SMART, you must be responsible for maintaining the service level to GE as well as operating efficiency, as well as various issues related to the energy industry.

Question: Calculate safety stock and ROP! (Notes: D 2016, DL, σD, σL, should be found first using the previous demand data). Please provide the calculation using excel.