case study with excel

Table of Contents

Data Analysis Using Excel Case Study

Data analysis is an essential skill in today’s business world. As organizations deal with increasing amounts of data, it becomes crucial for professionals to make sense of this information and derive useful insights. Excel is a powerful and versatile tool that can assist in analyzing and presenting data effectively, particularly through the use of case studies.

A case study is a detailed examination of a specific situation or problem in order to better understand the complexities involved. By using Excel for data analysis, individuals can explore and analyze the data related to the case study in a comprehensive and structured manner. Excel offers various tools and functionalities, such as PivotTables, slicers, and data visualization features, which allow users to assess patterns, trends, and relationships within the data.

Applying these techniques for data analysis in Excel case studies enables professionals to make well-informed business decisions and communicate their findings effectively. By leveraging the capabilities of Excel in conjunction with case studies, individuals can unlock valuable insights that drive organizational success and contribute to an enhanced understanding of the overall data landscape.

Excel Basics for Data Analysis

Dataset preparation.

When working with Excel, the first step in data analysis is dataset preparation . This process involves setting up the data in a structured format, with clearly defined headers and cells. To start, you must import or enter your data into an Excel spreadsheet, ensuring that each record is represented by a row and each variable by a column. Headers should be placed in the top row and provide descriptive labels for each column. Proper organization of your dataset helps to ensure accurate analysis and interpretation .

For example, suppose you have a dataset that contains the following information:

In this dataset, the headers are “Year,” “Category,” “Sales,” and “Profit.” Each row represents a record, and the cells contain the corresponding data.

Data Cleaning

The next step in data analysis using Excel is data cleaning . Data cleaning is the process of identifying and correcting errors, inconsistencies, and inaccuracies in your dataset. Common data cleaning tasks include:

• Removing duplicate records,
• Filling in missing values,
• Correcting data entry errors,
• Standardizing and formatting variable names and values.

To perform data cleaning in Excel, you can use various functions and tools:

• Remove duplicates: To remove duplicate records, select your dataset and navigate to the Data tab. Click the “Remove Duplicates” button and select the columns to be used for identifying duplicate rows.
• Fill in missing values: Use Excel functions such as VLOOKUP , HLOOKUP , and INDEX-MATCH to fill in missing values based on other data in your dataset. You can also use the IFERROR function to handle errors when looking up values.
• Correct data entry errors: Use Excel’s “Find and Replace” tool (Ctrl + F) to search for and correct errors in your dataset. You may need to perform this multiple times for different errors.
• Standardize and format variable names and values: Use Excel functions such as UPPER , LOWER , PROPER , and TRIM to standardize text data. Format numerical values using the Number Format options in the Home tab.

By ensuring your dataset is clean and well-organized, you can confidently move forward with more advanced data analysis tasks in Excel.

Powerful Excel Functions

Excel is a versatile tool when it comes to data analysis. There are many powerful functions that can help you perform complex calculations and analysis easily. In this section, we will explore some of the top functions in three categories: Text Functions, Date Functions, and Lookup Functions.

Text Functions

Text Functions are crucial when working with large sets of data containing text. These functions help in cleaning, extracting, and modifying text data. Some key text functions include:

• LEFT : Extracts a specified number of characters from the beginning of a text string.
• RIGHT : Extracts a specified number of characters from the end of a text string.
• MID : Extracts a specified number of characters from a text string, starting at a specified position.
• TRIM : Removes extra spaces from text, leaving a single space between words and no space at the beginning or end of the text.
• CONCATENATE : Joins multiple text strings into one single string.
• FIND : Locates the position of a specific character or text string within another text string.

Date Functions

Date Functions are essential for dealing with dates and times in data analysis. These functions help in calculating the difference between dates, extracting parts of a date, and performing various date-related calculations. Some notable date functions include:

• TODAY : Returns the current date.
• NOW : Returns the current date and time.
• DATEDIF : Calculates the difference between two dates in days, months, or years.
• DATE : Creates a date by combining individual day, month, and year values.
• WEEKDAY : Returns the day of the week corresponding to a specific date, as an integer between 1 (Sunday) and 7 (Saturday).
• EOMONTH : Returns the last day of the month for a given date.

Lookup Functions

Lookup Functions are powerful tools used to search and retrieve data from a specific range or table in Excel. These functions can save time and effort when working with large datasets. Some essential lookup functions include:

• VLOOKUP : Searches for a specific value in the first column of a range and returns a corresponding value from a specified column.
• HLOOKUP : Searches for a specific value in the first row of a range and returns a corresponding value from a specified row.
• INDEX : Returns a value from a specific cell within a range, using row and column numbers.
• MATCH : Searches for a specific value in a range and returns its relative position within that range.
• XLOOKUP : Performs a lookup by searching for a specific value in a range or table and returning a corresponding value from another column or row (available only in Excel 365 and Excel 2019).

These powerful Excel functions can help make the process of data analysis more efficient and accurate. In combination with appropriate formatting, tables, and other visual aids, these functions can greatly enhance your ability to process and understand large datasets.

Related Article: Excel Functions for Data Analysts.

Data Exploration and Visualization

In the process of data analysis using Excel, data exploration and visualization play essential roles in revealing patterns, trends, and relationships within the data. This section will cover two primary techniques for data visualization in Excel: Charts and Trends, and Pivot Tables and Pivot Charts.

Charts and Trends

Charts in Excel are a highly effective method of uncovering patterns and relationships within the dataset. There are various types of charts available in Excel that cater to different use cases, such as bar charts, line charts, and scatter plots. These chart types can be customized to suit the needs of the analysis and to emphasize specific trends or patterns.

Trends in the data can be identified with the help of charts, and Excel offers trend lines functionalities to visualize these trends more clearly. By applying a trend line, one can easily identify the overall direction (positive or negative) of the dataset and make predictions based on this information. Additionally, Excel offers built-in formatting options that can help emphasize certain data points or highlight particular trends for easier interpretation.

Pivot Tables and Pivot Charts

Pivot Tables are another powerful data analysis feature in Excel. They allow the user to summarize, reorganize, and filter data by dragging and dropping columns into different areas. This enables the user to analyze data across multiple dimensions, revealing hidden insights and patterns.

To complement Pivot Tables, Excel also offers Pivot Charts, which allow users to create dynamic visualizations derived from the Pivot Table data. Pivot Charts offer the same chart types as regular Excel charts but with the added capability to update the chart when the Pivot Table data is altered. This makes Pivot Charts ideal for creating interactive and easily updatable visualizations.

Overall, incorporating these techniques into the data analysis process can enhance understanding and unveil valuable insights from the dataset. When using Excel for data analysis, data exploration and visualization with Charts and Trends, as well as Pivot Tables and Pivot Charts, can provide a comprehensive and insightful overview of the data in question.

Case Study: Covid-19 Data Analysis

Data collection and cleaning.

The Covid-19 pandemic has generated vast amounts of data, requiring researchers and analysts to collect, clean, and organize data sets to gain valuable insights. Several sources, such as the World Health Organization and Johns Hopkins University , provide updated information on confirmed cases, recoveries, and deaths.

Data collection starts with gathering raw data from various sources. These data sets may have inconsistencies, missing values, or discrepancies, which need to be addressed to ensure accurate analysis. Data cleaning is a critical step in this process, involving tasks such as removing duplicates, filling in missing values, and correcting errors.

Exploratory Data Analysis

Once the data is clean and organized, exploratory data analysis (EDA) can be conducted using tools like Excel. EDA helps analysts understand the data, identify patterns, and generate hypotheses for further investigation.

Some useful techniques in conducting EDA in Excel include:

• Pivot Tables : These allow users to summarize and reorganize data quickly, providing aggregated views of the data.
• Charts and Graphs : Visual representations of data, such as bar charts or line graphs, can display trends, correlations, or patterns more clearly than raw numbers.
• Descriptive Statistics : Excel’s built-in functions allow easy calculation of measures such as mean, median, and standard deviation, providing a preliminary statistical analysis of the data.

In the context of Covid-19 data, EDA can help reveal important information about the pandemic’s progression. For example, analysts can:

• Compare infection rates across countries or regions
• Monitor changes in case numbers over time
• Evaluate the effectiveness of public health interventions and policies

The insights gained from exploratory data analysis can guide further research, inform decision-making, and contribute to a better understanding of the pandemic’s impact on public health.

Case Study: Stock Market Data Analysis

Data collection and preparation.

The first step in the stock market data analysis case study is collecting and preparing the data. This process involves gathering historical stock prices, trading volumes, and other relevant financial metrics from reliable sources. The data can be cleaned and organized in Excel, removing any errors or inconsistencies. It’s essential to verify the collected data’s accuracy to ensure the analysis’s validity.

After preparing the financial data, the next step is to compute essential measures and ratios. These may include:

• Price-to-Earnings (P/E) Ratio
• Dividends Yield
• Total Return
• Moving Averages

Calculating these ratios and measures provides a general overview of a company’s performance in the stock market, which can be further analyzed with Excel tools.

Profit and Loss Analysis

In this stage of the case study, profit and loss analysis is conducted to assess the stock’s performance. Using Excel PivotTables, we can summarize the data to identify trends or patterns in the stock market. For instance, we can analyze the historical profits and losses of multiple stocks during a specific state or market condition.

