My Secret Weapon for Bank Recons

It seems that balancing a checkbook is a personal task most folks dread doing (although here’s 7 reasons to do so), which means it’s likely not much different for businesses. I, on the other hand, would say that bank reconciliations are my favorite thing! To me there’s nothing better than a big ol’ detail-ridden, large-volume-of-transaction laden recon. I’m sure part of my joy comes from the process that I’ve nailed down in my numerous years of corporate accounting and today I’d like to share my secret weapon: a simple excel template.

This template will look very different from what you may be used to in QuickBooks, and will probably look completely foreign if you’re new to financial reconciliations in general. I will break it out piece by piece so that you, too, can find joy – or at least less pain – in reconciling your own bank.

Template for Reference:

Jamie’s simple excel template – CLICK TO ENLARGE
  • STEP 1: First, we start by entering our ending bank balance from the statement into item (A), which in this case is $1,100 on 3/31/17. Then we enter the ending general ledger balance as of the same date into item (B), which is $800.
  • STEP 2: With this template, notice that at the very bottom at item (H) it will indicate how much of a variance between the bank and general ledger we have left to identify. After completing step 1 above, the variance will show $300. ($1,100 – $800).
  • STEP 3: The next step is to start matching transactions between the bank statement and the general ledger. After you’ve matched all that you can, you may be left with any of the three following types of discrepancies:

Uncleared checks: these are checks you have recorded on the general ledger that have not cleared the bank. Enter these under item (C). These will be negative amounts since the bank balance will be reduced once they clear.

Deposits in transit: these are deposits made and recorded in the general ledger but have not posted to the bank yet. Enter these under item (D). These will be positive amounts since the bank balance will increase once they post.

Items to be posted: these are items on your bank statement that did not get posted in the general ledger by the cut-off date. Enter these under item (E). These can be positive or negative amounts, depending on what the item is (it should match what it shows on the bank statement).  See examples in the reference template above. Be sure to post these! Otherwise they will stay on the reconciliation until you do.

  • As you enter your identified discrepancies, your unidentified variance will change. Once it reaches zero, your reconciliation is complete. Warning: it’s still important to confirm that items of the same amount are correctly matched. If you have recurring or multiple transactions of the same amount, simply matching amounts could lead to a discrepancy later on that will be more difficult to research.
  • Also at this point, your “adjusted” bank and general ledger balances will be the same. This indicates matching balances after all discrepancies have been resolved.

I hope that this simple template will get you on your way to less-stressful bank reconciling. If you would like further training or assistance creating a more complex template, I would be happy to help! Just shoot me an email at jamie@renoaccountingsolutions.com to get started.

Find out other reasons I use Excel in my previous blog “The Power of Excel.”

Why I Won’t Do Your Taxes

One of the most commonly asked questions I get from both potential clients and colleges alike is “Do you do taxes?” and the common reaction is bewilderment when I give them my answer: “No.”  Usually there’s a follow-up question asking why, since what the heck else would a CPA be good for? Well, when I got licensed as a CPA back when I lived in California, my intent never was to file taxes for people. Could it be profitable? Of course. Is the service in high demand? Yes, definitely around this time of year. So, what’s my deal?

Photo Credit

My deal is that taxes are not enjoyable for me. Come on….I know most of you can relate to that! Taxes are tedious and always changing and I don’t care to keep up with the ever-evolving, never-ending tax code. Plus, if I put time and energy into doing tasks I don’t enjoy, my time and energy will be diverted away from the tasks that I do enjoy.

 

I recently picked up “Book Yourself Solid” by Michael Port, at the recommendation of my friend Kristin Stith (another local rock-start entrepreneur!), and it has truly invigorated my motivation to serve my clients well. One of the first steps in this book is to start working with only your ideal clients and not let dud clients suck away your enthusiasm potential. By allowing only ideal clients into your line-up, you are able to work at your best. I feel the same way about which particular projects I work on and filing taxes is my equivalent “dud client.”

Bogging myself down with undesirable projects will likely cause me to dread the work and dread communication with the client, even if I genuinely like the client. If I focus on the tasks I’m passionate about, I will perform at my best, which benefits both myself and my clients. That is why you will never find me offering to do someone’s taxes, other than my own. If you need someone to take a good, hard look at your accounting and business processes, you can bet that I’ll be doing my best to find solutions that will increase efficiency and effectiveness and I’ll be doing it with enthusiasm because it’s what I truly love to do!

Rest assured though, that I do have enough working knowledge surrounding taxes that I’m able to identify areas of opportunity and areas of potential risk as I’m working with my clients. I am always on the lookout while completing other projects.

