Josh Aharonoff
May 18, 2023
Welcome to another information packed episode of Legit Numbers!
This week's digest covers a variety of essential financial metrics, as well as the differences between my 4 favorite: EBITDA, Gross Profit, Net Income, and Cash Flows.
We'll also explore the an in depth tutorial on how to create a PivotTable Profit & Loss.
What we’ll be covering in this edition:
The Periodic Table of Financial Metrics
Gross Profit vs Net income vs EBITDA vs Cash Flows
Learn how to build a PivotTable Profit & loss
Let's dive in...
Where Chemistry meets Profitability (and much more)...
1️⃣ OCF
Operating Cash Flow
Net Income + Other Non-Cash Items - Changes in Working Capital
2️⃣ FCF
Free Cash Flow
Operating Cash Flow - Capital Expenditures
3️⃣ CCC
Cash Conversion Cycle
Days of Inventory Outstanding + Days of Sales Outstanding - Days of Payables Outstanding
4️⃣ NCF
Net Cash Flow
Operating Cash Flow + Investing Cash Flow + Financing Cash Flow
5️⃣ DCF
Discounted Cash Flow
CF1 / (1+r)1 + CF2 / (1+r)2 + ... + CFn / (1+r)n, where CF is cash flow, r is the discount rate, and n is the number of periods.
6️⃣ PV
Present Value
CF / (1+r)^t, where CF is cash flow, r is the discount rate, and t is the number of periods.
7️⃣ FV
Future Value
CF x (1+r)^t, where CF is cash flow, r is the interest rate, and t is the number of periods.
8️⃣ PP
Payback Period
Initial Investment / Annual Cash Flow
9️⃣CR
Cash Ratio
(Cash + Marketable Securities) / Current Liabilities
🔟 CB
Cash Burn
Cash from Operating Activities + Cash from Investing Activities
1️⃣1️⃣ UFCF
Unlevered Free Cash Flow:
EBIT x (1 - Tax Rate) + Depr & Amort - CapEx - Incr in Net Working Capital
1️⃣2️⃣ LFCF
Levered Free Cash Flow:
EBITDA - Taxes - Capital Expenditures - Changes in Net Working Capital - Interest Expense
1️⃣3️⃣ GP
Gross Profit
Total Revenue - Cost of Goods Sold (COGS)
1️⃣4️⃣ EBITDA
Earnings Before Interest Taxes Depr and Amort
Net Income + Int Expense - Int Income + Taxes + Depr + Amort
1️⃣5️⃣ EBITDA (M)
EBITDA Margin
EBITDA / Revenue
1️⃣6️⃣ OI
Net Operating income
Gross Profit - Operating Expenses
1️⃣7️⃣ NI
Net Income
Total Revenue - Total Expenses
1️⃣8️⃣ NIM
Net Income Margin
Net Profit / Revenue
1️⃣9️⃣ GPM
Gross Profit Margin
Gross Profit / Revenue
2️⃣0️⃣ ROI
Return on Investment
Net Profit / Total Investment
2️⃣1️⃣ ROE
Return on Equity
Net Profit / Shareholders Equity
2️⃣2️⃣ ROA
Return on Assets
Net Profit / Total Assets
2️⃣3️⃣ NOI
Net Other Income
Other Income - Other Expense
2️⃣4️⃣ EPS
Earnings per Share (EPS)
Net Profit / Total Shares Outstanding
2️⃣5️⃣ OM
Operating Margin
Operating Income / Revenue
2️⃣6️⃣ PE
Price-to-Earnings
Price per Share / EPS
2️⃣7️⃣ ROCE
Return on Capital Employed
Operating Profit / Capital Employed
…and many more!
⬇️ Download this in high resolution by clicking the infographic
These 4 metrics are CRUCIAL to understand
They all tell you something different, and have their own time & place
1️⃣ GROSS PROFIT
➡️ What it means
▪️ The amount that’s left over from your revenue after you subtract the cost to deliver your product or service
➡️Where it’s found
▪️ The Profit & Loss
➡️ What’s the formula
▪️ Revenue - COGS
➡️ Why it's important
▪️ It helps you understand the maximum amount of money your business can earn - if you have a negative gross profit, you don’t have a business!
2️⃣ NET INCOME
➡️ What it means
▪️ The net profitability of your business for a specific period of time - IE all income less all expenses
➡️ Where it’s found
▪️ The profit & Loss
➡️ What’s the formula
▪️ Revenue - COGS - Operating Expenses + Other Income - Other Expenses
➡️ Why it's important
▪️ This is the ultimate number that you are generating in profitability from your business - if you keep posting losses, you will need to raise capital to sustain your operations
3️⃣ EBITDA
➡️ What it means
▪️ Earnings before Interest, Tax, Depreciation, and Amortization
➡️ Where it’s found
▪️ No where - it’s compiled separately and is a non GAAP Metric
➡️ What’s the formula
▪️ Net Income + Interest Expense - Interest Income + Taxes + Depreciation + Amortization
➡️ Why it's important
▪️ Many feel that EBITDA is a good approximation for cash flows. It is also commonly used to value businesses
4️⃣ Cash Flows
➡️ What it means
▪️ How much cash went in and out of your bank account
➡️ Where it’s found
▪️ The Statement of Cash Flows (or by taking the ▲ in cash on your balance sheet)
➡️ What’s the formula
▪️ Cash from Operating Activities + Cash from Investing Activities + Cash from Financing Activities (Or ending cash - beginning cash)
➡️ Why it's important
▪️ Cash is king - you can have the most profitable business in the world, but if your expenditures keep outpacing your receipts, you’ll have to raise capital to sustain your operations
If I had to focus on one, which would it be?
For me...Gross Profit.
Everything else falls into place once you have great margins...
But ofcourse, there are tons of factors to consider
A few weeks ago I did a post on the benefits of creating a PivotTable Profit & Loss (check it out over here https://lnkd.in/ebSeFRMu)
Now I’m here to explain step by step how you can create one yourself
1️⃣ Export your Profit & Loss
Start by exporting your Profit & Loss from your accounting software on a monthly basis
If you don’t have an accounting software, you can go ahead and create your own profit & loss
2️⃣ Add 2 columns for Grouping 1 and Grouping 2
These 2 columns will allow you to aggregate your data in your PivotTable so you can have the right mix of detail vs summary as you expand and collapse
3️⃣ Convert your Profit & Loss to a table
Highlight your data, and hit Control + T. There are so many benefits to working in a table format…one of the biggest being your ability to load it into the Power Query Editor
4️⃣ Load your Data into Power Query
Power Query may be my favorite excel tool - there’s so much you can do with it.
Think of it like a way to write macros on your data with a simple click of a button
Anytime you refresh your data, those “macros” get applied
To load into Power Query, navigate to “Data” and then under “Get & Transform” hit “From Table / Range”
5️⃣ Unpivot your data
Now that you’re in the Power Query editor, we’re going to unpivot our dates so that they show in a column instead of across multiple columns going vertically
Simply click the columns you don’t want unpivoted, and click “unpivot other columns”
This is my favorite Power Query function!
6️⃣ Set your PivotTable
Now that we have our data ready to use in our PivotTable, let’s add fields to our rows in this order
Grouping 1 > Grouping 2 > Account
Then for the values we’ll add our value field
And for the columns, we’ll add our date
7️⃣ Set a custom list
This is looking good, but we have one last important step - creating a custom list so we can sort for the correct order of our P&L
To do that, navigate to File > Options > Advanced > Custom List
Then write out the custom list for Grouping 1
Navigate back to your PivotTable and hit Sort
8️⃣ Add a timeline slicer & make it all pretty
Now let’s make a bit pretty by changing the styling, adding a timeline slicer, and giving a nice title
There you go! You just created a PivotTable Profit & Loss
Click the image below to watch this TikTok
And that concludes another insightful edition of Legit Numbers.
I hope you found the discussions on financial metrics, EBITDA, Gross Profit, Net Income, Cash Flows, and how to build a PivotTable P&L insightful.
If you have any feedback or suggestions for future topics, I'd love to hear from you. Simply reply to this email and share your thoughts.
Thank you for your continued support, and I look forward to bringing you more finance insights in the weeks to come.
See you next week!