Analyzing profit and loss data can also be done with natural language capabilities in Excel. This feature allows us to ask questions about the dataset, and Excel will produce relevant results. For example, we could pose a question like “Which stocks had the highest profit margins in the last quarter?” or “What is the average loss for the technology sector?”

After exploring the profits and losses of the stocks, we can gain insights into which stocks or sectors are more profitable or risky. This information can help potential investors make informed decisions about their investment strategies. Additionally, the insights from the case study can serve as a reference point for future stock market analyses.

Remember, this case study only serves as an example of how to conduct stock market data analysis using Excel. By adapting and expanding on these techniques, one can harness the power of Excel to explore various aspects of financial markets and derive valuable insights.

Case Study: San Diego Burrito Ratings

Data gathering and cleaning.

The main objective of this case study is to evaluate and analyze the various factors that contribute to the ratings of San Diego burritos. The data used in this analysis is collected from different sources, which include customer reviews and ratings from Yelp, along with other relevant information about burrito sales and geographical distribution. The raw data is then compiled and cleaned to ensure that it is consistent and free from any discrepancies or errors. This process involves standardizing the fields and records, as well as filtering out any irrelevant information. The cleaned data is then organized into a structured format, which is suitable for further analysis using Excel PivotTables and Charts.

Use of Pivot Tables and Charts

After cleaning and organizing the data, Excel PivotTables are utilized to analyze the regional distribution of San Diego burrito ratings. By categorizing the data based on regions, such as East and West, it becomes convenient to identify the ratings and sales trends across these regions. The organized data is then sorted based on the ratings and popularity of burrito establishments within specific densely populated areas.

Using Pivot Charts, a graphical representation of the data is created to provide a clear and comprehensive visual of the ratings distribution in different regions of San Diego. It becomes easier to discern patterns and trends, allowing for the development of informed conclusions on the factors influencing the popularity and success of burrito establishments.

Throughout the analysis, various parameters are investigated, which include the relationship between ratings and sales, the potential impact of particular fields on popularity, and the apparent differences between densely populated regions in terms of burrito preferences. By utilizing PivotTables and Charts confidently, it is possible to draw insights and conclusions that can help optimize marketing strategies, guide customer preferences, and influence the overall success of burrito establishments across San Diego.

Case Study: Shark Attack Records Analysis

Data collection and pre-processing.

In this case study, the primary focus is on the analysis of shark attack records recorded between 1900 and 2016, consisting of just under 5,300 records or observations. To begin the analysis, the data needs to be collected from a reliable source and pre-processed to ensure its accuracy and relevance.

Data pre-processing is an essential step to prepare the dataset for analysis. It involves checking for missing values, outliers, and inconsistencies in the data. Additionally, it may also require converting the data into a suitable format, such as categorizing dates or splitting location information into separate columns (latitude and longitude).

Identifying Trends and Patterns

Once the dataset has been pre-processed, it’s time to dive into the analysis using Microsoft Excel. Excel offers a fast and central way to analyze data and search for trends and patterns within shark attack records. One powerful tool for this purpose is Excel’s PivotTables, which allows users to easily aggregate and summarize data.

Some possible trends and patterns that can be identified through the analysis of shark attack records include:

• Temporal Trends: Analyzing the frequency of shark attacks over time to identify any patterns in the occurrence of attacks, such as seasonality or specific years with higher attack rates.
• Geographical Patterns: Identifying areas with a higher concentration of shark attacks, which can provide insights into hotspots and potentially dangerous locations.
• Victim Demographics: Examining the demographics of shark attack victims, such as age, gender, and activity type, to determine if certain groups are more prone to attacks.
• Species Involved: Investigating the types of shark species responsible for attacks and their relative frequency in the dataset.

By utilizing Excel’s data analysis tools and PivotTables, researchers can confidently and clearly identify trends and patterns in the shark attack records, providing valuable insights into shark behavior and risk factors associated with shark attacks. This analysis can be helpful in understanding and managing the risks associated with shark encounters for both public safety and conservation efforts.

Related Article: How to Solve Data Analysis Real World Problems.

Additional Resources and Exercises

Kaggle and data analysis courses.

Kaggle is a popular platform that offers data science competitions, datasets, and courses to help you improve your data analysis skills in Excel. The courses are designed for various skill levels, and they cover essential concepts like PivotTables and data visualization. The comprehensive exercises and practical case studies provide a real-world context for mastering data analysis techniques.

The course reviews on Kaggle are usually quite positive, with many users appreciating the knowledgeable instructors and engaging content. If you’re looking to become a data analyst or enhance your existing skills, exploring the data analysis courses on Kaggle is a great starting point.

Power Query in Excel

Power Query is a powerful data analysis tool in Excel that enables you to import, transform, and combine data from various sources. This feature is particularly useful when working with large datasets or preparing data for analysis. There are numerous resources available to learn how to use Power Query effectively.

To practice using Power Query, consider working on exercises that focus on data cleansing, data transformation, and data integration. As you progress, you will gain a deeper understanding of the various Power Query functionalities and become more confident in your data analysis abilities.

In conclusion, engaging with additional resources like Kaggle courses and Power Query exercises will help you hone your Excel data analysis skills and enable you to tackle complex case studies with ease.

Frequently Asked Questions

How can excel be used for effective case study analysis.

Excel is a versatile tool that can be utilized for effective case study analysis. By organizing and transforming data into easily digestible formats, users can better identify trends, patterns, and insights within their data sets. Excel also offers various functions and tools, such as pivot tables, data tables, and data visualization, which enable users to analyze case study data more efficiently and uncover valuable information.

Which Excel functions are most useful for data analysis in case studies?

There are numerous Excel functions that can be highly useful for data analysis in case studies. These include:

• VLOOKUP, which allows users to search for specific information in large data sets
• INDEX-MATCH, a more advanced alternative to VLOOKUP that’s capable of handling more complex data structures
• IF, which helps in making conditional statements and decisions in data analysis
• AVERAGE, MAX, MIN, and COUNT for basic data aggregation
• SUMIFS and COUNTIFS, which allow users to perform conditional aggregation based on predefined criteria

What are some examples of data analysis projects using Excel?

Many different projects can benefit from data analysis using Excel, such as financial analysis, market research, sales performance tracking, and customer behavior analysis. Businesses across industries are known to use Excel for evaluating their case studies and forming data-driven decisions based on their insights.

How can Excel pivot tables aid in analyzing case study data?

Pivot tables in Excel are powerful, enabling users to summarize and analyze large data sets quickly and efficiently. They allow users to group and filter data based on different dimensions, making it much easier to identify trends, patterns, and relationships within the data. Additionally, pivot tables provide user-friendly drag-and-drop functionalities, allowing for easy customization and requiring minimal Excel proficiency.

In which industries is Excel data analysis most commonly applied in case studies?

Excel data analysis is widely used across various industries for case studies, including:

• Finance and banking, for analyzing investment portfolios, risk management, and financial performance
• Healthcare, for patient data analysis and identifying patterns in disease occurrence
• Marketing and sales, to analyze customer data and product performance
• Retail, for inventory management and sales forecasting
• Manufacturing, to evaluate the efficiency and improve production processes

What steps should be followed for a successful data analysis process in Excel?

A successful data analysis process in Excel typically involves the following steps:

• Data collection: Gather relevant data from various sources and consolidate it in Excel.
• Data cleaning and preprocessing: Remove any errors, duplicate records, or missing values in the data, and reformat it as necessary.
• Data exploration: Familiarize with the data, identify patterns, and spot trends through descriptive analysis and visualization techniques.
• Data analysis: Use relevant functions, formulas, and tools such as pivot tables to analyze the data and extract valuable insights.
• Data visualization: Create charts, graphs, or dashboard reports to effectively visualize the findings for improved understanding and decision-making.

What you should know:

• Our Mission is to Help you to Become a Professional Data Analyst.
• This Website is a Home for Data Analysts.  Get our latest in-depth Data Analysis and Artificial Intelligence Lessons and Updates in your Inbox.

Tech Writer | Data Analyst | Digital Creator

Get Our Professional Data Analyst Roadmap for free

You may also like

Fundamentals of Data Analysis in Excel – Case Study

• Construct data using a variety of functions and formulas
• Present data with a mixture of formatting and structures
• Visualize data with Excel Charts

• What You'll Learn
• Career Programs
• What Students Say

Data Analysis in Excel Case Study Overview

Dive into the world of Excel data analysis with this engaging case study, featuring seven unique challenges. Each challenge, growing progressively more complex, offers a practical opportunity to sharpen your Excel skills through real-world problem-solving scenarios. You’ll be provided with different datasets for each challenge, where your task is to manipulate, analyze, or visually represent the data using Excel’s diverse set of tools. These challenges are not just about mastering Excel functions; they’re designed to apply these skills to solve actual problems you might encounter professionally.

Data Analysis in Excel Case Study Learning Objectives

By the end of the practice lab, you should be able to:

• Transform data with conditional formulas, Lookup functions, and SUMPRODUCT
• Analyze data to highlight insights with conditional formatting, Excel Tables, and Dynamic Arrays
• Visualize data effectively by creating and formatting Excel Charts