Like I mentioned before, the tax code is endless! According to Forbes.com the code has grown from a whopping 27 pages in 1913 to OVER 4 MILLION WORDS (9,000 pages), with 4,600 changes from 2001-2012. WOW. That’s a lot to keep up on! If you’d like to see more humorous reasons that add to why I don’t do taxes, check out the Forbes article “20 Really Stupid Things In The U.S. Tax Code.”

My Gritty Start-Up

In my last blog I talked about grit and my grittiest semester as an undergrad. Needless to say, I survived that semester and ever since then have been on a crazy adventure in the accounting profession. The latest chapter of my adventure consists of my second attempt to be my own boss. Yes, SECOND attempt. In November of last year I transitioned from working as a full-time corporate accountant to being an independent accountant. It’s been a long, arduous process but I am finally starting to see my dreams come into fruition. That was not the case the first time around.

In 2014 I was working as an assistant controller for a commercial property manager. I loved coming into a new company and using my niche for lean accounting and business process improvement and whipping the books into tip-top shape. Things were no different at this place, but soon enough I would be faced with a huge career-changing dilemma. I discovered an on-going process that was illegal and needed to be rectified. I felt especially committed to exposing it because it dealt with payroll and labor laws. When I met with the Controller about it, he shrugged it off and said that’s just how things were done there. So I proceeded next to the CFO and I got the same dismissive attitude.

How on earth could I continue working for a place that did not align with my ethical standards? I knew once I had my CPA license that ethics played a major role in being able to retain your license in good standing. I was not yet licensed but I felt morally obligated to treat any current situation as though I was. This work day made me reflect on whether it would ever be worth it to sit at a desk all day and feel like I was not making a difference or to experience recurring challenges of moral distress. I knew that I would not be happy until I was working for myself, where I could control every situation to ensure it complied with my ethical values.

Photo Credit

The very next day I QUIT. I had no clients lined up, I had no network to speak of, and I had NO IDEA WHAT THE HELL I WAS DOING. I did not even have a plan. Maybe I should have read “5 Steps to Take Before Quitting Your Job to Become an Entrepreneur” and consider my options if things didn’t pan out so well. Because that’s exactly what happened. I was reckless with my first meddling in entrepreneurship. I owned a home with no income from roommates. I pulled out $20,000 from my credit cards to fund my personal expenses. I did not know how to build a client base and the one big project I worked on was probably the worst client I could have worked with. It was so discouraging.

6 months later, with no clients and mountains of debt, I put my tail between my legs and started job hunting. I felt like a complete failure. I felt like maybe I wasn’t cut out for my dream. I worked the next job for about a year, slowly – ever so slowly – paying off that $20,000 cash advance, which amounted to ridiculously high monthly interest. Then I decided to pick up my entire life and move to Reno, NV, where I worked a couple more corporate jobs. It was at my most recent job that an opportunity presented itself that I could not pass up. An opportunity to give entrepreneurship another try. But this time things would be different and failure was not an option.

I was working as a senior accountant for the Reno Aces and they were willing to support me by becoming my first client. Since then I have immersed myself in the business community. I joined Dynamic B2B, a wonderfully supportive and educational networking group that focuses on small businesses. I aim to genuinely connect with as many people as possible and constantly take initiative to reach out, whereas previously I was more selling focused instead of relationship focused. I truly want to help other business become more efficient and obtain more meaningful financial information and I’m continually working to make that resonant in my branding.

I still have a lot of work ahead of me to make this second venture successful. The experience is a complete 180 from the first time. I go to tons of meetings each week, I’m working long hours, and I’m absorbing as much as possible in my MBA program. The best part of it all is I’m loving every minute of it!

My favorite meetings are those where I get to talk with other entrepreneurs who have either been in my shoes or are in my shoes right now. Hearing other’s stories keep me motivated and invigorated so if you have a gritty story I would love for you to share it in the comments below! Or even better, shoot me an email so we can grab coffee 🙂

My Gritty Undergrad

I’m currently reading “Grit” by Angela Duckworth (@angeladuckw) for one my MBA courses and I am in love! I’ve always been an avid reader and genuinely enjoyed school growing up – one reason I’m tackling my MBA – so the psychological backing behind what grit is and how you get it is right up my alley. I think reflecting on my own adventures to find grit portrays what type of entrepreneur I am and the level of dedication I have to my profession and those that I serve.

Photo Credit

