Cost Allocation - help!

Hi all, I’ve just joined the EDNA community as it was about time I got some proper training on power BI having used for some time!

I’m working on a departmental accounts project and have a fairly decent model created with financial periods, revenues and costs, however now need to work out how to allocate costs across different departments (replacing a large manual spreadsheet system)…

I have separated costs out to actuals and ‘to be allocated’. I need to divide the ‘to be allocated’ costs across departments, based on proportionate headcount per location, ie if there are 3 departments in birmingham, each with 20 staff, then costs for this location will be spread evenly between departments.

It feels like I need to create some kind of lookup table with formulas to work out who needs to be charged for what, and how much, based on headcount…

apologies if my first post is super complex (theres more to it!), but hoping I can get a few pointers on which functions I should be looking at to get all my calculations worked up?

Thanks
brent

Dear Bret,

Welcome to EDNA, I can assure you that you are at a right place.

I am also beginner, I am sure there might be different approaches by the experts. One option to solve this might be.

Create the 2 meaures.

Cost per head count = Divide ( costs to be allocated, total head count, 0)

Cost per department = Head count in the department * cost per head count.

This might be simple approach, I am sure there will be different ways to solve this.

Hi @brent

I’m new to power bi too

I’ve done this for years allocating cost based on revenue but it can be allocated based on head count too

@piryani has the right idea too

Allocated to Department equal Total actual cost allocated times( (head count for Department(location)/total HeadCount for company)

total actual cost would be for the period (month)

I was just looking at your screen shot, is the Cost to allocate table the same as Cost Actual table? If it is you don’t really needed it (duplicate information) You can pull the information directly from the Cost Actual table.

Just another thought this might be able to done automatically within your financial system.

There is also some great video on this site that you might want to take the time in review


I hope this helps
Keith

Thanks, Piryani- I agree simple is best- youve just helped me realise my reporting groups lookup table isn’t matching departments to headcount properly And I need another lookup for locations (as some costs are only allocated To those departments who are in a location/ others are spread evenly across all by headcount)… will keep going!

Thanks Keith, yes the data source is the same, actually a massive flat file pulled from sun, as connector proving way too slow and issues with refresh: but filtered and split at source -by general ledger codes, probably just so I could keep Them separate in my brain!

Have watched these - but my Dax formulas mostly go wrong as soon as I combine more than one function, so I need to practice writing them out!

My challenge is that costs in the actual table are against a department code, some departments are locations and some costs need to be allocated across departments in location by headcount(most), though some by salary (cost data in the actual table) and a couple of others.

I have created a table Listing allocation method by by GL code, but just seem to be missing the key method to stitching together the right formulas for allocating to the right places. Once I have this it should be easy to layer up cost types by department (he said optimistically!). Any other thoughts you have on cost allocation by headcount methods appreciated. Will continue trying to align the headcount data in the mean time!

Thanks
Brent

Hi @brent,

I was wondering if this might be option.

I believe you have these two columns within the Reporting groups table - department id and location. Create a column indicating within (reporting groups table ) that you want this location and department id to be allocated based on head count data. You should be able to pull information using head count table and reporting groups table. You might be able to pull the information for actual cost the same way.

I think my process would be:

  1. Start with Location allocation along with Department id that only involved the locations - take only head count that are only involving head count at the locations. Only take the actual cost related to that those locations by department id and then allocate that total cost to all locations/departments.

You will have to calculate the Total Actual Cost by location(involving only the department id at all location)
Cost for location(by department id) = Total actual cost for all locations times head count for department id(location)/total head count for all location)

My experience you will have allocate the cost back to the department id with the general ledger

  1. Cost by Department id = (Total Actual cost minus - Total cost by location) times ( Head count by department id/ Total Head Count.)

I’m taking the Actual cost by location out of the total Actual as you don’t want to double apply the cost back to the department id again.

I hope this helps

Best regards and stay safe :smile:

Thanks
Keith

1 Like

@Keith - thanks for this, though my brain hurts a little it makes sense… so how do I create a column in the reporting groups lookup table that shows headcount by dept code per location? I am trying to use lookupvalue, but I need to reference both location and dept code, which I cant seem to do… is there an easier function to use to pull in headcount into this lookup table?

(Once I have this, I’ll try to create the allocations based on your suggestion…).

thanks!
Brent

@brent,

I think that information is one of the videos that i suggested within the string that you can review. There is other video that you can review on that. I believe that information is the video Ultimate beginner guide

I know i have seen within the forum that will be able to help with that too “adding a column within table”

I’m not sure how pulled the reporting group table in with powerbi or how you populated that table. If information is done by an excel file you can put that information right in that file and power query it in to power bi

I would just put a “Y” or Yes in the column within the reporting group table and then you make up a query that reference both the head count and reporting group.

Sorry i’m new to power bi too.

Maybe even do a search on insert a column in table within the forum might be able to help too.

I haven’t done much programming aspect within power bi…sorry

Try doing some searching on the forum.

Sorry i can’t help much with the process on that.
thanks
keith

@brent,

There is also some videos on Youtube that might be able to help too

what is the possibility of loading the power bi file? If you can do that, we could help more too. this video will help make your data not so sensitive

Hi @brent, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like