how to make a financial statement in excel

• 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

Create a Financial Statement using Microsoft Excel

Taught in English

Instructor: Paula Del Rey

34,077 already enrolled

Guided Project

Recommended experience

Intermediate level

Basic knowledge in finances and spreadsheets

(365 reviews)

What you'll learn

Understand the components and variations of a Financial Statement

Complete a Transactions page and Profit and Loss statement that summarizes monthly transactions.

Understand the components of a Balance Sheet.

Skills you'll practice

• Spreadsheet
• Financial Statement
• Calculations
• Balance Sheet

Details to know

Add to your LinkedIn profile

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

Learn, practice, and apply job-ready skills in less than 2 hours

• Receive training from industry experts
• Gain hands-on experience solving real-world job tasks
• Build confidence using the latest tools and technologies

About this Guided Project

By the end of this project, you will be able to complete a financial statement with Microsoft Excel, composed of a transactions page, profit and loss statement, and balance sheet. You will learn how to enter your business transactions and interpret the data presented in the profit and loss statement. You will also learn about the components of a balance sheet and will have a better understanding of how a financial statement can be used to help track and present financial information for your company.

Learn step-by-step

In a video that plays in a split-screen with your work area, your instructor will walk you through these steps:

Log into Microsoft Excel Online and open a blank spreadsheet.

Identify the assumptions for the financial statement and create a Transactions page.

Create a Profit and Loss statement that summarizes monthly transactions.

Use the SUMIFS formula to populate the categories for the Profits and Loss with the transactions.

Understand the components of a Balance Sheet and complete the Assets section

Complete the Liabilities and Owner’s Equity sections of the Balance Sheet and perform a final balance check.

6 project images

Instructor ratings

We asked all learners to give feedback on our instructors based on the quality of their teaching style.

The Coursera Project Network is a select group of instructors who have demonstrated expertise in specific tools or skills through their industry experience or academic backgrounds in the topics of their projects. If you're interested in becoming a project instructor and creating Guided Projects to help millions of learners around the world, please apply today at teach.coursera.org.

How you'll learn

Skill-based, hands-on learning

Practice new skills by completing job-related tasks.

Expert guidance

Follow along with pre-recorded videos from experts using a unique side-by-side interface.

No downloads or installation required

Access the tools and resources you need in a pre-configured cloud workspace.

Available only on desktop

This Guided Project is designed for laptops or desktop computers with a reliable Internet connection, not mobile devices.

Why people choose Coursera for their career

Learner reviews

Showing 3 of 365

365 reviews

Reviewed on Nov 26, 2023

Outstanding Course, straight to the point and very convenient for all people

Reviewed on Feb 8, 2023

The overall course was good but balance sheet should also be thought by using Microsoft excel formulas, that was usually used for the larger organisations

Reviewed on Jul 24, 2023

I felt this guided course was clear and informative to the subject matter.

You might also like

Coursera Project Network

Grab Data Fast with Vertical and Horizontal LOOKUP

University of Colorado Boulder

Everyday Excel, Part 1

New to finance 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

Are guided projects available on desktop and mobile.

Because your workspace contains a cloud desktop that is sized for a laptop or desktop computer, Guided Projects are not available on your mobile device.

Who are the instructors for Guided Projects?

Guided Project instructors are subject matter experts who have experience in the skill, tool or domain of their project and are passionate about sharing their knowledge to impact millions of learners around the world.

Can I download the work from my Guided Project after I complete it?

You can download and keep any of your created files from the Guided Project. To do so, you can use the “File Browser” feature while you are accessing your cloud desktop.

How much experience do I need to do this Guided Project?

At the top of the page, you can press on the experience level for this Guided Project to view any knowledge prerequisites. For every level of Guided Project, your instructor will walk you through step-by-step.

Can I complete this Guided Project right through my web browser, instead of installing special software?

Yes, everything you need to complete your Guided Project will be available in a cloud desktop that is available in your browser.

What is the learning experience like with Guided Projects?

You'll learn by doing through completing tasks in a split-screen environment directly in your browser. On the left side of the screen, you'll complete the task in your workspace. On the right side of the screen, you'll watch an instructor walk you through the project, step-by-step.

More questions

How to use Excel for financial statement analysis: 8 brilliantly simple tips

In today's fast-paced business environment, you need a tool like Microsoft Excel to analyze financial data quickly and accurately. In this guide, we'll explore how to effectively use Excel for financial statement analysis. Read on to learn just how this can help you make better decisions and drive success in your organization.

