Embedded Files
Quick Start
Quick Start
This is a short tutorial designed to familiarize you with the basic concepts of creating a financial report with FRPro. Every FRPro financial report starts as a worksheet and uses the features of Microsoft Excel. For this reason you will also find Excel’s online Help to be an invaluable aid when you create your financial reports.
FRPro Security Setup
1. In order to use FRPro, your Excel macro security setting must allow unsigned macros.
2. Changing Excel's macro security
Open up the "Tools" menu and click "Macro" then "Security".
Select the "Medium" setting
Quit Excel then open the document again.
For Office 2007
FRPro Start up
1. Ensure that your security level is set to Medium (see the section on FRPro Security Setup). Open the FRPro Excel Worksheet.
2. When Excel prompts the Security Warning Dialog Box, make sure you click the “Enable Marcos” button.
3. The Login dialog box should appear, use the User Name and Password of FRPro to access the Financial Report
4. If you prefer FRPro calculate manually to improve performance, choose Tools | Options. Select the Calculation tab and click on the Manual button.
The worksheet is now set to Manual Calculation mode. This means that you must instruct Excel to calculate your formulas. Press the F9 key to calculate your entire workbook. Shift+F9 to calculate the active worksheet only, or F2+Enter to calculate the active cell. To save time, all FRPro reports should be created with Excel set to Manual Calculation, so that the entire workbook does not recalculate every time you edit a cell. For more information on Manual Calculation see the Excel Help under Calculation.
The Fundamental FRPro Formula: =GL
The GL Function is the fundamental FRPro formula as it links any cell in your worksheet to any balance in your general ledger. This is accomplished with a string of parameters that tell FRPro what balance you world like returned. The syntax of the GL formula is:
=GL(Account, Year, Month, Balance Type, Period Type)
Where: Account is the GL Account number
Year is the specific fiscal year
Month is the specific month of the fiscal period
Balance Type specific 'N' for net amount,
other options are 'D' debit amount and 'C' credit amount
Period Type is ‘YTD’ (Year to Date) or ‘PTD’ (Period to Date)
In many instances you will want your balances to be returned as negative values. To do this simply uses a NGL function in place of the GL function. The parameters of the NGL function are identical to the GL function.
The GL Formula Example
In cell E9 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(A9,A3,A4,"N","YTD")
Where: A9 is the Account number
A3 is the Fiscal Year
A4 is the Month of the Fiscal Period
“N” is the Net Balance
“YTD” is the YTD Balance
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 E9 to cell E10, all row references will changed by one row, This formula would then be: =GL(A10,A4,A5,"N","YTD").
In the new formula, the Account specifier (A10) is correct but the Fiscal Year is now cell A4 – which is “3” and obviously not correct. In fact, the Fiscal Month (A5) 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.
1. Click on cell E9, and in the formula bar (located at the top of the worksheet, above the column names) move you cursor to the Account reference (A9).
2. Press F4. Each time you press F4, Excel toggles through the combinations: absolute column and absolute row ($A$9), relative column and absolute row (A$9), absolute column and relative row ($A9), and relative column and relative row (A9). 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 $A9. You can also add the $ symbol with the keyboard.
3. We will not want the Fiscal Year and Fiscal Month to change as we copy it down rows,so make both the column and row reference absolute ($A$3 and $A$4).
4. Press Enter
The formula should now be: = GL($A9, $A$3, $A$4,”N”,”YTD”) and is ready to copied down all of the account numers.
Auto-Fill
1. Click on the cell in which the first GL formula is currently specified (E9). 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.
2. Double click the fill handle. The formula will now copy down all rows that have a value in Column D. If there is no value in Column D, 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.
3. Click on any cell in your worksheet. You will notice that all of the cells have the same value as cell E9. This is because the sheet is set form manual calculation. Press F9 to calculate the workbook, and the value from your GL will populate the report.
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 your bank accounts (a Natural Account code of 2009 through 2018), you would specify a range of accounts that includes all bank accounts.
Returning Budget Values
To return budget values in FRPro, use the GLBudget Formula as for GL formula.
The syntax of the GLBuget formula is:
=GLBudget(Account, Year, Month, Balance Type, Period Type)
Where: Account is the GL Account number
Year is the specific fiscal year
Month is the specific month of the fiscal period
Balance Type specific 'N' for net amount,
other options are 'D' debit amount and 'C' credit amount
Period Type is ‘YTD’ (Year to Date) or ‘PTD’ (Period to Date)
Returning Account Name
We will now want to make the account name in this report formula-based so that when you edit an account number it will return the appropriate account name.
The syntax of GLAcctName formula is simple:
=GLAcctName(Account)
Download FRPro.xls
See also
- How to copy FRPro worksheet to new worksheet retaining values and format?
Report abuse