Excel 2010

Read all questions and instructions carefully.

Retail Trade Analysis

Benson & Benson Retail, Inc., is a large successful retail and grocery chain based the North East region. It is formulating a 7-year expansion plan. It is interested in diversifying its business to related retail industries by acquisition or expanding its geographical presence by opening new chains in other states where there is no presence of David. You have been requested to conduct a market research for the expansion plan. Specifically, you need to identify which retail industry is promising in which state or region in the United States and will present your study to your supervisor and the CEO. You have downloaded the retail trade census data in 2007 from U.S. Census Bureau (http://www.census.gov/retail/) and are about to analyze it.

1. (Function 10pt) Take the sales data from Maryland. Delete all the other records. Using SUM and AVERAGE function, calculate (i) the total sales in all industries and (ii) the average per capita sales in Maryland.

2. (Subtotal 10pt) Using Subtotal, create a worksheet that total sales in each industry and shows the grand total of all states at the bottom. This worksheet should be sorted by industry name first.

3. (PivotTable 10pt) Build a PivotTable that shows the sum of sales by region and industry. Make sure that all sales figures are displayed in a currency format. Highlight by blue which industry has the largest sales volume among states in the Mountain region.

4. (Chart 10pt) Take the sales data from North Carolina. Delete all other records. Create a column chart that shows per capita sales of each industry. Industry names should appear on the horizontal axis. Add proper chart title and descriptions of the horizontal and vertical axes.

5. (10pt) One new analysis of any kind that you can think of. It should be different from the above analyses. State the business question that you’re trying to answer.

Submission Instruction

· For each question, create a separate worksheet and re-name each by Q1, Q2, and so on.

· Late submission is allowed, but there will be 10% penalty per day of delayed submission.