Often organizations would like to see Salary and Raise in separate accounts. This article covers an example that you can add to most existing personnel/workforce sheets to achieve this without breaking past versions. We will create 2 new accounts: Base Salary and Raises. How to flow this data to your GL accounts though is dependent on your setup. It may be as simple as updating shared formulas in the current version, this article does not cover all the options.
This article assumes that you understand the basics of updating modeled sheets and formulas. This article also requires salary to be calculated evenly across the year (1/12 per month for example). It would be a more detailed process to do this if you are set up to spread on any other pattern.
The following screenshot shows you the result of this work. Existing Salary account working as always and the 2 new accounts to break out raise amount and baseline salary.
TIP: When adding new accounts to a modeled sheet if you select an account before you click Create New Calculated Account, the new account will be added after the selected account versus at the bottom of the list.
For the formulas we will be creating, below are the accounts and codes that will be referenced.
This account will capture any change in the Salary account by comparing this period Salary to last period. We will also do the first check to make sure we don’t count the first month of your version as a raise.
Select the Salary account.
Click the Create New Calculated Account button and create Raises.
Enter 0 into the formula field and save the account.a. We do this as the formula we will right refers back to this account and it does not exist yet.
Replace the 0 in the formula with the following:
ROW.Salary[time=this-1] = 0,0,
ROW.Salary - ROW.Salary[time=this-1] + ROW.Raises[time=this-1]
This account is a simple subtraction of the Raises from the Salary account.
Select the Raises account.
Click the Create New Calculated Account button and create Baseline Salary.
Enter the following formula:
ROW.Salary - ROW.Raises
Your personnel/workforce sheet now has the following data:
Salary - default calculation that goes up when the user gets a raise.
Raises - new account to hold the change in Salary as raises.
Baseline Salary - new account to hold the starting salary constant.
You can do this type of work and analyze it via reporting or connect it up to your GL accounts with formulas or links.