Analyzing sales data is essential for any business looking to make informed decisions and optimize its operations. In this project, we will utilize Microsoft Excel and Power Query to conduct a comprehensive analysis of Superstore sales data. Our primary objectives will be to establish meaningful connections between various data sheets, ensure data quality, and calculate critical metrics such as the Cost of Goods Sold (COGS) and discount values. Below are the key steps and elements of this analysis:

1- Data Import and Transformation:

• Gather and import relevant sales data from various sources into Excel.
• Utilize Power Query to clean, transform, and structure the data for analysis.
• Merge and link different data sheets to create a cohesive dataset, ensuring that all data fields are connected logically.

2- Data Quality Assessment:

• Perform data quality checks to identify and address issues like missing values, duplicates, outliers, and data inconsistencies.
• Standardize data formats and ensure that all data is in a consistent, usable state.

3- Calculating COGS:

• Determine the Cost of Goods Sold (COGS) for each product sold by considering factors like purchase price, shipping costs, and any additional expenses.
• Apply appropriate formulas and calculations to determine COGS accurately.

4- Discount Analysis:

• Analyze the discount values offered on products to understand their impact on sales and profitability.
• Calculate the average discount percentage, identify trends, and visualize the data using charts or graphs.

5- Sales Metrics:

• Calculate and analyze various sales metrics, such as total revenue, profit margins, and sales growth.
• Utilize Excel functions to compute these metrics and create visuals for better insights.

6- Visualization:

• Create visualizations, such as charts, graphs, and pivot tables, to present the data in an understandable and actionable format.
• Visual representations can help identify trends, outliers, and patterns in the data.

7- Report Generation:

• Compile the findings and insights into a well-structured report or dashboard, making it easy for stakeholders to understand and make informed decisions.

Throughout this analysis, the goal is to provide a clear and comprehensive understanding of the Superstore's sales performance. By using Excel and Power Query, we can efficiently manage and analyze the data, ensuring that the insights gained contribute to the store's growth and success.

Replication materials for the manuscript "Skepticism in Science and Punitive Attitudes", published in the Journal of Criminal Justice.Note that the GSS repeated cross sections for 1972 to 2018 are too large to upload here, but they can be accessed from https://gss.norc.org/content/dam/gss/get-the-data/documents/spss/GSS_spss.zipIncluded here are:(A link to the repeated cross-sections data)Each of the 3 wave panels (2006-2010; 2008-2012; 2010-2014)Replication R script for the repeated cross sections cleaning and analysisReplication R script for the panel data cleaning and analysisAn excel spreadsheet with Uniform Crime Report data to merge to the cross sections.

The SMARTDEST DATASET WP3 v1.0 includes data at sub-city level for 7 cities: Amsterdam, Barcelona, Edinburgh, Lisbon, Ljubljana, Turin, and Venice. It is made up of information extracted from public sources at the local level (mostly, city council open data portals) or volunteered geographic information, that is, geospatial content generated by non-professionals using mapping systems available on the Internet (e.g., Geofabrik). Details on data sources and variables are included in a ‘metadata’ spreadsheet in the excel file. The same excel file contains 5 additional spreadsheets. The first one, labelled #1, was used to perform the analysis on the determinants of the geographical spread of tourism supply in SMARTDEST case study’s cities (in the main document D3.3, section 4.1), The second one (labelled #2) offers information that would allow to replicate the analysis on tourism-led population decline reported in section 4.3. As for spreadsheets named #3-AMS, #4-BCN, and #5-EDI, they refer to data sources and variables used to run follow-up analyses discussed in section 5.1, with the objective of digging into the causes of depopulation in Amsterdam, Barcelona, and Edinburgh, respectively. The column ‘row’ can be used to merge the excel file with the shapefile ‘db_task3.3_SmartDest’. Data are available at the buurt level in Amsterdam (an administrative unit roughly corresponding to a neighbourhood), census tract level in Barcelona and Ljubljana, for data zones in Edinburgh, statistical zones in Turin, and località in Venice.

Here you can download the data underlying our household application as Pdf, Excel, CSV, XML document. You may use the data, e.g. reproduce, distribute, process and merge with other data, including for commercial purposes.

