STATISTICS ASSIGNMENT
Leila@
stat.zipChapter 6.pdf
Assignment: Chapter 6
1.
Problem 6-1
The Football Bowl Subdivision (FBS) level of the National Collegiate Athletic Association (NCAA) consists of over
100 schools. Most of these schools belong to one of several conferences, or collections of schools, that compete
with each other on a regular basis in collegiate sports. Suppose the NCAA has commissioned a study that will
propose the formation of conferences based on the similarities of the constituent schools. The file FBS contains
data on schools belong to the Football Bowl Subdivision (FBS). Each row in this file contains information on a
school. The variables include football stadium capacity, latitude, longitude, athletic department revenue,
endowment, and undergraduate enrollment.
Click on the webfile logo to reference the data.
(a) Apply k-means clustering with k=10 using football stadium capacity, latitude, longitude, endowment, and
enrollment as variables. Be sure to Normalize input data and specify 50 iterations and 10 random starts in
Step 2 of the XLMiner k-Means Clustering procedure. Analyze the resultant clusters and answer the following
questions.
Choose the correct option for smallest cluster?
_________________
Which of the following is the least dense cluster?
_________________
What makes the least dense cluster so diverse?
The least dense cluster is so diverse because its average distance in cluster is _________________
(b) State theproblems which occur with defining the school membership of the 10 conferences directly with the
10 clusters?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(c) Why do the clusters in non-normalized input data differ from the clusters in normalized input data?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
Which of the following is the dominating factor?
_________________
2.
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
1 of 11 5/15/2015 2:55 PM
Problem 6-5
The Football Bowl Subdivision (FBS) level of the National Collegiate Athletic Association (NCAA) consists of over
100 schools. Most of these schools belong to one of several conferences, or collections of schools, that compete
with each other on a regular basis in collegiate sports. Suppose the NCAA has commissioned a study that will
propose the formation of conferences based on the similarities of the constituent schools. The file FBS contains
data on schools belong to the Football Bowl Subdivision (FBS).The NCAA has a preference for conferences
consisting of similar schools with respect to their endowment, enrollment, and football stadium capacity, but
these conferences must be in the same geographic region to reduce traveling costs. Follow the following steps to
address this desire. Apply k-means clustering using latitude and longitude as variables with k = 3. Be sure to
Normalize input data and specify 50 iterations and 10 random starts in Step 2 of the XLMiner k-Means
Clustering procedure.
Click on the webfile logo to reference the data.
(a) For the "west" cluster, apply hierarchical clustering with Ward's method to form two clusters using football
stadium capacity, endowment, and enrollment as variables. Be sure to Normalize input data in Step 2 of the
XLMiner Hierarchical Clustering procedure. Using a PivotTable on the data in HC_Clusters1, choose the
appropriate cluster which has small stadium capacity, small endowment, and small enrollment.
_________________
Compare the two clusters with respect to the variables stadium capacity, endowment, and enrollment.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(b) For the "east" cluster, apply hierarchical clustering with Ward's method to form three clusters using football
stadium capacity, endowment, and enrollment as variables. Be sure to Normalize input data in Step 2 of the
XLMiner Hierarchical Clustering procedure. Using a PivotTable on the data in HC_Clusters1, choose the
appropriate cluster which has small stadium capacity, small endowment, and small enrollment.
_________________
Compare the two clusters with respect to the variables stadium capacity, endowment, and enrollment.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(c) For the "south" cluster, apply hierarchical clustering with Ward's method to form four clusters using football
stadium capacity, endowment, and enrollment as variables. Be sure to Normalize input data in Step 2 of the
XLMiner Hierarchical Clustering procedure. Using a PivotTable on the data in HC_Clusters1, choose the
appropriate cluster which has small stadium capacity, small endowment, and small enrollment.
_________________
Compare the two clusters with respect to the variables stadium capacity, endowment, and enrollment.
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
2 of 11 5/15/2015 2:55 PM
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(d) State the problems do you see with the plan with defining the school membership of the nine conferences
directly with the nine total clusters? And hence write the approach to resolve the problem.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
3.
Problem 6-9
A grocery store introducing items from Italy is interested in analyzing buying trends of these new "international"
items, namely prosciutto, peroni, risotto, and gelato.
Click on the webfile logo to reference the data.
(a) How many rules satisfy this criterion of minimum support of 100 transactions and a minimum confidence of
50%?
_________________
(b) How many rules satisfy this criterion of minimum support of 250 transactions and a minimum confidence of
50%?
_________________
Why may the grocery store want to increase the minimum support required for their analysis and write down
the risk of increasing the minimum support required?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(c) Using the list of rules from part b, which of the following rule with the largest lift ratio that involves an Italian
item?
_________________
Interpret what the above rule say about the relationship between the antecedent item set and consequent
item set.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
3 of 11 5/15/2015 2:55 PM
(d) What is the support count of the item set involved in the rule with the largest lift ratio that involves an Italian
item?
_________________
Interpret the above support count.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(e) What is the confidence of the rule with the largest lift ratio that involves an Italian item?
If required, round your answers to two decimal places.
_________________ %
Interpret the above confidence of the rule.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(f) What is the lift ratio of the rule with the largest lift ratio that involves an Italian item?
If required, round your answers to two decimal places.
_________________
Interpret the above lift ratio.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(g) What insight can the grocery store obtain about its purchasers of the Italian fare?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
4.
Problem 6-13
Telecommunications companies providing cell phone service are interested in customer retention. In particular,
identifying customers who are about to churn (cancel their service) is potentially worth millions of dollars if the
company can proactively address the reason that customer is considering cancellation and retain the customer.
The WEBfile Cellphone contains customer data to be used to classify a customer as a churner or not.
In XLMiner's Partition with Oversampling procedure, partition the data so there is 50% successes (churners) in
the training set and 40% of the validation data is taken away as test data. Classify the data using k-Nearest
Neighbors with up to k = 20. Use Churn as the output variable and all the other variables as input variables. In
Step 2 of XLMiner's k-Nearest Neighbors Classification procedure, be sure to Normalize input data and to
Score on best k between 1 and specified value. Generate lift charts for both the validation data and test
data.
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
4 of 11 5/15/2015 2:55 PM
Click on the webfile logo to reference the data.
(a) What % of success is observed in the data set used?
If required, round your answer to two decimal places.
_________________
Why is partitioning with oversampling advised in this case?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(b) For the cutoff probability value 0.5, what value of k minimizes the overall error rate on the validation data?
_________________
(c) What is the overall error rate on the test data? If required, round your answer to two decimal places.
_________________ %
(d) What are the class 1 error rate and the class 0 error rate on the test data? If required, round your answers
to two decimal places.
Error rate for class 1 _________________ %
Error rate for class 0 _________________ %
(e) What are the values of sensitivity and specificity for the test data and hence interpret them. If required,
round your answers to two decimal places.
Sensitivity _________________ %
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
Specificity _________________ %
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(f) How many false positives and false negatives did the model commit on the test data?
False positives _________________
False negatives _________________
What percentage of predicted churners were false positives? If required, round your answer to one decimal
place.
_________________ %
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
5 of 11 5/15/2015 2:55 PM
What percentage of predicted non-churners were false negatives? If required, round your answer to one
decimal place.
_________________ %
(g) What is the first decile lift on the test data?
If required, round your answer to two decimal places.
_________________
Interpret the above first decile lift
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
5.
Problem 6-17
A consumer advocacy agency, Equitable Ernest, is interested in providing a service in which an individual can
estimate their own credit score (a continuous measure used by banks, insurance companies, and other
businesses when granting loans, quoting premiums, and issuing credit). The file CreditScore contains data on an
individual's credit score and other variables.
Partition the data into training (50%), validation (30%), and test (20%) sets. Predict the individuals' credit scores
using a regression tree. Use CreditScore as the output variable and all the other variables as input variables. In
Step 2 of XLMiner's Regression Tree procedure, be sure to Normalize input data and specify Using Best prune
tree as the scoring option. In Step 3 of XLMiner's Regression Tree procedure, set the maximum number of levels
to seven. Generate the Full tree, Best pruned tree, and Minimum error tree. Generate Detailed Scoring for
all three sets of data.
Click on the webfile logo to reference the data.
(a) What is the root mean squared error (RMSE) of the best pruned tree on the validation data and on the test
data?
If required, round your answers to two decimal places.
For validation data _________________
For Test data _________________
Explain the difference and whether the magnitude of the difference is of concern in this case.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
6 of 11 5/15/2015 2:55 PM
(b) Complete the following set of rules implied by the best pruned tree and how these rules are used to predict
an individual's credit score. If required, round your answers to one decimal place.
i) Predicted credit score for an individual with two or more missed payments is _________________ .
ii) Predicted credit score for an individual with less than two missed payments, less than 9.5 years of
continuous employment, and using less than 26.75% of their credit is _________________ .
iii) Predicted credit score for an individual with less than two missed payments, less than 9.5 years of
continuous employment, and using more than 26.75% of their credit is _________________ .
iv) Predicted credit score for an individual with less than two missed payments, between 9.5 and 13.5 years
of continuous employment is _________________ .
v) Predicted credit score for an individual with less than two missed payments and more than 13.5 years of
continuous employment is _________________ .
(c) Identify the weakness of the regression Best pruned tree model.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
Explain what may be contributing to inaccurate predictions and discuss possible ways to improve the
regression tree approach.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(d) Construct Regression Tree setting the Minimum #records in a terminal node to 1.
What is the RMSE of test data? If required, round your answer to two decimal places.
_________________
Compare the RMSE of the best pruned tree on the test data to the RMSE calculated in part (a)?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
In terms of number of decision nodes, how does the size of the best pruned tree compare to the size of the
best pruned tree from part (a)?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
6.
Problem 6-21
As an intern with the local home builder's association, you have been asked to analyze the state of the local
housing market which has suffered during a recent economic crisis. You have been provided three data sets in the
file HousingBubble. The Pre-Crisis worksheet contains information on 1,978 single-family homes sold during the
one-year period before the burst of the "housing bubble." The Post-Crisis worksheet contains information on
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
7 of 11 5/15/2015 2:55 PM
1,657 single-family homes sold during the one-year period after the burst of the housing bubble. The
NewDataToPredict worksheet contains information on homes currently for sale.
Click on the webfile logo to reference the data.
(a) Consider the Pre-Crisis worksheet data. Partition the data into training (50%), validation (30%), and test
(20%) sets. Predict the sale price using multiple linear regression. Use Price as the output variable and all
the other variables as input variables. To generate a pool of models to consider, execute the following steps.
In Step 2 of XLMiner's Multiple Linear Regression procedure, click the Best subset option. In the Best
Subset dialog box, check the box next to Perform best subset selection, enter 16 in the box next to
Maximum size of best subset:, enter 1 in the box next to Number of best subsets:, and check the box
next to Exhaustive search. Once you have identified an acceptable model, re-run the Multiple Linear
Regression procedure and in Step 2, check the box next to In worksheet in the Score new data area. In
the Match variable in the new range dialog box, (1) specify the NewDataToPredict worksheet in the
Worksheet: field, (2) enter the cell range A1:P2001 in the Data range: field, and (3) click Match
variable(s) with same name(s).
i. What is the lowest Mallow's Cp statistics to choose the good fit model?
If required, round your answer to two decimal places.
_________________
Select an expression for best model from the following options;
Option i) 7741.69 + 1.22*LandValue + 0.73*BuildingValue - 9102.36*Acres + 16.83*AboveSpace +
4.54*Basement - 1821.96*Baths - 46.64*Age - 20993.96*PoorCondition +
5881.86*GoodCondition
Option ii) 7741.69 + 1.22*LandValue + 0.73*BuildingValue - 9102.36*Acres + 16.83*AboveSpace -
46.64*Age - 20993.96*PoorCondition + 5881.86*GoodCondition
Option iii) 7741.69 + 1.22*LandValue + 0.73*BuildingValue
Option iv) 7741.69 + 1.22*LandValue + 0.73*BuildingValue + 16.83*AboveSpace -
20993.96*PoorCondition + 5881.86*GoodCondition
_________________
ii. What is the RMSE on the validation data and test data?
If required, round your answers to two decimal places.
For validation data _________________
For Test data _________________
iii. What is the average error on the validation data and test data?
If required, round your answers to two decimal places.
For validation data _________________
For Test data _________________
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
8 of 11 5/15/2015 2:55 PM
What does this suggest?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(b) Consider the Post-Crisis worksheet data. Partition the data into training (50%), validation (30%), and test
(20%) sets. Predict the sale price using multiple linear regression. Use Price as the output variable and all
the other variables as input variables. To generate a pool of models to consider, execute the following steps.
In Step 2 of XLMiner's Multiple Linear Regression procedure, click the Best subset option. In the Best
Subset dialog box, check the box next to Perform best subset selection, enter 16 in the box next to
Maximum size of best subset:, enter 1 in the box next to Number of best subsets:, and check the box
next to Exhaustive search. Once you have identified an acceptable model, re-run the Multiple Linear
Regression procedure and in Step 2, check the box next to In worksheet in the Score new data area. In
the Match variable in the new range dialog box, (1) specify the NewDataToPredict worksheet in the
Worksheet: field, (2) enter the cell range A1:P2001 in the Data range: field, and (3) click Match
variable(s) with same name(s).
i. What is the lowest Mallow's Cp statistics to choose the good fit model?
If required, round your answer to two decimal places.
_________________
Select an expression for best model from the following options;
Option i) -9298.71+ 1.22*LandValue + 0.73*BuildingValue - 9102.36*Acres + 16.83*AboveSpace +
4.54*Basement - 1821.96*Baths - 46.64*Age - 20993.96*PoorCondition +
5881.86*GoodCondition.
Option ii) 7741.69 + 1.22*LandValue + 0.73*BuildingValue - 9102.36*Acres + 16.83*AboveSpace -
46.64*Age - 20993.96*PoorCondition + 5881.86*GoodCondition.
Option iii) -9298.71 + 1.20*LandValue + 0.79*BuildingValue - 13179.34*Acres + 2296.51*Baths +
1994.64*Fireplaces + 3329.81*Beds + 900.22*Rooms + 3748.22*AC -
4356.64*PoorCondition + 4759.17*GoodCondition.
Option iv) -9298.71 + 1.22*LandValue + 0.79*BuildingValue + 16.83*AboveSpace -
20993.96*PoorCondition + 5881.86*GoodCondition.
_________________
ii. What is the RMSE on the validation data and test data?
If required, round your answers to two decimal places.
For validation data _________________
For Test data _________________
iii. What is the average error on the validation data and test data?
If required, round your answers to two decimal places.
For validation data _________________
For Test data _________________
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
9 of 11 5/15/2015 2:55 PM
What does this suggest?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(c) The MLR_NewScore worksheets generated in parts a and b contain the sales price predictions for the 2000
homes in the NewDataToPredict using the pre-crisis and post-crisis data, respectively. For each of these 2000
homes, compare the two predictions by computing the percentage change in predicted price between the
pre-crisis and post-crisis model. Let percentage change = (post-crisis predicted price - pre-crisis predicted
price) / pre-crisis predicted price.
Choose the appropriate histogram of percentage change for above scenario.
Option (i)
Option (ii)
Option (iii)
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
10 of 11 5/15/2015 2:55 PM
Option (iv)
_________________
What is the average percentage change in predicted price between the pre-crisis and post-crisis model?
If required, round your answer to two decimal places.
_________________ %
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
11 of 11 5/15/2015 2:55 PM
Chapter 7.pdf
Assignment: Chapter 7
1.
Problem 7-1
Cox Electric makes electronic components and has estimated the following for a new design of one of its products:
Fixed Cost = $10,000
Material cost per unit = $0.15
Labor cost per unit = $0.10
Revenue per unit = $0.65
Note that fixed cost is incurred regardless of the amount produced. Per unit material and labor costs together make up the variable cost per unit. Assuming Cox
Electric sells all that it produces; profit is calculated by subtracting the fixed cost and total variable cost from total revenue.
Click on the webfile logo to reference the data.
(a) Choose the correct influence diagram that illustrates how to calculate profit.
(i) (ii)
(iii) (iv)
_________________
(b) Choose the correct mathematical model for calculating profit.
Let q = production volume (quantity produced)
R = revenue per unit
FC = the fixed costs of production
MC = material cost per unit
LC = labor cost per unit
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
1 of 9 5/15/2015 2:55 PM
P(q) = total profit for producing (and selling) q units
(i) P(q) = Rq - FC - (MC)q - (LC)q
(ii) P(q) = Rq + FC + (MC)q + (LC)q
(iii) P(q) = Rq + FC - (MC)q + (LC)q
(iv) P(q) = Rq + FC + (MC)q - (LC)q
_________________
(c) Implement your model from part (b) in Excel using the principles of good spreadsheet design and find the profit if Cox Electric makes 12,000 units of the
new product.
If required, round your answer to nearest whole number. For subtractive or negative numbers use a minus sign. (Example: -300)
$ _________________
2.
Problem 7-3
Eastman Publishing Company is considering publishing an electronic textbook on spreadsheet applications for business. The fixed cost of manuscript
preparation, textbook design, and website construction is estimated to be $160,000. Variable processing costs are estimated to be $6 per book. The publisher
plans to sell access to the book for $46 each.
(a) Build a spreadsheet model in Excel to calculate the profit/loss for a given demand. What profit can be anticipated with a demand of 3500 copies?
For subtractive or negative numbers use a minus sign.
$ _________________
(b) Use a data table to vary demand from 1000 to 6000 increments of 200 to test the sensitivity of profit to demand. Breakeven occurs where profit goes from
a negative to a positive value, that is, breakeven is where total revenue = total cost yielding a profit of zero. In which interval of demand does breakeven
occur?
(i) Breakeven appears in the interval of 4200 to 4800 copies.
(ii) Breakeven appears in the interval of 4000 to 4200 copies.
(iii) Breakeven appears in the interval of 3800 to 4000 copies.
(iv) Breakeven appears in the interval of 3600 to 3800 copies.
_________________
(c) Use Goal Seek to answer the following question. With a demand of 3500 copies, what is the access price per copy that the publisher must charge to break
even?
If required, round your answers to two decimal places.
$ _________________
3.
Problem 7-3
Eastman Publishing Company is considering publishing an electronic textbook on spreadsheet applications for business. The fixed cost of manuscript
preparation, textbook design, and website construction is estimated to be $170,000. Variable processing costs are estimated to be $5 per book. The publisher
plans to sell access to the book for $45 each.
(a) Build a spreadsheet model in Excel to calculate the profit/loss for a given demand. What profit can be anticipated with a demand of 3,700 copies?
For subtractive or negative numbers use a minus sign.
$ _________________
(b) Use a data table to vary demand from 1000 to 6000 increments of 200 to test the sensitivity of profit to demand. Breakeven occurs where profit goes from
a negative to a positive value, that is, breakeven is where total revenue = total cost yielding a profit of zero. In which interval of demand does breakeven
occur?
(i) Breakeven appears in the interval of 3,800 to 4,000 copies.
(ii) Breakeven appears in the interval of 4,200 to 4,400 copies.
(iii) Breakeven appears in the interval of 4,400 to 4,600 copies.
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
2 of 9 5/15/2015 2:55 PM
(iv) Breakeven appears in the interval of 4,600 to 4,800 copies.
_________________
(c) Use Goal Seek to answer the following question. With a demand of 3,700 copies, what is the access price per copy that the publisher must charge to break
even?
If required, round your answers to two decimal places.
$ _________________
4.
Problem 7-5
The University of Cincinnati Center for Business Analytics is an outreach center that collaborates with industry partners on applied research and continuing
education in business analytics. One of the programs offered by the Center is a quarterly Business Intelligence Symposium. Each symposium features three
speakers on the real-world use of analytics. Each of the corporate members of the center (there are currently ten) receives five free seats to each symposium.
Nonmembers wishing to attend must pay $75 per person. Each attendee receives breakfast, lunch, and free parking. The following are the costs incurred for
putting on this event:
Rental cost for the auditorium: $150
Registration Processing: $8.50 per person
Speaker Costs: 3@$800 $2,400
Continental Breakfast: $4.00 per person
Lunch: $7.00 per person
Parking: $5.00 per person
(a) The Center for Business Analytics is considering a refund policy for no-shows. No refund would be given for members who do not attend, but for
nonmembers who do not attend, 50% of the price will be refunded. Build a spreadsheet model in Excel that calculates a profit or loss based on the number
of nonmember registrants. Extend the model you developed for the Business Intelligence Symposium to account for the fact that historically, 25% of
members who registered do not show and 10% of registered nonmembers do not attend. The Center pays the caterer for breakfast and lunch based on the
number of registrants (not the number of attendees). However, the Center only pays for parking for those who attend. What is the profit if each corporate
member registers their full allotment of tickets and 127 nonmembers register?
If required, round your answers to two decimal places.
$ _________________
(b) Use a two-way data table to show how profit changes as a function of number of registered nonmembers and the no-show percentage of nonmembers.
Vary number of nonmember registrants from 80 to 160 in increments of 5 and the percentage of nonmember no-shows from 10% to 30% in increments of
2%. In which interval of nonmember registrants does breakeven occur if the percentage of nonmember no-shows is 22%?
(i) Breakeven appears in the interval of 80 to 85 number of registered nonmembers.
(ii) Breakeven appears in the interval of 95 to 100 number of registered nonmembers.
(iii) Breakeven appears in the interval of 85 to 90 number of registered nonmembers.
(iv) Breakeven appears in the interval of 105 to 110 number of registered nonmembers.
_________________
5.
Problem 7-7
Lindsay is 25 years old and has a new job in web development. Lindsay wants to make sure she is financially sound in 30 years, so she plans to invest the same
amount into a retirement account at the end of every year for the next 30 years.
(a) Construct a data table in Excel that will show Lindsay the balance of her retirement account for various levels of annual investment and return. If Lindsay
invests $10,000 at return of 6%, what would be the balance at the end of 20 year in the account?
If required, round your answers to two decimal places.
$ _________________
(b) Develop the two-way table in Excel for annual investment amounts of $5000 to $20,000 in increments of $1000 and for returns of 0% to 12% in
increments of 1%. Note that because Lindsay invests at the end of the year, there is no interest earned on that year's contribution for the year in which she
th
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
3 of 9 5/15/2015 2:55 PM
contributes. Complete the below table.
If required, round your answers to two decimal places.
1% 2%
$5,000 $ _________________ $ _________________
$6,000 $ _________________ $ _________________
$7,000 $ _________________ $ _________________
$8,000 $ _________________ $ _________________
$9,000 $ _________________ $ _________________
$10,000 $ _________________ $ _________________
$11,000 $ _________________ $ _________________
$12,000 $ _________________ $ _________________
$13,000 $ _________________ $ _________________
$14,000 $ _________________ $ _________________
$15,000 $ _________________ $ _________________
$16,000 $ _________________ $ _________________
$17,000 $ _________________ $ _________________
$18,000 $ _________________ $ _________________
$19,000 $ _________________ $ _________________
$20,000 $ _________________ $ _________________
6.
Problem 7-7
Lindsay is 25 years old and has a new job in web development. Lindsay wants to make sure she is financially sound in 30 years, so she plans to invest the same
amount into a retirement account at the end of every year for the next 30 years.
(a) Construct a data table in Excel that will show Lindsay the balance of her retirement account for various levels of annual investment and return. If Lindsay
invests $12,000 at return of 6%, what would be the balance at the end of 20 year in the account?
If required, round your answers to two decimal places.
$ _________________
(b) Develop the two-way table in Excel for annual investment amounts of $5000 to $20,000 in increments of $1000 and for returns of 0% to 12% in
increments of 1%. Note that because Lindsay invests at the end of the year, there is no interest earned on that year's contribution for the year in which she
contributes. Complete the below table.
If required, round your answers to two decimal places.
4% 5%
$5,000 $ _________________ $ _________________
$6,000 $ _________________ $ _________________
$7,000 $ _________________ $ _________________
$8,000 $ _________________ $ _________________
$9,000 $ _________________ $ _________________
$10,000 $ _________________ $ _________________
$11,000 $ _________________ $ _________________
$12,000 $ _________________ $ _________________
$13,000 $ _________________ $ _________________
$14,000 $ _________________ $ _________________
$15,000 $ _________________ $ _________________
$16,000 $ _________________ $ _________________
th
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
4 of 9 5/15/2015 2:55 PM
$17,000 $ _________________ $ _________________
$18,000 $ _________________ $ _________________
$19,000 $ _________________ $ _________________
$20,000 $ _________________ $ _________________
7.
Problem 7-9
Newton Manufacturing produces scientific calculators. The models are N350, N450, and the N900. Newton has planned its distribution of these products around
eight customer zones: Brazil, China, France, Malaysia, U.S. Northeast, U.S. Southeast, U.S. Midwest, and U.S. West. Data for the current quarter (volume to be
shipped in thousands of units) for each product and each customer zone are given in the file Newton. Newton would like to know the total number of units going
to each customer zone and also the total units of each product shipped. There are several ways to get this information from the data set. One way is to use the
SUMIF function.
Click on the webfile logo to reference the data.
The SUMIF function extends the SUM function by allowing the user to add the values of cells meeting a logical condition. The general form of the function is
=SUMIF (test range, condition, range to be summed)
The test range is an area to search to test the condition, and the range to be summed is the position of the data to be summed. So, for example, using the
Newton file, we would use the following function to get the total units sent to Malaysia:
=SUMIF (A3:A26, A3, C3:C26)
Here, cell A3 contains the text "Malaysia", A3:A26 is the range of customer zones, and C3:C26 are the volumes for each product for these customer zones. The
SUMIF looks for matches of "Malaysia" in column A and, if a match is found, adds the volume to the total. Use the SUMIF function to get each total volume by
zone and each total volume by product.
(a) Complete the below table for total number of units distributed in each customer zone.
If required, round your answers to one decimal place.
Customer Zone Total Volume (000 units)
Brazil _________________
China _________________
France _________________
Malaysia _________________
US Midwest _________________
US Northeast _________________
US Southeast _________________
US West _________________
Total _________________
(b) Complete the below table for the total units of each product shipped.
If required, round your answers to one decimal place.
Product Total Volume (000 units)
N350 _________________
N450 _________________
N900 _________________
Total _________________
8.
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
5 of 9 5/15/2015 2:55 PM
Problem 7-11
Professor Rao would like to accurately calculate the grades for the 58 students in his Operations Planning and Scheduling class (OM 455). He has thus far
constructed a spreadsheet as partially shown below.
Click on the webfile logo to reference the data.
(a) The Overall Score is calculated by weighting the Midterm and Final Score 50% each. Use VLOOKUP with the table shown to generate the Course Grade for
each student in cells E14 through E24. Complete the below table.
Last name Course Grade
Alt _________________
Amini _________________
Amoako _________________
Apland _________________
Bachman _________________
Corder _________________
Desi _________________
Dransman _________________
Duffuor _________________
Finkel _________________
Foster _________________
(b) Use the COUNTIF function to determine the number of students receiving each letter grade.
Course Grade No. of Students
A _________________
B _________________
C _________________
D _________________
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
6 of 9 5/15/2015 2:55 PM
F _________________
9.
Problem 7-13
A put option in finance allows you to sell a share of stock at a given price in the future. There are different types of put options. Let us consider the European
put option which allows you to sell a share of stock at a given price called the exercise price, at a particular point in time after the purchase of the European put
option. For example, suppose you purchase a six-month European put option for a share of stock with an exercise price of $26. If six months later, the stock
price per share is $26 or more, the option has no value. If in six months the stock price is lower than $26 per share, then you can purchase the stock and
immediately sell it at the higher exercise price of $26. If for example, the price per share in six months is $22.50, you can purchase a share of the stock for
$22.50 and then use the put option to immediately sell the share for $26. Your profit would be the difference, $26- $22.50 = $3.50 per share less the cost of
the option. If you paid $1.00 per put option, then you profit would be $3.50-$1.00 = $2.50 per share.
Build a model in Excel to calculate the profit the European put option described above. Construct a data table in Excel that shows the profit per share for a share
price in six months between $10 and $30 per share in increments of $1.00. Complete the given table below.
If required, round your answers to two decimal places. For subtractive or negative numbers use a minus sign.
Share Price Profit Per Share
$10.00 $ _________________
$11.00 $ _________________
$12.00 $ _________________
$13.00 $ _________________
$14.00 $ _________________
$15.00 $ _________________
$16.00 $ _________________
$17.00 $ _________________
$18.00 $ _________________
$19.00 $ _________________
$20.00 $ _________________
$21.00 $ _________________
$ 22.00 $ _________________
$23.00 $ _________________
$24.00 $ _________________
$25.00 $ _________________
$26.00 $ _________________
$27.00 $ _________________
$28.00 $ _________________
$29.00 $ _________________
$30.00 $ _________________
10.
Problem 7-15
The Camera Shop sells two popular models of digital cameras. The sales of these products are not independent of each other, but rather if the price of one
increase, the sales of the other will increase. In economics, these two camera models are called substitutable products. The store wishes to establish a pricing
policy to maximize revenue from these products. A study of price and sales data shows the following relationships between the quantity sold (N) and prices (P)
of each model:
N = 195 - 0.6P + 0.25P
N = 301 + 0.08P - 0.5P
Construct a model for the total revenue and implement it on a spreadsheet. Develop two-way data table to estimate the optimal prices for each product in order
A A B
B A B
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
7 of 9 5/15/2015 2:55 PM
to maximize the total revenue. Vary each price from $250 to $500 in increments of $10.
Max profit occurs at Camera A price of $ _________________ .
Max profit occurs at Camera B price of $ _________________ .
11.
Problem 7-15
The Camera Shop sells two popular models of digital cameras. The sales of these products are not independent of each other, but rather if the price of one
increase, the sales of the other will increase. In economics, these two camera models are called substitutable products. The store wishes to establish a pricing
policy to maximize revenue from these products. A study of price and sales data shows the following relationships between the quantity sold (N) and prices (P)
of each model:
N = 192 - 0.5P + 0.25P
N = 305 + 0.08P - 0.6P
Construct a model for the total revenue and implement it on a spreadsheet. Develop two-way data table to estimate the optimal prices for each product in order
to maximize the total revenue. Vary each price from $250 to $500 in increments of $10.
Max profit occurs at Camera A price of $ _________________ .
Max profit occurs at Camera B price of $ _________________ .
12.
Problem 7-17
A few years back, Dave and Jana bought a new home. They borrowed $230,415 at a fixed rate of 5.49% (15-year term) with monthly payments of $1,881.46.
They just made their twenty-fifth payment and the current balance on the loan is $208,555.87.
Interest rates are at an all-time low and Dave and Jana are thinking of refinancing to a new 15-year fixed loan. Their bank has made the following offer: 15-year
term, 3.0%, plus out-of-pocket costs of $2,937. The out-of-pocket costs must be paid in full at the time of refinancing.
Build a spreadsheet model to evaluate this offer. The Excel function
=PMT(rate, nper, pv, fv, type)
calculates the payment for a loan based on constant payments and a constant interest rate. The arguments of this function are as follows:
rate = the interest rate for the loan
nper = the total number of payments
pv= present value - - the amount borrowed
fv = future value - - the desired cash balance after the last payment (usually 0)
type = payment type (0 = end of period, 1 = beginning of the period)
For example, for Dave and Jana's original loan there will be 180 payments (12*15 = 180), so we would use =PMT( .0549/12, 180, 230415,0,0) = $1881.46.
Note that since payments are made monthly, the annual interest rate must be expressed as a monthly rate. Also, for payment calculations, we assume that the
payment is made at the end of the month.
The savings from refinancing occur over time and therefore need to be discounted back to today's dollars. The formula for converting K dollars saved t months
from now to today's dollars is:
K
(1 + r)
where r is the monthly inflation rate. Assume r = .002 and that Dave and Jana make their payment at the end of each month.
Assume that they have accepted the refinance offer of a 15-year loan, at 3% interest rate with out-of-pocket expenses of $2,937. Recall the amount they are
borrowing is $208,555.87. Assume there is no pre-payment penalty, so that anything above the beyond the required payment is applied to the principle.
Construct a spreadsheet model in Excel so that you may use Goal Seek to determine the monthly payment that will allow Dave and Jana to pay off the loan in
12 years. Do the same for 10 and 11 years. Which option for prepayment if any, would you choose and why?
Hint: Break each monthly payment up into interest and principal (the amount that gets deducted from the balance owed) Recall that the monthly interest that is
charged is just the monthly loan rate multiplied by the remaining loan balance.
A A B
B A B
t - 1
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
8 of 9 5/15/2015 2:55 PM
If required, round your answers to two decimal places.
Pay off loan in years Additional Payment
10 Years $ _________________
11 Years $ _________________
12 Years $ _________________
Which option for prepayment if any, would you choose and why?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
13.
Problem 7-19
Floyd's Bumpers pays a transportation company to ship its product to its customers. Floyd's Bumpers ships full truckloads to its customers. Therefore, the cost
for shipping is a function of the distance traveled and a fuel surcharge (also on a per mile basis). The cost per mile is $2.42 and the fuel surcharge is $.56 per
mile. The file FloydsMay contains data for shipments for the month of May (each record is simply the customer zip code for a given truckload shipment), as well
as the distance table from the distribution centers to each customer. Use the MATCH and INDEX functions to retrieve the distance traveled for each shipment
and calculate the charge for each shipment. What is the total amount that Floyd's Bumpers spends on these May shipments?
Click on the webfile logo to reference the data.
Hint: The INDEX function may be used with a two-dimensional array: =INDEX(array, row_num, column_num), where array is a matrix, row_num is the row
numbers and column_num is the column position of the desired element of the matrix.
If required, round your answers to two decimal places.
$ _________________
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
9 of 9 5/15/2015 2:55 PM
Chapter 8.pdf
Assignment: Chapter 8
1.
Problem 8-1
Kelson Sporting Equipment, Inc., makes two different types of baseball gloves: a regular model and a catcher's model. The firm has 900 hours of production time available in its
cutting and sewing department, 300 hours available in its finishing department, and 100 hours available in its packaging and shipping department. The production time requirements
and the profit contribution per glove are given in the following table:
Production Time (Hours)
Model
Cutting and
Sewing Finishing
Packaging and
Shipping Profit/Glove
Regular model 1 1/2 1/8 $5
Catcher's model 3/2 1/3 1/4 $8
Assuming that the company is interested in maximizing the total profit contribution, answer the following:
(a) What is the linear programming model for this problem? Enter the answers as fractions or round the answers to 3 decimal places.
Let R = number of units of regular model.
C = number of units of catcher's model.
Max _________________ R + _________________ C
s.t.
_________________ R + _________________ C _________________ _________________ Cutting and sewing
_________________ R + _________________ C _________________ _________________ Finishing
_________________ R + _________________ C _________________ _________________ Packing and Shipping
R, C _________________ 0
(b) Develop a spreadsheet model and find the optimal solution using Solver. How many gloves of each model should Kelson manufacture?
Regular Model = _________________ units
Catcher's Model = _________________ units
(c) What is the total profit contribution Kelson can earn with the given production quantities?
$ _________________
(d) How many hours of production time will be scheduled in each department?
Department Time Used (Hours)
Cutting and sewing _________________
Finishing _________________
Packing and Shipping _________________
(e) What is the slack time in each department?
Department Slack Time (Hours)
Cutting and sewing _________________
Finishing _________________
Packing and Shipping _________________
2.
Problem 8-1
Kelson Sporting Equipment, Inc., makes two different types of baseball gloves: a regular model and a catcher's model. The firm has 700 hours of production time available in its
cutting and sewing department, 300 hours available in its finishing department, and 200 hours available in its packaging and shipping department. The production time requirements
and the profit contribution per glove are given in the following table:
Production Time (Hours)
Model
Cutting and
Sewing Finishing
Packaging and
Shipping Profit/Glove
Regular model 1 3/2 1/6 $4
Catcher's model 3/2 1/2 1/2 $8
Assuming that the company is interested in maximizing the total profit contribution, answer the following:
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
1 of 12 5/27/2015 6:30 AM
(a) What is the linear programming model for this problem? If required, round your answers to 3 decimal places or enter your answers as a fraction.
Let R = number of units of regular model.
C = number of units of catcher's model.
Max _________________ R + _________________ C
s.t.
_________________ R + _________________ C _________________ _________________ Cutting and sewing
_________________ R + _________________ C _________________ _________________ Finishing
_________________ R + _________________ C _________________ _________________ Packing and Shipping
R, C _________________ 0
(b) Develop a spreadsheet model and find the optimal solution using Solver. How many gloves of each model should Kelson manufacture?
Regular Model = _________________ units
Catcher's Model = _________________ units
(c) What is the total profit contribution Kelson can earn with the given production quantities?
$ _________________
(d) How many hours of production time will be scheduled in each department?
Department Time Used (Hours)
Cutting and sewing _________________
Finishing _________________
Packing and Shipping _________________
(e) What is the slack time in each department?
Department Slack Time (Hours)
Cutting and sewing _________________
Finishing _________________
Packing and Shipping _________________
3.
Problem 8-3
Blair & Rosen, Inc. (B&R) is a brokerage firm that specializes in investment portfolios designed to meet the specific risk tolerances of its clients. A client who contacted B&R this past
week has a maximum of $50,000 to invest. B&R's investment advisor decides to recommend a portfolio consisting of two investment funds: an Internet fund and a Blue Chip fund.
The Internet fund has a projected annual return of 12%, while the Blue Chip fund has a projected annual return of 9%. The investment advisor requires that at most $35,000 of the
client's funds should be invested in the Internet fund. B&R services include a risk rating for each investment alternative. The Internet fund, which is the more risky of the two
investment alternatives, has a risk rating of 6 per thousand dollars invested. The Blue Chip fund has a risk rating of 4 per thousand dollars invested. For example, if $10,000 is
invested in each of the two investment funds, B&R's risk rating for the portfolio would be 6(10) + 4(10) = 100. Finally, B&R developed a questionnaire to measure each client's risk
tolerance. Based on the responses, each client is classified as a conservative, moderate, or aggressive investor. Suppose that the questionnaire results classified the current client as
a moderate investor. B&R recommends that a client who is a moderate investor limit his or her portfolio to a maximum risk rating of 240.
(a) Formulate a linear programming model to find the best investment strategy for this client.
Let I = Internet fund investment in thousands
B = Blue Chip fund investment in thousands
If required, round your answers to two decimal places.
_________________ _________________ I + _________________ B
s.t.
_________________ I + _________________ B _________________ _________________ Available investment funds
_________________ I + _________________ B _________________ _________________ Maximum investment in the internet fund
_________________ I + _________________ B _________________ _________________ Maximum risk for a moderate investor
I, B _________________ 0
(b) Build a spreadsheet model and solve the problem using Solver. What is the recommended investment portfolio for this client?
Internet Fund = $ _________________
Blue Chip Fund = $ _________________
What is the annual return for the portfolio?
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
2 of 12 5/27/2015 6:30 AM
$ _________________
(c) Suppose that a second client with $50,000 to invest has been classified as an aggressive investor. B&R recommends that the maximum portfolio risk rating for an aggressive
investor is 320. What is the recommended investment portfolio for this aggressive investor?
Internet Fund = $ _________________
Blue Chip Fund = $ _________________
Annual Return = $ _________________
(d) Suppose that a third client with $50,000 to invest has been classified as a conservative investor. B&R recommends that the maximum portfolio risk rating for a conservative
investor is 160. Develop the recommended investment portfolio for the conservative investor.
Internet Fund = $ _________________
Blue Chip Fund = $ _________________
Annual Return = $ _________________
4.
Problem 8-3
Blair & Rosen, Inc. (B&R) is a brokerage firm that specializes in investment portfolios designed to meet the specific risk tolerances of its clients. A client who contacted B&R this past
week has a maximum of $50,000 to invest. B&R's investment advisor decides to recommend a portfolio consisting of two investment funds: an Internet fund and a Blue Chip fund.
The Internet fund has a projected annual return of 12%, while the Blue Chip fund has a projected annual return of 9%. The investment advisor requires that at most $35,000 of the
client's funds should be invested in the Internet fund. B&R services include a risk rating for each investment alternative. The Internet fund, which is the more risky of the two
investment alternatives, has a risk rating of 6 per thousand dollars invested. The Blue Chip fund has a risk rating of 4 per thousand dollars invested. For example, if $10,000 is
invested in each of the two investment funds, B&R's risk rating for the portfolio would be 6(10) + 4(10) = 100. Finally, B&R developed a questionnaire to measure each client's risk
tolerance. Based on the responses, each client is classified as a conservative, moderate, or aggressive investor. Suppose that the questionnaire results classified the current client as
a moderate investor. B&R recommends that a client who is a moderate investor limit his or her portfolio to a maximum risk rating of 240.
(a) Formulate a linear programming model to find the best investment strategy for this client.
Let I = Internet fund investment in thousands
B = Blue Chip fund investment in thousands
If required, round your answers to two decimal places.
_________________ _________________ I + _________________ B
s.t.
_________________ I + _________________ B _________________ _________________ Available investment funds
_________________ I + _________________ B _________________ _________________ Maximum investment in the internet fund
_________________ I + _________________ B _________________ _________________ Maximum risk for a moderate investor
I, B _________________ 0
(b) Build a spreadsheet model and solve the problem using Solver. What is the recommended investment portfolio for this client?
Internet Fund = $ _________________
Blue Chip Fund = $ _________________
What is the annual return for the portfolio?
$ _________________
(c) Suppose that a second client with $50,000 to invest has been classified as an aggressive investor. B&R recommends that the maximum portfolio risk rating for an aggressive
investor is 320. What is the recommended investment portfolio for this aggressive investor?
Internet Fund = $ _________________
Blue Chip Fund = $ _________________
Annual Return = $ _________________
(d) Suppose that a third client with $50,000 to invest has been classified as a conservative investor. B&R recommends that the maximum portfolio risk rating for a conservative
investor is 160. Develop the recommended investment portfolio for the conservative investor.
Internet Fund = $ _________________
Blue Chip Fund = $ _________________
Annual Return = $ _________________
5.
Problem 8-5
Round Tree Manor is a hotel that provides two types of rooms with three rental classes: Super Saver, Deluxe, and Business. The profit per night for each type of room and rental class
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
3 of 12 5/27/2015 6:30 AM
is as follows:
Rental Class
Super Saver Deluxe Business
Room
Type I $30 $35 -
Type II $20 $30 $40
Type I rooms do not have wireless Internet access and are not available for the Business rental class. Round Tree's management makes a forecast of the demand by rental class for
each night in the future. A linear programming model developed to maximize profit is used to determine how many reservations to accept for each rental class. The demand forecast
for a particular night is 130 rentals in the Super Saver class, 60 rentals in the Deluxe class, and 50 rentals in the Business class. Round Tree has 100 Type I rooms and 120 Type II
rooms.
(a) Use linear programming to determine how many reservations to accept in each rental class and how the reservations should be allocated to room types.
Rental Class with room type No of Reservations
Super Saver rentals allocated to room type I _________________
Super Saver rentals allocated to room type II _________________
Deluxe rentals allocated to room type I _________________
Deluxe rentals allocated to room type II _________________
Business rentals allocated to room type II _________________
Demand by _________________ rental class was not satisfied.
Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(b) How many reservations can be accommodated in each rental class?
Rental Class No of Reservations
Super Saver I _________________
Deluxe _________________
Business _________________
(c) Management is considering offering a free breakfast to anyone upgrading from a Super Saver reservation to Deluxe class. If the cost of the breakfast to Round Tree is $5, should
this incentive be offered?
_________________
Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(d) With a little work, an unused office area could be converted to a rental room. If the conversion cost is the same for both types of rooms, would you recommend converting the
office to a Type I or a Type II room?
Type I Type II
Shadow Price $ _________________ $ _________________
Convert an unused office area to _________________ room.
Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(e) Could the linear programming model be modified to plan for the allocation of rental demand for the next night?
_________________
What information would be needed and how would the model change? Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
6.
Problem 8-5
Round Tree Manor is a hotel that provides two types of rooms with three rental classes: Super Saver, Deluxe, and Business. The profit per night for each type of room and rental class
is as follows:
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
4 of 12 5/27/2015 6:30 AM
Rental Class
Super Saver Deluxe Business
Room
Type I $30 $35 -
Type II $20 $30 $40
Type I rooms do not have wireless Internet access and are not available for the Business rental class. Round Tree's management makes a forecast of the demand by rental class for
each night in the future. A linear programming model developed to maximize profit is used to determine how many reservations to accept for each rental class. The demand forecast
for a particular night is 130 rentals in the Super Saver class, 60 rentals in the Deluxe class, and 50 rentals in the Business class. Round Tree has 100 Type I rooms and 120 Type II
rooms.
(a) Use linear programming to determine how many reservations to accept in each rental class and how the reservations should be allocated to room types.
Rental Class with room type No of Reservations
Super Saver rentals allocated to room type I _________________
Super Saver rentals allocated to room type II _________________
Deluxe rentals allocated to room type I _________________
Deluxe rentals allocated to room type II _________________
Business rentals allocated to room type II _________________
Demand by _________________ rental class was not satisfied.
Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(b) How many reservations can be accommodated in each rental class?
Rental Class No of Reservations
Super Saver I _________________
Deluxe _________________
Business _________________
(c) Management is considering offering a free breakfast to anyone upgrading from a Super Saver reservation to Deluxe class. If the cost of the breakfast to Round Tree is $5, should
this incentive be offered?
_________________
Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(d) With a little work, an unused office area could be converted to a rental room. If the conversion cost is the same for both types of rooms, would you recommend converting the
office to a Type I or a Type II room?
Type I Type II
Shadow Price $ _________________ $ _________________
Convert an unused office area to _________________ room.
Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(e) Could the linear programming model be modified to plan for the allocation of rental demand for the next night?
_________________
What information would be needed and how would the model change? Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
7.
Problem 8-7
Vollmer Manufacturing makes three components for sale to refrigeration companies. The components are processed on two machines: a shaper and a grinder. The times (in minutes)
required on each machine are as follows:
Machine
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
5 of 12 5/27/2015 6:30 AM
Component Shaper Grinder
1 6 4
2 4 5
3 4 2
The shaper is available for 120 hours, and the grinder is available for 110 hours. No more than 200 units of component 3 can be sold, but up to 1000 units of each of the other
components can be sold. In fact, the company already has orders for 600 units of component 1 that must be satisfied. The profit contributions for components 1, 2, and 3 are $8, $6,
and $9, respectively.
(a) Formulate and solve for the recommended production quantities.
Let C = units of component 1 manufactured
C = units of component 2 manufactured
C = units of component 3 manufactured
Max _________________
C
+ _________________
C
+ _________________
C
s.t.
_________________
C
+ _________________
C
+ _________________
C
_________________ _________________ Shaper
_________________
C
+ _________________
C
+ _________________
C
_________________ _________________ Grinder
_________________
C
_________________ _________________ Maximum units of component
3
_________________
C
_________________ _________________ Maximum units of component
1
_________________
C
_________________ _________________ Maximum units of component
2
_________________
C
_________________ _________________ Minimum units of component
1
C , C , C _________________ 0
Optimal solution: C , = _________________ , C , = _________________ , C = _________________
(b) What are the objective coefficients ranges for the three components?
Variable Objective Coefficient Range
C _________________ to _________________
C _________________ to _________________
C _________________ to _________________
Interpret these ranges for company management.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(c) What are the right-hand-side ranges?
Constraint Right-Hand-Side Range
1 _________________ to _________________
2 _________________ to _________________
Interpret these ranges for company management.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(d) If more time could be made available on the grinder, how much would it be worth?
_________________
Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
(e) If more units of component 3 can be sold by reducing the sales price by $4, should the company reduce the price?
_________________
1
2
3
1 2 3
1 2 3
1 2 3
3
1
2
1
1 2 3
1 2 3
1
2
3
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
6 of 12 5/27/2015 6:30 AM
Explain.
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
8.
Problem 8-9
The Westchester Chamber of Commerce periodically sponsors public service seminars and programs. Currently, promotional plans are under way for this year's program. Advertising
alternatives include television, radio, and newspaper. Audience estimates, costs, and maximum media usage limitations are as shown:
Constraint Television Radio Newspaper
Audience per advertisement 100,000 18,000 40,000
Cost per advertisement $2000 $300 $600
Maximum media usage 10 20 10
To ensure a balanced use of advertising media, radio advertisements must not exceed 50% of the total number of advertisements authorized. In addition, television should account
for at least 10% of the total number of advertisements authorized.
(a) If the promotional budget is limited to $18,200, how many commercial messages should be run on each medium to maximize total audience contact?
Advertisement Alternatives
No of commercial
messages
Television _________________
Radio _________________
Newspaper _________________
What is the allocation of the budget among the three media?
Advertisement Alternatives Budget ($)
Television $ _________________
Radio $ _________________
Newspaper $ _________________
What is the total audience reached?
_________________
(b) By how much would audience contact increase if an extra $100 were allocated to the promotional budget?
Increase in audience coverage of approximately _________________
9.
Problem 8-9
The Chamber of Commerce periodically sponsors public service seminars and programs. Currently, promotional plans are under way for this year's program. Advertising alternatives
include television, radio, and newspaper. Audience estimates, costs, and maximum media usage limitations are as shown:
Constraint Television Radio Newspaper
Audience per advertisement 100,000 16,000 35,000
Cost per advertisement $1,500 $250 $650
Maximum media usage 10 24 14
To ensure a balanced use of advertising media, radio advertisements must not exceed 50% of the total number of advertisements authorized. In addition, television should account
for at least 10% of the total number of advertisements authorized.
(a) If the promotional budget is limited to $18,400, how many commercial messages should be run on each medium to maximize total audience contact?
Advertisement Alternatives
No of commercial
messages
Television _________________
Radio _________________
Newspaper _________________
What is the allocation of the budget among the three media?
Advertisement Alternatives Budget ($)
Television $ _________________
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
7 of 12 5/27/2015 6:30 AM
Radio $ _________________
Newspaper $ _________________
What is the total audience reached?
_________________
(b) By how much would audience contact increase if an extra $100 were allocated to the promotional budget?
Increase in audience coverage of approximately _________________
10.
Problem 8-11
The employee credit union at State University is planning the allocation of funds for the coming year. The credit union makes four types of loans to its members. In addition, the
credit union invests in risk-free securities to stabilize income. The various revenue-producing investments together with annual rates of return are as follows:
Type of Loan/Investment Annual Rate of Return (%)
Automobile loans 8
Furniture loans 10
Other secured loans 11
Signature loans 12
Risk-free securities 9
The credit union will have $2 million available for investment during the coming year. State laws and credit union policies impose the following restrictions on the composition of the
loans and investments:
• Risk-free securities may not exceed 30% of the total funds available for investment.
• Signature loans may not exceed 10% of the funds invested in all loans (automobile, furniture, other secured, and signature loans).
• Furniture loans plus other secured loans may not exceed the automobile loans.
• Other secured loans plus signature loans may not exceed the funds invested in risk-free securities.
How should the $2 million be allocated to each of the loan/investment alternatives to maximize total annual return?
Type of Loan/Investment Fund Allocation
Automobile loans $ _________________
Furniture loans $ _________________
Other secured loans $ _________________
Signature loans $ _________________
Risk-free securities $ _________________
What is the projected total annual return?
Annual Return = $ _________________
11.
Problem 8-11
The employee credit union at State University is planning the allocation of funds for the coming year. The credit union makes four types of loans to its members. In addition, the
credit union invests in risk-free securities to stabilize income. The various revenue-producing investments together with annual rates of return are as follows:
Type of Loan/Investment Annual Rate of Return (%)
Automobile loans 8
Furniture loans 10
Other secured loans 11
Signature loans 12
Risk-free securities 9
The credit union will have $2 million available for investment during the coming year. State laws and credit union policies impose the following restrictions on the composition of the
loans and investments:
• Risk-free securities may not exceed 25% of the total funds available for investment.
• Signature loans may not exceed 11% of the funds invested in all loans (automobile, furniture, other secured, and signature loans).
• Furniture loans plus other secured loans may not exceed the automobile loans.
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
8 of 12 5/27/2015 6:30 AM
• Other secured loans plus signature loans may not exceed the funds invested in risk-free securities.
How should the $2 million be allocated to each of the loan/investment alternatives to maximize total annual return?
Type of Loan/Investment Fund Allocation
Automobile loans $ _________________
Furniture loans $ _________________
Other secured loans $ _________________
Signature loans $ _________________
Risk-free securities $ _________________
What is the projected total annual return?
Annual Return = $ _________________
12.
Problem 8-13
The Silver Star Bicycle Company will be manufacturing both men's and women's models for its Easy-Pedal bicycles during the next two months. Management wants to develop a
production schedule indicating how many bicycles of each model should be produced in each month. Current demand forecasts call for 150 men's and 125 women's models to be
shipped during the first month and 200 men's and 150 women's models to be shipped during the second month. Additional data are shown:
Model
Production
Costs
Labor Requirements (hours)
Current
Inventory Manufacturing Assembly
Men's $120 2.0 1.5 20
Women's $90 1.6 1.0 30
Last month the company used a total of 1000 hours of labor. The company's labor relations policy will not allow the combined total hours of labor (manufacturing plus assembly) to
increase or decrease by more than 100 hours from month to month. In addition, the company charges monthly inventory at the rate of 2% of the production cost based on the
inventory levels at the end of the month. The company would like to have at least 25 units of each model in inventory at the end of the two months. Hint: Define variables for
production and inventory held in each period for each product. Then use a constraint to define the relationship between these: inventory from end of previous period + produced this
period - demand this period = inventory at end of this period.
(a) Establish a production schedule that minimizes production and inventory costs and satisfies the labor-smoothing, demand, and inventory requirements. What inventories will be
maintained and what are the monthly labor requirements?
Production schedule: The optimal solution is as follows
Production
Schedule
Men's Model Women's Model
Month 1
_________________ _________________
Month 2
_________________ _________________
Total Cost = $ _________________
Inventory Schedule
Men's Women's
Month 1 _________________ _________________
Month 2 _________________ _________________
If required, round your answer to two decimal places.
Labor Levels
Previous
month
_________________
hours
Month 1 _________________
hours
Month 2 _________________
hours
(b) If the company changed the constraints so that monthly labor increases and decreases could not exceed 50 hours, what would happen to the production schedule?
Production schedule: The revised optimal solution is as follows
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
9 of 12 5/27/2015 6:30 AM
Production Men's Model Women's Model
Month 1
_________________ _________________
Month 2
_________________ _________________
Total Cost = $ _________________
How much will the cost increase?
$ _________________
What would you recommend?
The input in the box below will not be graded, but may be reviewed and considered by your instructor.
_________________
13.
Problem 8-15
Bay Oil produces two types of fuels (regular and super) by mixing three ingredients. The major distinguishing feature of the two products is the octane level required. Regular fuel
must have a minimum octane level of 90 while super must have a level of at least 100. The cost per barrel, octane levels and available amounts (in barrels) for the upcoming
two-week period appear in the table below. Likewise, the maximum demand for each end product and the revenue generated per barrel are shown below.
Ingredient Cost/bbl. Octane Available (barrels)
1 $16.50 100 110,000
2 $14.00 87 350,000
3 $17.50 110 300,000
Revenue/barrel Max Demand (barrels)
Regular $18.50 350,000
Super $20.00 500,000
Develop and solve a linear programming model to maximize contribution to profit.
Let R = the number of barrels of input i to use to produce Regular, i = 1, 2, 3
S = the number of barrels of input i to use to produce Super, i = 1, 2, 3
If required, round your answers to one decimal place. For subtractive or negative numbers use a minus sign even if there is a + sign before the blank. (Example: -300)
Max
_________________
R
+
_________________
R
+
_________________
R
+
_________________
S
+
_________________
S
+
_________________
S
s.t.
_________________
R
+
_________________
S
≤
_________________
_________________
R
+ +
_________________
S
≤
_________________
_________________
R
+
_________________
S
≤
_________________
_________________
R
+
_________________
R
+
_________________
R
≤
_________________
_________________
S
+
_________________
S
+
_________________
S
≤
_________________
_________________
R
+
_________________
R
+
_________________
R
≥
_________________
R
+
_________________
R
+
_________________
R
_________________
S
+
_________________
S
+
_________________
S
≥
_________________
S
+
_________________
S
+
_________________
S
i
i
1 2 3 1 2 3
1 1
2 2
3 3
1 2 3
1 2 3
1 2 3 1 2 3
1 2 3 1 2 3
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
10 of 12 5/27/2015 6:30 AM
R , R , R , S , S , S ≥ 0
What is the optimal contribution to profit?
Maximum Profit = $ _________________ by making _________________ barrels of Regular and _________________ barrels of Super.
14.
Problem 8-17
Consider the following network representation of a transportation problem:
The supplies, demands, and transportation costs per unit are shown on the network. The optimal (cost minimizing) distribution plan is given below.
City Quantity Cost
Jefferson City - Des Moines 20 280
Jefferson City - St. Louis 10 70
Omaha - Des Moines 5 40
Omaha - Kansas City 15 150
Total Cost: $540
Find an alternative optimal solution for the above problem.
City Quantity Cost
Jefferson City - Des Moines 5 _________________
Jefferson City - Kansas City _________________ _________________
Jefferson City - St. Louis _________________ _________________
Omaha - Des Moines _________________ _________________
Total Cost: $ _________________
15.
Problem 8-19
The Calhoun Textile Mill is in the process of deciding on a production schedule. It wishes to know how to weave the various fabrics it will produce during the coming quarter. The sales
department has confirmed orders for each of the 15 fabrics that are produced by Calhoun. These demands are given in the following table. Also given in this table is the variable cost
for each fabric. The mill operates continuously during the quarter: 13 weeks, 7 days a week, and 24 hours a day.
There are two types of looms: dobbie and regular. Dobbie looms can be used to make all fabrics, and are the only looms that can weave certain fabrics, such as plaids. The rate of
production for each fabric on each type of loom is also given in the table. Note that if the production rate is zero, the fabric cannot be woven on that type of loom. Also, if a fabric can
be woven on each type of loom, then the production rates are equal. Calhoun has 90 regular looms and 15 dobbie looms. For this exercise, assume the time requirement to change
over a loom from one fabric to another is negligible.
Management would like to know how to allocate the looms to the fabrics and which fabrics to buy on the market so as to minimize the cost of meeting demand. Complete the optimal
solution table.
Click on the webfile logo to reference the data.
If required, round your answers to two decimal places. If an amount is zero, enter "0".
Fabric Demand Dobbie Rate Regular Rate Mill Cost Sub. Cost
1 16500 4.65 0.00 $0.66 $0.80
1 2 3 1 2 3
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
11 of 12 5/27/2015 6:30 AM
2 52000 4.65 0.00 $0.56 $0.70
3 45000 4.65 0.00 $0.66 $0.85
4 22000 4.65 0.00 $0.55 $0.70
5 76500 5.19 5.19 $0.61 $0.75
6 110000 3.81 3.81 $0.62 $0.75
7 122000 4.19 4.19 $0.65 $0.80
8 62000 5.23 5.23 $0.49 $0.60
9 7500 5.23 5.23 $0.50 $0.70
10 69000 5.23 5.23 $0.44 $0.60
11 70000 3.73 3.73 $0.64 $0.80
12 82000 4.19 4.19 $0.57 $0.75
13 10000 4.44 4.44 $0.50 $0.65
14 380000 5.23 5.23 $0.31 $0.45
15 62000 4.19 4.19 $0.50 $0.70
Optimal Solution:
Fabric Dobbie Regular Purchase Outside
1 16,500.00 _________________ 0
2 _________________ _________________ _________________
3 _________________ _________________ _________________
4 _________________ _________________ _________________
5 _________________ _________________ _________________
6 _________________ _________________ _________________
7 _________________ _________________ _________________
8 _________________ _________________ _________________
9 _________________ _________________ _________________
10 _________________ _________________ _________________
11 _________________ _________________ _________________
12 _________________ _________________ _________________
13 _________________ _________________ _________________
14 _________________ _________________ _________________
15 0 62,000.00 0
Total Cost: $ _________________
CengageNOW | Assignment | Print http://sjc.cengagenow.com/ilrn/takeAssignment/printUntakenAssignmen...
12 of 12 5/27/2015 6:30 AM
