Hello,

I am trying to produce a financial report summarizing our income and expenses as follows:

Actual YTD Bud YTD Variance
Revenue
Dept 1 100 90 10
Dept 2 350 400 (50)
Dept 3 150 125 25

Total Revenue 600 615 (15)

Expenses
Dept 1 90 85 5
Dept 2 300 340 (40)
Dept 3 100 110 (10)

Total Expense 490 535 (45)

Surplus(Deficit) 110 80 30

We have over 25 departments and I would like a one or two pager that just sumarizes the revenues and expenses by dept. Basically our revenue codes range from 5000-5999 and expenses from 6000-6999. How would the formuala be structured to segregate only the revenues in a range from 5000-5999, by each department?

Thanks,

Ken

Hello Ken A.,

Is it meant to look like this?

code:

---

              Actual YTD  Bud YTD Variance
Revenue
     Dept 1      100        90       10
     Dept 2      350       400      (50)
     Dept 3      150       125       25
Total Revenue    600       615      (15)
Expenses
     Dept 1      90         85        5      
     Dept 2      300       340      (40)  
     Dept 3      100       110      (10)        
Total Expense    490       535      (45)
Surplus(Deficit) 110        80       30

---

A couple of questions:

Do you want a row for each department?

When you say "basically", do you mean that there are some expense accounts in amongst the revenue accounts, and you don't want to have to itemize them in the formula?

Normally, turn on the "SmartSheet", place the account range in column A, and the department number in column B. Then use the formulas:

code:

---

=ADGET("GLDEPT.DESC",Bn) to get the department name
=ADGET("GLDATA.Y",RangeMrg(An,Bn) (make sure that the account number includes the separating "-")

---

(Everything's possible in Adagio's Financial Reporter!)

Oh yes! If you turn on the SmartSHeet, you can "drag and drop" from your chart of accounts in the account ranges in column A.

Hi Ken,

Well there's a number of ways that you can create a revenue/expense summary by department. It all depends on how you want it to be presented.

A brief 'lesson' (for others reading this) in case you want to do something a little different, so you can tailor this to your needs:

The formula that the Adagio Financial Reporter uses to read the Ledger data is:
=ADGET('gldata.FIELD','ACCT-DEPT')

You can put any specific field code for FIELD, any single or range of accounts for ACCT, and any single or range of departments for DEPT. In addition, you may use a cell reference to indicate either the account, department or both (just like you would in Excel).
------
In your specific requirements, it appears you want all Revenue or Expense accounts consolidated into a single row, and one row per Department. This is how I would do this:

In column A, type the Department code. If it is completely numeric, put an apostrophe in front of it to indicate the cell contents is a text string.
In column B, type the formula for the Actual YTD amount, using a cell reference to get the account. If you were working in Row 9 (for example), the formula would be:
=ADGET('gldata.Y','5000:5999-'&$A9)
Copy that cell and paste in in column C. Change 'Y' to 'BY' for the Budget YTD.
In column D, use the simple formula =$B9-$C9 to get the variance.

Now you've got one row complete, copy the row and paste it below. Edit the department code in column A to the next code, and that row is now done. Copying/pasting automatically updates the cell reference to the correct row.

When you get to the Expense rows, replace 5000:5999 with 6000:6999, and with some additional formatting, you're done!

Take care,

Thank you both for your quick reply. You have provided me with exactly what I was seeking. The report looks great! I am very happy with all the Adagio Products we have and know I am just beginning to scratch the surface regarding the Financial Reporter (and Quick reports). How about setting up some online tutorials for the Reporter and Quick reports???

Have a Great Christmas and Fantastic 2003>

Ken