Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Pivot Tip 26 - How to analyse sales by financial year. By David Carter

by
19th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

ExcelZone's Five Minute Tips aim to develop your skill at using Excel pivot tables. To test these tips for yourself, try them out on your copy of the pivot_practice.xls database.

When you use the Group function to summarise dates, Excel groups by calendar year. But organisations need to report by financial year, and that will usually be different.

To report by financial year you will need to hold the financial year and period number in your source database. Go into the source database and sort it into DATE order. Insert a blank column either side and head one column FINYR, the other MTHNO.

Go down each column entering the financial year and period number on each line. In the FINYR column type the year when the financial year ends. In the MTHNO column type a combination of period number and month in the format 01_MAY (if your financial year begins in May.)

Practice Session – analysing sales by financial year
In the previous sessions you have been asked to save the practice database pivot_practice.xls on your own machine. If you HAVE done this, open it now. [If you HAVEN’T made your own copy, you should do so now. Follow the instructions at the end of this article.]

Go into the SHORT master database containing the 27 invoice records.

There is no reference here to the financial year. Suppose that this company’s financial year runs from 1 May to 30 April. Create two new fields to hold the financial year and period number as follows:

Create new FINYR and MTHNO columns
Click onto cell B10 in the DATE column. Then click your AZ icon at the top. The records are sorted into date order.

Insert a blank column to the right of the DATE field. Type the column heading MTHNO.

Insert a blank column to the left of the DATE field. Type the column heading FINYR. The first five column headings should now read INVNO, FINYR, DATE, MTHNO, ACC.

Since our financial year runs from May to April, April will be the last month of year 2004/5, while May will be the first month of year 2005/6. Therefore:

In cell B2 of the FINYR column, type 2005. Copy it down to B10. [Tip: Highlight B2 and press Ctrl+C to copy, then hold down the Shift key and press the Down Arrow key repeatedly to highlight the cells down to B10. Then press Ctrl+D to copy down.]

In B11 type 2006. Copy it down to the bottom row.

In D2 of the MTHNO column, type 12_APR. Copy this down to B10 for all the April dates. Against the May dates type 01_MAY, against the June dates type 02_JUN and so on.

Create a pivot table showing sales by financial year
Now go into the Pivot Table Layout screen and create a new table showing sales by financial year. To do so, place the following data elements in the appropriate layout zones:
ROW = CUSTNAME
COLUMN = FINYR and MTHNO
DATA = NET

The sales are now split over the 2 financial years. Column B shows sales for April, and column C total sales for year 2004/5. Columns D and E show sales for May and June, and column F total sales for year 2005/6.

The Grand Total in column G is irrelevant. To remove it, right click on the pivot table to get the Table Options menu and untick the Grand Totals of Rows box at top left.

To compare this year with last year
Often you will wish to compare this month’s figures against the same month last year. There is not enough data here to make this a meaningful exercise, but you would do it by clicking on the grey MTHNO box in C3, and ticking the down arrow. A list of months appears:

  • To compare June this year with June last year, untick all the months except 02_JUN.
  • To compare cumulative May to June this year with May to June last year, untick all the months except 01_MAY and 02_JUN. The cumulatives for both years will now be shown in the 2005 Total and 2006 Total columns.

END OF SESSION. Do not Save.

Appendix: How to create the pivot worksheet
You need to have your own copy of the pivot_practice.xls workbook; otherwise you will have to begin each session by re-creating the pivot table from scratch.

The first articles in the series Tip 1 and Tip 2 and Tip 3 explained how to create the pivot worksheet. If you are new to pivot tables, it would be best for you to go through these first articles in full. If you are already familiar with pivot tables, create the pivot worksheet now as follows:

Download the Pivot_Practice.xls database

Create the following pivot table:

PRODUCT in the ROW area
CUSTNAME in the PAGE area
NET in the DATA area
DATE in the COLUMN area

OK Finish.

Now Group the dates by month as follows:

Right click on the DATE field button in C3. Select: Group and Show Details–Group.

Months is highlighted as the default. Scroll down to the bottom of the list under Quarters and highlight Years as well. OK.

The pivot table now displays the transactions grouped by month – Apr, May, Jun.

The first line of the pivot table should read:
Compaq 1000mb 2300 2300

Change the name of the worksheet from sheet1 to pivot (double left click on the worksheet tab at the bottom)

There should now be two worksheets – pivot and master.

Now save the Pivot Practice workbook on your own machine. Future articles in this series will use the pivot worksheet as their starting point.

Related material in ExcelZone
For more than four years, David Carter and AccountingWEB members have built up a massive stockpile of material on Excel and pivot tables. To delve more deeply into the subject, see:

  • David Carter's Five Minute Pivot Table Tips - index
  • Want to learn about Excel pivot tables? Start here
  • The Excel Compendium - Pivot Tables
  • 100 Best Time-Saving Ways to Use Microsoft Office by Simon Hurst

    Subscribe to the ExcelZone newswire
    To keep up with David Carter's Five Minute tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

  • Tags:

    Replies (0)

    Please login or register to join the discussion.

    There are currently no replies, be the first to post a reply.