Reconcile PayPal Lightning Fast with a Pivot Table

Reconciling PayPal accounts has been a nightmare for accountants all over the world and for a long time. But PayPal isn’t going anywhere, so I’ve made it one of my missions to figure out how to reconcile PayPal lightening fast. The answer? Pivot Tables.

The first rule to reconcile PayPal lightning fast is that you never use the banking app in QuickBooks Online for PayPal. It is horrible. It does not download the activity consistent with how it actually shows up in PayPal. Particularly on the sales side. You get gross amounts and fees in separate transactions, and that is not how it shows up on the PayPal statement. If you only pay for things with PayPal then it’s fine.

The second rule to reconcile PayPal lightning fast is that you need to download the monthly statement (found in reports) in both PDF and CSV format. The PDF is only to verify and enter the ending balance and statement date. The csv file is what you’ll use to reconcile the account.

Here are the steps to reconcile PayPal lightning fast

  • One – Once you have the PayPal monthly statement in csv format is to save it as an Excel Workbook (see video).
  • Two – convert the data to a table (see video).
  • Three – format your dates and amounts (see video).
  • Four – create a pivot table (see video).
  • Five – create your money in tab (see video).
  • Six – create your money out tab (see video).

Once you have completed these steps you will have everything you need to reconcile PayPal Lightning fast. The video will show you what this all looks like.

Create Revenue Projections For Your Accounting Or Bookkeeping Practice

Accounting professionals can offer a wide range of services. Bookkeeping, Tax Prep, CFO services, and other advisory services.You will find that it is incredibly useful to sit down and spec out those services that you offer. Look at what the monthly fees are for each service / level. Then look at how many current clients you have in each. Finally plot out how many new clients you want to get each month for each service and at each level. In short you will want to create revenue projections for your accounting or bookkeeping practice.

I find it therapeutic actually!

Do you ever feel like everything is out of control?

I do. This process helps me every time.

When you create revenue projections for your accounting or bookkeeping practice it gives you a reality check on things.

Are you going in the right direction?

Are you realistic in your projections?

How close are you coming to what you projected or thought?

Was your last projection just a thought? As in, “I want to gross $500,000 this year?”

Newsflash kid! That’s not a projection.

This is:

The above is a revenue projection that is easy to create and manage.

Each month you want to look at these numbers and see how your actual compares to projected. If you do this monthly, you will see if and where you are way off. Expenses are easy to manage for accounting and bookkeeping practices. Most of your costs are fixed. It’s the revenues (or lack thereof) that kills us.

There is another benefit / reason why you should create revenue projections for your accounting or bookkeeping practice.

Focus.

If you take my suggestions of not just doing this, but reviewing it every month?

Scratch that.

How about having it up on your screen all day every day.

Wall St. Analysts have their stock tickers and charts on screen all day long.

This is your company’s stock ticker. Keep it open all day every day. Every time you get a new client, track that here.
This will keep your focus on getting new clients, and keep you out of those endless political arguments on Facebook! This is also why I keep mine in Google Sheets. It is one of the default browser tabs that opens when I launch Chrome.

Create Revenue Projections for your Real Estate Business

Real Estate Professionals can have a hard time managing their finances. It’s feast or famine in many cases. You work long and hard on a listing, and the time between commission can be stressful. You will find it useful to sketch out the listings you have, and what the likely outcome is in terms of the commissions you’ll earn. Having this list in front of you will also help you prioritize and stay focused. In short you will want to create revenue projections for your real estate business.

I find it therapeutic actually!

Do you ever feel like everything is out of control?

I do. This process helps me every time.

When you create revenue projections for your real estate business it gives you a reality check on things.

Are you going in the right direction? Are you realistic in your projections? How close are you coming to your projections?

Was your last projection just a thought? As in, “I want to gross $1,000,000 this year?”

Newsflash kid! That’s not a projection.

This is:

The above is a revenue projection that is easy to create and manage.

