Problem 1: Create Excel graphs using the "Student Mix in 6 W&M Classes" data with 6 data series (C231, C365,...,B735) with the characteristics below:

1) Data Table included

2) Title: "Mix in 6 W&M Classes"

3) Category "All" should appear as the last Horizontal Axis Label and should appear only once

4) Label Y-axis "No. of Students"

a) Create a Column 2-D graph.

b) Create a 3-D Stacked Column, but remove the following categories--All (both), In-state, Out-state, and International.

Problem 2:  Create the following Minitab and Excel (both) graphs using the adjacent data series: Actual Sales X21 Regulator/Forecasted Sales X21/National Plumbing Wholesale of Similar X21 Category data:

a) A line graph comparing all yearly Actual Sales X21 Regulator series over a 13 period horizon.

b) A line graph comparing all yearly National Plumbing Wholesale of similar X21 Category series over a 13 period horizon.

c) A line graph for the 3 series -- Actual Sales X21 Regulator, Forecasted Sales X21, and National Plumbing Wholesale of Similar X21 Category - for the entire 6 year horizon.  (Caution with the Y-axis scale for these graphs, since there may be a magnitude difference in the data!)

d) Calculate summary/descriptive statistics for Actual Sales X21 Regulator y2007. Use the Data Analysis Tools package in Excel and the Graphical Summary Tool in the Descriptive Statistics section of Minitab.

Problem 3: The Transportation Security Administration (TSA) has assembled a random list of 40 ft containers passing through 5 US East Coast container ports--Baltimore (Bal), New York/New Jersey (NY-NJ), Charleston (Chlsn),  Jacksonville (Jxsvl). They are identified by weight in Metric tons (Tonnes), shipper (NYK, EG, M, CSCL), and delivery port.  Each observation represents manifest information of a vessel call designated for further examined by a forensic group at TSA.

Perform a multi-level sort of the list (not including the "OBS" column) by "Shipper" (A-Z), "Port" (A-Z), and "# of 40 ft Cntrs" (Smallest-Largest).  Complete the sort in the order provided.

Caution: Save a copy of the original data.  After a sort in a) you will need to return to the original data.

a) What is the Tonnes value for the new 8th OBS (remember to not include OBS in the sort)?

b) Return to the original unsorted data.  Create a Custom Sort for shippers that sorts in the following Custom order--NYK, EG, CSCL, and M.  What is the 50th OBS value for Tonnes? (hint: Create a "custom list" under the "order" tab in "custom sort" tool.  Also, remember to not include OBS in the sort)

c) Which of the following formulas will allow you to accurately count the simultaneous occurrence of records with VA in the Port column and M in the Shipper column of the original Port data? Assume the Port Data is located in the upper left-hand corner of a worksheet (OBS is in cell A1):

W)  =COUNTIFS(D2:D79,"M", E2:E79,"VA")         

X)   =COUNTIF(E2:E79,"VA")+COUNTIF(D2:D79, "M")

Y)   both W) and X)

Z)   none of the above

Problem 4: Filter the Port Data by 3 keys in the order given--

1st......# of 40 ft Cntrs. greater than 30,000

2nd.....Port equal to VA and Jxsvl

3rd......Shipper equal to NYK and M

a) How many observations make up this filtered group?

b) Create a SUBTOTAL formula to calculate the filtered SUM of Tonnes. Assume the OBS in the table is located in cell A1.

Problem 5:

a) Create a Frequency Distribution column graph for the # of 40 ft Cntrs. data in the table.  Use the Frequency(data_array,bins_array) function in Excel to construct the column graph and use bins that contain 5000 unit intervals, starting with 5,001-10,000 and ending with 55,001-60,000. Finally, make the values on the x-axis match the exact bin values (5,001-10,000, 10,001-15,000,....,55,001-60,000) so there is no doubt of the values contained in each bin.

b) Which of the following statement(s) is (are) false about the frequency distribution graph?

1) This frequency distribution graph is Unimodal

2) This frequency distribution graph is not symmetric

3) This frequency distribution graph is skewed left

4) This frequency distribution graph is skewed right

5) This frequency distribution has a median value less than its mean value

Problem 6: This data represents the $ value of inventory for a particular Stock Keeping Unit (SKU)....SKU 143.  Perform a Pareto analysis on the SKU 143 data. 

a) Construct a single Pareto Chart with the sorted values of SKUs as the primary axis and the Cumulative Relative Frequency of the sorted values on the same graph and on a secondary axis.  The SKUs should use a column chart format and the Cumulative Frequency should use a line graph. (Make sure that your chart accommodates the difference in magnitudes of these two series.)

b) How many of the sorted observations (starting largest to smallest) result in approximately 50% of the cumulative relative frequency?

Problem 7: The bank data shown in the table is from a promotion conducted by a bank during the month of September.  Each record represents a transaction of: an Existing or New customer; an opening of an account type (Savings, CD, etc.); in a particular $Amount; at a particular branch (Central, Westside, etc.); the account transaction was performed by a Teller or a New Accts agent.  Data is also collected on the customers age and the day of the week the account was opened.

a) What is the 71st percentile of the $Amount using the percentile function (use the Excel ".INC" version)?

b) What is the rank of the 71st percentile of the $Amount using the common formula (in our text) for calculating the rank of the Kth percentile?

c) What is the 71st percentile of the $Amount using the Rank and Percentile Data Analysis tool in Excel?

Problem 8: Your Import/Export business operates out of 5 international port cities.  You have asked an employee to construct a Cross-Tabulation of 3 major expenses vs. the port in which they are incurred. 

a) What percent of Antofagasta's total expenses are a result of Trucking?

b) What percent of Grand Total of expenses is Paradip Rail?

c) What port has the highest expense % for Admin relative to Grand Total expense?

Problem 9: The bank data shown in the table is from a promotion conducted by a bank during the month of September.  Each record represents a transaction by: an Existing or New customer; opening of an account type (Savings, CD, etc.); in a particular $amount; at a particular branch (Central, Westside, etc.); the account transaction performed by either a Teller or a New Accounts agent.  Data is also is collected on the customers age and the day of the week the account was opened.

Create a Pivot table for the value of $Amount that cross-tabulates by AcctType and Branch.

a) What is the Average value of Savings accounts opened at the Westside Branch?

b) Create the PivotChart for 1).

c) What % of the Sum grand total represent CDs at the Central Branch ?

d) What is the Sum of $Amount in CDs at the Central location by-- Existing Customers that are 65 and older and transacted on all Mondays of the month? (Hint: this will require 3 filters...Existing, etc.)

e) For the transactions that constitute the Sum of $Amount for IRAs at the Westside Branch, what was the age of the oldest individual?

Problem 10: For the data series provided and in the order they are provided (Series 1 as x-axis/Series2 as y-axis):

a) Create a scatter diagram of Series1 and Series2 using Minitab.

b) Using the Minitab's Basic Statistics Correlation function, which of the following is True:

X) the data is very slightly positively correlated

Y) there is absolutely no correlation between the series2 and Series2

Z) none of the above

c) Add a linear trendline to the Scatter Diagram in a).

Attachment:- Assignment WorkBook.rar