Calculated Columns

Calculated columns contain figures resulting from calculations on data in other columns of the statement. To define a calculated column, use standard spreadsheet formulas in the default column specification row.

For example:

	Current Balance		Last Year Balance		Curr/ LYear
Petty cash	274.57		358.00		.77
Bank account, US dollars	8,437.54		5,932.90		1.42
Bank account, CAD dollars	4,372.96		3,665.39		1.19
Total:	13,085.07		9,956.29		1.31

In this statement, the default formula for the last column (column H on a report specification) is "=(F1/G1)". The formula states that this column contains the value of column F divided by column G.

When the report is generated, Financial Reporter adjusts the relative cell references on each line.

Using Currency Exchange Rates in Column Calculations

The Financial Reporter also provides access to the exchange rates kept by Sage ERP Accpac, so you can create financial reports in any currency you maintain -- using the exchange rates from any fiscal period.

You use the FRRATE function to get exchange rate information. FRRATE lets you specify a functional currency, a source currency, a rate type, and a date. From this data, it provides the rate of exchange.

For example,

 

 =FRRATE("USD","JPN","AV",FR("END"))

will return the average rate in effect for converting Japanese Yen to US dollars at the end of the fiscal period for which the report is being printed.

A ledger which used the Yen as functional currency could report its earnings in US dollars with the following formula in the US dollars column (assume that Yen are reported in cell F9, and that the rate is a multiplier):

 

 =(F9*FRRATE("USD","JPN","AV",FR("END")))

The FR function in the above examples is used instead of a date, to allow the rate to match the period for the report.

If no date is specified (the date is an optional parameter), Financial Reporter will use the period-end date for the default fiscal period of the report. If there is no period-end date (this is possible if you open a sheet with formulas that has no specification), the program will use the session date.

Exchange rate is expressed as a multiplier

Note that the rate returned by the FRRATE function is always used as a multiplier to convert source currency to functional even if you defined the rate as a divisor.

If the rate is defined in the System Manager as a divisor, Financial Reporter returns the reciprocal of the rate.