Each month you want to look at these numbers and see how your actual compares to projected. If you do this monthly, you will see if and where you are way off. Expenses are easy to manage for real estate businesses. Most of your costs are fixed, or easy to calculate (eg percentage of your commission). It’s the revenues (or lack thereof) that kills us.

There is another benefit / reason why you should create revenue projections for your real estate business.

Focus.

If you take my suggestions of not just doing this, but reviewing it every month?

Scratch that.

How about having it up on your screen all day every day?

Wall St. Analysts have their stock tickers and charts on screen all day long.

This is your company’s stock ticker. Keep it open all day every day. Every time you get a new client, track that here.

Keeping your projections up all day will keep your focus on getting new clients, and keep you out of those endless political arguments on Facebook! I keep mine in Google Sheets. It is one of the default browser tabs that opens when I launch Chrome.

Create Revenue Projections for your eCommerce Business

Managing the accounting for eCommerce businesses can be very challenging. There are lots of moving parts (literally). You will find it useful to sketch out at least the top products you have, how many you expect to sell, and what they sell for. Having this list in front of you will also help you prioritize and stay focused. In short you will want to create revenue projections for your eCommerce business.

I find it therapeutic actually!

Do you ever feel like everything is out of control?

I do. This process helps me every time.

When you create revenue projections for your eCommerce business it gives you a reality check on things. Are you going in the right direction? Are you realistic in your projections? Are you coming anywhere close to what you projected or thought?

Was your last projection just a thought? As in, “I want to gross $1,000,000 this year?”

Newsflash kid! That’s not a projection.

This is:

The above is a revenue projection that is easy to create and manage.
Each month you want to look at these numbers and see how your actual compares to projected. If you do this monthly, you will see if and where you are way off. Expenses are also complex to manage for eCommerce businesses. That’s another conversation for another day / pots.

There is another benefit / reason why you should create revenue projections for your eCommerce business.

Focus.

If you take my suggestions of not just doing this, but reviewing it every month?

Scratch that.

How about having it up on your screen all day every day.

Wall St. Analysts have their stock tickers and charts on screen all day long.

This is your company’s stock ticker. Keep it open all day every day. Every time you get a new client, track that here.

Looking at this all day will keep your focus on getting new clients, and keep you out of those endless political arguments on Facebook! I keep mine in Google Sheets so it’s easy to have up all day. My projections is one of the default browser tabs that opens when I launch Chrome.

Create a Customer and Price Matrix in Microsoft Excel

Create a Customer and Price Matrix in Microsoft Excel

A long time ago in a galaxy far far away, a small business owner who had a problem he wanted me to solve reached out to me. George had inherited a meat route with different prices for different customers, but for the same products. He needed a solution that could look up a price by both customer AND product. The solution? I created a customer and price matrix in Microsoft Excel.

And if you think this stuff is really advanced (and it is) then sign up and take my Excel courses right here on the site!

The key to the customer and price matrix in Microsoft Excel is the Index formula.

The index formula lets you do exactly what we need to do here. Look up a result in a matrix based on references to a row and a column.

The formula is actually pretty easy.

You set up your matrix of prices with customers in rows, and products in columns.

Then write the formula to grab a price based on a particular row and column:

=Index([matrix range],[Row #],[Column #])

The challenge is setting up a customer and price matrix in Microsoft Excel that isn’t based on a “particular” row and column, but rather based on something dynamic.

Something Excel can look up.

In order to accomplish this, we need to surround that matrix with the customers and products. Then in between we need the numbers that mark the row and column numbers of the matrix.

Next we write a nested formula. The index formula will contain a vlookup, and an hlookup based on a customer and product we’ve selected from a drop down.

This way we can select the customer and product from their respective lists. Then we can look up the price based on any combination of customer and product that we like.

The video will walk you through this step by step.

Important!
Before you start the video, open up a spreadsheet. Pause the video periodically so you can follow along by creating what I am teaching you here.

And remember! If you think this stuff is really advanced (and it is) then sign up and take my Excel courses right here on the site!