case study of 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

• Online Degree Explore Bachelor’s & Master’s degrees
• MasterTrack™ Earn credit towards a Master’s degree
• University Certificates Advance your career with graduate-level learning
• Top Courses
• Join for Free

Excel Skills for Data Analytics and Visualization Specialization

Unleash the Power of Excel to Analyse Your Data. Import, visualize, and analyze huge and complex datasets using modern Excel tools.

Taught in English

Some content may not be translated

Instructors: Dr Prashan S. M. Karunaratne +1 more

Instructors

Financial aid available

81,537 already enrolled

Specialization - 3 course series

(3,175 reviews)

Recommended experience

Intermediate level

You should have familiarity with the Excel user interface, moving around an Excel workbook, and creating basic formulas.

Skills you'll gain

• Data Analysis
• Microsoft Excel
• Data Visualization

Details to know

Add to your LinkedIn profile

See how employees at top companies are mastering in-demand skills

Advance your subject-matter expertise

• Learn in-demand skills from university and industry experts
• Master a subject or tool with hands-on projects
• Develop a deep understanding of key concepts
• Earn a career certificate from Macquarie University

Earn a career certificate

Add this credential to your LinkedIn profile, resume, or CV

Share it on social media and in your performance review

As data becomes the modern currency, so the ability to quickly and accurately analyse data has become of paramount importance. Therefore, data analytics and visualization are two of the most sought after skills for high paying jobs with strong future growth prospects. According to an IBM report Opens in a new tab , the Excel tools for data analytics and visualization are among the top 10 competencies projected to show double-digit growth in their demand. This course will help you develop your analytical and visualization skills so that you not only improve your current work performance but also expand your future job prospects. For those in business and data analysis who want to master advanced Excel and beginner Power BI , that will add an asset to your employability portfolio.

Upon completing this specialization, you will be able to bring data to life using advanced Excel functions , creative visualizations , and powerful automation features . These courses will equip you with a comprehensive set of tools for transforming , linking , and analysing data . You will master a broad range of charts and create stunning interactive dashboards . Finally, you will explore a new dimension in Excel with PowerPivot, Get and Transform, and DAX. Harnessing the power of an underlying database engine, we will remove the 1,048,576 row limitation, completely automate data transformation, create data models to effectively link data, and open the gateway to Power Business Intelligence.

Applied Learning Project

Working with datasets similar to those typically found in a business, you will use powerful Excel tools to wrangle the data into shape, create useful visualizations, and prepare dashboards and report to share your results. You will learn to create a data workflow to automate your analysis and make the results flexible and reproducible.

Excel Fundamentals for Data Analysis

What you'll learn.

Use Excel tools and functions to clean and prepare data for analysis.

Use Named Ranges and Tables to automate your analysis.

Understand the different types of data in Excel and use appropriate functions to work with them.

Use logical and lookup functions to transform, link and categorise data.

Data Visualization in Excel

In an age now driven by "big data", we need to cut through the noise and present key information in a way that can be quickly consumed and acted upon making data visualization an increasingly important skill. Visualizations need to not only present data in an easy to understand and attractive way, but they must also provide context for the data, tell a story, achieving that fine balance between form and function. Excel has many rivals in this space, but it is still an excellent choice, particularly if it's where your data resides. It offers a wealth of tools for creating visualizations other than charts and the chart options available are constantly increasing and improving, so the newer versions now include waterfall charts, sunburst diagrams and even map charts. But what sets Excel apart is its flexibility, it gives us total creative control over our designs so if needed we could produce our own animated custom chart to tell the right story for our data.

Over five weeks we will explore Excel's rich selection of visualization tools using practical case studies as seen through the eyes of Rohan, an environmental analyst. Rohan is required to produce visualizations that will show trends, forecasts, breakdowns and comparisons for a large variety of environmental data sets. As well as utilising the usual chart types he wants to use conditional formats, sparklines, specialised charts and even create his own animated charts and infographics. In some cases, he will also need to prepare the data using pivot tables to drill down and answer very specific questions. We are going to help him achieve all this and present our finished visualizations in attractive reports and dashboards that use tools like slicers and macros for automation and interactivity. These are the topics we will cover: Week 1: Dynamic visualizations with conditional formatting, custom number formatting, sparklines and macros Week 2: Charting techniques for telling the right story Week 3: Creating specialised and custom charts Week 4: Summarising and filtering data with pivot tables and pivot charts Week 5: Creating interactive dashboards in Excel This is the second course in our Specialization on Data Analytics and Visualization. The first course: Excel Fundamentals for Data Analysis, covers data preparation and cleaning but also teaches some of the prerequisites for this course like tables and named ranges as well as text, lookup and logical functions. To get the most out of this course we would recommend you do the first course or have experience with these topics. In this course we focus on Data Visualization in Excel, join us for this exciting journey.

Excel Power Tools for Data Analysis

Welcome to Excel Power Tools for Data Analysis. In this four-week course, we introduce Power Query, Power Pivot and Power BI, three power tools for transforming, analysing and presenting data.

Excel's ease and flexibility have long made it a tool of choice for doing data analysis, but it does have some inherent limitations: for one, truly "big" data simply does not fit in a spreadsheet and for another, the process of importing and cleaning data can be a repetitive, time-consuming and error-prone. Over the last few years, Microsoft have worked on transforming the end-to-end experience for analysts, and Excel has undergone a major upgrade with the inclusion of Power Query and Power Pivot. In this course, we will learn how to use Power Query to automate the process of importing and preparing data for analysis. We will see how Power Pivot revolutionises the actual analysis process by providing us with an analytical database inside the Excel workbook, capable of storing millions of rows, and a powerful modelling language called DAX which allows us to perform advanced analytics on our data. We will finish off by venturing out of Excel and introducing Power BI, which also uses the Power Query and Power BI architecture but allows us to create stunning interactive reports and dashboards. This is the third course in our Specialization on Data Analytics and Visualization. The previous courses: Excel Fundamentals for Data Analysis and Data Visualization in Excel, cover data preparation, cleaning, visualisation, and creating dashboards. To get the most out of this course we would recommend you do the previous courses or have experience with these topics. In this course we focus on Excel Power Tools, join us for this exciting journey. Please note that Power Query, Power Pivot and Power BI Desktop are only available on the Windows platform, so Mac users will require Bootcamp running Windows or a Virtual machine with a Window O/S. While Power Query is available as an add-in Excel 2010 and 2013, the tools have changed significantly, and this course has only been designed and tested for Excel 2016 and later. For an optimal experience, we recommend Office 365.

