Templates – Dillner's Accounting Tools

Create a transaction in the Template window for entries that are calculations. The Template does not become a transaction until it is selected from the drop-down list of the Template field in the Write Up>General Journal window.

Create a Template

Select Setup>Templates.

Create a Template using in-cell formulas as in any other window. The difference is that the formula will always be displayed in this window. When the Template is selected in the General Journal window, the results of the formula will be displayed and posted. Use the following syntax to write formulas:

Operands:
+ = Add
- = Subtract
* = Multiply
/ = Divide

Account Code Wildcards:
% = all characters following the symbol
_ = any character in the position of the underscore ( _ )

A Function will return various values based on the Function. Multiple Functions may be used in calculations with mathematical operands. To make it easier, there is a drop-down list of Functions on the Template window which you may copy and paste.

Following are detailed explanations for each of the Functions:

NAME	FUNCTION	DESCRIPTION
Current Period	CurrentPeriod	Inserts current processing period as a number value that can be used in calculations with GL account data.
Account Balance	BALANCE(account lookup)	Returns the current amount balance of a GL account. Example: BALANCE(103)
Account Balance Prior Period	BALANCE(account lookup,1)	Returns the prior amount balance of a GL account as of the end of the prior period where the number following the "," determines how many periods prior. Example: BALANCE(103,1)
Account Quantity Balance	QTYBALANCE(account lookup)	Returns the current quantity balance of a GL account. Example: QTYBALANCE(103)
Account Quantity Balance Prior Period	QTYBALANCE(account lookup,1)	Returns the prior quantity balance of a GL account as of the end of the prior period where the number following the "," determines how many periods prior. Example: QTYBALANCE(103,1)
Account Period Activity	ACTIVITY(account lookup)	Returns the current period amount activity of a GL account for the current period. Example: ACTIVITY(103)
Account Period Prior Period Activity	ACTIVITY(account lookup,1)	Returns the prior period amount activity of a GL account for the prior period where the number following the "," determines how many periods prior. Example: ACTIVITY(103,1)
Account Period Quantity Activity	QTYACTIVITY(account lookup)	Returns the current period quantity activity of a GL account. Example: QTYACTIVITY(103)
Account Period Quantity Prior Period Activity	QTYACTIVITY(account lookup,1)	Returns the prior period quantity activity of a GL account for the prior period where the number following the "," determines how many periods prior. Example: QTYACTIVITY(103,1)
Account YTD Activity	YTDACTIVITY (account lookup)	Returns the year-to-date amount activity of a GL account. Example: YTDACTIVITY(103)
Account YTD Prior Period Activity	YTDACTIVITY (account lookup,1)	Returns the year-to-date amount activity of a GL account through the end of the prior period where the number following the "," determines how many periods prior. Example: YTDACTIVITY(103,1)
Account YTD Quantity Activity	YTDQTYACTIVITY (account lookup)	Returns the year-to-date quantity activity of a GL account. Example: YTDQTYACTIVITY(103)
Account YTD Quantity Prior Period Activity	YTDQTYACTIVITY (account lookup,1)	Returns the year-to-date quantity activity of a GL account for the prior period where the number following the "," determines how many periods prior. Example: YTDQTYACTIVITY(103,1)
Current Assets Balance	CBALANCE(Current Assets)	Returns Current Assets Balance
Current Assets Balance Prior Period	CBALANCE(Current Assets,1)	Returns Current Assets Balance as of the end of the prior period where the number following the "," determines how many periods prior
Current Assets Period Activity	CACTIVITY(Current Assets)	Returns Current Assets Period Activity for the current period
Current Assets Prior Period Activity	CACTIVITY(Current Assets,1)	Returns Current Assets Period Activity for the prior period where the number following the "," determines how many periods prior
Current Assets Activity YTD	YTDCACTIVITY(Current Assets)	Returns Current Assets Activity YTD through the current period
Current Assets Activity YTD Prior Period	YTDCACTIVITY(Current Assets,1)	Returns Current Assets Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Other Assets Balance	CBALANCE(Other Assets)	Returns Other Assets Balance
Other Assets Balance Prior Period	CBALANCE(Other Assets, 1)	Returns Other Assets Balance as of the end of the prior period where the number following the "," determines how many periods prior
Other Assets Period Activity	CACTIVITY(Other Assets)	Returns Other Assets Period Activity for the current period
Other Assets Prior Period Activity	CACTIVITY(Other Assets,1)	Returns Other Assets Period Activity for the prior periods where the number following the "," determines how many periods prior
Other Assets Activity YTD	YTDCACTIVITY(Other Assets)	Returns Other Assets Activity YTD through the current period
Other Assets Activity YTD Prior Period	YTDCACTIVITY(Other Assets,1)	Returns Other Assets Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Fixed Assets Balance	CBALANCE(Fixed Assets)	Returns Fixed Assets Balance
Fixed Assets Balance Prior Period	CBALANCE(Fixed Assets,1)	Returns Fixed Assets Balance as of the end of the prior period where the number following the "," determines how many periods prior
Fixed Assets Period Activity	CACTIVITY(Fixed Assets)	Returns Fixed Assets Period Activity for the current period
Fixed Assets Prior Period Activity	CACTIVITY(Fixed Assets,1)	Returns Fixed Assets Period Activity for the prior period where the number following the "," determines how many periods prior
Fixed Assets Activity YTD	YTDACTIVITY(Fixed Assets)	Returns Fixed Assets Activity YTD through the current period
Fixed Assets Activity YTD Prior Period	YTDCACTIVITY(Fixed Assets,1)	Returns Fixed Assets Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Long Term Assets Balance	CBALANCE(Long Term Assets)	Returns Long Term Assets Balance
Long Term Assets Balance Prior Period	CBALANCE(Long Term Assets,1)	Returns Long Term Assets Balance as of the end of the prior period where the number following the "," determines how many periods prior
Long Term Assets Period Activity	CACTIVITY(Long Term Assets)	Returns Long Term Assets Period Activity for the current period
Long Term Assets Prior Period Activity	CACTIVITY(Long Term Assets,1)	Returns Long Term Assets Period Activity for the prior period where the number following the "," determines how many periods prior
Long Term Assets Activity YTD	YTDCACTIVITY(Long Term Assets)	Returns Long Term Assets Activity YTD through the current period
Long Term Assets Activity YTD Prior Period	YTDCACTIVITY(Long Term Assets,1)	Returns Long Term Assets Activity YTD through the end of the prior period where the number following the "," determines how many period prior
Current Liabilities Balance	CBALANCE(Current Liabilities)	Returns Current Liabilities Balance
Current Liabilities Balance Prior Period	CBALANCE(Current Liabilities,1)	Returns Current Liabilities Balance as of the end of the prior period where the number following the "," determines how many periods prior
Current Liabilities Period Activity	CACTIVITY(Current Liabilities)	Returns Current Liabilities Period Activity for the current period
Current Liabilities Prior Period Activity	CACTIVITY(Current Liabilities,1)	Returns Current Liabilities Period Activity for the prior period where the number following the "," determines how many periods prior
Current Liabilities Activity YTD	YTDCACTIVITY(Current Liabilities)	Returns Current Liabilities Activity YTD through the current period
Current Liabilities Activity YTD Prior Period	YTDCACTIVITY(Current Liabilities,1)	Returns Current Liabilities Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Long Term Liabilities Balance	CBALANCE(Long Term Liabilities)	Returns Long Term Liabilities Balance
Long Term Liabilities Balance Prior Period	CBALANCE(Long Term Liabilities,1)	Returns Long Term Liabilities Balance as of the end of the prior period where the number following the "," determines how many periods prior
Long Term Liabilities Period Activity	CACTIVITY(Long Term Liabilities)	Returns Long Term Liabilities Period Activity for the current period
Long Term Liabilities Prior Period Activity	CACTIVITY(Long Term Liabilities,1)	Returns Long Term Liabilities Period Activity for the prior period where the number following the "," determines how many periods prior
Long Term Liabilities Activity YTD	YTDCACTIVITY(Long Term Liabilities)	Returns Long Term Liabilities Activity YTD through the current period
Long Term Liabilities Activity YTD Prior Period	YTDCACTIVITY(Long Term Liabilities,1)	Returns Long Term Liabilities Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Capital Balance	CBALANCE(Capital)	Returns Capital Balance
Capital Balance Prior Period	CBALANCE(Capital,1)	Returns Capital Balance as of the end of the prior period where the number following the “,” determines how many periods prior
Capital Period Activity	CACTIVITY(Capital)	Returns Capital Period Activity for the current period
Capital Prior Period Activity	CACTIVITY(Capital,1)	Returns Capital Period Activity for the prior period where the number following the “,” determines how many periods prior
Capital Activity YTD	YTDCACTIVITY(Capital)	Returns Capital Activity YTD through the current period
Capital Activity YTD Prior Period	YTDCACTIVITY(Capital,1)	Returns Capital Activity YTD through the end of the prior period where the number following the “,” determines how many periods prior
Income Period Activity	CACTIVITY(Income)	Returns Income Period Activity for the current period
Income Period Prior Period Activity	CACTIVITY(Income,1)	Returns Income Period Activity for the prior period where the number following the "," determines how many periods prior
Income Activity YTD	YTDCACTIVITY(Income)	Returns Income Activity YTD through the current period
Income Activity YTD Prior Period	YTDCACTIVITY(Income,1)	Returns Income Activity YTD through the end of the prior period where the number following the "," determines how many period prior
Cost Period Activity	CACTIVITY(Cost)	Returns Cost Period Activity for the current period
Cost Prior Period Activity	CACTIVITY(Cost,1)	Returns Cost Period Activity for the prior period where the number following the "," determines how many periods prior
Cost Activity YTD	YTDCACTIVITY(Cost)	Returns Cost Activity YTD through the current period
Cost Activity YTD Prior Period	YTDCACTIVITY(Cost,1)	Returns Cost Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Expense Period Activity	CACTIVITY(Expense)	Returns Expense Period Activity for the current period
Expense Prior Period Activity	CACTIVITY(Expense,1)	Returns Expense Period Activity for the prior period where the number following the "," determines how many periods prior
Expense Activity YTD	YTDCACTIVITY(Expense)	Returns Expense Activity YTD through the current period
Expense Activity YTD Prior Period	YTDCACTIVITY(Expense,1)	Returns Expense Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Other Income Period Activity	CACTIVITY(Other Income)	Returns Other Income Period Activity for the current period
Other Income Prior Period Activity	CACTIVITY(Other Income,1)	Returns Other Income Period Activity for the prior period where the number following the "," determines how many periods prior
Other Income Activity YTD	YTDCACTIVITY(Other Income)	Returns Other Income Activity YTD through the current period
Other Income Activity YTD Prior Period	YTDCACTIVITY(Other Income,1)	Returns Other Income Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Other Expense Period Activity	CACTIVITY(Other Expense)	Returns Other Expense Period Activity for the current period
Other Expense Prior Period Activity	CACTIVITY(Other Expense,1)	Returns Other Expense Period Activity for the prior period where the number following the "," determines how many periods prior
Other Expense Activity YTD	YTDCACTIVITY(Other Expense)	Returns Other Expense Activity YTD through the current period
Other Expense Activity YTD Prior Period	YTDCACTIVITY(Other Expense,1)	Returns Other Expense Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Depreciation Period Activity	CACTIVITY(Depreciation)	Returns Depreciation Period Activity for the current period
Depreciation Prior Period Activity	CACTIVITY(Depreciation,1)	Returns Depreciation Period Activity for the prior period where the number following the "," determines how many periods prior
Depreciation Activity YTD	YTDCACTIVITY(Depreciation)	Returns Depreciation Activity YTD through the current period
Depreciation Activity YTD Prior Period	YTDCACTIVITY(Depreciation,1)	Returns Depreciation Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Amortization Period Activity	CACTIVITY(Amortization)	Returns Amortization Period Activity for the current period
Amortization Prior Period Activity	CACTIVITY(Amortization,1)	Returns Amortization Period Activity for the prior period where the number following the "," determines how many periods prior
Amortization Activity YTD	YTDCACTIVITY(Amortization)	Returns Amortization Activity YTD through the current period
Amortization Activity YTD Prior Period	YTDCACTIVITY(Amortization,1)	Returns Amortization Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Tax Period Activity	CACTIVITY(Tax)	Returns Tax Period Activity for the current period
Tax Prior Period Activity	CACTIVITY(Tax,1)	Returns Tax Period Activity for the prior period where the number following the “,” determines how many periods prior
Tax Activity YTD	YTDCACTIVITY(Tax)	Returns Tax Activity YTD through the current period
Tax Activity YTD Prior Period	YTDCACTIVITY(Tax,1)	Returns Tax Activity YTD through the end of the prior period where the number following the “,” determines how many periods prior
Payroll Period Activity	CACTIVITY(Payroll)	Returns Payroll Period Activity for the current period
Payroll Prior Period Activity	CACTIVITY(Payroll,1)	Returns Payroll Period Activity for the prior period where the number following the "," determines how many periods prior
Payroll Activity YTD	YTDCACTIVITY(Payroll)	Returns Payroll Activity YTD through the current period
Payroll Activity YTD Prior Period	YTDCACTIVITY(Payroll,1)	Returns Payroll Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Accrual Period Activity	CACTIVITY(Accrual)	Returns Accrual Period Activity for the current period
Accrual Prior Period Activity	CACTIVITY(Accrual,1)	Returns Accrual Period Activity for the prior period where the number following the "," determines how many periods prior
Accrual Activity YTD	YTDCACTIVITY(Accrual)	Returns Accrual Activity YTD through the current period
Accrual Activity YTD Prior Period	YTDCACTIVITY(Accrual,1)	Returns Accrual Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Inventory Balance	CBALANCE(Inventory)	Returns Inventory Balance
Inventory Balance Prior Period	CBALANCE(Inventory,1)	Returns Inventory Balance as of the end of the prior period where the number following the "," determines how many periods prior
Inventory Activity	CACTIVITY(Inventory)	Returns Inventory Activity for the current period
Inventory Prior Period Activity	CACTIVITY(Inventory,1)	Returns Inventory Activity for the prior period where the number following the "," determines how many periods prior
Inventory Activity YTD	YTDCACTIVITY(Inventory)	Returns Inventory Activity YTD through the current period
Inventory Activity YTD Prior Period	YTDCACTIVITY(Inventory,1)	Returns Inventory Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Accounts Receivable Balance	CBALANCE(Accounts Receivable)	Returns Accounts Receivable Balance
Accounts Receivable Balance Prior Period	CBALANCE(Accounts Receivable,1)	Returns Accounts Receivable Balance as of the end of the prior period where the number following the "," determines how many periods prior
Accounts Receivable Activity	CACTIVITY(Accounts Receivable)	Returns Accounts Receivable Activity for the current period
Accounts Receivable Prior Period Activity	CACTIVITY(Accounts Receivable,1)	Returns Accounts Receivable Activity for the prior period where the number following the "," determines how many periods prior
Accounts Receivable Activity YTD	YTDCACTIVITY(Accounts Receivable)	Returns Accounts Receivable Activity YTD through the current period
Accounts Receivable Activity YTD Prior Period	YTDCACTIVITY(Accounts Receivable, 1)	Returns Accounts Receivable Activity YTD through the end of the prior period where the number following the "," determines how many periods prior
Accounts Payable Balance	CBALANCE(Accounts Payable)	Returns Accounts Payable Balance
Accounts Payable Balance Prior Period	CBALANCE(Accounts Payable,1)	Returns Accounts Payable Balance as of the end of the prior period where the number following the "," determines how many periods prior
Accounts Payable Activity	CACTIVITY(Accounts Payable)	Returns Accounts Payable Activity for the current period
Accounts Payable Prior Period Activity	CACTIVITY(Accounts Payable,1)	Returns Accounts Payable Activity for the prior period where the number following the "," determines how many periods prior
Accounts Payable Activity YTD	YTDCACTIVITY(Accounts Payable)	Returns Accounts Payable Activity YTD through the current period
Accounts Payable Activity YTD Prior Period	YTDCACTIVITY(Accounts Payable, 1)	Returns Accounts Payable Activity YTD through the end of the prior period where the number following the "," determines how many periods prior

