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 cancelations 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 report writer at a basic level. Then, in future posts I’ll go over how to set up dimensions and account groups. (This can be a little tricky but easy if you know where to go.)
Now that I’ve built up a bit of anticipation, let’s get started!
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 – remember this name so you can run it later!
- 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. (I’ll go over creating dimension and account groups in the next post).
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! (The ability to report dimensionally is one of the coolest things about Intacct). 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. Account Group: This column is where you set the data. Selecting the “Set account group” button brings you to this screen:
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 HF-Audit-Revenues.
3. 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! (And a little trick that I noticed people get hung up on sometimes). 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!)
*Tip: Remember when we specified our customers as the rows? Well, Intacct doesn’t automatically put the labels in (you have to tell it to). So the first column will be the names of the customers. (People get stuck on this and wonder why the names of the rows aren’t showing up so doing this step will save you a little frustration).
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
- 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?
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)
- 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).
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
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.
Sorry for the long 24 day break! I left you hanging with my last post on promises of this awesome Financial Report Writer and then I disappeared! I know! I’m sorry. That was all just to build up the anticipation! (And a little because I got busy). Hopefully that was a good overview of the Financial Report Writer. Of course let me know if you have any questions or special requests! (email: firstname.lastname@example.org – I promise I reply back to email faster than I post blogs 🙂 )