Who Should Take This Case Study?

This Excel Case Study is perfect for those who want to put their knowledge about Data Analysis in Excel into practice with selected, real-life scenarios. This makes the case study a great follow-up to BIDA’s Fundamentals of Data Analysis in Excel.

Prerequisite

Ideally some basic Excel skills including formulas and basic pivot tables.

Software Requirements

Fundamentals of data analysis in excel - case study.

Approx 3h to complete

100% online and self-paced

What you'll learn

Case study introduction, looking up data, conditional formulas, conditional formatting, excel tables, dynamic arrays, visualizing data, case study wrap-up, qualified assessment, this course is part of the following programs.

Why stop here? Expand your skills and show your expertise with the professional certifications, specializations, and CPE credits you’re already on your way to earning.

Business Intelligence & Data Analyst (BIDA®) Certification

• Skills Learned Data visualization, data warehousing and transformation, data modeling and analysis
• Career Prep Business intelligence analyst, data scientist, data visualization specialist

Data Analysis in Excel Specialization

• Skills Learned Data Modelling & Analysis, Data Transformation, Data Visualization
• Career Prep Data Analyst, Business Intelligence Specialist, Data Scientist, Finance Analyst

Business Intelligence Analyst Specialization

• Skills learned Data Transformation & Automation, Data Visualization, Coding, Data Modeling
• Career prep Data Analyst, Business Intelligence Specialist, Finance Analyst, Data Scientist

What Our Members Say

Farooq anwer, alamir alshafaey, lydia angolo endjala, soliana berhe, kristoffer lim, christopher omondi, tinashe hemish katanda, kenneth kiplangat bett, jeandre pike, wegderes nigusie agonafir, muhammad jawad latif, maphindela hleta, elijah mwelu, mutlu hazar, sayed ali salem, antowan batts, jina ratsoma, frequently asked questions.

Create a free account to unlock this Template

Access and download collection of free Templates to help power your productivity and performance.

Already have an account? Log in

Supercharge your skills with Premium Templates

Take your learning and productivity to the next level with our Premium Templates.

Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.

Already have a Self-Study or Full-Immersion membership? Log in

Access Exclusive Templates

Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.

Already have a Full-Immersion membership? Log in

• Contact AIChE
• Communities
• Learning & Careers
• Publications
• Careers at AIChE
• Equity, Diversity, Inclusion
• Young Professionals
• Operating councils
• Local Sections

Other Sites & Tools

Technical groups, follow aiche.

Search form

Excel Tip #5: Take Advantage of Data Tables for Case Studies

• Feb 28, 2017
• Comments (0)

Topics: 

• General Interest
• Professional Development
• General Interest (Professional Development)

Share This Post:

Share your tips, on engage.

Do you have some tips of your own about spreadsheets? If you're a member, I'd love you to share them on AIChE Engage .

Once chemical engineers develop a spreadsheet calculation, however large or small in scale, they are typically interested in running case studies. Case studies can produce results for variations in input values. Engineers very often do this manually, by copying-and-pasting calculation results into an adjacent table and then generating charts to depict the relationships. However, there is a better way.

Below, we illustrate the application of Excel’s Data Table tool for a “one-way” case study. A set of input values is mapped into an input cell, and the corresponding values from a result cell are tabulated. This feature is live on the spreadsheet and is implemented with Excel’s TABLE array function.

We can use the Data Table tool to study the cash flow table (a) below. In this example, the internal rate of return (IRR) and net present value (NPV) are calculated based on net cash flows in years 0 through 5. The underlying formulas for the first several columns are shown in (b) below; the rest follow the established pattern.

To carry out a case study of IRR versus selling price, we set up a column of candidate selling prices and a pointer formula to IRR in the adjacent column, one row up from the selling prices (see below). Then, by invoking the Data Table command from the What-If Analysis drop-down list in the Data Tools group of the Data tab of the Ribbon, and identifying the Column Input cell as the Selling Price (named Sell), we can flesh out the table.

This is a live case study, so when another parameter, such as the inflation rate, is changed, the values update automatically.

The Data Table feature also allows for two-way case studies. To construct a two-way case study, place a column of values for one input parameter on the left of the table and a row of values for a second input parameter in the top row of the table. Then, place the pointer formula, or rule, in the empty cell in the upper left-hand corner of the table.

Excel’s Data Table is a convenient, efficient tool for carrying out case studies using spreadsheets as a calculation engine. Several case studies can be adjoined to a spreadsheet calculation, anticipating questions that might arise about the sensitivity of results to changes in input parameter values. Take advantage of Data Tables!

More tips and techniques

If you're just joining us, check out the entire series . And if you want a full crash course instead of just helpful tips, you should check out the AIChE Academy's " Spreadsheet Problem-Solving for Chemical Engineers ," where these tips come from, and also check out the other Excel courses available through the AIChE Academy at www.aiche.org/academy .

Want more Excel tips for chemical engineers?

If you know you want to delve even deeper than this blog series – or if our Excel tips leave you hungry for more – be sure to check out AIChE’s  virtual combo course on spreadsheet problem solving and VBA programming . It’s taught by David E. Clough, the author of this series, and combines two of AIChE’s most popular spreadsheet courses, Spreadsheet Problem-Solving for Chemical Engineers and Excel VBA Programming for Chemical Engineers.

Do you have some tips of your own about spreadsheets? If you're a member, I'd love you to share them on AIChE Engage .

This Excel spreadsheet series is drawn from an article by David Clough that appeared in AIChE's CEP Magazine .  You can find the current issue and an extensive archives of back issues at  www.aiche.org/cep .

Cyclistic Case Study Using Spreadsheets, SQL and Tableau

In this case study, I analyze historical data from a Chicago based bike-share company in order to identify trends in how their customers use bikes differently. The main tools I use are spreadsheets, SQL and Tableau. Here are the highlights:

Tableau Dashboard: Cyclistic Bikeshare in Chicago

Slides: Where Rubber Meets Road in Converting Casual Riders to Cyclistic Members

GitHub: Cyclistic Case Study Repository

A more in-depth breakdown of the case study scenario is included below, followed by my full report.

Cyclistic is a bike-share company based in Chicago with two types of customers. Customers who purchase single-ride or full-day passes are known as casual riders , while those who purchase annual memberships are known as members . Cyclistic’s financial analysts have concluded that annual members are much more profitable than casual riders. The director of marketing believes the company’s future success depends on maximizing the number of annual memberships.

The marketing analytics team wants to understand how casual riders and annual members use Cyclistic bikes differently. From these insights, the team will design a new marketing strategy to convert casual riders into annual members. The primary stakeholders for this project include Cyclistic’s director of marketing and the Cyclistic executive team. The Cyclistic marketing analytics team are secondary stakeholders.

Defining the problem

The main problem for the director of marketing and marketing analytics team is this: Design marketing strategies aimed at converting Cyclistic’s casual riders into annual members. There are three questions that will guide this future marketing program. For my scope on this project, I will anlyze the first question:

1) How do annual members and casual riders use Cyclistic bikes differently? 2) Why would casual riders buy Cyclistic annual memberships? 3) How can Cyclistic use digital media to influence casual riders to become members?

By looking at the data, we will be able to first get a broad sense of certain patterns that are occurring in the two different groups. Understanding the differences will provide more accurate customer profiles for each group. These insights will help the marketing analytics team design high quality targeted marketing for converting casual riders into members. For the Cyclistic executive team, these insights will help Cyclistic maximize the number of annual members and will fuel future growth for the company.

Business task

Analyze historical bike trip data to identify trends in how annual members and casual riders use Cyclistic bikes differently. #

Data sources

We’ll be using Cyclistic’s historical bike trip data from the last 12 months, which is publicly available here . The data is made available by Motivate International Inc. under this license . The data is stored in spreadsheets. There are 12 .CSV files total:

It is structured data, organized in rows (records) and columns (fields). Each record represents one trip, and each trip has a unique field that identifies it: ride_id . Each trip is anonymized and includes the following fields:

Bike station data that is made publicly available by the city of Chicago will also be used. It can be downloaded here . In terms of bias and credibility, both data sources we are using ROCCC:

Reliable and original: this is public data that contains accurate, complete and unbiased info on Cyclistic’s historical bike trips. It can be used to explore how different customer types are using Cyclistic bikes.

Comprehensive and current: these sources contain all the data needed to understand the different ways members and casual riders use Cyclistic bikes. The data is from the past 12 months. It is current and relevant to the task at hand. This is important because the usefulness of data decreases as time passes.

Cited: these sources are publicly available data provided by Cyclistic and the City of Chicago. Governmental agency data and vetted public data are typically good sources of data.

Data cleaning and manipulation

Microsoft excel: initial data cleaning and manipulation.

Our next step is making sure the data is stored appropriately and prepared for analysis. After downloading all 12 zip files and unzipping them, I housed the files in a temporary folder on my desktop. I also created subfolders for the .CSV files and the .XLS files so that I have a copy of the original data. Then, I launched Excel, opened each file, and chose to Save As an Excel Workbook file. For each .XLS file, I did the following:

