support@chatgptassignment.com
1.(20 pts.) A manufacturing firm is planning to open a new factory. There are five countries under
consideration: USA, Canada, Mexico, Chine, 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 $790 per unit.
Location Fixed Cost Variable cost
Canada $5,500,000 $220
Mexico $2,500,000 $250
USA $4,000,000 $230
Panama $2,000,000 $300
China $3,000,000 $240
a- Using cross-over analysis, find the range of production that makes each location optimal at 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
10,000.
c- If the company forecasts market demand of 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 (all in one) for the optimal location in part
(c). In your graph, use quantity values from 0 to 200,000 at increment of 10,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 depends 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 150 300 600 800 1000 1500 2000
Labor
Time (Y)
7300 6500 5400 4900 4500 4200 4000 4000 3500 3200 3100 2900
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 fits based on the value of R2. Which fit is the best?
b- If an airplane manufacturer charges $175 per hour for direct labor cost, find the total labor hours
and cost if a small airliner orders 5 airplanes (#2200 to 2204).
3- (25 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.
b- Find the Coefficient of Variation for the two data sets and determine which data 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 data sets. For Total Compensation use Bins (5,10,15, …,50) and for
Return use Bins (-50,-40,…, 140,150). Describe 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 (Y) vs. Return (X). Find best fitted linear line and R2. Is
the corporate return a good indicator of CEO compensation? 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 interruptions for this corporation.
Type of Risks Probability Cost Coverage
None 0.92 $0
Minor: A: (Loss or Damage to Electronic
Data) 0.040 $100,000
Moderate: A + B (Loss of Income and
Extra Expenses 0.020 $500,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 $10,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.
b- If the yearly price of purchasing a network interruption insurance is $100,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 $60,000? Why?
5- (20 pts.) Frigid-Transport is a small, refrigerated shipping company based in California.
Currently they have 5 refrigerated trucks and planning to expand as market for their services is
on the rise. The fixed cost of their operations is $1,000,000 per year covering managerial,
administrative, insurance, and other related expenses at their headquarters. Every truck has
approximately $100,000 annual expense for lease, maintenance, and insurance. The variable
cost of operation for each truck is $3.00 per mile covering fuel and truck driver’s pay. The
maximum mileage capacity of each truck is 200,000 miles per year and current demand is
1,000,000 miles per year. The company charges $9.00 per mile for a fully loaded truck shipment.
Construct an Excel cross tabulation table to find profit for this company using 6 to 14 trucks and
mileage demand of 1,000,000, 1,500,000, 2,000,000, 2,500,000, 3,000,000, and 3,500,000 miles
per year. Based on the table find the optimal number of trucks needed for above listed mileage
demands.
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 interruptions for this corporation.
Type of Risks Probability Cost Coverage
None 0.92 $0
Minor: A: (Loss or Damage to Electronic
Data) 0.040 $100,000
Moderate: A + B (Loss of Income and
Extra Expenses 0.020 $500,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 $10,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.
b- If the yearly price of purchasing a network interruption insurance is $100,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 $60,000? Why?
5- (20 pts.) Frigid-Transport is a small, refrigerated shipping company based in California.
Currently they have 5 refrigerated trucks and planning to expand as market for their services is
on the rise. The fixed cost of their operations is $1,000,000 per year covering managerial,
administrative, insurance, and other related expenses at their headquarters. Every truck has
approximately $100,000 annual expense for lease, maintenance, and insurance. The variable
cost of operation for each truck is $3.00 per mile covering fuel and truck driver’s pay. The
maximum mileage capacity of each truck is 200,000 miles per year and current demand is
1,000,000 miles per year. The company charges $9.00 per mile for a fully loaded truck shipment.
Construct an Excel cross tabulation table to find profit for this company using 6 to 14 trucks and
mileage demand of 1,000,000, 1,500,000, 2,000,000, 2,500,000, 3,000,000, and 3,500,000 miles
per year. Based on the table find the optimal number of trucks needed for above listed mileage
demands.