excel based case study

How to Perform Case Study Using Excel Data Analysis

In the latest Excel 365, performing any case study for business or research, it’s quite easier and smarter. It includes more exclusive features which give our required case studies a better way. I hope, you will learn the easiest way to perform a case study using Excel data analysis with some clear illustrations.

What Is Data Analysis in Excel?

The Analyze Data tool in Excel 365 is a powerful tool to manage our data by writing our queries directly in a search box. So we’ll be able to ask anything about our data without using any complex commands or formulas. Along with that, we can get very enriched and vivid visual illustrations or patterns of our data. In the earlier versions of Excel, the tool is named Data Analysis .

How Data Analysis Helps in Excel

It can search according to the queries through our natural language.
Provides high-level graphical illustrations and patterns.
Easy to get the Pivot Tables and Pivot Charts.
Faster steps save time.
The interest of Fields can be changed easily.

How to Use Excel Data Analysis for Case Study

Now let’s see how to apply the Analyze Data tool to perform a data analysis case study. But first, get introduced to our dataset that represents some categories’ yearly sales and profits of a company.

Read More: How to Install Data Analysis in Excel

With Default Analyze Data Option

First, we’ll see the default analyses that Excel makes automatically. Excel shows the most commonly used analyzes by default.

Click any data from the dataset.
Next, click as follows: Home > Analyze Data .

How to Use Excel Data Analysis for Case Study

Soon after, you will get an Analyze Data field on the right side of your Excel window. Where you will see different kinds of cases like- Pivot Tables and Pivot Charts .

Look, there is a sample Pivot Table of Sales and Profit by Category. Click on Insert Pivot Table .

Now see, the Pivot Table is inserted in a new sheet.

Click on Insert Pivot Chart from the Sales by Category section then you will get the Pivot Chart in a new sheet.

Here’s the chart.

Scroll down more and Excel will show you more possible Pivot Tables and Charts .

Feel free to use them if you prefer.

Read More: How to Analyze Data in Excel Using Pivot Tables

Analyze by Inserting Queries

Here, we’ll learn how to analyze data by inserting queries in the ‘ Ask a question about your data ’ box.

When you will click on the question box, it will show some default questions. Click one of them and it will show the answer according to the question. See, I clicked Total ‘Sales’ of ‘Accessories’ over ‘Year’ .

It’s the answer from Excel.

Or you can write your question. I asked- Profit chart according to Year .
After that, hit the ENTER button .

Now you see, it’s showing the chart of profit by year. Click on Insert PivotChart .

Soon after, a new sheet will open up with the PivotChart .

Also, there is a Setting icon in the Discover insights part, click it and a dialog box will open up to select the customized fields of interest.

Mark your desired fields from here. I marked Category and Profit.
Finally, just click Update .

Now it is showing the answers only about Category and Profit.

Things to Remember

The Analyze Data tool is only available in the latest Excel 365. But in the earlier versions, it is named Data Analysis ToolPak and available as Add-ins by default.

Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.

I hope the procedures described above will be good enough to perform a case study using Excel Data Analysis. Feel free to ask any question in the comment section and please give me feedback.

How to Use Data Analysis Toolpak in Excel
How to Enter Data for Analysis in Excel
How to Make Histogram Using Analysis ToolPak
[Fixed!] Data Analysis Not Showing in Excel

<< Go Back to Data Analysis with Excel | Learn Excel

What is ExcelDemy?

Tags: Data Analysis with Excel

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

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

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 .

Join 307,012+ Monthly Readers

Get Free and Instant Access To The Banker Blueprint : 57 Pages Of Career Boosting Advice Already Downloaded By 115,341+ Industry Peers.

Break Into Investment Banking
Write A Resume or Cover Letter
Win Investment Banking Interviews
Ace Your Investment Banking Interviews
Win Investment Banking Internships
Master Financial Modeling
Get Into Private Equity
Get A Job At A Hedge Fund
Recent Posts
Articles By Category

The 3-Statement Model: Full Tutorial for a Timed 90-Minute Modeling Test

