The work must be shown

2.3.
    A. Sort the carriers in descending order by their current year on-time %. Which ones are providing the best and the worst service in the current year?
    B. Calculate the change from previous to current year on-time percentange. Use conditional formatting to highlight those with decreases.
    Carrier    Previous Year On-time Percentage    Current Year On-time Percentage    Change in On-time Percentage
    Blue Box Shipping    88.4%    94.8%    7.240%
    Cheetah LLC    89.3%    91.8%    2.800%
    Smith Logistics    84.3%    88.7%    5.219%
    Granite State Carriers    81.8%    87.6%    7.090%
    Super Freight    92.1%    86.8%    -5.755%
    Minuteman Company    91.0%    84.2%    -7.473%
    Jones Brothers    68.9%    82.8%    20.174%
    Honsin Limited    74.2%    80.1%    7.951%
    Rapid Response    78.8%    70.9%    -10.025%
    C. Use conditional formatting to create data bars for the change in on-time %.
    Carrier    Previous Year On-time Percentage    Current Year On-time Percentage    Change in On-time Percentage
    Blue Box Shipping    88.4%    94.8%    7.24%
    Cheetah LLC    89.3%    91.8%    2.80%
    Smith Logistics    84.3%    88.7%    5.22%
    Granite State Carriers    81.8%    87.6%    7.09%
    Super Freight    92.1%    86.8%    -5.75%
    Minuteman Company    91.0%    84.2%    -7.47%
    Jones Brothers    68.9%    82.8%    20.17%
    Honsin Limited    74.2%    80.1%    7.95%
    Rapid Response    78.8%    70.9%    -10.03%
    D. Which carriers should Ohio Logistics try to use in the future? Why?
    Blue Box Shipping
    As it has the highest current year on time percentage. And a reasonable 7% increase as compared to year
2.17.
    Suppose you have invested $10,000 in the Stivers mutual fund, and $5,000 in Trippi fund. The value of these investments in the subsequent years are given in the below table.
    Compute the growth factors for both, and the geometric mean to determine which mutual fund performed better. Explain your reasoning.
        Stivers        Trippi
    Year    End of Year Value    Growth Factor    End of Year Value    Growth Factor
    0    $10,000        $5,000
    1    $11,000    0.100    $5,600    0.120
    2    $12,000    0.091    $6,300    0.125
    3    $13,000    0.083    $6,900    0.095
    4    $14,000    0.077    $7,600    0.101
    5    $15,000    0.071    $8,500    0.118
    6    $16,000    0.067    $9,200    0.082
    7    $17,000    0.063    $9,900    0.076
    8    $18,000    0.059    $10,600    0.071
            Stivers Geometric Mean:    7.52%        
            Trippi Geometric Mean:    9.66%        
    Trippi with a higher 9.66% of average annual returns performed better.
2.25.
    Below profits and market capitalization of a sample of firms in the Fortune 500.
    Company    Profits ($ millions)    Market Capitalization ($ millions)
    Alliant Techsystems    313.2    1891.9
    Amazon.com    631    81458.6
    AmerisourceBergen    706.6    10087.6
    Avis Budget Group    -29    1175.8
    Boeing    4,018.00    55188.8
    Cardinal Health    959    14115.2
    Cisco Systems    6,490.00    97376.2
    Coca-Cola    8,572.00    157130.5
    ConocoPhillips    12,436.00    95251.9
    Costco Wholesale    1,462.00    36461.2
    CVS Caremark    3,461.00    53575.7
    Delta Air Lines    854    7082.1
    Fidelity National Financial    369.5    3461.4
    FMC Technologies    399.8    12520.3
    Foot Locker    278    3547.6
    General Motors    9,190.00    32382.4
    Harley-Davidson    599.1    8925.3
    HCA Holdings    2,465.00    9550.2
    Kraft Foods    3,527.00    65917.4
    Kroger    602    13819.5
    Lockheed Martin    2,655.00    26651.1
    Medco Health Solutions    1,455.70    21865.9
    Owens Corning    276    3417.8
    Pitney Bowes    617.5    3681.2
    Procter & Gamble    11,797.00    182109.9
    Ralph Lauren    567.6    12522.8
    Rockwell Automation    697.8    10514.8
    Rockwell Collins    634    8560.5
    United Stationers    109    1381.6
    United Technologies    4,979.00    66606.5
    UnitedHealth Group    5,142.00    53469.4
    A. Plot a scatter diagram showing how market capitalization changes with profits.
    
    B. Calculate the covariance between profits and market capitalization. Interpret it.
    Covariance:    127553205.133    
    C. Calculate the correlation coefficient between profits and market capitalization. Interpret it.
    Correlation:    0.823    
