F9 Quick Tour

iii

Table of Contents

F9 Quick Tour ................................................................................................................. 1

Getting Started ............................................................................................................ 2

Attach F9 ............................................................................................................... 2

Data Connection ................................................................................................... 3

F9 Default Settings ................................................................................................ 3

Excel Settings ....................................................................................................... 6

Reporting ................................................................................................................... 11 Table Report .......................................................................................................... 11

Static Trial Balance ............................................................................................. 12 Dynamic Trial Balance ........................................................................................ 17

Report Wizard ........................................................................................................ 21

Period Activity ..................................................................................................... 22 Revenues by Department ................................................................................... 30

Inquire and Paste ................................................................................................... 35

Actual vs. Budget ................................................................................................ 36 Pivot Table Report ................................................................................................. 46

Report Creation – Start to Finish ............................................................................... 51 Report Wizard – Balance Sheet ............................................................................. 51

Scheduling Reports ................................................................................................... 65

Login ...................................................................................................................... 66

Tasks ..................................................................................................................... 67

Reports .................................................................................................................. 68

Email ...................................................................................................................... 69

Schedule ................................................................................................................ 71

F9 Formulas .............................................................................................................. 73 =GL( ) and =NGL( ) ................................................................................................ 73

=BSPEC( ) ............................................................................................................. 74

=DESC( ) and =SDESC( ) ...................................................................................... 74

=CODATA( ) ........................................................................................................... 75

=ACCTDATA( ) ...................................................................................................... 75

=GLTRAN( ) ........................................................................................................... 76

Additional Resources ................................................................................................. 79 Tips and Tricks ....................................................................................................... 79

Period Specifiers .................................................................................................... 80

Other Account Ranges ........................................................................................... 81

Printed Documentation

iv

Copyright ................................................................................................................ 82

1

F9 Quick Tour

This guide is designed to provide you with a general overview of F9’s reporting functionality, tools to build reports, best practices, and additional references for related information. You will also find tip and tricks and recommendations for best practices throughout the document. To work through any of the reports, this guide is based on the AccountMate SQL sample database: 999 and if possible, we recommend having this database available as the installed F9 Sample Reports can then be recalculated.

2

Getting Started

Attach F9

The first step is to configure the F9 Add-In to attach each time Excel loads.

1) Open Excel.

If the F9 tab displays within the Excel Ribbon, the program is attached. For hosted customers this should have been setup for you during the installation.

Excel 2013:

Excel 2010:

Excel 2007:

NEXT STEP:

If the F9 tab displays, continue with: Data Connection

In some cases the F9 Ribbon will not be setup to auto-attach without first loading Excel with administrative rights. Please refer to the instructions within the installed file: F9 Readme.PDF.

Additional Resources:

F9 Help: VAMSQL.CHM

See also: System requirements

F9 Quick Tour

3

Data Connection

With F9 attached and running within Excel, the next step is to become familiar with how F9 is setup to access your AccountMate company.

1) From the F9 Ribbon and Preferences, select F9 Setup and then F9 Company Setup

2) If you see the processing set in the dropdown, F9 has successfully connected.

3) If you do not have access to 999, please select your company’s database.

For more information on the data connection to your accounting system, refer to the F9 Ribbon:

and select F9 Windows/Preferences/F9 Setup.

NEXT STEP:

Report Filters

F9 Default Settings

Report filters within F9 Setup act as defaults in the event any F9 formulas contain missing parameters. For example, if a fiscal year is not included in the F9 formula =GL( ), the F9 Setup Year will be used.

4

We recommend always including the required filters as cell references as it may not be obvious how results are returned otherwise. How F9 formulas are built is described later in this guide. F9 formula referencing all cell parameters (recommended):

Versus F9 formula using defaults from F9 Setup (not recommended):

Using Excel’s Insert Function, view how parameters are referenced:

F9 Quick Tour

5

The F9 Setup includes a setting which affects how F9 calculates account balances.

1) Select or de-select access of Include Inactive Accounts to control how balances and transactions are totaled. This acts as a global setting during calculation.

2) For details on all other settings, please refer to F9’s Help.

NEXT STEP:

Excel Settings

6

Excel Settings

We recommend Excel be set to Manual calculation prior to designing new reports as well as when saving F9 reports as this allow you to control when balances are re-calculated.

1) Ensure that you have a blank workbook open.

Excel 2013:

To open a blank workbook, choose the File Tab , New, Blank Workbook, and Create.

Excel 2010:

To open a blank workbook, choose the File Tab , New, Blank Workbook, and Create.

F9 Quick Tour

7

Excel 2007:

To open a blank workbook, choose the Office Button , New, Blank and recent, and Create.

2) Ensure Calculation in Excel is set to Manual.

This allows for the most control on when F9 results will be refreshed.

Excel 2010/2013:

Select the File Tab , Options, Formulas and click Manual as shown:

8

Excel 2007:

Select the Office Button , Excel Options, Formulas and click Manual as shown:

F9 Quick Tour

9

TIP #1: Manual Calc Setting

We recommend working within Excel with Calculation set to Manual as you can make any number of changes to the report and control when F9 recalculates the balances.

