QAD 2016 Enterprise Edition Training Guides > Financials-Advanced > Excel Integration > Hands-On Exercises
  PPT
Hands-On Exercises
Log in to 10USACO or 22UKCO
Exercise 1: GL Account Excel Integration
1 In GL Account Excel Integration (25.3.13.5), right-click in the grid and select Load Accounts.
2 Right right-click in the grid again and select Export to Excel for Maintenance.
3 Enter a file name and a location for your file.
4 Go to the dump location and open the Excel file.
The worksheet displays field labels on the first row, and field names on the second row. Do not modify these two rows.
5 Make some modifications to the rows below. Add some accounts, modify others.
Add the following account:
 
Field
Value
Account
2059
Description
Supplier Open Bal Trans
Type
Standard
Category
Liability
Proj Analysis
Cost Center Account
Sub-Account
Intercompany Account
Quantity
Base Currency
SAF
Auto
Fixed IC
Val Date
False
Active
True
Budget Enabled
True
Debit/Credit
True
Balance Account
True
TC Revaluation in BC
TC Revaluation in SC
Analysis Limit
Analysis
None
Manual Posting
 
Consolidation Method
Current
Category
Asset
Balance/P&L
Balance
Debit/Credit
Debit
Auto/Manual
Manual
Sub-Account
Blank
6 Save when you are done. Return to the QAD application.
7 In the GL Account Excel Integration grid, right-click and select Import from Excel.
8 Select your location and file name.
9 Click Open.
10 In GL Account Excel Integration, verify that the data is as you entered in Excel.
If a description is too long, an error message displays:
“Value is too long.”
You can correct this in the grid.
11 Save your changes in GL Account Excel Integration.
Exercise 2: Business Relation Excel Integration
This process is similar to that for GL accounts. You will export the business relation data to Excel, and use the file as a template for data load.
1 Open Business Relation Excel Integration (36.1.4.3.5).
2 Right-click the grid and select Export to Excel for Maintenance.
This option exports a blank spreadsheet in the format required for loading business relations.
3 Open the Excel file you exported.
The first row on the Excel file shows field labels, the second row database field names. Do not modify these rows. Do not change the order of the columns.
4 In the exported Excel file, add a row for the new business relation:
10USACO
 
Field
Value
Business Relation Code
10-C1006
Business Relation Name
Global Aluminum US
Search Name
Global Aluminium
Act
True
Address Type
Headoffice
Address Line 1
11 Reston Street
Zip Code
07310
City
Jersey City
State
NJ
Country
US
Tax Zone
USA
Lang
US
 
 
Contact
 
Name
Muller
Initials
J
Gender
Male
Function
Sales Manager
Telephone
+1-201-75800123
Lang
US
Primary
True
22UKCO
 
Field
Value
Business Relation Code
22-C1003
Business Relation Name
Global Aluminium UK
Search Name
Global Aluminium UK
Act
True
Address Type
Headoffice
Address Line 1
11 Amberley Way
Zip Code
BS1 7EQ
City
Bristol
Country
GB
Tax Zone
GB
Lang
US
Primary
True
 
 
Contact
 
Name
Bedford
Initial
M
Gender
Male
Function
Sales Manager
Telephone
+44-117-7580013
Lang
US
5 Return to the QAD application.
6 In the Business Relation Excel Integration grid, right-click and select Import from Excel.
Exercise 3: Supplier Excel Integration
1 Open Supplier Excel Integration (28.20.1.5).
2 Right-click in the grid and select Export to Excel for Maintenance.
3 Enter a file name and a location for your file.
4 Go to the dump location and open the Excel file.
5 Create supplier 10S1007 in entity 10USACO:
 
Field
Value
Supplier
10S1007
Business Relation
10-C1006
Default Currency
USD
Credit Terms
2M
Invoice Status Code
999
Control GL Profile (Invoice)
APcontrol3rdparty
Control GL Profile (Credit Note)
APcontrol3rdparty
Control GL Profile (Pre-payment)
APcontrol3rdparty
Sub-Account Profile
Mech
Purchase Account GL Profile
Purchase
Supplier Type
OTH
Taxable Supplier
True
Tax Zone
USA
Tax Class
H
Tax Nature
None
Tax Level
None
Create supplier 22S1003 in entity 22UKCO.
 
Field
Value
Supplier Code
22S1003
Business Relation
22-C1003
Default Currency
GBP
Credit Terms
2M
Invoice Status Code
999
Control GL Profile (Invoice)
APcontrol3rdparty
Control GL Profile (Credit Note)
APcontrol3rdparty
Control GL Profile (Pre-payment)
APcontrol3rdparty
Sub-Account Profile
Mech
Purchase Account Profile
Purchase
Supplier Type
OTH
Taxable
Yes
Tax Zone
GB
Tax Class
H
Tax Nature
None
Tax Level
None
Exercise 4: Supplier Bank Number Excel Integration
1 Open Supplier Bank Number Excel Integration (28.20.1.7).
2 Right-click in the grid and select Export to Excel for Maintenance.
3 Save the Excel spreadsheet.
4 Open the exported, blank spreadsheet and create the following supplier bank account:
In entity 10USACO:
 
Field
Value
Bank Number
US800800
Formatted Number
US800800
Active
True
SWIFT Code
Blank
Default
True
Validation
XX
Own Bank Account
55667342
Bank GL Account
1100
Payment Format
CHECK-AP
Type
CREDITOR
Parent Object Code
10S1007
Currency
USD
Entity Code
10USACO
In entity 22UKCO:
 
