Question

In: Operations Management

INFO 564 Homework Assignment 5 (100 pts) This work must be done completely in EXCEL. Answer...

INFO 564 Homework Assignment 5 (100 pts)

This work must be done completely in EXCEL. Answer each question on a separate tab. Label each tab appropriately. You can copy and paste the data given into an Excel worksheet.

South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New York. The following data show quarterly sales revenues (in $’000s) for the past 5 years.

Quarter

Year 1

Year 2

Year 3

Year 4

Year 5

1

20

37

75

92

176

2

100

136

155

202

282

3

175

245

326

384

445

4

13

26

48

82

181

Question 1 (5 pts)

Plot this data with quarters from years 1-5 on the horizontal axis. What components do you see in this time series?

Question 2 (20 pts)

Ignore any trend or seasonality in the data.

  1. Suppose the company uses moving averages to make forecasts. Make forecasts all the way through Q4 Year 5. Assume the company uses (i) 3-quarterly moving averages and (ii) 4-quarterly moving averages.
  2. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – 3 quarterly moving average or 4 quarterly moving average?
  3. On a line chart plot the time series along with the forecasts from the method you select in (b).

Question 3 (20 pts)

Ignore any trend or seasonality in the data.

  1. Suppose the company uses weighted moving averages to make forecasts. Make forecasts all the way through Q4 Year 5. Assume the company uses (i) 3-quarterly moving averages with weights 0.6, 0.3, and 0.1 and (ii) 4-quarterly moving averages with weights 0.4, 0.3, 0.2, and 0.1. In both cases the most weight is given to the most recent quarter and the least to the oldest quarter in the moving average.
  2. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – 3 quarterly weighted moving average or 4 quarterly weighted moving average?
  3. On a line chart plot the time series along with the forecasts from the method you select in (b).

Question 4 (20 pts)

Again ignore any trend or seasonality in the data.

  1. Suppose the company uses exponential smoothing to make forecasts. What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming (i) alpha = 0.3 and (ii) alpha = 0.7? In both cases assume that the forecast for Q1 Year 1 was 25 units.
  2. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent Deviation. Which is more accurate – alpha of 0.3 or alpha of 0.7?
  3. On a line chart plot the time series along with the forecasts from the method you select in (b)

Question 5 (20 pts)

Now make adjustments for trend and seasonality.

  1. Quantify the trend in the time series. What does the trend equation tell you?
  2. Quantify the seasonality in the time series by calculating seasonality indexes. What do these indexes tell you?
  3. Using the trend and the seasonality information from (a) and (b) make forecasts from Q1 Year 1 through Q4 Year 5.
  4. Calculate the Mean Absolute Percent Deviation for the forecasts in (c).
  5. On a line chart plot the time series along with the forecasts from (c).

Question 6 (15 pts)

Using the most accurate method of all of the above,

  1. Make forecasts for the four quarters of Year 6.
  2. Plot these forecasts on the same line chart as the time series.
  3. Summarize in a few lines your findings from your answers to Q1 through Q6b. I really need answer to question 4 and 5 please. Thank you.

Solutions

Expert Solution

1)

Below is the screenshot of the chart -

It shows seasonal trend in data.

2)

a & b)

Below is the screenshot of the formula applied to get the result -

Below is the desired result -

c)

From above its is clear that 4 quarter moving average is better as it has the lower MAPD.

d)

Below is the chart-

3)

a&b)

Below is the screenshot of the formula applied to get the result-

Below is the screenshot of the result -

c)

4 Quarter weighted moving average is better as it has the lower MAPD.

d)

Below is the chart -

4)

a&b)

Below is the screenshot of the formula applied to get the result-

Below is the screenshot of desired result -

Alpha = 0.3 has the lower MAPD hence alpha = 0.3 will be the better.

c)

Below is the chart -

For rest of the questions, kindly initiate new thread


Related Solutions