Macquarie is ranked among the top one per cent of universities in the world, and with a 5-star QS rating, we are recognised for producing graduates who are among the most sought-after professionals in the world. Since our foundation 54 years ago, we have aspired to be a different type of university: one focused on fostering collaboration between students, academics, industry and society.

Why people choose Coursera for their career

New to Business Essentials? Start here.

Open new doors with Coursera Plus

Unlimited access to 7,000+ world-class courses, hands-on projects, and job-ready certificate programs - all included in your subscription

Advance your career with an online degree

Earn a degree from world-class universities - 100% online

Join over 3,400 global companies that choose Coursera for Business

Upskill your employees to excel in the digital economy

Frequently asked questions

What version of excel will i need.

We recommend using the most recent Office 365 version of Excel on Windows. Versions since Excel 2016 should have most of the necessary features, with some modifications. Versions prior to 2013 probably do not have the needed features.

Mac users with recent versions of Excel should be able to complete the first 2 courses in the Specialization, but the tools used in the third course are only available for Windows at this stage.

Is this course really 100% online? Do I need to attend any classes in person?

This course is completely online, so there’s no need to show up to a classroom in person. You can access your lectures, readings and assignments anytime and anywhere via the web or your mobile device.

What is the refund policy?

If you subscribed, you get a 7-day free trial during which you can cancel at no penalty. After that, we don’t give refunds, but you can cancel your subscription at any time. See our full refund policy Opens in a new tab .

Can I just enroll in a single course?

Yes! To get started, click the course card that interests you and enroll. You can enroll and complete the course to earn a shareable certificate, or you can audit it to view the course materials for free. When you subscribe to a course that is part of a Specialization, you’re automatically subscribed to the full Specialization. Visit your learner dashboard to track your progress.

Is financial aid available?

Yes. In select learning programs, you can apply for financial aid or a scholarship if you can’t afford the enrollment fee. If fin aid or scholarship is available for your learning program selection, you’ll find a link to apply on the description page.

Can I take the course for free?

When you enroll in the course, you get access to all of the courses in the Specialization, and you earn a certificate when you complete the work. If you only want to read and view the course content, you can audit the course for free. If you cannot afford the fee, you can apply for financial aid Opens in a new tab .

Will I earn university credit for completing the Specialization?

This Specialization doesn't carry university credit, but some universities may choose to accept Specialization Certificates for credit. Check with your institution to learn more.

More questions

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

Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy

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

• Analytics , Charts and Graphs , Financial Modeling

Doing Cost Benefit Analysis in Excel – a case study

• Last updated: September 2016

Imagine you are the in-charge of finance department at Hogwarts. So one fine day, while you are practicing the spells, Dumbledore walks in to your office and says, “Our electricity bills are way too high. As the muggles don’t accept wizard money, we have to find a way to reduce our power consumption.”

So you summoned the previous 12 month utility bills to examine energy consumption patterns, and pretty soon you realized that most of the electricity consumption is due to the light bulbs. You suddenly have a brilliant idea. Why not replace the light bulbs with a variety that consumes low power?   A light bulb moment indeed.

Your next step is to figure out what varieties of light bulbs are out there. Fortunately this is easier than catching a snitch in a game of quidditch. A quick search revealed that there are 3 types of light bulbs:

• Regular incandescent bulbs (the kind Hogwarts currently uses)
• Compact Fluorescent Light bulbs (CFL)
• Light Emitting Diode bulbs (LED)

Now your job is to do a  cost benefit analysis  of these options and pick one.

What is cost benefit analysis & how to do it?

Cost benefit analysis, as the name suggests is a process of identifying all the costs & benefits of different decision choices and finding which choice offers maximum benefit for minimum cost.

It is a generic technique and the implementation varies depending on situation, industry and available data.

A typical cost benefit analysis involves these steps:

• Gather all the necessary data
• Fixed or one time costs
• Variable costs
• Calculate the benefits
• Compare costs & benefits over a period of time
• Decide which option is best for chosen time period
• Optional: Provide what-if analysis

Let’s conduct cost benefit analysis for our light bulb problem and figure out which option is best.

But first, download the cost benefit analysis workbook

Click here to download the cost benefit analysis workbook . Refer to it as you read this article for best results.

1. Input Data & Assumptions

For each type of bulb we need to find out below information:

• Electricity consumption (watts/hr)
• Life time in hours
• Amount of light (lumens) generated by the bulb

Assumptions:

We also need to assume a few things to keep our cost benefit analysis model realistic & simple.

Some of the assumptions we can go with are,

Global assumptions:

• We need only 1 bulb (doing analysis for n bulbs is just a matter of multiplying 1 bulb results with  n )
• Analysis will be conducted in Indian Rupees
• Let’s compare bulbs that give same amount of light (lumens). This means we can ignore the benefit part and focus on costs alone
• This analysis ignores any impact / costs / benefits associated with environmental impact (CO2 emissions, harmful metals like mercury, heat generation etc.)
• Analysis time frame is 5 years.

Other assumptions:

• Average usage of bulb per day is 8 hours .
• Cost of electricity is Rs. 5 per unit (KWH)
• Inflation for electricity cost is 1%

Once we have all the data, tabulate it in Excel like this:

2. Calculate Total Cost of Ownership

Once we have all the necessary data, let’s calculate the total cost of each option (Regular, CFL & LED) over a period of 5 years.

This step involves calculating both fixed & variable costs.

Fixed or one time costs:

The fixed cost for each light bulb type is nothing but the price .

But wait… what about the life time of bulb?

Since each type of bulb has certain life time, we will have to pay for replacement of bulbs too.

This means, apart from fixed cost at the start of time period, we will also have a variable cost that depends on the life time of bulb type.

Variable costs:

There are 2 variable costs in our analysis.

• Electricity consumption cost
• Bulb replacement cost

Electricity consumption cost:

This is calculated by below formula:

Wattage per month / 1000 * Inflated unit cost

The actual Excel formula looks like this:

FV($C$13/12,$B20,-$C$14*30*C$8*$C$12/1000)

How this formula works?

To understand this formula, first imagine what the total unit cost should be at the end of Month x .

For first month, the cost is = total monthly usage in hours * watts per hour / 1000 * unit cost * (1 + inflation/12)^ 1

For second month, the cost is same as above, but the exponent in the end becomes 2 .

Let’s say, the blue part of the formula is denoted by  something .

Then, the cost at the end of Month X will be,

= something * ( (1+inflation/12)^1 + (1+inflation/12)^2 + … + (1+inflation/12)^X )

Oh, all this math is confusing… Isn’t there a simple spell to answer this?

I am glad you asked. There is a spell to get this answer in one shot. It is called as FV()

The FV formula calculates sum of above series.

We simply write

