Video: QuickBooks to Excel Reporting
April 12, 2025 | Issue #117
Big Deal Small Business: QuickBooks to Excel Tips
April 12, 2025 | Issue #117
This is a blog written by Kaustubh Deo // Guesswork Investing about acquiring & operating small businesses. If you are a new reader / new searcher, please start here.
Testing Something New!
Now that this blog is not written anonymously, it opens up some new forms of content I can provide you all with. So today, I’m trying out sharing a Loom video for the first time.
This Loom video walks through how I take a QuickBooks export and turn it into a clean Excel reporting template. This is relevant for searchers when they receive a QuickBooks export from a seller. It’s relevant for operators who are looking to build out clean financial reporting in a relatively low-pain manner (nothing related to QuickBooks is painless).
I was inspired to create this video based on this exchange on Twitter:
I do love writing, but modeling & financial reporting is much more easily shown through live demonstration. I’d love your feedback on this format if you take the time to watch it. Link below!
Sponsor Information
Big Deal Small Business is sponsored by Live Oak Bank, a leading self-funded search fund lender.
To learn more, reach out to Lisa Forrest and Sarah Andrews at Lisa(dot)Forrest(at)LiveOak(dot)Bank and Sarah(dot)Andrews(at)LiveOak(dot)bank.
Mention that Kaustubh at Big Deal Small Business sent you!
Video Link & Outline
Without further ado, here’s the Loom video. Below is the outline of the video, written by Loom AI (pretty cool tool I use a lot these days):
Introduction 0:00
This guide is designed for both searchers and operators looking to streamline financial data from QuickBooks into Excel.
It consists of two main parts: setting up a template and updating it monthly.
Setting Up the Template 0:44
Request monthly data from the target company, ideally for three years, but this guide will focus on one year.
Start by deleting unnecessary rows in the QuickBooks export to avoid formatting issues.
Add two columns to the left of the data for categorization purposes.
Creating a Clean Template 2:05
Rename the first sheet to 'Clean' and the second to 'QBO Raw'.
Organize revenue items into categories such as 'Tree Pruning', 'Tree Removal', and 'Other Revenue'.
Calculate total revenue based on these categories.
Cost of Goods Sold (COGS) 3:18
Include relevant costs such as labor, other COGS, and marketing expenses.
Format these items for clarity using Excel's alignment features.
Overhead Expenses 5:15
Identify and categorize overhead expenses like insurance, legal fees, and office expenses.
Ensure all relevant expenses are captured, including smaller items that may seem insignificant.
Allocating Line Items 7:45
Allocate each line item to the appropriate category, ensuring no totals are mistakenly included.
Use individual line items for accuracy in financial reporting.
Creating Monthly Models 12:18
Use the SUMIFS function to create a monthly model that sums values based on specified criteria.
Ensure the criteria range matches the data structure to avoid errors.
Adding Quarterly and Annual Summaries 16:13
Extend the model to include quarterly summaries by adjusting the SUMIFS criteria.
Set up the model to accommodate future months and years by using flexible cell references.
Finalizing the Model 24:40
Check the model for accuracy by comparing totals against the original QuickBooks data.
Ensure that the model is adaptable for future updates by maintaining a consistent structure.
Conclusion
QuickBooks is a headache and a half at times, but hopefully this helps you navigate a bit more smoothly when moving it into Excel.
Let me know your thoughts on both the content & format, I’m always curious to hear your feedback.
Just hit reply to this email or find me on Twitter.
Thanks,
Kaustubh // Guesswork Investing