Search this site

Microsoft Business Central Revaluation of Exchange Rates These adjustments are done to revalue all the customer ledger entries, vendor ledger entries and bank ledger entries. Join On Point Academy to view all content Join the academy → Log in → To perform Revaluation an end of month revaluation of currency. 1. Choose the Search icon, Currencies , and then choose the related link. 2. On the Currencies page, click on Process and Select Adjust Exchange Rate page . 3. On the Adjust Exchange Rate page , Starting Date : Specifies the beginning of the period for which entries are adjusted. This field is usually left blank, but you can enter a date. Note: Best practice is to leave it blank so any adjustments you missed previously can be captured. Ending Date : Specifies the last date for which entries are adjusted. This date is usually the same as the posting date in the Posting Date field. Posting Date: Specifies the date on which the general ledger entries are posted. The posted entries appears at the Exchange Rate Adjustments Registers . This date is usually the same as the ending date in the Ending Date field. Document No: Specifies the document number that will appear on the general ledger entries that are created by the batch job. For example ADJUST MAY EX. Adjust Customer, Vendor and Bank Accounts: Specifies if you want to adjust customer, vendor, and bank accounts for currency fluctuations. Adjust G/L Accounts for Add.-Reporting Currency: Specifies if you want to post in an additional reporting currency and adjust general ledger accounts for currency fluctuations between LCY and the additional reporting currency. 4. Click on OK. Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Jet Reports Posted Purchase Invoice A purchase invoice can be any receipt or record of a supplier invoice for goods or services, which also include rent and utility bills. The invoice details are entered manually and the invoice lines are matched to outstanding purchase orders. To create this report, you need to pull out information from the following table. Join On Point Academy to view all content Join the academy → Log in → Procedure Step 1 Know where you are pulling your data from Just as shown below, the NL(Rows) will contain our source of data, which will be pulled from the table “VAT Entry“. The NF (Keys) will be a cell reference, which will be referencing cell $D7, and the fields will be pulled from below the “$D&“ names as shown. Since we now know where to pull our table, keys, and fields from, I’ll show you how to do that here. Once you are done creating the functions, you can remove the names on the last row. They are just there to guide you on the name to put in the field when filling in the formulas. filling the formulas for the NL function. Fill in as shown below Filling the formulas for the NF function Fill in as shown below. Note: use the same procedure to fill in all the NF functions. the key will be the same, just the field will be different. Filling in the NL Formula for Purch. inv Line. Fill in as shown below. Note that we apply a filter (Document No) here, referencing no. pull from Purch. inv. Header. Filling the formulas for the NF function. After filling in the formulas, it should look like this. Step 3 Running your report. When you run your report, it should look beautiful as shown below. Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Jet Reports Using Report Wizard A Report Wizard is one of the Jet Features in the ribbon tools that allow us to do some cool stuff using Jet Report. A Report Wizard makes building reports easy by guiding you through a series of questions to help you generate your report. A Report Wizard let you mark out the fields you wish to include in a report, allowing you to add a filter, group, sort, and also layout you want to use for your report. I’ll show you how to create a Trial Balance using Report Wizard in this report. What do you understand by Trial Balance Report? The trial balance report shows the balance of each nominal account at a particular point in time. the report might include debits and credits posted to each account. our trial balance report will be created from the G/L Account table with the following Fields; No, Name, Date, Income/Balance, and Balance. Procedure. Step 1 Open your Report, Wizard Just at the ribbon under the Jet Report section, you should be able to see Report Wizard as shown below. Click on that, then a new window will pop up, just like we have below. Select the table you want to create your report from, then click on ok as shown below. that will take you to the next page. Step 2 Selecting the fields and applying the necessary criteria Field Select the field you want to show in your report in the (Add Fields) by searching the name of the fields on the search tab. the selected fields will be shown in the Column order box, then click on next. Filter Group Sort We sort our balance in descending order, from the highest to the smallest. Total We want our report to show the total balance, so we select it also. Format We want to be able to edit our report after creating it with Report Wizard, so click to add basic Excel formatting. Step 3 Review your report Once you are done selecting, click on next. this will take you to a page to show you all you’ve done so far. Once you’ve cross-checked your work, you can click on finish. This will automatically create your Report, and it should be like this. Step 4 Running your report. Once you have your report created, you can then run your report. When you do that, it should be like this. Join On Point Academy to view all content Join the academy → Log in → Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Microsoft PowerBI How to Churn Analysis in Microsoft Power BI Churn analysis is an important aspect of customer relationship management that every organization must pay attention to. It helps to identify and understand why customers stop doing business with a company, which in turn enables organizations to take the necessary steps to improve customer retention. Join On Point Academy to view all content Join the academy → Log in → Churn analysis is an important aspect of customer relationship management that every organization must pay attention to. It helps to identify and understand why customers stop doing business with a company, which in turn enables organizations to take the necessary steps to improve customer retention. Churn analysis in Power BI typically involves analyzing customer data such as transaction history, frequency of purchases, and communication with the business. By analyzing this data, businesses can identify patterns and trends that can provide valuable insights into customer behaviour. For example, Power BI can be used to analyze the purchasing habits of customers who have stopped doing business with a company. By examining factors such as the frequency of purchases and the types of products purchased, businesses can identify patterns and trends that can help them understand why these customers churned. Another area where Power BI can be useful is in analyzing customer feedback. By examining customer feedback data, businesses can identify areas of weakness in their products or services that may have contributed to customer churn. This can help businesses take the necessary steps to address these issues and improve customer satisfaction. Power BI can also be used to analyze customer engagement. By analyzing data on how customers interact with the business, such as website visits, social media interactions, and email communications, businesses can identify areas where they can improve engagement and prevent churn. How then do we do this in PowerBI? Let’s go into PowerBI. The model used is the model you are familiar with First, we need to have our base measures set. Total Sales 2. Total Customers Now, for our churn measures. Let’s think of the logic together. Say you are managing a business and you need to identify customers that are no longer buying from you. What would you do? How would you go about it manually? Think about this before moving downwards. Well, your guess is as good as mine. To do this manually, you will need to follow the process as thus: Get a list of all customers that have purchased from you Get a list of customers that have purchased from you recently. Recently is relative i.e. it could be a month, two months, three months etc. Compare both lists to identify who is on number 1 and not on number 2 Similar logic is followed for the DAX measure. The Churned/Lost customers can be calculated with the DAX below The Customers_last_365_days variable creates a virtual table containing a list of customers who purchased a product between 365 days ago to 60 days ago. The Customers_last_60_day variable creates a virtual table containing a list of customers who purchased a product between 60 days ago to the current day context provided by the calendar table. The COUNTROWS function counts the number of customers who made purchases in the previous period (Customers_last_365_days) but did not make any purchases in the current period (Customers_last_60_day). If we are losing customers, what that implies is that we are losing potential revenue. The DAX below calculates the potential revenue we are losing out on. Note that the revenue doesn’t necessarily mean that the exact amount will be made if the customer is not churned. It is best interpreted as you are losing a certain percentage of that lost revenue with the customer being churned. For example, If I say Nigeria is a $500 billion economy. It doesn’t mean every company will generate that amount as revenue but companies operating in Nigeria will generate a certain percentage as revenue. That percentage could be as low as 0.000000000000001% and as high as it could get. Now that we have gotten that out of the way. Identifying lost customers is not just sufficient. My Former Boss (Joshua) used to say something. So what? We’ve lost 300 customers, so what? What do we do with that? How does it help us take the next step? The so what can be gotten from understanding what those lost customers bought, where they brought from, their feedback on both the product and service rendered to them etc? Let’s take a quick walkthrough of this together Overall, churn analysis in Power BI can provide organizations with valuable insights that can help them improve customer retention and increase revenue. By identifying patterns and trends in customer behaviour, organizations can take proactive steps to address issues and improve the overall customer experience. If you’re looking to improve customer retention in your business, churn analysis in Power BI is worth exploring Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Microsoft Business Central Exchange Rates in Microsoft Dynamics 365 Business Central Introduction The aim for this tool is to allow accountants and finance professionals to download the daily exchange rates from Currency Exchange Rates - International Money Transfer | Xe source directly into Microsoft Dynamics 365 Business Central . This may be done for each company within the instance, allowing the user to define the company parameters. Join On Point Academy to view all content Join the academy → Log in → Why do we need it? This app is not the natural choice when we have euro-group of companies or a euro company however this changes as soon as we are faced with a group of companies that have a number of companies operating with different base currencies. By having one single source of information such as XE, we are ensuring that no fictitious differences on exchanges are created, which are usually created as a result of reworkings and rounding of numbers. Key Parameters per company Domain Address API Key Username Base Currency Key Advantages One common source of currency exchange rates Less exchange rate differences Less reconciliation issues Making use of an internationally-accepted data source Pricing €200 one-time installation or for existing On Point Customers - 2 hours from a package of hours €25 monthly fee per instance Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Atlassian Academy Atlassian Connect - Malta 21 October 2022 at 08:00:00 RSVP Atlassian Connect - Malta 23 June 2022 at 07:30:00 RSVP Jira Software Workshops - Demo 19 January 2022 at 09:00:00 TICKETS Jira Software for Construction and Real Estate 23 March 2022 at 12:00:00 TICKETS Jira Software for Banking 9 March 2022 at 12:00:00 TICKETS See all events → Available Courses Advanced Beginner Intermediate Course Jira Admin Course Understand How to Set up and Desgin Jira as an Admin Learn more Course Jira Cloud Fundamentals Beginner Course to Get Started with Jira Learn more Course Manage Agile Teams in Jira A Team Leader's Guide to Jira Cloud Learn more Related Articles Access our resources loaded with insightful articles , how-to 's and recorded webinars See all how-to's →

Jet Reports Report Options Introduction When Creating a report with Jet, Report Options are created to allow us to select specific filters to narrow down the report to show just the information you want to see. Report Options are mainly created for Jet’s particular users (Viewers). This gives them the ability to apply some specific filter to the report without changing the report itself. Report Options show as a pop-out window when you try to run a Report (built with report Report Options) just as shown below. Procedure. Step 1 Creating Report Options The Report Options Function on Excel Ribbon Tools is the key to creating Report options. When Click on the Report Options tab, the Window pop-out empty like this You can then Fill in the spaces as shown below Title = Start Date Value = 1/1/2022 Tooltip = Select a start date of your choice. For the look-up, I’ll show you how to use that later. use the same procedure to fill in the End date also, and your result should look like this. Once this is done, a new sheet named Options is created automatically. Step 2 Tags For creating a Report Options page We have a required and optional tag to create our report options Required tags Creating a Report Options window requires the use of three tags which include, Option, Titles, Values. Optional tags Two additional tags can be used to add functionality to Report Options windows which are, Lookup and Tooltip. Required tags Option The Option must always be in column A, any row that has OPTION in column A has become an option in the report options. Titles This creates a title for the options. it is always in the columns next to “Option” Values You can create value for your option here. Join On Point Academy to view all content Join the academy → Log in → Procedure. Step 1 Creating Report Options The Report Options Function on Excel Ribbon Tools is the key to creating Report options. When Click on the Report Options tab, the Window pop-out empty like this You can then Fill in the spaces as shown below Title = Start Date Value = 1/1/2022 Tooltip = Select a start date of your choice. For the look-up, I’ll show you how to use that later. use the same procedure to fill in the End date also, and your result should look like this. Once this is done, a new sheet named Options is created automatically. Step 2 Tags For creating a Report Options page We have a required and optional tag to create our report options Required tags Creating a Report Options window requires the use of three tags which include, Option, Titles, Values. Optional tags Two additional tags can be used to add functionality to Report Options windows which are, Lookup and Tooltip. Required tags Option The Option must always be in column A, any row that has OPTION in column A has become an option in the report options. Titles This creates a title for the options. it is always in the columns next to “Option” Values You can create value for your option here. Optional tags Lookup This allows users to select a value or set of values from a drop-down list, this can be done using the Jet function “NL(lookup)“. Tooltip This provides you with useful information when you hover over an option in the Report Options window. Step 3 How to use your NL(Lookup) NL("Lookup") function is to simply pull a list of values from the database. For example, if a list of customer numbers ("No.") from the "Customer" table is desired then the function would look something like this: Step 4 Linking your report Option to your report. Since we created our report options, I’ll show you how to link the Report Options page to your report. we will link this report option to the “Customer Sales Report“ we’ve created. As we already know, the report option helps us narrow down our report. now, will filter our report with the date range and customer no from the report options. The filter will be applied on “D5“ and “H5“. customer no. the filter will be applied to NL(rows). “D5“ while the Date range will be applied to “H5“. When we applied this filter, we will be able to run our report for a particular date and also for customer no. we only want the report to show. The amount here is our main concern, we want the amount to display concerning time. so, we apply our date filter here. Click on the Jet formula you use to create the Amount, when the report wizard pops out, scroll down to the filter. under the filer field, search for the date and select the posting date as shown below. Click on the next box on the right side “Filter“. Once you do that, go to Nested Jet Function and click on the drop-down. On the drop-down, click on the NP function. Once you click on the NP, a new window will pop out where we fill in the required information. On “What”- fill in “Date filter“ On the start date, click the insert report option at the top. This will open the report options where you can now select the start date. select that and then click add. do the same for the End date. When you’re done adding the date range, click back and then ok. Adding customer No. as a filter to NL(rows) D5. Click on the NL function to get access to the report wizard. then fill in as shown below. To fill in the filter box here, just click on the “Insert Report Options“ and select No. Step 5 Running our report. Anytime you click on the run icon, the report option pops out for you to select or fill in how you want to apply your filter to the report. As shown here, I run the report for (First January) 1/1/2022 to 12/1/2022 (First December) and I also want the report to show for just some particular customers, so I select from the lookup just 4 No. when I run my report, it will display for that particular period and just the 4 customers. Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Jet Reports Trial Balance Report A trial balance report is a financial report that lists all of the accounts in a company's general ledger along with their ending balances for a specific period of time, typically a month or a quarter. The purpose of the trial balance report is to ensure that the total debits equal the total credits in the ledger, which is an indication that the accounting entries are balanced. The trial balance report can help businesses to identify errors in their accounting records, such as incorrect journal entries or transposition errors, which can cause the debits and credits to be out of balance. If there are any discrepancies in the report, the company can investigate and make adjustments as needed to ensure the accuracy of its financial records. Join On Point Academy to view all content Join the academy → Log in → The trial balance report is often used as the basis for preparing financial statements such as the income statement, balance sheet, and cash flow statement. It is a valuable tool for internal financial management and is also required by external auditors during an audit of a company's financial statements. Let’s see how to do this with Jet Reports. Open your excel report and go to the Jet tab. Navigate to the Report options and fill in the following. Under Title Period start Period End Under value Your expected starting date Your expected end date 4. After you close the window, a new page titled 'Options' will appear automatically. it should look like this… 5. GO back to Sheet1, navigate to tools and click on NL. Fill in as shown to create the Period Start date on sheet 1 page. Note: To fill in for Period Start ‘filter’, navigate to Nested Jet Function, and click on 'Insert NP'. 6. To fill in for Start Date and End date, click on Insert Report Options. A new window will appear. a. Click on Period Start, then add to fill in for Start Date b. Click on Period End, then add to fill in for End Date 7. Navigate to tools again, click on NL function, and fill in as shown to create Period End Date on sheet 1 page 8. Your date might appear in form of numbers, no worries. Just right-click on it, and go to Format cells-change from General to Date. 9. Your report is ready to run. When you run your Report, you should have something like this. Now, Let’s use the monthly report we just build into our sales report. We want to use our monthly report in our G/L Account Balance, which we will display with G/L account number Name Account type, and Balance. We get our Account number, name, and account type using the NL and NF function. but the balance is received using our G/L function. Now we retrieve our No., Name, and account type using the NF function. we fill in as shown, and change the field for each no, name, and account type Now, let’s get the balance from the GL function and fill it in as shown below. we will want our monthly movement to display by the names of month and year. We use our Excel formula to create that. Where we have “jan-2022“, we fill in the formula shown above in it. this allow Excel to automatically display all the months in the given year. if you run your report, you should have something like this. If you encounter any problems, please contact habib@onpointserv.com Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Microsoft Business Central Adding Custom Layouts and e-mail addresses to individual Customer Cards Customers in Microsoft Dynamics Business Central (BC) can be assigned with distinct document layouts (e.g. sales invoice, sales credit memo) and e-mail body templates. Multiple e-mail accounts can be linked to these layouts. Join On Point Academy to view all content Join the academy → Log in → Step-by-step guide Navigate to the Customer Card and from the task bar click on Navigate > Customer > Document Layouts Under the Usage column select the document you want to configure from the drop-down, e.g Invoice Complete the setup as follows: Report ID : 1306 (Report Caption will be updated automatically by the system) Custom Layout Description: Select the customer layout report pre-set in the system Sent to Email : insert e-mail accounts of contacts you want to send documents to, separated by a semicolon ; Use for Email Body : Tick this box if you want to attach a pre-set e-mail body Email Body Layout Description : Select the customer layout report Press close Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Microsoft Business Central How to set and use No. Series in Business Central This document is a quick reference to how to set and use No. Series in Business Central. No. Series provides automatic numbering of invoices, customers, vendors, etc. Each document is given a number automatically. Join On Point Academy to view all content Join the academy → Log in → No. Series Navigate to No. Series -insert No. Series to the search box and choose the related link Click on +New In the new line insert Code and Description and click on the blank field in starting Fill in Starting No., Ending No. and Warning No.: Starting No. = Number of the first invoice Ending No. = Number of the last invoice Warning No. = Business Central will remind you that almost all numbers are uses and it’s necessary to create a new No. Series However, there are more options on how to create the No. series. For example, you can use different No. Series for every year. To do so fill in starting date, e.g.: To set up where the No. Series is supposed to be used navigate to Sales & Receivable Setup (use SearchBox) and in Number Series Tab choose particular No. Series for the related document. Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Jet Reports FA Monthly Movement by Type Aim The idea of this report is to have a breakdown of the movement related to the Fixed asset, grouped by: FA Posting Type Asset No Period Join On Point Academy to view all content Join the academy → Log in → Options Available The user may choose from the following options: Period Start Period End Period Definition - i.e. whether it’s daily, weekly, monthly, quarterly, yearly gaps Any filter on the Fixed Asset No. Notes This report is extremely useful when analysing the life-cycle for any fixed asset. We may bear in mind that some assets are more complex having multiple acquisitions and different depreciations besides possible revaluations from time to time. FA Monthly Movement by type .xlsx Download XLSX • 41KB Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →

Microsoft Business Central How To Perform Year-End Closing As the end of the fiscal year approaches for most firms, let's have a look at the essential setup and procedures for conducting year-end activities in Microsoft Dynamics 365 Business Central. Let's use the "Accounting Periods" and the "Close Income Statement" approach on Business Central. Join On Point Academy to view all content Join the academy → Log in → METHOD 1 (Accounting Periods) STEP 1 : Click on the search icon at the top right and type in “ACCOUNTING PERIODS”. A list drops down then select the related link. STEP 2: Among the options available select "PROCESS", a list drops down then select "CLOSE YEAR" STEP 3: Select "YES" to confirm the action. This closes the immediate open accounting year and then activates the next accounting year. METHOD 2 (Close Income Statement ) STEP 1 : Click on the search icon at the top right and type in “Close income statement”. A list drops down then select the related link. STEP 2: An Options window pops up, then fill in the spaces provided as required and select OK and the process will proceed. OPTIONS Fiscal Year Ending Date: This date is used to establish the closure date, and it will automatically populate with the last date of the most recently concluded fiscal year. General Journal Template: Select the section where the entries will be placed. (Usually, General is selected). General Journal Batch: Choose the general journal batch that contains the entries. You have the option of creating a new batch if necessary. Document No: The batch job automatically fills this section with the next available number from the No. Series for the journal batch. You may also manually fill up this area. Retained Earnings: Choose the retained earnings preferred to end the fiscal year. Post to Retained Earnings: Specify whether the resultant entries are posted as Balance or Details. Posting Description: Specify the description that will appear with the posting. (The default text is Close Income Statement). Business Unit Code: Select this option if an entry should be created for each Business Unit Code. If the firm is a consolidated company and this option is chosen, separate entries are produced for each business. Dimensions: Select the dimension boxes being used to close the income statement. For each dimension utilized in the GL account, an entry will be made. Inventory Period Closed: Specify the inventory period with an ending date equal to or before the last date of the accounting period to be closed. NB: After running the Close Income Statement batch job, access the journal indicated in the batch job, examine the entries, and publish them. STEP 3: Click on the search icon at the top right and type in “GENERAL JOURNAL”. A list drops down then select the related link. STEP 4: Choose the Batch Name that was used to close the income statement then select OK . NOTE: The posting date for the entries in the chosen batch will start with the letter "C". On completion, an entry is posted to each income statement account, causing its balance to become zero, and the year's outcome is assigned to the balance sheet. Get trained the On Point way ​ From self paced online courses to private training sessions . Become industry ready. View courses →