= -FV(inflation/12, month number,  total monthly usage in hours * watts per hour / 1000 * unit cost )

to get the answer we want.

Why the minus sign in front of FV?

This is because, by default FV returns values in negative. It has got something to do with how banks always take money from us, but are very reluctant to give back or like that .

Bulb replacement cost:

The unit cost formula felt like trying to catch a snitch while riding a broomstick upside down. Thankfully, the bulb replacement cost formula feels like sitting in the crowd, cheering match while eating chocolate frogs.

The calculation for bulb replacement goes like this:

Cumulative usage in hours / life time of the bulb * unit price of bulb

Here is the formula for this:

(INT(cumulative usage/ life time of the bulb)+1)*unit price of bulb

Why use INT(…) + 1

Let’s say the life time is 1,000 hrs, cost is Rs 20 and we use 240 hrs in first month. Our cost is still Rs. 20, not 24% of 20.

Likewise, at the end of 5th month, our total usage would be 1200 hrs (240 x 5 = 1200) and we must buy a new bulb as the life time is only 1000 hrs.

The replacement cost is not uniformly spread across months (or hours). It happens once at beginning and then recurs once per lifetime of the bulb.

Hence we use INT to round the  cumulative usage / life time  to the integer portion (ex: INT(240/1000) will be  0 ) and add 1 to it as there is initial cost.

Explanation of these formulas in our spreadsheet

Look at below illustration to understand how these formulas look in the cost benefit analysis worksheet.

3. Calculate benefits

This part is not required for our problem as the benefits are same for all 3 types of bulbs. You can use logic similar to cost calculation when the benefits vary. For example if you want to do cost benefit analysis of 3 types of investment choices – mutual funds, stocks, bank deposits, then you can use below framework:

• Brokerage costs
• Entry costs
• Operating expenses
• Volatility / risk factors
• Return of the investment
• Liquidity benefit

4. Compare costs & benefits over a period of time

Once we have these cost & benefit calculations ready, we need to calculate them for 60 months (5 years) for all 3 types of bulbs.

The resultant table looks something like this:

5. Decide the winner

Once we have all the numbers, it is just a matter of picking the winner. If you are comparing costs, pick the lowest cost item . If you are comparing benefits, pick the item that offers most benefit / cost ratio .

How to convince your boss about the decision?

While it is easy to decide which option is the winner by just looking at numbers, when you take this proposal back to Dumbledore, he may want a little more explanation. This is where a  visualization of cost benefit analysis  can help.

Example – Visualization of Cost benefit analysis

Here is a completed visualization of our light bulb cost benefit analysis.

How to create this chart?

Simple, just follow below steps.

• Select the total cost of ownership table that you calculated
• Insert a new line chart
• Format the chart as per your (or your boss’) taste

Things to keep in mind:

• Go with line charts if your analysis is against a time period or similar
• Go with column / bar charts if you are comparing various options with one time costs only
• Format the chart so that it is easy to identify winning choice at each point of time.

Adding what-if analysis

What if analysis is a great way give power to your decision makers. When you prepare a cost benefit analysis model like above, you will always hear questions like:

• So what would be the total cost for using 12 CFL bulbs 16 hours per day for next 25 years?
• In above case, how much would we save if we switch to LED bulbs?
• What would be the cost for 30 years? 10 hours a day? 20 bulbs?

Thanks to powerful Excel form control feature , you can easily add a comprehensive what-if analysis tool to your model.

Process for adding what-if analysis

Follow below process for including what-if analysis in your spreadsheet models.

• Identify all possible scenarios for which what-if analysis may be required.
• Determine the variables (in our case, the variables are number of bulbs, bulb type, usage in hours & years)
• Figure out what output should be displayed (in our case, the output is total cost and a comparison with other bulb types)
• Set up an input area where user can select any combination of variables
• Use form controls , slicers , data validation , VBA  or simple input cells for gathering this data Tip: click on those linked words to understand how to set them up
• Write formulas that link to the user input cells / form controls
• Display the output (text, charts etc.)

Please examine the download workbook for actual implementation of this.

Guidelines for creating better analysis models

Whenever you are analyzing something like this, please follow below guiding principles for awesome results.

• Do your research first:  Identify all factors, inputs, assumptions & facts that impact the decisions. Spend sometime researching before jumping in to Excel.
• Set up separate areas of input, analysis & output:  Create separate areas in your workbook to handle inputs, calculations & outputs (such as charts, text). Clearly demarcate them by using different styles or headers for each section.
• Avoid analysis paralysis:  Keep your analysis workbooks simple & realistic. Don’t over complicate them with too many inputs or too much calculation. If a certain factor is irrelevant or too complicated to consider for your analysis, ignore it. Example: in our analysis we ignored benefits as they are same for all 3 options. We also ignore environmental impact as it is tricky to calculate.
• Use consistent formulas:  Write your formulas in such a way that same pattern is repeated many times. This way you can write once and use the power of relative & absolute references in Excel to fill the formulas everywhere.
• Let users play with your model:  Include what-if analysis or form control based interaction in your workbooks so that your users can play with the model and get answers for their questions.
• Visual explanations: Visual explanations like charts, dashboards are very powerful & memorable. So depict your results visually as much as possible. Remember the old adage,  a picture is worth thousand words. 

Related: Spreadsheet modeling best practices for analysts

Download Cost Benefit Analysis workbook

Please click here to download cost benefit analysis workbook . Examine the calculations & form controls to learn more.

Cost benefit analysis – your experiences please…

Cost benefit analysis was a big part of my work when I worked as an analyst. Now I am in the role of a CEO and it is even more relevant for me. For most situations I create a simple Excel model to examine the costs & benefits to decide the winner.

What about you?  How do you analyze costs vs benefits? What techniques do you use? Please share your stories, examples & tips in the comments section.

Want more? Introducing 50 ways to analyze data course

If you like to learn how to analyze data, gather insights, prepare outputs & interpret results, then you will love my new course –  50 ways to analyze data.

The above example is lesson number 24 in our course. Each of the 50 lessons deal with common business analysis situations, case studies & techniques so that you can become the analytical wizard you always wanted to .

This course is now open.

If you want to know more about it, please click here .

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME. Read my story • FREE Excel tips book

• 20 Comments
• Ask a question or say something...
• Tagged under advanced excel , cost benefit analysis , downloads , Financial Modeling , form controls , FV , Learn Excel , line charts , Microsoft Excel Formulas , no-nl , screencasts , solo
• Category: Analytics , Charts and Graphs , Financial Modeling

20 Responses to “Doing Cost Benefit Analysis in Excel – a case study”

It shows my address is already subscribed. Am I listed in the mail list?

