Hi,
Each month we extract revenue and cost data from an ERP system. The data gives revenue by division and then costs by category. We also get headcount and number of transactions by division.
What is required is to take each cost category and allocate them to each division based on either revenue, headcount or number of transactions.
I have attached a file that shows in a very simplified form what we have. The first table shows the cost type, allocations method and total cost. The second table then shows the divisional revenue, headcount and transactions. The last table is a basic P&L statement that gives a profit or loss by division where the costs are apportioned based on each of the allocation methods.
The actual outputs are thousands of lines of costs and around 70 divisions (!) so as you can imagine doing it manually as I have in the example file would be a bind.
Does anyone have any bright ideas as to how I might achieve what is required using power query? All input greatly revceived.
Thanks
Bax
Hi Bax,
I used to do this exact task in my previous life as a management accountant. I'm pretty sure I demo a technique you can use to do this in lesson 7.08 Add or Expand Rows, albeit using a different example, but the concept is still the same.
Mynda
Thanks Mynda. I have taken a look and it is really useful. I need to work out how to get the pro-rata values into the lists. I will take a look.
