Josh Aharonoff
Aug 31, 2023
Welcome to another week of Legit Numbers!
This week, we'll try to understand the connections between Financial Statements, ways to calculate Budget vs Actuals, and some of the most used an Excel Formulas + a Cheat Sheet for you to download.
What we’ll be covering in this edition:
Understand Financial Statements and how they are connected
8 Ways to calculate Budget vs Actuals
The Excel Formulas Cheat Sheet
Let's dive in...
Whether you work in Finance & Accounting…
are running a business…
or just be a well rounded person.
Understanding financial statements will add tremendous value to our life.
and if you are still struggling…I've got you covered
Let’s do a deep dive:
➡️ THE BALANCE SHEET
This statement tells you the most information about a business.
The concept is really simple…
What you OWN (assets)
Was funded by amounts owed to CREDITORS (liabilities)
and Owners (owners equity)
and your owners equity has a special account called Retained Earnings.
That account is actually just a summarized balance of the details behind your Profit & loss…
➡️ THE PROFIT & LOSS
This statement is designed to tell you one thing:
how PROFITABLE your business is each period
It’s separated by:
Revenue → IE your income
Cost of Goods Sold → the cost to deliver your sales
Operating Expenses → the cost to operate your business
Other Income / Expense → income and expense accounts not related to your core business
Income taxes → the taxes you pay on the businesses profits
And then you can understand the profitability of your business with 4 metrics found on this statement…
Gross Profit → Revenue - COGS
Net Operating Income → Gross Profit - Opex
Net Other Income → Other Income - Other Expenses
Net Income (before or after tax) → all income accounts, less all expense accounts.
This Net income account flows through into your Balance Sheet under Retained Earnings
➡️ THE STATEMENT OF CASH FLOWS
This statement is designed to tell you one thing:
where your cash is going
No…cash is NOT the same as profits.
You can be insanely profitable, but have a slow collections cycle, leading to cash constraints.
The Statement of Cash flows is separated by 3 sections:
Cash from Operating Activities → This shows you the cash movements from operating your business…
starting with your net income…adding back your depreciation & amortization…
and factoring items such as cash collected from customers, cash paid to suppliers etc.
Cash from Investing Activities → This shows you the cash movements from the long term assets that you invest in…
IE fixed assets (equipment, machinery, land)
intangible assets (patents, copyrights, domains)
and long term investments (bonds)
Cash from Financing Activities → This shows you the cash movements from items related to financing your business…
IE you raise capital from investors…
or repay a loan
➡️ PUTTING IT ALL TOGETHER
As you can see…the Balance Sheet is the one true statement of the business…
the other statements just provide extra detail not found on your Balance Sheet
The Income Statement PUSHES net income to the Balance sheet via Retained Earnings
The Statement of Cash Flows PULLS from the balance sheet for all accounts other than cash, to back into where your cash is going
Do you have anything to add?
This is a really important report…
in fact, it’s my favorite
Why?
Because it gives insight into just how much of an understanding that you have on the business.
No..you don’t need to have 0 variances
But you do need to have a story on each line item, and why there was a hit / miss
Let’s dive into each way that you can showcase Budget vs Actuals:
1. TABLE
This is one of the simplest and most common ways to showcase Budget vs Actuals
The idea here is:
→ include both a $$ variance as well as a % variance to understand materiality
→ show good variances as positive, bad variances as negative
→ use conditional formatting for good / bad variances
2. KPI
The KPI box is one of my favorite ways to display data in a pretty manner
You can include whatever you’d like here, as long as you don’t crowd the box with too much data
3. DONUT
This may be my favorite of them all
This is usually the way companies showcase battery life (take a look at your apple watch)
I find it a great way to shade in the positive or negative % of your variances, in a manner similar to the KPI box
4. SPEEDOMETER
This is just like the Donut…but here you have a dial
5. BAR WITH CUT OFF LINE
Here you have a bar chart showcasing the actual numbers you did…
with a cut off line chart for where you had hoped you would be
6. CLUSTERED BAR CHART
Here you have both your budget and your actuals column…
and you can showcase positive or negative variances by stacking a new bar chart to showcase the difference
7. WATERFALL
Waterfall charts are neat in that they allow you to see the cumulative impact of values
Here, you can showcase the cumulative impact of your net income…
or your cash flows
8. THERMOMETER
Here, we have similar elements to the Bar with Cut off line chart…only we can see the positive / negative variance more easily with color coding
Those are my 8 ways to showcase budget vs actuals
Remember…how you display the information is just one part.
Having a story with each KPI is where you really get to shine!
What has your experience been with budget vs actuals?
And which is your favorite?
This cheat sheet contains close to every formula I’ve ever used in excel 👀😮
📣 Help me spread my love for excel with the world by Liking, Commenting, and Sharing this post 📣
Let’s do a walk through of the different categories, and when to use each
➡️ SUM
These are fairly simple - they allow you to sum values in a range
• SUM → simplest way to sum values
• SUMIFS → A great way to sum values based off of multiple criteria
• SUMPRODUCT → Similar functionality to SUMIFS, but much more powerful & flexible
• SUBTOTAL → Allows you to perform any mathematical function on a range, and doesn’t count cells with formulas
➡️ LOOKUPS
These are really common → they return a value that you enter into your lookup formula
• VLOOKUP → Allows you to pull the value in the column of a range you specify
• HLOOKUP → Allows you to pull the value in the row of a range that you specify
• XLOOKUP → Allows you to pull both the row & column in a range you specify
• INDEX/MATCH → Similar to Xlookup, and combines two powerful functions
• GETPIVOTDATA → Allows you to lookup values in a pivottable
➡️ DATES
These are formulas that allow you to work flexibly with dates
• DATE → Returns the date value after you specify the Year, Month, and Day
• NETWORKDAYS → calculates the net working days between 2 days
• DATEVALUE → calculates the date value of a cell formatted as text
• EOMONTH → returns the last day of the month after however many specified months from a cell
• EDATE → returns the date of a cell after X amount of months specified
• NOW → returns the current date & time
• TODAY → returns the current date
• MONTH → pulls the month value
• YEAR → pulls the year value
➡️ CONDITIONAL / BOOLEAN
These are also really popular, and allow you to return results that meet a specific true / false criterion
• IF → Returns one value if TRUE, another value if FALSE
• AND → returns TRUE if ALL conditions are met, otherwise FALSE
• OR → returns TRUE if ANY conditions are met, otherwise false
• SUMIF(S) → sums a range if criteria is met
• IFERROR → returns a value if an error is found
• COUNTIF(S) → counts a range if a critieria is met
• AVERAGEIF(S) → averages values if a critieria is met
• ISBLANK → returns TRUE if a text is blank
• ISNUMBER → returns TRUE is the cell is a number
• COUNTA → counts cells if there are values
• NOT → returns TRUE if a cell is not a criteria that you specify
And much much more that I can’t fit in a post of only 3k characters 😇
But lucky for you…you can grab this cheat sheet for free in high resolution (for now) via the link on my site
⬇️ Click the inforgaphic below to get your copy today ⬇️
I hope you gained valuable insights into EBITDA, Month End Close processes, and Revenue Forecasting with this week's edition of Legit Numbers!
If you have any questions or need further assistance, feel free to reach out. I reply to all my emails personally :)
Till next Thursday!