F9 Financial Report Writer

1
F9 Financial Report Writer

• Edward Kwang
• President

2
What is F9 Financial Report Writer?

• It is not a general purpose report writer like
  Crystal Reports.
• It is a special purpose Financial Report Writer
  for General Ledger.
• F9 pick up where Elliott GL left off
• Presentation
• Detail Analysis
• It is spreadsheet base and supports
• Excel
• Lotus 123
• Quatro Pro

3
What Can F9 Do?

• Create any financial report you want.
• Professional WYSIWYG report creating
• Never type another general ledger figure into
  your spreadsheet again.
• Play with What If Scenario.
• Create an Executive Information System
• Budget Preparation Write Back

4
Installation of F9

• For Excel Users, do not use the default path for
  Office. Use C\F9 instead.
• Use Tools -gt Add-Ins -gt Browse and choose
  C\F9\F9.XLL.
• Delete WBTRV32.DLL from C\F9 directory.

5
Choose Excel 2000 if you are using Excel 2002
(Office XP)
6
For Excel users, do not use the Microsoft Office
directory that suggested by the system. Use
C\F9 as the Installation Path
7
Choose Add-Ins under Tools
8
Click Browse Button
9
Browse to C\F9 and choose F9.XLL
10
Click OK to confirm F9 Add-In
11
The F9 Tool Bar
12
Rename WBTRV32.DLL in C\F9
13
Setup F9

• Setup Elliott directory
• Choose the Company Number
• Set the current year.

14
Choose Setup under F9 Menu
15
Setup Directory, Comp No Year
16
F9 Basic Formula

• GL( Account, Period ) in Excel
• _at_GL( Account, Period ) in Lotus 123
• Cell Referencing
• A3 (relative cell, change as copying)
• A3 (column is fixed, row is relative)
• C1 (column is relative, row is fixed)
• A1 (both column and row are fixed)

17
The Simple Way of Referencing to a Chart of
Account Period
18
Reference Another Cell in GL
19
Use Copy Paste
20
Reference Both Chart of Account Period to Other
Cells
21
Copy Paste It Doesnt Work
22
Make A column fixed by using A
23
Copy Paste to the Right Cells It Works
24
Copy Paste to the cell below It doesnt work
25
Change B1 to B1 to make sure it always reference
to row 1
26
GL(A2,B1) can be copied to all other cells and
still work
27
Use F9 Chart Function

• We normally use the 12 Periods option.
• Adjust Column Width
• Click on the spread sheet top right corner to
  highlight the entire spread sheet.
• Move mouse in between the column until you see
  the special icon appear and click
• Use Freeze Panes function to make it easy to
  navigate.
• Find the cell that its top left corner meet the
  intersection of the freeze panes

28
Choose Chart under F9 Menu
29
Setup Chart for the 1st time

• Make sure point to the right company
• Make sure Year is correct.
• Change Period from This Month to All Period.

30
Change to All Periods
31
Click Find for Chart of Account
32
Accounts are copied to clipboard
33
Paste Accounts at Cell A1
34
Highlight entire spread sheet auto adjust
column width
35
Freeze Panes under Windows
36
The left top stay static make it easy for you
to navigate
37
Apply GL Function to Chart

• Taking the next step and apply GL function to
  each cell.
• Use Copy and Paste
• Youll see the result is the same
• Whats the benefit of applying GL function?
• Next Month, the figure will update automatically.
• If it is a static value (as the previous one),
  you need to use the same procedure next month.
• Do not Interrupt F9 Calculation
• Spreadsheet may freeze
• Turn off Always Calculation in F9 Option

38
Apply GL function, Copy Paste
39
Choose Options under F9
40
Turn off Always Calculate

• Dont Interrupt F9 Calculation.
• No Keyboard Entry
• No Mouse Click
• If you do, F9 may freeze.
• Always Calculate make it difficult to predict
  when the F9 calculation will take place. Its
  easy to freeze F9 accidentally.
• Turn off Always Calculate Need to press F9
  key to re-calc.
• Since you know when the calculation will take
  place, you can wait for the calculation to finish
  without interrupting.

41
Access Last Year Data

• Use more parameters in GL function
• GL( Account, Period, CompNo, Year )
• Make CompNo Year stored in one of your
  spreadsheet cell.

42
GL(A4,C2,A1,B1)
43
Copy Paste No Change
44
Change Year in Cell B1 to 1998
45
Use NGL (Negative GL) function