• Formatted as custom DATETIME
• Format > Cells > Custom > yyyy-mm-dd h:mm:ss
• Calculated the length of each ride by subtracting the column started_at from the column ended_at (example: =D2-C2 )
• Formatted as TIME
• Format > Cells > Time > HH:MM:SS (37:30:55)
• Calculated the date of each ride started using the DATE command (example: =DATE(YEAR(C2),MONTH(C2),DAY(C2)) )
• Format > Cells > Date > YYYY-MM-DD
• Entered the month of each ride and formatted as number (example: January: =1 )
• Format > Cells > Number
• Entered the year of each ride and formatted as general
• Format > Cells > General > YYYY
• Calculated the start time of each ride using the started_at column
• Calculated the end time of each ride using the ended_at column
• Calculated the day of the week that each ride started using the WEEKDAY command (example: =WEEKDAY(C2,1) )
• Formatted as a NUMBER with no decimals
• Format > Cells > Number (no decimals) > 1,2,3,4,5,6,7
• Note: 1 = Sunday and 7 = Saturday

After making these updates, I saved each .XLS file as a new .CSV file.

BigQuery: further data cleaning and manipulation via SQL

Since these datasets are so large, it makes sense to move our analysis to a tool that is better suited for handling large datasets. I chose to use SQL via BigQuery .

In order to continue processing the data in BigQuery, I created a bucket in Google Cloud Storage to upload all 12 files. I then created a project in BigQuery and uploaded these files as datasets. I’ve provided my initial cleaning and transformation SQL queries here for reference: initial_setup_query.sql

The results from the COUNT DISTINCT query for each table are very interesting. We can see that the three summer months have the highest trip counts, followed by alternating spring and fall months before ending with winter months:

Create quarterly tables

In order to perform analysis by season, let’s combine these tables. We’ll create Q1, Q2, Q3 and Q4 tables for analysis. We’ll have two Q1 tables– one for 20221 and one for 2022 – since we have FEB/MAR data from 2021 and JAN data from 2022:

• Table 1) 2021_Q1 -> FEB(02), MAR(03)
• Table 2) 2021_Q2 -> APR(04), MAY(05), JUN(06)
• Table 3) 2021_Q3 -> JUL(07), AUG(08), SEP(09)
• Table 4) 2021_Q4 -> OCT(10), NOV(11), DEC(12)
• Table 5) 2022_Q1 -> JAN(01)

We’ll first create 2021_Q2 and then repeat for the remaining four tables:

Clean and transform day of week