If you're new here, please click here to get my FREE 57-page investment banking recruiting guide - plus, get weekly updates so that you can break into investment banking . Thanks for visiting!

A long time ago, we received one complaint/criticism more than any other:

“All your models start from templates ! What about case studies where I have to start from a blank Excel sheet and do not get any data, formatting, or schedules?”

We did this mostly to save time : entering the historical information and setting up the formatting, layout, etc., usually takes at least 30 minutes and sometimes up to several hours.

But there is also value in learning how to build a full model from scratch .

We’ll cover a 90-minute 3-statement modeling test here and explain how to use the company’s financials, 10-K, and investor presentation to do everything.

WARNING: You must have a decent-to-high Excel proficiency to follow along and finish this in the allotted time.

The video walkthrough below has captions for some of the Excel shortcuts, but it’s not a full Excel tutorial, and we assume you already know the basics.

What is a 3-Statement Model?

In financial modeling , the “3 statements” refer to the Income Statement, Balance Sheet, and Cash Flow Statement.

Collectively, these show you a company’s revenue, expenses, cash, debt, equity, and cash flow over time, and you can use them to determine why these items have changed.

In a 3-statement model, you input the historical versions of these statements and then project them over a ~5-year period.

In real life, you do this to value companies, model transactions, and determine whether the company’s expected growth, margins, and cash flow metrics are plausible.

For example, if the company claims it will generate $5 billion of Free Cash Flow and use it to repay $1 billion of Debt and issue $4 billion in Dividends, is that realistic?

Will the company generate more or less cash flow? Might it need outside financing? How do the numbers change if market conditions worsen?

Banks like to test this topic because it’s a quick way to assess who’s proficient in Excel, accounting, and financial modeling.

If you cannot read or interpret a company’s historical financial statements, you won’t be working on complex deals anytime soon.

Types of 3-Statement Modeling Tests

Most 3-statement models and case studies fall into one of three categories:

Blank Sheet / Strict Time Limit: These are more about working quickly, knowing the Excel shortcuts, simplifying, and making decisions under pressure.
Template / Strict Time Limit: These tests are more about entering the correct formulas, justifying your assumptions, and answering questions based on your model’s output.
No Strict Time Limit: These case studies are more about using outside research and data to justify your assumptions for the revenue, expenses, cash flow, etc. You might also have to give a presentation based on your findings.

The “strict time limit” could be anything from 30 minutes to 3-4 hours, and the complexity increases as the time limit increases.

The “no strict time limit” type might give you several days or even 1 week+.

There is still a deadline, but you don’t need to rush around like a madman to finish.

The 90-Minute 3-Statement Model from a Blank Sheet

For this tutorial, I picked an example where you start from a blank sheet and review the company’s filings and presentations .

So, you must demonstrate Excel proficiency and the ability to interpret data and make reasonable assumptions.

You can get the case study prompt, the company documents, and the completed Excel file below:

90-Minute 3-Statement Model – Case Study Prompt (PDF)
90-Minute 3-Statement Model – Completed Excel File (XL)
Overview of Main Points in 90-Minute 3-Statement Modeling Test – Slides (PDF)
Otis – 10-K (PDF)
Otis – 10-K in Excel Format – Raw (XL)
Otis – User-Friendly 10-K in Excel with Swapped Columns (XL)
Otis – Investor Presentation (PDF)

There is no “blank” or “beginning” file because we create a new sheet in Excel and enter everything from scratch in this tutorial.

You can get the video version of this entire tutorial below:

2:35: What is a 3-Statement Modeling Test?
5:54: Part 1: Inputting the Historical Financial Statements
15:31: Balance Sheet Entry
24:14: Cash Flow Statement Entry
35:11: Part 2: Income Statement Projections
50:12: Part 3: Balance Sheet Projections
57:51: Part 4: Cash Flow Statement Projections
1:07:12: Part 5: Linking the Statements
1:10:59: Part 6: Debt and Stock Repurchases
1:19:16: Part 7: Model Checks, Review, and Final Comments
1:22:35 : Recap and Summary