Scatterplot
Market Capitalization ($ millions)    
313.2    631    706.6    -29    4018    959    6490    8572    12436    1462    3461    854    369.5    399.8    278    9190    599.1    2465    3527    602    2655    1455.7    276    617.5    11797    567.6    697.8    634    109    4979    5142    1891.9    81458.600000000006    10087.6    1175.8    55188.800000000003    14115.2    97376.2    157130.5    95251.9    36461.199999999997    53575.7    7082.1    3461.4    12520.3    3547.6    32382.400000000001    8925.2999999999993    9550.2000000000007    65917.399999999994    13819.5    26651.1    21865.9    3417.8    3681.2    182109.9    12522.8    10514.8    8560.5    1381.6    66606.5    53469.4    Market Capitalization ($ millions)
Profits ($ millions)
3.8.
    The data below shows failures of FDIC insured banks between 2000 and 2012. Create SEPARATE pivot tables to asnwer the following questions.                                                                    Closing Date    (Multiple Items)
                                                                                    Closing Date    (Multiple Items)        Closing Date    (Multiple Items)
    Bank Name    City    State    Closing Date                                         Closing Date    (All)            Row Labels    Count of State
    Fort Lee Federal Savings Bank, FSB    Fort Lee    NJ    20-Apr-12            A. Which state had the largest number of bank closings between 2000 and 2012?                                            AL    1        Row Labels    Count of State        Row Labels    Count of State            Row Labels    Count of State
    Fidelity Bank    Dearborn    MI    30-Mar-12                                        Row Labels    Count of State            AR    1        CA    33        FL    58            FL    63
    Premier Bank    Wilmette    IL    23-Mar-12            GA    80                        GA    80            AZ    4        FL    58        Naples    4            11-Jan-02    1
    Covenant Bank & Trust    Rock Spring    GA    23-Mar-12                                        FL    63            CA    12        NY    4        Miami    3            1-Mar-02    1
    New City Bank     Chicago    IL    9-Mar-12                                        IL    53            FL    29        TX    7        Tampa    3            12-Mar-04    1
    Global Commerce Bank    Doraville    GA    2-Mar-12                                        CA    39            GA    21        Grand Total    102        Sarasota    3            1-Aug-08    1
    Home Savings of America    Little Falls    MN    24-Feb-12                                        MN    19            IL    16                    Orlando    2            31-Oct-08    1
    Central Bank of Georgia    Ellaville    GA    24-Feb-12                                        WA    17            KS    3                    Coral Gables    2            30-Jan-09    1
    SCB Bank    Shelbyville    IN    10-Feb-12                                        MI    14            LA    1                    Jacksonville    2            13-Feb-09    1
    Charter National Bank and Trust     Hoffman Estates    IL    10-Feb-12                                        AZ    13            MA    1                    Bradenton    2            21-May-09    1
    BankEast    Knoxville    TN    27-Jan-12                                        MO    12            MD    4                    Ocala    2            31-Jul-09    1
    Patriot Bank Minnesota    Forest Lake    MN    27-Jan-12                                        NV    11            MI    5                    Key West    1            7-Aug-09    2
    Tennessee Commerce Bank     Franklin    TN    27-Jan-12                                        TX    10            MN    8                    Port Orange    1            23-Oct-09    3
    First Guaranty Bank and Trust Company of Jacksonville    Jacksonville    FL    27-Jan-12                                        CO    9            MO    6                    Milton    1            13-Nov-09    2
    American Eagle Savings Bank    Boothwyn    PA    20-Jan-12                                        KS    8            MS    1                    Clearwater    1            20-Nov-09    1
    The First State Bank    Stockbridge    GA    20-Jan-12                                        UT    7            NE    1                    Cape Coral    1            11-Dec-09    1
    Central Florida State Bank    Belleview    FL    20-Jan-12                                         SC    7            NJ    1                    Clermont    1            18-Dec-09    1
    Western National Bank    Phoenix    AZ    16-Dec-11            B. How many bank closing occurred in Nevada (NV) in 2010? In which cities?                            PA    7            NM    2                    Marco Island    1            22-Jan-10    1
    Premier Community Bank of the Emerald Coast    Crestview    FL    16-Dec-11                                        OH    7            NV    4                    Clewiston    1            29-Jan-10    1
    Central Progressive Bank    Lacombe    LA    18-Nov-11            4                            WI    7            NY    3                    Panama City Beach    1            19-Feb-10    1
    Polk County Bank    Johnston    IA    18-Nov-11                                        NJ    6            OH    2                    Cocoa Beach    1            5-Mar-10    1
    Community Bank of Rockmart    Rockmart    GA    10-Nov-11                                        MD    6            OK    1                    Boca Raton    1            12-Mar-10    1
    SunFirst Bank    Saint George    UT    4-Nov-11                                        OR    6            OR    3                    Bonifay    1            26-Mar-10    1
    Mid City Bank, Inc.    Omaha    NE    4-Nov-11                                         AL    6            PA    2                    Jupiter    1            16-Apr-10    3
    All American Bank    Des Plaines    IL    28-Oct-11                                        NY    5            PR    3                    Crawfordville    1            7-May-10    1
    Community Banks of Colorado    Greenwood Village    CO    21-Oct-11            C. Use filtering toview bank closings in CA, FL, TX and NY for 2009 through 2012. What are the total bank closing in each state between 2009 and 2012?                            VA    4            SC    4                    Lantana    1            28-May-10    3
    Community Capital Bank    Jonesboro    GA    21-Oct-11                                        OK    4            TX    1                    Belleview    1            25-Jun-10    1
    Decatur First Bank    Decatur    GA    21-Oct-11            Closing Date    (Multiple Items)                        NC    4            UT    3                    Bartow    1            16-Jul-10    3
    Old Harbor Bank    Clearwater    FL    21-Oct-11                                        TN    3            VA    1                    Brooksville    1            23-Jul-10    1
    Country Bank    Aledo    IL    14-Oct-11            Row Labels    Count of State                        NM    3            WA    11                    Carrabelle    1            30-Jul-10    2
    First State Bank    Cranford    NJ    14-Oct-11            CA    33                        IN    3            WI    2                    Palm Beach    1            20-Aug-10    2
    Blue Ridge Savings Bank, Inc.    Asheville    NC    14-Oct-11            FL    58                        AR    3            Grand Total    157                    Crestview    1            10-Sep-10    1
    Piedmont Community Bank    Gray    GA    14-Oct-11            NY    4                        LA    3                                    Palatka    1            24-Sep-10    1
    Sun Security Bank    Ellington    MO    7-Oct-11            TX    7                        NE    3                                    Englewood    1            1-Oct-10    1
    The RiverBank    Wyoming    MN    7-Oct-11            Grand Total    102                        PR    3                                    Panama City    1            22-Oct-10    2
    First International Bank    Plano    TX    30-Sep-11                                        IA    2                                    Fort Lauderdale    1            19-Nov-10    1
    Citizens Bank of Northern California    Nevada City    CA    23-Sep-11            D. Which city in FL hd the greatest number of closings between 2009 and 2012? How many?                            MS    2                                    Ponte Vedra Beach    1            17-Dec-10    1
    Bank of the Commonwealth    Norfolk    VA    23-Sep-11            Naples    4                        WV    1                                    Port St. Lucie    1            7-Jan-11    1
    First National Bank of Florida    Milton    FL    9-Sep-11            E. Create Pivotchart to display a column chart that shows the number of closings in each year from 2000 to 2012 in FL. Discuss what this chary suggests.                            MA    1                                    Port Saint Joe    1            11-Feb-11    1
    CreekSide Bank    Woodstock    GA    2-Sep-11                                        SD    1                                    Aventura    1            29-Apr-11    2
    Patriot Bank of Georgia    Cumming    GA    2-Sep-11                                        ID    1                                    Winter Park    1            6-May-11    1
    First Choice Bank    Geneva    IL    19-Aug-11                                        CT    1                                    Fort Myers    1            17-Jun-11    1
    First Southern National...