Recently I got asked for help by a colleague. She had helped a friend set up a spreadsheet to track his income and expenditure on his properties. Of course he had started off with one and that was easy to track but he had been very successful and was now up to 20 and found tracking his income and expenditure really tricky in the format she had used. Different properties were on different sheets so it was nearly impossible to get an overview. He was coming to the end of the year and looking to see how to make it easier.
I gave this some thought. My thinking was the following:
- How can I make this relatively easy to use?
- How can I make it so that he can easily add new properties/costs etc.
- How can I set it up so that he can get a quick snapshot of what his income and taxable liability should be?
So I decided to go the pivot table route…but worked to keep it as easy as possible. So I am doing this over 2 blog posts. In this first blog post, I will show you how to set up your data so that it’s pivot table friendly and in the next blog post (to be published on 18/11/2013) I will show you how to create a pivot table from it that gives you your summary view.
- Create a data entry sheet (called. ..surprisingly enough…Data Entry) . I’ve added the following headings:
- Property Name: Enter a name you can identify the property by
- Amount – Full: (this is the actual amount paid or received). If it’s a cost enter it as a minus e.g. -25
- Amount – Taxable (Not all of all expenses are allowed for tax purposes, this is where you can enter the amount that IS tax-deductible). If it’s a cost, enter it as a minus e.g. -20
- Date: Just enter date of transaction (and if you are not sure of the date, just enter the first day of the month)
- Category: – Broadly speaking, a transaction will be income or expense. Rent is income (assuming you are receiving it and mortgage payments, repairs etc are expenses)
- Description: This is for yourself later so that you know what it relates to.
Data Entry guidelines:
Assuming every property will receive a monthly rent, you will need to enter 12 rows for this e.g. an entry for every month. Assign it the category Income. Description is optional
For Costs: Enter the property name, the full Amount (with a minus), taxable amount e.g. if you have paid €100 for repairs but you can only deduct €80 for tax purposes . Enter- €100 under Amount-Full and -80 under Amount-Taxable. Enter the date and assign it a category e.g. Cost.
You can add a further description in Description or you can add a comment by right clicking on the cell and going to Insert Comment
When you have added in some sample data – convert this to a table (Insert – Table). This will make our Property Portfolio Data Entry much more pivot table friendly. We will be covering this in the next blog post.
Have fun with this. The beauty of this is that it’s a template that can be adapted to quite a few situations…
As always if you have any comments/suggestions, please feel free to add them 🙂
Excel Products for Sale
Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.
Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!) that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.
Xtreme Pivot Tables course
This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.
He also has a useful Pivot Table webinar…
This is also a useful chart helper.
Debra Dalgleish – Contextures.com
Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.
Excel UserForms for Data Entry
The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.
The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function
Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only