Question

The direct labor cost of making airplanes decreases with learning (learning curve). As a result, the cost of manufacturing a given airplane depends on the unit number (first, fifth, 10th, 50th, etc.). The following data shows the direct labor cost for selected production numbers. Airplane # (X) 1 4 12 32 64 130 200 300 500 Labor Time (Y) 7300 6500 5400 5000 4500 4200 4000 3700 3400 a- Using Excel graphs, find linear, power, exponential, and polynomial 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. NOTE: No logarithmic model required. b- If an airplane manufacturer charges $200 per hour for direct labor cost, use the best fitted model equation in part (a) and find the total labor cost if a small airliner orders 5 airplanes (#505 to 509).

Please explain/show, with formulas, in Excel.  

Answer 1

The given data is:

Airplane (X)	Labor Time (Y)
1	7300
4	6500
12	5400
32	5000
64	4500
130	4200
200	4000
300	3700
500	3400

Using Excel<data<megastat<regression/correlation<scatterplot

Using Excel<data<megastat<regression/correlation<scatterplot

linear

power

Note: Using Excel<data<megastat<regression/correlation<scatterplot<doble click on trend line obtained in a part<select power

After getting the scatterplot<double click on the trend line that is obtained in a part. You will get a dialog box. select power from there.

Similarly, all the graphs are obtained from a part graph.

I am attaching a screenshot. Please do the comment if this is not clear to you i will surely help you

exponential

Using Excel<data<megastat<regression/correlation<scatterplot<doble click on trend line obtained in b part<select Exponential

polynomial

The best fit model is Power Curve

b)

Now using the best fit model equation we have to find the total labor cost if a small airline orders 5 airplanes:

Power Curve equation

Here X=5

The total labor cost if a small airliner orders 5 airplanes is 6142.

Please do the comment for any doubt or clarification. Please upvote if this helps you out. Thank You!