Running your business requires you to manage dozens of tasks each day, and delivering a quality product or service to your customers is the first priority. Some owners become overwhelmed by daily business operations and neglect their financials, which may cause big problems down the road. Use this list to evaluate your financial fitness and to keep your business on track. Read more
We often ask clients to run their Quickbooks Profit and Loss reports for the “trailing twelve month” period. Need some help on actually doing that? Read on:
1. Create the Standard Profit & Loss report (Reports > Company & Financial > Profit & Loss Standard).
2. Change the dates to the year desired (for a calendar year from January 1 to December 31 for the year desired)
3. Change the Columns to Display to Month (as indicated by the arrow below)
4. Optionally, click Collapse to see just the main accounts and one column for each month
Your report will look like this:
We often ask for multiple years of financial statements from our clients. Here is the easiest way to run one simple report to send over.
1. Start by running a report in QuickBooks
(the ‘Profit and Loss Standard’ & ‘Balance Sheet Standard’ are suggested formats)
2. Customize the Report columns
To make the import process more efficient its best to import a number of periods at once, PlanGuru will allow you to import up to 36 periods of financial data in a single import. Your QuickBooks report can also be modified to include multiple columns of results by month or year. Do this by clicking “Customize Report”, next with select month (or year) from the “Display columns by” drop down box, and finally update your date range (note QuickBooks will allow you to create a report with 36 or more columns)
The result is a QuickBooks report with one column for each month or year of the selected date range.
3. Export the Report to Excel
You will need to get the report in a format that can be opened in Excel. To do this click the Excel tab and select ‘Create New Worksheet’
4. Choose csv format, this produces the best results.
The default selection sends the file to an xls format, which provides account information in inconsistent columns, this is no good for importing into PlanGuru.
Instead choose to the .csv output, this will put all of your account information into one column, making it ready to import.
IF YOU ARE SENDING FILES TO US… YOU CAN STOP HERE! We’ll make them pretty once we get them. But if you are using this for your own purposes… go ahead and read on. This is a great way to get useable Excel reports!
5. Use Excel’s Text to Columns to separate account numbers.
If your QuickBooks company includes account numbers this feature will allow you to parse out both portions of the account number and description into separate columns. With your csv report open in Excel start by inserting a blank column into the report.
Next highlight the combined account number & description string and click on the ‘Data’ tab, and ‘Text to Columns’
In the Text to columns wizard select ‘Delimited’ in the first dialog. In the second dialog select ‘Other’ and copy and paste the character QuickBooks uses as a delimiter into the text box to the right of other. Then click finish to complete the text to columns process.
The result is a QuickBooks export with account numbers and descriptions in separate columns. NOTE: rows with that are still mixed, or that don’t have account numbers are parent account and total rows. Its fine that these aren’t formatted the same, because we don’t want to import these into PlanGuru (they will result in double counting). These rows will get filtered out during the import process.
For more questions on the Excel “text to columns” functionality see this Microsoft forum post: http://technet.microsoft.com/en-us/library/ee692872.aspx
Quantive is a veteran owned and operated financial services firm. We work exclusively on matters related to corporate value: business valuation, value growth, and M&A advisory.
Quantive Business Valuations
Mid Atlantic Office
1945 Old Gallows Road, Suite 350
Tysons Corner, VA 22182
New Jersey Office
2500 Harborside Financial Centrer, Plaza 5
Jersey City, NJ 07314