Financial Health
How to Improve Your Time & Expense Tracking
To improve your time and expense tracking, you need software that provides smart timesheets, receipt capture, reminders, and more.
Microsoft Excel Tips and Tricks – Formatting a QuickBooks Export
Microsoft Excel Tips and Tricks – Formatting a QuickBooks Export - BQE Software
In my previous post, Planning Your Projects for 2015 I showed how to take a report from QuickBooks, Export it to Excel and then after some quick maneuvers around the spreadsheet, I was able to get at the total # of projects for the year, and based on that, average revenue per project. Now I want to show you some Microsoft Excel Tips and Tricks which will explain how I got the data from what it looked like when it was originally exported to Excel to what it looked like when I started the analysis.
The short answer is that QuickBooks Exports the customers and jobs in a separate column for each level (Customer:Job Level1:Job Level2:Job Level3). The Microsoft Excel tips and tricks I show you in this video will demonstrate how you can re-purpose the layout such that you have a simple linear report showing each project with all 3 levels on the same line along with the corresponding revenue for that project for the period you are reporting on. This enables you to write a simple COUNTA formula in Microsoft Excel to get a quick count on the # of projects. Then it’s an easy division (Sales / # of Projects) to get average sales (or Revenue) per project.
Microsoft Excel makes it easy to get at this information quickly. Then if you refer back to my previous post on Planning Your Projects for 2015 you can see how we start using this information in conjunction with a report in BillQuick to look for opportunities in 2015 to increase revenue without having to increase resources (ie expenses).
Taking the time to do this kind of analysis using QuickBooks, Microsoft Excel, and ultimately the kind of intelligent information that BillQuick provides us can go a long way to helping you identify opportunities to increase sales and profits while maintaining or improving efficiency. This is how you can work smarter and not harder in 2015. I hope to bring you more helpful tips along these lines to ensure that you have the best 2015 possible. Please post your comments and questions below.