Some additional data cleaning is needed on the new table. First, we’ll update the format for day_of_week from FLOAT to STRING . Then, we’ll change the values from numbers to their corresponding day names (i.e. 1 = Sunday, 7 = Saturday. We’ll start with 2021_Q1 and repeat for the remaining four tables:

Delete old tables

Now that we have our tables organized into quarters, we can delete the original monthly tables from BigQuery. We no longer need the monthly tables since the data is available in the quarter tables. Also, it costs money to store these datasets in BigQuery.

Analysis #1: Exploratory

2021_q1 - quarterly data exploration.

We’ll select a few columns from 2021_Q1 to preview in a temporary table. This will help give us an idea of potential trends and relationships to explore further:

The above query returned 278,119 rows. That is the number of recorded trips we have data for in this quarter. Let’s dive deeper into those trip totals.

Total trips

We’ll create total columns for overall, annual members and casual riders. We’ll also calculate percentages of overall total for both types:

Of the 278,118 total trips in 2021_Q1, 66% were from annual members while 34% were from casual riders.

Average ride lengths

How does average ride_length differ for these groups?

We can see that casual riders average about 23 more minutes per ride. That seems like a pretty big difference. What influence are outliers having on these averages? Let’s investigate.

Max ride lengths

We’ll look at the maximum values for ride_length to see if anything extreme is influencing the casual rider average:

As we suspected, the casual riders average ride_length was significantly impacted by at least one outlier. The longest trip duration for casual riders was 528 hours, or 22 days. Meanwhile, the longest for annual was about 26 hours.

Let’s take a look at the top 100 highest ride_length values for casual riders to confirm there is more than one outlier affecting the average:

Median ride lengths

Since there are more than a few outliers impacting the average, we’re going to use median instead of average. Median will be more accurate for our analysis:

Now we see a much closer number, with 18 minutes for casual riders and 10 minutes for annual members.

Busiest day for rides

Let’s see which day has the most rides for annual members and casual riders:

Unsurprisingly, Saturday is the most popular day for both annual members and casual riders.

Median ride length per day

Let’s look at the median ride lengths per day for both annual members and casual riders. Since Saturday is the most popular overall, do we think it will also have the highest median ride length?

Very interesting! The median ride length for casual riders on the top five days (SUN, SAT, MON, TUE, WED) is nearly double the amount for annual members on their top five days (SAT, SUN, MON, TUE, WED).

Total rides per day

Let’s look at total rides per day. We’ll create columns for overall total, annual members and casual riders:

Start stations

Next, we’ll look at the most popular start stations for trips. We’ll again include columns for overall, annual member and casual rider totals per start station:

We can begin to see some interesting patterns in the start station data. It looks like casual riders and annual members tend to favor different regions for beginning their trips. By updating the ORDER BY function to sort by casual DESC and member DESC in two separate queries, we can compare the top ten start stations for both:

Wow! There is only one start station that cracks the top ten for both lists. The Clark St & Elm St start station is ranked #1 for annual members and #10 for casual riders. The casual riders seem to favor stations near the water like Lake Shore Dr & Monroe St and Streeter Dr & Grand Ave , while annual members frequent start stations in the River North neighborhood like Dearborn St & Erie St and Kingsbury St & Kinzie St .

An initial hypothesis for casual riders could be that they tend to favor start stations near the water and close to tourist attractions because they use bikes for weekend entertainment. An initial hypothesis for annual members could be that they tend to favor start stations in downtown, retail areas because they are using bikes for their work commutes and shopping trips.

Quarterly data exploration (cont.)

Instead of walking through each quarter like we’ve done for 2021_Q1, I will instead provide links to the full SQL files. The queries used are similar to the ones above:

• analysis_2021_Q1.sql
• analysis_2021_Q2.sql
• analysis_2021_Q3.sql
• analysis_2021_Q4.sql
• analysis_2022_Q1.sql

I’ll included some high-level quarterly analysis notes in the next section.

Analysis #2: Summary

Full_year - trends, relationships and insights.

In order to analyze all twelve months together, we’ll combine the five quarterly tables into one table. The queries used to accomplish this are included here for reference. I’ve also provided the SQL file used for full year analysis: analysis_full_year.sql .

For a summary and overall visualization of my full year analysis, please visit the Tableau Public dashboard I created here: Tableau Dashboard: Cyclistic Bikeshare in Chicago . I will also highlight some of the interesting trends and relationships I discovered below.

Annual Members vs Casual Riders

Seasonal trends

Summer vs winter.

The busiest time of year for overall bike trips is Q3– July, August and September. This makes sense because these months are mainly summer time. Bike riding is better suited for warmer weather, which is also why we see a major drop-off in total rides during the winter months of Q1– January, February and March.

Annual members outnumbered casual riders in every quarter except Q3. Interestingly, the annual members nearly doubled the casual ridership in Q1 and Q4 while only slightly edging them out in Q2.

Median ride length

We can see that casual riders consistently have longer rides than annual members.

Day of week

Which days of the week have the highest number of rides for casual riders vs annual members? Let’s look at the mode for each quarter and for the full year:

Casual riders were extremely consistent, with Saturday revealing itself as their preferred day of week for each quarter and across the full year. Meanwhile, the annual members looked to favor the middle of the week for their bike use. The most popular day for them acrosss the full year was Wednesday . Let’s see how the total rides for each day stack up for both groups:

How about median ride length per day of week for both groups?

A few fascinating insights from the above chart:

U-shape pattern Sunday and Saturday are favored by both groups for longer rides, while ride duration decreases towards the middle of the week before increasing again on Friday. This results in a u-shape trend for both groups in the above chart, although it is much more dramatic for casual riders.

Range differences For annual members, difference between their longest day and their shortest day is 1 minute and 44 seconds. For casual riders, difference is 4 minute and 57 seconds. That is a 185.58% increase in difference for casual riders.

Annual members: day-to-day consistency The annual members may have shorter ride lengths when compared to casual riders, but they are extremely consistent with their bike use day-over-day.

Casual riders: weekend warriors The daily median ride length for casual riders is consistently higher than that of annual members. The range of their ride length duration varies at a greater amount than that of annual members. Sundays and Saturdays stand out as their longest ride days.

Do members and casual riders have different preferences for bike type? Are classic bikes more popular than electric bikes?

We can see that classic bikes are favored by both groups. Let’s look at the percentages of bike type use within each group:

Looking at the above, we might ask what exactly is a docked bike and why are only casual riders using them?

We can now see from the above charts that docked bikes are the culprit for the outliers affecting our ride length averages from earlier in our analysis. This is something we should discuss with our team further and address.

Start and end station use

In the Tableau Dashboard I created, which is again available here , there is a worksheet that allows the exploration of start and end station use by members, casual riders and combined overall rides. The snapshot below is from the overall view. While interacting with the dashboard, we can see that casual riders have a higher max than annual members. Annual members have a lower max, but we can see more colors represented across the member map versus the consistent coloring across the casual map. This tells us that rides by members are more distributed across stations while rides by casual riders are more top heavy in that a huge chunk are happening at the same few stations.

Stakeholder presentation and dashboard

I’ve provided links below for my dashboard and shareholder presentation, which includes the following:

• A summary of my analysis
• Supporting visualizations and key findings
• Three recommendations based on my analysis

Presentation: Where Rubber Meets Road in Converting Casual Riders to Cyclistic Members

BloomTech’s Downfall: A Long Time Coming

Coursera’s 2023 Annual Report: Big 5 Domination, Layoffs, Lawsuit, and Patents

Coursera sees headcount decrease and faces lawsuit in 2023, invests in proprietary content while relying on Big 5 partners.

• [2024] 1300+ Free SWAYAM + NPTEL Courses
• 6 Best Crystal Programming Courses for 2024
• 10 Best Pandas Courses for 2024
• 10 Best React Native Courses for 2024
• Revolutionizing Web Animation: Best Ways to Learn GSAP in 2024

600 Free Google Certifications

Most common

• data science
• web development

Popular subjects

Web Development

Graphic Design

Popular courses

Internet History, Technology, and Security

Product Management Fundamentals

Competitive Strategy

Organize and share your learning with Class Central Lists.

View our Lists Showcase

Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

Excel PivotTables: Real-World Case Studies

via LinkedIn Learning Help

Limited-Time Offer: Up to 75% Off Coursera Plus!

• Part 2: Introduction
• U.S. voters
• San Francisco salaries
• Shark attack records
• Stock market data
• Baseball team statistics
• San Diego burrito ratings
• Weather conditions
• Spartan Race Facebook posts
• Apple app ratings
• Wine tasting results
• Part 2: Conclusion

Chris Dutton

Related Courses

Microsoft excel: data analysis with excel pivot tables, excel pro tips part 1: productivity, microsoft excel: business intelligence w/ power query & dax, microsoft excel for complete beginners w/ case studies, microsoft excel pro tips: go from beginner to advanced excel, related articles, 10 best microsoft excel courses for beginners, 1000 hours of free linkedin learning courses with free certification.

Select rating

Start your review of Excel PivotTables: Real-World Case Studies

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Scenario Analysis in Excel: A Guide with 2 Sample Cases + Template

What is Scenario Manager in Excel?

Scenario manager in Excel is an element of three what-if-analysis tools in Excel, which are in-built features in excel. You can notice the effect of switching input values without altering the existing data. It works like the data table in Excel. You must input data that should change to acquire a particular outcome.

Scenario Manager in Excel lets you change or replace input values for numerous cells. You will be able see the output of different inputs or different scenarios at the same time.

How to Perform Scenario Analysis in Excel

You’re looking for a rental house. There are some available options to choose from. We can consider these options as scenarios. You have to decide which house you want before making the final decision in order to save more money.

To illustrate this, we will use the following sample dataset:

This is for House 1. We will create a scenario for House 2 and House 3.

• Go to the Data From the Forecast group, select What-If Analysis > Scenario Manager.

• In the Scenario Manager dialog box, click on Add .

• In the Edit Scenario dialog box, enter a Scenario name . We have entered, House 2 . Select Changing cells .

• Select the range of cells C5:C9 . We will change these inputs.

• Click on OK .
• In the Scenario values dialog box, we will enter the expenses of House 2. Click on Ok .

• We have added a scenario for House 2 . Do the same for House 3 .
• We have entered the following values for House 3.

• We added both scenarios. Select House 2 and click on Show to see the changes.

• The changes for House 2 will be displayed.

• If you choose House 3, it will give you the following total cost:

Create Scenario Summary:

You can also show effects side-by-side using the Scenario Summary.

• Open the Scenario Manager.

• Click on Summary .

• Select your Result cells . Our result cell is C10 because we were showing our Total values on that cell. Click on OK .

You can see the side-by-side scenario summary in a different worksheet.

Read More:  How to Use Scenario Manager in Excel

Scenario Analysis in Excel: 2 Practical Examples

Example 1 – scenario analysis of compound interests in excel.

In this section, we will use an example of the Compound interests of banks. We will create two scenarios of this example for illustration.

Compound interest means earning or paying interest on interest. It is one of those popular financial terms. When we think about compound interest, we consider it as gaining money. It increases our savings after a limited period.

The formula of Compound Interest:

This example will be used in the same dataset. But we will calculate compound interests separately.

Suppose you want to invest $10000 for ten years. You have got three options:

• Bank "X" is providing 5% interest compounded yearly.
• Bank "Y" is offering 5% interest compounded monthly.
• Bank "Z" is giving 5% interest compounded daily.

We will use the scenario manager to find which bank will give more interest.

This is the dataset for Bank “X”:

Enter the following formula to calculate the Estimated Balance:

Create a scenario analysis.

• Go to the Data From the Forecast group, select What-If Analysis > Scenario manager .
• In the Edit Scenario dialog box, enter a Scenario name . We have entered Bank “Y” . Select cell C6 in Changing cells (because only the number of compounding periods per year will vary here. Everything will be the same). Click on OK .

• In the Scenario Values dialog box, enter 12 (because Bank “Y” gives 5% compound interest monthly. So, there will be 12 compounding periods per year). click on OK .

• We have created a scenario for Bank “Y”.

• To add a scenario for Bank “Z”, click on Add.

• We have named this scenario, Bank “Z”. Select cell C6 as the changing cell.

• Enter the scenario values 365 (because Bank “Z” is offering 5% interest compounding daily. So, no. of compounding periods will be 365 days).

• To create a scenario summary report, click on Summary . Select cell C9 as the result cell.

We have successfully created a scenario analysis. You can see the estimated balance for each compound interest of the banks.

Example 2 – Preparing Budget for an Office Tour Using Scenario Manager

Your office has decided to go on an office tour. Your boss has given you the responsibility to make the budget. You have three options to choose from.

You have made the following budget for Place 1:

You have to make a budget for Place 2 and Place 3.

• Go to Data, from the Forecast group, select What-If Analysis > Scenario manager.
• The Scenario Manager dialog box will pop-up. Click on Add .
• In the Edit Scenario dialog box, enter a Scenario name . We have named it Place 2 . Select the range of cells C5:C9 in Changing cells . Click on OK .

• Enter the expenses for Place 2.

• We have added the Place 2 scenario.
• Create a Scenario for Place 3 following the above steps. Enter your expenses for Place 3.

• Click on Summary to analyze the scenarios side-by-side. Select cell C10 for showing the result.

We have successfully performed the scenario analysis of an office tour.

Read More:  How to Create a Scenario Summary Report in Excel

Things to Remember

✎ By default, the summary report uses cell references to recognize the Changing cells and the Result cells. If you make named ranges for the cells before you run the summary report, the report will have the names instead of cell references.

✎ Scenario reports do not automatically recalculate. If you modify the values of a scenario, those modifications will not show up in a current summary report but will show up if you build a new summary report.

✎ You don’t require result cells to generate a scenario summary report, but it is required for a scenario PivotTable report.

Download Practice Workbook

Further Readings

• How to Create Scenarios in Excel
• How to Create a Scenario with Changing Cells in Excel
• How to Edit Scenarios in Excel
• How to Remove Scenario Manager in Excel

<< Go Back to Excel What-If Analysis Scenario Manager | What-If Analysis in Excel  |  Learn Excel

What is ExcelDemy?

Tags: Excel What If Analysis Scenario Manager

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

Thank you for sharing Shanto! I found a problem that also appears in your article. In the Scenario Summary for section 1 example, the “Current Values” column shows data for House 3 as the result of the last operation. How to retrieve the original data for House 1?

Hello HOWARD, Thanks for asking this important question. Basically scenario summary will show the latest dataset in the current values column. As we changed the scenario by clicking OK.

Now, this is not a wonderful solution. But it may help you.

1. Copy the original dataset to a new sheet. 2. Then go to Scenario Manager 3. Now click Summary. You will see the original data in Current Values. Thank You.

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

• Excel World Championship Sample Case #1: Knowledge Is Power /

Excel World Championship Sample Case #1: Knowledge Is Power

Anybody who’s anybody has heard of the hot new sport sweeping the world: competitive Excel ! Every year, dozens of the best analysts, accountants, and mathematicians face off to see who can best model extremely complicated problems within thirty minutes. The “sport” first gained widespread attention during the Covid-19 lockdowns and has been growing in popularity ever since; next week’s championship will take place in HyperX Arena in Las Vegas and be televised in bars on the other side of the country .

If this is the first time you’re hearing about all this, take a moment to check out People Make Games’s short documentary on the topic:

Personally, I don’t think my prowess at Excel quite reaches the levels of storied champions like Diarmuid Early or Laurence Lau (yet…). However, it came to my attention that the website for the Financial Modeling World Cup offers free sample cases adapted from actual tournament matches from previous years. With just a few minor tweaks, these would work perfectly as projects for my data analytics portfolio.

In this and future installments, I will use these sample cases as a jumping off point. Since I’m not doing these in a competitive context, I’m dispensing with the thirty-minute time limit and the graded questionaire. Instead, I will take the results and visualize them for the sake of the imagined stakeholders. Along the way, I will describe my process and the challenges I faced.

Scenario: Knowledge is Power #

This sample case (entitled “Knowledge Is Power” on the download page ) tasks us with modeling future revenue for an online education company named Oxbridge Inc, which currently operates in the United States and Australia. They’ve become very successful during the pandemic, and they’re now planning to pitch to potential investors to fund their expansion.

Provided with the pricing and membership for five different programs (Dot, Line, Triangle, Square, and Star) along with projected monthly rates of membership growth and inflation, we have to model the next five years of monthly revenue starting in January 2022. This would be relatively simple if it weren’t for the following complications:

• All numbers must be reported in US dollars, so Australian revenue needs to be converted after applying its own specific inflation rate.
• Membership prices for each class need to keep pace with inflation and must rise in $5 increments.
• Prices can’t change too quickly. Oxbridge has recently instituted a policy of not changing any prices until at least three classes in the same country are closer to the next $5-increment than the current price. For example, a membership that currently costs $24.99 won’t jump to $29.99 until it’s actual inflation-adjust price is at least $27.49 and there are at least two other membership types that also qualify.

These are the assumptions on which our analysis will rely.

This case looks like it will require conditionals based on other conditions, so we have our work cut out for us. Let’s first start with the number for the USA.

First, we’ll need to plot out all the dates and their corresponding inflation rates. Each inflation rate is assumed to hold for the entire year, so we need to make sure that the rate switches over every January. From there, it’s simply a matter of adding the monthly interest for each class:

The Prices for the USA classes.

So how do we trigger the actual price increases below? This is where the SUMPRODUCT function comes in. With this formula, we can count the number of times where values in the range surpass the halfway threshold for the next $5-increment:

Using the SUMPRODUCT function to compare the inflation-adjusted price with the previous month’s prices.

Now it’s the moment of truth: we need a conditional function predicated on both the difference in prices and the number of classes that month to meet that threshold. Obviously we’re going to us the IF function, but more importantly, we’ll need to use the AND operator to fit both conditions:

Using the IF and AND functions to change prices in $5-increments.

As a side note, I found it interesting that Excel’s AND is a function, as opposed to a mere operator like in Python or R. This makes it slightly weird to read since AND doesn’t come between the two conditions.

We can then work on membership numbers and their monthly growth rate. We set up the months and growth rate much in the same way as we did for the prices above, then plug in the starting numbers and iterate the monthly formula for each class:

Projecting future membership of each class using the expected monthly growth rate.

Now it’s just a matter of multiplying each class’s monthly cost by its monthly users:

Projecting revenue by using each class’s membership and monthly cost.

Australia #

The sheet for the Australian branch follows a similar pattern as the sheet for the USA:

Determining the interest-adjusted prices for each Australian class.

The major difference is that all prices are in Australian dollars, so I need to convert these into US currency using the provided conversion rates:

Converting Australian dollars to US dollars.

Just like the inflation rate, the conversion rate is assumed to remain the same for each year. We need to double check that the number changes beginning with each January.

Visualization and Analysis #

Now it’s time to import the resulting revenue into the next sheet and add the revenue for both branches together:

We can use Excel’s charts to prepare a presentation for potential stockholders. These line charts describe the projected overall revenue as well as the revenue for each branch:

Monthly revenue for the US branch.

I’ve elected to include revenue for each class since it might help stakeholders strategize and make informed marketing decsions. Pie charts are often discouraged these days because they can sometimes be poorly designed, but in this case, I thought one might be helpful in visualizing how much each type of class contributes to revenue:

I’d say these projections are very reliable in the first couple years, but caution making any decisions based on the fourth and fifth years. We’re working with starting assumptions here, not data, so the numbers become less credible the further we move away from the starting state. For example, starting in 2024, the prices rise every month in at least four classes, ultimately defeating the intent of the company’s policy not to raise prices too frequently. It also beggars belief that the US branch will more than double it’s revenue to $225 million dollars starting in September 2025. If this were a real-life business situation, I would try to find a public or internal dataset to temper these projections or at the very least double-check the starting assumptions provided.

Conclusion #

This was a fun exercise and I’m looking forward to the next one. Some final thoughts on the process:

• At a certain point, I wondered if it would have been better to have chosen a long format over the wide format (i.e. long columns vs. long rows). In the next project, I’ll try to work with both simultaneously so I don’t paint myself into a corner.
• Excel is a powerful application, but Google Sheets rates higher in my book in terms of its usability. For example, I’ve never had problem making pivot tables in the latter, but for some reason, Excel is super fussy about it and won’t even let you begin one until every single thing is in the exact right place.
• I surprised myself with how much I missed creating charts using Python libraries like matplotlib and Seaborn. Sure, the process can be tedious at times, but at least every single element is theoretically customizable. Excel’s charts have a large array of options, but they’re split across several menus and sub-menus and sub-menus of sub-menus. If I can’t find the option I’m looking for, I can never be sure if it’s because it simply doesn’t exist or I haven’t plunged into the menus deeply enough. In short, the general release of Python for Excel can’t come soon enough!

If you’d like to download my Excel workbook and double check my work, click here ! Feel free to message me on LinkedIn if you have any questions or suggestions.

• Online Undergraduate Students
• Online Graduate Students
• Prospective Students
• First Generation
• International
• Persons With Disabilities
• Military-Connected
• Student Parents
• Accounting & Finance
• Architecture & Construction
• Arts, Media & Communications
• Business Management & Administration
• Counseling & Social Work
• Community Impact
• Education & Training
• Government & Public Administration
• Health Science
• Information Technology
• Law & Policy
• Operations & Logistics
• Create a Resume / Cover Letter
• Expand Your Network / Mentor
• Explore Your Interests / Self Assessment
• Negotiate an Offer
• Prepare for an Interview
• Prepare for Graduate School
• Search for a Job / Internship
• Linkedin Learning
• Labor Market Insights

Excel PivotTables: Real-World Case Studies

• Share This: Share Excel PivotTables: Real-World Case Studies on Facebook Share Excel PivotTables: Real-World Case Studies on LinkedIn Share Excel PivotTables: Real-World Case Studies on X

Instructor: Chris Dutton

Once you grasp the basics of Excel PivotTables, you’re ready to see how this powerful data analysis tool can add value in real-world situations. In this course— the final installment in a two-part series—instructor Chris Dutton dives into ten real-world case studies that showcase how PivotTables can be leveraged to explore and analyze data in a variety of situations. While the previous installment in this series dove into theory and the general ins and outs of PivotTables, this course helps expand your knowledge by looking at practical applications of the tool. Chris covers case studies on San Diego burrito ratings, historical shark attack records, major league baseball statistics, stock market data, and more.

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications You must be signed in to change notification settings

Excel Case Study | iNeuron

Rahul-Bhanushali/ExcelCaseStudy

Folders and files.

Repository files navigation

Excelcasestudy.

Analytics allow you to quantify the effects of making a change to your strategy, and that's invaluable to the process of improving and optimizing campaigns. The biggest benefit of utilizing proper analytics is being able to identify strengths and weaknesses. Problem statements are mentioned along with the datasets in the given excel sheets. Please download the file and answer the questions.

● Create a 3-dimensional column chart comparing sales data for men and women, but omitting BMWs

● Create a chart to compare the favorite films data for 15-25 year old only (be careful not to include any unnecessary blanks rows or columns in your selected data).

Format this chart so that it is a pie chart, with the Barbarella slice "exploded" and each segment labelled:

● Select the necessary ranges of data to create a 3-D cone chart showing the City and the Population

● Convert this data into a line chart. Make the necessary changes to ensure that it resembles the one shown below.

● Create a chart which shows the top 6 countries and their medal hauls

● Plot a graph to compare the sales of 2012 and 2013. Also show the growth on same page.

Dataset used: Google Drive links have been shared below: https://drive.google.com/drive/folders/1VrH6EhC7c_KTZuTJcsp900X3l2h1mZiV?usp=sharing

Breadcrumbs Section. Click here to navigate to respective pages.

Operations Research Using Excel

DOI link for Operations Research Using Excel

Get Citation

The field of operations research provides a scientific approach to managerial decision making. In a contemporary, hypercompetitive ever-changing business world, a manager needs quantitative and factual ways of solving problems related to optimal allocation of resources, profit/loss, maximization/minimization etc.  In this endeavor, the subject of doing research on how to manage and make operations efficient is termed as Operations Research.

The reference text provides conceptual and analytical knowledge for various operations research techniques. Readers, especially students of this subject, are skeptic in dealing with the subject because of its emphasis on mathematics. However, this book has tried to remove such doubts by focusing on the application part of OR techniques with minimal usage of mathematics. The attempt was to make students comfortable with some complicated topics of the subject. It covers important concepts including sensitivity analysis, duality theory, transportation solution method, Hungarian algorithm, program evaluation and review technique and periodic review system.

Aimed at senior undergraduate and graduate students in the fields of mechanical engineering, civil engineering, industrial engineering and production engineering, this book:

• Discusses extensive use of Microsoft Excel spreadsheets and formulas in solving operations research problems • Provides case studies and unsolved exercises at the end of each chapter • Covers industrial applications of various operations research techniques in a comprehensive manner • Discusses creating spreadsheets and using different Excel formulas in an easy-to-understand manner • Covers problem-solving procedures for techniques including linear programming, transportation model and game theory

TABLE OF CONTENTS

Chapter 1 | 18  pages, operations research, chapter 2 | 47  pages, linear programming, chapter 3 | 58  pages, chapter 4 | 54  pages, sensitivity analysis and duality theory, chapter 5 | 54  pages, network model i, chapter 6 | 48  pages, network model ii, chapter 7 | 33  pages, network model iii, chapter 8 | 48  pages, project scheduling, chapter 9 | 27  pages, game theory.

• Privacy Policy
• Terms & Conditions
• Cookie Policy
• Taylor & Francis Online
• Taylor & Francis Group
• Students/Researchers
• Librarians/Institutions

Connect with us

Registered in England & Wales No. 3099067 5 Howick Place | London | SW1P 1WG © 2024 Informa UK Limited

How Kelley Blue Book® Instant Cash Offer Helps Real Dealers Excel

We’ve written a lot this year about all the ways Kelley Blue Book Instant Cash Offer can help dealerships get access to more used cars at better prices, and simultaneously create the kind of superior customer experience that turns one-time shoppers into loyal buyers and sellers. But what does that all look like on the ground? How are real dealerships using Kelley Blue Book Instant Cash Offer to win?

In this post, we’ll hear from three successful dealerships around the country exploring the different ways they use Kelley Blue Book Instant Cash Offer to boost traffic, improve customer experience and hold on to more profit.

Team Nissan

New Hampshire

Selling or trading-in an old vehicle is a big financial decision, and customers naturally want to get the greatest value possible out of their cars. That can lead to friction if an assessment comes in under what they’re looking for — even if your price is fair, the customer is likely to keep shopping around to try and do better.

Overcoming this disconnect is one of the top reasons Ralph Fast, general manager at Team Nissan in Manchester, New Hampshire, decided to give Kelley Blue Book Instant Cash Offer a try — since Kelley Blue Book is the nation’s most trusted source for vehicle valuations 1 and the #1 most used third-party automotive site for research and online car buying, 2 Fast thought it made sense to meet customers where they were and use the same tool to generate valuations.

Not only did that decision increase the credibility of Team Nissan’s offers — customers were more likely to trust Kelley Blue Book’s impartial valuation over one that came from the dealership itself — it also boosted gross profit by simplifying customer acquisition both by generating more leads and by creating a streamlined digital retailing process that made selling as simple as possible for consumers.

“When you look at the cost of acquisition, you look at the cost of contact and then you look at the gross profit per transaction, Instant cash offer is a clear winner.” — Ralph Fast, General Manager, Team Nissan

Lindsay Automotive

As the owner of one of the country’s largest Honda dealerships, Steve Lindsay needed a reliable way to source high-quality used cars, especially as the inventory of vehicles became tighter over the last several years. Relying on auctions was proving difficult due to both lower-quality cars and the fees and logistical headaches that came with getting those cars to the lot.

Taking a closer look, Lindsay saw that the quality he was seeing from trade-ins was much higher than on the auction line, so to get the best vehicles, he decided he had to focus on attracting those customers interested in selling or trading-in their used cars. To help achieve that, he turned to Kelley Blue Book Instant Cash Offer, trusting that reliable, easy-to-use third-party valuations would make it simpler for his dealership to win the business of consumers looking to sell their used vehicles.

With Kelley Blue Book Instant Cash Offer, Lindsay has been able to reliably source high-quality used cars, even when inventory has been tight, all while avoiding the fees and logistical headaches that come with auctions. It’s saved him real money, too — vehicles sourced through Kelley Blue Book Instant Cash Offer are saving him 3.2% over other channels. 1 That translates to, on average, about $900 more in profit per vehicle than they would otherwise make with any other sourcing channel. 1 And with trusted valuations and an easy, all-digital process, his customers feel like they’re getting a great deal through a simple, streamlined sales process.

“The nice thing about KBB ICO is not only are you getting an established value, but then you’re not having the additional costs of the logistics of trying to get the vehicle here, which is not only expensive in dollars but difficult in [terms of] time to actually get the vehicle.” — Steve Lindsay, Lindsay Automotive, Columbus, OH

Germain Dealer Group

Ohio, Michigan, Florida, Arizona

With 20 locations across the country, Germain Dealer Group needs a lot of used vehicles to keep up with consumer demand. And with the tighter supply dealerships have been navigating for the last few years, getting their hands on the volume of cars they needed was getting challenging.

That’s why Used Car Director Jim Farkas turned to Kelley Blue Book, putting his trust in a proven source of new customers — and their used cars. Farkas knew that Kelley Blue Book products and tools reach nearly 8 in 10 online shoppers 1 , so integrating those tools into his dealerships’ process would create a reliable stream of new customers looking to sell. With a focus on streamlined buying, he’s been able to source more cars for better prices — in fact, he now gets 40%–50% of his used vehicles through Kelley Blue Book Instant Cash Offer — and he’s seeing big profits by trying to acquire as many cars as he can without worrying about selling new cars to every buyer.

And because Kelley Blue Book Instant Cash Offer is so easy to use for his customers, he’s seeing great results in other KPIs, like appointment show rate and closing percentage. When you’re looking at something as vital to a dealership’s health as margin, every little bit helps, and even little wins like those have translated to big bottom-line results for Farkas and his business.

“The trust factor that we have with the consumer between our Germain brand and Kelly Blue Book is the biggest thing that we can work with.” — Jim Farkas, Used Car Director, Germain Dealer Group

It’s Easy to See Why Dealers Love Kelley Blue Book Instant Cash Offer

As we’ve seen, Kelley Blue Book Instant Cash Offer is a tool you need in your dealership strategy if you want to source high-quality cars at great prices and make customers happy. With easy-to-use digital tools, Kelley Blue Book helps dealerships implement powerful hybrid approaches to acquiring used cars, giving customers access to trusted valuations and streamlining the selling process from start to finish so that every step feels like a win-win for dealers and consumers.

To learn more about how Kelley Blue Book Instant Cash Offer is helping dealerships all over the country increase their margins, even in a tough market, check out our testimonials page and hear directly from the dealers who are seeing easier acquisitions, higher margins and happier customers by working with us.

1. Q4 2023 Consumer Brand Tracker.

2. 2023 Cox Automotive Car Buyer Journey Study, Cox Automotive.

Related posts

Fixed Ops & Variable Ops – How to Proactively Create Service Revenue

It’s all about increasing your profitability by combining the power of your two departments that drive your highest revenue: fixed ops and used cars. In this podcast, you’ll hear from […]

Podcast – 5 Appraisal Best Practices to Capture More Cars and Customers

In this podcast, Micah Tindor, senior director of strategic planning for Kelley Blue Book Instant Cash Offer, outlines five best practices top-performing dealers use to improve their appraisal processes to […]

Droppin’ Knowledge Podcast at AIS NRC

Noah Lee discusses the concept of “Cradle to Cradle” automotive retailing with Jade Terreberry and Micah Tindor at Auto Intel Summit + National Remarketing Conference. Get expert insight into how […]

• Onsite training

3,000,000+ delegates

15,000+ clients

1,000+ locations

• KnowledgePass
• Log a ticket

01344203999 Available 24/7

Best CISM Books That Are Worth Your Time

Embark on your journey to CISM certification with our top 10 must-read CISM books. This blog offers practical examples, insightful case studies, and hands-on exercises. Each CISM book equips you to excel in Information Security Management. Master the essentials and begin your journey to becoming a recognised expert. Your success story begins here!

Exclusive 40% OFF

Training Outcomes Within Your Budget!

We ensure quality, budget-alignment, and timely delivery by our expert instructors.

Share this Resource

• BCS CISMP (Certificate in Information Security Management Principles)
• Chief Information Security Officer Training
• CISSP Certification
• Information Systems Security Management Training

Are you on the journey to becoming a Certified Information Security Manager (CISM)? If so, you know that having the appropriate resources at your fingertip can make a huge difference. With so many options available, how do you choose the perfect CISM Book? Fear not, and we've curated the top 10 essential books for CISM Certification, offering comprehensive insights and strategies to master Information Security Management. 

What makes these books stand out? They offer practical examples, case studies, and Practical tasks for applying your knowledge in real-world scenarios. Are you ready to elevate your career and become a recognised expert in Information Security Management? Dive into our top picks and find the perfect CISM Book to guide your success.

Table of Contents

1) Top 9 CISM Books

   a) Complete Guide to CISM Certification

   b) Information Security Management Metrics

   c) Penetration Testing 

   d)  Malware Analyst's Cookbook and DVD 

   e) Network Security Policy: A Complete Guide 

   f) CISM All-in-One Exam Guide 

   g)  Essential CISM: Exam Guide

   h) Cryptography Theory and Practice

   i) CISM Certified Information Security Manager Practice Exams 