The legally binding document is always the official document on the federal budget. Therefore, if you use data from this application, please always point out these documents to your users.

For any questions about this data please email me at jacob@crimedatatool.com. If you use this data, please cite it.Version 3 release notes:Adds data in the following formats: Excel.Changes project name to avoid confusing this data for the ones done by NACJD.Version 2 release notes:Adds data for 2017.Adds a "number_of_months_reported" variable which says how many months of the year the agency reported data.Property Stolen and Recovered is a Uniform Crime Reporting (UCR) Program data set with information on the number of offenses (crimes included are murder, rape, robbery, burglary, theft/larceny, and motor vehicle theft), the value of the offense, and subcategories of the offense (e.g. for robbery it is broken down into subcategories including highway robbery, bank robbery, gas station robbery). The majority of the data relates to theft. Theft is divided into subcategories of theft such as shoplifting, theft of bicycle, theft from building, and purse snatching. For a number of items stolen (e.g. money, jewelry and previous metals, guns), the value of property stolen and and the value for property recovered is provided. This data set is also referred to as the Supplement to Return A (Offenses Known and Reported). All the data was received directly from the FBI as text or .DTA files. I created a setup file based on the documentation provided by the FBI and read the data into R using the package asciiSetupReader. All work to clean the data and save it in various file formats was also done in R. For the R code used to clean this data, see here: https://github.com/jacobkap/crime_data. The Word document file available for download is the guidebook the FBI provided with the raw data which I used to create the setup file to read in data.There may be inaccuracies in the data, particularly in the group of columns starting with "auto." To reduce (but certainly not eliminate) data errors, I replaced the following values with NA for the group of columns beginning with "offenses" or "auto" as they are common data entry error values (e.g. are larger than the agency's population, are much larger than other crimes or months in same agency): 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000, 100000, 99942. This cleaning was NOT done on the columns starting with "value."For every numeric column I replaced negative indicator values (e.g. "j" for -1) with the negative number they are supposed to be. These negative number indicators are not included in the FBI's codebook for this data but are present in the data. I used the values in the FBI's codebook for the Offenses Known and Clearances by Arrest data.To make it easier to merge with other data, I merged this data with the Law Enforcement Agency Identifiers Crosswalk (LEAIC) data. The data from the LEAIC add FIPS (state, county, and place) and agency type/subtype. If an agency has used a different FIPS code in the past, check to make sure the FIPS code is the same as in this data.

This document explain how data were generated and how to interpret them.

LICENSE: CC0 But if you want to combine data with other datasets, feel free to use them as if they were published under CC0 license.
Data were published in February 2017. At that time, Zenodo only provided CC BY, CC BY-SA, CC BY-NC, CC BY-ND and CC BY-NC-ND. No CC0 option was available.

HOW DATA WERE COLLECTED The 21 recorded sessions took place between February 2013 and December 2016.
Data were collected using Turning Technologies' remote controls (called clickers) and TurningPoint software.

The 4 versions of the quiz used during these 4 years are provided in the 'quizzes' folder for information purpose (in PDF and Powerpoint formats).

Turning Technologies records data in a closed format (.tpzx) that can be exported and converted them into 3 formats provided here (these 3 files contain the same data):

• Excel (.xslx)
• Comma-spearated values (.csv)
• SQLite (.sqlite)

The first one was directly exported from TurningPoint and is provided for Excel users who can't read CSV correctly.
CSV was converted from Excel and is provided for non-Excel users.
Finally, SQLite is provided in order to apply different sorting and filters to the data. It can be read using SQLite manager for Firefox (https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/).

CODEBOOK Here is the name, the meaning and the possible values of the columns (name - meaning [possible values]). If students didn't answer the question, the value is '-'.

