Ensuring accurate allocation of costs and resources is fundamental to insightful planning and analysis. In this guide, we’ll walk you through leveraging our allocation template for seamless interaction with Cube. Already have a template you love? You can also use this article to modify an existing allocation template. 

What's included with our template:

• Allocation Inputs tab: sync your actuals data with Cube and modify a tailored actuals-allocation scenario.
• P&L variance tab: visualize the broader impacts of your allocation adjustments over time.
• Product P&L tab: gain insights into product line performance before and after making allocation adjustments.
• Management P&L tab: see a detailed overview of each line item in your P&L over time. 
• Drivers tab: lists your Cube dimensions to build named ranges and use as quick references throughout your template. Learn more about using drivers in your spreadsheets. 

Download our allocation template here. 

1. Establish a new Scenario

When you publish using the Spreadsheet add-on, you will use an allocation dimension to store the values from your allocation template. First, confirm there is an allocation scenario dimension in your cube in Scenarios on the Web portal's Dimensions page.  If there isn't an allocation scenario dimension already, go ahead and create a new one.

2. Update dimension names

Next, it's crucial that your spreadsheet template reflects the same dimension names as in your Cube data structure. This synchronization ensures smooth data fetching and publishing via the Spreadsheet add-on. 

1. Consult the Dimensions page in the Web portal to view or download your dimension names.
2. Match or update dimension names in your template (whether it's Cube’s or yours) with those in the Cube Web portal. Be sure to open any hidden spreadsheet rows to columns to update all dimensions. 
3. Consider incorporating a Drivers tab in your workbook to increase the longevity and adaptability of your template. If you are using our template, update the lists in the Drivers tab to match the dimensions in your Cube. 

Remember, you'll determine the cell range for data fetching or publishing before taking these actions in the Spreadsheet add-on. This flexibility allows for as many template adjustments as you need.

Hidden cells and formulas

Some dimension names may be housed in hidden cells or formulas of our templates. Cube needs all dimensions specified, even if the same dimension will be used across a range. Cube will still be able to access the dimension names in hidden cells so you can hide them and clean up your spreadsheet. 

The Allocation tab hides the Scenario, Entity, Market, and Region dimensions in columns F-I. Unhide, modify them as needed, and then hide them again if you wish.  

Certain tabs contain formulas adjusting Time dimensions for Cube’s formatting. They might be set, but it's good practice to review them. You can find them in row 5 of the Allocation tab and cells D3-G4 of the P&L Var New tab. 

3. Revise assumptions and formulas

Customize the template's underlying formulas and assumptions to mirror your allocation approach. If you’re retrofitting an existing template, ensure modifications to rows and columns during dimension naming didn't introduce errors.

Allocation tab breakdown

Data from Department, Account, and Product dimensions gets pulled into the Cube Fetch section for specified months, updating rows 7-15.

Rows 18-22 compute each product’s revenue percentage based on fetched data, and here are a mix of manual and calculated allocations for the product and engineering teams. 

The Cube Publish section (rows 30-34) identifies data designated for Cube publishing under the allocation scenario. Rows 36-40, meanwhile, ensure accurate allocations.

4. Select and validate ranges

With your workbook ready to use, launch the Spreadsheet add-on. Use its Validate feature to verify that Cube recognizes your rows and columns. Any discrepancies? The system will notify you.

5. Perform and save allocations

Harness the Spreadsheet add-on to fetch necessary data for allocation planning. When your allocation is completed, publish your allocations back to Cube.

Leveraging our allocation template can streamline your allocation processes, offering clear insights into product line performance and the impact of allocations over time. Your feedback is invaluable, and we're here to support your journey towards refined allocation methodologies. Please reach out if you have any questions about this or other templates. Happy analyzing!