According to Duckworth, grit is a combination of passion and perseverance for a singularly important goal. Not only that, but she also speaks to how grit can grow. We move from identifying a self-oriented interest (ie: job), to learning self-disciplined practice (ie: career), and finally to a revelation of a larger purpose and meaning (ie: calling). I can recall two particular experiences in my career life where grit was essential to avoiding setbacks or complete and utter failure. I will speak of one today and the second on my next blog.

In the fall of 2006 I transferred from junior college to a University to get my Bachelor’s in Accountancy. Since high school I had always worked a full-time job while attending college part-time. I knew I was not on the usual four-year track to obtaining my Bachelor’s but I had no idea just how far behind I was. At the rate I was going I still had another four years to go. This news was very disheartening but I did not allow myself to feel discouraged for long. I made a bold decision: get a part-time gig and go full-steam with school.

In the fall of 2008 I accepted a position as an entry-level accountant for a property management company. I work worked 24 hours a week, being on-site Monday-Wednesday-Friday, and filled my time closing out the monthly books for over 15 different properties and joint ventures.

That same semester I did the unthinkable: I enrolled in 24 units (8 classes) at CSU Sacramento – and not just any 24 units – they were all upper-division (aka HARD) accounting units. This is a double full-time class schedule and was recommended that students not work any hours at this unit level. Well, I wasn’t having any of that. I knew I had it in me to get the work done so I got permission from the school to do so.

My two days off from work I was in class from 7:30am-4pm, back to back with no breaks. I usually got to school an hour early and left as late as 9pm to get in extra study time. Monday and Wednesday I had one class after work, plus an online-based class. I essentially ate, drank and slept school during those months. I admit, it was hell. I struggled to have any kind of social/family life at all, but I managed to survive the semester. I finished with 4 A’s and 4 B’s and I was floored at the accomplishment and relieved it was over. I already knew I had found my passion and I pushed through a treacherous semester so that I could graduate over a year sooner. I can say with certainty that I could never have gotten through it if I hadn’t had the passion and perseverance for my interest in accounting.

I am working on transitioning my interest from just a career to my true calling. Find out how I’m making that happen in my next blog this week, where I discuss using grit to persevere through a business failure in order to get back up and try again.

QuickBooks & Common Expense Mistakes

As mentioned in my last blog that covered “QuickBooks & Common Deposit Mistakes”, I’m a QuickBooks ProAdvisor and I assist my clients with fixing discrepancies. Another area that I get asked about often has to do with expenses. When do you use a check? When do you use an expense? What about vendor bills? Here are some common mistakes I’ve encountered and how to fix them.

Using a Bill and a Check

If you plan to enter vendor bills, it can certainly be a helpful function to utilize. Tracking upcoming bills due and how much is owed can be of great benefit when planning for future cash outflows. It is imperative though, to make sure you are not duplicating the bill when you cut a check.

When you are ready to pay a bill that has already been entered, you will likely pay one bill or pay an entire batch. If you are paying only one, go to the bill and click on “make payment” in the upper right-hand corner (Figure 1). Choose the payment method from the drop-down menu next to the Vendor name and then click “Save and Close” at the bottom. From here you can complete the “Print Checks” step.

QuickBooks Bill
Figure 1 – click to enlarge

If you are doing a batch of bill payments, click the “Plus” icon at the top right and choose “Pay Bills.” Here you can select multiple bills to pay that have already been entered (Figure 2). Finish by click “save and Print” in the lower right corner and print the batch of checks.

QuickBooks Pay Bills
Figure 2 – click to enlarge

Either of these methods will properly record the bill (expense) and subsequent payment. The common mistake is posting a bill and then subsequently posting a check, without using the Bill Payment step. The expense will be recorded once on the bill and once on the check. This will also lead to showing bills an unpaid even after you have paid them.

Let’s say, for example, that our client Sue entered the PG&E bill in Figure 1 and then also posted the check in Figure 3. If Sue were to go to PG&E in her vendor list, see would see that the bill was unpaid. To fix this, Sue must go to the check and add the bill to it. Notice in Figure 3 on the right-hand side there are unpaid bills listed. Click on “Add” and then the bill will appear in the check detail. If the check amount defaults to $0.00 simply change it back to the amount the check was written for.

QuickBooks Check
Figure 3 – click to enlarge

You can record a check directly to an expense, like was done in Figure 1. Just be sure that you have not already posted a bill for that item. These same steps apply to credit card expenses and bill payments as well.

 

These screen shots were taken using a sample company in QuickBooks Online, so QuickBooks Desktop may look slightly different. If you’d like personalized help with fixing your expenses, please contact me HERE and we can set up a one-on-one meeting. If you have another expense issue you’d like me to delve into in a future blog, please leave it as a comment below.

