Hands-On Exercises (US)

QAD 2016 Enterprise Edition Training Guides > Financials-Advanced > Allocations > Hands-On Exercises (US)

Exercise 1: Constant Value Source

Every month, housing expenses must be allocated to the following cost centers:

• Adm: 40%

• Log: 10%

• Mar: 20%

• Mfg: 30%

In this exercise, you will create the corresponding allocation, create an allocation batch, and run the batch for the GL period prior to the current period.

1 In GL Account Create (25.3.13.1), create account 7399.


Field	Data
Account	7399
Description	HC alloc offset
GL Type	Standard Account
Budget Enabled	Yes
Category	Expense

Posting tab
Balance/P&L	Profit and Loss Account
Auto/Manual	Manual
Debit/Credit	Debit

Analysis tab
Analysis	None

2 In Journal Entry Create (25.13.1.1), create a template for the posting:

Let the year and posting date default.


Field	Data
Daybook Code	Template
Save as Template	Yes
Description	CVS alloc template
Template Code	Templ 1

The following are the postings in 10USACO:


GL Account	GL Desc.	Sub-Account	Cost Center	BC Debit	BC Credit
7399	HC alloc offset				100.00
7300	Housing Expense	Gserv	Adm	40.00
7300	Housing Expense	Gserv	Log	10.00
7300	Housing Expense	Gserv	Mar	20.00
7300	Housing Expense	Gserv	Mfg	30.00

3 Save your changes.

4 In GL Allocation Create (25.3.22.1), create an allocation


Field	Data
Allocation Code	CVS H01
Description	CVS H01
Source Type	Constant Value
From Layers	Primary
Quantity	0
BC Amount	10,000.00
Fraction type	Constant Factor
From Layers	Primary
Constant Fraction	1.00
Daybook Code	TRAJE
Template Code	Templ 1
Layer Code	Trans-Approve

5 Save your allocation.

6 In GL Allocation Batch Create (25.3.22.6), create the following allocation batch:


Field	Data
Allocation Batch Code	ConstVS
Description	ConstVS
JE Group	GR1
Alloc Code	CVS H01

7 Save the allocation batch.

8 Run GL Allocation Batch Run Execute (25.13.9) in the previous period of the system date.


Field	Data
Allocation Batch	ConstVS
Source Period	Enter the period previous to the current.
Target GL Period	Specify the same period as the source.
Posting Date	The last day of the target GL period.

9 Click Search.


Field	Data
Grid
Sequence	1
JE Group	GR1
Execute	Yes
Allocation Code	CVS H01
BC Amount	10,000.00
Posting Amount	10,000.00
Posting Reference	2013/TRAJE/nnn

10 Click Execute to run the allocation batch.

11 In Journal Entry View (25.13.1.3), search for transactions in daybook TRAJE with the same posting date as the allocation batch posting date you entered.

12 Review the transaction.

13 The same result can be obtained using another QAD Financials functionality: how?

Exercise 2: Allocation using WBS Source 10USACO

In this exercise, you will create a GL allocation for housing costs using:

1 Using GL Account Create (25.3.13.1) or GL Account Excel Integration (25.3.13.5), create the following accounts:


Acct	Desc	GL Type	Cat.	Analysis	Bal/P&L	DR/CR	Auto/Manual
7301	HC1	Standard Acct	Expense	None	P&L	Debit	Manual
7302	HC2	Standard Acct	Expense	None	P&L	Debit	Manual
7303	HC3	Standard Acct	Expense	None	P&L	Debit	Manual

2 In Journal Entry Create (25.13.1.1), create the following journal entry:


Field	Data
Posting Date	Last date of previous GL calendar period
Daybook Code	Template
Description	Alloc H02
Template Code	HC-alloc-1
Save as Template	Yes

The postings are:


GL Account	GL Desc.	Sub-Account	CC	Debit	Credit
7399	HC alloc offset				1000.00
7300	Housing Expense	Gserv	Adm	250.00
7300	Housing Expense	Gserv	Mfg	450.00
7300	Housing Expense	Gserv	R&D	100.00
7300	Housing Expense	Gserv	Sls	200.00

3 Click Save.

4 In Budget Create (25.5.1.1), create the following WBS structure.


