HomeHome SearchSearch MenuMenu Our productsOur products

Three secrets of how PivotTables can save you time and make your job easier

by , 01 September 2015
PivotTables is a useful tool in any accountant's toolbox.

They help you keep control over the large amount of financial data you record, manage, understand and report on.

With PivotTables you can sort, count, total or give the average amounts of financial data. And double-click on any value in the report to instantly drill down to the underlying detailed data.

Read on as I let you in on three secrets of how PivotTables can save you time and make your job easier.

*********************Recommended for you*********************

Attend our PivotTable Training Course where we'll answer these seven questions and more:

  1. What's the quickest way to inspect statements for errors e.g. phone records, supplier statements, e-toll accounts?
  2. How do I use Excel to change my data to into business reports?
  3. Can I use Excel to identify trends in sales, leads, orders and deliveries?
  4. Is there an easier way to change my management reports by adding new elements, re-arranging layouts, drilling down into sales or costs?
  5. How do I summarise orders by supplier, client, or product (or all of them) in Excel?
  6. Can I use Excel to import my sales, accounting, payroll or any other data directly into a reporting system with pivots?
  7. Does Excel let me sum, sort, average and more without formulas?

 Click here to book your seat for the PivotTable training course.


Three secrets you can use PivotTables to save you time and money

With PivotTables you can summarize a lot of data into a couple of lines, get the information you need accurately and efficiently. You'll reduce a five-hour job to five minutes.

Use PivotTables, in your daily accounting practices, to:

·         Draft your management accounts;
·         Itemise your inventory; and
·         Draft your fixed asset register.

Draft your management accounts

Mary is a financial manager at ABC Furnishers. At the end of April, she had to prepare management accounts for the owners of ABC, to show how well or badly the business is doing.
She decides to use a PivotTable because the company expanded operations into Africa. The new division needs to be included in the management accounts.

She knows the PivotTable reports will include the new division; all she needs to do is add the data that relates to it, to the bottom of the existing data on the other divisions.

Using PivotTables allows her to prepare summaries, charts on margins, relative contributions to overall profit and trends. Another bonus is that the charts based on these PivotTables will include the new division results.

Itemise your inventory

Selwyn is the sales manager at Elco Manufacturers. He realises that his staff create a new sale item on the accounting system, when they can't find the item they want to invoice.
So this led to duplication of several inventory items in the system. This makes a stock count very difficult, and integrating the stock module with the general ledger impossible.

He imports a data extract from the accounting package into Excel that lists all inventory items. Using PivotTables he prepares a report to count the number of times each of the inventory items appear in the list. He also filters the report to exclude items where the inventory item count is one, as he only wants to focus on the duplicated items.

*********************Just Released*********************

Get to grips with Excel's PivotTables to turn your raw data into meaningful reports in minutes, with no formulas!

Five new skills you'll take home with you:

1. How to resolve debtors, creditors, credit card and bank accounts with PivotTables.
2. How to summarise payroll data with three clicks.
3. How to use Excel to analyse sales listings to identify trends.
4. How to manage project costs and allow for detailed reporting and analysis in Excel.
5. How to use Excel to track orders and deliveries in detail or at a 'big picture' level.

Become a financial data analysis and reporting guru at this one-day PivotTable Training Course.

Click here to book for the PivotTable training course online and save R1 000.


Draft your fixed asset register  

Kim is the finance manager for ATH Tool Hire, with five branches nationally. Due to rapid expansion in the past year, it has become necessary to loan items between branches to ensure they meet the client's needs. These inter-branch transfers happen daily and usually for short periods of time.

Kim realises that to keep the depreciation charge per branch accurate. She would need to post 15 depreciation journals per asset during the course of one month.

So she created a excel report that list every assets in use, nationally.

In this report the fields capture the day the asset leaves its current branch and the new name of the branch.
She creates a Pivot report based on this data. Grouping assets into appropriate categories for depreciation calculations and allocating the depreciation to the proper branches.

Once a month she only needs to refresh the PivotTables with two clicks of her mouse. Select the new month from a drop-down list. Her report gives her the exact amount of depreciation to post per asset, to each branch.

P.S. Make sure you get you book and pay before 4 September 2015 to take advantage of the early-bird offer and save R1 000.


Related articles

Related articles

Watch And Learn

Related Products


Recommended for You 

  Quick Tax Solutions for Busy Taxpayers – 35 tax answers at a glance

Here are all the most interesting, thought-provoking and common tax questions
asked by our subscribers over the last tax year – everything from A to Z!

To download Quick Tax Solutions for Busy Taxpayers – 35 tax answers at a glance click here now >>>
  Employees always sick? How to stop it today

Make sure you develop a leave policy to regulate sick leave in your company.

BONUS! You'll find an example of the leave policy and procedure in this report.

To download Employees always sick? How to stop it today click here now >>>
  Absenteeism: Little known ways to reduce absenteeism

This FREE e-report will tell you how you can reduce absenteeism in your workplace while avoiding the CCMA and without infringing your employees' labour rights.

To download Absenteeism: Little known ways to reduce absenteeism click here now >>>
  7 Health & safety strategies to save you thousands

Don't let a health and safety incident cost you one more cent. Implement these seven
strategies in your company today.

To download 7 Health & safety strategies to save you thousands click here now >>>