Truly magnificent.

Hello Chandoo,

Great post to become awesome.

Just a minor matter. Please check the units of Power, it is "Watt" not "Watts per hour". If a 60 Watt bulb is used for an hour it uses 60 Watthour(= 60 Wh) of energy, divided by 1000 this gives 0.06 kWh.

I know this is not a site for physics, and is has no impact for the end results, but it may confuse some of your readers and blur a for the rest good example.

I love your site!

Regards, Hardy

Very good model Chandoo, simple and to the point. Just to help improve the input process in an evaluation. Be doubly careful if your decision depends too much on assets / articles lifetime. As an example: advertised bulb CFL lifetime is 8000 hr. But this only happens at the Lab and only if you turn on the bulb once per day. Reality is that in many locations, electricity voltage & power is variable, and bulbs are turned on more often increasing the probability of burning. So the real lifetime of bulbs are not too different between the options. Regards Carlos

Good post but missing very important issue. LEDs deteriorate with time, so just after 5k hrs usually the LED is capable of 50-60% of initial Lumens. Quite a drastic drop. As for the lifetime both CFL and LED lights (the mainstrem ones) lifecycle is around 6-8k hrs. And in places where lights is often switched on/off the lifetime of CFL/LED drops to 3-4k hrs.

Hi Chandoo. Interesting analysis as always. I especially liked your what-if tool. Another way to think about multi-year cost/benefit is from a discounted cash flow perspective. Using your example I would use the Incandescent bulb as the base line and calculate the differential cash flows for the two alternatives. Period zero would be an incremental investment (cash out) of 100 for the CFL and 380 for the LED. Benefits (cash in) would be each period's cost savings for each alternative comparing their costs to the Incandescent's (including any additional bulb purchases). You could then use these cash flow estimates to calculate simple Payback but I would discount the cash flow streams (excluding initial cash outlay) using the business's Weighted Average Cost of Capital (WACC) or Hurdle Rate and Excel's NPV or IRR functions. Subtract out that initial cash outlay and the result would suggest that, all other factors being equal, the highest NPV (or IRR) is the best alternative. A negative NPV, or an IRR less than the discount rate, would suggest the Incandescent is the better alternative. The benefit of this approach is that it considers the always important time value of money.

One final thought, I would do this type analysis with annual data. The monthly nuances aren't worth the trouble - especially when you are forecasting the future.

Hi Bob. I like your approach. Do you think you could adjust Chandoo's spreadsheet to match your idea?

Sure. But I am not sure how to post a sample workbook here.

@Bob You can't attach a file here

Ask the question in the Chandoo.org Forums http://chandoo.org/forum/

Where you can attach a file

Hi Chandoo. I am always geeking out on this website. I agree with Bob. I think an important aspect of time value of money is being lost here. the expensive upfront cost of the LED typically outweighs the low cost of energy. I think long run though this model indicates that LED's reign supreme in a childless home that is. As always keep up the good work. 🙂

Two other points I neglected to include in my earlier post.

First, NPV and/or IRR calculations should use risk adjusted cash flows. With any cost/benefit forecast there is some degree of risk. (As someone once said, forecasting is difficult, especially about the future.) For example, if because of the CFL lifetime risk noted by Carlos, and the LED lumen output risk pointed out by ChrisAd, you estimate the probability of achieving the incremental cash flow benefits of these alternatives is only 60%. For the NPV calculation you would then use only 60% of the originally calculated benefits -- a 100 Rupee cash benefit becomes 60 for NPV purposes.

Secondly, somehow I forgot the always present tax authorities. NPV/IRR costs (if expense, not capex) and benefits should be after tax. Depending on your country and your tax situation your 60 Rupees may become 40.

So if you adjust your cash flow stream for risk and taxes you are good to go. Sorry I forgot these two important factors in my initial comment.

[…] Chandoo has a light bulb moment, while setting up a cost benefit analysis. […]

Hi Chandoo. There is a unique flaw in your model specifically related the cost effectiveness of light bulb technologies. The flaw has nothing to do with the challenges indicated by the other commenters. It also comes with a fun story.

It was spring break for my two young children. We visited a local science and technology museum where we live. I first identified the flaw watching my children play with one of the hands-on exhibits. The exhibit was a small bicycle connected to a generator. The generator is connected to three light bulbs, regular, CFL, and LED. As you pedal the bike the lightbulbs illuminate. Light pedaling illuminates only the LED. Very aggressive pedaling illuminates all three bulbs. The purpose of the exhibit is to show children how much extra energy is required to power regular lightbulbs. Simple enough in a very linear world.

The flaw I identified, then validated with the museum staff, is the math is completely linear. Meaning the math favors LED technology assuming 100% of the light is required 100% of the time, in the same room, and assumes the light is either on or off.

1) Buildings tend to have multiple rooms, which have different lighting requirements. Think about your house.

The lights over the vanity mirror in your bathroom may only require 1 x LED in terms of total lumens however may require multiple bulbs to eliminate shadows while applying makeup. Which takes a short amount of time, then the lights are turned off for much of the day. The cost of LED may be too high based on the short amount to time the lights are in use.

Lamps in your living room may have a 3-way switch that progressively increases the lumens, requiring a special bulb.

Your back porch may require a high power bulb that shines brightly over a large area.

In these cases you need to identify the bulb technology that meets the "art" side of the equation before applying the "science" part. When done correctly you will identify a mix a build technologies that meet your overall requirements.

2) CFL and other specialized bulb technologies cannot be used with a dimmer switch. Only a traditional on-off switch. The ceiling light in my kids bedrooms are connected to a dimmer switch, allowing me to progressively change the lumens up or down depending on need and time-of-day. That barrier eliminates some bulb technologies from the equation for those rooms.

Net/net, in a linear world we would always choose LED technology. However, the more accurate analysis is to treat each room as it's own cost analysis based on your lighting requirements and use case.

[…] http://chandoo.org/wp/2015/01/28/cost-benefit-analysis-in-excel/ […]

The crux of the matter in this lesson is to learn how to use some Excel tools and techniques for analysis. The choice of light bulbs is only an example, which appears to be subject of many controversies. However, the numeric data associated with this example are pretty good for the learning purpose of Excel techniques.

Excellent work Chandoo.

Hi Chandoo, I’m shy about asking, but if I buy 20 LED bulbs at 400 rupee each, then wouldn’t the first year cost for an LED bulb be at least 8,000 for cost of bulbs (not including the energy cost)? The Quick Compare says the total cost would be 6,088 (16hrs/day). If I’m buying bulbs, I would like to know the upfront cost. Maybe I’m missing something in this cost-benefit. I enjoy your blog very much.