Session - session number (chronological) [1 to 21] AcademicYear - academic year [12-13, 13-14, 14-15, 15-16, 16-17] Year - calendar year [2013, 2014, 2015, 2016] Month - month (number) [1 to 12] Day - day (number) [1 to 31] Section - section abbreviation [CH, ESC, GM, IF, SIE, SV] Level - students' level [BA2, BA3, MA] Language - course's language [FR or EN] DeviceID - clicker's ID [(unique ID within a session)] Q1 - answers to question 1 [A, B, C, D, E] Q2 - answers to question 2 [A, B, C, D] Q3 - answers to question 3 [A or B] Q4 - answers to question 4 [A or B] Q5 - answers to question 5 [A or B] Q6 - answers to question 6 [A or B] Q7 - answers to question 7 [A or B] Q8 - answers to question 8 [A or B] Q9 - answers to question 9 [A or B] Q8-9 - answers to the question 8-9 (merge) [A or B] Q10 - answers to question 10 [1, 2] Q11 - answers to question 11 [A or B] Q12 - answers to question 12 [A, B]

Section abbreviation meaning * CH: chemistry * ESC: school of criminal justice (Unil) * GM: mechanical engineering * IF: financial engineering * SIE: environmental engineering * SV: life sciences

Level meaning
* BA2: 2nd year of Bachelor * BA3: 3rd year of Bachelor * MA: Master level

Question types For some questions, multiple answers were allowed: Q1, Q2, Q10 & Q12.
Half of the questions have only one correct answer, true or false: Q3, Q5, Q6, Q7, Q8, Q9 & Q8-9.
Finally, for 2 questions only one answer was accepted, but there is not only one correct answer: Q4 & Q11.

INFORMATION ABOUT THE SESSIONS Except otherwise stated below, all sessions were conducted like the original one: Q1 to Q12 (no Q8-9). The original French version of the quiz has been translated into English for a few sessions with Master students. For sessions 14 and 20, Q5 was removed and Q8 & Q9 were merged in Q8-9.
Session 18 was a short one with only 7 sevens questions: Q1, Q2, Q3, Q4, Q6, Q7 & Q9.

CONTACT INFORMATION If you have any question about these data, contact formations.bib@epfl.ch.

For any questions about this data please email me at jacob@crimedatatool.com. If you use this data, please cite it.Version 4 release notes:Adds data for 2018Version 3 release notes:Adds data in the following formats: Excel.Changes project name to avoid confusing this data for the ones done by NACJD.Version 2 release notes:Adds data for 2017.Adds a "number_of_months_reported" variable which says how many months of the year the agency reported data.Property Stolen and Recovered is a Uniform Crime Reporting (UCR) Program data set with information on the number of offenses (crimes included are murder, rape, robbery, burglary, theft/larceny, and motor vehicle theft), the value of the offense, and subcategories of the offense (e.g. for robbery it is broken down into subcategories including highway robbery, bank robbery, gas station robbery). The majority of the data relates to theft. Theft is divided into subcategories of theft such as shoplifting, theft of bicycle, theft from building, and purse snatching. For a number of items stolen (e.g. money, jewelry and previous metals, guns), the value of property stolen and and the value for property recovered is provided. This data set is also referred to as the Supplement to Return A (Offenses Known and Reported). All the data was received directly from the FBI as text or .DTA files. I created a setup file based on the documentation provided by the FBI and read the data into R using the package asciiSetupReader. All work to clean the data and save it in various file formats was also done in R. For the R code used to clean this data, see here: https://github.com/jacobkap/crime_data. The Word document file available for download is the guidebook the FBI provided with the raw data which I used to create the setup file to read in data.There may be inaccuracies in the data, particularly in the group of columns starting with "auto." To reduce (but certainly not eliminate) data errors, I replaced the following values with NA for the group of columns beginning with "offenses" or "auto" as they are common data entry error values (e.g. are larger than the agency's population, are much larger than other crimes or months in same agency): 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000, 100000, 99942. This cleaning was NOT done on the columns starting with "value."For every numeric column I replaced negative indicator values (e.g. "j" for -1) with the negative number they are supposed to be. These negative number indicators are not included in the FBI's codebook for this data but are present in the data. I used the values in the FBI's codebook for the Offenses Known and Clearances by Arrest data.To make it easier to merge with other data, I merged this data with the Law Enforcement Agency Identifiers Crosswalk (LEAIC) data. The data from the LEAIC add FIPS (state, county, and place) and agency type/subtype. If an agency has used a different FIPS code in the past, check to make sure the FIPS code is the same as in this data.

