Allocate Expenses for Reporting in Dynamics GP

There are times when you want to report data in a way that is not necessarily supported by your Microsoft Dynamics GP general ledger policies.  For instance, expense allocation might be one of those items that you do not have allocated in your general ledger, but you still may want to view a financial report that breaks down an expense line by a percentage of another attribute in your report.

One practical example of this is to break out the expenses of non-revenue generating department among revenue generating departments. I will walk you through the steps to perform this in GP’s Management Reporter, basing an allocation off of a percentage of revenue contribution by department.

In this example, we are taking the Administrative Expenses of $1,026,233 and allocating it out to Sales A and Sales B, based on the percentage of total sales.

This calculation starts with the row format by using a named column calculation as follows:

First, we need to calculate a percentage of the total sales, but the calculation needs to be column specific (see row @250).  We will use the formula C,D=(@130/E.130).  This formula is the same as C=C.130/E.130 & D=D.130/E.130, and gives us a column that indicates percentage of the total sales.

Next, we need to use the percentage calculation in row @280 to allocate the expenses in column B (Administrative column).  We will use the formula C,D=(@250*B.190).  This formula is the same as C=C.250*B.190 & D=D.250*B.190, giving us the expenses of B.190 multiplied by the allocation percentage.

This calculation theory can be used in a variety of situations where you need to calculate variable columns by a fixed column.  Use a named column expression on visible or non-printing rows to give you a new look at data that you cannot get from the posted transactions alone.