Reference no: EM132864479

Explain Access Database Basics.

Create Pivot Tables for data analysis.

Compare and analyze database tools.

Instructions
Your bakery business has grown tremendously and you have several franchises. For this assignment, you will be analyzing the weekly sales data for one of the bakeries located in Lisle, Illinois.

You are tasked with determining the impact of what rotating management personnelwould have on sales. For this assignment, you will:
• Import sales data from an Access database file into Excel.
• Create a PivotTable report to summarize the quarterly sales by franchise and by manager.

Steps:
1. Open Excel and save a new workbook asBakery Sales Your Initials.xlsx.
2. Locate the BSales.accdb Access database file and save it into the same folder as your Excel spreadsheet.
3. Import the information BSales.accdb as table (not PivotTable) into your Bakery Sales Your Initials.xlsx file.
4. Name this sheet "Sales".
5. Create a PivotTable report using the data in the Bakery Sales worksheet to a new worksheet within the same workbook. (See screenshot.)
6. Go to Insert - PivotTable.
7. Choose Date and Sales from the list of fields.
8. Name the newly created sheet"Analysis".
9. On the PivotTable report, format sales data as currency.
10. Sort the sales from highest to lowest.
11. After adding the Date field to the PivotTable report, group records in this field into Quarters. To do this, right-click on one date in the list and choose Group - Quarter.
12. Add the Bakery Field to the PivotTable report. (Click on the Row Labels to enable access to the fields if it is not already visible.)
a. Which quarter was the most profitable?
b. Which bakery earned the highest quarterly sales?
13. Add the Manager field to the PivotTable report and remove the Bakery field.
a. Which manager earned the highest quarterly sales?
b. Which quarter was the most profitable for that manager?
14. Go to the Sales sheet and sort the sales column from largest to highest to determine the highest selling manager. (Remember to select all data.)
15. Using the dropdown arrow next to the column heading "Manager" filter out all other managers.
16. Using the information from Level 3 of Chapter 10, create a simple macro to highlight this manager's name in yellow, bold the text, and change the font color to red.
a. HINT: Perform an Internet search for a video using the keywords: "Creating Simple Excel Macro Excel 2013 Beginners Tutorial".
b. Save the macro as CTRL+shift+M in the current workbook.
c. Use the macro to continue to highlight and bold
17. Unfilter the data by clicking on the dropdown arrow next to the column heading "Manager". Choose "Clear filter from Manager".
18. Save and close the Bakery Sales Your Initials.xlsx workbook.

Attachment:- Database Basics.rar