Budget reports

tom-LelandNC

I am new to Quicken, and now have about 2.5 months of data. I am looking for a nice clean budget variance report. Can anyone suggest their favorite.
Second- how do I eliminate Investments accounts from reports? For instance, when I run the cash flow report, I do not want investments. I only want certain income accounts (which right now are social security and pension) and the expenses I have traccked. Thanks in advance for repsonses.

jacobs

Tom, that's two separate questions. Let's start with the last one first…

For any reports you generate from the Reports menu, you can click on Edit, and then the Accounts tab. Click the Selected Accounts radio button and deselect the Investment accounts. Easy.

The budget report, unfortunately, does not have a nice & easy answer. This is an area of the program which the developers have inexplicably ignored for years, so there is very limited functionality. You can't do any budget report from the Reports section of the program. The only report to show budget versus actual is if you open your budget and print it. But that's not so useful, because it shows all 12 months, and it doesn't even total across the months.

The only place you can see year-to-date budget versus actual is in the columns to the right of the category names. If your second column is January, click the "<<" icon to open the YTD columns; then click the "v" icon to toggle between Jan-December and Jan-current month. So that's good, right? Unfortunately, not really. First, these columns don't print when you print the budget. Second, it always shows actual versus budget through the current month. So if you view it today, March 10, you're seeing actual income and expenses through March and budget through the end of March; but what you likely want is to see is YTD through the end of the prior month (e.g. Jan 1-Feb 28) so you're comparing apples to apples. And there is no report to do that. So if you look on the last day of March, and all your transactions are up-to-date, you can view — but not print — your year-to-date through March. But don't sleep on March 31; if you wait until April 1 to look at it, the YTD column will be showing the budget through April, which you don't want. I wish that was an April Fool's joke, but it's not. It's just a gaping hole in the program.

If you're somewhat adept with a spreadsheet (Excel, Numbers, or Google Sheets), you can get the YTD report you want by exporting the budget, deleting the future months, summing the actual columns, summing the budget columns, creating a difference column, and then copying those cells down through all the rows, and totaling them. (Even totaling the columns its tricky because the budget rows show both main categories and subcategories, and you can't include both in your totals.) If you're comfortable with a spreadsheet, you can get there reasonably quickly. But it's annoying to have to build each month. 

If you're an advanced spreadsheet user, you can build a spreadsheet with formulas for each month such that so you just have to export the Quicken report and paste it into one sheet, select your month, and see a YTD report in another sheet. I have an Excel file with three sheets: the first is where I paste in the export from Quicken; the second builds 12 columns of YTD actuals and 12 columns of YTD budget values from the first sheet; the third is my YTD report where I enter the month number and it pulls the appropriate actual and budget values from the second sheet. This allows me to generate a YTD budget report in 5 seconds without needing to create or edit any formulas — but building such a spreadsheet requires some proficiency.

The developers somehow thought their job was done when they provided a way to print the budget or dump it into a spreadsheet. Sadly, this seems to indicate that no one on the development team or management team actually uses the budget in Quicken Mac in real life, or they would understand how problematic this is. I continue to hope this is at or near the top of their development to-do list, but it's been several years since they added budgets and a modern reports engine... and so far, there's still no useful budget report. Fingers eternally crossed…

jacobs

Tom, that's two separate questions. Let's start with the last one first…

For any reports you generate from the Reports menu, you can click on Edit, and then the Accounts tab. Click the Selected Accounts radio button and deselect the Investment accounts. Easy.

The budget report, unfortunately, does not have a nice & easy answer. This is an area of the program which the developers have inexplicably ignored for years, so there is very limited functionality. You can't do any budget report from the Reports section of the program. The only report to show budget versus actual is if you open your budget and print it. But that's not so useful, because it shows all 12 months, and it doesn't even total across the months.

The only place you can see year-to-date budget versus actual is in the columns to the right of the category names. If your second column is January, click the "<<" icon to open the YTD columns; then click the "v" icon to toggle between Jan-December and Jan-current month. So that's good, right? Unfortunately, not really. First, these columns don't print when you print the budget. Second, it always shows actual versus budget through the current month. So if you view it today, March 10, you're seeing actual income and expenses through March and budget through the end of March; but what you likely want is to see is YTD through the end of the prior month (e.g. Jan 1-Feb 28) so you're comparing apples to apples. And there is no report to do that. So if you look on the last day of March, and all your transactions are up-to-date, you can view — but not print — your year-to-date through March. But don't sleep on March 31; if you wait until April 1 to look at it, the YTD column will be showing the budget through April, which you don't want. I wish that was an April Fool's joke, but it's not. It's just a gaping hole in the program.

If you're somewhat adept with a spreadsheet (Excel, Numbers, or Google Sheets), you can get the YTD report you want by exporting the budget, deleting the future months, summing the actual columns, summing the budget columns, creating a difference column, and then copying those cells down through all the rows, and totaling them. (Even totaling the columns its tricky because the budget rows show both main categories and subcategories, and you can't include both in your totals.) If you're comfortable with a spreadsheet, you can get there reasonably quickly. But it's annoying to have to build each month. 

If you're an advanced spreadsheet user, you can build a spreadsheet with formulas for each month such that so you just have to export the Quicken report and paste it into one sheet, select your month, and see a YTD report in another sheet. I have an Excel file with three sheets: the first is where I paste in the export from Quicken; the second builds 12 columns of YTD actuals and 12 columns of YTD budget values from the first sheet; the third is my YTD report where I enter the month number and it pulls the appropriate actual and budget values from the second sheet. This allows me to generate a YTD budget report in 5 seconds without needing to create or edit any formulas — but building such a spreadsheet requires some proficiency.

The developers somehow thought their job was done when they provided a way to print the budget or dump it into a spreadsheet. Sadly, this seems to indicate that no one on the development team or management team actually uses the budget in Quicken Mac in real life, or they would understand how problematic this is. I continue to hope this is at or near the top of their development to-do list, but it's been several years since they added budgets and a modern reports engine... and so far, there's still no useful budget report. Fingers eternally crossed…

tom-LelandNC

Jacobs,
Thank you for taking the time to explain so thoroughly. I am gald the first answer is an easy one. I am a little shocked that a personal bookkeeping system like this has ignored such a common rep[ort. When you take the time to enter a budget, you generally want to see how you are doing against that budget. Luckily for me, the main purpose for using Quicken this year is to track expenses. At the end of the year, I can do some simple comparisons to what I had budgeted, but more importantly, I will have a more accurate picture of my expenses. I am retired and have moved, and now living on a fixed income. Tracking expenses will help us know what to expect in the future.
Again, thank you for taking the time to reply. YOu ahve helepd me before!

Tom