Midterm Exam Fall 2023 Name ………………
This take-home exam is an individual exam. Do not provide or receive help from anyone for completion of this test. Do not share any file with anyone in class. Must Submit Test in Excel format. Due: Midnight, Sunday Sep. 17
- (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 $750 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 140,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 |
- 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 $220 per hour for direct labor cost, find the total labor cost if a small airliner orders 10 airplanes (#2200 to 2209).
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.
- 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 data 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 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.
- 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.
- 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 |
- 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 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?
- Would you a buy this insurance policy at cost of $60,000? Why?
6- (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,200,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.50 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 $10.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.