Field	Data
Budget Code	HC-1
Description	HC-1
Used for Allocatiion	Yes

5 In the Budget Period tab, right-click in the grid and select Insert a New Row. Update the new row as follows:


Field	Data
Period Code	Period
Start Date	01/01/16
End Date	12/31/16

6 In the Levels tab, right-click in the grid and select Insert a New Row.


Field	Data
WB Level	1
COA Element	General Ledger

7 In the Structures tab, right-click in the grid and select Insert a New Row.

8 Name the topic HC alloc HO2.

9 Right-click the topic and select Topic Properties.

The Topic Properties window opens.

10 In the Topic Properties window, select the COA Link tab.

• Budget Group: leave blank.

• Link by Level: specify 7301, 7302, 7303

11 Click OK.

12 Change the budget status to Operational and save.

13 In GL Allocation Create (25.3.22.1), create the following allocation:


Field	Data
Allocation Code	HC-1
Description	HC-1

General tab
Source Type	WBS Topic
Source WBS	HC alloc H02
From Layers	Primary
From Amt	Balance
Amt By	GL Period
Value Of	BC Amount
Fraction Type	Constant Factor
Constant Fraction	1
Daybook Code	TRAJE
Template Code	HC-alloc-1

14 Save the allocation.

15 In GL Allocation Batch Create (25.3.22.6), create the following batch.


Field	Data
Allocation Batch Code	HC
Description	HC
Right-click in the grid and insert a row.
JE Group	GR-1
Alloc Code	HC-1

16 Save the allocation batch.

17 Open the QMI Documentation/Financial Activities folder on the desktop.

18 From the Allocations folder in the Financials Activities folder, download the file Alloc HC act1 USD.xlsx using File|Download in GoogleDocs.

The file is saved to My Documents/Downloads.

19 Return to the QAD application.

20 Use Journal Entry Excel Integration (25.13.1.6) to import the transactions in the Alloc HC act1 USD.xlsx Excel spreadsheet.

21 The result should be a transaction with the description “Housing exp.”

22 Modify the grid line in Journal Entry Excel Integration so the transaction uses the previous GL period to the current and the last posting date of the previous GL period.

23 Save the newly imported data.

24 Open GL Allocation Batch Run Execute (25.13.9) and specify the following:


Field	Data
Allocation Batch	HC
Source GL Period	Enter the period previous to the current.
Target GL Period	Specify the same period as the source.
Posting Date	Last date of previous GL period
Number of Groups	1
To Be Processed	1

25 Click Search. The allocation batch details display in the grid.


Field	Data
Sequence	1
JE Group	GR-1
Execute	Yes
Allocation Code	HC-1
Source WBS Topic
BC Amount	4,850.00
Posting Amount	4,850.00
Posting Reference	2013/TRAJE/nnn

26 Run the allocation batch.

27 In Journal Entry View (25.13.1.3), review the newly created journal entry.


Field	Data
Posting Date	04/30/13
Daybook Code	TRAJE

Exercise 3: Proportional Allocation

In this exercise, you will create a GL allocation to further allocate housing costs allocated to the cost center Mfg.

1 In Journal Entry Create (25.13.1.1), create the following template:


Field	Data
Year	2013/5
Posting Date	05/31/13
Daybook Code	TEMPLATE
Save as Template	Yes
Description	Proportional Allocation Template
Template Code	Propalloc1


GL Acct	GL Desc	Sub-Acct	CC	Debit	Credit
7300	Housing Expense	GServ	Mfg		100.00
7300	Housing Expense	GServ	WC01	100.00

2 Click Save.

3 In GL Account Create (25.3.13.1), create the following accounts. Ensure you select the Quantity field and enter a unit of measure.


GL Account	Description	GL Type	Category	Budget Enabled	Budget Group
Z9000	Machine HRS	Standard Account	Expense	Yes	<blank>
Z9001	Offset Mach. HRS	Standard Account	Expense	Yes	<blank>


Bal./P&L	DR/CR	Auto/Manual	Quantity	GL Acct UM	Analysis Type (Analysis Tab)
P&L	Debit	Manual	Yes	HRS	Cost Center
P&L	Debit	Manual	Yes	HRS	None

4 In Budget Create (25.5.1.1), create the allocation basis structure, and ensure that the Use Quantity Info field is selected.