Hi Chandoo, would like to know if the course would include supply chain analysis and modeling etc. If not, will you consider adding them or conduct a separate course on this?

am particular interested to know more on this aspect.

Rgds, Jason

Hi Chandoo,

Thank you very much for sharing your experience and knowledge here! It became very useful discussion. Thanks to all guys who shared their opinions and different solutions as well. The article is EXCELENT and full of professionalism! A real example how excel can help you doing real solutions.

For this, I had been using this and it's great. It has instructions which can help you. They also have live support and forum to help you anytime.

Hi Chandoo, I am a big fan of your dashboards and reports, i take lot of ideas from it to build my project management dashboards. Regarding Benefits Analysis, could you please help provide information in details on how to calculate benefits realization for 5 years for a IT Software development project? it can be both tangible and intangible benefits

Leave a Reply

Name (required)

Mail (will not be published) (required)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Notify me of when new comments are posted via e-mail

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free. 

Get Started

• Excel Basics
• Advanced Excel
• 100+ Formulas
• Free Crash Course
• Pivot Tables

Online Classes

• Excel School
• VBA Classes
• Power BI Classes
• 50 ways to analyze data
• Mini Courses
• Welcome Message
• Forum & Community
• 20+ Excel Templates
• 200+ Dashboards
• 40+ VBA Code samples
• Ebooks & More

© All rights reserved.

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

In Microsoft Excel, analyzing scenarios is one of the crucial tasks. We consider it as a part of data analysis. Scenario analysis means comparing values and results side-by-side. You will create a dataset first. After that, you have to create a scenario for every possible value. In this tutorial, you will learn to do scenario analysis in Excel.

This tutorial will be on point with suitable examples and proper illustrations. So, read this article to enrich your Excel knowledge.

What is Scenario Manager in Excel?

Scenario manager in Excel is an element of three what-if-analysis tools in Excel, which are built-in in, excel. In uncomplicated words, you can notice the effect of switching input values without altering the existing data. It basically 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. After that, you can see the output of different inputs or different scenarios at the same time.

How to Perform Scenario Analysis in Excel

We can perform a scenario analysis by the Scenario Manager in Excel. We discussed that earlier. Now, in this section, you will learn to create your first scenario in Excel. So, stay tuned.

You want to rent a house. There are some options for houses. We can consider these options as scenarios. Now, you have to decide which house to decide to save more money.

To demonstrate this, we are going to use the following dataset:

This is for House 1. Now, we are going to create a scenario for House 2 and House 3.

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

• Then, the Scenario Manager dialog box will appear. After that, click on Add .

• Then, in the Edit Scenario dialog box, give a Scenario name . We are giving it House 2 . After that, select Changing cells .

• Next, select the range of cells C5:C9 . We will change these inputs.

• After that click on OK .
• Now, in the Scenario values dialog box, we are giving the expenses of House 2. Then, click on Ok .

• Now, we have added a scenario for House 2 . Do the same for House 3 .
• Here, we are giving these values for House 3

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

• Now, you will see these changes for House 2 .

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

As you can see, we have successfully performed scenario analysis in Excel

Create Scenario Summary:

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

• First, open the Scenario Manager.

• Then, click on Summary .

• Now, select your Result cells . Here, our result cell is C10 because we were showing our Total values on that cell. Next, click on OK .

Here, you can see the side-by-side scenario summary in a different worksheet. Now, you can easily decide which House you should choose.

Read More:  How to Use Scenario Manager in Excel

Scenario Analysis in Excel: 2 Practical Examples

In the following sections, we will provide you with two practical examples of scenario analysis in Excel. We recommend you read and try all of these.  We hope it will increase your interest in scenario analysis. Hopefully, it will improve your Excel knowledge.

1. Scenario Analysis of Compound Interests in Excel

In this section, we will show you an example of the Compound interests of banks. We will create two scenarios of this example to demonstrate.

Compound interest means earning or paying interest on interest. Basically, 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 contain the same dataset. But we will calculate differently compound interests.

Suppose, you want to invest $10000 for ten years somewhere. 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.

Now, you are in puzzlement where to apply. So, let’s use our scenario manager to find which one will provide you with more profit.

This is the dataset for Bank “X”:

We are using this formula to calculate the Estimated Balance:

Let’s create a scenario analysis.

• First, go to the Data tab. Then, from the Forecast group, select What-If Analysis > Scenario manager .
• Then, in the Edit Scenario dialog box, give a Scenario name . We are giving it Bank “Y” . After that, select cell C6 in Changing cells . Because only the number of compounding periods per year will vary here. Everything will be the same. Then, click on OK .

• Then, 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. Next, click on OK .

• Now, we have created a scenario for Bank “Y”.

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

• Then, give this scenario the name Bank “Z”. Then, select cell C6 as the changing cell.

• Now, give the scenario values 365. Because Bank “Z” is offering 5% interest compounding daily. So, no. of compounding periods will be 365 days.

• Then, click on OK .

• Now, to create a scenario summary report, click on Summary . Then select cell C9 as the result cell.

• After that, click on OK .

As you can see, we have successfully created a scenario analysis in Excel. You can see the estimated balance for each compound interest of the banks.

2. Preparing Budget for an Office Tour Using Scenario Manager

In this section, we are going to show you almost a similar example as we showed earlier.

Suppose, your office has decided to go on an office tour. Now, your boss has given you the responsibility to make the budget. You have three options for choosing a place.

For this, you have made this budget:

Now, the budget you have made is for place 1. You have to make a budget for Place 2 and Place 3. After that, you have to decide which option will be better.

• First, go to Data Then, from the Forecast group, select What-If Analysis > Scenario manager.
• Then, in the Edit Scenario dialog box, give a Scenario name . We are giving it Place 2 . After that, select the range of cells C5:C9 in Changing cells . Then, click on OK .

• Now, give the expenses for Place 2

• Now, we have added the Place 2 scenario. After that, click on Add to add scenario for Place 3.
• Create a Scenario for Place 3 in the same process. Now, give your expenses for Place 3.

• Now, click on OK .

• After that, click on Summary to analyze the scenarios side-by-side. Then, select cell C10 for showing the result.

• Finally, click on OK .

As you can see, we have successfully performed the scenario analysis of an office tour in Excel.

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 you need to require them for a scenario PivotTable report.

Download Practice Workbook

Download this practice workbook.

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to create a scenario analysis in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!

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 Tutorial

Excel formatting, excel data analysis, table pivot, excel functions, excel how to, guided projects, excel examples, excel references.

Excel is the world's most used spreadsheet program

Excel is a powerful tool to use for mathematical functions

Start learning Excel now »