We'll cover the benefits of using Excel for financial statement analysis, the steps to prepare and present your analysis, and why Zebra BI for Office is the ultimate tool to help you take it to the next level.

Although some have reservations about using Excel for a financial statement analysis, it's actually a super powerful tool.

Why prepare financial statement analysis in Excel?

The truth is, Excel is packed with numerous advantages that make it the go-to tool for finance professionals around the globe. Let's have a look at a few reasons why it's a powerful ally when it comes to financial statement analyses:

• Automation : Excel can help you streamline your financial analysis process. Once you've entered the relevant formulas for calculations such as financial ratios, KPIs, and trend analysis, Excel will automatically update these values as you input new data. This one-time formula entry saves time and effort, allowing you to focus on making data-driven decisions.
• Accuracy : Excel's precise calculations ensure accuracy and consistency across your financial analysis. With reliable results, you can be confident in your conclusions and present stakeholders with credible reports.
• Flexibility : Need to adjust your calculations or presentation style? Excel enables you to customize your analysis easily, making it simple to adapt to changing business needs or new insights.
• Data Management : Excel is excellent for managing and organizing large volumes of financial data. This capability allows you to draw meaningful conclusions while providing a high-level overview and the option to dive deeper into the details.
• Collaboration : Excel facilitates seamless collaboration by allowing you to share your financial statement analysis with colleagues. Multiple users can work on the same document, streamlining the reporting process.
• Visualization : By visualizing your financial statement analysis in Excel, you can make it much clearer and more efficient. While Excel does offer some visualization options, they are pretty limited. A powerful add-in like Zebra BI for Office can help you streamline the process & uncover actionable insights in seconds. This way, you can quickly identify critical information such as variances against the previous year or plan.

Now that we've established why it's crucial to use Excel for financial statement analysis, let's dive into how to prepare and present your analysis effectively.

How to prepare financial statement analysis and present results in Excel

To effectively use Excel for financial statement analysis, you need to follow a systematic approach. But don't worry, it's easier than it sounds. Here's how you can go about it:

1. Gather financial information

Begin by collecting all the relevant financial data. The key here is granularity – you want detailed data that allows you to dig into specific areas of your financial statements. This could be data broken down by product line, business unit, region, or any other segment relevant to your organization. The more detailed your data, the more insights you can uncover.

2. Organize data and analyze your financial statements

Organization is crucial when dealing with financial data. Excel allows you to separate your raw input data from your analysis calculations which you should absolutely do. This way, you can maintain clean, easy-to-read financial reports that inspire confidence in their credibility. Remember, a messy report can lead to confusion, errors, and ultimately, a loss of credibility.

3. Allow traceability by using linking formulas

Make sure every calculation can be traced back to its source data . This makes it easier for others to understand your work and for you to spot any potential errors. It also promotes transparency, accuracy and credibility in your financial analysis.

4. Calculate key financial ratios & compare them to targets and industry benchmarks

Excel excels (pun intended) at computations. It allows you to easily calculate key financial ratios such as gross profit margin, EBITDA margin, and debt-to-equity ratio. These provide invaluable insights into your company's financial health. Comparing them to your internal targets and industry benchmarks can help you gauge your performance and identify areas for improvement .

• Income statement: gross profit margin, EBITDA margin, operating profit margin, earnings per share, operating expenses as % of sales
• Balance sheet: current ratio, quick ratio, debt-to-equity ratio, debt-to-assets ratio, Net debt-to-EBITDA

5. Spot the main drivers of variances against the previous year and plan

Identifying variances is crucial in financial analysis. Once you do, you can dig deeper into the general ledger/trial balance to find explanations for these variances. This enables you to address any issues promptly.

6. Have supporting sheets ready for a fully data-driven storytelling approach

Supporting sheets can include breakdowns of sales, costs, and any other relevant financial data, enhancing your financial story with context and details . This will help you create a compelling narrative around your performance.

7. Use Pivot Tables to facilitate data grouping and insight discovery

Pivot Tables are like the Swiss Army knife of Excel, featuring all sorts of different tools that you can use to make sense of your data. You can use them to summarize and analyze large datasets, which makes it easier for you to draw actionable insights. By grouping your data in different ways, you can also use them to uncover patterns and trends that might be less obvious in a standard table format.

8. Visualize financial statements

