OIM $320$ QUALITY MANAGEMENT

HW$1$

Understanding patterns & costs of mishandled baggage.

The fictitious airline $$Flying Carpets$($a$.$k$.$a $$FC$)$ operates each week $164$ flights,

serving $30$ airports. While FC operates most flights, it uses $5$ other smaller operators

$($AV$,$ BL$,$ DJ$,$ SH and XN$)$ to service the smaller airports.

During the last week of August, which typically has a high volume of passengers

$($students returning to college, Labor Day travel, etc.$),$ FC experienced a high rate of

mishandled baggage which has hurt the bottom line of the company.

The mishandled baggage claims data has been recorded in the Excel file:

Mishandled$-$Baggage$-$Claims$-$Data.xlsx $($Click here to download the file$)$

In this file you have two worksheets:

$$ Baggage Claims Data provides the number of claims for each of the flights $($each flight

operates Sunday through Saturday, i$.$e$.$ day $1$ through $7)$

$$ FC Flight Data provides tables with information about each of the $6$ operators, the airports

they service as well as their flights. In addition it provides general data about capacity

utilization of airplanes as well as costs of mishandled bags.

Use Excel to get insights about the problem and then write a brief recommendation to FC

specifying what it needs to address to lower the rate of mishandled bags.

See next page for more information about the report that you need to submit

Your analysis

Specifically you should determine metrics and$/$or explore patterns of mishandled baggage claims:

$$ For all flights $($general observations about the entire sample$)$

$$ For each operator

$$ For each day of the week

To get started you might want to set up a pivot table that can help you

with the analysis.

If you are not sure how to set up the pivot table you can

$1.$ Click here to watch a $7$ min video clip: EXCEL Pivot Table for Lost Baggage Claims in which Prof.

Ali explains how to use a pivot table to analyze lost baggage data.

$2.$ Download the Excel file PIVOT TABLE $-$ Lost Baggage File that accompanies the $7-$mins video

Note that while the excel file that accompanies the video has a similar format to the file we use, the data

is different so $$ once you watch the video and understand how the pivot table is set up$,$ you can

replicate these steps for the file Mishandled$-$Baggage$-$Claims$-$Data.xlsx

Calculate the costs and DPMO of the mishandled bags

The information about capacity utilization of airplanes and costs of mishandled bags is provided on

the next page.

$($This information is also available in Excel file: in the purple shaded area in worksheet FC Flight Data$).$

For each operator calculate $($I recommend to use Excel for these calculations...$)$:

i$.$ The average number of passengers on each flight $($hint: use the capacity utilization data$)$

ii$.$ The average number of bags checked$-$in on each flight.

iii. The total number of bags that were checked$-$in during the week $($hint: multiply the value in $($ii$)$ by the

operator$$s total number of flights; you should have the latter value in the Pivot table...$)$

Hopefully this is a fun.

Do not spend more than

$2\mathrm{.}5$hrs on the analysis$)$

OIM $320$ QUALITY MANAGEMENT

HW$1$

iv$.$ The number of bags that were delayed, the number of bags that were damaged and the number of

bags that were stolen$/$lost $($hint: use the percentage breakdown in the purple shaded area above or

in the FC Flight Data worksheet$)$

v$.$ The costs of delayed bags, damaged bags and stolen$/$lost bags

vi$.$ The total costs of mishandled bags $($hint: add the $3$ costs you found in $($v$))$

vii. The total number of mishandled bags $($hint: use data from pivot table or add numbers you found in iv$)$

viii. Calculate DPMO $($number of Defects, i$.$e$.$ mishandled bags, per Million Opportunities$)$

$($hint: you need to use the values that you found in $($iii$)$ and $($vii$))$

Given that the $2021$ SITA report indicates that the average of the industry is $3\mathrm{.}5$ mishandled bags

per $1,000$ passengers and assuming that the average number of bags per passenger is $1\mathrm{.}3,$ how

does FC$$s operation $($including all six operators$)$ compare to the average of the industry?

hint: to determine the DPMO of the industry $-$ review problems that we did in class