By default, when Excel is first loaded with a new workbook, the calculation is set to Automatic. The default setting can be changed in multiple ways:

1) Excel Options + Formulas:

2) Excel Formulas Tab + Calculation Options:

3) F9 Reporting Tab – Run any of:

4) File + Open a Report Saved with Manual Calculation:

Excel writes its current calculation setting into every report file saved so that the next time it is opened, it will recalculate in the preferred manner. We recommend all F9 reports are saved with Manual Calculation so that you control when balances are refreshed.

10

TIP #2: Ways to Calculate

Once the default setting for Excel’s calculation is set to Manual, there are several ways to recalculation a given report:

F2 + Enter Calculate active cell

Shift + F9 Calculate active Worksheet (tab)

F9 Calculate open Workbook(s) and all Worksheets

Ctrl + Alt + F9 Calculate open Workbook(s) regardless of if changed since last calculation

Ctrl + Alt + Shift + F9 Rechecks dependencies of formulas and then calculates all open Workbooks

Typically, users select Shift-F9 or F9 to calculate.

F9 Quick Tour

11

Reporting

Table Report

F9’s Table Report will quickly build a variety of results for you in a tabular format which can be static, dynamic, contain dropdown controls, and include optional filtering and KPIs.

TIP #3: Uses for Table Report

Create Excel Tables with your financial information Rows by All Accounts or Ranges Columns by Descriptions and Period Balances Enhance the Table created

o Columns with additional formulas, for example: Variance =ACCTDATA( ) for Account Type

o Use the enhanced Table to populate a custom Pivot Table

12

Static Trial Balance

F9 can quickly output your Chart of Accounts and period balances in a variety of ways and to begin we will use F9’s Table Report.

1) Select from the F9 Ribbon, F9 Table.

2) An F9 task pane will display to the right of the active worksheet. F9 task panes can be resized as well

as have their docking location changed.

TIP #4: How to Resize Task Panes

All F9 task panes will display to the right of the active worksheet but can be resized or moved/docked in a different manner.

To Resize: o Hover the mouse on its left border (recommended)

o Or, select its dropdown control at the top of the task pane

3) Within the tab: F9 Table, set the F9 dropdowns to output the entire Chart of Accounts by typing an * in each account part; select Company 99 , 01, 2011 and January:

F9 Quick Tour

13

4) Next select tab: Options.

o F9 Chart: select Include Account Code, Description and Account Origin o Output: select F9 Classic Chart, Static Values, New Sheet o Formatting: select Add Total Row

14

5) Click on the Find button at the bottom of the F9 Table task pane to see the results output in a new worksheet. The columns will be sized to display your information and should look similar to the following:

F9 Quick Tour

15

5) As a trial balance, we should ensure the total for the period is zero. We set F9 Table to create the subtotal formula within the Options tab and we can scroll to the end of the report.

a. Click on any cell in column E with a value. b. Click the End keyboard key and then the down arrow key. c. Excel should bring you to the end of the report in column E.

d. The last cell should contain an Excel =SUM(…) formula similar to:

TIP #5: Navigation within Large Reports

For cases where there are a large number of adjacent rows or columns, there are keys available within Excel to help with navigation

To navigate to the last row/column: o Click on any cell within a large group o Click the End keyboard key o Click an arrow key to get to the last cell, for example

Down arrow for last row Right arrow for last column

To return to the top of a report o Click Ctrl + Home

To navigate to the end of a report o Click Ctrl + End

6) To improve the format of this cell, select the Excel Home tab and within the group Number the comma option:

16

The value in the trial balance total should now display as: To return to the top of the report, click at the same time: Ctrl + Home from the keyboard.

7) Notice the current report contains account rows with zero amounts. One way to not display these is to use the F9 Zero Suppress command.

8) Select the range of information to suppress, for example: highlight entire column E.

9) Within F9 Tools, select Zero Suppress and then Zero Suppress Rows from the dropdown.

The F9 report should now display just rows with amounts. Notice the rows with zeros have been hidden in Excel. To redisplay the rows at any time, simply highlight the area and select Undo Zero Suppress.

7) Save the workbook as F9 Tour.xlsx by selecting the Excel 2010/2013 File Tab (or

Excel 2007 button ), Save As, Excel Workbook.

F9 Quick Tour

17

Dynamic Trial Balance

With the current settings in the Table Report task pane, we can easily enhance the results by having F9 create a report that calculates with dropdown controls and F9 formulas built in.

1) If the Table task pane is not already displaying, select from the F9 Ribbon, F9 Table.

2) Options tab:

o F9 Chart: Include Account Code, Segment Separate Columns, Description o Output: GL Formulas, New Sheet o Formatting: Add Total Row, Table Style Light 2 (blue)

3) Click on the Find button

A new report will output as an Excel table.

18

4) Select the first balance and notice a =GL( ) formula has been created with absolute and relative cell referencing.

5) Double-click on the formula to see the cell references used for re-calculation.

F9 Quick Tour

19

6) Next, click on the cell for Year 2011 and notice it is an F9 report dropdown control.

7) Select Year 2010 and Shift-F9 to recalculate the report.

You can also easily filter rows within the Table and we will next select the dropdown for January to suppress all zeros.

20

8) Select the dropdown arrow in the Table heading Amount January.

9) Scroll through the numbers to unselect 0.00

10) The table report will now display just rows containing non-zero balances.

F9 Quick Tour

21

Report Wizard

Report Wizard is available to step users through defining report dimensions such as: rows, columns, and a control area, and then creates a completed F9 report having its F9 formulas written for you.

Select or change any of the dropdown controls to calculate new results easily. The initial report can be expanded upon since F9 formulas are written with the correct cell referencing.

TIP #6: Uses for Report Wizard

Rows by any filter such as Accounts, Years, Departments Columns by any filter such as Periods, Years, Divisions, Budgets Create dropdown controls and F9 formulas Predefined formatting or none Build and enhance the report created:

o Add additional Columns, for example Variance o Insert additional subtotals, blank rows, header rows o Customize the formatting

o Add Charts and KPIs that change based on calculation

22

Period Activity

1) Select a blank tab as the active worksheet.

2) From the F9 Ribbon, select Report Wizard.

3) Resize the Report Wizard task pane to increase its display by dragging its border on the left towards the active worksheet.

4) Step 1 - select 999 for the Processing Set and Next.

5) Step 2 - for the type of rows, select Account in the upper window.

6) Next, highlight all accounts from 401100 to 408500 and select the >> button to move to Row Headers.

7) Select Total and >>.

8) Select Next

9) Step 3 - for the type of columns, select Periods in the upper window.

10) Select Description and >> to move to the selected window.

F9 Quick Tour

23

11) For Periods, highlight January to March and >>.

12) Select Next.

13) Step 4 is for the report control area settings which can later be changed. Set to:

o Budgets: 01 o Year: 2010 o Division: * (for all) o Department: * (for all)

24

14) Select Next.

15) Step 5 – report format options, change settings to: o Design Format: F9 Sample Report Format o Report Title: Period Activity Report. o Reverse the sign of account balance (select) o Show Gridlines (deselect)

F9 Quick Tour

25

16) Click Finish.

17) The resulting report will next be created in a new tab.

26

18) Select the Period heading (Row 20, Column K) and notice there are F9 dropdown controls for

the period parameter which the F9 formulas reference.

19) Some parameters including Periods can include multiple values. With period March selected as the active cell, Right-Click and select F9 Control List

20) Select Exclude Descriptions as just the period is in the report and select Period March and April

21) The dropdown will now have the list: March, April.

F9 Quick Tour

27

22) Shift-F9 to recalculate the report.

You can continue to enhance the report output; for example, add a Chart for Freight revenue.

23) Select the values for a new Chart; highlight Freight Revenue and its 3 balances

24) From the Excel Ribbon, select Insert, Column

25) Select the 3-D Column:

26) Position the generated Chart above the report

28

The final step is to change the headings to reflect the Periods being calculated.

27) Click within the chart to select values: 1,2,3

28) From the Ribbon, select Chart Tools and then Select Data

29) Within Horizontal Labels, click Edit

30) Highlight the three Period cells and click OK

F9 Quick Tour

29

30

Assets by Division

The next example will create an F9 report with an outline, dropdown report filters, and conditional formatting KPIs.

1) Select Report Wizard from the F9 Ribbon

2) Step 1 – select 999

3) Step 2 – Rows, select by Account

4) Highlight and copy all accounts from 110100 to 140300

5) Copy Total across to Row Headers

6) Step 3 – Columns, select by Division

7) Select Description and then 100,110,120,130,140

F9 Quick Tour

31

8) Step 4 – Remaining Parameters, set to Year 2011, Period January

32

9) Step 5 – Format Report, set to: o F9 Conditional Format o Reverse Sign o Zero Suppress o Create Details Outline

F9 Quick Tour

33

2) The resulting report will look similar to the following with an Outline to collapse/expand rows as well as the conditional formatting KPI.

3) The KPIs can be changed by simply highlighting the range of cells and selecting Conditional Formatting from the Excel Home tab on the Ribbon.

34

In this report, we did not include Division 000 and the next steps demonstrate using Excel’s drag and drop capabilities with Autofill.

4) Highlight the cells for Division 140 including the heading, balances and total

5) Hover the mouse in the lower right corner till it changes to a dark plus sign.

6) Holding the mouse down, drag to the next column.

7) Shift F9 to calculate.

F9 Quick Tour

35

8) Double-click on a formula to see how the parameter cell references automatically changed to reference Division 000.

TIP #8: Using Excel Autofill

Adding new F9 formulas as well as parameters into adjacent columns or rows can be done with Excel in just a few quick steps.

o Highlight an existing group of cells to copy from

o Hover the mouse over the lower right corner until the pointer turns to: o Drag the cells across to the new column(s) or row(s)

o F9 formulas will change to calculate relative to their new location o If an F9 parameter is moved, Excel will increment its value; example: January February;

2012 2013. In some cases the new parameter may not be correct; for example if they are non-incremental.

