How To Do A Profit And Loss Statement In Excel

admin media

How To Do A Profit And Loss Statement In Excel – Years ago, I was explaining to my boss that I was feeling bored, and they told me to learn how to read a Profit and Loss (P&L) statement. At the time, it sounded suspiciously like, “Stop wasting my time,” but working in an executive role has changed my perspective a little: this is an incredibly useful thing to learn. The P&L statement is a map of the company’s performance and is an effective tool to point you to the most pressing areas to dig into.

While there is a lot of depth to reading a P&L, this will go from zero to one, and hopefully take less than thirty minutes. I will begin by reviewing the components of the P & L statement, explain the steps I use to review the P & L, show an example of using those steps, and end with instructions for obtaining public company P & L statements for practice.

How To Do A Profit And Loss Statement In Excel

How To Do A Profit And Loss Statement In Excel

In order to examine the P & L statement, we need the P & L statement to read, and I chose the Consolidated Summary of Financial Statements on page 18 of HashiCorp’s S-1 filing. It will be useful to have that nearby for reference everywhere.

What Is A Profit And Loss Statement? P&l Example

There’s a lot there! First let’s look at the top topics. The first three columns show the income for 2019 through 2021. All the numbers here are in “thousands”, which means that $18,503 is actually $18,503,000 and so on. The last two columns look at six-month windows. We are trying to get a general sense of the business, to focus on annual numbers.

When looking at internal data, it is very important to understand where the table goes from historical data to forecast data. It is very common when you talk to an early stage startup that they will include their optimistic forecast for the current year as part of their financial data. Public companies, on the other hand, did not share the assumptions. All S-1s, 10-Ks and similar documents and historical information. There are cases when the reported data initially looks like an estimate, for example, some companies end their Financial Year in January, this is the case here at Gitlab: Their 2021 Financial Year only runs through January 31, 2021, so their 2021 results

Forecast although this report was issued on November 4, 2021. (As a result, most of Gitlab’s 2021 performance will only be available in their Fiscal Year 2022 report.)

There are a few more rows, but everything else you can ignore from a business sense perspective.

What Is Profit & Loss Account Statement?

Finally, it’s worth taking a moment to dig into GAAP vs Non-GAAP. The Financial Accounting Standards Board defines the accounting rules known as GAAP, Generally Accepted Accounting Principles, and most of the financial statements you will see will describe themselves as GAAP compliant or not, for example this part of HashiCorp’s 10-K report.

There is little consistency in how companies calculate their non-GAAP financials, which makes them tricky to figure out. Most of the time, companies do not include non-recurring or one-time costs. Assuming you’re looking at your company’s internal P&L, your best bet is to ask someone on your Finance team to walk you through how any non-GAAP numbers differ from the GAAP statement. Non-GAAP often provides a clearer understanding of a business’s operating situation, but the purpose of any non-GAAP measure is

Now that we’ve covered the components of the P&L, let’s dig into the analysis. The first step to take is to take us to our spreadsheet to do a bit of basic math, in this case using Google Sheets. We’ll start with columns to track year-over-year (YoY) growth.

How To Do A Profit And Loss Statement In Excel

Once you have a version of the table that you can edit, the remaining steps to view the report are:

Profit And Loss (p&l)statement For Amazon Sellers

The last step we will take is when we collect our questions. Before that, we’ll go through the table row by row and use the first three steps, starting with the entry:

Another question for each of these lines of business is what percentage of revenue comes from repeat customers and what percentage comes from new business? The key to a good SaaS business is a healthy renewal rate: you need a small sales force to drive revenue growth if the product does a good job of maintaining existing revenue.

Next, look at the “cost of income.” Of particular interest are areas where cost growth is accelerating or decelerating in line with revenue growth. For example, licensing revenue grew by 96% from ’20 to ’21 and costs grew by a modest 82% over the same period. This means they get some savings in their growth. In contrast, it would certainly be interesting to dig into cloud services revenue and costs, where revenue grew only 75% from ’20 to 21 while costs grew 246%. Not understanding the strategic role of cloud services, the P&L doesn’t tell a particularly optimistic story about its trajectory.