Use the Insert Row, Remove Row, Move Row Up, and Move Row Down buttons to position the rows as desired.

Examples

1: Interest

The following example is for Interest calculation for a Notes Payable account where the interest rate is a fixed monthly amount of .5% and the total amount of the check is posted to the Notes Payable account. Run this Template transaction at the end of processing for the month (after the check to the Notes Payable account has been recorded).

This formula calculates the monthly interest of .5% of the balance as of the end of the prior month (balance 266 minus activity 266) and debits the 550 account and credits the 266 account with the amount. This works even when a payment is not actually made as interest is assessed whether a payment is made or not.

A similar calculation could be entered directly in the Write Up>Checks window, but the formula would have to be entered each month. Using a Template transaction to spread the payment allows the bookkeeper to simply record the full payment when entering the checks and then just run the Template in the General Journal window at the end of processing.

The following formula can be copied and pasted into the Template by highlighting the formula, then pressing Ctrl+C. Double-click in the desired cell in the Template and press Ctrl+V. If the account codes in this example are different than the account codes to be used, edit them after pasting the formula into the cell.

Row 1 & 2: (BALANCE(266)-ACTIVITY(266))*.005

2: Profit Center Allocations

Spread Rent payment to Profit Centers where Rent is posted to the first Profit Center account (52001) and where Rent is not the same each month, i.e., a percentage of sales. In the example below, there are Rent accounts for three Profit Centers where the last two digits of the account code specifies the Profit Center.