QuickBooks & Common Deposit Mistakes

As a QuickBooks ProAdvisor (see my profile here), I often coach clients on fixing discrepancies in deposits. Today I will be discussing two common mistakes and how to fix them.

Not Receiving Payments

I recently worked with a client – let’s call her Sue – who was puzzled by open invoices. She knows for certain that she had received wire transfers for these invoices and posted the deposits in QuickBooks. The first step to solving this issue is to look at the deposit. Let’s say, for example, that Sue invoiced Cool Cars on 2/1/17 for $2,500 (Figure 1):

Figure 1 – click to enlarge

We would then look at the deposit that Sue made on 3/1/17 for $2,500 (Figure 2):

Figure 2 – click to enlarge

I see here that Sue posted a deposit in a manner that did not get applied to the invoice (B). To apply funds to an invoice, we must use the “Select Existing Payments” section (A). There are no payments here for Cool Cars so first we must receive a payment (Figure 3). Go to the client’s open invoice and click “receive payment.” Fill out deposit information and check next the invoice(s) to apply the payment to (C).

Figure 3 – click to enlarge

Since we chose Checking as the “deposit to” account, there are no further steps needed. However, we do still need to delete the deposit previously posted in Figure 2, otherwise there will be a duplication.

Multi-Payment Deposit

Another common mistake I see is when clients make only periodic deposits, ie: every two weeks, and each deposit contains multiple customer payments. If we receive payment as in Figure 3 above, it may be more difficult to reconcile bank deposits since each payment will show as a separate deposit amount. These receive payments should be posted with the “deposit to” account being “Undeposited Funds.”

Undeposited funds is simply a clearing account to be used when there is a timing difference between when payments are received and when they are deposited to the bank. Let’s say I have received payment from three different customers on March 1st, 5th, and 8th. I record the payments as received on each of those dates.

I think go to the bank on  March 10th and deposit all three checks together. In QuickBooks, we add a new deposit, checking next to each payment already recorded that was included in the deposit (Figure 4, see also (A) on Figure 3). We can see here that the deposit total is $2,965 which will match what is shown on my monthly bank statement.

Figure 4 – click to enlarge

These screen shots were taken using a sample company in QuickBooks Online, so QuickBooks Desktop may look slightly different. If you’d like personalized help with fixing your deposits, please contact me HERE and we can set up a one-on-one meeting. If you have another deposit issue you’d like me to delve into in a future blog, please leave it as a comment below.

Check out my next blog where I discuss common QuickBooks expense mistakes.

The Power of Excel

As an accountant I absolutely love using Excel. I use it for a multitude of items and it helps me cut down time required to complete a task. Here, I will briefly describe some of its functionality I find most useful.

Photo Credit

 

 

 

 

 

Automation

Many Excel users actually under-utilize the functionality of Excel. They may have taken the first step in creating a template for a recurring task, but it could still be a very manual task in general. There are a lot of functional formulas that can remove redundancy of data entry and calculations. Without a doubt, I can say that my favorite and most used function is =SUMIF. It easily adds numerous line items that have a specific cell value.

For instance, in this screen shot, my SUMIF formula is adding the amounts in Column C whenever there is an “A” in Column B. I can do the same formula to add up all the B’s, C’s, and D’s. There is no need to do any sorting or individual summations. Excel does work for you!

Documentation

If you use Excel as your source document for an office or accounting task, it is easy to have the Excel file contain ALL related documentation. Let’s say you use an Excel template for each bank deposit you record in your accounting system. A critical element to keep record of is the actual deposit slip that goes to the bank. You can easily embed a picture or PDF into the template that shows the deposit slip. From the top ribbon bar you go to the Insert tab, click on “Object,” click “Browse” and choose the file to embed. There are three embedding options: click “OK” for the object to show the full first-page; click “link to file” to show as a hyperlink; or click “Display as Icon”.

Integration into Other Software

Often times, Excel templates can be used for direct uploading into other software, such as an accounting system. This can be valuable when data input in the other software is manual and redundant. If you’re able to do the data entry in Excel, you can use copy/paste and formulas to speed up the task, then import into the other software.

You can find some basic templates on the Microsoft website, such as calendars, budgets, and to-do lists. Beyond templates, there is a plethora of functionality within Excel. If you are wondering if Excel is capable of doing this or doing that, it’s likely that it can. Exploring what I hoped Excel was capable of doing is how I became so proficient in it. Take some time to play around with different functions and items in the top Ribbon Bar. If you’re curious about something specific, please leave a comment below and I will answer it in one of my next blog posts.