This example is not taken from our courses – it’s new for this article – but it is similar to some of the case studies in our Core Financial Modeling course :

Core Financial Modeling

Learn accounting, 3-statement modeling, valuation/DCF analysis, M&A and merger models, and LBOs and leveraged buyout models with 10+ global case studies.

The full course has 3-statement models with and without templates for additional practice. If you want more advanced 3-statement models with additional schedules, the Advanced Financial Modeling course might be more appropriate since it goes into far more depth in each case study:

Advanced Financial Modeling

Learn more complex "on the job" investment banking models and complete private equity, hedge fund, and credit case studies to win buy-side job offers.

3-Statement Model, Part 1: Inputting the Historical Statements

You could attempt to input the data by copying and pasting from the PDFs, but it’s far more efficient to link directly to the Excel or CSV files.

A few tips:

Swap the Excel columns, so they go from oldest to newest (see below).

Otis - Financial Statements with Columns Swapped

On the Income Statement , use positives for revenue and other income sources and negatives for all expenses and outflows, as it will be easier to check your work that way.

3-Statement Model - Correct Signs on the Income Statement

Consolidate smaller line items as much as possible; you ideally want ~5 items on each side of the Balance Sheet (maybe 10 at the most) and only a few items in each section of the Cash Flow Statement.

3-Statement Model - Consolidated Balance Sheet

As you proceed, you can check your work by summing up the sections and comparing the totals to the company’s numbers.

3-Statement Model, Part 2: Income Statement Projections

The case study document says that we need to use “something more complicated” than a simple percentage Year-Over-Year (YoY) growth rate for Revenue:

But the investor presentation and 10-K do not make it easy to find unit-by-unit data.

We’d ideally like to project new escalators and elevators sold, forecast the average prices, and assume a certain percentage of these new units go into “Service Units,” generating Services revenue in future periods.

It would also be helpful to know about something like the degree of operating leverage , so we could better forecast different expenses.

But we can’t find enough solid data to do this within the strict time limit, so we simplify and use Market Share and Market Size to project the New Equipment Revenue, with the Services Revenue based on the company’s estimates for the growth in Service Units:

Revenue Projections - Market Share and Market Size

The Cost of Products, Cost of Services, and Operating Expenses are simple percentages of Revenue, and the Taxes and NCI Net Income are based on average historical percentages:

3-Statement Model - Income Statement Projections

3-Statement Model, Part 3: Balance Sheet Projections

In this part, we focus on projecting the Working Capital line items , such as Accounts Receivable (AR), Inventory, and Accounts Payable.

With more time/information, we might also use metrics like the Days Sales Outstanding or Cash Conversion Cycle to forecast some of these items.

The key point is that the absolute numbers do not matter .

What matter is the Change in Working Capital on the Cash Flow Statement since that affects the company’s cash flow and ability to repay Debt and repurchase Stock.

If the Change in WC has been positive as the company has grown, it should stay positive and in the same range in the future (and vice versa if it has been negative or near-0).

We make most of these items simple percentages of Income Statement lines such as Revenue, COGS , or Total Expenses:

3-Statement Model - Balance Sheet Projections

Once we do this and set up the projections, we can calculate the Change in Working Capital on the Cash Flow Statement to check our work:

Working Capital Check on the Cash Flow Statement

We also simplify the Operating Leases here by making the Operating Lease Assets a percentage of Operating Expenses and assuming the Operating Lease Liabilities change by the same amount each year.

Lease accounting is more complicated in real life and under IFRS, but this approach is fine for a U.S.-based company.

3-Statement Model, Part 4: Cash Flow Statement Projections

Most of the key line items here, such as CapEx and Depreciation & Amortization, are simple percentages of Revenue:

3-Statement Model - Cash Flow Statement Projections

A few line items, such as the ones for Pension Contributions and Noncontrolling Interests , are more complex to project “correctly,” but we don’t have time to do so here.

One exception to these simple rules is the Dividends line, which we forecast based on the Dividend Payout Ratio (i.e., Dividends / Net Income) (for more, see our tutorial on the dividend yield ).

