Computer Science

EXAM 2 DEVELOPMENT – PART A BIA 3621 – Introduction to Business Analytics

Scenario Java Beans Coffee Shop operates 156 locations across 20 states in the U.S. Among their items for sale,

Java Beans offer 13 types of leaf-based (regular and herbal tea) and bean-based (coffee and espresso)

drinks at these stores, some of which are decaffeinated. The company has collected data from 2013-2014

on the sales of these products and has asked you to evaluate their data.

Java Beans would like two reports that are derived from the annual sales numbers from each products at

each location over the past two years. Management would like a report of annual sales at each location

and state, and a report of the best-selling decaffeinated products/locations in 2014.

Final Result of this Activity When you have completed this part of the exam development, your Excel spreadsheet should look very

similar to the following pics:

:

Instructions

Open Data File 1) From the Exam 2 Development – Part a section of D2L (where you found this document), download

a copy of Exam 2 – Table Data.xlsx to the desktop of the computer you are using.

2) Open the file. It consists of two separate tabs, one for each part of this exam development.

Create a Report from the Data 1) Navigate to the first of the two tabs in the data file.

2) Create a report that shows annual totals for stores.

3) The report should be organized by region (alphabetically A-Z), state (alphabetically A-Z), store (ascending), and year (earliest to latest), with products listed in descending order by revenue within

year.

4) Totals for unit sales and revenue should be shown for each year within a store, for the store, and for

the overall state.

5) Successfully collapsing the outline results in the following three results:

Create a Table from the Data 1) Navigate to the second of the two tabs in the data file.

2) Create a table using the data in the file. 3) Calculate, label, and format the average sales price for each line in the data.

4) The table should show decaffeinated products that grossed more than $50,000 in revenue during

2014 for stores in the Northeast region.

5) Products should be shown in descending revenue order. 6) Change the table style to one that uses orange shaded headers (with white font) and banded rows.

7) At the bottom of the report, management would like some basic statistics including the mean of the

average sales prices for the stores in the list, the total for revenue and units sales, and the number

of store/products shown on the list. Add labels in this row as necessary to explain these results.

8) Freeze the panes on the spreadsheet so that column A and rows 1:7 are always visible.