Field	Data
Budget Code	Propalloc1
Description	Propalloc1
Used for Allocation	Yes

General tab
Use Quantity Info	Yes
Budget Period tab
Right-click in the grid and insert a row.
Period Code	Period
Start Date	01/01/2013
End Date	12/31/2013

5 In the Levels tab, right-click in the grid and select Insert a New Row.


WB Level	COA Element	Used for Proportional Allocation
1	General Ledger	No (Cleared)
2	Cost Center	Yes (Selected)

6 In the Structures tab, right-click in the grid and select Insert a New Row twice.

7 Name the first topic “7300.”

8 Right-click the row and select Topic Properties.

9 On the COA Link tab, specify 7300 in the Link by Level field. Click OK.

10 Name the second topic “Z9000.”

11 Right-click the second topic row and select Topic Properties.

12 On the COA Link tab, specify Z9000 in the Link by Level field. Click OK.

13 In the first topic, right-click and select Insert Child Row.

14 Name the child topic “MFG” and then select Topic Properties.

15 On the COA Link tab, specify Mfg in the Link by Level field. Click OK.

16 In the second topic, right-click and select Insert Child Row.

17 Name the child topic “WC0x” and right-click and select Topic Properties.

18 On the COA Link tab, specify WC01, WC02, and WC03 in the Link by Level field, separated by commas. Click OK.

19 Go to the Versions tab and enter a version using the GL calendar year and GL period.

20 Change the budget status to Operational and save.

21 In GL Allocation Create (25.3.22.1), create the following allocation.

Ensure that you select the layers in which the data is posted.


Field	Data
Allocation Code	MfghrsHC-1
Description	MfghrsHC-1
Source Type	WBS Topic
Source WBS	Mfg
From Layers	Trans-Approve
From Amt	Balance
Amt By	GL Period
Value Of	BC Amount
Fraction Type	Proportional Fraction
From Layers	Trans-Approve
Denominator WBS	WC0x. See the important note below the table.
Amt By	GL Period
Value of	Qty
Daybook Code	TRAJE
Template Code	Propalloc1

Important: The Denominator WBS must be the same as the topic code you assigned in step 16.

22 In the Proportional Allocation tab, create three lines, one for each cost center. Right-click to insert a new row.


Field	Data
Cost Center Code	WC01
Cost Center Code	WC02
Cost Center Code	WC03

23 Save the allocation.

24 In GL Allocation Batch Create (25.3.22.6), create the following allocation batch.


Field	Data
Allocation Batch Code	MfgHC / MfgHC
JE Group	GR1
Alloc Code	MfghrsHC-1

25 Save your changes.

26 Open the QMI Documentation/Financial Activities folder on the desktop.

27 From the Allocations folder in the Financials Activities folder, download the file Alloc mfgHC act2 USD.xlsx using File|Download in GoogleDocs.

The file is saved to My Documents/Downloads.

28 Return to the QAD application.

29 Use Journal Entry Excel Integration (25.13.1.6) to import transactions in the Alloc mfgHC act2 USD.xlsx Excel spreadsheet.

The result should be a transaction with the description “Machine Hours.”

The actual amount to allocate to the cost center Mfg is 2000. This figure is the result of the previous hands-on.

a Ensure that, for cost center Mfg, you have 2000 on account 7300 (US).

b If you skipped the previous hands-on, post the 2000 as follows:

Daybook: TRAJE

Period: Previous GL period

GL 7300 (US) 2000 Debit USD

GL 2470 (US) 2000 Credit USD

If a sub-account is required, use Gserv.

30 In GL Allocation Batch Run Execute (25.13.9), run the following allocation batch:


Field	Data
Allocation Batch	MfgHC
Source GL Period	Previous GL period
Target GL Period	Previous GL period
Number of Groups	1
To Be Processed	1
Click Apply

31 Go to Journal Entry View (25.13.1.3) and verify the entry.


GL Acct	Sub-Acct	CC	DR	CR
7300	GServ	Mfg	0	714.29
7300	Gserv	WC01	714.29	0
7300	GServ	Mfg	0	809.52
7300	GServ	WC02	809.52	0
7300	GServ	Mfg		476.19
7300	GServ	WC03	476.19	0