Inquire and Paste

F9 can generate a properly formatted GL function easily for you via the Inquire and GL Paste command.

TIP #9: Uses for Inquire and Paste

Quick account balance lookup by different filters Start a new report with dropdown controls and correctly referenced formulas Create and copy report dropdown controls for use in other tabs

36

Actual vs. Budget

With GL Paste, we will build and expand on a report to demonstrate how budgeting works with F9.

1) F9 Ribbon, choose Inquire and GL Paste.

2) Settings:

o Processing Set: 999 o Budget: 01 o Year: 2011 o Period: January o Account: 21* o Division: * o Department: *

3) Select Get Balance to review the total

4) If the balance is negative, unselect checkbox Reverse Sign

F9 Quick Tour

37

5) Next, we will paste the F9 formulas. Click New Sheet and then GL Paste. You will see the beginnings of a new report with F9 report dropdown controls and two properly parameterized F9 formulas.

6) Select the cell with the account description and notice a =DESC( ) formula was written:

7) Notice how the account segments are in separate cells and the F9 formula includes the embedded formula called BSPEC( )… this is explained in more detail next.

38

TIP #10: =BSPEC( ) to Group Accounts

BSPEC is used to build up combinations of segments into multiple accounts easily. o =BSPEC(Segment 1, Segment 2, Segment 3, …)

For the sample company, these are Account, Division, Department Enter ranges, wildcards, lists, and/or single values Use ranges instead of lists where possible as they are a great deal faster! Report Wizard, Inquire and GL Paste, Table create BSPEC for you. F9 builds up all the combinations of accounts to return the net balance Use F9 Drill to view all the accounts built

8) Next, select from the F9 Ribbon F9 Lists. We will use Lists to expand rows for individual accounts. Set to: o Available Lists: Segments o List Segment: Account # o Where: Account # o Is:21*

F9 Quick Tour

39

o Uncheck Generate Descriptions

Here we are generating all Accounts within the wildcard range of 21*

9) Select To Clipboard and OK to close

10) Click on cell A10 which contains ‘21* and then Paste the results

11) With all the accounts highlighted, move to column C: a. Ctrl-X (Cut) b. Select cell C10 c. Ctrl-V (Paste) With accounts adjacent to the F9 formulas =DESC() and =GL(), we can take advantage of Excel’s AutoFill.

12) Highlight cells D10 and E10 and when the mouse changes, you can select AutoFill.

13) Shift-F9 to see the new account rows recalculate

40

TIP #11: Using F9 Lists

The primary role of the Lists feature is to save time from manually entering values for parameters. The most common use is to output a range of segments when building a new report

List can output all the F9 filters supported including:

o Segments (used most often) o Companies o Budgets o Periods o Fiscal Years

14) Add Total Expense formula a. Click on any cell in column E containing balances b. End Key + Down Arrow c. Select the Formulas tab and AutoSum d. Add text for Total Current Liabilities

15) Add Budget Column

a. Ctrl + Home to return to the top of the report b. Copy January dropdown (Ctrl-C) and Paste to cell F8 c. Select the January Budget

F9 Quick Tour

41

d. Copy first January balance from column E to column F

e. Autofill the formula to copy to all rows below f. Copy the Total formula across and Ctrl + Home to return to top of report g. Shift-F9 to recalculate

42

16) Add Variance Column a. Cell G8, enter text: Variance b. Cell G10, enter formula: =E10-F10

c. Autofill the formula to copy for all rows below d. Copy the Total formula across and Ctrl + Home to return to top of report e. Shift-F9 to recalculate

F9 Quick Tour

43

17) Zero Suppress Rows a. Highlight columns E to G b. Select F9 Zero Suppress Rows from the Ribbon

44

TIP #11: Dropdown Controls - Creation

F9 creates dropdown controls via: o Report Wizard o Inquire and Paste o Table Report

1) Create a Division List

a. Insert a new tab

b. Type in cell A1: Division c. Run F9 Lists + Division d. Copy to cell A2 e. Rename tab to: F9Lists

2) Add Division Dropdown Control a. Return to Sheet1 tab b. Select Cell I13 c. Excel Ribbon: Data + Data Validation

F9 Quick Tour

45

d. Data Validation Settings:

e. Cell I13: select 000 from dropdown f. Copy the dropdown to adjacent columns and change selection to be:

o 100 o 110 o 120 o 130 o 140

46

TIP #12: Dropdown Controls - Content

Content of F9 dropdown controls is stored per report file within a hidden tab F9 provides tools to manage the content via F9 Report Controls:

o Select Refresh Selection or All to have F9 update the content o Delete All will permanently remove all dropdown controls from the file

For custom dropdown controls created (Division segment), you will need to run F9 Lists to update the items manually

Pivot Table Report

F9’s Pivot Table enables you to select multiple filters and combinations to easily create an Excel Pivot Table.

TIP #13: Uses for Pivot Table Report

2-step Source/Options to easily create Pivots by F9 Create multi-dimension criteria to aggregate, view, and drill into Group rows and columns by multiple filters Analyze and identify trends across larger sets of information Output balances as static (fast) or formulas (to recalculate) If you need additional dimensions that F9 Pivot does not provide:

o Use F9’s Table Report o Add custom formulas and columns o Select anywhere within the F9 Table o Excel + Table Tools + Summarize with PivotTable

1) From the F9 Ribbon, select Pivot Table Report:

2) Source – describes what data to populate the Pivot table with: o Years: 2009, 2010 and 2011. o Budget: 01 o Account: 1* o Period: Year to date Month 12 o All other filters: All

F9 Quick Tour

47

3) Options tab –how content is created: o Segments in Separate Columns o Create supporting amounts as: NGL Formulas o AutoFit Pivot Table Columns

48

4) Click on the Create Pivot Table button.

5) To calculate values, click anywhere inside the Pivot, right-mouse click and select Refresh.

The results should look similar to:

F9 Quick Tour

49

6) Note amounts are shown broken down by Year. To showcase some of the capabilities of the pivot

table, move filters to different areas using the provided PivotTable Field List task pane. For example, move Department over to Row Labels:

50

7) Note that you also have filters available to you on both the rows and columns to display totals, for example, for certain departments or divisions.

8) Another feature with a Pivot table is drill. Select a cell with an amount and either double-click or right-mouse and select Show Details. The details that total this amount are shown in a newly created tab.

F9 Quick Tour

51

Report Creation – Start to Finish

Report Wizard – Balance Sheet

1) Select from the F9 Ribbon, F9 Report Wizard.

2) STEP 1 – select 999 for Company

3) STEP 2 – select Account # to build rows

52

TIP #14: Selecting Accounts in Report Wizard

Locate the first account in the group by setting rows to be by Accounts If you do not see the Account, type its first digit for F9 to auto-scroll to the first row. For

example to find accounts that start with ‘4’, type the number 4. Select the first account for the group so that it is highlighted Hold the Shift-Key down and page down or scroll to highlight the group of accounts Another option is to drag the mouse for the group selection Once all are highlighted, click the >> button to copy the selection to Row Headers

4) Current Assets: Click on Account 110100 and highlight up to 110500

F9 Quick Tour

53

5) Click >> to copy the selected accounts to Row Headers

6) Click Sub Total and >> for =SUM( ) formulas

7) Within Axis Values, select Blank Line and >>

54

8) Repeat the above steps for all groups adding a Sub Total and Blank Line after each: o Account Receivable: 120100 to 120910 o Inventory: 130100 to 130400 o Prepaid Expenses: 140150 to 140300 o Fixed Assets: 152000 to 158000 o Other Assets: 160510 to 170110 o Current Liabilities: 210200 to 230600 o Other Liabilities: 270400 to 270700

9) For Shareholders’ Equity, we are going to create a single range in one row. a. Shareholders’ Equity (Liabilities + Net Assets): 301000 to 340200 b. Click >> to copy c. Click Sub Total d. Highlight accounts 301000 to 340200 in the Row Headers window e. Click Edit f. Click Create Range

F9 Quick Tour

55

The last row will now display as: 301000.. 340200

56

.

10) STEP 3 – select items for columns which for the Balance Sheet report will be Years.

a. If in Step 2, Click Next b. Click Description and >> to copy to Column Headers c. Highlight Years 2010 and 2011 d. Click >>

Columns should now be setup as:

F9 Quick Tour

57

11) STEP 4 – defaults for Control Area: o Set defaults as shown, if required

12) STEP 5 – output options:

o Report Design Format: F9 Sample Report Format o Title: Balance Sheet o New Spreadsheet o Show Gridlines

Your screen should display as follows:

58

TIP #15: Report Wizard – Optional Output

A number of options are described as a quick reference on how reporting results change if selected within Report Wizard.

Report Design Format o Select None or a predefined format for F9 to add color and highlights to the

report created o Once created, you can change the format via Excel at anytime

Reverse sign of account balances

o Select for F9 to create =NGL( ) formulas o Unselect for F9 to create =GL( ) formulas

Zero Suppress the report

o All account rows will have =GL( ) or =NGL( ) but for rows where all balances return as zero, its row will be hidden

Include optional parameters in GL/NGL Functions o Unselect to have GL/NGL formulas with less parameters and dropdowns

F9 Quick Tour

59

13) Click Finish

a. Select cells containing balances as well as account descriptions to see how F9 has built its =GL( ) and =SDESC( ) formulas for calculation

b. All the cell referencing is setup such that it is possible to work within Excel to customize the

report. For example, you can insert rows and/or columns and copy formulas properly.

c. In addition, report dropdown controls have been created in the top left corner for selecting different filters for recalculation.

14) Excel Content – change headings

a. Add Assets Heading

o Delete the text ‘Description from cell H20

60

o Select entire row 21 (which is the first account listed) o Right-click to insert a new blank row

o Change the blue color to No Fill o In cell H21, enter Assets

b. Similarly, insert a new row for a Liabilities Heading

c. Clear all Sub Total text in column E (below the account number lists). For example:

d. Change Sub Total text in column H to be:

o Total Cash o Net Receivables o Inventory o Total Prepaid Expenses o Net Land and Equipment o Other Assets o Current Liabilities o Long Term Liabilities o Total Shareholders’ Equity

F9 Quick Tour

61

15) Excel content – insert subtotals

a. Add new subtotal for Total Current Assets I. Insert 2 blank rows below Cell H49: Total Prepaid Expenses II. Set the grey background to yellow by copying from an existing blank row

III. Cell H51: Total Current Assets IV. Cell I51: =SUM(I26,I34,I44,I49) V. Copy formula from I51 to cell J51

62

b. Continue adding new subtotal rows for: o Total Assets (Current Assets + + Net Land and Equipment + Other Assets) o Total Liabilities (Current Liabilities + Long-Term Liabilities) o Total Equity and Liabilities (Liabilities + Net Assets )

16) Change =GL( ) to =NGL( ) formulas for all Liability rows (reverse the sign)

a. Locate the first cell containing the balance for Accounts Payable b. Select the F2 function key to Edit c. Add the letter N to change the formula to NGL( ) and press Enter

d. Copy the NGL( ) formula to all Liability rows/columns as well as Net Assets

17) Calculate to check the report is in balance a. Shift-F9 to calculate the tab b. Check that Total Assets and Total Equities and Liabilities are equivalent.

18) (Optional) Add company name above the title a. Select the empty cell above the report title (cell H27) b. Enter the formula: =codata(“name”,$C$3) c. National Office Supply, Inc. should now display

F9 Quick Tour

63

19) (Optional) Add Excel outline

a. Highlight the entire tab by selecting the top left corner

b. Select the Data Tab from Excel’s Ribbon c. Select Group and Auto Outline d. Excel will create the outline based on the sub totals within the report e. Collapse groups to see sections display as summarized

64

20) (Optional ) Remove display of gridlines a. Select View from Excel’s Ribbon b. Unselect Gridlines

c. Change any remaining formats such as unneeded shading

F9 Quick Tour

65

Scheduling Reports

New to F9 Version 5 is the ability to easily schedule F9 reporting direct from within Excel and some of the benefits include:

o Calculate

o Print

o Publish to PDF

o Save to different files and folders

o Email distribution

Scheduling consists of up to 5 steps:

1. Login to set credentials

2. Create Tasks

3. Select Reports and output

4. Optionally Email recipients

5. Schedule Tasks to run

Additional tasks also enhance working with Email and include: an F9 address book; exporting contacts from a Provider such as Outlook; importing emails; working with distribution groups.

66

Login

Each task setup by F9 scheduling is run with an associated login.

1. Select Tasks from the F9 Ribbon.

2. Type in your Windows login and click OK.

Note: User Name will differ depending on the operating system; for example: Windows XP: Domain\UserName Windows 7: UserName Windows Server 2003: UserName Windows Server 2008: UserName

Once your login has been entered once, F9 will not prompt again for the current Excel session and you will be placed within the first tab of Scheduling for Tasks. To create tasks as a different user, please close the current Excel session; open Excel; and finally select the Tasks button once F9 is re-attached.

F9 Quick Tour

67

Tasks

An F9 task represents work scheduled for one or more reports. Once logged in, the first tab displays a window for Tasks to create and manage for all scheduling scenarios. We will create a new task to calculate the F9 Sample Reports.

1. Check Create New Task

2. For Task Name, type Calc Reports

3. Click Create Log File

4. Click Next >>

68

Reports

One or more F9 reports is associated with a given task and can have additional options for its output and distribution. We will setup the task to recalculate the F9 Sample Reports and save to a new output folder.

1. Click Add to browse to the F9 Sample Reports.xlsx

2. Select Calculate

3. For Save Report To, select Browse

4. Navigate to folder C:\F9V5 and select Make New Folder for Reports

F9 Quick Tour

69

5. Click OK and Next >>

Email

The ability to email is optional and can be used as a notification as well as means to distribute newly calculated and published reports. The following steps will setup and test email.

1. Check the Send Email option. 2. Enter your company SMTP.

If you are unsure, please contact your IT resources or alternatively skip the Email steps by selecting Next >>.

3. Within Sender Email, enter an email address to represent who will send emails for tasks. 4. Select Test Email.

If the settings are working, an email message will be sent in a short period of time and you can check Sent Emails.

70

F9 Scheduling provides a number of features to support email including: Address Book to store emails Distribution Groups to optionally group emails within the Address Book Import Contacts to load emails from provider Exports

This is documented in detail within F9 Help topics under Scheduling and will not be demonstrated in this short Quick Tour.

5. Select Next >> to proceed to the final Schedule tab.

F9 Quick Tour

71

Schedule

The final step is to define when and how often the task will run. For first time creation, we recommend running the task once to ensure all options are working as expected as the task can be modified at any time once saved.

1. Select One time. 2. Start: ensure is today and time is in a few minutes. 3. Click Save to have all the settings for the task stored. 4. After the task runtime, check the c:\f9v5\Reports folder for the saved F9 Sample Reports.xlsx

file.

72

5. Click Close to exit Scheduler.

