Tutorial: Create a flexible P&L in Excel 2010/2013

Introduction

In the first part of this series we looked at how some of the changes in Excel 2010 and 2013 have given us new tools for working with accounting data. We showed how the inclusion of the PowerPivot ‘Data model’ within Excel 2013 itself could replace the use of Excel Lookup formulae to give easier, more automatic and potentially more reliable results. For inspiration, we used a six year old article by David Carter on working with accounting data in Excel entitled: Tutorial: Create a P&L from monthly balances with a pivot table.

Having processed our data through the Excel 2013 data model, we ended up with our profit and loss report. This time we’ll look at the various things we can do with our PivotTable in the latest versions of Excel...

Further reading and tutorials

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments
buttercup books's picture

Excel    1 thanks

buttercup books | | Permalink

Hi Simon

I really really like these excel tutorials,

but I wish they were all in one need book,  I'm still on 2007 ,

can we  a book, beyond the basics, with pivot tables and sage links,

I don't have time to trawl AW looking for the bits I like, give me a book to work through

 

jean

John Stokdyk's picture

You wish has been fulfilled

John Stokdyk | | Permalink

Thanks for the comment and kind words, Jean. I think I can help with a few suggestions. First, if you look at the "Tags" section just below the end of the article and click tutorial or Simon Hurst, you should find some quite useful tutorial selections. 

There's also a tag for Excel 2007 material and PivotTables, while I should also recommend David Carter's tutorials, which go into Sage reporting in some detail.

But if it's a book you want (and I know what you mean about having a separate document to work through rather than working from a screen), Simon has already answered your request with Maximising the Impact of Accounting & Financial Spreadsheets for Finance Users, available from FSN for £24.99.

If that title doesn't grab you, try Advanced Excel Techniques for Accountants, an ebook available from Simon's website.

That lot should fuel your Excel learning for some time - and don't forget to get our monthly Excel Zone bulletin, as we'll be featuring more tutorials from Simon in the months to come.

shurst's picture

Thanks

shurst | | Permalink

Thanks Jean and John. Just back from a couple of days holiday, so apologies for not responding earlier. I'm glad you like the tutorials Jean, and hope you continue to enjoy them in the coming months. Thanks for the mention of the book (the title wasn't my choice - honest!) and the other resources John.

Getpivotdata - DATES

darryl_bright@c... | | Permalink

Hi

 

I am trying to insert a dynamic cell reference into my getpivotdata formula however I keep getting an error, it works find on a text field however when I try an link it to a date, I get an error. 

 

As per the example below, I have text referenced date highlighted in bold.

 

=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]","[ACTULAS].[Month].&[2013-02-01T00:00:00]","[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")

 

When I try and link it to a cell reference it errors out.  The cell reference is a date format   e.g.  01/01/2013.

 

=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]","[ACTULAS].[Month].&["&$E3&"]","[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")

 

Any idea how to comate this other than changing every text filed manually. 

 

Thanks

Darryl

 

shurst's picture

GetPivotData dates

shurst | | Permalink

Hi Darryl

Assuming that E3 contains a valid date, would the following work?

=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]",E3,"[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")

GetPivotData dates

darryl_bright@c... | | Permalink

Hi Simon,

Thanks for getting back to me, however ",E3," didn't work.  There must be a way of doing it in powerpivots, it works fine in with standard pivot tables.

Any other idea's?

 

Thanks

Darryl

 

shurst's picture

PowerPivot

shurst | | Permalink

Ah, sorry - I hadn't realised you were using PowerPivot - that would explain the strange syntax. I found this on a forum:

=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]","[ACTULAS].[Month].&["&text(e3,yyyy-mm-ddThh:mm:ss")&"]","[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")

I'm sure I found an easier way a few months ago but can't work it out now!

 

PowerPivot

darryl_bright@c... | | Permalink

 

It's still not working, I get an error message stating 'The formula you typed contains an error'. Usually this is as a result of a missing comma "," or bracket ")" in a standard formula however when it comes to power pivot syntax I am totally lost, does not seem intuitive.

Thanks anyway Simon!

 

 

shurst's picture

Missed a double-quote    1 thanks

shurst | | Permalink

Sorry Darryl, my fault - I should have checked more carefully, I think I missed a double-quote in the text function:

=GETPIVOTDATA("[Measures].[Sum of SALES VALUE]",PIVOT!$B$11,"[ACTULAS].[Month]","[ACTULAS].[Month].&["&text(e3,"yyyy-mm-ddThh:mm:ss")&"]","[MAPPING].[ACCOUNT]","[MAPPING].[ACCOUNT].&["&$A5&"]")

Thank you Simon!

darryl_bright@c... | | Permalink

Thank you! Thank you! Thank you!!

It's working perfectly now, you are a legend! :)

 

 

shurst's picture

Excellent

shurst | | Permalink

Really glad it worked, and thanks for letting me know, and for your comments.

But.

I should have got it right first time.

Excel 2010 and Cube

MariaMaria | | Permalink

Really enjoy reading your articles! Very useful. However, I cannot understand how to insert a "total" line under revenue, as shown in your "Cubes" section. I am using Excel 2010 with PowerPivot add-in. Thank you. 

shurst's picture

Total rows

shurst | | Permalink

I'm sorry MariaMaria - I've only just spotted your question.

Thanks for the kind comments.

Once the PivotTable has been converted to Cube formulae the cells behave like any other Excel cells, rather than having the restrictions of a PivotTable, so you can insert rows and columns as you wish and put whatever formulae you like in them - such as a simple SUM() to create a total.

I hope it works for you.