In this case, the company provides specific guidance on the Dividend Payout Ratio, so we increase it slightly over the period to match their targets (see below).

The bolt-on acquisitions are also a bit different because the company estimates $50 – $100 million per year in acquisition spending in its investor presentation , so we pick the middle of the range and assume $75 million each year:

Strangely, CapEx is below D&A in each projected year, but it’s not necessarily “wrong” for a low-growth company like this one.

We would examine this point and refine these projections if we had several hours or days to complete this case study.

3-Statement Model, Part 5: Linking the Statements

We already have the Working Capital items and the Operating Lease Assets and Liabilities linked on the Balance Sheet, so there are only a few items left to complete.

The main rules are:

Assets Side – When linking an Asset to a line on the CFS, you start with the old Asset on the Balance Sheet and subtract the matching line on the CFS. This is because cash outflows represent increases in Assets, and cash inflows represent decreases in Assets .
Liabilities & Equity Side – It’s the opposite: add the line items on the CFS to the old numbers on the Balance Sheet.

Here’s what we do for the remaining line items:

Cash: Old Cash Balance + Net Change in Cash on the CFS.
PP&E/Goodwill/Intangibles: This simplified/consolidated line item equals the old balance minus CapEx minus D&A. Due to the signs on the CFS, CapEx increases this number, and D&A decreases it.
Other Assets: Start with the old number and subtract Acquisitions and the “Pensions/Other” line.
Total Debt: Old Debt Balance + Change in Debt from the CFS.
Noncontrolling Interests (NCI) : Old NCI Balance + NCI Net Income from the CFS + NCI Dividends from the CFS.
Common Shareholders’ Equity (CSE): Old CSE + Net Income + Dividends + Stock Repurchases + Other Items + FX Rate Effects.

This last one is a “catch-all” for everything else on the CFS that has not yet been reflected on the Balance Sheet, and it’s sometimes also known as the Statement of Owner’s Equity .

Our Balance Sheet balances after completing these links, which is a good sign:

But we’re not done because the Change in Debt, Stock Repurchases, and Interest Expense lines are still blank.

3-Statement Model, Part 6: Debt and Stock Repurchases

The case study document tells us to “follow company guidance” for these last few line items.

On slide 41 of their investor presentation , Otis provides an estimated percentage split of its Free Cash Flows over the next 3 years:

We already have the Dividends and Acquisitions, so we’ll use a simple logical check for the Debt and Stock Repurchases based on the $3 billion Minimum Cash from the case document :

Step 1: Does the company have Excess Cash Flow in this period? In other words, is its Beginning Cash + Net Change in Cash – Minimum Cash a positive number? If so, it can use that cash flow to repay Debt principal and repurchase Stock.
Step 2: Based on the chart above, we assume an 85% / 15% split between Stock Repurchases and Debt Principal Repayments.
Step 3: If the company has a Cash Flow Deficit , i.e., Beginning Cash + Net Change in Cash – Minimum Cash is negative, it must issue additional Debt to fund its operations.

You can see the logic below:

3-Statement Model - Debt and Stock Repurchase Logic

With these formulas, we can now add these links to the Cash Flow Statement and set the “Other” line item in Cash Flow from Financing to ~2% of Debt Issuances to represent the issuance fees.

The last line item is the Interest Expense on the Income Statement.

We can calculate the average interest rate on Debt in the previous years, but we don’t know how it will change in the future.

Interest rates were rising at the time of this case study, but if the company’s Debt has fixed rates and matures far into the future, it may not matter.

We can search for “long-term debt” in the 10-K and get a quick answer:

Otis - Debt Maturities and Interest Rates

Since most of this company’s Debt matures after the 5-year projection period, the average rate probably won’t increase by that much in this period.

But there are ~$3.4 billion of maturities in the next 5 years, so we increase the average interest rate from 2.0% to 3.5% and use these numbers to calculate the Interest Expense:

To avoid circular references, we can use the Beginning Debt balance to calculate the interest expense as well (for more, see our tutorial on how to find circular reference in Excel ).

3-Statement Model - Interest Rates and Interest Expense

