Share
Explore BrainMass

# Time series analysis& Hypothesis testing

Homework Assignment

Question #1: The Prescott Electric Windings Company, which produces medium-scale electric motors for the fishing industry, is concerned about their number of orders over the past several years. Marty Sturgeon, general manager said, "With this decrease in orders I'm worried that I'm going to have to lay some people off." We need to figure out if the electric windings company is in actual trouble, or is it just the imagination of Marty.

1) First complete the following Index Number Table.

Year Number of Electric Motors Produced Index
(Base year 2002) Profit per Electric Motor Sold
2001 25,450 \$135.00
2002 26,250 100 \$139.00
2003 25,750 \$145.00
2004 25,250 \$149.00
2005 24,900 \$152.00
2006 24,750 \$160.00
2007 24,500 \$167.00

2) Indicate, based on the index numbers only, if the Prescott Electric Windings Company is in trouble and may have to lay off some workers? (This is not a yes or no answer, tell me why.)

3) Now based on all the information you have, is this company in financial trouble or not, and why? If your answer is the different from the one you gave in part 2, why is it different?

Question #2 Carol Tandem, manager of Prescott's Downtown Cycle-Rama, (Just off the square next to the Electric Coop), has been wondering about how much to gouge, I mean charge, the good tourists who visit Prescott for her rent-by-the-hour bikes. She has been experimenting with the amount of advertising she does in the Prescott Daily Disappointment, and has been varying the rental price of her bikes to see what the result would be in the total number of bikes rented. Over the past 10 weeks she has found the following.

Week Bike Rentals Rental Price
(\$100s)
1 35 9.50 3.9
2 49 7.50 3.3
3 32 9.25 3.2
4 43 8.75 4.5
5 35 8.60 3.1
6 48 7.50 4.0
7 43 7.75 3.0
8 47 7.95 3.7
9 40 8.25 3.5
10 46 7.80 4.0

Now she needs your help in figuring out just how much she should optionally rent her bikes for and how much advertising she should do in the Disappointment.

Here are the rules for this regression (Oh yes, it is a regression!):

1. Dependent variable = bikes rented per week.

2. Write out the hypotheses for this regression analysis.

3. Conduct a regression analysis and show the impact of price and advertising on the number of bikes rented.

4. Reach a decision for each null hypothesis, and calculate the F-value for the regression.

5. Predict the number of bikes that will be rented if she spent \$450 in advertising (Watch this variable carefully!) with a rental price of \$8.99 per hour.

6. In your opinion, what is the optimal price for Carol to rent her bikes for per hour and optimally how much should she spend on advertising?

NOTE: For this exercise the number of hours the bikes are rented is not relevant.

Question #3 Marty Sturgeon over at the Prescott Electric Windings Company wasn't satisfied with your answer in question 31, so he has asked for more help. This time he is interested in something called moving averages. Here is the data about the number of electric motors produced over a number of years, by quarters. He wants you, yes you, (he asked for you by name) to help complete this moving average table, to graph the data, and then recommend to him which moving average set of numbers he should use to make business decisions. (Remember this is a 3 part question, complete the table, graph the results and give Marty Sturgeon some advice as to which one to use, and why he should use this set of numbers rather than the others.)

Quarter Motors Sold 3 MA 4 MA 5 MA
1 6360
2 6240
3 6110
4 6505
5 6300
6 6320
7 6470
8 6630
9 6200
10 6350
11 6390
12 6500

Question #4 Boy are you lucky. Marty loved your report and thinks that you are the greatest statistician ever alive (Little does he know.). Anyway, he now wants you to take the same raw data found in question 33 and complete the following table using single exponential smoothing, then graph the results, and tell him if there are issues he needs to understand about seasonal purchases of this motors versus forecasted number of motors. Again this is a three part answer - complete the table, graph the results (actual versus forecasted amounts), and explain the results to Marty. Oh, by the way, use .2 as the dampening factor.

Quarter Motors Sold Forecast from the prior period Forecast for the next period
1 6360
2 6240
3 6110
4 6505
5 6300
6 6320
7 6470
8 6630
9 6200
10 6350
11 6390
12 6500

Question #5 Marty's cousin, Harry Airedale, is the manager of the local pet food producer in Prescott, Where every dog is a king/queen. Old Harry learned early that it doesn't pay to cater to one sex of dog over the other. Anyway, Marty told his cousin just how impressed he was at the great job you were doing for him, and he thought you would volunteer to help him cost out his new, Meaty Chunky Sweet Smelling All-Pro, Old Roy's Kibbles Dog Food (Did I miss any brand?)

