Audience: Administrators who manage the model.
The following is a scenario that walks you through how linked accounts work.
See Linked Accounts Overview and Pull Data from a Modeled or Cube Sheet into Another Account.
In your Personnel Planning modeled sheet, you track information about personnel, including salaries, headcount, roles, and type of employee.
One of the things that this modeled sheet drives is Salaries. Notice that salary isn't an account that displays on the sheet. The salary is calculated based on information in other accounts, such as the Hr/Week, the Health Benefits plan, the Raise Date, Pay Raise %, and Pay Rate. The sheet has a location dimension that tags the data by each employee with values, such as United States, United Kingdom, and Canada.
In your general ledger accounts, you have Operating Expenses broken down into several child accounts:
Use the Personnel Model's Salary in General Ledger Expenses
How can you get the salary expenses from your personnel model to contribute to the overall expense account in your general ledger? You could:
- Use a master or shared formula. See Linked Account Overview for pros and cons of formulas versus links.
- Add the modeled account to the expense sheet. Your team enters the data in the general ledger, copying it line by line from the salary account in the modeled sheet.
- Link the accounts.
If you add the modeled Salary account to your expense sheet, it would look like this:
1 The Total Expense doesn't include the salaries.
2 The salaries are tracked separately. Your team would have to copy the total into the Salary Expense at the top.
Link the Salary Expenses general ledger account to the Salary account in the Personnel Planning model.
Then the Operating Expenses total includes salaries:
Pivot the View of Modeled or Cube Data
If you link the salary account in your modeled sheet to a general ledger account, you see the salary breakdown of each employee, just as it is tracked in your modeled sheet. What if you don't want this visible? What if you would rather break the data down by a more relevant dimension, such as location?
To pivot a modeled or cube sheet, you must create a standard cube account in a cube sheet.
If you want to see a pivoted view of this data that totals the salary per location:
1 Create a standard cube account
2 Enable the link setting
3 Link it to the Personnel Planning model and the modeled Salary account.
Then add the Location dimension to the cube sheet.
When you look at the cube sheet, you see the totals of all the modeled sheet's rows for the salary without having to deal with the long list of employees. You can then use the location dimensions to slice the data.
When you explore the cell, you see the salaries per employee (if you have the salary detail permission).