Learn Excel

Examples in Each Chapter

We use practical examples to give the user a better understanding of the concepts.

Copy Values Tool

Example values can be copied from the tutorial and into your spreadsheet, making it easy for you to tag along step-by-step:

Advertisement

Case Based Learning

We have created active learning activities, so you can test and build your knowledge. Making the learning experience more fun and engaging.

Solve Case »

Why Study Excel?

Excel is the world's most used spreadsheet program.

Example use areas:

• Data analytics
• Project management
• Finance and accounting

Test Yourself With Exercises

Excel exercise:.

Complete the Excel formula:

Start the Exercise

My Learning

Track your progress with the free "My Learning" program here at W3Schools.

Log in to your account, and start earning points!

This is an optional feature. You can study at W3Schools without using My Learning.

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

[email protected]

Top Tutorials

Top references, top examples, get certified.

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 DCF Model: The Complete Guide… to a Historical Relic?

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!

It may be an understatement to say that we live in “interesting times.”

Cryptocurrencies based on dog memes suddenly spike up or down by 500%, people think that meme stocks are better investments than high-dividend stocks, and growth-oriented tech stocks seem to rise forever, all based on promises of “profits in the future – the distant future.”

In this environment, it’s fair to ask if the discounted cash flow (DCF) analysis and DCF models are still relevant at all.

I’ll address this question at the end of this article, but the short answer is that the DCF model still matters – but perhaps less so for a tiny percentage of overhyped companies and less so in crazed market environments.

But let’s start by describing each step of the analysis and giving you a few simple examples:

DCF Model: Video Tutorial and Excel Templates

If you’d prefer to watch rather than read, you can get this [very long] tutorial below:

Table of Contents:

• 2:29: The Big Idea Behind a DCF Model
• 5:21: Company/Industry Research
• 8:36: DCF Model, Step 1: Unlevered Free Cash Flow
• 21:46: DCF Model, Step 2: The Discount Rate
• 28:46: DCF Model, Step 3: The Terminal Value
• 34:15: Common Criticisms of the DCF – and Responses

And here are the relevant files and links:

• Walmart DCF – Corresponds to this tutorial and everything below.
• Walmart 10-K Excerpts .
• Slide presentation for this tutorial .
• Uber Valuation and DCF – Different DCF model for a high-growth company (sort of).
• Snap Valuation and DCF – Different DCF model for a different high-growth company.

The Big Idea Behind a DCF Model

The big idea is that you can use the following formula to value any asset or company that generates cash flow (whether now or “eventually”):

The “Discount Rate” represents risk and potential returns – a higher rate means more risk, but also higher potential returns.

A company is worth more when its cash flows and/or cash flow growth rate are higher, and it’s worth less when those are lower.

The company is also worth less when it is riskier or when expectations for it are higher, i.e., when the Discount Rate is higher.

If a company’s Discount Rate and Cash Flow Growth Rate stayed the same forever, then investment analysis would be simple: just plug the numbers into this formula.

But that never happens!

Companies grow and change over time, and often they are riskier with higher growth potential in earlier years, and then they mature and become less risky later on.

Valuation is more than this simple formula because companies’ Discount Rates and Cash Flow Growth Rates change over time.

To represent that change, you divide companies’ lifecycles into two periods:

• Period #1 (Explicit Forecast Period): The company’s Cash Flow, Cash Flow Growth Rate, and potentially even the Discount Rate change over 5, 10, 15, or 20+ years, but the company reaches maturity or “stabilization” by the end.
• Period #2 (Terminal Period): The Discount Rate and Cash Flow Growth Rate stop changing because the company is mature. Its Cash Flow will still change, but the valuation formula above works because it requires only the first year of Cash Flow in this period.

You value the company in both these periods and then add the results to get its total value from today into “infinity” (AKA until the Present Value of its cash flows falls to near-0).

Company/Industry Research

Before you jump into Excel and start entering numbers, you should do a bit of company and industry research to establish the following:

• What are the top 5-10 most important drivers for the company?
• How can you project its revenue beyond a simple percentage growth rate? What about its expenses?
• What do its historical trends look like, ideally going back 5-10 years?

The company’s annual report and investor presentations are the best starting points.

You could also search for industry data from companies like IDC , Gartner , and Forrester , but it’s not necessary for a quick analysis of a mature company.

And if you are dealing with a rapidly changing company or a tech startup (e.g., Uber or Snap), it’s often more useful to get KPIs and financial stats from similar companies that were once growing quickly but have since matured.

In theory, you could spend days, weeks, or months on industry and company research, but that much effort is not necessary.

We recommend reading through the annual report and investor presentation to the extent that you can come up with those 5-10 key drivers .

For Walmart, we came up with the following:

Its annual filing repeatedly cited its total square feet, so we made the total retail square feet the top-line driver and based other numbers on $ per square foot figures.

DCF Model, Step 1: Unlevered Free Cash Flow

While there are many types of “Free Cash Flow,” in a standard DCF model, you almost always use Unlevered Free Cash Flow (UFCF) , also known as Free Cash Flow to Firm (FCFF) , because it produces the most consistent results and does not depend on the company’s capital structure.

Unlevered Free Cash Flow should include:

• COGS and Operating Expenses
• Depreciation & Amortization and sometimes other non-cash adjustments*
• The Change in Working Capital
• Capital Expenditures

*Depreciation & Amortization gets a bit more complicated, especially if you’re analyzing a company that follows IFRS (see the next section).

This list means that you ignore almost everything else: Net Interest Expense, Other Income / (Expense), most non-cash adjustments, most of the Cash Flow from Investing section, and the Cash Flow from Financing section.

For Walmart, many of the items in UFCF are simple $ per square foot figures:

To calculate UFCF, start with Revenue and subtract COGS , OpEx, and Taxes (which are now different since they’re based on Operating Income ).

Then, add back D&A, factor in Deferred Taxes, any other recurring operating activities, and the Change in Working Capital, and subtract CapEx:

In some cases, we recalculate items such as Deferred Taxes because we’re modifying the company’s historical Taxes to make them comparable to future Taxes.

Most of these items should be fairly low as percentages of revenue or the change in revenue.

For example, it would be highly unusual if the Change in Working Capital represented 50% of a company’s UFCF.

For most companies, Working Capital is not a major value driver because it represents simple timing differences.

We also made sure that CapEx as a percentage of revenue stays ahead of D&A as a percentage of revenue in each year because Walmart’s cash flows are growing .

Even if the growth is modest, the company will need to increase its Net PP&E over time to support that growth.

If you don’t know what some of these items mean, please see our coverage of the Change in Working Capital and Unlevered Free Cash Flow for more details.