3-Statement Model, Part 7: Model Checks, Reviews, and Final Comments

At a high level, this model confirms that most of the company’s claims are reasonable.

For example, Otis generates just over $5 billion in FCF over the next 3 years, and it spends the expected amounts on Dividends, Acquisitions, and Stock Repurchases:

Otis - Cumulative Free Cash Flow Over 3 Years

Its Free Cash Flow Conversion, which the company defines as FCF / Net Income, also stays well above 100%.

We’ve completed the model and met the requirements within the 90-minute time limit, so this attempt was successful.

However, there are some issues that we would fix with more time and resources:

Formatting – It’s not pretty right now. We must clean up the number formats, add input boxes for the projections, fix the color coding, add headers/footers, etc.
Revenue, Expense, and Cash Flow Detail – It’s better to project Revenue based on individual units sold and link the Product and Service segments to each other, such that New Units Sold drives Service Revenue in future periods; items like Operating Expenses should be linked to the Employee Count, and CapEx should be linked to the company’s production capacity.
Scenarios – Finally, we always evaluate companies across multiple scenarios in real life. What happens if the market growth changes? What if the company’s market share falls? What if its expenses rise? This model is not robust enough to support these scenarios or sensitivities.

How to Master the 3-Statement Model

This example is more difficult than the average 3-statement modeling test.

If you don’t have moderate-to-high Excel proficiency, you could easily spend an entire day (or more) on this.

But if you can finish in 2-3 hours, you’re at the level where you can improve your times with repeated practice and eventually do this in 90 minutes or less.

You don’t need to score 100% to “pass” these tests; the median scores tend to be very low.

Your goal should be to finish the model , and if you can’t complete everything, simplify so that you can answer at least the main questions by the end.

If you have an upcoming 3-statement modeling test, get as many examples as possible and complete them.

If you can’t find good examples, pick companies you follow, download their statements and investor presentations, and do what we did here: start from scratch and give yourself a few hours to build a simple 3-statement model.

If you improve over time and find it interesting to pick apart companies and business models, great.

If not… well, maybe the finance industry is not for you.

Further Learning

You might be also interested in this tutorial on balance sheet forecasting .

About the Author

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street . In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.

Free Exclusive Report: 57-page guide with the action plan you need to break into investment banking - how to tell your story, network, craft a winning resume, and dominate your interviews

Learn Valuation and Financial Modeling

Get a crash course on accounting, 3-statement modeling, valuation, and M&A and LBO modeling with 10+ global case studies.

Industry Case Studies

We have helped countless small and large organizations automate & streamline their operations saving them time and money

Expert Excel & Database Solutions

View case studies of our excel, access, sql hybrid development below:, insurance industry.

Processing insurance claims can be challenging due the vast amount of data that is required and the process is typically very manual. eSoftware Associates was hired to improve a manual claims processing system as well as a build a quoting tool for another firm.

Case Study:

Construction Industry

Efficiently managing project resources in the construction industry can be very difficult. ESW built a calculation engine that would forecast full-time and contractor resource availability as they rolled off and on to projects. A huge win for the executive team when it came to resource planning.

Whitepaper:

Automotive Manufacturing Case Study

There are a tremendous amount of moving pieces in the automotive manufacturing industry that prohibits one software solution from servicing all. ESW has provided cost savings and extreme value over the past decade by designing BOM, estimate and inventory systems per specific client requirements.

Manufacturing Engineering Industry

Attempting to create a single process for all of your products and custom manufacturing processes can be extremely burdensome since there is so many pieces of data to consider. ESW aligns manufacturing business with proper technology in order to grow without interruption.

Finance Industry

Based on the nature of the finance industry, ESW has had a vast array of opportunities to assist, streamline and develop efficient systems related to financial models, database repositories, reporting and various visualization analytics for financial companies.

Oil & Gas Industry

ESW works in many facets of the Oil & Gas industry from assisting various drillers in analyzing their findings to helping pipelines and worldwide oil transport companies make sense out of their rig or oil related data.

Legal Industry

