HomeHome SearchSearch MenuMenu Our productsOur products

How to create an excel pivot table to manage all your sales data in less than five minutes

by , 12 April 2013
Selwyn is the sales manager at Elco Manufacturers. He realises his sales staff create new sales items on the accounting system when they can't quickly find the item to invoice. This has led to duplications of inventory items in the system. As you can imagine this makes stock count a nightmare.


Selwyn is the sales manager at Elco Manufacturers. He realises his sales staff create new sales items on the accounting system when they can't quickly find the item to invoice. This has led to duplications of inventory items in the system. As you can imagine this makes stock count a nightmare.
 
He can't even integrate the stock module with the general ledger because of the mess. But, there was one thing he could do to fix the problem. He could use pivot tables. These helped him prepare a report to count the number of times each of the inventory items appear in the list. He could also filter the report to exclude items where the inventory item count is one. But, in this case he only wants to focus on the duplicated items.
 
If you've been faced with the same situation I'm sure you'll agree that a pivot table would be your saving grace. Here's how you can use a pivot table, in less than five minutes, to manage your sales data summarisation and analysis.
 
+++Do you know why Gail doesn't spend R10 000 a month on bookkeeping fees?+++

Gail is a GP who's just opened her own private practice. Because she's trying to save money on admin costs, she is doing her books herself. She thinks: 'How hard can it be?'

The answer: 'It's very hard unless you've got the right tools at your fingertips!' The Practical Accountancy Loose Leaf is just one of those tools.

With everything from checklists about how to prepare for an audit, to detailed explanations about financial statements, the Practical Accountancy Loose Leaf is your one-stop accountancy resource!

 +++

How to create an excel pivot table to manage all your sales data in less than five minutes

Step 1: Choose your raw data. Select the data you want to put into your pivot table. Say you want to analyse sales for a certain period. You'll use the raw sales data as input data.

Step 2: Click anywhere on the raw sales data. Click 'insert menu' on the top of your screen.

Step 3: Click 'file' and then 'pivot table'.
 
pivot table image 1
 

+++Limited offer+++

Get professional tax advice from our top tax consultants for just R0.89 an hour


+++

Step 4: In the 'create pivot table' dialog pop-up box, Excel will automatically select the whole data source. Put your new pivot table on a new worksheet and use this sheet as your report page. Then click 'ok'.
 

pivot table image 2



Step 5: In the 'pivot table field list' select the fields to add to your report and drag and drop into the areas where you want them displayed
Well done, you've created a pivot table!


pivot table image 3


For examples and more detail on how to create pivot tables, turn to page 003 of Excel: Pivot tables chapter E 02 in the Practical Accountancy Loose leaf. Don't own a copy? Click here to order now.



Until next time,
Philip Rosenberg

P.S :  'How is the profit or loss determined on an asset that is sold after say 4 years e.g.: motor vehicle purchased 2008 for 350 000 and sold in 2012 for 285 000?' For answers to this accounting question, and other urgent accounting and tax questions, check out our Accounting and Tax club website. It's free!


Related articles




Related articles



Related Products



Comments
0 comments


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 >>>