It would also help to know a bit about the company’s operating leverage to forecast some of the expenses, but it’s not essential for a quick analysis.

But Wait! What About Operating Leases in DCF Models?

Accounting for operating leases has become more complicated with the introduction of IFRS 16 in 2019, which required companies to put Operating Lease Assets and Liabilities directly on their Balance Sheets (see: our full tutorial to lease accounting ).

The equivalent rules under U.S. GAAP aren’t too bad because U.S. companies still record Rent as a simple operating expense on their Income Statements.

Under IFRS, however, Rent is split into an Amortization or Depreciation element and an Interest element, similar to the treatment for Finance Leases.

Over a large portfolio of leases with different start and end dates, the Lease Amortization + Lease Interest is about the same as the Rental Expense under U.S. GAAP.

The goal in a DCF is to reflect the company’s cash revenue , cash expenses , and cash taxes , so we believe the best approach is to deduct the entire Operating Lease Expense in UFCF.

For IFRS-based companies, that means you’ll have to deduct the Interest element in the EBIT and NOPAT calculations:

Also, you should not add back the Operating Lease Depreciation or Amortization because in this case, it represents part of an actual cash expense .

If you follow this treatment, the UFCF number will reflect the deduction for the full Lease Expense.

Some argue that you should add back the entire Lease Expense and count Operating Leases as an item in the Equity Value to Enterprise Value bridge.

We don’t favor that approach because UFCF does not reflect the company’s cash expenses if you do that, and it’s more difficult to compare companies that way.

DCF Model, Step 2: The Discount Rate

Once you’ve projected the company’s Unlevered Free Cash Flows, you need to discount them to their Present Value : what they’re worth today.

That value today depends on how much you could earn with your money in other, similar companies in this market, i.e., your expected, average annualized returns.

The Discount Rate expresses these expected, average annualized returns, and in an Unlevered DCF, it’s equal to WACC, or the  “ Weighted Average Cost of Capital .”

The name means what it sounds like: you estimate the “cost” of each form of capital the company has, weigh them by their percentages, and then add them up.

“Capital” means “a source of funds.” So, if a company borrows money in the form of Debt to fund its operations, that Debt is a form of capital.

And if it goes public in an IPO, the shares it issues, called “Equity,” are also a form of capital.

The exact formula is:

WACC = Cost of Equity * % Equity + Cost of Debt * (1 – Tax Rate) * % Debt + Cost of Preferred Stock * % Preferred Stock

The Cost of Equity represents potential returns from the company’s stock price and dividends, or how much it “costs” the company to issue shares.

For example, if the company’s dividends are 3% of its current share price (i.e., the dividend yield is 3%), and its stock price has increased by 6-8% each year historically, its Cost of Equity might be between 9% and 11%.

The Cost of Debt represents returns on the company’s Debt, mostly from interest, but also from the market value of the Debt changing.

For example, if the company is paying a 6% interest rate on its Debt, and the market value of its Debt is close to its face value, then the Cost of Debt might be around 6%.

You also multiply that by (1 – Tax Rate) because Interest paid on Debt is tax-deductible. So, if the Tax Rate is 25%, the After-Tax Cost of Debt would be 6% * (1 – 25%) = 4.5%.

The Cost of Preferred Stock is similar because Preferred Stock works similarly to Debt, but Preferred Stock Dividends are not tax-deductible, and overall rates tend to be higher, making it more expensive.

The Discount Rate in Real Life vs. Simple Approximations

The calculations for the Cost of Debt and Preferred Stock are straightforward, but the Cost of Equity is more challenging because it’s subjective and depends on how other, similar companies have performed relative to the market.

In many DCF models, you’ll see a sheet dedicated to this calculation, where the modeler “un-levers Beta” for each peer company to estimate its risk/volatility independent of its capital structure and then re-levers it for the subject company:

The problem with this approach is that you need quick access to data for comparable companies, which may be tricky without Capital IQ, FactSet, or similar services.

Luckily, there is a “shortcut method” as well, which involves using the same formula but simplifying the last input:

Cost of Equity = Risk-Free Rate + Equity Risk Premium * Levered Beta

The Risk-Free Rate (RFR) is what you might earn on “safe” government bonds in the same currency as the company’s cash flows (so, U.S. Treasuries here).

The Equity Risk Premium (ERP) is the percentage the stock market is expected to return each year, on average, above the yield on these “safe” government bonds.

And Levered Beta tells you how volatile this stock is relative to the market as a whole, factoring in both business risk and risk from leverage (Debt).

If it’s 1.0, then the stock follows the market perfectly and goes up by 10% when the market goes up by 10%; if it’s 2.0, the stock goes up by 20% when the market goes up by 10%.

Rather than finding comparable companies and un-levering and re-levering Beta, you could just look it up for the company on Yahoo Finance:

You can then combine it with easy-to-find data on 10-year U.S. Treasury yields and the Equity Risk Premium from Damodaran’s collection (or other sources – there are plenty of estimates for the current ERP in different markets):

The Discount Rate is around 4.0% with this approach (assuming ~90% Equity and ~10% Debt for Walmart), close to the 4.37% in the full model.

Sure, you could make it more complicated, but I would argue it’s a waste of time in a case study or modeling test unless they specifically ask for it.

The important part is that the company’s Discount Rate is closer to 5% than 10% or 15%, so we can use a range of values with 5% in the middle.