2) Conclusion

Top 9 CISM Books 

Explore the top 10 essential books for CISM Certification preparation, offering comprehensive insights and strategies for mastering Information Security Management. 

1) Complete Guide to CISM Certification 

The "Complete Guide to CISM Certification" is an excellent resource designed to prepare individuals for the Certified Information Security Manager exam. It covers essential topics such as Information Security governance, risk management, incident management, and program development and management. 

The book provides in-depth explanations, practical examples, and case studies to help readers understand and implement CISM concepts effectively. With a focus on industry best practices and exam preparation strategies, it aims to equip candidates with the knowledge and skills necessary to succeed in achieving CISM Certification.  

2) Information Security Management Metrics 

"Information Security Management Metrics" offers a detailed exploration of metrics used in assessing and managing Information Security. It covers various metrics frameworks, measurement methodologies, and the application of metrics in evaluating security controls and risk management effectiveness. 

The book provides insights into how metrics can support decision-making and improve overall security posture. It serves as an advantageous resource for security professionals looking to implement robust measurement practices and enhance their organisation's Information Security Management strategies. 

3) Penetration Testing 

"Penetration Testing" is a comprehensive guide that explores the practice of simulating cyber-attacks on computer systems, networks, and applications. This book covers various techniques and methodologies used by penetration testers to identify vulnerabilities and assess the security posture of an organisation. 

