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:
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
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.