Accounting and Finance training and advice to help you land your accounting and finance job, and then not only keep that job, but get promoted quickly!
Learn how to completely automate (100%) your reports with Microsoft Excel Pivot Tables and some simple tweaks/tricks. If you prepare frequent reports, such as daily sales, or daily margin report, you do not have to create and update the reports every day at all. You do not even need advanced business intelligence tools. You can keep it simple, and nice and easy by automating you existing Pivot Table.
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
For fully automated reporting using a Pivot Table, you need a few things to be automated within the Pivot Table: 1) Pivot Table Data Range (to expand or shrink with changes in Source Data) 2) Calculations within the Pivot Table (such as calculation of Gross profit and profit Margin) 3) The Sorting Order for data in the Pivot Table 4) Pivot Table format to stay consistent with changes in source data 5) The Pivot Table should be able to Auto Refresh when new data is updated. This video covers all of these topics, while we work together with source data in the file provided.
Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance.
Calculating prices in a grocery store can seem like a daunting task, but it’s actually quite simple once you understand the basic principles. In this blog post, we’ll walk you through the step-by-step process of how to calculate prices in a grocery store, so you can confidently set prices for your products and maximize your profit.
Step 1: Determine your cost
The first step in calculating prices in a grocery store is to determine the cost of each product. This includes the cost of purchasing the product from a supplier, as well as any additional costs associated with storing, handling, and selling the product (such as packaging and labor).
To determine the cost of a product, you’ll need to consider the following factors:
Purchase price: This is the price you pay to purchase the product from a supplier.
Handling and storage costs: These are costs associated with handling and storing the product, such as the cost of packaging, labeling, and refrigeration.
Labor costs: These are costs associated with the labor required to handle, store, and sell the product, such as the wages of employees who stock shelves or check out customers.
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
Step 2: Determine your desired margin (or markup)
The next step in calculating prices in a grocery store is to determine your desired margin or markup, or the amount of profit you want to make on each product. The desired markup or margin will depend on a variety of factors, including your overall business goals, your competition, and market conditions.
You’ll need to consider the following factors:
Business goals: Consider your overall business goals and how much profit you want to make on each product.
Competition: Research the prices of similar products at other grocery stores in your area, and determine how much margin you need to remain competitive.
Market conditions: Consider factors such as the current state of the economy and the demand for the product when determining your desired margin.
Step 3: Calculate the selling price
Once you’ve determined your cost and desired profit, you need to calculate the selling price. This can be a little tricky as you need to make a distinction between markup and margin. They are both not the same. Markup is a percentage on the cost, while margin is a percentage on the selling price. For example, If your product costs $100, and the required markup is 25%, then the selling price will be calculated as:
Note that 25% markup is not the same as 25% margin. So, its important to understand what you are really looking for. If you would like to learn more about the difference between markup and margin, and how to convert one into the other, e.g. 10% margin = __% Margin? or 10% margin = __% markup, watch this video in which, I explain in detail the concepts and the calculations.
By using this formula, you can easily calculate the selling price of each product in your grocery store, ensuring that you’re making the desired profit while also remaining competitive with other stores in the area.
In conclusion, calculating prices in a grocery store is a simple process that requires you to consider the cost of each product, your desired margin, and market conditions. By following these steps, you can confidently set prices for your products and maximize your profit.
Hope you find the information in the videos helpful. If you like to learn about accounting, financial analysis and controller ship, and learn from videos that help you directly in doing your job, subscribe to my channel. Lets stay connected at #learnaccountingfinance.
Here is a list of videos that I recommend you watch in sequence to gain a quick basic understanding of Accounting. This should give you enough information to understand what is accounting? the accounting principles, rules of debit and credit and how to create financial statements such as 1) Income statement, 2) balance sheet, and 3) Cash flow statements.
Sit back and enjoy the course! Let me know if you would like to know more, or something is not clear enough.
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
Hope you find the information in the videos helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the videos, I would love if you could LIKE themand leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance.
A career as a financial analyst can be rewarding and challenging, offering the opportunity to analyze and interpret financial data and make informed recommendations to help businesses and organizations make sound financial decisions. But is a financial analyst career right for you? Here are some things to consider:
Do you have an aptitude for math and finance? Financial analysis involves working with numbers and financial data, so it’s important to have strong analytical and problem-solving skills. If you have a natural aptitude for math and finance, or if you have a degree in a related field such as economics or business, you may be well-suited for a career as a financial analyst.
Do you enjoy working with data? Financial analysts spend much of their time analyzing financial data, so it’s important to enjoy working with numbers and finding patterns and trends. If you enjoy working with data and are comfortable using tools such as spreadsheets and financial software, you may be well-suited for a career as a financial analyst.
Do you have strong communication skills? Financial analysts need to be able to clearly and effectively communicate their findings and recommendations to both technical and non-technical audiences. If you have strong communication skills and are comfortable presenting information in a clear and concise manner, you may be well-suited for a career as a financial analyst.
Do you have a strong attention to detail? Accuracy is crucial in finance, and financial analysts need to have a strong attention to detail in order to avoid errors and ensure that financial information is reliable. Attention to detail is important in finance, as even small mistakes can have significant consequences. If you have a high level of attention to detail and can work accurately and efficiently under pressure, you may be well-suited for a career as a financial analyst.
Do you have strong time management skills? Financial analysts often have to juggle multiple tasks and meet tight deadlines, so the ability to manage time effectively is important. If you have strong time management skills and are able to prioritize tasks and work efficiently, you may be well-suited for a career as a financial analyst.
Do you have an interest in business and finance? A career as a financial analyst can be rewarding for those who are interested in understanding how businesses and organizations operate and make financial decisions. If you have an interest in business and finance and enjoy staying up-to-date on economic and market trends, you may be well-suited for a career as a financial analyst.
Are you willing to continuously learn and grow? The finance industry is constantly evolving, and financial analysts need to be able to adapt to new technologies and techniques. If you are open to learning and are willing to continuously improve your skills, you may be well-suited for a career as a financial analyst.
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
If you have a strong aptitude for math and finance, enjoy working with data, have strong communication and attention to detail skills, and are interested in business and finance, a career as a financial analyst may be a good fit for you. By continually learning and growing, financial analysts can position themselves for success in an ever-changing industry.
If you would like to learn these skills, including help with Microsoft Excel, accounting and financial analysis, make sure you connect with us through one of the following channels.
As a finance professional, it’s important to have a strong foundation of knowledge and skills to be able to succeed in your career. Here are the 7 most important skills that every finance professional should have, along with examples of how these skills can be applied in real life:
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
Financial analysis: The ability to analyze and interpret financial data is essential for finance professionals. This includes being able to understand and use financial statements, perform ratio analysis, and create financial projections. Financial analysis is an important tool for understanding a company’s financial performance and making informed decisions about its future. For example, a financial analyst might use financial statements to identify trends in a company’s revenue and expenses (what will the revenue and expenses look like a month, year, three years from now), and use this information to make recommendations for cost-cutting measures or to identify opportunities for growth.
Communication: Finance professionals need to be able to effectively communicate financial information to both technical and non-technical audiences. Communication is important for any professional field, but is a core skill required for good financial analysts. This includes being able to explain complex financial concepts in a clear and concise manner and using various forms of communication, such as written reports, presentations, and graphs (One example of such graph is a waterfall chart). Strong communication skills are essential for finance professionals, as they often need to present financial information to stakeholders such as clients, investors, and management. For example, a financial advisor might use a presentation to explain the benefits of different investment options to a client, while a CFO might use a written report to update the board of directors on the company’s financial performance.
Leadership: Finance professionals often work in leadership roles and need to be able to motivate and manage teams. This includes the ability to delegate tasks, provide constructive feedback, and resolve conflicts. Leadership skills are important for finance professionals as they may be responsible for leading teams of financial professionals and ensuring that projects are completed efficiently and effectively. For example, a finance manager might lead a team of financial analysts and be responsible for assigning tasks, setting goals, and providing guidance to team members.
Problem-solving: Finance professionals need to be able to identify problems and find solutions. This includes being able to analyze data, develop and evaluate options, and make informed decisions. Problem-solving skills are essential for finance professionals, as they are often faced with complex financial challenges that require creative and analytical thinking. For example, a financial analyst might be faced with the challenge of finding ways to increase profitability for a company. To solve this problem, the analyst might analyze the company’s financial data, identify areas where costs can be reduced, and present recommendations to management.
Attention to detail: Accuracy is crucial in finance, and finance professionals need to have a strong attention to detail in order to avoid errors and ensure that financial information is reliable. Attention to detail is important in finance, as even small mistakes can have significant consequences. For example, an accountant might need to review a large number of transactions to ensure that they are properly recorded and that the financial statements accurately reflect the company’s financial position.
Time management: Finance professionals often have to juggle multiple tasks and deadlines, so the ability to manage time effectively is important. This includes being able to prioritize tasks and work efficiently. Time management skills are essential for finance professionals, as they may have to handle multiple projects and meet tight deadlines. For example, a financial planner might have to prepare financial plans for several clients at the same time, and need to be able to manage their time effectively to ensure that all clients receive the attention they need.
Adaptability: The finance industry is constantly evolving, and finance professionals need to be able to adapt to new technologies and techniques. This includes being open to learning and continuously improving skills. Adaptability is important for finance professionals, as they need to be able to keep up with changes in the industry and adopt new tools and approaches as needed. For example, a financial analyst might need to learn new software to analyze financial data more efficiently, or a financial advisor might need to stay up-to-date on new investment products and strategies,
By developing these skills, finance professionals can position themselves for success in their careers. Whether you’re just starting out in finance or are an experienced professional, it’s always a good idea to work on improving and expanding your skillset. By continually learning and growing, finance professionals can stay ahead of the curve and succeed in an ever-changing industry.
If you would like to learn these skills, including help with Microsoft Excel, accounting and financial analysis, make sure you connect with us through one of the following channels.
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
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.
In this video, I explain vertical analysis of income statement (in Excel) by comparing the real income statements of Coca Cola and Pepsi.
What is vertical income statement analysis?
Vertical analysis is a top to bottom analysis of income statement where amounts for all line items in the income statement are converted to a percentage of a base amount (usually total revenue or Net Sales). This analysis is done to see the relative size of each type of income or expense with respect to the revenue (base).
When is Vertical Analysis used?
Vertical analysis of income statement can be used when trying to understand the size and significance of the components of income statement (hence reflected in percentage), and also to compare financial statements of difference companies either in the same or different industries, which may or may not be of similar size or revenue base. Converting amounts into percentage gives a particularly good idea for comparison, as you will see in the video above. Although Pepsi’s total revenue is more than double Coca Cola’s revenue, you can still compare the two income statements and analyze them to make informed decisions.
Vertical Analysis vs Horizontal Analysis
While vertical analysis looks at the components of income statements and their relative size, horizontal analysis looks at changes in the financial statements over a period of time. It is usually reflected in terms of year over year growth or decline.
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://www.udemy.com/course/learn-financial-analysis-of-variances-in-profit-and-sales/?couponCode=YOUTUBE10
Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance.
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
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
Learn how to sort pivot table data from largest to smallest values and vice versa for multiple columns including Customer names, months and Values. In this video, I will explain the basics and advanced uses of Sorting Pivot Table Data.
Sort Pivot Table values from Largest to Smallest:
We start with very simple sorting of Customer Names based on the largest value of Sales amount. Then we start adding other fields in the pivot table and see how it impacts the Sorting.
Sort Pivot Table Manually
E.g. we add the countries in the Pivot Table report, and manually change the order resulting in Manual Sorting of the Data.
Sort Pivot Tables by Subtotals and Grand totals
We can also sort Pivot Table based on subtotal values or Grand Total values. Just click on the Cell for the field you are looking to sort and then click the sort ascending or Descending button for this to work.
Checking Pivot Table Sort Settings
We can also check current Pivot Table Sort settings by right clicking on any field and Value and then clicking on Sort à More Sort Options. This shows exactly how the current sorting is setup in the Pivot Table.
Sort Pivot Table by Months and Dates
We also take a look at how we can sort dates or months in Pivot Tables. By default, if the dates or months are entered in correct format, the Pivot Table will sort them based on Oldest to Newest. However, we can change that setting and Sort based on values for each date as well as sort from Newest to Oldest date or month.
Issues with Sorting, Errors in Sorting data
Sometimes, you may not be able to sort values when there are no subtotals or Grand Totals to click and Sort on. In this case, you can still sort Pivot tables data even though you do not add the subtotals. You can do this by right clicking on the Field you want to sort by values, and then click on Sort, and then More Options. Then Choose Ascending or Descending Sort option. Finally, select the value field you want to sort based on, from the Drop down selection available.
Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance.
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
Apply Calculated Field and Calculated Items to your Pivot Tables and automate calculations for reporting and analysis. This is such a time saver for Excel users and something every Excel user should learn. In this advanced tutorial of pivot tables, you will learn the use and examples of apply Pivot Table Calculated Field and Calculated Items options. Here is the link to the video tutorial:
What are Pivot Table Calculated Fields? Calculated Fields are calculations within the Excel Pivot Tables based on the fields or columns already available in the data. You can create many calculations inside the pivot table so that when you slice and dice or update the pivot table, the calculations also get updated with it.
What are Calculated Items? Calculated items represent Pivot table calculations of the data inside each field. So for example, if you have Calendar month, Sales and Standard margin as three fields available in the data set. Any calculation at the filed level such as std margin % calculated from Sales and Std margin field is done by clicking on “Calculated Fields” on the Fields, Items and Sets button of the Pivot Table Analyze menu. However, any calculations at the level of the data itself, for example calendar month Feb minus Jan is done by clicking on the Calculate Item selection of the same menu.
Why is Calculated Item grayed out or turned off sometimes? It matters what field or cell you have selected on the Pivot Table as it impacts turning On or Off of the Calculated Item feature. If you have selected a Values cell, then Calculated Field will be available but Calculated Item will be greyed out. In order to make the Calculate Item option available you will need to select one of the Cells from the Rows section of the Pivot Table.
Can we use Formulas when Calculating Fields and Items? Yes. this is exactly what the Calculated Fields and Calculated Items are for. However, the application of formulas is not entirely the same as the formulas in regular excel cells. Some formulas do not work at all and some formulas do not work the same way as they would work in a regular spreadsheet cell.
How to use IF statement in Calculated Fields and Items You can use the IF Statement with the syntax as normal in the Calculated Fields and Items calculation. Look at the video where I shared how I used the IF function to calculate sales commission %, and then nested AND function with the IF function to calculate a conditional bonus calculation.
What does List Formula do in a Pivot Table Analyze Menu? The list Formula option creates a separate spreadsheet and shows all the Calculated Fields and Calculated items used in a pivot table. This helps you to see any time the calculated fields which are not originating from the raw data set. This is also very helpful when you are reviewing a pivot table file that was prepared by someone else who included calculated items and calculated fields in the file.
I think using advanced pivot table functions such as Calculated Fields and Calculated Items help the Excel users significantly in being productive and creating and refreshing reports quickly with automated calculations. This saves time and avoids chances of mistakes when formulas are maintained outside of the pivot table calculations.
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
Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance.
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
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
How to lookup and have an exact match between two columns where the spellings are different with all kinds of spelling mistakes, spaces and characters? In this video, I will show how you can use Excel fuzzy lookup tool to solve this time consuming problem.
Often you find yourself comparing two reports or data sets where the names or text strings are similar but do not match exactly. As a human, you can tell that they are the same, but you cannot look up data using the exact match features of Vlookup or Xlookup. In this case, the Fuzzy lookup tool comes in handy. In this video, I show you how to install and use the Add-in to lookup Company names, or Customer Names from two separate data sources, so that you dont have to spend time manually finding the names and then copying and pasting the values from one table to the other.
I have used this method when trying to compare sales reports from two different sources which have the customer names, one report has the freight costs by customer and the other report has the sales by customer. When I try to bring the two together by using a vlookup or an Xlookup, I am unable to do that because the names do not exactly match. In addition, the differences in spelling do not follow a set pattern, so I cannot even use wildcard matches or be creative about using additional Excel formulae to help with the lookup. In this case technology becomes really useful. My recommendation is to use the Fuzzy lookup tool or add-in which can be downloaded from the Microsoft website and installed in Excel. This is such a time saver. I will show in the video the setup of the tables to use the fuzzy logic add-in as well as the use of the “Similarity threshold” to help lookup majority of the company names in the example. By the way, the sample data includes Revenues and Net Profits for 25 of US top Fortune 500 companies by revenue. You can also download the sample Excel from the link provided. Hope this video helps!
Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance #lookupnames #spellingdifference
Would you like to work with me to get one on one training, or to solve your specific workplace challenges, book your 15 minute consultation here: https://calendly.com/learnaf
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.
How do you calculate selling price of a product with cost and margin, while taking into account discount and rebates offered to the customer. If you have tried this calculation of a selling price of a product which requires not only the calculation of markup as you only have margin %, but also complicating the calculation are the discount and rebate factors, you probably ended up seeing a Circular Reference error in Excel. This is because the Selling price needs to be calculated, but discount and rebate / commission are also calculated based on the selling price. In this video, I will show you how computers and Excel can help.
Here is the Video …
Microsoft Excel comes with a built in Solver add-in that does the calculation for you. The Solver Add-in is an advanced version of the Goal Seek function and can even consider constraints to the calculation. All you need to do is to set up the product cost calculation template in a way that the Solver Add-in can do the calculation for you. If you had to do it on your own, you would have to use the trial and error method. But with the Excel Solver Add-in, you don’t have to worry, as it is very quick and easy to calculate selling price of a product so that you can achieve the target margin as well as consider discount and rebates and commissions (if applicable). So, if you are provided with, say discount of 1% and rebates of 3% that the customer would usually be able to claim, you can consider this in the product pricing decisions, and with the help of this calculation, you can calculate exactly the price you need to sell for, in order to still achieve the margin % that was originally required.
Practical accounting and finance training to get the job, keep the job and get promoted quickly! 