A Quick Tour of F9 for BusinessVision
Contents
1. A Location Department Company C f932 Demo btr Year 2000 Type ACTUAL Currency Home Month 40000 ol I4 4 P pI Gl Formula BSPEC Sheet3 ja m Ready Calculate OE NM f T In the above sample cell E10 contains the value for the account s that was specified in Account Enquire If you click on the appropriate cell in your sheet you will see that there is a NGL formula with the proper BSPEC function and absolute and relative cell referencing In the sample sheet above the BSPEC references cell B1 Location A10 Nat ural Account and B3 Department Currently Location and Department segments are referencing an asterix meaning ALL To reference a specific department location division region etc change the appropriate reference to the department or location that you will want the sheet for and recalculate cell E10 Now the sheet has only one account referenced You can begin entering Natural Account codes and ranges in cell A11 A12 etc If you do not want to consolidate any accounts the fastest way to output the entities that exist in any segment of your account code is Lists Lists The primary role of the Lists feature is to save time manually entering data that already exists in your GL The List feature allows you to enumerate e Segments Companies Budgets e Lists e Periods e Years 1 Click the List Toolbar Button The Available Lists section allows you2. cell reference to the third segment of your account There can be as many Segments as your account contains The GL formula with the BSPEC function GL BSPEC Segment 1 Segment 2 Segment 3 Period Company Year Type In any F9 formula that references an account number the BSPEC can also be a cell reference The easiest way to learn the BSPEC function is to see it in use The F9 report that we have created is not really suited to using the BSPEC function We will now go to Sheet 2 and create a departmentalized Profit and Loss statement Unfortunately the Businiss Vision sample company ABC has only a one segment account code so we will use the F9 Demo data Demo btr for the remainder of this tutorial 1 Open the F9 Setup window F9 Setup 2 Click the Browse button and browse to your F9 directory 3 Select Demo btr and click Open Page 8 A Quick Tour of F9 The GL Wizard Account Enquire and GL Paste The GL Wizard creates the first GL formula in a new report using the BSPEC function which reduces the time to cre ate reports because it enters the correct absolute and relative cell referencing 1 Click on the Sheet 2 Page Tab at the bottom of the spreadsheet to make Sheet 2 active Double click the Tab and rename this sheet BSPEC 2 Choose F9 Enquire This will open the F9 Account Enquire window F9 Account Enquiry Professional X Company C 59324D emo btr 7 Periods Month Year fi 999 x Type fact UAL
3. description for The first seg ment is 1 Make the descriptions in this report formula based 1 Make cell B4 active and enter the DESC formula referencing the appropriate cells for each parameter DESC Account Company Year Type Press Enter and recalculate this cell F2 Enter If the description is not returned ensure that your formula is correct i e DESC A4 A1 B1 C1 2 Give the parameters in this formula the correct absolute and relative cell referencing The DESC function should now be defined as DESC A4 A 1 B 1 C 1 3 Copy the formula down so that it covers all accounts Ensure that you entered the correct absolute and relative cell referencing and recalculate the current sheet Shift F9 Page 6 A Quick Tour of F9 F9 can also return sub account descriptions if they are supported by your accounting system with the SDESC func tion 1 Select any cell in Column C Choose Insert Column 2 Copy the DESC function from Cell B4 to Cell C4 3 Edit the DESC formula in Cell C3 so that it is a SDESC formula pulling a description for the first segment of your account code SDESC A4 1 A 1 B 1 C 1 If the first segment of your account code is your nat ural account you will want to return a description for a segment other than 1 4 Ensure the parameters in this formula have the correct absolute and relative cell referencing Because this for mula was copied from the DESC formula these should be co
4. F9 FINANCIAL INTELLIGENCE A Quick Tour of F9 for BusinessVision A Quick Tour of F9 This is a short tutorial designed to familiarize you with the basic concepts of creating a financial report with F9 Every F9 financial report starts as a spreadsheet and uses the features of Microsoft Excel or Lotus 1 2 3 For this rea son you will also find Excel s or Lotus s online Help to be an invaluable aid when you create your financial reports To find complete information about any F9 feature see the index in the F9 Manual Let s begin the tour 1 Open Excel and ensure F9 is attached there should be a F9 pull down menu between Window and Help in the Excel menu list like this Tools Data Window F9 Help If the F9 pull down menu is not listed please refer to Installation and Setup Chapter of the F9 Manual 2 Ensure that you have a blank workbook open To open a blank workbook choose File New select Workbook and click OK 3 Choose Tools Options Select the Calculation tab and click on the Manual button The workbook is now set to Manual Calculation mode This means that you must instruct Excel to calculate your for mulas Press the F9 key to calculate your entire workbook Shift F9 to calculate the active spreadsheet only or F2 Enter to calculate the active cell To save time all F9 reports should be created with Excel set to Manual Calcula tion so that the entire workbook does not recalculate every time you edit a cell Fo
5. I Origin 11 11120 First National Bank Balance Sheet 1 934 949 60 11130 Cash and Check Receipts Balance Sheet 13 000 00 11140 American Express Balance Sheet 112 608 09 11160 MasterCard Balance Sheet 49 854 95 11160 Visa Balance Sheet 46 053 86 11180 Payroll Clearing Account Balance Sheet 12 674 79 11210 Accounts Receivable Trade Balance Sheet 397 624 31 11220 Allowance Doubtful Accts Balance Sheet 2 500 00 11280 Employee Advances Receivable Balance Sheet 44 gt DI GL Formula Sheet2 y Sheets 7 Jal 3 ENSURE THAT YOUR CELL REFERENCES ARE CORRECT and press F2 Enter to calculate the cell If the formula is correct the cell will return the same value but it will now be based on an F9 formula As you will notice none of the other values below cell D4 have changed This is because they are not formula based Don t worry though you do not have to type the GL formula again if you use the correct Absolute and Relative cell references and Auto Fill Absolute and Relative Cell Referencing By default a formula you create in Excel uses Relative cell references This means that if you copy a formula Excel will automatically adjust the cell references in the copied formula to refer to different cells relative to the position of the original formula So if we copy this formula from cell D4 to cell D5 all row references will change by one row This formula would then be GL A4 D3 A2 B2 C2 D2 In the new form
6. and for detailed instructions on the advanced features of F9 please see your F9 Users Manual Page 12 A Quick Tour of F9
7. e a crosshair Press and hold the left mouse button and drag the selected cell across columns until it covers all the periods between January and December stop at Column O Select cell D4 and copy the formula across all of the periods To copy the formula down select only cells E4 through O4 and double click the fill handle If the D column is selected this will not work because D5 already has a formula in it Press F9 to calculate the report This may take a few minutes if you have a large Chart of Accounts Select the entire Sheet and AutoFit the selection Save the workbook File Save You may notice that your Balance Sheet accounts have the same value for a consecutive number of periods current period and all future periods F9 following the principles of accounting returns Balance Sheet accounts as a cumu lative balance P L accounts are returned as a monthly change value A Quick Tour of F9 Page 5 Returning Budget Values To return budget values in F9 use the same GL Formula as for Actual values but add the word Budget to your Period specifier All valid Period specifiers can be used to return budget values So a period parameter of January Budget will return the budgeted amount for that account If you have multiple budgets in your accounting system simply change the Type specifier to the correct budget name Select any cell in Column E Choose Insert Column Select cell E2 and enter January Budget Copy the f
8. ignify the fact that this is a range of accounts and will return the descrip tion for the first account listed If you have a number of ranges of accounts that you will be using frequently the best thing to do is create Financial Entities in F9 for these ranges For information on Financial Entities and other advanced F9 features see the F9 User Manual or consider registering for F9 Training Although these Account specifiers may seem to be able to accommodate every aspect of your reporting needs imag ine if you wanted to create a departmentalized report for each department Using the above Account ranges will accomplish this but you will have to recreate the report for each department reentering the appropriate account ranges each time specifying a different department A very important feature of F9 is allowing you to cell reference each segment of your account code individually so that you can make your report turn on any segment of your account code This is accomplished with the BSPEC Function BSPEC The BSPEC function expands the functionality of the Account specifier by Building the Account SPECifiers from cell references The BSPEC is really just the Account parameter in any F9 formula that uses an Account parameter The Syntax of the BSPEC is BSPEC Segment 1 Segment 2 Segment 3 Where Segment is a cell reference to the first segment of your account Segment 2 is a cell reference to the second segment of your account Segment 3 is a
9. in the above Example and select Paste Edit Paste This will paste in all P L Natural Account codes 2 Click on the NGL formula and copy the formula down so that it covers all account numbers 3 Copy the first NGL formula and paste it to the corresponding Row in Column B and edit the formula so that it is a DSEC formula That is change NGL to DESC then delete the Period specifier Your Description formula will now be similar to DESC BSPEC B 1 A10 B 3 B 5 B 6 B 7 B 8 If the correct description is not returned ensure that Structure Code is correct 4 Copy the DESC formula across all account numbers 5 Recalculate the report 6 AuotFit and Save the report A Quick Tour of F9 Page 11 You have now completed the beginning of your Departmentalized P L Statement To change the Department or loca tion etc simply edit the appropriate cell on the sheet cell B2 in the sample and recalculate To change the Period simply edit the Period specifier If you like make this a multiple period report by adding Periods You can use the features of Excel and F9 to make this report exactly how you would envision the ideal P L Report Add sum lines change single account specifiers to ranges add a variance column to compare the current year versus the past year make graphs and charts add macros etc These are all benefits of creating your financial reports with F9 in a spreadsheet program as powerful as Excel For more information
10. including your account numbers column A descriptions column B and balances for the current open period column D The cells may need to be resized to show all of your information Resizing Cell Widths Using AutoFit 1 Select the entire sheet by clicking on the Select All button which is located at the corner between Row and Column A After clicking the Select All button the entire sheet will be highlighted To AutoFit the column widths choose Format Column AutoFit Selection Select All button A L 1 1 2 Rename this spreadsheet GL Formula by double clicking the Sheet1 tab at the bottom left of the Excel window and typing GL Formula Press Enter 3 Save the workbook as F9 Tour by selecting File Save typing the File name F9 Tour and clicking Save You will want to save all F9 Reports often and always be sure to save a backup copy when it is completed Now that we have our Chart of Accounts in Excel we will want to start learning the basic F9 formulas The Fundamental F9 Formula GL The GL Function is the fundamental F9 formula as it links any cell in your spreadsheet to any balance in your general ledger This is accomplished with a string of parameters that tell F9 what balance you would like returned The syntax of the GL formula is GL Account Period Company Year Type Currency Where Account is the GL Account number Period is the fiscal period Company is the specific company Year is the specific fiscal
11. lish terms Period Specifier Examples Description Month 1 The first period of the fiscal year This Month Month The current usually open period 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 trans actional values by default September The balance for September Quarter 1 QTR 1 Total for the first quarter Let s make the Period specifier January N Edit cell D2 so that your Period specifier the second parameter in the GL formula is January type January in cell D2 Recalculate the workbook by pressing the F9 key The values in column D will change to the value of that account in the month of January please note if you are currently posting to January in your GL this value will not change We will now want to make this report for multiple periods Fortunately we do not have to manually enter each month in the appropriate cell as Excel has the ability to generate data automatically based on the data in the original cell This is referred to as a copy series My oe BoD 5 6 Ta Select cell D2 January Move your mouse over the fill handle and your pointer will becom
12. nts that have a first character of 4 character in the exact and third and fourth character of 0 for Department position 010 and Division 100 Indicates a range of 4000 5000 Returns all accounts from 4000 up to and including account segment val 5000 for all departments and all divisions ues d Creates a list of 4000 010 100 5000 Returns a balance for the sum of account 4000 010 account specifiers or 100 and 5000 with all departments and all divisions account segments Please note that for most ranges of accounts to work you must have Return Zero For Account Match Not Found selected in F9 Setup Let s add a line to the report that pulls the value for all Balance Sheet accounts and a line that pulls the value for all Profit and Loss accounts A Quick Tour of F9 Page 7 Returning a Balance for All Balance Sheet Accounts and All Profit and Loss Accounts 1 Scroll down to the last line of your report 2 In the first empty A cell enter the appropriate account range for all Balance Sheet accounts In most cases the Natural Account range would be 1000 3999 with an asterix for all sub accounts 3 Inthe next A cell enter the appropriate account range for all Profit and Loss accounts In most cases the Natural Account range would be 4000 9999 with an asterix for all sub accounts 4 Copy the formulas from the above row to these rows and calculate the current sheet Shift F9 The descriptions for these ranges will not s
13. ormula in cell D4 to cell E4 Auto Fill the formula in cell E4 down all account numbers Recalculate the workbook F9 Oi S Roe hs Save the workbook That s it You now have January s budget values for the specified Type cell C1 in Column E Description Formulas We will now want to make the descriptions in this report formula based so that when you edit an account number it will return the appropriate description In F9 there are two formulas for returning account descriptions these are the DESC Function and SDESC Function DESC returns the account description for the full account code If the DESC function is used on a range or list of accounts it returns the appropriate description for the first account number in the range or list SDESC produces the descriptions associated with the individual segments of the account code In short the DESC is used to return the Nat ural Account description and SDESC is used to return the Sub Account description DESC Function The syntax of this formula is the same as the GL function except it omits the Period specifier DESC Account Company Year Type SDESC Function The syntax of this formula is SDESC Segment Segment Number Company Type Where Segment may contain either a single segment value or a complete account code If a complete account number is provided the single segment is extracted and evaluated Segment Number always contains the number of the segment you wish a
14. own or across the report so make both the column and row references absolute A 1 Press Enter The formula should now be GL A4 D 2 A 1 B 1 C 1 D 1 and is ready to be copied down all of the account numbers Auto Fill l Click on the cell in which the first GL formula is currently specified D4 You will see a fill handle which is located at the bottom right of the cell when it is selected Move your mouse over the fill handle and your pointer will become a crosshair C Fill harail Double click the fill handle The formula will now copy down all rows that have a value in Column C If there is no value in Column C or if your version of Excel does not support this feature simply drag the formula down by clicking and holding the left mouse button over the fill handle and dragging the formula until the last row with an account number is selected Click on any cell in your spreadsheet You will notice that all of the cells have the same value as cell D4 This is because the sheet is set for manual calculation Press F9 to calculate the workbook and the values from your GL will populate the report Because the Period specifier cell D2 is currently the word Month the values returned are for the current usually open period Page 4 A Quick Tour of F9 Period Specifiers In F9 the Period specifier is used to identify the period or periods for which you are requesting account balances and uses logical plain Eng
15. r more information on Manual Cal culation see the Excel Help under Calculation View Your Chart of Accounts Chart 1 Now we will want to view your Chart of Accounts To do this select Chart from the F9 pull down menu F9 Chart This will open the F9 Chart window F9 Chart Professional X V Include Description IV Include Account Origin Balance or P L IV Prepend the account code with a quote I Skip Accounts Showing all Zeros Accounts Periods Month Company E 59325Abc btr x Year 1998 Type ACTUAL x Currency Home Account Match Count 0 Reset Clipboard Quit Help A Quick Tour of F9 Page 1 2 Click on the Reset button in the Chart window Clicking Reset will set the Chart options to be the same as those specified in F9 Setup If your company and current year are not listed in the listed in the Company and Year dia log boxes refer to the Installation and Setup Chapter of the F9 Manual 3 Now click the Find button F9 will then run through an Account Match Count When all the accounts in your GL have been found you will get a message box stating the number of account keys that were copied to the Clip board Click on the OK button in this message box and Quit the Chart window This will bring you back to your blank workbook 4 Click on cell Al and Paste the contents of the clipboard Edit Paste This will fill the spreadsheet with your basic account information
16. rrect 5 Copy the formula down so that it covers all accounts and recalculate the current sheet Shift F9 You should now have the appropriate description for the segment you specified returned in Column C 6 Save the File Account Ranges So far the only values returned have been for individual account numbers but in most financial reports you will want to return values for a range or series of accounts in one cell For example to return a value for all of your cash accounts a Natural Account code of 1000 through 1099 you would specify a range of accounts that includes all cash accounts Let s assume that you have a three segment account code where the first segment of your account code is the Natural Account the second is the Department and the third is the Division In this example the Account speci fier would be 1000 1099 This would return the value for all accounts between 1000 and 1099 for all Depart ments and all Divisions As you can see Wildcards the asterix can be used in specifying ranges of accounts with F9 Account Specifier Description Example Result ig Matches any charac e Returns all accounts all departments all divisions ter or set of charac ters regardless of Returns the 4000 accounts for all departments in the length 4000 100 100 division Returns all accounts that start with a 4 for Depart 4 010 100 ment 101 and Division 100 Matches a single 4 00 010 100 Returns all accou
17. to select the List that will be generated List Segment determines which segment will be listed Page 10 A Quick Tour of F9 Where defines the limitation that you will define in the Is section Is determines the limitation on the List output i e an Account range only for P L accounts Wildcards and ranges can be used Transpose List generates the List on the horizontal axis if selected usually selected for Period specifiers Generate Descriptions creates descriptions if available for the selected segment not formula based 2 Select Segments under Available Lists 3 Select the following e Inthe List Segment drop down select Account e Inthe Where drop down select Account e In the Is field type 4000 9999 or the appropriate range for all of your P L Accounts F9 Lists Professional x m Available Lists Segments C Lists C Companies Periods Budgets C Years C BWB Headers C Currencies C TWB Headers C Accounts List Segment Account Where Is Account v 4000 9999 v I Transpose Lit MM Generate Descriptions Send to Clipboard 4 Click Send To Clipboard press OK to the List was Sent To Clipboard message and Quit the Lists window Now that the List of all of your P L Accounts has been sent to the Clipboard we will want to paste this into the BSPEC sheet and finish the report 1 Select the cell in the BSPEC sheet that contains your first account number Cell A10
18. ula the Account specifier A5 is correct but the Period specifier is now cell D3 which is blank and is obviously not correct In fact the Company A2 Year B2 Type C2 and Currency D2 are also incorrect now To copy this formula correctly we will have to ensure that Excel does not adjust the cell references when we copy the formula This is called an Absolute reference and is accomplished by placing a dollar sign before the parts of the reference that do not change For more information see the Excel Help Index A Quick Tour of F9 Page 3 5 Click on cell D4 and in the formula bar located at the top of the spreadsheet above the column names move your cursor to the Account reference A4 GL A3 D2 A1 B1 C1 D1 Press F4 Each time you press F4 Excel toggles through the combinations absolute column and absolute row A 4 relative column and absolute row A 4 absolute column and relative row A4 and relative column and relative row A4 We will want the Account reference to change as we copy down rows relative no but not across columns absolute Press F4 until the Account reference is A4 You can also add the symbol with the keyboard We will not want the Period reference to change as we copy it down rows but we will across columns relative column and absolute row Make the Period reference D 2 The Company Year Type and Currency cell references should not change when we copy the formula d
19. x Currency Home x Location B NA x Create GL Function by Account faoo00 Costs of Goods Sold x Period 7 Department Jooo NA Balance z 0 00 E To Chart To Drill Hep Quit 3 Using the drop down lists complete the specifics of the GL Function That is the Account by Company Identi fier Periods Type Segments Year and Currency The Create GL Function By option determines which GL parameter becomes the horizontal axis on your report In most cases this will be Period Be sure to select your first P L account as the Natural Account and leave the other account segments as asterix ALL 4 Click Get Balance to return the balance of the selected account s 5 Click Quit Now to paste the GL Formula into the spreadsheet we will use GL Paste GL Paste interprets the information from the Account Enquiry to form a valid GL Function ba 1 Select cell Alin the BSPEC spreadsheet and click the GL Paste toolbar button The formula as well as the Con trol area for your report is pasted into the spreadsheet A Quick Tour of F9 Page 9 2 Select the entire sheet and Auto Fit the column widths Choose Format Column AutoFit Selection Your sheet will now look similar to Fd Microsoft Excel F9 Tour_xls Iof x F File Edit view Insert Format Tools Data Window F3 Help ee ere 10 B F oe 7 NGL BSPEC B51 A10 5653 E 9 635 5656 657 630
20. year Type is the Account type i e Actual or Budget that you want this value returned for Currency is the currency type if your accounting system supports multiple currencies Write this formula down in fact write it down twice as it is the core of all F9 Reports The GL formula does not require all 6 parameters so if your accounting system does not support multiple currencies you do not need to enter this specifier In many instances you will want your balances to be returned as negative values To do this simply use a NGL func tion in place of the GL function The parameters of the NGL function are identical to the GL function Page 2 A Quick Tour of F9 The GL Formula 1 Open the F9 Tour workbook and select the GL Formula sheet if it is not active click the GL Formula tab Cell D4 should contain your first balance Click on cell D4 and you will see that although there is a value in this cell there is no formula The true strength of F9 is rooted in F9 formulas and the ability of all spreadsheets to use absolute and relative cell references so that they can turn on a single cell 2 In cell D4 type the GL formula substituting the appropriate cell reference for each parameter of the GL formula In the example below as in your sheet the GL Formula will be GL A4 D2 A1 B1 C1 D1 Where A4 is the Account number D2 is the Period A1 is the Company B1 is the Year Cl is the Type D1 is the Currency E F9 Tour xls CN Account
Download Pdf Manuals
Related Search