All articles with 'accounting' Tag

We can calculate any Finance & Accounting KPI values using Excel easily. In this article, I  am sharing the top 10 accounting KPI calculations. These are… Topics ? Net Profit Margin Net Profit Margin = Net Profit / Sales Positive profit margin indicates business is profitable while negative indicates the business is in loss. ? […]

Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.

In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.

Why Power Query?

Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.

For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.

Last week, we learned how to use formulas to reconcile (match) transactions in Excel. Today, lets take a look at even faster and simpler way to do this:

Using Pivot Tables

Here is a short video explaining the technique and why it works. See it below

Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.

Now, every month you get a transaction report.

And you want to know which numbers are matching up.

i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,

• $427.77 on 1st April
• $152.88 on 2nd April
• $291.69 on 2nd April

Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).

This is part 2 of 6 on Profit & Loss Reporting using Excel, written by Yogesh Data sheet structure for Preparing P&L using Pivot Tables Preparing Pivot Table P&L using Data sheet Adding Calculated Fields to Pivot Table P&L Exploring Pivot Table P&L Reports Quarterly and Half yearly Profit Loss Reports in Excel Budget V/s […]

With this post we are starting a new series on how to do basic accounting in Microsoft Excel. In this and next 5 posts, we will aim to setup Profit & Loss account reporting for multi-location retail company.

During this series we will learn how to make P&L reports on various criteria with just few clicks.

Many users find it difficult to manage their P&L reporting for Multi Location organization.

We will be using Pivot Tables for our reporting purpose and will take example of a Retails chain with multiple locations divided into various regions.

On a dark night, I am sitting in my room with QT textwondering whether to learn T-Test or go out for a Tea Taste Tomorrow (or rather today) is the last day of mids. Got QT and OM exams. Sofar finished majority of QT. will go through OM once I comeback from T break. Actually […]

When i was a kid, i was fascinated by numbers. So much so that I used to top all the maths courses. Later during the engineering first year when I failed to add 2 matrices in the final exam I felt like suiciding. It was such a shame for me. All the penchant for numbers […]

Well, you have seen them all. Cost accounting, management accounting, HR accounting, this accounting, that accounting. But Accounting of Exams??? Well, I guess there is no such concept in existence till now. Hmm, so something original, you must be thinking. But even if you are remotely aware of the blog title you will remember that […]

Note: This post assumes minimum knowledge of cost accounting terminology. Also anything that has ‘account’ in it written by an engineer would be highly sarcastic. You are warned! Scene 1 (Location: our very own parliament house, Time: just after a loksabha winter session)Speaker: (Sigh!) another round of mindless damage to this house. Last year during […]

After coming here the meaning of weekend blurred a lot. We have seen some crazy weeks in both the terms. Day before yesterday was different experience altogether. Unlike weekends for most people living in India, certain sample of hazaar people celebrate it in a different way. Their weekend starts on Saturday afternoon and ends on […]

Classes will begin for the term 2 in another 40 mins. 🙂 well, today we have fin, accounting and bto2 sessions. Should be an interesting day for the one who has prepapred for the classes. Thanks largely to the vacation, i got lazy and havent completed the reading assignments for BTO2. It seems i am […]

Done with my registration for term 2 today morning. Got the reading material for the term too. Well, the material is simply intimidating. One of the books actually looked like a dictionary. Dont know howmuch I can comprehend. Broadly the out line of the term is, Monday 4th October – classes begin Thursday 20th October […]

I am here for the Term 2 of the PGDM. Before I start blabbering anything about the t2 I think a review of the term one might be a good read. Like everyother soul on this hill I also came with infinite dreams and unlimited($) hopes to this place. Well, looking back at the term […]

Somedays, life seems highly miserable and hopeless. Well, not for cribbing but for the fun part of the life under thousand tonnes of pressure read on. Start the day at 12:00 by reading some come-dicks accounting case and trying to learn and appreciate their taxation policies and lease mechanisms, not to mention about the amortization […]