A well-designed, professional-looking report can enhance your credibility and make your conclusions more persuasive. By delivering meaningful insights in a way that is clear and easy-to-understand, it makes it easier for the decision-makers to take the right steps faster . Here are some tips to help you with this:

• Display financial statements in a table with financial periods (previous year, actuals, forecast, and plan) in adjacent columns to identify trends and variances easily
• Clearly denote units in your analysis and presentation to avoid incorrect conclusions
• Incorporate comments into your report to answer questions upfront and guide your readers through it

These steps will help you use Excel for financial statement analysis effectively, allowing you to maximize the insights you gain from your data.

Zebra BI: The Ultimate Tool for Visualizing Your Financial Statement Analysis in Excel

Excel is powerful, but Zebra BI for Office takes your financial statement analysis to the next level. By integrating seamlessly with Excel, it enhances your analysis and helps you make smarter, more informed decisions .

It streamlines data visualization , allowing you to create stunning and insightful reports fast and with zero effort. The best part? It's IBCS -compliant out of the box! With its easy-to-use interface and customization options, it enables you to quickly adapt your analyses to suit your company's needs.

The tool also offers advanced data management capabilities , empowering you to handle large data sets with ease. Oh, and have we mentioned top-notch collaboration features available for PRO plans? They enable your team to work on your reports and dashboards together, saving you time, maintaining consistency & boosting your productivity.

Why settle for good when you can have the best?

Get Zebra BI for Excel today and unlock the full potential of Excel for financial statement analysis. Unsure if that's what you're looking for? Try out Zebra BI for Office for free. It comes with a free instructional file that will help you get started.

Related Resources

Excel Report Templates: Build Better Reports Faster

Master the Art of Data Transformation: Use Power Query to Transform Data in Excel

The Ultimate 2023 Bar Chart Guide

Stacked Bar Chart in Excel: How to Create Your Best One Yet

One comment, leave a comment cancel reply.

Want to join the discussion? Feel free to contribute!

Save my name, email, and website in this browser for the next time I comment.

Privacy Policy

Legal documentation

Try it in your Excel. For free.

Openai rewriter.

Want to download financial statements in Excel?

In this article, we'll take a look at the steps that you need to complete to prepare a basic set of financial statements in Excel. Most financial statements are compiled from trial balance values and this is also our recommended approach.

You therefore need to have a trial balance available to be able to prepare your financial statements. Most financial statement software programs start with a trial balance which means that you should have an accounting or bookkeeping solution which enables you to record individual income and expense entries to compile your trial balance.

Go to our unique Excel accounting templates

Once you have your trial balance ready, each account in your trial balance needs to be linked to the appropriate section in the financial statements. The easiest way of setting up these links is to use pre-defined reporting class codes which are basically defined for each line in the financial statements.

If you've defined a code for each line in the financial statements, you can link each of your accounts in your trial balance to the appropriate codes so that the linked account is included in the appropriate line on the financial statements. A SUMIFS formula can typically be used for this calculation.

Now that you've defined a link between your trial balance and financial statements, you should be able to include all the trial balance info in your financial statements but what about financial information which is not included in the trial balance?

Most financial statements consist of information which can be obtained from a trial balance and information which is typically not included on a trial balance. Examples are depreciation rates, asset addition or disposal amounts, par value of shares, contingent liabilities and more.

When preparing financial statements in Excel, you would therefore also need to set up an efficient way of including the information which typically does not form part of a trial balance. Our preference is to also use reporting class codes for this purpose and to link the affected lines in the financial statements to a sheet with the information not found on a trial balance.

After setting up all the links to your trial balance and other financial information, the next step would be to make it easy to switch between financial years so that you do not need to create these links for each set of financial statements that you prepare. The easiest way of accomplishing this is to use a formula like VLOOKUP or MATCH to pick up the correct annual column based on a user-defined cell for selecting the required financial year.

We've only really covered the basics here but as you can see, it can be a relatively complex exercise to prepare a model which enables users to download financial statements in an Excel format. Especially if you want the financial statements to be practical and easy to use!

Why attempt to reinvent the wheel?

You can save hundreds of hours by buying our 100% unique financial statements template which makes it easy to prepare financial statements in Excel and to roll them forward for the next financial year!

How to Make Income Statements Using Excel

• Small Business
• Accounting & Bookkeeping
• ')" data-event="social share" data-info="Pinterest" aria-label="Share on Pinterest">
• ')" data-event="social share" data-info="Reddit" aria-label="Share on Reddit">
• ')" data-event="social share" data-info="Flipboard" aria-label="Share on Flipboard">