Sometimes interesting questions arise from changes over time, for example it is interesting that the growth in support costs exceeded the growth of support income in ’20, but not in ’21. Understanding why support costs grew more slowly in ’21 than in ’20 will lead to important insights into how the industry works.

Profit And Loss Statement Meaning, Importance, Types, And Examples

Looking at total revenue growth versus total revenue growth, there is a slight trend toward revenue growing slower than expenses in ’20 (155% vs 125%) and just slightly faster than expenses in ’21 (75% vs 72%). However, this is a situation where looking at percentage growth is tricky. The overall numbers tell a much healthier story, as explained in the gross profit line and strong positives across the board.

Next are operating costs. Sales and marketing (S&M) spending accelerated substantially in ’20 and decreased in ’21 on a baseline basis. However, on an overall basis S&M costs grew by approximately $50 million in both years. That’s a big step and it’s worth digging into where the spending is going. It’s always useful to look at the relative size of all operating divisions, and I personally find it surprising that “General and administrative” (G&A) is a larger operating cost than “Research and development” (R&D) and I’d like to understand that. a little better.

Finally, a quick look at the net loss. This is not a profitable business, and losses are mounting. However, losses grew slower than revenue growth in ’21 and grew

How To Do A Profit And Loss Statement In Excel

Slower than revenue growth in ’20. Understanding what caused the swing (at least part of it was G&A expenses doubling from ’20 to ’21) will make the path to profitability clearer.

Key Metrics: Balance Sheet Vs Income Statement (example With $aapl)

Okay, so let’s finish by considering how we can dig into each of the surprising areas (from an internal executive’s perspective). What I’ve found to work best is to schedule questions with a group you can follow, send them your questions, and then schedule a time to discuss.

After conducting these discussions, you will have a clearer understanding of the reality of your business. Before you do this exercise, you can

If you understand your business, but you have been relying on other people’s interpretations for understanding. Now it’s your understanding to drive your confidence.

. That’s true in an obvious sense, but in reality your understanding is suspended until the next P&L iteration. For example, we reviewed HashiCorp’s S-1 which covers their performance through January, 2021. They shared their results through January, 2022 in their 10-K issued in March, 2022. On page 77, they have their results for 2022 (things that directly , results. through January, 2022).

Income Statement: How To Read And Use It

I won’t go into the full details, but if you review this spreadsheet that combines their S-1 and 10-K results, you can tell they had a rough year. HashiCorp is far from alone in this regard, almost everyone has a difficult year, but if they rise to the challenge, they can come out of this crisis as a very profitable company. This uncertainty is at least part of why I don’t generally advise people to try to make financially sound moves during a downturn.

A short issue of obtaining P&L statements for public companies. All of the numbers shared in this piece are public record, and you can find them by going to the Securities and Exchange Commission EDGAR search and typing in the company name. Using HashiCorp as an example, I started typing “hash” after the search suggested the HashiCorp tag, “HCP”, and clicked on the HashiCorp, Inc. page. From there click on “View Files” and you can see all the files of interest, especially the S-1 and 10-Ks.

Most companies will have an investor relations (IR) website, such as HashiCorp’s which has a link to their most recent filings on this page that hosts HashiCorp’s quarterly results. In general, it’s easy to use EDGAR, but the IR website will often support the company’s preferred reporting through their earnings calls and press releases. Even if you want to understand the company’s preferred story, I would recommend that you read their P&L without any report first to avoid misdirecting your attention in their preferred direction.

How To Do A Profit And Loss Statement In Excel

As a closing thought, P&Ls for early stage companies are often wrong, and they can be wrong in many different ways. Costs can

Restaurant Profit And Loss: Complete Guide [free Template]

Profit & loss statement excel, how to create a profit and loss statement in excel, profit and loss statement, how to make profit and loss statement in excel, how do i make a profit and loss statement, how to do a profit loss statement, excel profit and loss, profit and loss statement excel, how to do profit and loss statement, profit and loss statement format in excel, how to prepare profit and loss statement, how to do a profit and loss statement in excel

Also Read



Leave a Comment