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.
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
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:
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?