19.3 The main calculation spreadsheet (Menu)

19.3.2 Update data from SharePoint

After the link has been established (See above), metadata from the registration in SharePoint will appear here. The data in the calculation spreadsheet will be updated by clicking the Update leases button on the Menu.

The metadata will contain changes that are relevant, as well as changes that are deleted.

Update all data and the recalculation by clicking

Clear all leases by clicking

This can also be a function that your auditor uses to control that the calculation is correct and that you have not made manual adjustments.

If you have an integration function, you can get an integration export sheet by clicking Export leases.

19.3.3 Settings

By clicking the Settings button, you’ll find a settings menu with different options.

Warning and error settings

Hide warnings: When you check this box, Warning tab won’t be shown in the calculation spreadsheet. This is useful if you have checked all warnings and have concluded that the leases are registered correctly.

Hide errors for non-IFRS 16 liable leases: When you check this box, the errors for the short-term and the low-value leases registered in the system will not be displayed in the calculation model.

Currency settings

Use closing currency rates only: By checking this box, only the closing currency rate will be used. The closing currency rate is determined by the currency date, which is the same date as the Period end date set in the Lookup periods. By unchecking the box, the system will automatically calculate income statement captions and changes to average exchange rates. Lookup periods set the date range to retrieve the average exchange rate.

Currency exchange rate source: Here, you can specify your currency source. Two sources are currently available in the dropdown menu: Norges bank and Sveriges Riksbank. If you’re interested in adding your own currency source, please contact us to see if it’s possible to include it.

Calculation settings

Pro Forma calculation: Turn on this feature to get proforma figures in a period before the acquisition date of leases acquired through a business combination. By checking the box, all the acquisition date effects will be removed. This means that the calculation model will adjust the ROU asset so it corresponds with the remaining liability on the IFRS 16 implementation date, January 1st 2019, instead of the acquisition date. A warning will show up if you set the calculation model in Pro Forma mode.

Pre implementation calculation: By checking this box, the implementation date effect will be removed. This means that the calculation model won’t make any adjustments and that the ROU asset will correspond to the remaining liability on the IFRS 16 implementation date, January 1st 2019. A warning will show up if you set the calculation model as a Pre Implementation mode.

Short-term liability formula: We support two separate definitions for the short-term liability. By selecting Standard formula, the short-term liability will be the sum of the instalments for the next 12 months. By selecting Alternate formula, the short-term liability will be the sum of the discounted value of the lease payments for the following 12 months. When selecting a formula, its definition will be displayed.

Extra functions

These are used to export the calculation at a given time to a separate system like Power BI. Contact us for details

19.3.4 Lookup periods

For the period selections (Period Start and Period End), you can locate Lookup periods by using the drop-down menu or by entering the dates individually. It’s not necessary to set a start date or an end date. If the start date is not specified, it’s automatically set to January 1st of the same year as the end date. When the end date is blank, it will automatically be set to the last date in the same month as the start date.

For information for one month, set as either

For the first quarter in 2019, either

For the second quarter 2019

For the full year, 2019, either

For Year to date period (for example, January 2019 to October 2019), either

Period Start and Period End will affect which numbers appear in the result column. Period Instalments show the numbers between Start and End. If both are empty, the search result will be empty. If only the Period Start is empty, you get the sum for the entire period from January to the Period End month in the year that is set in the Period End (I.e. year to date amount). If only the Period End is empty, you get the result for the month that’s set in the Period Start (I.e. a one-month period).

19.3.5 Currency

The Target currency cell is the currency of results that the numbers should be displayed in for the different summary sheets. If a lease is in a different currency, the calculation result will be converted to the target currency, using the rates in the currency table. The conversion mechanism (Average exchange rates or closing exchange rates) can be chosen in the Settings menu. The period selector, Lookup periods, determines which exchange rates are retrieved. The opening exchange rate is the last exchange rate available before Period Start. The closing rate is the last exchange rate available before or on the Period End. And the average rates are the monthly averages for each month in between.

If no Internet connection is available, or if the Lookup periods are longer than 1 year, with Sveriges Riksbank as the source of the currency exchange rate, you’ll get the following error message:

The currency table is based on the currency exchange rate source specified in the Settings menu (Currently either Norges bank or Sveriges Riksbank). If the target currency doesn’t exist in the currency table, you need to either change the target currency or update the currency table to include the currency. A textbox will appear to inform the user about this.

You can also use your own exchange rates. To do this, click the Edit currency table button. This will show you the exchange rate table, which you can edit freely. Editing the table will lock it, meaning that it‘ll no longer automatically update rates from Norges Bank or Sveriges Riksbank. In this case, a textbox informing the user about this will become visible, and a button to re-sync the exchange table to its source will appear. The textbox and the button are shown below. If this button is clicked, the table will automatically retrieve rates based on dates in the selected Lookup periods.