Note: Run Task allows you to run the current task now.

F9 Quick Tour

73

F9 Formulas

This section is designed as a quick reference to the F9 formulas available for The Financial Edge. Examples can also be found within F9’s on-line help and more comprehensive details are available with F9 Courseware.

=GL( ) and =NGL( )

o Core formula within F9 that returns account balances o GL and NGL are identical apart from NGL reverses the sign of the balance o 2 options for parameters: basic and extended o Use the =BSPEC( ) formula for the Account parameter to group segments o F9 Tools that create the formula: Report Wizard, Table Report, Inquire and Paste

=GL(Account, Period, Company, Year, Type) =NGL(Account, Period, Company, Year, Type) Where:

Account is the GL account(s) specifier Period is the fiscal period(s) Company is one or more AccountMate companies Year is the specific fiscal year Type represents 01

74

=BSPEC( )

o Builds lists and combinations of segments to group accounts easily o Stands for Build Specifier o Tools that create the BSPEC formula: Report Wizard, Inquire and Paste, Table Report o Use Ranges and Wildcards over Lists – faster calculation!

1..3 is faster than 1,2,3

10* is faster than 101,102,103,104,105

=BSPEC(Segment 1, Segment 2, Segment 3, …) Where:

Segment 1 is the main Account segment Segment 2 is the Division segment Segment 3 is the Department segment

=DESC( ) and =SDESC( )

Two formulas are available for returning account descriptions. We recommend using these when the account or segment is single in value (not a group).

o DESC( ) Returns the description for the account code

If the account is a range or in a group, F9 returns the first description found.

Tools that create the DESC formula: Inquire and Paste, Table Report

o SDESC( ) Returns the description for the segment

If the segment is a range or in a group, F9 returns the first description found

Tools that create the SDESC formula: Report Wizard

=DESC(Account, Company, Year) Where:

Account is the GL account(s) specifier Company is the specific database

=SDESC(Segment, Segment Number, Company) Where:

Segment is the GL segment specifier Segment No is the segment position Company is the specific database

F9 Quick Tour

75

=CODATA( )

o Returns information for the Company o Typically used to create dynamic reports based on the current fiscal period or year

=CODATA(Data, Company) Where:

Data is the type of information returned (refer to table) Company is the specific database

Data Result

Name Company name

Year Current fiscal year

Period Current period

Segments Total account segments

Main Segment number of the natural account code

StartN Start date of period N

EndN End date of period N

StartN_YYYY Start date of period N for the year YYYY

EndN_YYYY End date of period N for the year YYYY

=ACCTDATA( )

o Returns account code related information

=ACCTDATA(Data, Account, Company) Where:

Data is the type of information returned (refer to table) Account is the full account specifier Company is the specific database

Data Result

Description Full account description

Account Type Account type (Asset, Liability, Expense, …)

Origin Balance Sheet or P/L

76

Typical Balance Returns "DR" or "CR"

Class Class for the account.

=GLTRAN( )

o Returns the sum of account transactions o Similar to the =GL( ) and =NGL( ) formulas where Start and End Dates replace the Period o Use the =BSPEC( ) formula for the Account parameter to group segments o Drill by Transactions works well with GLTRAN( ) formulas to view details

=GLTRAN(Account, Start Date, End Date, Company, Type, Currency) Where:

Account is the GL account(s) specifier Start Date is an Excel formatted date for start End Date is an Excel formatted date for end Company is one or more AccountMate companies Type is the specific type Currency is the specific currency

TIP #16: Excel Insert Function

Excel include a very useful utility to write formulas as well as troubleshoot complex formulas for proper cell referencing and syntax

Function Insert displays the formula’s definition and lists its parameters and current values Useful for formulas not created by F9’s reporting tools such as =GLTRAN( )

F9 Quick Tour

77

78

TIP #17: Absolute and Relative Cell Referencing

By default a formula you create in Excel uses Relative cell references; if copied, Excel will automatically adjust its row and column references.

F9 creates formulas for you with both absolute and relative referencing F9 parameters that typically change relate to rows and columns F9 parameters that stay fixed are often from the control area.

To change cell referencing:

o Select a cell with an F9 formula o F2 for edit mode o Point within the formula to the cell address o Select the F4 function key to toggle between all possible referencing

Examples: A2, $A$2, $A2, A$2 To move any F9 parameter: (Excel will correctly change all formulas that reference it)

o Select the cell with the F9 parameter o Ctrl + X to cut o Select the new cell for the parameter o Ctrl + Y to paste

To move F9 parameters into a new tab: (Excel will change all formulas to reference the other tab)

o Select the range of cells with F9 parameters (example: F9 control area) o Ctrl + X to cut o Select a different tab

o Ctrl + Y to paste all the parameters

To copy any F9 parameters: (No change to existing formulas, used if writing F9 new formulas) o Select the cell(s) with the F9 parameter(s) o Ctrl + C to copy o Select a new cell and/or tab o Ctrl + Y to paste

TIP #18: Dropdown Controls - Using

