1. (20 pts.) A manufacturing firm is planning to open a new factory. There are four countries under consideration: USA, Canada, Mexico, and Panama. The table below lists the fixed costs and variable costs for each site. The product is mainly sold in the U.S. for $795 per unit.
Location XXXXXXXXXXFixed Cost Variable cost
Canada $7,000,000 $210
Mexico $2,500,000 $250
USA $4,000,000 $230
Panama $1,500,000 $300
China $3,000,000 $270
a- Using cross-over analysis, find the range of production that makes each location optimal with lowest total cost.
b- Using Excel, construct total production cost linear graph for all 5 locations and verify cross-over points obtained in part (a). In your graph, use quantity values from 0 to 200,000 at increment of 5,000.
c- If the company forecasts that market demand will be around 130,000 per year, which country
is the best choice and what is the yearly profit?
d- Construct Total cost, Total revenue, and Total profit graphs for the optimal location in part (C).
Use quantity values from 0 to 200,000 at increment of 5,000.
2- (20 pts.) The direct labor cost of making airplanes decreases with repetition (learning curve). As a result, the cost of manufacturing of airplanes depend on the unit production number (first, fifth, 10th, 50th, etc.). The following data shows the direct labor cost for selected production numbers.
Airplane # (X)
1
8
16
32
64
130
200
300
400
500
600
Labor Time (Y)
7300
6500
5400
5000
4500
4200
4100
3500
3200
3000
2800
a- Using Excel graphs, find linear, power, exponential, and logarithmic fits for this dataset. Make sure to include equations and R2 for each fit. Insert all graphs here and select the best model based on R2. Rank the models based on the value of R2. Which model fit is the best?
- If an airplane manufacturer charges $150 per hour for direct labor cost, find the total labor hours and labor cost if a small airliner orders 10 airplanes (#700 to 709).
3- (30 pts.) The Excel Midterm Data File in BB includes the total compensation (in $millions) of CEO’s of 170 large public companies and the investment returns in 2012.
a- Using Excel Data Analysis, find the descriptive measures of the two data sets and describe the shape of the two data sets based on comparison of Mean and Median.
- Find the Coefficient of Variation for the two data sets and determine which set has higher variability per unit of the mean.
c- Sort the data set based on Compensation and return separately (2 sorts) and identify top 5 corporations with highest return and highest compensations.
d- Find the histogram of the two sets. For Total Compensation use Bins (5,10,15, …,50) and for Return use Bins (-40, -30 ,…. , 99, XXXXXXXXXXDescribe the shapes based on histograms.
e- Find the ratio of “Return/Total Comp.” in a new column. Based on this ratio, list top 5 corporations with highest ratio and 5 with lowest ratios. List 3 companies that you would invest in.
f- Construct scatter plot of Compensation (X) vs. Return (Y). Find best fitted linear line and R2. Is CEO compensation a good indicator of corporate performance? Explain.
4- (15 pts.) A midsize corporation is considering purchase of Cyber Liability Insurance Policy. The following table provides the probability distribution of yearly cost coverage associated with different level of Cyber Security IT network inte
uptions for this corporation.
Type of Risks
Probability
Cost Coverage
None
0.90
$0
Minor: A: (Loss or Damage to Electronic Data)
0.040
$100,000
Moderate: A + B (Loss of Income and Extra Expenses
0.03
$1,000,000
Major: A+B+ C (Cyber Extortion)
0.018
$2,500,000
Catastrophic: A+B+C+D (loss of reputation & credit monitoring of all affected)
0.002
$15,000,000
a- What is the expected cost of this policy for the insurance company? What is the Standard deviation of cost? Hint: Use Excel.
- If the yearly cost of purchasing a network inte
uption insurance is $125,000, calculate the expected gain or loss for a corporation that purchase this insurance. Would you recommend purchasing this insurance? Why?
c- Would you a buy this insurance policy at cost of $100,000? Why?
5- (15 pts.) XPRO Manufacturing is considering the introduction of a family of new products. John Avalon, the VP of Operations considering three different production processes (batch manufacturing, custom manufacturing, and group technology. The demand and profit depends on the state of economy as provided in table below.
Production Process
Poor Economy
Fair Economy
Good Economy
Excellent Economy
Batch
-$25.0 million
$60m
$155m
$200m
Custom
$12m
$22m
$60m
$100m
Group Technology
-$100m
-$60m
$40m
$150m
Probability
0.10
0.20
0.40
0.30
a- Find the Maximax, Maximin, equally likely, and Expected value decisions with respective profits. Must show all work and calculations in tabular Excel format.
- Find the Minimax regret decision and profit. Use Tabular Excel format.
c- Based on above analysis, what is your recommendations to John Avalon?
Problem 1
Location Fixed Cost Var. Cost
Canada $7,000,000 $210
Mexico $2,500,000 $250
USA $4,000,000 $230
Panama $1,500,000 $300
China $3,000,000 $270
Problem 2
Airplane # (X) Labor Time (Y)
1 7300
4 6500
12 5400
32 5000
64 4500
130 4200
200 4100
300 3500
400 3200
500 3000
600 2800
problem 3
Company Compensation ($millions) Return in 2012 (%)
Berkshire Hathaway 15.4 -36
Whole Foods Market 7.2 -35
Cincinnati Financial 5.3 -29
M&T Bank 6.6 -28
Kimco Realty 9 -25
People's United Financial 8.4 -20
Fastenal 9.5 -19
O'Reilly Automotive 20.5 -16
TECO Energy 6.9 -15
AGL Resources 9.7 -12
MeadWestvaco 9.2 -11
Sigma-Aldrich 4.7 -10
FLIR Systems 8.1 -10
Apartment Investment and Management 10 -10
Genuine Parts 8.6 -9
C.H. Robinson Worldwide 4.2 -8
Intuitive Surgical 6 -8
Total System Services 5 -7
Helmerich & Payne 7 -7
F5 Networks 10 -6
Newfield Exploration 10 -6
CenterPoint Energy 5.3 -4
Ryder System 5.5 -4
Gannett 16 -4
Kellogg 8.4 -3
Bemis 13.4 -3
First Horizon National 5.8 -2
Ameren 10.1 -2
Eastman Chemical 3.7 -1
International Game Technology 4.3 -1
McCormick & Company 11.5 0
PNC Financial Services Group 8.3 1
Carnival 8.2 2
Weyerhaeuser 11.7 2
Joy Global 12.7 2
St. Jude Medical 7.1 3
Huntington Bancshares 7.4 3
Sempra Energy 15 3
Moody's 6.3 4
Baker Hughes 10.7 4
Spectra Energy 12 4
MetroPCS Communications 7.1 5
American Electric Power 20.4 5
AmerisourceBergen 21.1 5
PACCAR 5 6
Allegheny Technologies 8.7 6
Comerica 12.6 6
Northern Trust 15.4 6
Rowan 4.9 7
NRG Energy 13 7
FMC 18 7
DTE Energy 21.6 7
Avery Dennison 6.7 8
Cabot Oil & Gas 13.2 8
Snap-on 3.8 9
Beam 8.7 9
C.R. Bard 8.7 9
Tenet Healthcare 9.1 9
Dominion Resources 13.1 9
International Flavors & Fragrances 4.5 10
Duke Energy 8.5 10
Humana 9.7 10
Public Service Enterprise Group 12.7 10
T. Rowe Price Group 11.6 11
W.W. Grainger 3.8 12
Hershey 5 12
United States Steel 10.7 12
BB&T 11.8 12
SunTrust Banks 13.8 13
Reynolds American 5.7 14
Edison International 11.8 14
EQT 5.7 15
Ecolab 7 15
Fifth Third Bancorp 7.8 15
Newmont Mining 9.5 15
Wisconsin Energy 11.6 15
McGraw-Hill 17.1 15
Ball 21.8 15
Applied Materials 8.4 17
Fluor 16.6 17
XL Group 17.9 17
Peabody Energy 21 17
Noble 5.1 18
Norfolk Southern 9.3 18
Progressive 11.3 18
TE Connectivity 4.6 19
PerkinElmer 6.3 19
Discover Financial Services 6.7 19
AES 8.5 19
CareFusion 8.6 19
Hess 10.6 19
Agilent Technologies 23.8 19
VF 25 19
U.S. Bancorp 7.2 20
Coca-Cola Enterprises 16.3 20
Motorola Solutions 18.5 20
Life Technologies 8 21
Harley-Davidson 10.1 21
Teradata 10.2 21
Covidien 20.5 21
Kimberly-Clark 4.7 22
United Parcel Service 8.9 22
Stanley Black & Decker 10.3 22
Sherwin-Williams 28 22
Tesoro 7.1 23
L-3 Communications Holdings 3.3 24
Textron 5.7 24
Corning 8.8 24
Chu
9.1 24
Dover 9.8 24
Alcoa 9.1 25
Equifax 12 25
BorgWarner 7.9 26
HCP 10.2 26
Praxair 10.3 26
NYSE Euronext 12 26
Adobe Systems 12.3 26
Delphi Automotive 0.5 27
Aflac 7.6 27
Franklin Resources 10.3 27
Apple 12.8 27
Marathon Petroleum 19.3 27
Valero Energy 10.3 28
PepsiCo 18.5 28
E.I. du Pont de Nemours 3 29
Analog Devices 6.7 29
Cigna 7.2 29
Bank of New York Mellon 8.2 29
Whirlpool 9.4 29
Marathon Oil 16.1 29
Lennar 20.7 30
Texas Instruments 7.9 31
MetLife 12.3 32
Goodyear Tire & Ru
er 12.8 32
PPG Industries 25 32
Verizon Communications 2.1 34
Pentair 6.1 35
Schlumberger 11.3 35
Gilead Sciences 7.6 37
Hewlett-Packard 10.3 37
IBM 22.6 37
Bristol-Myers Squi
16 38
Baxter International 23.9 39
Ameriprise Financial 5.5 41
Honeywell International 35 41
EMC 11.7 42
Deere 5.2 47
DirecTV 38.9 48
General Dynamics 45 48
Pfizer 1.3 50
Eaton 14.7 50
Wells Fargo 6.8 51
Allergan 6.4 52
Occidental Petroleum 8.1 58
General Electric 7.9 59
Qualcomm 9.8 60
AT&T 8.6 61
Boeing 12.5 65
Coca-Cola 13.5 65
United Technologies 12.4 73
Capital One Financial 39 73
Lockheed Martin 11 74
Tyco International 9.9 76
A
ott Laboratories 12.3 76
Prologis 5.9 77
American Express 12.2 78
Viacom 15.3 79
Sta
ucks 11.2 90
Walt Disney 12.9 95
eBay 13 100
Problem 4
Type of Risks Probability Cost Coverage
None 0.9 $0
Minor: A: (Loss or Damage to Electronic Data) 0.04 $100,000
Moderate: A + B (Loss of Income and Extra Expenses 0.03 $1,000,000
Major: A+B+ C (Cyber Extortion) 0.018 $2,500,000
Catastrophic: A+B+C+D (loss of reputation & credit monitoring of all affected) 0.002 $15,000,000
Problem 5
Production Process Poor Economy Fair Economy Good Economy Excellent Economy
Batch -$25.0 million $60m $155m $200m
Custom $12m $22m $60m $100m
Group Technology -$100m -$60m $40m $150m
Probability 0.1 0.2 0.4 0.3