About Cyclistic

Cyclistic is a bike-share program that features more than 5,800 bicycles and 600 docking stations. They offer making bike-share more inclusive to people with disabilities and riders who can’t use a standard two-wheeled bike. In 2016, Cyclistic launched a successful bike-share offering. Since then, the program has grown to a fleet of 5,824 bicycles that are geotracked and locked into a network of 692 stations across Chicago. The bikes can be unlocked from one station and returned to any other station in the system anytime.

Problem Statement

The target/aim of marketing team is to convert casual riders into annual riders. In order to convert the causal riders into annual members need to understand the behavior of the users that how the annual members are using this service differently than causal riders. Need to understand how often this service is being used by annual members and casual riders.

Solution

For the analysis of this project, we picked/chose Excel with the mutual consent of our team to show our work. To help with our analysis we started with Ask, then we prepare our data according to what client was asking to provide then we process the data to make it clean, organize and easy to accessible and at the end we analyze that data to get the results.

As per the requirement of our client, they wanted to increase the number of their annual members. To increase their annual members, they wanted to know How do annual members and casual riders use Cyclistic bike differently?

After having company’s requirement now, it was the time to Prepare and Process the data. For this analysis we been told to use only previous 12 months of Cyclistic trip data. The data has been made available online by Motivational International Inc. we checked the integrity and credibility of data by making sure that online source is safe and secure through which the data is available to use.

While preparing the data, we started with downloading the files on our machine. We saved the files and unzip them. Then we created the subfolders for the .csv and the .xls sheets. Before further analysis we cleaned the data. We used Filter option on our required columns to see if there are any NULLS or any data that it supposed to be not here.

While cleaning the data in some of the monthly files we found that start_at and end_at columns had the custom format of mm: ss.0. For consistency with all other spreadsheets we changed the custom format to m/d/yy h:mm. We also found that some spreadsheets had the data from other months but after further analysis we figured it out that the ride was starting in that month and ending in the next month so that data supposed to belong from that worksheet.

After cleaning the data, we created 2 new columns in each worksheet to perform our calculations. To perform our calculations, we made 2 new columns and named them: a) ride_length
b) day_of _week

To create ride_length column we used Subtraction Formula by choosing stareted_at and ended_at columns. That gave us the ride length of each ride for everyday of the month. To create day_of_week we used WEEKDAY command. After cleaning the data on monthly basis, it was the time to merge all 12 months into a single spreadsheet. After merging the whole data into a new sheet, it was time to Analyze! Before analyzing our team made sure one more time that the data is properly organize, formatted and there is no error or bug in our data to get the correct results. We made sure on more time that all the Formatting are correct.To analyze the data we ran few calculations to get a better sense of the data layout that we were using. We calculated: a) mean of ride_length b) max of ride_length c) mode of day_of_week

To find out mean of ride_length, we used Average Formula, to get an estimate/ overview of how long rides usually last. By doing Max calculation we found out the longest ride length. Last but not the least mode function we calculate the most frequent day of the week when riders were using that service.

To Support the requirement/ question that been asked by our client to identify the trends and relationship we made a Pivot Table in Excel so that we can show/ present our work/ insights/ results in an easy way to the client. By using Pivot Table its clearer to see the trend that annual members are using this service more than the casual riders and it’s also giving the good picture of the relation that how often annual members are using this service. By using the Pivot Table, we analyzed that total number of rides for annual members are more than the causal riders. On the basis of our analysis, we found out that the average length of ride is more for casual riders than the annual members, it means that casual members are riding for longer period of time than the annual members. But annual members are using more often than casual ri...

The sixteen Ground Water Basins of the Reunion Island are merged into a separate shape due to the location in the southern hemisphere resulting in a deviating map projection. All GWBs in Reunion are assigned to horizon 1.

