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 dimension members 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 App, you will use an allocation dimension member to store the values from your allocation template. First, confirm there is an allocation scenario dimension member in your cube in Scenarios on the Workspace's Dimensions page. If there isn't an allocation scenario dimension member already, go ahead and create a new one.
2. Update dimension member names
Next, it's crucial that your spreadsheet template reflects the same dimension member names as in your Cube data structure. This synchronization ensures smooth data fetching and publishing via the Spreadsheet App.
- Consult the Dimensions page in the Workspace to view or download your dimension member names.
- Match or update dimension member names in your template (whether it's Cube’s or yours) with those in the Cube Workspace. Be sure to open any hidden spreadsheet rows to columns to update all dimensions.
- 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 dimension members in your Cube.
Remember, you'll determine the cell range for data fetching or publishing before taking these actions in the Spreadsheet App. This flexibility allows you to make as many template adjustments as you need.
Hidden cells and formulas
Some dimension member 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 dimension members 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 renaming 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 App. 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 App 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!