How to Compute Annual Gross Pay in Excel

How to enter cash expenses in quicken, how to add retroactive pay in quickbooks.

• How to Make a Profit & Loss Spreadsheet
• How to Make a Square Bubble Chart in Excel

An income statement is typically prepared at the end of the year or the beginning of a new year when all financial information has been wrapped up and reported, but you can create one at any time using Microsoft Excel. With Excel, you can recalculate parts of the sheet without having to drag out a calculator because Excel’s auto-calculations will do it for you. Get an at-a-glance snapshot of your organization’s financial fitness at any time with an Excel income statement.

Open Microsoft Excel. Click into the top cell (A1) and type the name of the organization for which you are preparing the income statement, such as “ABC Wood Creations.” Double-click the line separating columns A and B so your income statement title fits.

Press the “Enter” key and type the year to which the income statement applies, such as “For December 31, 2010.”

Press the “Enter” key twice to leave a line below the header information. You should now be in cell A4. Type “Revenue” and then highlight the text in the cell. Click the “B” icon in the Font portion of the ribbon to boldface the text.

Click into the cell catty-corner below the header (B5). Type the first category from which the organization derives money, such as “Product Sales.” Press the “Tab” key to move over to cell C5. Type the total amount of money you received from that category.

Press the “Enter” key or click into cell B6. Type in the next income category, such as “Donations.” Press the “Tab” key to move into cell C6 and type the total amount received during the year. Continue entering data into cells in these two columns until complete.

Click into the cell below the last item in the “Revenue” section and type “Revenue Summary.” Leave the corresponding cell in the “C” column blank.

Click into the “A” column in the next row and type “Expenses.” Below that, click into the catty-corner “B” column and type the first expense category, such as “Employee Salaries.” Press the “Tab” key. In column “C,” enter the total expenses in this category during the year.

Click into the cell below “Employee Salaries” and input additional payment categories such as “Lease,” “Insurance” and “Charitable Giving.” Enter the corresponding finances in column C. After all expenses are entered, type “Total Expenses” in the cell below and leave the corresponding “C” cell blank.

Highlight all of the cells in the first part of the income statement for the “Revenue” section in column C, but do not highlight the blank cell below them. Click the “AutoSum” button on the right side of the ribbon. Excel calculates your incoming finances in this cell. If you change any of these numbers, Excel will recalculate.

Highlight all of the cells in the “Expenses” section but not the blank cell. Repeat the “AutoSum” process to get your expenditure figure.

Add and calculate other areas of the income statement pertinent to your organization such as losses, taxes and extraordinary expenditures. This is optional.

Click the “File” tab. Click “Save As” and enter a name for the income statement. Click the “Save” button.

• University of California Santa Barbara: Sample Single-Step Income Statement
• The income statement described in this article is merely an example to familiarize you with the procedure for entering data and reviewing it in Excel. Although business categories are used, you can swap them out for personal ones such as “groceries” and “rent" to make the income statement fit your specific needs.

Fionia LeChat is a technical writer whose major skill sets include the MS Office Suite (Word, PowerPoint, Excel, Publisher), Photoshop, Paint, desktop publishing, design and graphics. LeChat has a Master of Science in technical writing, a Master of Arts in public relations and communications and a Bachelor of Arts in writing/English.

Related Articles

How to create a formula for income & expenses in excel, how to create a sales budget on excel, how to create income statements in excel, how to build a chart in excel, how to keep track of credit and debit using excel, how to organize business expenses, how to make an itemized deductible list for a personal business on excel, how to budget for your annual expenses with quickbooks, how to calculate operating expenses in excel, most popular.

• 1 How to Create a Formula for Income & Expenses in Excel
• 2 How to Create a Sales Budget on Excel
• 3 How to Create Income Statements in Excel
• 4 How to Build a Chart in Excel

Share this page

Financial management

Keeping your finances in check is easy to start-and maintain- when you use an excel budget planning template in your financial management routine. customize an excel template to suit your unique financial management needs, whether you're balancing the books of a small business or keeping track of your household budget..

Budget your personal and business finances using these templates

Manage your finances using Excel templates. Stay on track for your personal and business goals by evaluating your income and expenses. Use these templates to add in pie charts and bar graphs so that you can visualize how your finances change over time. Create infographics that show what categories are included in your budget and the types of factors that incorporate each category. Download your financial management template so that you can access it and edit it as you need.