It includes hands-on exercises, real-world scenarios, and best practices for conducting effective penetration tests. By understanding and applying these methods, security professionals can strengthen their defences, mitigate risks, and enhance the overall security resilience of their systems and networks. 

4) Malware Analyst's Cookbook and DVD 

The "Malware Analyst's Cookbook and DVD" provides a comprehensive guide for analysing and understanding malware. It includes practical recipes and techniques for dissecting malicious software, covering topics such as dynamic and static analysis, malware behavioural analysis, and memory forensics. 

The accompanying DVD offers additional tools, datasets, and examples to aid in hands-on learning and experimentation. Aimed at security professionals and analysts, this book trains readers with the skills and knowledge needed to effectively identify, analyse, and respond to malware threats in diverse computing environments. 

Unlock advanced Cyber Security management techniques with our CISM Certified Information Security Manager Course – start your professional journey today!  

5) Network Security Policy: A Complete Guide 

"Network Security Policy: A Complete Guide" provides a comprehensive overview of creating, implementing, and managing network security policies. It covers essential topics such as policy development, risk assessment, access control, encryption, and monitoring. The book offers practical guidance on drafting effective policies tailored to organisational needs and regulatory requirements. 

It also discusses best practices for maintaining and updating policies to address evolving threats and technology trends. With case studies and examples, this guide equips security experts with the knowledge and tools necessary to establish robust network security frameworks and protect against cyber threats effectively. 