Signature Assignment - Capital Budgeting Course Project Work must be done in Excel. Please show formulas...
Signature Assignment - Capital Budgeting Course Project Work must be done in Excel. Please show formulas if possible so I can get a true understanding of the deliverables. You have recently assumed the role of CFO at your company. The company's CEO is looking to expand its operations by investing in new property, plant, and equipment. You are asked to do some capital budgeting analysis that will determine whether the company should invest in these new plant assets. Signature Assignment...
Use Excel to complete the following question. You must submit/attach all work done in Excel in...
Use Excel to complete the following question. You must submit/attach all work done in Excel in order to receive full credit. 5. A health advocacy group conducted a study to determine if the nicotine content of a particular brand of cigarettes was equal to the advertised amount. The cigarette brand advertised that the average nicotine content per cigarette was 1.4 milligrams. The advocacy group randomly sampled 24 cigarettes. The nicotine level for each of the sampled cigarettes is given below....
Completely work the problems in Excel, conclusions and answers may be typed in Excel or Word....
Completely work the problems in Excel, conclusions and answers may be typed in Excel or Word. Then submit the assignment via Canvas to upload the file, so I can grade an electronic version of the homework. #1        Problem 25 [page 583-584]. The price drivers pay for gasoline often varies a great deal across regions throughout the United States.  The following data show the price per gallon for regular gasoline for a random sample of gasoline service stations for three major brands of...
Homework Assignment III (Due on May3, 2018) • Answer All Questions • Show all your work...
Homework Assignment III (Due on May3, 2018) • Answer All Questions • Show all your work (use of formula, etc.) in solving the problems. You still need to show your work even if you use the financial calculator to get the answers. (Please show all work and do not use excel) 1. Drill Mart Inc. is considering a new product launch. The project will cost $1,200,000 have a eight-year life, and have no salvage value; depreciation is straight-line to zero....
HOMEWORK 1 This assignment is designed to illustrate how a software package such as Microsoft Excel...
HOMEWORK 1 This assignment is designed to illustrate how a software package such as Microsoft Excel supplemented by an add-in such as PHStat can enable one to calculate minimum sample sizes necessary in order to construct confidence intervals for both population means and proportions and to construct these types of confidence intervals. You should use PHStat in order to accomplish all parts of this assignment. You should not only find the required information, but you should explain the meanings of...
HOMEWORK 1 This assignment is designed to illustrate how a software package such as Microsoft Excel...
HOMEWORK 1 This assignment is designed to illustrate how a software package such as Microsoft Excel supplemented by an add-in such as PHStat can enable one to calculate minimum sample sizes necessary in order to construct confidence intervals for both population means and proportions and to construct these types of confidence intervals. You should use PHStat in order to accomplish all parts of this assignment. You should not only find the required information, but you should explain the meanings of...
Answer any 4 of the 5 questions. Work out your solutions neatly and completely just as...
Answer any 4 of the 5 questions. Work out your solutions neatly and completely just as you would in a class test. Draw pictures and use English where appropriate. SHOW ALL YOUR WORK to receive full credit. Draw a box around each of your final answers to identify them to me. Make sure to give proper units for all your answers, and to round all your answers (except question 5) to 3 significant figures. 3. A 1.37 kg aluminum pot...
Read the assignment and answer the questions. You must also upload your Excel file, Word document...
Read the assignment and answer the questions. You must also upload your Excel file, Word document or pdf showing your work. Bodacious Building Co. is considering four different acquisition methods for obtaining pickup trucks. If the contractor’s MARR is 6%, which alternative do you recommend? The alternatives are: Immediate cash purchase of the trucks for $22,500 each, and after five years sell each truck for an estimated $4,900. Lease the trucks for five years for $4,000 per year paid at...
Calculations must be done in Excel – You must create your own spreadsheet do not copy...
Calculations must be done in Excel – You must create your own spreadsheet do not copy and paste someone else’s. Polycorp Limited Steel Division is considering a proposal to purchase a new machine to manufacture a new product for a potential three year contract. The new machine will cost $1.9 million. The machine has an estimated life of three years for accounting and taxation purposes. Installation will cost a further $120,000. The contract will not continue beyond three years and...
Objective: Work with basic input commands. Work with decision making commands. Assignment This will be done...
Objective: Work with basic input commands. Work with decision making commands. Assignment This will be done using Python. Part 1 Create a program that asks the user to input a number between 20 and 99. The program must then print out the number in English words. If the user does not enter a value between 20 and 99 display an error message stating that the input is not within a valid range. No credit will be given for programs that...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT