Josh Aharonoff
Aug 17, 2023
Welcome back to another exciting Thursday!
In this episode, we're diving into the world of Excel charts and their best applications, demystifying Accounts Payable vs Accounts Receivable, and uncovering the essential tools that make up the CFO Tech Stack to help you grow efficiently.
What we’ll be covering in this edition:
Every Excel Chart, and When to Use it 📊📈
Accounts Payable vs Accounts Receivable
The CFO Tech Stack
Let's dive in...
Charts are one of the most powerful features that Excel offers…
and each one has their own time & place for when you should use them.
Let’s do a walk through of each chart:
📊Bar charts → helps you understand trends over time. • Bar chart → single series
• Stacked bar chart → multiple series grouped together
• Clustered bar chart → multiple series side by side
• Horizontal → alternate orientation
📈 Line chart → helps you understand trends over time, with a line • Line chart → can show 1 or multiple series. I like to utilize 2 series when I’m trying to understand the intersection (like with a break even analysis)
🟡 Pie chart → helps you understand the distribution between multiple categories • Pie Chart → shows the split / distribution between multiple categories
Hierarchy charts → helps you understand the hierarchy of data • Treemap → provides a hierarchal view of data by color and size
🔢 Statistical charts → provide a visual representation of data distributions, trends, and patterns • Histogram → Shows the underlying frequency distribution
Waterfall chart → Helps in understanding the cumulative effect of sequentially introduced positive or negative values This is especially relevant when showcasing MRR and ARR
Scatter charts → often used to find correlations and patterns in the data • Scatter Chart → displays the relationship between 2 numerical variables
Combo charts → allows you to combine data from multiple series Helpful when you have information across 2 distinct series (like Revenue and Gross Margin)
🗺️ Map Charts → provide a geographical representation of data
PivotCharts → allows you to control chart data from a PivotTable This may be the most powerful of them all, as it allows you to take any chart, and connect it to a PivotTable…
That’s my take on Excel charts, and when to use each.
There’s a whole lot more to be said about how to structure your data, and how to design your charts to captivate your audience 🤩
What would you add about Excel Charts?
2 BIG areas of Finance & Accounting…
and many organizations have entire departments dedicated to these 2 functions.
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
Got anything to add?
After working with 100+ companies in my career…
I’ve been exposed to TONS of tools.
These tools are vital in helping us:
→ work efficiently
→ reduce errors
→ reduce costs
→ save time
Here’s an overview of what each of these tools do
• ACCOUNTING SOFTWARE
This can be a traditional accounting software….or a full fledged ERP
The idea is that instead of utilizing a spreadsheet, you can leverage:
→ automatic bank feeds
→ integrated & dynamic reporting
→ bank reconciliations
and so much more
• AP MANAGEMENT
Almost every company has bills to pay…
and many are still processing them manually from their bank…
or worse…via check 🤮
With an AP platform, you can:
→ upload bills right from your inbox
→ categorize & sync bills to your accounting software
→ collect the necessary approvals
→ process payments directly from one platform
• PAYROLL & HRIS
We’ve come a long way with payroll.
No one does this by hand anymore - everyone uses some form of a payroll company.
Payroll platforms help you:
→ onboard new employees
→ process paychecks, with withholding taxes
→ remain in compliance
• EXPENSE REIMBURSEMENTS
People are always spending money on their personal cards…
it’s a popular way to rack up points.
Expense reimbursement softwares make it easy for you to manage the repayments, allowing you to:
→ upload receipts
→ generate expense reports
→ process payments
• VIRTUAL CREDIT CARDS
Instead of dealing with the headache of expense reimbursements…
why not give your employees a virtual credit card?
With virtual credit cards you can:
→ create a card
→ set a limit
→ destroy a card
→ control which vendor they can pay
all in a matter of seconds.
This is one of my favorite tools in this list
• TAX & LEGAL
Taxes are notorious for being complicated and difficult to file.
The same holds true for legal matters…which is a common aspect of your cap table
I love working with tools that allows me to stay in compliance..
without having to read up on all the legalities 🧐
• REVENUE & CONTRACT MANAGEMENT
Got 40+ customers? Don’t make the mistake of managing that all in excel.
Sure, Excel is my favorite tool on this list…but you need something much more robust.
Something that can:
→ calculate various metrics (MRR, NDR, CAC etc.)
→ manage contract changes, both retroactively and prospectively
→ calculate revenue & deferred revenue
• BANKING & TREASURY
We all remember what happened earlier this year with SVB…
but thankfully, they aren’t the only ones providing banking solutions
I’m a much bigger fan of using a well known bank as opposed to a regional bank…
as the bigger guys have a lot of integrations & easy to use platforms, which is key for scaling.
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!