Please solve this using excel
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 $595 per unit.
Location Fixed Cost Variable cost
Canada $7,000,000 $210
Mexico $2,500,000 $260
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 160,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).
2- 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)
Labor Time (Y)
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?
If an airplane manufacturer charges $200 per hour for direct labor cost, find the total labor cost if a small airliner orders 10 airplanes (#710 to 719).
3- The Excel Data File includes the total compensation (in $millions) of CEO’s of 170 large public companies and the investment returns in 2012.
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.
Sort the data set based on Compensation and return separately (2 sorts) and identify top 5 corporations with highest return and highest compensations.
Find the histogram of the two sets. For Total Compensation use Bins (5,10,15, …,60) and for Return use Bins (-60,-50,-40,…., 100,140). Describe the shapes based on histograms.
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.
Find the Coefficient of variation for “Return/Total Comp” and compare with answers in part (b). What can you conclude?
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- 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
Minor: A: (Loss or Damage to Electronic Data)
Moderate: A + B (Loss of Income and Extra Expenses
Major: A+B+ C (Cyber Extortion)
Catastrophic: A+B+C+D (loss of reputation & credit monitoring of all affected)
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 cost 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 $50,000? Why?
5- 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.
a- Find the Maximax, Maximin, Equally likely, and Hurwicz Decisions. Must show all work and calculations in tabular Excel format.
b- Find the Minimax regret decision. Use Tabular Excel format.
c- Based on above analysis, what is your recommendations to John Avalon?
6- 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 $150,000 annual expense for lease, maintenance, and insurance. The variable cost of operation for each truck is $4.50 per mile covering fuel and truck driver’s pay. The maximum mileage capacity of each truck is 250,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. Based on the table indicate the optimal number of trucks needed for above listed mileages. Hint: this problem is similar to Copy Machine problem in chapter 2.
Assignment status: Solved by our experts