URGENT: BUDGET VS ACTUAL ACCOUNT BALANCE

(0) Share

Report

Posted on by Community Member

Hi all -

I'm looking for a user-friendly way to generate a report that gives me a period and/or yearly budget vs actual, where the selection can be made by GL account segment with a possibility to group by a designated segment (segment 1, 2 or 3); a subtotal for each goup would also be needed. I tried doing it from Smartlist builder but didn't succeed. A close comparison of what I'm looking for is a GL Trial Balance but grouped by any segment of the account.

All the help is greatly appreciated! thanks.

Diop

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

FRx or Management Reporter is what you need to use.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Frank! I installed Management Reporter. Now I'm working on an Income Statement. I have to create the Income Statement for each department on my GL account and I want each of the departments to show on a separate column and the accounts on the row. So it'll look like this:

Dept1 Dept2 Dept3

Revenue:

sales xxx xxx xxx

Total Revenue xxx xxx xxx

Expense: (etc....)

Any help would be great appreciated! Thanks!

Diop -

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

Diop, you need to incorporate a Reporting Tree and make the units in the Tree = your departments. Then, in the column definition, create a column for each department and map each column to the appropriate unit in the Reporting Tree.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I used your suggestion Frank and It's working! I did the mapping using the Dimension Filter (is it the correct way?); I wasn't sure what the Attribute Filter does.

One last thing Frank - if you don't mind: I'm trying to make the budget column to include the beginning balance and the entire yearly budget, but none of the options (Periodic, Periodic/BB, YTD, YTD/BB) seem to do that. I was thinking to add one column for the BB and one for YTD, add the two columns and only show the added column on the print out. is that a good solution or you have a better one?

Thanks again for all the assistance!

Diop

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

That is the way to do it DIOP. See, you're an expert already! :)

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

I'm liking this Management Reporter a lot! :-). One last favor (I know I'm a pain now - loll).

Just like I was able to show the departments in columns and main accounts in rows, is there a way to only show one row for all accounts in same acct category and have the balance for the department still in that column. Here's an example:

Acct                                                                                                                  dept1    dept2     dept3

GRANT EXPENSE (each dept has its own grant exp acct)                    xxx           xxx         xxx

TRAVEL EXPENSE (same)                                                                        xxx           xxx         xxx

In other words, the account descriptions are grouped by category and the amount for each department showing one next to the other (as above), rather than showing one below the other.

Thanks again!

Diop

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

I think so. Try inserting a wild card for department in the Dimension Link for 'GRANT EXPENSE' in the Row Definition and specify the department number in the Reporting Tree. I don't recall the field name in the Tree, but it's the 4th or 5th column from the left, I think. Link to Row Definition, or something like that.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

That worked perfectly! Link to Financial Dimensions using Account Categories did it!

Thank you so much Frank!

…until next post :-)

Diop

Was this reply helpful? Yes No
Suggested answer

Sue B. 295 on at

Like (0)

Report

If you want it in Smartlist, you could build that in Smartlist Builder.

If you're looking for something ad hoc/on the fly I'd pull the TB report into a CSV file and export the budget into Excel, combine them into one Excel file (different tabs), do a text to columns on the GL account segments to get them into their own columns and do maybe a v-lookup to pull the information from both the GL and budget tabs to match them up, then pull all that into a pivot so you can group/filter on the segments...you might be able to skip the v lookup and go straight to a pivot...you'd just need to play around with it a little until you find what works for what you need.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sue - thanks for the suggestion. I can certain use this as a back up in case Management Reporter goes down. But for now MR is doing it perfectly.

Diop

Was this reply helpful? Yes No