6) CISM All-in-One Exam Guide 

The "CISM All-in-One Exam Guide" provides a comprehensive resource for preparing for the Certified Information Security Manager exam. It covers all key domains required for the certification: Information Security governance, risk management, Information Security program development and management, and incident management. 

The book consists of detailed explanations, practice questions, and practical examples to reinforce understanding. It serves as a valuable study aid, equipping candidates with the knowledge and confidence needed to pass the CISM exam and excel in the field of Information Security Management. 

7) Essential CISM: Exam Guide

"Essential CISM: Updated for the 15th Edition CISM Review Manual" provides a focused and updated approach to preparing for the Certified Information Security Manager exam. This book covers key topics such as Information Security governance, risk management, incident management, and program development. 

It includes practical examples, case studies, and exam preparation strategies aligned with the latest CISM Review Manual. Designed for aspiring CISM professionals, it aims to enhance understanding of critical concepts and equip readers with the knowledge needed to succeed in obtaining CISM Certification. 

8) Cryptography Theory and Practice

"Cryptography Theory and Practice" provides a thorough exploration of cryptographic principles and their practical applications. This book covers foundational concepts such as encryption, decryption, key management, and cryptographic protocols. It delves into both classical and modern cryptographic algorithms, discussing their strengths, weaknesses, and real-world implementations.  

With a focus on both theoretical comprehension and practical application, the book equips readers with the knowledge and skills needed to design secure cryptographic solutions and protect sensitive information in various digital environments. 

Elevate your Information Security skills with our BCS CISMP (Certificate In Information Security Management Principles) Course - join now!  

9) CISM Certified Information Security Manager Practice Exams 

"CISM Certified Information Security Manager Practice Exams" is a preparatory resource designed to aid individuals studying for the Certified Information Security Manager Certification. This book offers a series of practice exams that simulate the structure and complexity of the actual CISM exam.  

Each exam is structured to test knowledge across the domains covered by CISM, including Information Security governance, risk management, incident management, and program development. By using this book, candidates can assess their identified areas for improvement, meet CISM requirements , and enhance their confidence in tackling the CISM Certification exam successfully. 

Join our CISM Training today and elevate your Cyber Security management skills instantly with expert guidance!  

Conclusion  

Embarking on your CISM Certification journey is a significant step. Our top 10 CISM Books offer practical examples, case studies, and exercises, ensuring you're well-prepared. Each CISM Book equips you with the essential knowledge to become a recognised Information Security Manager. Your journey to becoming a recognised Information Security Manager starts here! 

Join our CISMP Training today and elevate your Information Security Management skills to new heights! 

Frequently Asked Questions

CISSP and CISM serve different purposes. CISSP focuses on technical aspects of Information Security, ideal for those in hands-on roles. CISM emphasises management and governance, suitable for leadership positions. The decision depends on your professional goals and responsibilities. 

According to Payscale, the annual average pay for Certified Information Security Managers  in the United Kingdom is approximately £62,587 per year. 

The Knowledge Academy takes global learning to new heights, offering over 30,000 online courses across 490+ locations in 220 countries. This expansive reach ensures accessibility and convenience for learners worldwide. 

Alongside our diverse Online Course Catalogue, encompassing 17 major categories, we go the extra mile by providing a plethora of free educational Online Resources like News updates, Blogs , videos, webinars, and interview questions. Tailoring learning experiences further, professionals can maximise value with customisable Course Bundles of TKA .  

The Knowledge Academy’s Knowledge Pass , a prepaid voucher, adds another layer of flexibility, allowing course bookings over a 12-month period. Join us on a journey where education knows no bounds. 

The Knowledge Academy offers various CISM Training , including CISM Certified Information Security Manager. These courses cater to different skill levels, providing comprehensive insights into CISM . 

Our IT Security & Data Protection Blogs cover a range of topics related to CISM Certification, offering valuable resources, best practices, and industry insights. Whether you are a beginner or looking to advance your IT Security skills, The Knowledge Academy's diverse courses and informative blogs have got you covered. 

Upcoming IT Security & Data Protection Resources Batches & Dates

Mon 15th Jul 2024

Sat 3rd Aug 2024, Sun 4th Aug 2024

Mon 12th Aug 2024

Mon 9th Sep 2024

Mon 14th Oct 2024

Sat 9th Nov 2024, Sun 10th Nov 2024

Mon 11th Nov 2024

Mon 9th Dec 2024

Mon 6th Jan 2025

Mon 3rd Feb 2025

Mon 3rd Mar 2025

Mon 14th Apr 2025

Tue 6th May 2025

Mon 2nd Jun 2025

Mon 7th Jul 2025

Mon 4th Aug 2025

Mon 1st Sep 2025

Mon 6th Oct 2025

Mon 3rd Nov 2025

Mon 1st Dec 2025

Get A Quote

WHO WILL BE FUNDING THE COURSE?

My employer

By submitting your details you agree to be contacted in order to respond to your enquiry

• Business Analysis
• Lean Six Sigma Certification

Share this course

Our biggest spring sale.

* WHO WILL BE FUNDING THE COURSE?

We cannot process your enquiry without contacting you, please tick to confirm your consent to us for contacting you about your enquiry.

By submitting your details you agree to be contacted in order to respond to your enquiry.

We may not have the course you’re looking for. If you enquire or give us a call on 01344203999 and speak to our training experts, we may still be able to help with your training requirements.

Or select from our popular topics

• ITIL® Certification
• Scrum Certification
• Change Management Certification
• Business Analysis Courses
• Microsoft Azure Certification
• Microsoft Excel Courses
• Microsoft Project
• Explore more courses

Press esc to close

Fill out your  contact details  below and our training experts will be in touch.

Fill out your   contact details   below

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

Back to Course Information

Fill out your contact details below so we can get in touch with you regarding your training requirements.

Preferred Contact Method

No preference

Back to course information

Fill out your  training details  below

Fill out your training details below so we have a better idea of what your training requirements are.

HOW MANY DELEGATES NEED TRAINING?

HOW DO YOU WANT THE COURSE DELIVERED?

Online Instructor-led

Online Self-paced

WHEN WOULD YOU LIKE TO TAKE THIS COURSE?

Next 2 - 4 months

WHAT IS YOUR REASON FOR ENQUIRING?

Looking for some information

Looking for a discount

I want to book but have questions

One of our training experts will be in touch shortly to go overy your training requirements.

Your privacy & cookies!

Like many websites we use cookies. We care about your data and experience, so to give you the best possible experience using our site, we store a very limited amount of your data. Continuing to use this site or clicking “Accept & close” means that you agree to our use of cookies. Learn more about our privacy policy and cookie policy cookie policy .

We use cookies that are essential for our site to work. Please visit our cookie policy for more information. To accept all cookies click 'Accept & close'.