At ESW, we partner with law firms in order to provide case intensive data analysis, interpretation and documentation for court proceedings and settlement activity. Our teams will even provide expert testimony as is necessary and today’s legal world.

Government Case Study

ESW works with local, state, federal and approved foreign government entities in a variety of capacities which include general technology consulting, supplemental data analysis, Microsoft application support & development and training. Learn how ESW streamlined an entire country in 2016.

Health & Wellness Case Study

Fast growing startups need access to accurate information and reports for management to review at the drop of a hat. ESW takes pride in wrangling the suite of Microsoft applications to provide flexible retrieval of information in a fast-paced environment.

Pharmaceutical / Healthcare Industry

Learn how ESW has developed compliant healthcare and pharmaceutical Microsoft Excel forms & database applications for various organizations over the past decade.

Education & Non-Profit Industries

ESW works with many colleges all over the country providing support for various Microsoft related applications and training to fill in gaps where in-house resources do not exist. Many of these clients have partnered with ESW for many years.

Retail Industry

ESW works with many understand all of your data in a quickly changing marketplace becomes burdensome with out-of-the-box solutions and why we have helped numerous Fortune 500 retail chains improve their data awareness and analytics.

Fill out our contact form or call us right now at to find out more about our Insurance related services. Whether you need Excel data related Analysis or Excel / Microsoft VBA programming, we will save you time and money.

Advanced Excel for Business Analysis & Data Visualization

Excel Functions
Excel Formulas
Excel Macro
Training Programs

Excel Case Studies for Advanced Excel Users

[tcb-script async=”” src=”https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js”][/tcb-script][tcb-script] (adsbygoogle = window.adsbygoogle || []).push({});[/tcb-script]

Compare 2 worksheets

A participant came to me at the end of the course and asked if there is a easier way to compare 2 worksheets. This seems like a simple task of using VLOOKUP. Just enter the VLOOKUP formula into a column in one of the worksheet and refer to the other worksheet for comparative values.That’s what usually people need.

But for her problem, it is not so simple. Her worksheet which contains employee data (e.g. employee number, name, reporting manager, departmet) has multiple columns and rows and all the cells need to be compared against the other worksheet. To add to the challenge, the rows are not in the same order, meaning that the employee in row 10 may not be presented in Row 10 of the other worksheet. So that is the best way to compare the 2 worksheets?…. more details on compare 2 worksheets

Excel Calendar

This is a wonderful creation by John Walkenbach. The solution makes extensive use of the YEAR, MONTH, DAY and WEEKDAY formula. Understanding how Excel stores dates is a pre-requisite to understand how all the 4 formulas work as one. But another main ingredient is the use of array formula. The employ of the IF formula helps to clean up the monthly calendar so that only days related to that particular month is displayed.

The conditional formatting makes use of SUMPRODUCT, which I called a super formula. This formula can do wonders. Compared this formula with the new SUMIFs and COUNTIFs, it still wins. SUMPRODUCT allows the use of formulas within the conditions while SUMIFs and COUNTIFs do not. VLOOKUP and AND are also used in conditional formatting to generate this perfect solution. More details can be found in this excel calendar write-up .

Return to the top of case studies

New! Comments

Have your say about what you just read! Leave me a comment in the box below.

Share this page:

What’s this?

Enjoy this page? Please pay it forward. Here’s how…

Would you prefer to share this page with others by linking to it?

Click on the HTML link code below.
Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.

Excel Courses for Business Professionals

How to sleep and lose weight

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.

advanced-excel.com is in no way associated with Microsoft

Popular Courses

Useful Links

Links I found useful and wanted to share.

Search the website

Get Active in Our Amazing Community of Over 455,000 Peers!

Efficio - excel-based case study.

I have an interview with Efficio, which includes an excel-based case study. Does it require any preparation? And does anyone have sample tests to share?

Appreciate a quick response.

Overview of answers

Date ascending
Date descending

The answer for any interview in regards to “should I prepare” is yes :)

Unfortunately I don't know much about Efficio (imagine that, a coach confessing ignorance!), but a quick google led me to understand the following from their interviews:

An online numeracy and problem-solving test
A first round of interviews, typically involving two interviews and a case study
An Excel-based case study
A second round of interviews with senior management

I actually do have a few excel-based real-life case studies (from past candidates). They are across the topics of food delivery, investment decisions, and mining/operations optimization.

Feel free to reach out and I can take you through them!

Hi Ian! i have an excel-based test coming up soon and would like to practice some of the case studies you have if you can share them with me!

Greetings Ian, I would appreciate if you can share those Excel mock tests with me, as I have an upcoming excel test for a consultant position as well in the procurement and SCM consulting. Thanks,

Hi Mo, more than happy to! All of this comes with any of my coaching (single or 3 session pack). Just shoot me a message if interested!

Hi, If you can please share the materials with me as it would be super useful with interview rounds coming up!

I actually took and passed that test. However that was several years ago (feeling old;)) so it may have changed in the meantime. At the time, if you were familiar with the main Excel formulas you definitely had an advantage.

Given Ian has some resources, I would recommend to reach out to him as preparing with something similar would definitely help.

Please PM me

I haven´t taken it myself, but I have seen mocks and you benefit from knowing the classical formulas. Any basic Excel course can gives you some background, but the key in Excel is to have a lot of practice so you can be very quick.

Hope it helps!

Did you ever take that test? I am looking to get some insight on what to expect.

I have an excel-based case, but it's not related to procurement. I would guess that something related to procurement would be more appropriate in your situation. I believe Ian is your best option here.

Related Cases

Bain/BCG - Hunger Warriors

Bcg - us supply chain crisis.

Bain Final Round: Pharmacy Delivery Entry

Similar questions, dhl management consulting, help with a procurement assignment as part of a job interview, how would you best approach this question any tips.

Select category
General Feedback
Case Interview Preparation
Technical Problems

0800 772 3782

[email protected]
The Old Church School Butts Hill, Frome Somerset, BA11 1HR

Don’t Just Take Our Word For It

Take a look at some of the solutions we have designed and developed for our clients. You might need something similar or you might need something with your own unique twist. Don’t hesitate to get in touch with us to discuss your requirements.

Recent Case Studies

Property Portfolio Management Spreadsheet

There are many private landlords in the UK, and they all need to keep track of how their properties are doing and whether they are

pexels lukas 577210 scaled e1606904826117

Creating A KPI Dashboard

Key Performance Indicator or KPI Dashboards Excel Dashboards can be created and automated to deliver a specific type of business data directly to the appropriate

pexels marek levak 2265488 scaled e1606904841521

Order Tracking and Invoicing Spreadsheet

Keep track of your orders at every stage All businesses rely on customer orders for their revenue. An error in processing an order could easily

“We used The Excel Experts to create a joint police and CPS performance tracker. The support was exceptionally insightful and professional. The result has saved hours of time on a weekly basis and providing data analysis to drive our teams objectives.”

Hampshire Police

“We were concerned that our requirements for a spreadsheet would get lost in translation given the specialist nature of our requirement, however Ben has been fantastic, managing to interpret our non-technical jargon into a working solution which we are delighted with.”

“I have been absolutely delighted with my consultant’s work. He produced exactly what I wanted with maximum efficiency. I found him extremely knowledgeable and his expertise enabled him to rapidly grasp what I was looking to achieve. I certainly recommend The Excel Experts.”

See How The Excel Experts Can Help You!

Do you need help with your Excel project?

See how The Excel Experts have helped others unlock their Excel data and make better decisions.

Fill out our form to learn more about our success stories and find out how we can help you.

Get in touch to get started on your Excel journey today!

Don’t let bad data ruin your business 😱 Invest in professional data cleaning services today

Get updates and tips from our Excel Experts on using Excel, improving your workflows and the latest Excel and Google Sheets innovations.

For over 10 years, we’ve offered remote and on-site assistance in the UK and internationally for all sizes of Excel projects. We offer a 100% money back guarantee and have helped over 300 happy customers.

The Excel Experts The Old Church School Butts Hill, Frome Somerset, BA11 1HR, UK

0800 772 3782 [email protected]