Track and manage where your teams are in their budgeting using a status indicator in your spreadsheets. This guide will walk you through setting up a new status dimension member in Cube and implementing it in your spreadsheet, allowing for a seamless and transparent budgeting workflow across departments.

This article uses the Department budgeting template. Reference the department budgeting template guide if you would like to learn more. You can also set up a similar status indicator on any other template by following the steps below. 

There are three main steps to using status indicators on your spreadsheet:

1. Creating a new dimension member for the status in Cube
2. Configuring your spreadsheet to use the status dimension
3. Teaching your teams and leaders how to use it effectively

Set up the Plan Status dimension member

Add a new Account dimension member named "Plan Status" in Cube to track budgeting stages (more about adding new dimension members). We organize it under our HR metrics in our hierarchy, but it can go wherever makes the most sense with your data structure.

We will use this dimension member to store the current status for each department using a number that represents a different phase in the budgeting process:

• 1 - Open
• 2 - In Progress
• 3 - Submitted

This setup will provide a clear overview of each department's progress. You can have additional status numbers; this article will highlight where to adjust your spreadsheet to accommodate them.

Configuring your spreadsheet

Now you can open your spreadsheet or download our department budgeting template. Two things to note before we jump in:

1. We recommend setting up a drivers tab to hold all Cube dimensions and their members for you to use as dropdowns throughout your spreadsheet.
2. This guide uses multiple ranges to fetch and publish on different sheets. Learn more about adding multiple ranges and naming them. When you share the spreadsheet, these ranges will be available to all Cube users through the Spreadsheet App.

The image below shows the named ranges in the spreadsheet used in this article.

1. Steps tab to publish status updates

We'll use a tab called "Steps" to give the departments directions for using this template and allow them to publish their budget status back to Cube.

We will set up department and status dropdowns using the lists from our drivers tab and data validation. Learn more about using Cube dimensions as drivers in your spreadsheets. 

When leaders select their department and current status from a dropdown menu, hidden cells with white font will update in the range using a formula that connects the status dropdown to the number we want to publish back to the Plan Status dimension member in Cube. The range set for this tab is B21:G25.

This tab will be the control center for departments to update their progress. If you are using additional status phases, you'll want to be sure they are included in your dropdown and formula on this tab. 

2. Display budget status indicators

We want to see the budget status for each department on the Department Variances tab whenever we fetch the variance data from Cube.

To build this tab, we'll incorporate a hidden column to fetch the Plan Status dimension member and use it to power a workflow indicator in the columns immediately to the left.

The image below shows the formulas in column M to display the status as text and column O's fetched Plan Status dimension member. If you are using additional statuses, you will adjust the formulas used in column M. 

Column N is created by setting the cell equal to the neighboring cell in column O and applying conditional formatting rules that use an icon set.

Using status indicators successfully

When your template is ready for your colleagues to use, you can publish it as a template using the Spreadsheet App, upload the file to your library in the Workspace, or share the file with them directly.

Publishing to update department status

Department leaders will need the appropriate Cube permissions to publish data from their spreadsheets. More about inviting colleagues to Cube and setting their permissions.

Guide departments on how and when to publish their updated status from the Steps tab back to Cube. This action will update the Plan Status dimension member in Cube, ensuring all departments have real-time access to each other's budgeting progress.

Share this article with department leaders to guide them through budgeting using the Department template. 

Fetching status with the rest of your Cube data

Guide leaders through fetching the latest budgeting status into their Excel template, along with the rest of the necessary Cube data, and switching between multiple ranges. This process will allow them to view the updated progress status of other departments, fostering transparency and coordination in the budgeting process.

More about fetching to multiple ranges. 

Implementing a responsive status indicator in your Excel budgeting process promotes efficiency, clarity, and collaboration. This system simplifies tracking progress, keeping everyone informed and aligned.