Application assignments require solving problems from the
textbook. Most of the
problems require that you use Excel QM. Some chapters will
require you to use POM
QM for Windows or TreePlan. The answers must be submitted to
the Dropbox in a
Microsoft Word or Microsoft Excel file.
You must state your answers within a complete sentence so
that your understanding of
applying the results of the computations can be observed.
You should also include the
work for your computation; this will assist in applying
partial credit if your answers are
not correct.
Do not submit QM for Windows files as server security
policies do not allow many of
these files to be passed in the system. If you need to show
the QM work, either save as
an Excel file (a function within QM) or paste a screen
capture of the QM Entry Screen
before solving AND the QM result screen(s).
NOTE: QM files should not be sent through the online system.
If using QM for your
solution, run the file in QM and then copy and paste the
solution into a MS Excel file
(within QM there is an option tab at bottom of the screen to
save as MS Excel.) If you
cannot do this, with the QM solution file open on your
screen, select alt-print screen and
then paste the image into a MS Excel file.
If you need any assistance, please do not hesitate to
contact your instructor. He or she
will be happy to assist.
Submit Application Assignment 3 to the Dropbox no later than
Sunday 11:59 EST/EDT
of Module 3. Remember, the required formats are Microsoft
Word and Microsoft Excel.
For this module, you will
complete the following problems from the textbook:? Chapter 4, pages 146-147,
Problems 30, 31, and 32 using the Data Analysis
Add-In for Microsoft Excel
? Chapter 5, pages 188-189,
Problems 25 and 31 using either Excel’s Data
Analysis Add-In or Excel QM
Chap 3#30 pg 146 A sample of nine public
universities and nine private universities wastaken. The total cost for the
year (including room and board) and the median SAT score (maximum total is
2400) at each school were recorded. It was felt that schools with higher median
SAT scores would have a better reputation and would charge more tuition as a
result of that. The data is in the table below. Use regression to help answer
the following questions based on this sample data. Do schools with higher SAT
scores charge more in tuition and fees? Are private schools more expensive than
public schools when SAT scores are taken into consideration? Discuss how
accurate you believe these results are using information related the regression
models.
Category Total Cost Median
SAT
Public 21,700 1990
Public 15,600 1620
Public 16,900 1810
Public 15,400 1540
Public 23,100 1540
Public 21,400 1600
Public 16,500 1560
Public 23,500 1890
Public 20,200 1620
Private 30,400 1630
Private 41,500 1840
Private 36,100 1980
Private 42,100 1930
Private 27,100 2130
Private 34,800 2010
Private 32,100 1590
Private 31,800 1720
Private 32,100 1770
# 31 pg 146
In 2008, the total payroll
for the New York Yankees was $209.1 million, while the total payroll for the
Tampa By Rays was about $43.8 million about 1/5th of that of the Yankees. The
table below lists the payrolls in millions for all 14 MLB teams in the American
league and the total victories for 2008
Team Payroll millions
victories
NY Yankees 209.1 89
Detroit Tigers 138.7 74
Boston Red Sox 133.4 95
Chicago White sox 121.2 89
Cleveland Indians 79 81
Baltimore Orioles 67.2 68
Oakland Athletics 48 75
Los Angeles Angels 119.2
100
Seattle Mariners 118 61
Toronto Blue Jays 98.6 86
Minnesota Twins 62.2 88
Kansas City 58.2 75
Tampa Bay 43.8 97
Texas Rangers 68.2 79
Develop a regression model
to predict total number of victories based on payroll of the team. Based on
results discuss how accurate the model is. Use the model to predict the number
of victories for a team with a payroll of $79million
#32
In 2009, the New York
Yankees won 103 baseball games during the regular season. The table on the next
page lists the number of victories (W), the earned-run- average (ERA), and the
batting average (AVG) of each team in the American League. The ERA is one
measure of the effectiveness of the pitching staff, and a lower number is *****
The batting average is one measure of effectiveness of the hitters, and a
higher number is *****
TEAM W ERA AVG
New York Yankees 103 4.26
0.283
Los Angeles Angels 97 4.45
0.285
Boston Red Sox 95 4.35 0.27
Minnesota Twins 87 4.5
0.274
Texas Rangers 87 4.38 0.26
Detroit Tigers 86 4.29 0.26
Seaattle Mariners 85 3.87
0.258
Tampa Bay Rays 84 4.33
0.263
Chicago White Sox 79 4.14
0.258
Toronto Blue Jays 75 4.47
0.266
Oakland Athletics 75 4.26
0.262
Cleveland Indians 65 5.06
0.264
Kansas City Royals 65 4.83
0.259
Baltimore Orioles 64 5.15
0.268
A.Develop a regression
model that could be used to predict the number of victories based on the ERA.
B.Develop a regression
model that could be used to predict the number of victories based on the
batting average.
C.Which of the two models
is better for predicting the number of victories?
D.Develop a multiple
regression model that includes both ERA and batting average. How does this
compare to the previous models?
Q5.25
Sales of industrial vacuum
cleaners at R. Lowenthal Supply Co. over the past 13 months are as follows:
Sales (1,000s) MONTH Sales (1,000s) MONTH
11 January 14
August
14 February 17 September
16 March 12 October
10 April 14 November
15 May 16 December
17 June 11 January
11 July
(a) Using a moving average
with three periods, determine the demand for vacuum cleaners for next February.
(b) Using a weighted moving
average with three periods, determine the demand for vacuum cleans for
February. Use 3, 2 and 1 for the weights of the most recent, second most
recent, and third most recent periods, respectively. For example, if you were
forecasting the demand for February, November would have a weight of 1,
December would have a weight of 2, and January would have a weight of 3.
(c) Evaluate the accuracy
of each of these methods.
(d) What other factors
might R. Lowenthal consider in forecasting sales?
#31 pg 189
A major source of revenue
in Texas is a state sales tax on certain
types of goods and
services. Data are compiled and the
state
comptroller uses them to
project future revenues for the state budget.
One particular category of goods is classified
as Retail Trade. Four
years of quarterly data for
one particular area of southeast Texas
follows:
quarter year 1 year 2 year 3 year 4
------------------------------------------------------------
1 218 225 234 250
2 247 254 265 283
3 243 255 264 289
4 292 299 327 356
a)compute seasonal indices
for each quarter based on a CMA.
b)Deseasonalize the data
and develop a trend line on the deseasonalized
data.
c)Use the trend line to
forecast the sales for each quarter of year 5.
d)Use the seasonal indices
to adjust the forecasts found in part (c)
to obtain the final
forecasts.
Solution: Saint GBA334 all module assignments [module 1 to module 8 assignments ]