• In Elliott General Ledger
• Debit Store as a positive number
• Credit Store as a negative number
• If you do not want to see negative number with
  normally credit account.
• Apply NGL( Account, Period.) function
• Change Cell data type to Number
• Apply number with , like 1,000.00
• Display Negative as Red

46
Change the 1st Cell of Liability Account Range to
NGL(.)
47
Copy Paste NGL formula to all Liability Sales
Account
48
Change Cell to Number Format
49
The cells with red color require attention
50
Using F9 Wizard

• You need the new ELIOTT.DLL (v4.0.0.4) to make F9
  Wizard to work with Elliott.
• You can download it by visiting
  http//www.elliott.com support area and choose
  Utilities under the download area.

51
www.elliott.com support download
52
Choose Utilities in Download
53
Download Elliott.dll for F9
54
Need to unzip the download file
55
Extract to C\F9 directory
56
Rename existing Elliott.dll first
57
New DLL dated 08/22/02 and is larger is size
58
Think Vertical Horizontal

• Determine the vertical
• Typically is the main account
• Add Subtotal Grand Total at Proper Place
• Add Blank Line for Formatting
• Determine the Horizontal
• Add Description Column
• Add Period (Jan Dec) Columns
• Add Year-To-Date Column

59
Plan your Financial Statement
60
Start Wizard Choose Company
61
Vertical axis choose Main
62
Highlight all Sales Acct click gtgt
63
Highlight Sub Total click gtgt
64
Highlight Blank Line click gtgt
65
Highlight all Sales Acct click gtgt
66
Highlight Sub Total Click gtgt
67
Highlight Blank Line Click gtgt
68
Highlight Exp Acct click gtgt
69
Highlight Sub Total Click gtgt
70
Highlight Blank Line click gtgt
71
Highlight Total click gtgt
72
Click Next Define Horizontal
73
Choose Description click gtgt
74
Highlight Periods
75
Highlight Jan-Dec click gtgt
76
Highlight Total click gtgt
77
Click Next and Confirm the Rest
78
Check Create NGL Functions

• Sales Acct will show as positive number.
• COGS Exp Acct will show as Negative number.

79
PL is created on spread sheet
80
Dress up Your Spreadsheet

• Adjust Column Size
• Use Hide Function
• Highlight the Columns Rows and Right Click to
  Hide
• Special Font Size for Title, Subtotal Total.
• Apply NGL or GL depend on whether this is a debit
  or credit account.
• Change the Cell data type to Number
• Apply number with , like 1,000.00
• Display Negative as Red

81
Hide Columns
82
Hide col rows until it looks like
83
Change font so it looks like
84
Make 1st COGS Cell to GL
85
Copy Paste Then Press F9 Key
86
Repeat for all exp accounts
87
Freeze Panes at 1st Sales Acct
88
Highlight all number area change cell data type
to number
89
Choose 1000 separtor(,) red color for negative
90
The red color cell require attention
91
Why Learn Formula?

• Theres a limit for what Wizard can do for you.
• When you need to customize your spreadsheet, you
  need to use the formula.

92
Drill Down

• At Segments Choose Transaction
• Only work once per session.
• Dont save your spreadsheet once you Drill Down
• You can corrupt your spreadsheet

93
Highlight the cell to investigate
94
Click Drill to bring up the Dialog
95
Choose Transactions Drill
96
How Much Does F9 V4 Cost?

• Single User
• 595.00
• Multi-User (Site License)
• 1,250.00
• F9 V4 (32-bit) will work for All Elliott
  Versions. (V6.6, V6.7, V7.0 V7.1)
• F9 for Elliott is available from your Elliott
  Reseller.

97
Known Issues

• Do not use the Btrieve Client come with F9
• It is Btrieve V6.15 Client Engine
• May cause File Locking in GL
• Remove WBTRV32.DLL from the C\F9 directory
• Need update ELLIOTT.DLL to make Wizard work
• Download from http//www.elliott.com support area
• Do not Interrupt F9 calculation
• Spreadsheet may freeze
• Turn off Always Calculation in F9 Option
• Drill down only work once per session.

98
Conclusion

• F9 is a powerful tool that can greatly help CFO,
  Controllers or Accounting Managers to
• Present Financial Data
• Analyze Financial Data
• Play with What If Scenario
• Improve Productivity
• Preparing Budget and Write Back to Elliott

99
Questions Answers