homework assignment 5

Assignment 5

This assignment contains 2 parts. You need to address both parts to earn full credit.

Part a. Find the attached Excel File: Assignment 5_Charts. There are 4 excel worksheets in the excel file. Based on the instruction in each worksheet, create the following chart and provide explanation. Note: Editing the Excel file, and submit the complete excel file as the first document for this assignment.

1. Create a histogram about the inside diameter of metal sleeves (Hint: the histogram needs to include all 100 observations). Provide your explanation about the histogram chart.

2. Create a scatter plot with a trend line to show the relationship between disposable income and life insurance coverage. Reach your conclusion about the two variables.

3. Create a Pareto Chart regarding the data about types of error in a printing company (Hint: the pareto chart need to be created based on the total cost, only unit costs is given currently). Discuss the results found based on the pareto chart: if management has monthly allocation of $18,000, which areas should they tackle?

4. Based on the cols manufacturing data, conduct the x-bar chart and r chart. How to interpret the two charts?

Part b. This part includes 3 calculation questions regarding Chapter 4 Forecasting. Show your steps to earn full credit. Note: You can either type your answer in this word file, or you can print it out, work on it, and scan the file back in. I’m fine with either option, and whatever the option you choose, it should be the second file you turn in for this assignment.

1. As you can see in the following table, demand for heart transplant surgery at Washington General Hospital has increased steadily in the past few years:

The director of medical services predicted 6 years ago that demand in year 1 would be 41 surgeries.

a) Use a 3-year moving average to forecast demand in years 4, 5, and 6.

b) Use a 3-year weighted moving average, with weights of .1, .3, and .6, using .6 for the most recent year. Forecast demand for the year 4, 5, and 6.

c) Compute the forecast for the Year 6 using exponential smoothing with a forecast for Year 1 is 40 and a = .2.

d) Use the trend-projection method to forecast demand in years 1 through 6.

e) With MAD as the criterion, which forecasting method is better?

2. George Kyparisis owns a company that manufactures sailboats. Actual demand for George’s sailboats during each of the past four seasons was as follows:

George has forecasted that annual demand for his sailboats in year 5 will equal 5,600 sailboats. Based on this data and the multiplicative seasonal model, what will the demand level be for George’s sailboats in the spring of year 5?

3. The number of auto accidents in Athens, Ohio, is related to the regional number of registered automobiles in thou- sands (X1), alcoholic beverage sales in $10,000s (X2), and rainfall in inches (X3). Furthermore, the regression formula has been calculated as:

Y=a+b1X1 +b2X2 +b3X3

Where Y = number of automobile accidents

a = 7.5

b1 = 3.5

b2 = 4.5

b3 = 2.5

Calculate the expected number of automobile accidents under conditions a, b, and c: