## How to use Excel XLOOKUP Function – 7 tips for Reporting and Analysis using XLOOKUP

Course: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales \$, profit \$ and profit margin %, and how to explain performance vs budget and prior periods, click on the link for a detailed video course (at a special price). You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates. https://bit.ly/3xjMR8t

Do not forget to subscribe to my Youtube Channel

Learn how to use Excel XLOOKUP and what this function can do for you. In this video, I will share 7 practical ways in which you can use the new XLOOKUP function which serves as a perfect replacement for both VLOOKUP and XLOOKUP.

We start with a real life work situation where you have to respond with data requests quickly. The first situation being where the General Manager is asking for Sales for specific customers within 5 minutes. Not only do you provide him the information requested within 5 minutes, but you go above and beyond to provide additional information that he may be interested in. In the second example, your Manager is asking you to complete and send the sales commission file based on annual sales and commission plan. You use XLOOKUP Match mode functionality to quickly respond to the Manager with the completed sales commission report.

In the third example, you have been asked by the Purchasing Manager to provide him help with pulling the most recent purchase price from a long list of materials and purchase history. You use the Search Mode argument of the XLOOKUP function to reverse the order of the data and provide by material SKU, the most recent purchase price and earn bragging rights.

In the fourth example, the external auditors have asked you to provide information related to customers, in a layout which is the opposite of how your sales data is set up. Knowing well that XLOOKUP can replace horizontal lookup or HLOOKUP, you quickly pull the information in the requested format and respond to auditor’s request.

Finally I share a tip that I have personally been using since the VLOOKUP days which would help you avoid XLOOKUP error, when by mistake the data ranges (lookup array and return array) are not aligned. This tip actually saves a lot of time as well and has been one of my favorite tips.

Connect with me:

https://www.instagram.com/learnaccountingfinance

https://www.learnaccountingfinance.com

Thank You!

Learn Accounting Finance

## How to Analyse and Present financial results – Avoid these four common mistakes!

When I first started analyzing and presenting financial results to my organization’s leadership, I had no idea what I was doing wrong. I just felt something was not OK. I was not making a good impression. I would leave the meetings with this empty feeling. It was a little embarrassing, and painful at times. Can you relate?

This guilty feeling helped me, however, as I decided to learn and improve my financial analysis and presentation skills. That was a great decision, as things only got better from there. In this article, I will share with you four key mistakes that many beginner and often advanced finance professionals make when presenting financial results in management meetings. I will provide solutions as well, so that you can rectify these mistakes immediately, and become an indispensable and successful finance professional that you deserve to be.

#1: Presenting Numbers, Not telling Stories

The number 1 mistake that most beginner financial analysts, accountants, controllers and sometimes even CFOs make is present the results in the form of tables and numbers, year over variances and percentages. It is as if they are reading the results on the slides.

What is wrong with that, you ask? The problem with this approach is that it is much harder to comprehend. People loose focus quickly of what you are saying. They will not remember or even understand much of what you are saying. Even though, you may have provided some good information, your audience will not be able to absorb it. May be that is why, accountants and finance professionals are sometimes seen as boring. We do not leave an impression by sharing financial results in this format. Most of what we say during such meetings is forgotten before the meeting is over. The biggest problem with this approach is that the key message (if we had any) is lost in the details.

Note: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales \$, profit \$ and profit margin %, and how to explain performance vs budget and prior periods, click here for a detailed video course (at a special price for readers of this post) showing exactly how this is done. You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates.

Solution:

Presenting results is your opportunity to shine. Each slide of your presentation should have a key message. That key message should also be the title of your slide. Your overall presentation should be a collection of key messages that can be narrated in the form of a story. If you structure your presentation this way, presenting it will be much easy. You will always know what to say and where you are going. It will be a coherent and thoughtful analysis with key takeaways for your audience. They will love what you have to say and remember it. Some may even get inspired!

#2: Too many Ideas in one Place

A lot depends upon the setting you are in. Is it a short, 15 minutes financial results update?, or a longer, more detailed business review? However, when we prepare our presentation slides, often we do not think carefully about what goes into each slide. Often, we lump multiple ideas or topics into one slide. This results in confusion, and our message is too broad. We seem to be jumping from one topic to another and then back quite frequently.

Solution:

To avoid this situation, always create one slide for each topic. If more than one topic or issues are to be discussed, create separate slides for each one of them. Your audience finds it difficult to comprehend more than 5 objects on one slide any way. This includes a combination of pictures, tables and text. So, try to limit the number of objects to 5.

Do not include long textual paragraphs and definitions. Use minimal amount of animations, only to emphasize key points. For example, if your key message in a slide is mainly about Sales price increase and its impact on profit margin, you are better off not including numbers for Selling, general and admin expenses. If you are comparing prior year vs current year, you do not need to include budget or plan numbers in that slide.

If you have a lot to cover but not much time available, rather than skimming through each slide quickly, skip a few slides, focus on hammering home the key message on a lesser number of slides instead of going through all of your material.

#3: Data Issues and related Disclaimers:

One of the most common problem that we accountants and finance professionals face is the availability of  reliable data that can be used confidently to analyse and explain results. While your colleagues and leadership may appreciate that, the truth is they still expect you to come up with conclusive explanations regardless. I struggled with this one a lot initially. I thought that letting everyone know where the data has holes will help everyone understand that they cannot completely rely on the information. And therefore, should not make business decisions solely on the basis of the available analysis. The problem with this is that they still have to make decisions. They want you to provide them some conclusive guidance and you are not helping them by telling them that they cannot rely on the information you provided. It is like watching a movie without an ending. It can get pretty frustrating.

Solution:

Although there is no easy fix for this problem, the first step is to acknowledge the challenge your audience is facing. The next step is to identify what elements of the financial information can be relied upon to a reasonable degree. You can always look at the data available from multiple angles and validate so that you can conclude to a reasonable level of certainty.

You have to take this as a challenge, and deal with the uncertainty. This is a key area where accounting and finance professionals can and should add value. Anybody can look at reports and summaries. Where we add value, is that despite problems with data, we can validate the information and make suggestions. This comes with time and experience, but you have to start by taking on this challenge. Trust me! you will discover that you have not challenged yourself enough in the past. Even the incomplete and crappy data gives you some valuable insights that you can share with your leadership. You only get better as you practice this skill.

#4: Most credible source of financial results and analysis

This represents the other side of the balance for finance professionals, and is also a key job requirement. If you are in the accounting or finance profession, specially if you are involved in presenting financial results, this is an absolute must for you. When companies need new products or improvements in existing products, they look to engineers. When they need talented people to hire, they look to HR. Similarly, when they need accurate financial information and advice, they look to finance professionals like You and I to guide them and solve their problems.  Do you provide accurate reports, analysis and commentary?

Your organization’s IT system may be generating automated reports for managers. Do the managers ask you if they have doubts on numbers in a system generated report? If it is not you, then may be, you have not established the credibility that is an absolute must.

Solution:

If you are not there yet, do not worry. You really need to upgrade your skills such as financial analysis, variance analysis, data analysis as well as Microsoft Excel. You need to develop a thorough and clear understanding of cost and management accounting. Once you get better with these skills, your quality of work improves. Quality reports and analysis combined with your presentation skills such as those discussed in points #1 and #2 will make you an exceptional Finance professional who becomes the Go To person for management.

By the way, if you are serious with honing all of these skills, I recommend that you connect with me. I will be sharing with you, much of what I learnt about detailed financial analysis, presenting financial results, profitability and variance analysis (sales and cost of sales) and lots of advanced Excel. In addition, I will also be sharing my experience on how to find and successfully interview for the right job, how to get promoted quickly and become indispensable finance resource for any organization you work for. Make sure to sign up by clicking here for my email list and receive career transforming tips and information.

Conclusion

When done correctly,  analyzing financial results and business performance, and then presenting it can be an extremely rewarding experience. Especially, when you know you are adding value and making a difference. Your suggestions will be well respected. You will influence small and large business decisions. As you get better with this skill, you will find colleagues and business leaders from other functions relying more and more on you. You will be invited to more meetings and formal and informal discussions. Your advice will be sought. Even, your boss will value you more. Trust me! this is a great feeling.