He has gotten, from a secret source, the cost per serving of the top ranked dog foods, plus the number of servings in each can or box, the amount of protein, and the amount of fat in each can/box. He has willingly offered this secret information to you so that you can help him. This file is named, Dog Food.

Okay, so what are you to do with this information? Well, you are to do a multiple regression and to provide to Harry the following:

• A multiple regression that helps to predict future sales of his dog food.
• An F and T-tests to tell Harry if any of the individual variables are significant.
• What percentage of the total variation did this regression predict, and how do you know?
• Does multicollinearity exist for any of the independent variables, and if so, which one(s)?
• And figure out any other statistical tests that you would run to make both Harry and Marty go away.

Plus you have to give Harry the potential selling price, based on this regression, for the following different ingredients and packing sizes.

Ingredient/Product Name Healthy Choice Lean and Happy Pro Dog Choice
Cups/Servings 3 2.5 3.5
Protein 55 65 45
Fat 28 33 22

Briefly tell Harry the impact of more versus less cups/servings, and more versus less protein and fat on overall cost. Also remember, just for people, fat is bad for dogs, protein is good!

#### Solution Preview

Please see the attached file(s).

Homework Assignment

Question #1: The Prescott Electric Windings Company, which produces medium-scale electric motors for the fishing industry, is concerned about their number of orders over the past several years. Marty Sturgeon, general manager said, "With this decrease in orders I'm worried that I'm going to have to lay some people off." We need to figure out if the electric windings company is in actual trouble, or is it just the imagination of Marty.

1) First complete the following Index Number Table.

Year Number of Electric Motors Produced Index
(Base year 2002) Profit per Electric Motor Sold
2001 25,450 \$135.00
2002 26,250 100.00 \$139.00
2003 25,750 98.10 \$145.00
2004 25,250 96.19 \$149.00
2005 24,900 94.86 \$152.00
2006 24,750 94.29 \$160.00
2007 24,500 93.33 \$167.00

2) Indicate, based on the index numbers only, if the Prescott Electric Windings Company is in trouble and may have to lay off some workers? (This is not a yes or no answer, tell me why.)

There is a steady decline in the index number for Number of Electric Motors Produced. So the company may lay off some workers.

3) Now based on all the information you have, is this company in financial trouble or not, and why? If your answer is the different from the one you gave in part 2, why is it different?

The profit per Electric Motor Sold is steadily increasing. So we conclude that company is not in financial trouble . Thus number of Number of Electric Motors Produced is decreasing but the profit is increasing.

Question #2 Carol Tandem, manager of Prescott's Downtown Cycle-Rama, (Just off the square next to the Electric Coop), has been wondering about how much to gouge, I mean charge, the good tourists who visit Prescott for her rent-by-the-hour bikes. She has been experimenting with the amount of advertising she does in the Prescott Daily Disappointment, and has been varying the rental price of her bikes to see what the result would be in the total number of bikes rented. Over the past 10 weeks she has found the following.

Week Bike Rentals Rental Price
(\$100s)
1 35 9.50 3.9
2 49 7.50 3.3
3 32 9.25 3.2
4 43 8.75 4.5
5 35 8.60 3.1
6 48 7.50 4.0
7 43 7.75 3.0
8 47 7.95 3.7
9 40 8.25 3.5
10 46 7.80 4.0

Now she needs your help in figuring out just how much she should optionally rent her bikes for and how much advertising she should do in the Disappointment.

Here are the rules for this regression (Oh yes, it is a regression!):

1. Dependent variable = bikes rented per week.

2. Write out the hypotheses for this regression analysis.

3. Conduct a regression analysis and show the impact of price and advertising on the number of bikes rented.

4. Reach a decision for each null hypothesis, and calculate the F-value for the regression.

5. Predict the number of bikes that will be rented if she spent \$450 in advertising (Watch this variable carefully!) with a rental price of \$8.99 per hour.

6. In your opinion, ...

#### Solution Summary

Step by step method for testing the hypothesis under 5 step approach is discussed here. Excel template for each problem is also included. This template can be used to obtain the answers of similar problems. Time series analysis, Seasonal index, Trend, forecasting, exponential smoothing are also discussed here.

\$2.19