Excel Tip: Pivot Tables

Today I’m going to go over pivot tables in Excel. When you have large volumes of data to analyze, pivot tables allow you to summarize and manipulate the data quickly and easily. I’ll be using a sample set of data to show you the steps to creating and modifying a pivot table.

With the use of pivot tables, you’re going to want to start with data that is organized. In this sample data, it’s organized by date, region, rep, item, units, unit cost, and total. You want to make sure that your data is in some organized fashion.

To create a pivot table, go to the insert tab of the ribbon, click on Pivot Table, and then you’ll be able to select a table or range. We’re going to make sure to include the header and go all the way down to the bottom of the chart. Hit OK.

Once you scroll back to the top of your spreadsheet, you’ll see where your pivot table will be inserted. To the right, is your pivot table fields. This is where you will choose what will go into your pivot table and where. You can check next to the items if you want them included. My preference is to drag and drop the items into the sections where I want them.

I’m going to first summarize by region and drag it down into the rows section. All of that data will be categorized by Region in the rows. Let’s say I want to know how much in dollars there is per region. I’m going to take the Total and drag it into the Values section. Now I can see the dollars by region. We can also subcategorize the regions by pulling the Reps down into the rows section. Now I can see by region, by rep, the total dollars for each.

I can also move items around. Let’s say I want to move the Region into the columns. Now I show the Reps in the rows, categorized in each column by the Region.

Once you have your pivot table the way you want it set up, you can easily change the rows and columns by clicking on the down arrow. You can choose just to have a few items show, or add them all back in. You can also sort in whichever direction you want.

If you want to change the look of your pivot table, you can go to Format as Table under the home tab and to have it formatted with those colors.

Those are the basics of creating and modifying a pivot table. If you’d like me to go over a specific functionality of a pivot table in a future blog, please comment below or EMAIL ME and I’d be happy to go over it.

Excel: Using Concatenate to String Text Together

Are you ever in need of combining the text in multiple Excel cells? Are you ever in need of creating a pattern of text but one component is a variable? If so, you need to be using the CONCAT formula in Excel (this shortened formula now replaces the previously used CONCATENATE formula). Today I’ll go over two basic uses for this formula.

String Together Multiple Text Cells

Let say you have a list of first names, last names, and cities of residence. You’d like to combine all three items for a new list. Use the CONCAT formula to as follows:

  1. In the cell where you want the combined text, type =concat
  2. Add a parenthesis and then start selecting the cells in the order you want them combined, placing a comma between each chosen cell.
  3. Hit “enter” and the combined text will show in the cell.
  4. Note how the there are no spaces between the words. You will need to add in a space surrounded by quotes between each set of cells where you want a space.

String Together Text From Cells Plus Additional Text