This debits 52002 with 40% and debits 52003 with 20% of the total rent check for the month and then credits 52001 for 60%, leaving 40% for 52001.

The following formulas can be copied and pasted into the Template by highlighting the formulas and then pressing Ctrl+C. Double-click in the desired cell in the Template and press Ctrl+V. If the account codes in this example are different than the account codes to be used, edit them after pasting the formula into the cell.

Row 1: ACTIVITY(52001)*.4

Row 2: ACTIVITY(52001)*.2

Row 3: ACTIVITY(52001)*.6

3: Ending Inventory by COGS Percentage

Purchases Recorded to Purchases (Cost) Accounts

Set up formulas in Template using the example below for all inventory items to be adjusted based on a predetermined cost of sales percentage (Sales - GP%).

After an Ending Inventory Template has been set up, use the following example to post all purchases to the Cost account. Then at the end of the processing period, open the Write Up>General Journal window and select Ending Inventory as the Template. All necessary adjustments to Inventory and Cost accounts will be calculated in a transaction. Click Save to save the transaction. The Inventory Analysis report will report purchases for the period and year-to-date using this method.

All Rows: Edit percentage amount (.00) to be the percentage of cost (the inverse of GP%).

In this example the 4xx accounts are the Income accounts, the 5xx accounts are the Inventory accounts, and the 8xx accounts are the Cost (Purchase) accounts. Edit the formula to use the appropriate account codes.