Field
Value
Bank Number
UK800800
Formatted Number
UK800800
Active
True
SWIFT Code
Blank
Default Bank
True
Validation
XX
Own Bank Account
UK5564999
Bank GL Account
1120
Payment Format
UK-Check
Parent Type
CREDITOR
Parent Object Code
22S1003
Currency
GBP
Entity Code
22UKCO
Exercise 5: Supplier Open Items
1 Open Supplier Opening Balance Create (28.1.15).
2 In entity 10USACO, enter the following data:
 
Field
Record 1
Record 2
Record 3
Posting Year/GL Period
System date
System date
System date
Posting Date
Last day of current GL period
Last day of current GL period
Last day of current GL period
GL Transfer Account
1670
1670
1670
Action
Save
Save
Save
 
 
 
 
 
Grid Fields
Supplier Code
10S1007
10S1007
10S1007
Invoice Type
Invoice
Credit Note
Adjustment
Sub-Acct
Mech
Mech
Mech
Daybook Code
SINV
SCNHO
SUPADJ
Invoice Voucher
99900001
8880002
7770003
Invoice Date
Last day of current GL period
Last day of current GL period
Last day of current GL period
Invoice Tax Point Date
Last day of current GL period
Last day of current GL period
Last day of current GL period
Invoice Reference
INV1007-1
CN1007-1
BE1007-1
Invoice Description
Inv Po1001
Corr on inv
Prepayment
Posting Text
InvPo1001
Corr on inv
Prepaym
TC Invoice Amount
2,000
500
250
Curr
USD
USD
USD
Bank Number
US800800
US800800
US800800
Invoice Due Date
1 month from invoice date
1 month from invoice date
1 month from invoice date
Credit Term
1M
1M
1M
Invoice Status Code
OK2PAY
OK2PAY
OK2PAY
Daybook Set Code
10PURCH
10PURCH
10PURCH
In entity 22UKCO, enter the following data:
 
Field
Record 1
Record 2
Record 3
Posting Year/GL Period
System date
System date
System date
Posting Date
Last day of current GL period
Last day of current GL period
Last day of current GL period
GL Transfer Account
1670
1670
1670
Action
Save
Save
Save
 
 
 
 
 
Grid Fields
Supplier Code
22S1003
22S1003
22S1003
Invoice Type
Invoice
Creditnote
Adjustment
Sub-Acct
Mech
Mech
Mech
Daybook Code
SINV
SCNHO
SUBADJ
Invoice Voucher
99900001
8880002
7770003
Invoice Date
Last day of current GL period
Last day of current GL period
Last day of current GL period
Invoice Tax Point Date
Last day of current GL period
Last day of current GL period
Last day of current GL period
Invoice Reference
INV1003-1
CN1003-1
BE1003-1
Invoice Description
Inv Po1001
Corr on inv
Prepaym
Posting Text
InvPo1001
Corr on inv
Prepaym
TC Invoice Amount
2,000
500
250
Curr
GBP
GBP
GBP
Bank Number
UK800800
UK800800
UK800800
Invoice Due Date
1 month from invoice date
1 month from invoice date
1 month from invoice date
Credit Term
1M
1M
1M
Invoice Status Code
OK2PAY
OK2PAY
OK2PAY
Daybook Set Code
22PURCH
22PURCH
22PURCH
Exercise 6: Journal Entries
1 Open the QMI Documentation/Financial Activities folder on the desktop.
2 Download the file accrualJE USD.xlsx if you are in 10USACO or download the file accrualJE GBP.xlsx if you are in 22UKCO.
The file is saved to My Documents/Downloads.
3 Open Journal Entry Excel Integration (25.13.1.6).
4 Import the file accrualJE USD.xlsx if you are in 10USACO or import the file accrualJE GBP.xlsx if you are in 22UKCO. Post the transactions in the period of the current system date (change the posting date, GL calendar year, and GL period to match the system date, if required):
 
Field
Value
Daybook
TRAJE
Sub-Account
Gserv....
Cost Center
Adm
5 In both entities 10USACO and 22UKCO, the following are the transaction posting lines:
 
Account
Debit
Credit
7000 (Travel)
10,000
 
7010 (ICT)
900
 
7200 (Repair & Maint)
1,100
 
2470 (Accrual)
 
12,000
6 Save the transactions.
7 When complete, repeat the previous step, but save the transaction as a template.
 
Field
Value
Daybook
Template
Template Code
M-EXP-ACCR-1
You should now understand how easy to use QAD Financials Excel integration is, and how useful it is for loading data during implementation and modifying data in batches.
Exercise 7: Journal Entry Cross-Company Excel Integration
1 Open the QMI Documentation/Financial Activities folder on the desktop.
2 From the Excel Integration folder in the Financials Activities folder, download the file Cross-Company-JE.xlsx.
When running this exercise in 22UKCO, change the source entity in the data loaded from 10USACO to 22UKCO.The file is saved to My Documents/Downloads.
3 Open Journal Entry Cross Co Excel Integration (25.13.1.10).
4 Right-click in the grid and select Import from Excel.
Note: You may need to change the GL calendar year and posting date to 2014.
5 Save the uploaded file in Journal Entry Cross Co Excel Integration.
The status is updated to Processed in Journal Entry Cross Co Excel Integration.
6 Go to Journal Entry View to view the resultant postings. The following are the transaction posting lines:
 
Account
Entity
Debit
Credit
7040
10USACO
450,000
 
1975
11CANCO
 
320,000
1975
12MEXCO
 
130,000