Please Solve this Using Excel

Please solve this using excel

  1. 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.


Don't use plagiarized sources. Get Your Custom Essay on
Please Solve this Using Excel
Just from $13/Page
Order Essay

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) 1 8 16 32 64 130 200 300 400 500 600 700
Labor Time (Y) 7300 6500 5400 4900 4500 4200 4000 3700 3500 3200 3100 2900



  • 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 Probability Cost Coverage
None 0.90 $0
Minor: A: (Loss or Damage to Electronic Data) 0.050 $100,000
Moderate: A + B (Loss of Income and Extra Expenses 0.030 $1,000,000
Major: A+B+ C (Cyber Extortion) 0.017 $2,500,000
Catastrophic: A+B+C+D (loss of reputation & credit monitoring of all affected) 0.003 $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.


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.


Production Process Poor Economy Fair Economy Good Economy Excellent Economy
Batch -$22.0 million $55m $145m $180m
Custom $12m $22m $60m $100m
Group Technology -$120m -$73m $40m $1500m
Probability 0.10 0.30 0.40 0.20



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.