You can easily compare this year’s income and expenses to last year’s, and calculate the difference both in dollars and as a percentage using a report generated by Sage 50. You don’t even need to send it to Excel to calculate the change. One of the build in financial statements, <Standard> Income 2 yrs, gives us a good start. You can find it by going to the Reports & Forms menu and choosing Financial Statements. Find <Standard> Income 2 yrs in the list and double click it to view the report. At the Options window, I like to uncheck the Show Zero Amounts box so the report only shows accounts with balances. Click the OK button to accept the options and view the report. What you get is a basic income statement with columns for current month and year to date columns for both this year and last year.
At this point many people would send the report to Excel if they wanted to calculate the change from last year to this year. But we can very easily add those calculations to the report. Click the Design button that is at the top of the report window. In the designer, you’ll see a column of grey buttons down the left side of the window. Double click the large button labeled Column Desc or right-click on it and choose Properties. Here we can define the columns for our financial statement. Go to the first unused line (that should be line 6 on this report) and check the Print box. For the Contents choose Formula. Type Variance for the title. Moving to the Options section at the upper right, set Align Title to Right Of Column. You can also adjust the column width if you want. Now to calculate the from last year to this year, in the Formula Activity section, set the first Column field to 2. Below that choose Subtract from the drop down list and set the other Column field to 3. That will subtract column 3 (current month last year) from column 2 (current month this year). Now use the Move UP button to move the formula into the column 4 position.
Now let’s add a column to show the percent changed. Go to line 7, check the Print box and set the Contents to Percentage. Enter a title such as Var %. This column can be narrower. I usually set it to around 8. And align the title to the right. In the Percentage section set the first Use Column field to 4 (the variance amount) and the second one to 3 (last year’s amount). Use the Move Up button to make this column 5.
Now repeat that process for the year to date columns. The variance formula will use columns 6 and 7 (This YTD and Last YTD) and the percentage will use columns 8 and 7 (YTD variance and last YTD). You won’t need to move these formulas. When you have finished, click OK to return to the main design window.
This report has a second row of column titles. Since we added columns we’ll need to adjust those titles. Below the Column Desc button is Column Title. Double-click it. You’ll need to remove the This Year title from column 4 and Last Year from column 5, and re-enter them in columns 6 and 7 respectively. Click OK to save your change and return to the design window.
Click the Preview button to view your new financial statement. If everything is the way you want it, click Save and enter a new name for your report. If you want to make more changes, click the Design button to return to the financial statement designer.
If you only want the percentage variance on your report, click the Design button, double click on Column Desc and uncheck the Print box on both formula rows.