Place the formula in the Debit column for the Inventory account and in the Credit column for the Cost (Purchase) account. Replace the .00 below with the decimal value of the desired percentage.

ACTIVITY(804)+(ACTIVITY(404)*.00)

Purchases Recorded to a Common Inventory Account

Set up formulas in Template using the example below for all inventory items where inventory is to be adjusted based on a predetermined cost of sales percentage (Sales - GP%).

After an Ending Inventory Template has been set up, using the following example, post all purchases to the Cost account; and then at the end of the processing period, open the Write Up>General Journal window and select Ending Inventory as the Template. All necessary adjustments to Inventory and Cost accounts will be calculated in a transaction. Click Save to save the transaction. The Inventory Analysis report will not be valid for this method of recording inventory purchases. If an Inventory Analysis report is needed, follow the previous method where purchases are recorded to the cost accounts.

All Rows: Edit percentage amount (.00) to be the percentage of cost (the inverse of GP%).

In this example the 3xx accounts are the Income accounts, the 130 account is the Inventory account, and the 4xx accounts are the Cost (Purchase) accounts. Edit the formula using the appropriate account codes.

Place the formula in the Debit column for the Cost (Purchase) account and in the Credit column for the Inventory account. Replace the .00 below with the decimal value of the desired percentage.

ACTIVITY(303)*-.00

