Create a table from named ranges

[pwc86]

I am creating a financial forecast model that forecasts costs and revenues for about 15 departments; the costs change over time from inflation, volume changes and capital induced costs. I currently have about 45 tables, one for type of cost change for each department. I came across the following article: “How to create a union of n tables in Excel” http://blog.jamesbayley.com/2013/06/13/how-to-create-a-union-of-n-tables-in-excel/ and think I could make this work to Union the tables but am not sure if there is a better way.

My end goal is to have a table that I can put into PowerPivot/PowerView to create summaries of the forecasts. I have tested the new merge function in PowerQuery but find the update of the data model to be quite slow using the querys. It should be noted that the user needs to have the ability to add/delete/change departments and accounts.

The following is a visual of what I am trying to do:

Departments (named range)
o Admin
o HR
o Finance
o Sales
o Marketing
o Etc.

Accounts:
o Revenues (named range)
§ Sales
§ Commissions
§ Interest
§ Etc.
o Expenses (named range)
§ Salaries and Wages
§ Utilities
§ Taxes
§ Etc.

These then change in the future based on:
o Inflation
o Volume changes (arising from the company growing)
o Capital Induced Costs


I need to table to look like the following (with the figures calculating in columns on the right):

Department	Type	Account
Admin	Inflation	Sales
Admin	Inflation	Commissions
Admin	Inflation	Interest
Admin	Inflation	Salaries and Wages
Admin	Inflation	Utilities
Admin	Inflation	Taxes
Admin	Volume	Sales
...and so on

<tbody>
</tbody>

Any suggestions would be greatly appreciated!