General Discussions

 View Only
Expand all | Collapse all

Budget and Actual Coding Detail

  • 1.  Budget and Actual Coding Detail

    Founding Member
    Posted 03-17-2022 14:50
    Hi everyone. I'm interested to learn what types of coding dimensions others are using to tag financial/budget data and how helpful you find them? Do you approach your budgeting differently than your actuals?

    PIH is reflecting on what codes we're tagging our data with and whether we've possibly gone too far in what we're asking end users to keep track of, which we think might be leading to mis-coding and low quality data entry as we get into a level of detail that is too deep. Due to this lack of trust of the accuracy of certain data points, we rarely use them in reporting and that has us wondering whether it's worth collecting at all. Below are the dimensions we're using currently as a reference. I'm curious if others have similar dimensions and/or a similar number of dimensions.

    Budget + ERP (Actuals)
    Budget: the country or department in the US
    Program: department in countries or sub-departments for US departments
    Activity: a flexible code for individuals to track whatever they want, most often used to track sub-activities from grants
    Site: location/facility where an expense happens
    Fund: funding source for the expense
    Super Code: a code that matches to the description of the operating budget line (payroll lines use a default dimension member), AND which acts as a Dimension Speedkey in our ERP to more quickly enter all of the above dimensions
    General Ledger Account: the expense type
    Employee ID: each employee has a uniquely assigned code (non-payroll lines use a default dimension member)
    Employee Type: drives what fringe costs an employee is eligible to receive
    First Name, Last Name, Job Title: the employee's job title
    Company: the database where an expense occurs

    Budget Only
    Core Cost: a tag to mark the expense as a part of our core operations or a one-time expense that can end when funding ends
    Scenario: Board (approved once per year) or Working (flexible throughout the year as things change or new funding comes in)
    Donor Categories: specific categories for which we need to report back to the donor on - we lookup this value based on the Super Code which is our key between budget and actuals

    ERP Only
    Employee Role: a rolled-up employee grouping to see how many of a certain type of role we have (clinical vs non-clinical, admin vs IT vs doctor vs nurse vs etc.)

    ------------------------------
    Brendan Eger
    ------------------------------


  • 2.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 03-29-2022 17:26

    No love for this thread, huh?

    @Josh Weinberg @Clement Marlin @David Cornfield @Christine Sawyer are any of you willing to share your coding dimensions or comment on mine? Any general thoughts on how you decide what information is actually wort​​​​h collecting? Has anyone found time to reflect and ask yourself this question? 

    @Irene Tang is there a standard set of dimension​​s that Vena recommends based on your experience with non-profit customers?

    ------------------------------
    Brendan Eger
    ------------------------------



  • 3.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 03-30-2022 10:50
    Well, I don't really have time to dig in deep here, but at first glance, yes, it looks like quite a lot of extra dimensions, and I have no doubt this leads to coding errors.

    I've run FP&A at three arts & culture non-profits in the $50-100M annual budget range, and based on that experience, here are what I think of as the essential codes for the GL:

    Fund code for Restricted, vs Unrestricted, vs Board Restricted (if applicable). We still have funds that separate temp & perm restricted, but of course those roll up together now for financial statements.

    Project/program/activity code. It is my experience that you can definitely use a single dimension for this. If a program has multiple components, or multiple funders that cover different parts, then create multiple program/project/activity codes and roll them up in a parent dimension in your hierarchy so you can report on it as a whole or in parts from a single dimension. I REALLY don't like having subfund or grant codes that identify funders - they are often redundant, not used across all programs, and therefore the most likely segment in the code to be missed by an end-user creating a PO or filling out an expense report. My goal with a chart of accounts is that I never have to combine two different dimensions to get a breakdown that I should be able to get from a single dimension, and this applies to activity/program/project most of all. This will create more program codes, but reducing the overall number of segments is worth it. And I'd use that same dimension for administrative activities, too. Just because accounting & finance isn't a program does not mean it isn't an activity that needs to be tracked. Because of that blend, I prefer the label "Activity" for this dimension. And I make it a goal to be able to create the Statement of Activities with ONLY Natural account and Activity necessary to map all your rows.

    Budget owner or Department code. This is just to refer to whoever is responsible for the budget line. A program can have budget responsibility across multiple departments (and usually does), so I like to keep this separate. Sometimes it is redundant with Activity, but that's OK, because this dimension has a specific job: managing budget responsibility and workflows.

    Natural accounting code. Standard: revenue, expense, asset, liability accounts. Most orgs have way too many of these and could cut them back dramatically.

    Location code and Company code are naturally very important for some organizations. I've only worked in single-location, single-company, single-currency organizations thus far.

    There are other codes that are extremely relevant for subledgers and other systems, like vendor and payroll records.

    I'd never differentiate payroll by employee in the GL, but of course then you need a payroll system with good information/analytics platform so that you can dig into detail by head. I've considered bringing in actual payroll data in my Vena personnel cube, to do variance analysis there. The obstacle for us is that we do a great job modeling payroll on an accrual basis by month, but variance analysis of accrual basis budget to cash basis payroll is a great way to waste a lot of time for little benefit. We haven't yet found a very good way to model the budget on a biweekly cash basis to match our payroll cycle. I guess we could create an alternate period hierarchy and use that for the cash basis budget...

    So, my first thought is you could look at your list to see what you can and maybe should combine. Then I'd look at what is in subledgers/other systems, and can be kept out of the GL but analyzed elsewhere. Personnel is a great example. I have an employee dimension in my personnel cube in Vena, but that employee dimension is most emphatically not in the financial reporting cube, and never will be.

    I don't know if this is any help at all. I hope it is!



    ------------------------------
    Aaron Andersen
    ------------------------------



  • 4.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 03-30-2022 11:32

    Thanks for not digging in deep here ;) But actually this is very helpful!

    I think you're totally right that we should look to use a single code for multiple purposes rather than having separate codes where one is helpful for some, but not all teams. Also good call on reducing the GL/Natural accounting code list! We actually went through an exercise to do this about 4 years ago and reduced our Expense GL's from ~200 down to about 60. I think we've since crept back up to about 70, but we have more guidance in place to decide when a new GL is truly necessary and a culture that values keeping a smaller list for ease of use across our tools. Budgeting and expensing at an Employee level is also something that we're open to re-thinking. We often need this to make sure we're charging the right people/effort/amount to a grant, but we also have ~40-50% of our staff that are solely funded from unrestricted resources so there isn't a ton of benefit to keeping their information at such a detailed level.

    Plenty to think about from your response. Hope others are able to share their perspective as well!



    ------------------------------
    Brendan Eger
    ------------------------------



  • 5.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 04-04-2022 13:12
    We have a very similar set up at WWF.

    I assume you also have it but I would add the "Scenario" dimension is very important, mostly for our variance analyses Actual vs Budget, but also extremely useful for rolling forecast purposes (I have 1 separate scenario dimension for our monthly iterations), and for scenario planning.

    We add the employee dimension for planning purposes, but compared to Brendan, we use the "measure" field to capture different employee information like level, first/last names etc.

    ------------------------------
    Clement Marlin
    ------------------------------



  • 6.  RE: Budget and Actual Coding Detail

    Posted 03-30-2022 13:33
    Hey Brendan!!
    Let me do some digging for you and see if I can get any additional insight! 💡
    Loooving the participation, I see you getting those last minute engagement points for the contest! 👀💪

    ------------------------------
    Irene Tang
    ------------------------------



  • 7.  RE: Budget and Actual Coding Detail

    Venanite
    Posted 03-31-2022 09:27
    Hey everyone. Here are the standard dimensions recommended for Not For Profits, but there's always a little variety depending on the client:

    Account

    This dimension carries the P&L ("Net Income" parent below), Balance Sheet and Other Account roll-ups used in the templates and reports from the Foundation library.

    Entity

    This dimension carries the Entity roll up, used to differentiate the client legal entities ("ABC corp Canada", "ABC corp US", "ABC corp consolidated", etc)

    Department

    This dimension carries the Department roll up, used to differentiate the different departments / operating units

    Grant

    Those dimensions are placeholders and can be re-used to carry other client dimensions required to support the implementation ("customer", "product", "project", etc.)

    • Grant, Project, Restriction are common NFP dimensions which can be changed as required

    Project

    Restriction

    Placeholder 4

    Year

    This dimension carries the calendar year roll up (flat list of years)

    Period

    This dimension carries the Full Year and Year-to-Date monthly roll ups

    Scenario

    This dimension carries the different scenarios used to differentiate Actuals versus Plan versus Archive Scenarios (i.e versionning of the Plan scenario for closed periods).

    Currency

    This dimension supports the currency conversion module (Currency). It will carry by default a "Local" member.

    Measure

    This dimension carries all the measures used across the templates and reports of the [Foundation] library



    ------------------------------
    Olivia MacDonald
    ------------------------------



  • 8.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 03-31-2022 09:35
    Thanks @Olivia MacDonald! Very helpful to have this as a point of comparison.​

    ------------------------------
    Brendan Eger
    ------------------------------



  • 9.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 03-31-2022 10:39
    Hi Brendan,

    We are pretty simple and do not have grants to track. We have only two segments on our ledger (I think we could make good use of one or two more). We have had so much shifting of programs between departments it is much easier for us to keep that hierarchy within Vena's tree structure - although we lose the historical view when we switch a program from one roll-up to another. We are considering switching accounting systems in the next few years, so we will do a full review and revision of our segment needs at that time. I agree that your structure seems to be segment-heavy and I can see how easily miscoding can occur. Our problem is with account and sub-account (cost center/activity) bloat. Maintaining entry tasks on the workflow with so many activity codes is no fun, but I am looking forward to using the recently released task upload to make this easier.

    We have two entities (one is a c3, and the other a c6). We have consistency in our segment structure and our natural accounts across both orgs. Our GL segments are:
    • Natural Account - five digits
    • Activity - three digits. The first of the three digits differs denotes which entity

    In Vena we have the following dimensions:
    • Account, Company, Cost Center, Placeholder 1*, Year, Period, Scenario, Currency, Measure
    *We added the placeholder in the model during implementation as a suggestion from our implementation consultant as a hedge against a model change. It is not cumbersome to map this additional dimension.





    ------------------------------
    Christine Sawyer CAE
    Director, Finance and Accounting
    American College of Healthcare Executives
    Chicago, IL
    312-424-9391
    ------------------------------



  • 10.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 04-06-2022 17:15
    Thanks for all the replies to this thread and @Brendan Eger for kickstarting it -it's really interesting, and so relevant to us as we change accounting system & begin discussions about whether we should reconsider our dimensions etc.

    I aim to share more details on our setup too but a bit snowed under currently.​​​

    ------------------------------
    Josh Weinberg
    ------------------------------



  • 11.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 04-19-2022 12:35
    We essentially use 6 analysis fields really alongside our account code
    4 of these apply to all expenditure
    1. Department - which has a hierarchy structure so that each department is within a directorate
    2. Funder - which has a hierarchy structure so that each funder is either Restricted or Unrestricted
    3. Project - which represents the activity the funder is funding (or an internal only budget where we want to track something individually) has a hierarchy structure so that each department is within a directorate
    4. Scenario - I guess we don't think of this as scenario within our actuals data, but it is crucial from a Vena perspective & allows us to manage forecasts, budgets and also what we call a 'funder budget' which represents in year changes to a budget from amended funder agreements [which is different from a forecast as funder budget represents what a funder expects from us, where as the forecast is what we anticipate achieving] - this aspect being something we only looked at doing post Vena implementation.
    2 only apply to payroll (and only to our Employee cube in Vena)
    1. Job code -this is a unique reference for each job role 
    2. Employee ID - this is a unique reference for each individual
    These two payroll codes ensure we can budget for roles, without necessarily knowing who will fill each role. But we can also track the cost of individuals as well. It gets a bit fiddly where we have 2 people doing 1 role (e.g. job share or if there's extended leave) but seems to work ok. We bring this all into Vena and are able to do variance analysis by job code.

    Also, when I say there is a hierarchy structure, e.g. for Dept, whilst this works well in Vena, it doesn't work so well in our General Ledger system (currently Infor SunSystems - part of the reason we're looking at changing).

    We are looking to revisit all of this as we change finance systems imminently. Basically we have found ourselves creating extra work, as each project is essentially unique to a department / funder and yet we require people (finance staff and users e.g. when creating POs or expenses) having to select all 3. Thus all too often selecting a non-existent combination. Whilst rules can help preventing these, they are pretty limited - rules can't be applied in all the systems we have, and they require work to keep these updated. So really interested by your comments @Aaron Andersen around 'activity' - I think what you propose is a likely direction for us. I'd then like to think of each of the project/activities as having multiple attributes, but only one of each (a department, a funder, a fund type, a budget holder etc), and being able to slice & dice accordingly.


    ------------------------------
    Josh Weinberg
    ------------------------------



  • 12.  RE: Budget and Actual Coding Detail

    Founding Member
    Posted 04-26-2022 17:22
    @Josh Weinberg I like your approach of using a Job Code to budget for roles without necessarily assigning individual employees. We've for many years budgeted and recorded actuals at an employee level and we tag employees in our accounting system to essentially a Job Code​ so that we can do some rolled-up reporting, but hadn't thought about budgeting at a role level. I imagine we could budget at that rolled up level and still tag our individual employees to those roles in our accounting system, then we should be able to generate some donor reports by matching those codes. Probably need to think through that some more...

    I also like the idea of having more hierarchical dimensions, but our accounting system also is a bit limited on that as we can currently only do that easily with our GL Accounts. We could probably do something like saying Program 100 represents the rolled up grouping and then Program 101, 102, etc. are the specific departments, but I'd be concerned that someone would accidentally post something to the rolled up grouping of Program 100. That might not be any worse than people miscoding things now though. 🤷‍♂️

    ------------------------------
    Brendan Eger
    Assoc. Dir. of Information Systems and Analytics
    Partners In Health
    ------------------------------