The dbf tables of the shape files include the columns “EU_CD_GW” as the GWB identifier and “Horizon” describing the vertical positioning. The polygon identifier “Polygon_ID” was added subsequently, because some GWBs consist of several polygons with identical “EU_CD_GW”even in the same horizon. Some further GWB characteristics are provided with the Microsoft Excel file “GWB_attributes_2012June.xls” including the column “EU_CD_GW”, which serves as a key for joining spatial and attribute data. There is no corresponding spatial data for GWBs in the Microsoft Excel table without an entry in column “EU_CD_GW”.

The spatial resolution is given for about a half of the GWBs in the column “Scale” of the xls file, which is varying between the MS from 1:10,000 to 1:1,000,000 and mostly in the range from 1:50,000 to 1:250,000.

A Groundwater Body (GWB) under the Water Framework Directive (WFD) Art. 2 is defined as a distinct volume of groundwater within an aquifer or aquifers, whereas an aquifer is defined as a geological layer with significant groundwater flow. This definition of a GWB allows a wide scope of interpretations. EU Member States (MS) are under obligation to report the GWBs including the results of the GWB survey periodically according to the schedule of the WFD. Reportnet is used for the submission of GWB data to the EEA by MS and includes spatial data as GIS polygons and GWB characteristics in an XML schema.

The WISE provisional reference GIS WFD Dataset on GWBs combines spatial data consisting of several shape files and certain GWB attributes in a single table submitted by the MS according to Art. 13. The GWBs are divided into horizons, which represent distinct vertical layers of groundwater resources. All GWBs assigned to a certain horizon from one to five are merged into one shape file. GWBs assigned to horizons six or seven are combined in a single further shape file. Another two shape files comprise the GWBs of Reunion Island in the southern hemisphere and the GWBs from Switzerland as a non EU MS, all of which assigned to horizon 1.

The dbf tables of the shape files include the columns “EU_CD_GW” as the GWB identifier and “Horizon” describing the vertical positioning. The polygon identifier “Polygon_ID” was added subsequently, because some GWBs consist of several polygons with identical “EU_CD_GW”even in the same horizon. Some further GWB characteristics are provided with the Microsoft Excel file “GWB_attributes_2012June.xls” including the column “EU_CD_GW”, which serves as a key for joining spatial and attribute data. There is no corresponding spatial data for GWBs in the Microsoft Excel table without an entry in column “EU_CD_GW”. The spatial resolution is given for about a half of the GWBs in the column “Scale” of the xls file, which is varying between the MS from 1 : 10,000 to 1 : 1,000,000 and mostly in the range from 1 : 50,000 to 1 : 250,000. The processing of some of the GWB shape files by GIS routines as clip or intersect in combination with a test polygon resulted in errors. Therefore a correction of erroneous topological features causing routine failures was carried out. However, the GWB layer includes a multitude of in parts very tiny, distinct areas resulting in a highly detailed or fragmented pattern. In certain parts topological inconsistencies appear quite frequently and delineation methodologies are currently varying between the MS in terms of size and three dimensional positioning of GWBs. This version of the dataset has to be considered as a first step towards a consistent GWB picture throughout Europe, but it is not yet of a sufficient quality to support spatial analyses i.e. it is not a fully developed reference GIS dataset. Therefore, the layer is published as a preliminary version and use of this data is subject to certain restrictions outlined in the explanatory notes.

It should be underlined that the methodology used is still under discussion (Working Group C -Groundwater) and is not fully harmonised throughout the EU MS.

For the external publication the whole United Kingdom has to be removed due to licensing restrictions.

This data is sourced from the Census 2011 and shows the population and population density by council area. Raw data sourced from http://www.scotlandscensus.gov.uk/en/censusresults/downloadablefiles.html and then manipulated in excel to merge a number of tables. The resulting data was joined to a shapefile of Scottish Council areas from sharegeo (http://www.sharegeo.ac.uk/handle/10672/305). Both sources should be attributed as the sources of the base data. GIS vector data. This dataset was first accessioned in the EDINA ShareGeo Open repository on 2012-12-19 and migrated to Edinburgh DataShare on 2017-02-21.

Excel spreadsheet containing, in separate sheets, underlying numerical data used to generate the indicated figure panels.