19.3.6 Go directly from the calculation file to the contract in SharePoint

From a calculation spreadsheet of an individual contract in the calculation file, you can go directly to the individual contract in SharePoint by clicking Open in SharePoint in the upper right corner.

Please note that the SharePoint add-in will open each time you click on the tab.

This functionality makes it easier and quicker to review and to make changes to an individual contract, since you no longer need to find the contract in SharePoint.

The second part of the menu is dedicated to filters. These are the criteria used when creating the overview. If these are empty, all the contracts will be displayed.

To set a new filter, you can either tap the drop-down menu in the left column and get a list of all available filters, or you can directly enter the name of the filter.

To make a selection, choose from the drop-down menu in the corresponding right column.

Since there are many selections in the drop-down menu, you can quickly get a full list of alternatives with explanations and choose the filters you want. Click any cell in the left column and then press Ctrl and Space at the same time. This enables you to quickly search the filter by entering key words or letters (Auto complete) in the popup window. Click on the filter you want to use and the description of the filter will be shown in the window. Then click OK.

When a filter is selected, the overview is automatically updated. To delete a filter, you can clear the values in any of the two columns. This is most easily done by selecting the cell and pressing Delete on the keyboard.

The drop-down menus are currently selected so that they are dynamically updated as well. This means that if, for example, you have four categories in total among all your contracts, and these are strictly distributed among two companies, only the associated categories will appear in the selection menu if a company filter has already been selected.

The Title filter is a free text search, rather than a choice, and it searches the name that each contract has been given in SharePoint. The field is read as Regex, which means that you can do quite complicated searches if desired.

In the 2021 version, you can use the Search function on filters, columns and rows in the calculation file to access relevant information.

Place the cursor in the cell where you want to add a lookup filter and press Ctrl + Space to see a full list of alternatives with explanations. It’s quite flexible. You can use abbreviation s to get alternatives. For example, write “Disc” to locate filter alternatives for the discount rate.

Write, “end date”, to get suggestions for the end date.

19.3.8 Modify columns in the view

The way the filters work, you can set up the result yourself. The Menu sheet has always been the easiest way for the user to create their own reports. In addition to filters, you can also customize overview, by either selecting from the drop-down menu found on all cells in the title bar for the results or by entering the header in the cell directly.

The user can quickly search the result in a separate menu, that appears when a cell in the title bar is selected, by pressing Ctrl + Space. This menu is searchable and includes information on what the result represents. You can quickly search the selections by entering the key word or letters (Auto complete) in the pop out window. Then click the title of the result you want, followed by OK.

After a selection has been made, the view is updated automatically. Only the Title option will result in a link to the worksheet associated with it. To delete a column from the results, you can select the title of the result and delete the content by pressing Delete on the keyboard.

As mentioned in the previous section, some of the results depend on what has been selected as Period Start and Period End.

19.3.9 Archive calculation and serve links between SharePoint and calculation file

We recommend keeping copies of previous calculations in a financial close, in order to document the calculation for this close. Click the archive button to save a copy of the calculation that you want to archive. The link to SharePoint is deleted in the archive calculation file, so it won’t be updated by new information from SharePoint.

Open the calculation file that you want to archive and click Archive calculation. A window then pops out for you to save the calculation. The name of the calculation Excel file is automatically set as the name of the original calculation file archive date. If you want, you can change the name of the archived calculation file before you save it.

Clicking the Archive calculation button will serve all the links to live contract data registered in all the SharePoint libraries from the calculation model. It’s also possible to just sever the link between the calculation and one or some specific libraries in SharePoint, so the calculations won’t be updated with new data that are registered in those libraries.

The calculation file contains one worksheet with the live data for each of your SharePoint libraries. You’ll have to repeat the following process and sever the links to libraries individually.

First, select the worksheet with the library you plan to disconnect (For example, “Cars and machinery” or “Real estate”).

The contents of the worksheets containing data in SharePoint usually contains a single data table and would look similar to the following image.

Once the sheet is selected, you can sever the connection in one of two ways.

The first way is to right click anywhere inside the table and choose Unlink from Data Source in the Table submenu. If this option doesn’t appear, but the Table option does, it means the sheet is already disconnected.

You’ll be prompted with a warning, asking whether you want to complete the action or not.

The second way of disconnecting the table is to first click anywhere inside the table to make the Word table context ribbon appear (Table Design) and then click the Unlink button on this ribbon.

If the button is greyed out, along with the other connection buttons (Refresh, Properties, Open in Browser, etc.), it means that the sheet has already been disconnected.