Purchases Recorded to Multiple Inventory Accounts

Use the previous method (Purchases Recorded to a Common Inventory Account) except replace the 130 account in the example with the individual inventory account codes.

4: Fuel Blending

The following example shows the formula to generate the gallons and purchase amount for Plus and Super grades of fuel when only the Regular and Ultra grades are purchased. In this scenario the Plus grade is comprised of 71% Regular and 29% Ultra and the Super is comprised of 14% Regular and 86% Ultra. If the client has only have one mid-grade fuel, then use only rows 1 through 4. The 8xx accounts are the Purchase (Cost) accounts and the 4xx accounts are the Sales accounts.

Click here to use the Dillner's Power Tool - Formula Generator for Product Blending

Row 1 Debit Formula: ((-.71*QTYACTIVITY(457))*(ACTIVITY(856)/QTYACTIVITY(856)))+((-.29*QTYACTIVITY(457))*(ACTIVITY(858)/QTYACTIVITY(858)))

Row 1 Debit Qty Formula: QTYACTIVITY(457)*-1

Row 2 Debit Formula: (.71*QTYACTIVITY(457))*(ACTIVITY(856)/QTYACTIVITY(856)

Row 2 Debit Qty Formula: QTYACTIVITY(457)*.71

Row 3 Debit Formula: (.29*QTYACTIVITY(457))*(ACTIVITY(858)/QTYACTIVITY(858)

Row 3 Debit Qty Formula: QTYACTIVITY(457)*.29

Row 4 Debit Formula: 0 - Since there are calculations that sometimes create rounding variances of a penny, use this row to correct any variance created in the above set.

Row 5 Debit Formula: ((-.14*QTYACTIVITY(460))*(ACTIVITY(856)/QTYACTIVITY(856)))+((-.86*QTYACTIVITY(460))*(ACTIVITY(858)/QTYACTIVITY(858)))

Row 5 Debit Qty Formula: QTYACTIVITY(460)*-1

Row 6 Debit Formula: (.14*QTYACTIVITY(460))*(ACTIVITY(856)/QTYACTIVITY(856)

Row 6 Debit Qty Formula: QTYACTIVITY(460)*.14

Row 7 Debit Formula: (.86*QTYACTIVITY(460))*(ACTIVITY(858)/QTYACTIVITY(858)

Row 7 Debit Qty Formula: QTYACTIVITY(460)*.86

Row 8 Debit Formula: 0 Since there are calculations that sometimes create rounding variances of a penny, use this row to correct any variance created in the above set.

Results of the Fuel Blending template:

In the case of Fuel Blending, it is important to have negative Debits on rows 2,3,4,6 and 7 because the Inventory Analysis report is looking at Debit entries only to calculate Purchase totals. In other applications of the Template where Inventory is not an issue, these could have been created as positive Credit entries.