Josh Aharonoff
Jun 22, 2023
Welcome, finance enthusiasts!
In this edition of Legit number we are going to be covering a LOT
We'll start by helping you understand how the 3 Financial Statements are all connected via an easy to digest graphic, followed by a comparison of AR vs AP, and lastly, end with a pretty dashboard
What we’ll be covering in this edition:
The Financial Statements Mindmap
Accounts Payable vs Accounts Receivable
Month over Month Financial Dashboard
Let's dive in...
Learn about the Financial Statements and how they are all connected with this simple infographic
The Financial Statements are a popular topic in Finance & Accounting
Almost everything revolves around them
But how do they work? And how are they connected?
Let’s dive in:
➡️ The Profit & Loss
This statement is all about telling you what your income, and your costs
Your income is summarized by
1️⃣ Revenue → income related to your core business
2️⃣ Other Income → income related to none core activities (like credit card points)
Your expenses are summarized by:
1️⃣ Cost of Goods Sold → Costs that relate to carrying out your product or service
2️⃣ Operating Expenses → Costs that relate to operating your business
3️⃣ Other Expense → Costs that don’t relate to carrying out your income, or operating your business
The bottom line metric in a Profit & loss is your NET INCOME, which takes all income accounts, less all expense accounts
The P&L is a SELFISH financial statement…it pushes it’s data to the other 2 statements, but doesn’t care about any activities from the other 2 statements when presenting it’s information
*Note - it’s common to have journal entries that relate to both the Profit & Loss and the Balance Sheet
➡️ The Balance Sheet
This statement tells you all about the financial position of your company
It is summarized by:
1️⃣ Assets → Amounts of Economic value that business owns, or substantially controls
2️⃣ Liabilities → Amounts that you owe to creditors
3️⃣ Owners Equity → Amounts that you owe to the owners
The Balance sheet is presented on a cumulative basis, which is the only statement of the 3 that acts this way...
and it PULLS from the Income Statement via an account called RETAINED EARNINGS...
which is a cumulative balance of your NET INCOME from your Profit & Loss
➡️ The Statement of Cash Flows
This statement is all about telling you where your cash is going
It is separated by:
1️⃣ Cash from Operating activities → this section relates to cash movements from operating your business
2️⃣ Cash from Investing activities → This section relates to cash movements from fixed & long term assets (IE, Assets that are invested into the business for long term benefit)
3️⃣ Cash from Financing activities → This section relates to cash movements from amounts invested by owners, or creditors - both for amounts put in, and amounts repaid
The Statement of Cash Flows is the NEEDIEST of all 3 statements…in fact, it doesn’t even present you with any new data!
It simply pulls from the other 2 statements, as shown in the mindmap below
That’s my take on the 3 Financial Statements, and how they are all connected
Get your copy of the infographic by clicking the image below
They are the yin and yang of the money you OWE and the money you’re OWED
Each have their own quirks, and ways to analyze
Let’s get into it
➡️ What do they mean?
Accounts Payable → Money you owe to suppliers for goods or services purchased
Accounts Receivable → Money customers owe you for sales generated but not yet paid
➡️ Where do they show up on your financial statements?
They are both part of your working capital, and appear on your balance sheet
Accounts Payable → Current Liabilities
Accounts Receivable → Current Assets
The movements in these accounts get shown on your statement of cash flows in your Cash from Operating Activities
➡️ What are the journal entries?
Accounts payable → Goes up with a credit, and down with a debit
Accounts receivable → Goes up with a debit, down with a credit
➡️ Why are they important?
These 2 accounts can cause wild swings in your cash flows
Accounts Payable → the more favorable your credit terms with suppliers, the stronger your cash position
Accounts Receivable → the quicker you collect your cash, the less bad debt, and the more favorable your cash position
➡️ What are some formulas around these?
1️⃣ Accounts Payable Formulas:
Accounts Payable Turnover → this measures how many times a company pays off its accounts payable balance in a specific period
Formula=Purchases on credit / avg accounts Payable
Days Payable Outstanding (DPO) → Represents the average number of days it takes a company to pay its suppliers
Formula=Accounts Payable / Purchases on Credit * number of days
2️⃣ Accounts Receivable Formulas:
Accounts Receivable Turnover → this measures how many times a company can convert its accounts receivable balance into cash in a given period
Formula=Net Credit Sales / Avg AR balance
Days Sales Outstanding (DSO) → this measures how long it takes on average to collect again your receivables
Formula=Accounts Receivable / Net Credit Sales * Number of Days
Bad Debt Expense ratio → This show you how much you can expect to have in bad debt for each dollar in AR
Formula=Bad debt expense / Total Credit Sales
That’s my take on AP & AR
Showcase what happened, and how it compared to last month
OK, let’s talk about how I made this!
➡️ You can do a lot more in excel than you realize
Many people think excel is just for crunching numbers…
but you can do SO MUCH more in excel, especially when it comes to design
Many people showcase their data using software’s like powerbi, tableau, looker…
and these are all great
But you can also get your excel sheets set up in a way that looks just as pretty as these tools
and the best part? You won’t have to learn how to use any new platforms
➡️ Get familiar with shapes
Many people strictly use cells in excel - another big mistake!
Shapes are a big way in which you can make your excel reporting come alive
They give you the flexibility of controlling your graphics without worrying about column widths, or row heights
and when set up properly, you can easily group & reuse shapes for other areas of your dashboard (like I did over here)
➡️ Get familiar with Icons
I love alt codes (my favorite is alt + 3 + 0 to get this symbol ▲)
But like all things, alt codes have their limitations… and you can’t control the full details of your design with them
That’s where icons come in
There’s no limit to the amount of icons out there for whatever you’re trying to explain
and when you edit the color of those icons to be relevant to your color scheme
then you get a real home run in terms of the quality of the design 💥
➡️ Get familiar with copying and pasting as picture
It’s pretty common to take your dashboard and then plop it into a tool like powerpoint or google slides when you are ready to present your findings
While you can stay entirely in excel, it’s common to use a presentation software, since you’ll move likely include a lot of other content around other areas of the business
When it comes time to get your information into powerpoint, it’s actually REALLY simple
Just follow these steps
1/ copy your excel range(CTRL + C)
2/ paste your range as a picture in excel (ALT + H + V + U)
3/ cut your picture from excel (CTRL + X)
4/ paste your picture into powerpoint (CTRL + V)
If you can do all of that with your keyboard, you’ll save a ton of time & effort
Those are my tips for creating dashboards in excel
Get your copy of this dashboard by clicking the image below
I hope you enjoyed this week's edition of Legit Numbers!
If you haven't already replied to say hello, please do and let me know how I can help - I reply to all of my emails personally :)
Till next time!