How to Copy: o Copy controls to any other cells and tabs within the same report file o Ctrl + C (Copy) to copy for new formulas to reference o Ctrl + X (Cut) to move and have existing formulas change their address o If deleted by accident, F9 formulas may display with #REF errors in edit mode o Do not copy to new files as their values are stored in a hidden tab and would result in

external links across files

Changing Values:

o Enter different values in the cell where a dropdown control resides o Many F9 parameters allow lists of values

Right-click on the control If you see F9 Control List, then lists are supported Example: Division, Department, Budgets, Years

F9 Quick Tour

79

Additional Resources

Tips and Tricks

TIP #1: Manual Calc Setting TIP #2: Ways to Calculate TIP #3: Uses for Table Report TIP #4: How to Resize Task Panes

TIP #5: Navigation within Large Reports TIP #6: Uses for Report Wizard Error! Reference source not found. TIP #8: Using Excel Autofill TIP #9: Uses for Inquire and Paste TIP #10: =BSPEC( ) to Group Accounts TIP #11: Using F9 Lists TIP #11: Dropdown Controls - Creation TIP #12: Dropdown Controls - Content TIP #13: Uses for Pivot Table Report TIP #14: Selecting Accounts in Report Wizard TIP #15: Report Wizard – Optional Output TIP #16: Excel Insert Function TIP #17: Absolute and Relative Cell Referencing TIP #18: Dropdown Controls - Using

80

Period Specifiers

In F9, the Period specifier is used to identify the period or periods for which you are requesting account balances. To view different combinations of F9 Periods:

o Click F9 Drill from the F9 Ribbon o Click the Period button to display Period Wizard o Select different options to see different F9 Period specifiers o Optionally copy the result to a cell

Period Specifier Examples

Description

Month 1 The first period of the fiscal year

This Month The current fiscal period

Budget March Return the budget balance for March

This Month Last Year

The current period in the previous year

Last Month The period before the current period

Year to Date, YTD Total for the year including the current period (applies only to P/L accounts as BS accounts are returned as a cumulative balance by default)

Change Month 2 Period 2 activity (applies only to BS as P/L accounts are returned as transactional values by default)

September The balance for September

Quarter 1, QTR 1 Total for the first quarter

F9 Quick Tour

81

Other Account Ranges

In most financial reports you will want to return values for a range or series of accounts in one cell. Another option is to provide the full account code as the parameter within F9 formulas. Examples are shown in the following table and explain how wild cards are generally used:

Character Description Example Result

* Matches any character, or set of characters regardless of length.

*-*-* *-4000-100 1*-1005-100

Returns all funds, accounts, and departments. Returns all funds for 4000 account and department 100. Returns all funds that start with a 1, account 1005 and department 100.

? Matches a single character in the exact position.

10-4?00-100 Returns all accounts that have a first character of 4 and third and fourth characters as 00, fund 10, department 100.

.. Indicates a range of account segment values

*-4000..5000-* Returns all accounts from 4000 up to and including 5000 for all funds and departments.

, Creates a list of account specifiers or account segments.

*-4000-100,*-5000-*

Returns a balance for the sum of accounts: *-4000-100 and *-5000-*

Note:

o Most of the examples in this guide separate the accounts segments into separate cells o Use the =BSPEC( ) formula to build combinations of segments, for example certain departments

for Expenses o All of the wild card characters listed above are supported when segments are in separate controls o In all cases, a range or wildcard is faster than a list

82

Copyright

Copyright © 2013 Infor. All rights reserved. Important Notices

The material contained in this publication (including any supplementary information) constitutes and

contains confidential and proprietary information of Infor. By gaining access to the attached, you acknowledge and agree that the material (including any modification, translation or adaptation of the material) and all copyright, trade secrets and all other right, title and interest therein, are the sole property of Infor and that you shall not gain right, title or interest in the material (including any modification, translation or adaptation of the material) by virtue of your review thereof other than the non-exclusive right to use the material solely in connection with

and the furtherance of your license and use of software made available to your company from Infor

pursuant to a separate agreement, the terms of which separate agreement shall govern your use of this material and all supplemental related materials ("Purpose"). In addition, by accessing the enclosed material, you acknowledge and agree that you are required to maintain such material in strict confidence and that your use of such material is limited to the Purpose described above. Although Infor has taken due care to ensure that the material included in this

publication is accurate and complete, Infor cannot warrant that the information contained in this publication is complete, does not contain typographical or other errors, or will meet your specific requirements. As such, Infor does not assume and hereby disclaims all liability, consequential or otherwise, for any loss or damage to any person or entity which is caused by or relates to errors or omissions in this publication (including any supplementary information), whether such errors or omissions result from negligence, accident or any other cause.

Without limitation, U.S. export control laws and other applicable export and import laws govern your use of this material and you will neither export or re-export, directly or indirectly, this material nor

any related materials or supplemental information in violation of such laws, or use such materials for any purpose prohibited by such laws.

Trademark Acknowledgements

The word and design marks set forth herein are trademarks and/or registered trademarks of Infor and/or related affiliates and subsidiaries. All rights reserved. All other company, product, trade or service names referenced may be registered trademarks or trademarks of their respective owners.