Let’s say you have created a list of locations and distances ran recently. You’d like to create a formatted sentence using this list. Use the CONCAT formula to as follows:

  1. As in the steps above, type =concat( in the cell where you want the sentence.
  2. Anywhere you want to add text, put the text in quotes. You can do a combination of this and choosing cells, putting a comma between each section. (Note how spaces are added in the additional text sections. This can be done instead of individual “ “ between items).
  3. Drag the formula down, or copy/paste for the rest of the list to apply the same sentence to all lines of the list.

 

 

 

 

 

If you would like help with more complex concatenate formulas please CONTACT ME to work on it together. Or comment below with other formulas you’d like featured in a future blog.

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.”

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.

Love in Numbers

Tomorrow is Valentine’s Day, the day we express our love to others with cards, flowers, and chocolates. How about we skip the commercialized aspect and talk about a different kind of love… a love for numbers! No, you say? You don’t love numbers? Well, that’s ok, because you have a love for something else. Your passion. You broke into your business because you took your passion and ran with it, whether it be starting a new restaurant, creating beautiful artwork, or opening your favorite franchise. The last thing you want to consume your time with is numbers. Your time is much more valuable when spent curating new ideas and products that generate revenue.

Lucky for you, my passion IS numbers! How many people do you know who look forward to balancing their checkbook? (Hint: now you know at least one). Give me a detail-ridden, multi-layered account reconciliation, even one that hasn’t been done in over a year – or maybe 8! – and it’s GO TIME.

Since starting my venture into sole proprietorship this past November, I have been diligently working to meet as many local business owners as I can. I am amazed by the entrepreneurial supportive community that is Reno and I absolutely love hearing how others got started and learning about the different facets of their business. I want to be a part of it all! And as I conduct consultations (which are always free of charge) I delve into these details so I can best understand how I can be of benefit to their business.

My business model is to be adaptive to my client’s current and future needs. As their business grows and changes, their needs grow and change as well, meaning I adapt along with them. From a start-up who needs help setting up their accounting and business processes, to a young business that needs to look more deeply at financial analytics, to a mature company that needs to bring in-house accounting and seeks assistance in hiring and training the right team, my passion for numbers will always radiate in the task at hand. It is my ultimate goal to help my clients be as successful as possible by delivering meaningful information in a timely fashion in order to make the smarter business decisions.

This blog is just another tool that allows me to offer insightful information to the community I hold dear to my heart. I will cover topics on accounting and accounting software, excel, and how to seek out opportunities for efficiency and process improvement. I want to hear from YOU on the topics that will benefit you in your business decisions. Please leave your questions in the comments so that I can address them in a future blog. And remember to become a subscriber – in the right-hand menu – to ensure you don’t miss any of these helpful tidbits!

 

 

Steps to Identify Opportunities for Efficiency in your Business

Throughout my career in private accounting, I have had many opportunities to implement process efficiencies relating to not only my position, but others’ as well. Often times the process changes that affected me the most are ones that I suggested for my coworkers.

I have repeated specific steps time and time again, within companies of varying industries and sizes, which resulted in reduced time requirements, increased accuracy and automation, decreased need for manual input, and an increase in overall enjoyment of tasks. Coworkers have always been appreciative of the unsolicited help they received, and in return, I experienced positive results as well.

Want to increase efficiency within your job/department/company? Here’s my process for how to get there:

 

Start by working with one task at a time.

Don’t hit the pavement running with a mindset that you want to have everything working fluidly within a week/month/quarter. This can lead you to feeling overwhelmed when you realize how many tasks could potentially be improved. Process improvement is just that….a process!

Pick one task that seems to bog you down the most. Whether it’s monotonous, data-entry heavy, consistently riddled with errors, or something you merely don’t enjoy. There are a multitude of reasons why a task may be requiring more of your time than it needs to.

Step back from the task and take a thorough look at it.

Many times we forget to think about the task at hand; as in truly think about the mechanics of it. We get so consumed with meeting deadlines, we get too comfortable with routine, or we’re new to a position and following the lead of our predecessor.

My philosophy is to never stop thinking! Even if I’ve improved one component and feel that now the task is a well-oiled machine, I stop and ask myself “What else might I be able to do better?” In my experience, for example, it can take doing a routine monthly task 3 or 4 times to work out all the kinks. Gaining a deep understanding of one component of a task can lead you to recognize a deficiency in another area.

Step even further back and look at the company as a whole.

Maybe there’s one task you have that you consistently struggle to finish on time. But it’s not your fault, right? The information you need must first come from an outside department and you have reiterated numerous times how critical it is to have that information by a certain day, otherwise you risk missing your deadline.

This step right here is a HUGE game changer. When you step back and look at where your task requirements are coming from and how they end up in your possession, you have the opportunity to help others improve their processes. And when their process improves, you are likely to see an improvement on your end as well.

The same can be said if you’re always re-working something because the person you hand it off to has to kick it back. Set aside some time to meet with them and ask them to explain what they need from you in such a way that you can more effectively deliver your product.

When others feel that you are trying to help them instead of being that grumpy person from such-and-such department, they will be more willing to listen to your needs in the future and work together more collaboratively.

That’s the golden ticket, folks. Collaboration. Working collectively so that everyone can enjoy their work and those they work with. When you have this, it is much easier to procure efficiencies.

Repeat.

Once you’ve worked on one task, move on to the next! But every time you do a task that has already gone through the steps above, take just a minute or so and ask yourself if there’s something you missed before. And if so, put it back through the ringer. Process improvement is never-ending. So challenge yourself to do it better every time!

 

These steps in real life.

I know these steps work because I have gone through them more times than I can count. Back in 2013 I worked for company that purchased distressed commercial real estate, spruced them up, and then managed the properties.

The accounting department was relatively small: an AR/AP clerk, assistant controller (me), controller, and CFO. I had already worked on many of my own tasks, but when month-end close came around, I was usually waiting around for all the tenant billings to be posted. And there were A LOT.

I asked our AP clerk if I could help in any way, and, as per the usual, I got a “Thanks, but there’s not really much you can help with.” She assured me it was a task that had to be completed by following their standard procedure.

I did not accept that this was the only way you could possibly bill a multitude of tenants. So when I had some down-time I Googled our property management software, found the user manual and help site, and dug my nose into it for a good half an hour.

Alas, I had struck gold! The program was capable of utilizing Excel uploads. Thankfully, Excel is my forte so I got to working on a template. After a few trial runs, I finally had a template that was user-friendly for any level and fairly straight-forward.

The next day, I asked the AP clerk if she had time that I could show her what I’d discovered. The template peeked her interest and the next month we gave it a go. We had great success. I asked her how long the billing took her with the previous procedure. “At least 8 hours.” I am proud to say that the new template allowed her to complete the same billing in only 30 minutes!

She was immensely appreciative of my suggestion and her demeanor towards the task improved greatly. It was no longer a daunting day-long chore for her. Thereafter, she would periodically ask me to review other tasks to see if I could improve on them as well.

As for me, I was no longer forced to wait on her in order to get my own work done. If you ask me, it was a Win-Win situation.