Intacct: Financial Report Writer – Dimensional Reports

• September 26, 2016
• Kathy Jastrzebski

Financial reports are the basis of vital business decisions guiding the direction and shaping the future of a company.

Imagine you are the CFO of a company. You noticed in February that revenue didn’t go up as much as sales did. The sales numbers show that new sales were $200,000 monthly, while revenue only increased by $150,000. With these new customers being added, revenue should surely go up by $200,000. That’s the logical conclusion, right? But what if there’s something happening that you don’t see? What if you’re not accounting for cancellations or free months that a sales representative gave a customer to allow them to get on their feet initially. Or maybe customers are dropping products. All of these are possibilities. The hard thing about being a CFO (or any type of manager) is as much as you’d like to be aware of everything that’s happening in the company – it’s impossible amongst all your other responsibilities. So you decide you’d like to have a report that compares customer revenue month by month and identifies variances. That way you’ll be able to see (from a top level) if customers are dropping products, canceling, or receiving free months; thereby allowing you to identify the cause and solve the problem before it’s too late! *Cue Intacct Financial Report Writer*

The Financial Report Writer can also be used to create custom balance sheets, income statements, etc (if you need something different than the stock reports Intacct provides). This example will give you a good overview of the steps and various functions of the dimensional report writer structure at a basic level.

Now that I’ve built up a bit of anticipation, let’s get started!

Navigation:

General Ledger > Financial Reports > + or “Add”

Step 1: Report Info

Here’s a screenshot of what the Report Info step looks like:

• Report Name – the name of the report in the report menu.
• Report Structure – what will appear in the rows of the report. Either Accounts or Dimensions. In our example we’ll be choosing Dimensions. What’s the difference? Let me explain what Intacct calls dimensions and accounts. Dimensions are things like customers, items, locations. Accounts are GL account groups like revenue or expenses. We chose dimensions in the rows because we want to see customers in the left most column, while the revenue account group will be the data.

Step 2: Rows

Steps 2 and 3 are where the fun of building our report happens! (They’re a little long but I wanted to make sure you don’t get tripped up when you try to build one yourself).

Below is a screenshot of what appears when you select the Rows section.

This is where we’ll define what information we want in our report. In our report we have two variables: Customers and Revenue. We want the Customer to appear in the left most column and revenue to appear in all others. Let’s go through each column in the screenshot above.

1. Row Structure: 

To set the rows, click the “Select Dimension Structures” button. That button brings you to the following screen:

• Section/Row Type – Type of dimension – Customer in our example.
• Dimension structure – This is the customers we would like to include in our report. You’ll notice in my drop down I have different groupings. For example there are Washington Customers (all customers in Washington), Customers Over 5K (any customer that is over $5,000 in monthly revenue). You can set up these dimensional groupings based on any variable you’d like! You could even do this for items (type of donuts!). I’ll teach you how to set up these dimension structures in the next post. I chose Monthly Customers because I want to include everyone who is a month to month customer (while excluding anyone who pays us annually or was a one time customer).

2. Detail Level: 

Here we can choose either Detail or Summary. 

• Detail – Shows what is included in the dimension or account group. I chose detail because we want to see all customers that make up the “Monthly EHR/PM Customers” by name.
• Summary – If for example you had multiple dimensions like Donut Customers, Bagel Customers, Coffee Bean Customers and you just wanted to see a total revenue for each customer type (without each customer name listed) you would select Summary for each dimension.

(Half way done!)

Step 3: Columns

The fun part! Below is a screenshot of the Columns step. (You won’t have all these columns just yet! But let me get you through setting them up!)

Column 1:

The first column is auto-generated with the names of the customers.

On to Column 2!

This is where the data lives! In this column I want last month’s revenue (the screenshot shows January 2015 but I would suggest selecting Current Month instead).

• Column Type – Actual / Budget / Budget Forecast / Computation (This step is pretty cool because you could easily build a report that calculates actual expenses vs. budgeted expenses and compute the variance. In our example we don’t have a budget for revenue so we’ll be using Actual in Column 2).
• Reporting Period – Prior Month
• Column Heading 1 – Period Name
• Column Heading 2 – Period Date
• Column Title – Actual
• Account Group – This is the meat of our report (the data we want). Intacct has out of the box account groups (I would check to see what GL accounts are actually included in those groupings) or you could create a custom account group. I chose a custom created group called Audit-Revenues.
• Expand By – Here you can expand the column by locations, departments, projects, customers, vendors, employees, items, or industries. This creates a new column for each item in that dimension. For example if we were to expand by location this would create a new column for each location we have set up in Intacct (not customer location).

*Tip! – Intacct has a clever little feature for the reporting period which allows you to create the first column and then offset each sequential column by a certain amount. For example in Column 2 we have the reporting period set as prior month (February). For Column 3 we can set the reporting period as Current Month -1 (offset by 1) (February). Then, Column 4 could be Current Month -2 (January). Cool, right?

Computational Column:

Now, we want to calculate the difference in revenue between February and January. To do so, we need to create a computational column.

• Column Type: Summary on Columns
• Summary: Sub(3,4) – subtracts columns 3 and 4.
• Column Title: Difference
• Conditional Highlighting: We want to highlight any account that had a significant drop in revenue more than $100. (Conditional Highlighting > Edit Rules) (More on Visual Indicators here!)

• Rule for Conditional Highlighting: Less than -100.

Now, Column 4 displays the difference between revenue for January and February and highlights any customers with a significant drop in revenue greater than $100. (Side note: Why $100? Our customers are set up on exact day, prorated monthly revenue recognition schedules. This means when a month has only 28 days, there will be less revenue recognized for that month. (We make up for this difference with a journal entry at the end of the month). Naturally, the difference between a 31 day month and 28 day month will lead to a negative variance. Choosing $100 eliminates that factor and allows us to concentrate on accounts with a significant drop in revenue).

*Tip! To add more columns, click the blue column link down arrow at the top. This will allow you to add a column to the right or left.

 

Step 4: Filters

Here we can set the report to filter by date and dimensions. I usually allow all of these selections to be made by the person running the report by choosing the “Prompt” checkbox.

Step 5: Format

This step allows you to choose how the report will be displayed (fonts, font sizes, headers, footers, etc).

Step 6: Permissions

Here we choose who we’d like to be able to view these reports.

Step 7: Next Steps

This is where you can choose to add the report to your Dashboard or set it as a Favorite.

Now you’ll be able to see which customers have dropped products, cancelled, or received free months. This is what the report will look like when you run it (from an Intacct test company):

From here, you could build a report that compares quantities of each product sold month by month. Maybe from that report you’ll notice there was a huge drop in sprinkle donut sales and you can shift your supplies away from that and invest more in maple bars.

*Another cool feature about reports is the ability to set them to run automatically at a specified time and email to specified recipients. For example I had a report that automatically runs monthly flagging customers over $5,000 in monthly revenue. Intacct automatically sends the report to the implementations manager who can then flag these customers to make sure they receive prompt attention when calling in.

Feel free to reach out to your CLA Intacct team with any questions or special requests!

Tags: Customers, Reporting, Revenue, Visual Indicators, Reporting | 1 Comment »