pleas read the instractions carefully.

EXAM 2 DEVELOPMENT - PART D 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 to evaluate the sales that are occurring in the different market sizes in the regions of

the country. The company is interested in where locations are positioned and where there might be growth

opportunities.

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

same as the following pic if the slicer setting is the same.

Instructions

Obtain Data Files 1) This development work will require data from two Access databases that are available in the Exam

2 Development - Part d section of D2L (where you found this document). Download copies of the

Java Beans Sales.accdb and Dates.accdb database files and place them somewhere accessible on

your computer.

2) Open a new spreadsheet file in Excel. Format the spreadsheet and create appropriate titling as

indicated in the pic. The logo for Java Beans is also available in the Exam 2 Development - Part d

section of D2L.

Create the Table 1) Insert a pivot table based on all of the tables in the Java Beans Sales.accdb Access database file.

Add the table from the Dates.accdb database file and blend it with the tables from the Java Beans

Sales.accdb file. Position the pivot table in B11.

2) Populate the pivot table to show unit sales by market size within region and state. Format the

numbers and column widths in the pivot table appropriately. Set the pivot table so that the columns

do not resize when the pivot table changes.

3) Format the pivot table using Pivot Style Medium 18. Adjust the view of the pivot table so that only

the headers shown in the pic are present and +/- buttons are not visible.

4) Shade the columns representing the market size by successively lighter shades of blue. Make note of the shade choices so these can be replicated on the chart. Make sure that the entire column is

shaded regardless of whether it is collapsed or expanded.

5) Name the table “UnitSalesByMarket”.

6) Insert a slicer based on year, format the slicer with Slicer Style Light 3, give it 2 columns, size it, and position it appropriately above the Pivot Table.

Create the Chart 7) Insert a stacked-bar chart based on the data in the pivot table.

8) Position the chart to right of the pivot table and aligned near the top of the spreadsheet. 9) Provide proper formatting, titling, legend, axis formatting, and axis titling. Shade the market size

categories consistent with the columns you shaded in the pivot table.

10) Make sure that the results shown are sorted from highest total unit sales to lowest by region and by state.

11) Name the chart “UnitSalesByMarket”.