Also, you can now use this Discount Rate to take the Present Value of each UFCF (PV = UFCF / ((1 + Discount Rate) ^ Year #):

DCF Model, Step 3: The Terminal Value

The Terminal Value goes back to the “big idea” behind a DCF model.

Put simply, the “Company Value” in this formula:

IS the Terminal Value – assuming that each input represents the Terminal Period in the DCF model.

To calculate it, you need to get the company’s first Cash Flow in the Terminal Period and its Cash Flow Growth Rate and Discount Rate in that Terminal Period.

In an Unlevered DCF, this formula becomes:

Terminal Value = Unlevered FCF in Year 1 of Terminal Period / (WACC – Terminal UFCF Growth Rate)

And you can estimate the UFCF in Year 1 of the Terminal Period like this:

Terminal Value = UFCF in Final Year of Explicit Forecast Period * (1 + Terminal UFCF Growth Rate) / (WACC – Terminal UFCF Growth Rate)

This “Terminal Growth Rate” should be low : below the long-term GDP growth rate, especially in developed countries.

You could also estimate the Terminal Value with an EBITDA multiple based on median multiples from the comparable companies, but we don’t recommend that as the primary method.

It’s too easy to pick multiples that imply ridiculous Terminal FCF Growth Rates, so it’s safer to start with the growth rates and then check their implied multiples .

Once you have the Terminal Value, you can discount it back to Present Value and add it to the Sum of the Present Values of the Free Cash Flows:

And then, you can back into the Implied Equity Value and Implied Share Price from there:

You can also set up a sensitivity analysis in Excel to assess what the company’s valuation looks like with different assumptions for the Terminal Growth Rate, Terminal Multiple, Discount Rate, and so on:

One Final Note: This Terminal FCF Growth Rate should be fairly close to the UFCF growth rate in the final year of the explicit forecast period.

You don’t want UFCF to grow at 10% or 20% and suddenly drop to 2% in the Terminal Period.

If it does, you need to re-think your assumptions or extend the analysis.

Because of this problem, we extended the explicit forecast period to 20 years in the Uber valuation .

Conclusions from This DCF Model

Overall, Walmart seems modestly undervalued because its implied share price in most of the sensitivity tables is above its current share price of ~$140.

There is one problem with this analysis, though: we’re assuming that Walmart keeps growing its retail square feet, even though that number has been declining in recent years.

Therefore, if we had more time and resources, we might create a few operating scenarios, similar to the Uber and Snap models, to assess the results in “growth” vs. “stagnant” vs. “decline” cases.

Common Criticisms of the DCF Model – and Responses

People often criticize the DCF model for the following reasons:

• “But how can you possibly predict a company 5, 10, or 15 years into the future? No one can!”
• “The DCF is too sensitive to small changes in assumptions, such as growth rates and margins.”
• “A DCF ignores market conditions and comparable companies, so it might not give you the accurate market value.”
• “The DCF is no longer applicable because stocks are valued based on memes / crypto / Reddit! No one cares about cash flow.”

My response to the first three objections is similar: it’s not about the exact numbers but ranges, scenarios, and sensitivities .

No, you don’t know whether the Year 10 growth rate will be 10% or 8% or 12%, but you should have an idea of whether it will be closer to 10% or 20%.

And if you don’t, it’s fine to build a DCF with a wide valuation range that reflects high uncertainty.

The complaint about a DCF being “too sensitive” raises other questions: for example, is the FCF growth rate in the final year of the explicit forecast period close to the Terminal FCF Growth Rate?

If not, you need to re-think your assumptions or extend the projections.

And the critique about ignoring market conditions conveniently ignores that the Discount Rate is always based on current market conditions, no matter how you calculate it.

The DCF is indeed less reflective of the current market than comparable company analysis (for example), but it still reflects some market conditions.

And finally, for the crypto/meme/Reddit objection: yes, I agree that certain stocks seem to defy all logic and cash flow-based analysis.

That said, these stocks represent a tiny fraction of all the public companies worldwide.

The media gives them excessive attention, but they ignore the hundreds of thousands (millions?) of other companies that follow some semblance of logic.

And as for crypto, I agree that you cannot use a DCF to value Bitcoin, Ethereum, or Dogecoin.

But this is nothing new: a DCF only works for assets that generate cash flow , whether now or in the future.

No one has ever suggested valuing gold or silver with a DCF, and I’m not sure how crypto is any different in this regard.

DCF Models: Further Learning

If you want to learn more about DCF models and get a step-by-step walkthrough in more detail, sign up for our free financial modeling tutorials .

These tutorials provide a 3-part series on the valuation of Michael Hill, a retailer in Australia and New Zealand, and they go into each step in more depth than we did above.

And if you want in-depth case studies backed by real-world data and research, the Core Financial Modeling course delves into valuation/DCF analysis in even greater detail:

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.

A few modules are dedicated to valuation and DCF analysis, and there are example company valuations in other industries.

If you want even more complex examples, the Advanced Financial Modeling course might be more appropriate since it deals with topics like the mid-year convention, stub periods, a normalized terminal year, and net operating losses in a DCF:

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.

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.

• 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 .

• Real Estate

Home » Business » Free Case Study Templates & Examples (Word, PDF)

Free Case Study Templates & Examples (Word, PDF)

A case study template is used to make a report of an event, problem, or activity in an effective way. With the help of this template, you don’t have to think that what to include in your report. However, you just have to focus on a person, group, or event you are studying.

Table of Contents

What is a case study?

Generally, a case study is a kind of research methodology and usually used in social sciences. If people do a case study then they have to study an issue from a real-life perspective. Moreover, such type of studies demands in detail analysis of a person, a group, or an event. You may also like Remote Work Policy Template .

Different Types of Case Study Templates?

There are different types of templates that you can use for your case study;

• Student case study template
• Nursing case study template
• Clinical case study template
• Basic psychology case study template
• Treatment injury case study template

In addition, you can also create these templates on your own or you can download them from any website. There are no specific formats for these templates. You have to create or download those templates that suit your own needs.

What are the benefits of using a case study in business?

Using case study in the business provides you several benefits such as;

• A case study assists you to influence customers more efficiently. It makes you able to raise awareness on how to fulfill the requirements of customers more efficiently.
• Next, the information in the report will help you to encourage empathy in its readers. If you use the real-life issues and then explain how your product solves those issues will encourage the customers to use your product.
• Moreover, when you discuss the customers’ issues and questions in your case study it shows that you care and understand the customers.
• With the help of this report, you can strengthen your brand. Hence, it depends on real-life situations, it becomes relatable. You can also develop an emotional relationship with your customers. Furthermore, this report shows that you have everything that customers need.
• Above all, you can use this report to create other written works for your business. You should also check Employee Clearance Form Template .

Basic Psychology Phase One Case Study Template

Blog and case study template, clinical case study template, community engagement case study template, firebird sql case study template, grow’s case study template, ibm case study template, jisc case study template, memorial hospital case study template, nursing case study template, school education case study template, social values and health priority setting case study template, student case study example template, ultimate case study template, how to write a case study.

Here are some guidelines that you bear in mind during or before writing a case study;

At first, you should identify what type of case study you are going to perform. You should strategize your case study well.

Next, in business, you have to pay attention to customers. If you are going to sell products or services, then think about your customers and their needs.

Most importantly, gather extensive knowledge about your project before making a case study. Without enough knowledge, you won’t be able to create an effective document. You may also see Employee Handbook Template .

If you want to present, the best side of your business then finds those customers who have the best experience with your product or services. Then, include their testimonials in your report.

In conclusion, case study templates make you able to create a study that surely makes your business stand out.

You May also Like

Sharing is caring!

I am Ryan Duffy and legal writer. I received a bachelor of business administration (BBA) degree from London Business School. I have 8+ years of writing experience in the different template fields and working with ExcelTMP.com for 7 years. I work with a team of writers and business and legal professionals to provide you with the best templates.