Archive

Archive for November, 2009

This Blog sponsored by our Learning Center

November 19, 2009 Leave a comment

The reason we are able to bring you this is our Learning Center. Visit The Learning Center NOW to download full length tutorials from 1 – 3 hours. Just add a class to your cart, make your payment and our website shoots you an e-mail with a link to download what you need to watch your video class.

Visit our QuickBooks Blog also for great free QuickBooks help and information: QuickBooks Nerd

How to track your business in excel part 4

November 18, 2009 Leave a comment

The template for this web cast is available in our Learning Center for $11

This is the final chapter in the 4 part saga (with one bonus in part 2).

Here is the link to watch this web cast (Video):

http://nerdenterprises.na6.acrobat.com/p74667017/

In part 3 we were not able to get into pivot tables too deeply so I wanted to devote another web cast to that part.

We show you how to use the transaction data to run pivot tables so that you can quickly and concisely report on the data to give you really meaningful information.

If you’ve missed the first 3 parts, be sure and get caught up here:

How to track your business in excel – part 1 – http://nerdenterprises.na6.acrobat.com/p30305366/

How to track your business in excel – part 2 – http://nerdenterprises.na6.acrobat.com/p20159112/

How to track your business in excel – part 2 – Bonus – http://nerdenterprises.na6.acrobat.com/p44179111/

How to track your business in excel – part 3 – http://nerdenterprises.na6.acrobat.com/p84834671/

And this one again

How to track your business in excel – part 4 – http://nerdenterprises.na6.acrobat.com/p74667017/

The pivot tables are the key to this when it is all said and done because this is how you accomplish what any accounting system should be designed to do – take what you’ve put into it and give you something valuable that you can take out of it. This is what any system’s reporting feature is all about. You won’t be able to do a well formatted P&L and Balance sheet with this, but you can get close by running the pivot table on the transactions and then adding the destination account to the filter, then filter for all Balance sheet accounts. Keep in mind it won’t be perfect. If, for example you have payments to “Accounts Payable” then this will show up on your report, but does not represent what is “Due” in accounts payable. Instead it represents what has been “Paid” on accounts payable. So you may want to exclude that one. You can enter bills in your transaction register and then you would have an accurate payable, because the bills would go in as a negative (credit) to Accounts Payable and the payments on accounts payable would be entered as a positive number. Then you could pivot on accounts payable to get an accurate balance.

Next week we’re back on to QuickBooks so visit us there at http://www.qiuckbooksnerd.com

How to track your business in excel part 3

November 11, 2009 Leave a comment

For a better quality version of the video web cast:

http://nerdenterprises.na6.acrobat.com/p84834671/

Visit our Excel blog for more: http://www.msexcelguru.com

Here we really complete the picture for you by showing you how to enter and then report on your financial transactions.

We show you how to set up the hyperlinks so that if you expand upon this concept you will be able to incorporate any new items in the ‘Home’ or ‘Menu’ Tab.

We review the setup of a list and data validation to update the way the source account works, because that account really needs to represent sources of payments and deposits. This also makes it possible to rely on the “Destination” accounts for tracking all income, expenses, asset purchases and payments on liabilities.

Now that the template is set up we can show you how to enter your transaction data:

This is what the template looks like with everything entered. If there was a deposit to be entered the Source account would be the bank account where the money was deposited and the destination account would be the appropriate income account. This is not set up to show invoices to and payments on accounts receivable. This can be done but would have to be done on a private consult because it would be very involved.

Once we have data in we can begin to report on it. We show you in the last 3 minutes of the web cast how to create a pivot table on this data reporting on expense by category and then expanding upon that by showing you how to further report the expenses daily based on the date of the transaction.

Figure 1 – Pivot Table – Daily Expenses click for larger image

We will post a bonus web cast going into more detail on these pivot tables and how you can use them to report quickly on the information in the sales and transactions.

How to Track Your Business in MS Excel Part 2

November 11, 2009 1 comment

Click here to watch the webcast:

http://nerdenterprises.na6.acrobat.com/p20159112/

Bonus Web Cast (Too Long For YouTube) setting up your accounts:

http://nerdenterprises.na6.acrobat.com/p44179111/

MS Excel Info: http://www.msexcelguru.com (you’re here already)

QuickBooks Info: http://www.quickbooksnerd.com

In this part we show you how to set up your transaction register and show you how to set up your lists and then validate your Columns in your transaction register so you have drop-downs to choose from in your transaction register. There are two columns for accounts. In accounting terms it’s because every transaction needs a debit and a credit. In lay-person’s terms you need a source account and a destination account as shown here:

The source account is essentially how I paid for something (ie Bank account or Credit Card) or if I received money, which bank account that money went into (which bank account).

The destination account is where the money went or what the money went to pay for (ie office supplies, equipment, etc..)

We are going to show you how to set up lists in this web cast.

After that you will learn how to use those lists to populate drop-downs in the Excel Template.

It is important wherever possible to limit data entry to information that is populated in pre-set lists to ensure consistency. Eventually we want to run filters and pivot tables on the data and if there are variations on spellings this will take extra time in order to clean up for proper and accurate reporting.

In the next web cast we will begin to create some pivot tables and you will be able to see how powerful this is in terms of being able to use a simple template like this one created in Microsoft Excel to track your financial accounting information.

For a bonus web cast showing you how to set up your “Account List” click here:

http://nerdenterprises.na6.acrobat.com/p44179111/

This is too long for YouTube! Sorry