If the budgets of each of your business units are consolidated in another system (possibly your budgeting/planning or ERP system) or in a database, then you can import them into Power Pivot for Excel to analyze along with actual data. However the next section looks at a way to input directly and consolidate your budgets using Power Pivot.
As mentioned at the start of this article, Power Pivot for Excel is not well suited for any serious budgeting/planning process; it lacks write-back capability from Excel to the model and is a personal BI tool that, by design, does not support collaboration. For enterprises with more complex needs there are many other budgeting and planning tools available including the Excel-centric ones such as PowerOLAP
from PARIS Technologies
A small business with one or two business units might benefit from entering plan data into a model so that it can be analyzed, compared, and reported on with actual data. The business unit budgets could be consolidated into one table or entered in the model using Excel/Power Pivot Linked Tables (adding Data Tables to the model in 2013+ parlance).
Option 1: Budget data for all business units is in one table
Putting the budget data of all business units in one table requires less maintenance to the model when a business unit is added or removed. But updating a Linked Table is an all or nothing process; the contents of your table in Power Pivot is completely emptied and replaced, when you update from the linked Excel table. You can't input the budget data from one business unit on Monday and incrementally add the budget data from a second business model on Wednesday. You must input the budget data of all business units with each update. This means that tweaking a number in one budget requires that all budgets are reloaded.
As already explained, Power Query makes it easy to import a folder full of budget templates, regardless of whether the template data has changes. Nowadays, this is likely to be the preferred approach,Option 2: Each business unit has its own budget table
Here you have a Linked Table for each business unit. Each corresponding business unit table in PowerPivot must be separately related to the dimension tables such as time, planning chart of accounts, and business unit. This may be acceptable with 2 or 3 business units that don't change frequently if you prefer not to use Power Query.
The example described below is based around a small restaurant business with 2 units:
- The Elizabethan - a cloth napkin restaurant
- Lizzie's Diner for cheaper fare
Planning is done by month for sales, cost of sales, and overheads. As already mentioned, budget templates in Excel commonly have time periods on columns like this: