Management Information Systems NAME: ______

Professor Chen Due Date: ______

Bowls Stores Case

Bowls Department Stores

Bowls Department Stores, with corporate headquarters in Portland, Oregon, operates department stores in midsize towns in selected northwestern areas. Although the organization maintains a large computer system for its accounting operations, the sales department often downloads data to complete additional analysis of its operations. Daniel Partner, analyst for corporate sales department, regularly downloads data by territories and product areas including automotive, electronics, garden centers, and sporting goods. He often presents reports based on his analysis of sales by product areas and territory, best and worst performing product group-periods, and total sales for certain regions and product groups. He asks you to help him compile and summarize the data.

Data Files needed: Bowls.xlsx.

Complete the following:

  1. Open the Bowls workbook located in the Data Files folder, then save the workbook as Bowls_Stores in your folder.
  2. In the Documentation sheet, enter the date and your name and an appropriate purpose statement.
  3. Make a copy of the SalesData worksheet and rename the original worksheet as OriginalData and the copied worksheet as SalesData. (Hint: Press Ctrl and drag the sheet tab to make a copy of the worksheet.)
  4. In the SalesData worksheet, create an Excel table. (Hint: To create an Excel table, select all the data provided, including the headers, then click on the Table button, under the Tables group in the Insert tab.) Rename the table as ProductSales. Format the Sales column in the Currency number format with no decimal places. (Hint: Renaming the table here refers to the Table Name in the Properties group under the Table Tools Design tab.)
  5. Make a copy of the SalesData worksheet and rename the copied worksheet as Q4, for (“Question 4”). (Hint: Press Ctrl and drag the sheet tab to make a copy of the worksheet.) Sort the table in ascending order by Territories, then by Product Group, then by Year, and then by Month. Month should be sorted in Jan, Feb, Mar … order, not alphabetically. (Hint: Use the Sort button in the Sort and Filter group under the Data tab to do this multi-level sort. To sort the month as requested, in the Order column, choose custom list from the drop-down menu and then choose the Jan, Feb, Mar … option.)
  6. Make a copy of the SalesData worksheet and rename the copied worksheet asQ5 Auto Electronic.Display records for Automotive and Electronics in 2010, excluding sales in Vancouver. (Hint: To display specific records, click on the corresponding column header arrow and make sure only the required criteria are marked.) Sort this data by Sales in descending order. (Hint: You can also find this option by clicking on the column header arrow and choosing “Sort Largest to Smallest”.) Add a Total row and calculate the average sales for the filtered data. (Hint: To add the Total row, go to the Formulas tab, then use AutoSum button in the Function Library group. Click on the arrow appearing on the right hand side of the cell and choose Average.) Change the label in the Total row to Average. Split the worksheet into two panes. The top pane displays all the rows but the last row on your screen. The bottom pane displays the Total row. (Hint: To Split your worksheet, highlight the Total row and click the Split button in the Window group on the View tab.)
  7. Make a copy of the OriginalData worksheet and rename the copied worksheet as Q6, for (“Question 6”). Display subtotals for Sales (Sum) by Year, Month (Jan. Feb, Mar …), and Territory. (Hint: To display subtotals, sort columns first by Year, Month and Territories using the Sort button, in the Sort and Filter group under the Data tab for this multilevel sort. Then click on any cell in the Sales column, find Subtotal function in Outline group under Data tab. Apply the subtotals for each group as specified (Year, Month and Territory) while making sure the “Replace current subtotals” option is unchecked.)
  8. Make a copy of the SalesData worksheet and rename the copied worksheet as Q7 Lowest Periods. Display the five lowest periods based on sales. Assume each row represents a period. Sort the Sales so lowest sales appear first. (Hint: To display the five lowest Sales figures, click on the arrow on the Sales column header, go to Number filters in the drop-down menu and then choose Top 10. When the “Top 10 AutoFilter” prompt opens, change Top to Bottom and the number 10 to 5.)
  9. Create a PivotTable similar to the one shown in Figure 1a, displaying percentage of sales by Product Group, Territories and Year. (Hint: To create a PivotTable, find the PivotTable button in the Tables group under the Insert tab. Include Product Group and Territories under Row Labels, Year under Column Labels and Sum of Sales Under Values. To get the Percentage of Sales, click on Sum of Sales in the Values box, under PivotTable Field List, and choose the “Value Fields Settings” Option. Change Custom Name to “Pct of Sales” and click on the Show values as tab. From the drop-down menu choose “% of Column”.) Omit the columnar grand totals. (Hint: To remove the GrandTotal column, right-click on the GrandTotal heading and choose “Remove GrandTotal”.) Use a tabular layout, inserting subtotals at the bottom of each Product Group and excluding Sporting product group. (Hint:To use a tabular layout, click on the first item in the Row Labels, Product Group in this case, and choose the “Field Setting” option. Choose the “Layout and Print” tab then click on the option “Show item labels in tabular form”.) Figure 1b, shows how the Pivot Table Field List should look like. Rename the worksheet as Q8 Percent of Sales.

Figure 1a Figure 1b

  1. Using Figure 2 as a guide, create a PivotChart of total Sales By Product Group. Create a second PivotChart of Total Sales by Territory in the same worksheet. Include only the PivotCharts in the worksheet. Rename the worksheet as Q9 PivotCharts. (Hint: To create a PivotChart, find the PivotChart button under the PivotTable button in the Tables group under the Insert tab.)

Figure 2

  1. Using Figure 3 as a guide, create a PivotTable to show four calculations: minimum, maximum, average, and Total Sales categorized by Territories and Product Group and filtered by Year. (Hint: To create a PivotTable, find the PivotTable button in the Tables group under the Insert tab. Include Product Group and Territories under Row Labels, Year under Report Filter and four instances of Sum of Sales Under Values. To change the Sum to Average, Minimum or Maximum, click on Sum of Sales in the Values box, under PivotTable Field List, and choose the “Value Fields Settings” Option. Change Summarize Field by from Sum to desired function.) Display the results for 2010. Rename the worksheet as Q10 Statistical Summary.

Figure 3

  1. Make sure your worksheets should be placed in the order of “Documentation”, “SalesData”, Q4, Q5, … up to Q10 (or you might lose points)
  2. Save and close the workbook. Submit the finished workbook to your instructor.

What you should turn in:

  1. Create your EXCEL solution file save it as Your_Last_Firstname_Tornado.xlsx). (e.g., Chen_Jason_Tornado.xlsx). You should add class and personal information (name, row number etc.)in the Documentation sheet.
  2. Write a short report and save it as Your_Last_Firstname_Tornado.docx (e.g., Chen_Jason_Tornado.docx) describing what you learn from the case and what you can apply the experience to what business application in the near future. You should add class and personal information (name, row number etc.) within the Word documents.
  3. What/How you should turn in: email me the two files (*.xlsx and *.docx) with the following information (Fail to provide me with ALL information, you will losemajor points)

a) To:

b) Subject: mbus626 (AIE) EXCEL-Bowls Stores

c) Message:

Dr. Chen,

Attached please find two files (*.xlsx and *.docx) for my Excel#1.

<Your Full Name> at the end of the message line

so that I can record/grade your homework.

Bowls Stores, Page-1