Can you answer this?

  Please show work and use excel.

Integrated Case of Hope Steel, Inc.

A Brief Profile of Hope STEEL works, inc.

Hope Steel Works, Inc. – also known to the local market as ‘Hope Steel’ - is one of the largest producers of concrete-reinforcement steel bars. It was established in December of 1964 by Mr. John Long, an industry pioneer whose work in the steel industry began in 1948, the company has built up a solid reputation for superior quality, good service, and fair prices. 

Hope Steel supplies to a wide range of construction projects. Its bars are found in low-cost housing, private residences, luxury condominiums, commercial and industrial projects, high-rise buildings, schools, hospitals, bridges, highway over-passes and viaducts, dams and power stations, tunnels, airports, seaports, aqueducts, telecommunication sites, and other public infrastructure. 

Certified under ISO-9001:2000, Hope Steel regularly sample, test and analyze its products to ensure that only superior quality reaches its customers. 

Rebar Sizes  

The standard Rebar sizes that the Company manufactures and sells are as follows: 10mm, 12mm, 16mm, 20mm, 25mm, 28mm, 32mm, and 36mm. For special orders, it can also make 40mm and 50mm bars. 

Case 1 Introduction

Over the past years, the demand for 25mm rebars have fluctuated with a seasonal pattern being observed.  Since rebars are manufactured on a “made to order” basis, the VP for Production wanted an accurate forecast for the second half of this year 2015, to enable them to plan and schedule the ordering and the storage of the raw materials needed for the production. Demand for the 25 mm rebars for last year (2014) were as follows: 

Demand for the year 2014

  

x

y

 

January

3000

 

February

3500

 

March

4800

 

April

4300

 

May

2400

 

June

2700

 

July

3500

 

August

3500

 

September

1700

 

October

2100

 

November

3200

 

December

2700

 

Based on the data above, using simple linear regression with seasonality, compute the forecasts for the months of July, August, September, October, November and December of 2015.

Case 2 Introduction

In the production of rebars, carbon steel is melted down through a furnace, is molded and elongated into very hot rods, and are then pushed through the rolling mills which determines the sizes and lengths of the bars as appropriate. 

The annual demand for Carbon steel is 40,000 pounds. Its ordering cost is $ 30.00 while it’s holding cost is 20% per unit per year of the price of Carbon Steel. Carbon steel are sold at discounted prices as follows:

   

Quantity   range

Cost   (C)

 

Less   than 2500 pounds

$   0 .20 per pound

 

2500   to 5000 pounds

$   0.19 per pound

 

5000   or more pounds

$   0.18 per pound

Which quantity should be ordered and at what cost?