Data collection, analysis, forecasts

Notes:
Answers, excluding the data and computer output, should not exceed 4 pages. Therefore, answer each question briefly and precisely without omitting relevant points.

Tables containing summary of the results should be included in the main text while data, estimation output, etc. should be relegated to appendices. Each group should

submit three files
1- A typed coursework in word or pdf.
2- One excel file contain data and analysis.
3- One Eviews workfile for econometric analysis.

Objectives:
Having completed this coursework the students should be able to:
• Use excel to process and analyse the data, produce tables graphs and perform regression analysis, including diagnostic tests and hypothesis testing,
• Perform forecast and evaluate the performance of different types of forecasting techniques,
• Perform simulations, analyse the outcome and make decisions based on the outcome,
• Solve linear programming problems and use them for optimisation

Task
Management of a petrochemical company producing special type of polymer is trying to control its inventory costs. The weekly cost of holding one unit of this product

in inventory is $300 (one unit is 100kg). The marketing department reckons that weekly demand is reasonably close to a lognormal distribution with an average of 120

units and standard deviation of 40 units.
If the demand exceeds the amount of product on hand, those sales are lost – i.e. there is no backlogging of demand. The production department can produce at one of the

three levels: 100, 120 or 140 units per week. The cost of changing production from week to the next is $30,000.
Management would like to evaluate the following production policy. If the current inventory is less than l=30 units, then produce 140 units in the next week. If the

current inventory is more than u=80 units, then produce 100 units next week. Otherwise, if the current inventory is between u=80 and l=30 units, then produce 120 units

next week. The company currently has 60 units of inventory on hand and last week’s production level was 120.

1- Create a spreadsheet to simulate 52 weeks of operation at this manufacturer. Graph the inventory of the product over time. What is the total cost (inventory cost

plus production change cost) for the 52 weeks?
2- Use a simulation of 10,000 trials to estimate the average 52-week cost with values of u ranging from 50 to 100 in increments of 5. Keep l=30 for all trials.
3- Calculate the sample mean and standard deviation of the 52-week cost under each policy. Using those results, construct 90% confidence intervals for the average 52-

week cost for each value of u. Make and present a graph of the average 52-week cost versus u. What is the best level of u when l=30?
4- What would be the optimum production quantity if we follow the policy: produce 140 if inventory level less than 30, produce 100 units if inventory level is more

than 80, but keep the production level same as last week if 30<I<80.
5- After studying the historical demand figures in more detail, the manufacturer finds that there is some degree of seasonality in the demand; in the sense that demand

increases by 5% and 10% in 2nd and 3rd quarters, respectively, and declines by 10% and 5% in quarters 4 and 1, respectively. Incorporate seasonality in simulation

setting and calculate the new level of u to minimize the overall cost. (Assume the first week is the first week of the year.)
6- What other production policies might be useful to investigate?