Chat with us, powered by LiveChat

Blog for CPAs

Get & Transform Feature in Excel. Be Transformed!

If you have Excel for Office 365, Excel 2016, or Excel 2019 then you should have the Get & Transform feature available. To see if your version has it, just click on the data tab and see if the ribbon has the words Get & Transform Data. It should look similar to this, although it can differ depending on Excel versions.  In Excel 2016, this feature was originally called Power Query.

This is an amazing feature in Excel that you will definitely want to explore. It is a business intelligence tool that allows you to import millions of records from a variety of sources, clean and transform them, or reshape the data as needed.  As you perform these steps, Excel records all the steps.  This recording code, called M Code, operates behind the scenes, so you do not have to know anything about coding. The interface is easy to understand and use.

So, if you are frequently importing data, and then spending time cleaning, combining or extracting data, adding calculated totals, and using VLOOKUPs, then you are going to be so very excited at how much time this feature is going to save you. Get & Transform also allows you to combine multiple files together, either by appending them, or merging them together into a single table. So, set your query up once, refresh it, and you are done.  It will be a huge time-saver for you come month end. Quarterly reports- set them up once and then just refresh. Think of all the time you will have for something a bit more interesting then compiling reports.

Let’s go through a quick example.

To import the data from a saved file:

1. Click on the data tab.

2. Select from text.

3. Locate and select your file.

4. Click import.

5. Your data displays and shows the delimiter used. If the file is large, it may not bring in all the data.

If you do not want to clean or transform your data, you can click load, and it will take the data and put it directly into an Excel worksheet. We are going to transform our data first though.

6. Select transform data. This takes the data and displays it into Power Query editor.

Power Query editor’s ribbon and right-click menus are very similar to Excel, which is what is so great about the feature. It is taking database features, incorporating them into Excel, a familiar program, and making it easier for people to understand and use.

You have a variety of options to transform your data, as well as the ability to remove errors and duplicates.  Take some time and play around with all the choices. Once you have transformed the data, you close and load it into Excel.

What I want to call your attention to, is the right side of the screen, which is called Query Settings, and there you see applied steps.  This is the where the genius part of Get and Transform lies.

Power Query editor records every step that you make.  The applied steps dialog box shows every action that I took. If you look, you can see that I removed columns, cleaned the data, and then removed additional columns.

Here is the exciting part.  I only have to do these steps once and Excel’s Power Query editor will remember all these steps, and apply them the next time you refresh your data.

This could be a huge timesaver at month end. So, next month if the data was updated, all I would need to do is open the file and go to Power Query editor and it would refresh the data, and then go through all these steps, without me having to do anything.  The other key feature of the Power Query editor is that you are not impacting the actual data, but rather seeing a reflection of it.  So, when you delete a column or data, it is not gone from the original file, just from the display.

Again, a very cool feature that I have just touched upon here. If you have the Get & Transform feature, definitely check it out.

Plotting Empty Cells

I received a call from my daughter yesterday. She was totally frustrated as she was trying to plot data for a class project that contained a lot of empty cells. I told her how to fix it and then learned that these suggestions would not work on a MAC. As I helped her figure how to do it, I tripped across a lot of information on plotting empty cells and realized that a lot of people struggled with this. So, of course, here is a post on how to handle plotting data when some of the data is missing.

Here is a simple example. Below is twelve months data on beverage sales. Almond milk sales did not begin until June. Soy milk proved unpopular and they stopped selling it in May. The company introduced goat milk in January and then decided it needed more work and reintroduced it in November.

Let’s start with looking at the chart. Right off the bat, you can see there is something wrong with the top gray line for organic milk as there is a break in the line. Cider looks to be starting in March even though there is January data. January goat sales do not even display on the chart.

First, sometimes people want to add zeroes. Lets’ see what it looks like if I do that for February cider sales.

Well, now the January sales show but look at that plunge.  Adding zeroes is not recommended. Let’s see what alternatives there are.

In this example, below I have replaced the 0 with #N/A.

As you can see, it smoothed out without that excessive plunge where it was trying to display a zero.

Let’s try the same thing for the goat milk sales line.

Hmm.. not as perfect as it look as though. Goat milk was sold all year but better than what it was. Also, let’s face it, no one wants a lot of error values in their spreadsheets.

Another alternative that is the best, I think, is just show the empty cells as gaps or connect data points with a line.

To do this, right-click on the data in your chart and select Data…

Click on Hidden and Empty Cells.

If you look at the goat milk, when the cells are blank and I use Connect data points with line, it resembles the #N/A version but at least I do not have #N/A errors throughout my spreadsheet.

So, really you have a choice between showing gaps or connecting the lines.

I was reading Jon Peltier’s blog and he mentioned that Microsoft had updated the Hidden and Empty Cell Settings to handle #N/A. So, of course I immediately updated my Excel version and the new update suddenly included the Show #N/A as an empty cell. If you do not see that option, then you either have an earlier version of Excel or you need to update.

So, to finish this off and see how it looked, I put #N/A in all the empty cells, and then selected Connect Data points and put a checkmark in Show #N/A as an empty cell.

The results are below.

So, if you do have #N/As in cells, that option is a nice workaround.

Jon Peltier goes into much more detail and shows how the different choices look in a variety of chart formats so if you are looking for more information on this, I recommend you stop by Peltier Tech Blog.

And for you MAC users, if you right-click and Select Data…, you should see a similar dialog box.

Hopefully, my daughter’s project turned out well. For more information on computer software tips and tricks, see the full catalog of self-study CPE products.

Fill & Sign Forms-Adobe Acrobat DC

Fill and Sign PDF Forms

  • Open the PDF that you want to fill in and sign using Adobe Acrobat DC.
  • Click the Fill & Sign tool located in the right hand pane.
  • Click in a field and start typing.
    Adobe may suggest responses based upon previously entered data or stored information.
    To accept, Adobe’s suggestion, use your cursor to highlight it and then click to auto-fill the field with the suggested data.
    All your new responses will be saved and will appear as suggestions next time you fill out a form.

Checkmarks on the form

  • If you need to add a checkmark, hover over the checkmark square area and click once. Adobe will add and resize the checkbox.

Add A Signature

  • To add your signature directly into the form, click the Sign icon in the top toolbar.
add a signature adobe

  • Click the Add Signature.

You can do one of the following:




1.Type your name where it says “Type your name here” and then choose one of the signature styles.2. Draw your signature using your mouse in the “Type your name” area

3.Upload an image of your actual signature.

  • Once you have made your choice and created the signature, click Apply.

The signature is attached to your cursor, so move your mouse to the proper location and click once to place your signature.
You can click and drag the left side of the box if you need to resize it a bit. You can resize by dragging the handle on the  bottom right of the signature box where the blue dot is or you can use the font icons at the top of the contextual menu.

A warning box will display, telling you that when you save a form with a signature or initials, you can no longer edit the existing form fields so make sure that everything is the way you want it before you the save the form.

Send A Copy

In the upper right of the screen, there is a Send a Copy icon.  If you click that, it will request that you sign in with your Adobe id and then it will allow you to send this non-editable copy to others by generating a link which  you need to post into an email.


Find & Select Constants

I just discovered this little timesaver and thought I would share it with you. Okay, to be honest, my husband told me about it.

Suppose you have an Excel spreadsheet and you have a number of product codes all in one row and need to break them out. You can do text to columns but if the number of product codes in the rows are uneven, it can be time-consuming  to collect them all for analysis. Using Find & Select Constants solves this problem.

Let’s use a simple example. Below is a spreadsheet showing certificate ids that customers have been issued.

If I want to break out those certificate ids to count how many of each were issued, I would first select all the data in Column C and then select Data>Text to Columns. As you can see the data is delimited with Commas.

Click Next and select Comma.

Select Finish.
Text to Columns separates all the certificate ids  into separate columns.

In this example, it would be fairly easy to copy and paste the different certificate numbers into Column C but if you had rows and rows of data it would be very time-consuming especially if the certificate ids  extended over into Column L and M or further. This is where  Find and Select Constants comes in.

Select all of Column D and then click Find and Select.

Excel selects all the cells in Column D that contain data.

All the cells are selected so I can just select Copy and then Paste those certificate ids  into Cell C9.

find and select constants in excel

Excel ignores the blank cells and nicely grouped my selection so they are all contiguous. I can then delete the data in Column D since it now displays in Column C and then move on to the next column of certificates.  Unfortunately you need to copy as Excel does not let you cut multiple range selections.

Hopefully you see some applications for this. I found it quite handy.

Let’s Get Rid of =GetPivotData


If you use pivot tables a lot, then you have run into  the GetPivotData formula. The other day, I was trying to create a formula referencing some pivot table cells and the GetPivotData formula popped up when I did not want it to. Today, I read Debra Dalgleish’s tweet on the same topic and realized that it is a problem for a lot of people. So, I thought some of you might be interested in learning how to get rid of GetPivotData.

GetPivotData has a lot of uses, however, if you want to create another formula in the column next to a pivot table and refer to a cell in the pivot table then you know what happens. First, you get a long long formula referencing the pivot table and the cell which is okay.

getpivotdata formula

But then, you copy it down and suddenly, you have the same answer displaying in every cell.

This drives me nuts and I am sure some of you are shaking your head yes and agreeing with me.




If you do not use GetPivotData and want to remove it all you need to do is click in the pivot table to select it and then do the following:

  1. Click the drop down arrow under Options on the Excel ribbon.






2. Click Generate GetPivotData command and turn it off.
If you do not see the Options choice, make sure your cursor is in the pivot table.

This turns the formula GetPivotData off for all future spreadsheets. So, if you do want to use it at a later point in time, you will have to go back in and turn it back on.

Qualified Business Income

individual tax prep cpe

Need Info on Qualified Business Income?

A lot of people have been asking about Qualified Business Income recently and one of our authors, Paul Winn CLU ChFc  has nicely offered us an excerpt
from his course Tax Cuts and Jobs Act -Individual Tax Preparation.

Qualified Business Income
– Excerpt from Tax Cuts and Jobs Act Course by Paul Winn

Nature of the 20% Deduction for Qualified Business Income

Section 199A of the Internal Revenue Code added a deduction as a result of the passage of The Tax Cuts and Jobs Act of 2017 of up to 20% for qualified business income derived from a qualified trade or business.

Two components comprise the deduction for which taxpayers may be eligible:

  1. A deduction of up to 20% of qualified business income from a domestic business, subject to limitations based on –
  • the type of trade or business,
  • the taxpayer’s taxable income,
  • the amount of W-2 wages paid by the trade or business, and
  • the unadjusted basis immediately after acquisition (UBIA) of qualified property held by the trade or business.; and
    1. A deduction of up to 20% of the taxpayer’s combined qualified real estate investment trust (REIT) dividends and qualified publicly traded partnership (PTP) income.

The total of the qualified business income, referred to in item 1, and the REIT and PTP income in item 2 is referred to as the “combined qualified business income amount.” The deduction is generally equal to the lesser of:

  1. The combined qualified business income amount; and
  2. The taxpayer’s taxable income reduced by the taxpayer’s net capital gain.

Deduction Eligibility

Pursuant to the provision of the TCJA creating the deduction, taxpayers who may be eligible for the deduction are those who operate a business as a sole proprietor or through a pass-through entity. Accordingly, individuals, trusts and estates with qualified business income, qualified REIT dividends or qualified PTP income may qualify for the deduction. Income earned as an employee or through a C Corporation is ineligible for the deduction.

Eligibility for the pass-through deduction authorized by the TCJA does not require that the taxpayer choose to itemize tax deductions. Accordingly, eligibility for any pass-through deduction is unaffected by the taxpayer’s election to itemize deductions or take the standard deduction.

Pass-Through Deduction for Qualified Trade or Business

The TCJA, §11011, provides for a deduction of up to 20% of a pass-through business’ qualified business income. The deduction, depending upon the amount of qualified business income and other factors affecting it, may reduce the taxpayer’s income tax liability significantly. However, the “other factors” that affect the deduction include:

  • Whether the business is a qualified trade or business;
  • The taxpayer’s taxable income if the business is a specified service trade or business;
  • The amount of W-2 wages paid; and
  • The value of qualified property.

The amount of the pass-through deduction is equal to:

  1. The lesser of A or B, where:

A is 20% of the combined qualified business income; and

B is the greater of –

  • 50% of W-2 wages paid, or
  • 25% of W-2 wages paid + 2.5% of unadjusted basis of qualified property


  1. 20% of the total amount of the taxpayer’s qualified REIT dividends and qualified publicly traded partnership income for the taxable year.

Thus, critical to an understanding of the pass-through deduction are the definitions of:

  • Pass-through business;
  • Qualified trade or business;
  • Qualified business income;
  • Combined qualified business income; and
  • Qualified property.

Let’s consider each of these definitions.

Click on the link to access the rest of the PDF file

What is a Pivot Table?

I have had a couple of people ask me what a pivot table is so I made a short video that walks you through the basics.
One key bit of information that I left out of the video is that if your underlying data changes, all you have to do is right-click the pivot table and select Refresh.
This will make the pivot table update and display the newest information.

Click on the dog with the movie popcorn to watch the video.

Excel video on pivot tables


New Excel Functions Coming!

New Functions in Excel

I just read a tweet from David Ringstrom and Joe McDaid about dynamic arrays and new Excel functions that are coming soon.

Truly amazing and a huge time saver for many.  Check out this article which shows how each of the formulas works:

It would seem that it will be a slow roll up, so keep your eyes open when you update.
Below are the complete  set of functions that will be accompanying dynamic arrays.
• FILTER – filters an array of data based on criteria you define.
• UNIQUE – returns a list of unique values from a list or range.
• SORT – sorts an array of values.
• SORTBY – sorts an array based on a corresponding array.
• SEQUENCE – generates a list of sequential numbers, such as 1, 2, 3, 4.
• SINGLE – accepts a range or array and returns a single value using implicit intersection.
• RANDARRAY – returns an array of random numbers between 0 and 1.

Inflation Adjusted Limits and Thresholds

One of our authors, Paul Winn, recently sent me tax information.
The IRS recently sent notification of modified revisions of inflation-adjusted limits and thresholds to reflect the change in calculating inflation from the previous method to the Chained Consumer Price Index prescribed under the Tax Cuts and Jobs Act.  Here are some the changed items :


Original Inflation Adjustment

Revised Inflation Adjustment

HSA contribution limits for individuals with family HDHP coverage (other HSA limits are unaffected) $6,900 $6,850
Archer MSA

Self-only coverage out-of-pocket limit

Family coverage annual deductible

(other MSA limits are unaffected)







Adoption Assistance Exclusion and Adoption Credit

Phase-out begins at MAGI of

Completely phased out at MAGI of







Small Business Health Care Tax Credit

Phase-out begins at average annual wages

Phased out complete at average annual wages







Estate exclusion (for federal estate taxes) $11,200,000 $11,180

To check out our tax courses, click here.

Adding VLookup as a Control for Your Month-End Workpapers


When closing for the month, many use an Excel workbook to reconcile balances to the general ledger.  As the general ledger changes, do you have a process in place to easily determine that balances previously reconciled still tie to the general ledger?  If not, using Vlookup on each reconciliation worksheet provides a quick and easy way to accomplish this control.

Usually, the month-end balance from a reconciliation worksheet is at the bottom of the worksheet.  Below this balance, you can enter the associated general ledger account number to be used in a Vlookup formula.  After the account number and Vlookup formula have been entered in each workbook, all you need to do is cut and paste the most recent general ledger balances into a separate worksheet.  The Vlookup will display the most recent balance and you can use conditional formatting to highlight whether the two balances are within a tolerable error limit.

The example below assumes that the AR Trial Balance has been created and the total needs to be compared to the general ledger.  There is a worksheet labeled “Accounts Receivable” and a separate worksheet labeled “General ledger”.

The general ledger account for accounts receivable has been entered at the bottom of the Accounts Receivable worksheet and a Vlookup formula is used to compare this total to the amount in the General Ledger worksheet.

vlookup as control 1








The Vlookup function is searching for a worksheet titled “General ledger” in the same workbook.  The key to making this work is that you never delete this worksheet.  When the general ledger changes, simply paste the new general ledger data over the old data in the same worksheet.

For Vlookup to work, the lookup value, in this case the general ledger account number, must be in the leftmost column of the General Ledger worksheet (In this example, column A).  The general ledger data is in columns A and B and the account balance is in column 2 (which is the B column).  The FALSE range lookup value is used to indicate an exact match.  The example general ledger is shown below.









Why use the entire columns of A and B?  That way, if general ledger accounts are added, we’re not constrained by a limited range.  This is important as we want to paste over this data again and again without using new Vlookup formulas each time.

To calculate any differences, subtract the general ledger balance from the reconciliation balance.  Use the ABS function in the formula since we just want to know the absolute amount of a difference and do not need to know whether it is positive or negative (ABS provides the absolute value of a number).








In this case, the difference is 0.  However, we want a way to highlight a difference if it exceeds a tolerable error limit.  Assume that we want to highlight any change greater than $100 and, if the difference is greater than $100, highlight the cell in yellow.

Select cell B13 and then select the Conditional Formatting button on the Excel Home ribbon.  From the Conditional Formatting options, select “Highlight Cell Rules”, then “Greater Than”.

conditional formatting vlookup












Once “Greater Than” is selected, you have the opportunity to input both an amount and highlight colors.  In this example, our amount is greater than 100 and we’ll use yellow fill with a yellow text to highlight the difference.  Note that we used an absolute value since the difference could be either greater than 100 or less than 100, depending on whether the general ledger amount was more or less than then the reconciliation amount.  Having only one value makes the Conditional Formatting comparison much easier.

Vlookup and conditional formatting









Press OK.

Since the difference is not currently greater than 100, there should be no yellow highlight.  If the general ledger balance is changed to 31,500 (a difference that is greater than 100), the difference is now highlighted.


With this Vlookup control in place for each month-end reconciliation worksheet, you can copy and paste the new general ledger data into the General Ledger worksheet and the new balances will update.  Any differences that exceed a tolerable error amount will be highlighted and you can simply scan each worksheet to determine if a difference exists.

This process is much easier to implement if all month-end account reconciliations are in one workbook.  That way, the general ledger data only needs to be pasted once in the General Ledger worksheet each time the general ledger is updated.

CPASelfStudy has several in-depth courses on Vlookup and Conditional Formatting.  If you want more information or other productivity ideas, please check out our Excel course offerings.




Using TextJoin

text join







TEXTJOIN is a text function that was introduced in Excel 2016 to join text in cells together.
Initially, you may think that the function is not that worthwhile. After all, we already have the ampersand (& ) as well as the CONCATENATE function to join text together.
However, TEXTJOIN really shines when you want to join multiple cells together as you only have to specify the delimiter once.
The syntax is =TEXTJOIN(delimiter, ignore empty, text…)

textjoin function










Let me give you a simple example using a person’s first, middle and last name and their professional designation.
textjoin function



If we used CONCATENATE, the equation would be : =CONCATENATE(A2,” “,B2,” “,C2,” “,D2).
If we used  TEXTJOIN it is,  =TEXTJOIN(” “,,A2:D2)

textjoin text function










and this result would display:

Lisa Marie Brown CPA

Clearly, TEXTJOIN is a bit simpler and a bit more elegant if you have a lot of cells that you want to join together and you want to use the same delimiter throughout.
You can use different delimiters if you wish and it is still simpler than CONCATENATE as well. In this example, I just wanted spaces after each of the names but I wanted a comma before the professional designation so I used =TEXTJOIN(” “,,A2,B2,C2,”,”,D2) and Excel displayed  the following result.

Lisa Marie Brown , CPA

So, add TEXTJOIN to your arsenal of text functions if you have Excel 2016.



Economic Nexus – South Dakota Supreme Court Case

economic nexus

Economic Nexus

Update: Looks like many states are now moving forward with out-of-state sales tax.  My guess is that many states are going to set minimum revenue amounts so the small entrepreneurs and small EBay sellers won’t be impacted. Let’s hope.

8/2018- Here is the latest update and some of the state discussions going on:

6/2018 I just received the following information  from the Indian Department of Revenue:

” INDIANAPOLIS— The Indiana Department of Revenue (DOR) is announcing next steps after the U.S. Supreme Court issued its landmark decision in South Dakota v. Wayfair, Inc on June 21, 2018. 
Out-of-state retail merchants with annual gross revenues from Indiana sales exceeding $100,000, or 200 or more separate Indiana transactions will need to register and remit Indiana sales tax.  Pending resolution of a declaratory judgment action filed in 2017, DOR will begin enforcing Indiana’s economic nexus law on October 1, 2018, on a perspective basis. The Wayfair decision overturned the Court’s 1992 ruling in Quill Corp. v. North Dakota, which required a retail merchant to have a physical presence in a state to be subject to state sales tax registration and collection requirements.Indiana’s existing “economic nexus” law, effective July 1, 2017, is found at IC 6-2.5-2-1(c), and provides that a retail merchant that does not have a physical presence in Indiana shall collect the gross retail tax on a retail transaction made in Indiana if certain threshold requirements are met.
The law, which was challenged in the fall of 2017 and remains subject to a pending declaratory judgment action regarding its constitutionality, is directly impacted by the outcome of the Wayfair decision.Remote sellers seeking to comply with the laws of multiple states (including Indiana) should register with the Streamlined Sales Tax Registration system at Remote sellers seeking to comply with only Indiana’s economic nexus law should register through the online portal, INBiz, at”


In 2016, the state of South Dakota established an economic nexus standard for sales tax.  This standard requires sellers who meet certain revenue or transaction thresholds to collect and remit sales tax to the state even if they have no physical presence in South Dakota.  In September of 2017, the South Dakota Supreme Court held the state’s law to be unconstitutional.  However, in January of 2018, the U.S. Supreme Court agreed to hear the case with a ruling expected in the summer of 2018.  The case title is South Dakota v. Wayfair, Inc.

Since the 1992 U.S. Supreme Court case, Quill v. North Dakota, the standard for collecting and remitting sales tax to a state has been a physical presence in the state.  This physical presence, or nexus, is created by having facilities in the state, employees in the state or even sales personnel travelling to the state.

On-line sellers have benefitted from the physical presence requirement.   By being able to sell goods in states where they have no physical presence, on-line retailers have escaped the need to collect and remit sales tax in those states.

If South Dakota prevails in the case, the physical presence requirement could be replaced with economic nexus which is measured by a retailer’s revenue or number of transactions in a state.  This would require on-line retailers who were not previously required to collect and remit sales tax to do so.

The legal issue with the Quill case was the regulation of interstate commerce.  The ruling was that only Congress has the power to regulate interstate commerce and state action cannot restrict interstate commerce.  If the U.S. Supreme Court relies on legal precedent, it may keep the physical presence standard.  It may also recognize the realities of an internet economy where the physical presence standards could be construed as outdated.  Another option is to say that only Congress can decide the issue.

The stakes are high for both states and retailers.  Alabama and Washington are other states with an economic nexus standard.  Colorado has been trying to pass economic nexus and finally settled on requiring all Ecommerce companies with Colorado sales to submit a list of those sales to Colorado and to notify purchasers that they must pay Colorado use tax.  Idaho, Illinois, Louisiana, Mississippi, Nebraska, Oklahoma, Utah, Rhode Island and Vermont have also introduced legislation to create economic nexus.  If the court rules in favor of South Dakota, expect many more states to enact economic nexus standards.

This will be an interesting case to watch.

Find Files Easily

Are you an organized person? Is everything at your fingertips? If so, good for you. I, unfortunately, am a bit more unorganized and can spend quite a bit of time looking for a specific Excel file. I have found that adding keywords to a file when I create it, can help me locate it later if I don’t remember the name file. It is very easy to do.

use keywords



Click on File and then select
Click the Properties drop-down arrow located over on the far right side of the screen and then select Advanced Properties.

Click on the Summary Tab if necessary and enter keywords that relate to the file.  Click OK.

keywords in file













To search, simply go to the Windows Explorer window and in the search box, located on the far right of the window, type in one of your keywords.  Your file should display. You can also search on author and title as well.

search with keywords

Lots of options to find your file.

Passwords & Lucy the Retriever

Tips for Strong Passwords

Do you use strong passwords?  Are you sure?  They are the key to keeping your information safe and secure.  Since strong passwords are so important, a basic understanding of what constitutes a difficult to hack, strong password is essential.

A strong password should be long.  It should not be comprised of one word or a word followed by digits, dates or special symbols.  It really shouldn’t be an easy number combination like 123456.  Names, places and individual words in any dictionary should be avoided.

Why? Password hackers use dictionaries and names first.  Then, they tack on digits, dates and special symbols in an attempt to crack a password.  By the way, they use dictionaries in all languages, so don’t become complacent with your easy password in French or any language other than English.  Hackers will use a computer to accomplish this brute force hacking technique, so each attempt only takes milliseconds.  Easy word and numeric combinations can be cracked in seconds to minutes. has a password checker that provides an estimate of the amount of time it would take a brute force attack to crack a password.  Here are some estimates from this site:

1234 – is a top 1000 password.  It will be cracked in 0 seconds

123456789 – also a top 1000 password.  It will be cracked in 0-10 seconds depending on computer speed.

ILOVEYOU – Still a top 1000 password, but longer.  Depending on computer speed, it will take 0 seconds to 35 minutes to crack.

Here are some strategies for a strong password:

  • Pick a long phrase
  • Substitute certain letters with characters or numbers

Using the password checker from, let’s break down a phrase word by word to see how much more difficult it is to crack with the addition of each word or symbol.  The phrase will be “My Golden Retriever is Lucy”.


  1. My – 0 seconds
  2. MyGolden – 1 second to six days, depending on computer speed
  3. MyGoldenRetriever – 573 million to 573 trillion years
  4. MyGoldenRetrieveris – 2 trillion to 2 quintillion years
  5. MyGoldenRetrieverisLucy – 11 quintillion to 11 septillion years

As you can see, longer is better.  To make this phrase even stronger, substitute a number of symbol for 1 or two letters.  For this example, let’s use an @ sign for “G” and a 4 for “R or r”.

  • My@olden4etrieve4isLucy – 874 septillion years to 874 nonillion years.

That’s a pretty strong password.  It must be as I don’t even know how many zeros are in a nonillion.


Another strategy that may be used is to pick a phrase and only use the first letter of each word in the phrase.  Then, substitute numbers of special characters for certain letters.  Continuing with the above example, we’ll make the phrase a little longer.  The phrase will be:

  • My Golden Retriever is Lucy and she is a good dog

When we only use the first letter of each word, the password becomes MGRiLasiagd.  This password alone is not very strong.  According to the password checker at, the time to crack it is shown below.

  • MGRiLasiagd – 21 hours to 29 thousand years, depending on computer speed.

However, if we substitute a ! sign for each “i” and a @ sign for each “a”, the password becomes much stronger.

  • MGR!L@s!@gd – 40 years to 476 million years depending on computer speed.

A longer password is a stronger password.  The preferred method is a long phrase with the substitution of numbers or special characters for certain letters in the phrase.  Using the first letters of a phrase and substituting numbers or symbols for certain letters is not as preferable nor as strong as the use of a long phrase.  However, it can make an effective password (40 years is still a long time).

The key takeaways are to make your passwords long and don’t use one-word, easy passwords.  Never, ever use a password like “abc123”.  It’s easily guessed and can be cracked in seconds.


Current Expected Credit Loss (CECL) Standard

Summary of the Current Expected Credit Loss (CECL) Standard

On June 16, 2016, the Financial Accounting Standards Board issued Accounting Standards Update 2016-13, Financial Instruments – Credit Losses (Topic 326) Measurement of Credit Losses on Financial Instruments.  This has been codified in the Accounting Standards Codification as ASC 326-20.

This new standard replaces the incurred loss model of recognizing loss on loan portfolios.  The incurred loss model requires that it is probable that a loss has been incurred at the balance sheet date and that it can be estimated.  Instead, the expected loss model requires an estimate of the lifetime expected credit loss.  This lifetime expected credit loss is recorded as an allowance.

Major components of ASC 326-20 include:

  • Scope
    • Financing receivables
    • Lease receivables
    • Trade Receivables
    • Held-to maturity debt securities, loan commitments, financial guarantees, standby letters of credit, reinsurance receivables
  • Expected lifetime losses recognized upon recognition of the loan
  • Loss estimation considerations
    • Historical information
    • Current information
    • Supportable forecasts
  • Expected credit losses will be reported in current income through the Allowance for Loan and Lease Losses
    • Purchased financial assets with credit deterioration would be booked at face value with a noncredit discount (if applicable) and an allowance for credit losses.
  • Implementation:
    • Public Business Entity that is an SEC filer – 2020
    • Public Business Entity – 2021
    • Not a Public Business Entity (Private Companies) – 2021


How CECL Differs from FAS 5

FAS 5 has two components for loss recognition; (1) It is probable that an asset has been impaired and (2) The amount of the loss can be reasonably estimated.  This meant that to accrue a loss, something must have happened at or before the date of the financial statements that caused the loss to occur.  Expected future losses beyond those for which something already happened to cause the loss would not be recognized.  This loss estimation is known as an incurred loss model.

Under the incurred loss model, any estimate of loss relates only to those loans that have been impaired as of the balance sheet date.  So, it is an estimate of who has lost their job, who has large medical bills, or who has divorced that makes it probable that the customer is unable or unwilling to pay as of the balance sheet date.  It does not include an estimate of loss for those customers for whom nothing has yet happened to incur a loss.  The other component of the incurred loss model is that the amount of the loss may be reasonably estimated.

Under the CECL model, all expected losses over the life of the loan must be estimated and recognized when booking the loan.  Past events, current conditions and reasonable and supportable expectations about the future must be utilized to quantify the expected credit losses.  The loan assets on the balance sheet will be reduced by the lifetime estimate of losses.  This reduction will be accomplished through current earnings using the allowance for loan and lease losses.

Assume a pool of five year loans with the following incurred loss characteristics:

Year 1 2 3 4 5 Total
Incurred Loss 2% 5% 5% 2% 1% 15%


Using the incurred loss model, each year would have a valuation allowance entry that impacted the income statement through provision for loan losses.  The lifetime losses are spread out over a five year period in this example.

Using the CECL model, the total expected credit loss on the portfolio of 15% would be recognized when the loans are booked.  This results in a much greater impact to current income in many cases than the incurred loss model.

Both the incurred loss model and CECL require the use of historical information to support the loss estimate.  However, the incurred loss model has a much smaller “window” of loss to consider.  Accordingly, only loss data related to the period of time when the loss is incurred needs to be considered.  As a practical matter, most companies use 12 months of actual loss data as a starting point, then adjust this amount for any changes in company or overall economic conditions at the balance sheet date.

The CECL model will require that lifetime historical losses be maintained and used as the starting point for loss estimates.  If the company does not maintain lifetime loss information, this is a good place to start the CECL implementation process.

CECL also requires a forecast of future economic conditions and a methodology to use these forecasted conditions as an adjustment to expected lifetime losses.  Most published literature uses expected unemployment rate as an economic indicator to use as an adjustment factor.  Part of the CECL evaluation process will be to determine if there are any economic indicators that have a specific correlation to the loans being evaluated.

Another component of CECL not used in the incurred loss model is the necessity of determining the life of the loan(s).  Since CECL requires an estimate of lifetime losses, the actual lifetime of the loan(s) must be quantified.  CECL requires that prepayments be considered in the estimated lifetime of the loan, but extensions, renewals and modifications should not be considered.

The key differences between CECL and FAS 5 for loan portfolios are:

Incurred Loss Model Expected Credit Loss Model
Loss Recognition It is probable that a loss has been incurred and can be estimated All expected losses over the lifetime of the loan portfolio
Adjustments to historical loss Only adjust for conditions as of the balance sheet date Adjust for forecasted conditions over the life of the loan portfolio
Life of loan portfolio Not considered Must be quantified to estimate expected losses over the life of the portfolio
Content of allowance Incurred losses not yet charged off All expected losses over the lifetime of the loan portfolio that have not yet charged off


SUBTOTAL() and AGGREGATE() -TimeSavers

subtotal and aggregate - time saversMany people are only aware of the Subtotal icon and have never looked at the Subtotal function itself. After all, the icon, lets you add, average and count a list so who needs more? Well if you are one of the people who subtotal data a lot then you may want to look at the Subtotal function or the AGGREGATE() function.

Let’s start with SUBTOTAL().
There are 3 advantages of using the SUBTOTAL function rather than the icon.

  • First, the subtotal function allows you to display the subtotal wherever you want it to display on the worksheet rather than in the list itself.
  • Second, you can specify if you want Excel to subtotal all of the data in the list, even the hidden values, or to only subtotal the visible data in the list. That is powerful.

The syntax for SUBTOTAL is =SUBTOTAL(function_num, ref1,[ref2]…). The function_num indicates the mathematical operation you want Excel to do. For example, =Subtotal(1,B2:B50) tells Excel to Average the data in cells B2 through B50. Apparently the programmers sequenced the function_nums alphabetically rather than on frequency as you would normally expect that 1 would be to add – or at least I did. Anyway, I digress. 1 means to average, 2 to count and 9 means to sum.  By using the single digit function_number, Excel automatically includes all hidden rows and columns in the list.  If you use the corresponding 3 digit function_number, Excel subtotals only the visible list in the data. So, the function_num 1 includes the hidden rows, while 101 excludes them.  Below is a list of the most common function_num:


  • The third advantage of the Subtotal function is that it excludes other subtotals. So, for example, if you had a monthly report that has a lot of report sections such as Sales, Cost of Goods Sold, Operating Expenses, Non-Operating Gains(Losses) , typically you add up each grouping and then create a grand total. If you used =SUBTOTAL() on each of these groupings than all you need to do is use =SUBTOTAL() at the end of the report to get a grand total because SUBTOTAL() does not include subtotals when it totals!  How cool is that?  This is also a time saver if you need to create a new section for something new within the report as the grand total will automatically update if =SUBTOTAL() was used.


If you are excited about =SUBTOTAL() then check out =AGGREGATE() if you have Excel 2010 or higher. AGGREGATE() does everything that SUBTOTAL() does and more. While =SUBTOTAL() offers 11 functions, AGGREGATE() offers 19 functions. Some of the more useful new functions include SMALL() and LARGE(). In addition to offering you the option of ignoring hidden rows of data, it also allows you the option of ignoring  error values and/or subtotals.  This is key – how often have you tried to sum a column of data that has a #Div/0! Or a #N/A? Well, if you use AGGREGATE() you will actually get an answer instead of an error message in the total line.

The syntax of AGGREGATE() is similar to SUBTOTAL() but gives you more control over what you are totaling so it is more powerful.. It is AGGREGATE(function_num, options, ref1,[ref2],..]. A function_num of 1 still means Average while a 9 means Sum. The difference here is the options section. The option number tells Excel which values to ignore in the data list you are testing.









So, why didn’t I lead with AGGREGATE()? I started with =SUBTOTAL() because a lot of people are familiar with the Subtotal Icon and it is a bit simpler. However, if you are a manager of lots of data, I suggest you jump over SUBTOTAL() and use AGGREGATE().
Below is a simple example comparing SUM(), SUBTOTAL(), AGGREGATE().

Subtotal() aggregate()




DockDogs at Fair

dockdogs- state fair

We went to the  Indiana State Fair this weekend and had a blast as usual. It is a family tradition.
So much to see and do and eat.

  • Ducks, Goats, Cows, 4H Exhibits, Scavenger Hunts, Gardens, Soybeans, Corn hole, Giant Pumpkins, Tractors, Train
  • Dairy barn milk shakes, Honey Sticks, Corndogs, SweetTea, Ribeye Steaks, Roasted Corn on the Cob (my fav)

We even saw a pig farm via Virtual Reality Glasses which was very interesting.
The highlight this year though were the Dock Dogs.
Dogs competing to see who can jump the furthest over a pool of water to get their toy.
This is a very short video of the first dog competing. A whippet was the winner jumping almost 30 feet!
Click on the link below to see the first dog compete – The video may download to the bottom of your screen.

dockdogs2017.mp4   (4 seconds)

Here is a link to more information about them -on the State Fair website.Kelsey on tractor at fair

Joe looking at garden at fair

Sort Left to Right

Who knew how many different ways there are to sort?

Let’s talk about changing the Sort Option Orientation.

Typically you don’t have to change the orientation of sorting as the default works just fine. However, everyone in awhile, you have that one spreadsheet that has a mind of its own.
I have a sales report that is generated every month and the columns are never in the correct order. So, I would cut and paste every month to get everything to display the way I wanted, however, it was  very time-consuming and annoying.

Below are the steps to do this in a more efficient manner and forget all about that cutting and pasting.

Insert a column above your column headings and number them in the sequence you want them to appear.
Then select Data>Sort.
In the Sort Dialog box click Options and select Sort left to right.
Voila- your columns are sorted in the way you want them and it took you all of 30 seconds.

sort left to right

The default order is Smallest to largest.

Notice that you can also sort by Case Sensitivity as well.

This is a tip from my Must Know Excel Tips Tricks and Tools for CPAs course.


Vlookup vs Index Match -Which is Better?

Chris Chau asked 27 Excel MVPs and community experts their preference and why.
Check out the results. Which do you prefer?vlookup

If you want more information after reading Chris’ blog, check out my course Become a Lookup Expert.
It covers LOOKUP, HLOOKUP,  INDEX MATCH and work-arounds for people who love VLOOKUP.


VLOOKUP vs INDEX-MATCH: 27 Excel experts share their opinions


Using TRUNC()

This is an excerpt from our Excel CPE course – Must Know Excel tips Tricks and Tools for CPAs.

No Decimals
TRUN - no pennies

If you don’t want the pennies in your calculation you can use =TRUNC(), short for TRUNCATE, or =INT(), short for INTEGER,  to turn your data into integers.

The two functions work differently but return the same value when working with positive numbers. You will get different answers from these two functions with negative numbers. TRUNC() removes the fractional part of the number whereas INT() rounds down to the nearest integer based on the value of the fractional part.

In the screen shot below, -6.45 returns a different value using INT as it -7 is the lower value.



Understanding Excel Time Value of Money Functions

Understanding Excel Time Value of Money Functions

Below is an excerpt from our  Excel Time Value of Money Functions for CPAs course.

There are certain terms and conventions in Excel that are extremely important to understand prior to working with Excel time value of money functions.  The most important to understand are the terms used in Excel.

Most Excel time value of money functions contain four or five basic inputs.  They are:

  1. Pv – present value. Used for both single sums and annuities.
  2. Fv – future value. Used for both single sums and annuities.
  3. Nper – number of periods. Used for both single sums and annuities.
  4. Rate – interest rate for period. Used for both single sums and annuities.
  5. Pmt – periodic payment. Used only for annuities.

Functions may be entered directly or a function wizard may be used for input.  If the functions are entered directly, the required inputs and structure are below.

  1. =Pv(rate, nper, pv,fv, type)
  2. =Fv(rate, nper, pmt, pv, type)
  3. =Nper(rate, pmt, pv, fv, type)
  4. =Rate(nper, pmt, pv, fv, type, guess)
  5. =Pmt(rate,nper,pv,fv,type)

Keep in mExcel time value of money ;ind that, at a minimum, only three of the basic inputs are required to arrive at the correct answer.  Type (not one of the basic inputs) refers to when annuity payments are made (either at the beginning or end of a period) and, if omitted, the default is at the end of a period.  Guess (also not one of the basic inputs) is literally a guess of the correct answer and helps Excel narrow its calculations. It is not required, but may be necessary in the IRR calculation to arrive at an answer.

Most find it easier to use the function wizard as it is not necessary to remember the function inputs and you can see the function result prior to clicking “OK”.  For the Pv function, the function wizard is displayed below.

Excel uses a sign convention that indicates whether an amount is a cash inflow or cash outflow.  It gets a little confusing.  Basically, if you’re investing, that amount would be reflected as a negative amount – a cash outflow to the investment.  If you’re receiving a loan, that would be a positive amount as it is a cash inflow to you.  A cash payment for a loan (Pmt) would have a negative sign while cash received from an investment would have a positive sign.  Some people are confused when they compute a payment or a present or future value and it results in a negative amount.  However, there are certain calculations where the sign convention is very important.  This will also be covered in greater detail in the specific examples contained in the course.

Of the five (or six) function arguments that are available, make sure that if they’re not needed, they are set to blank or zero.  Anything other than blank or zero will enter into the calculation and make the result incorrect.  As an example, if you want to know the present value of a single sum and your inputs are Rate, Nper, and Fv, make sure that Pmt has a value of blank or zero as this argument is not needed.  While Type should have a value of zero as well, it is ignored in a single sum calculation.  However, in an annuity example, you would need to be more aware of the Type argument as it would have an impact on the result.

Excel does not have an option for compounding frequency.  In Excel, Nper must be the total number of periods and Rate must be adjusted so that it conforms to a per period basis.  As an example, if you’re dealing with a 5 year loan at an annual interest rate of 12% where payments are monthly, there are a total of 60 monthly payments in 5 years.  Nper in this case is 60.  Since the payments are monthly, Rate must also be adjusted from an annual rate to a monthly rate by dividing the annual rate by 12.

The Rate argument must be entered as a decimal.  If you want to enter 12%, it must be reflected as .12.  Having 12 as an input in this example would provide an incorrect result.

The “Type” argument is only used in annuity calculations and is used to establish whether the payment is made at the beginning of the period or at the end of a period.  The default in Excel is at the end of a period.  However, there are instances where the cash flow occurs at the beginning of the period and, in these instances, you must change the Type argument to 1.    In an annuity calculation, the resulting answer will be different depending on the Type argument selected.

While not required, it is suggested that cell references be used as inputs for these functions.  By doing this, it is easier to review and should result in a lower likelihood of input errors.  It also makes it easier to change inputs.

Top 100 CPA Blogs And Websites For Accounting & Tax Pros

Top 100 CPA Blogs 
top 100 cpa blogs

We are honored to be included in the Top 100 CPA Blogs And Websites For Accounting & Tax Pros. say that these are the  “The Best CPA Blogs on the Planet“.

So, if you are looking for information on some topic this may be a great place to start. I know I have already identified a few blogs that I plan to spend some time reading.

Take a look when you get the chance.  Just click on the award picture and it will take you to the site. Of course, since you are here definitely check out my site and my sister site as well.


RRI Function

Below is an excerpt from our Excel Time Value of Money course written by Joseph Helstrom,CPA.


RRI is a new function that was introduced in Excel 2013. It returns the equivalent interest rate for the growth of an investment.  The inputs required are the number of periods, the present value and the future value.
As an example, let’s say you invested $100,000 for 8 years compounded annually and the investment grows to a value of $150,000.  What is the equivalent rate of return?  In these examples, we will type the formula directly into the cell and not use the function wizard.
Start in the cell with =RRI( and then type the inputs as shown below.
RRI function




The answer:

RRI - Time value of money function

About 5.2%.

Remember, compounding periods are important.  What if the investment was for 8 years compounded quarterly?  You would need to adjust NPER for the quarterly compounding so it would be 8 years x 4 compounding periods per year = 32.  You will also need to adjust the result if the desired answer is an annual rate.  You can type a new formula by starting with =RRI( and then typing the new inputs or selecting the cell and pressing F2 to edit the existing formula.
RRI function



The answer:

RRI -Time Value of Money function



This is the answer for a quarterly rate.  When multiplied by 4 to translate it into an annual rate, the answer is .012751 x 4 = .051004 or about 5.1%.
The quarterly compounding slightly reduced the rate necessary to achieve the same result.

Dashboard Design

I just finished updating my Excel Dashboards course, Drive Your Dashboard with Excel,  and thought I would share an excerpt.

Dashboard Design

Creating a dashboard is NOT the same things as creating an Excel spreadsheet. By this, I mean that people tend to open an Excel spreadsheet and just start entering data. They worry about formatting and layout later – if at all.  (No, I am not talking about you.)
This is not true of creating a dashboard. The design of the dashboard is key.  Now, don’t roll your eyes. If you don’t know who your audience is and what the purpose of the dashboard is, it will not be successful. You will have simply wasted your time and probably confused your audience.

 To design a dashboard properly you need to know:

  • Who will be viewing the dashboard?
  • Who will be using the dashboard?
    • Viewers and Users may have different purposes
  • What they want to dashboard for – what measurements?
  • What metrics do you want to track?
  • What do users want to learn from dashboard/ how do they plan to use it?
    • High Level overview or detailed operational needs

Once you understand who your audience is and why you are designing it, you need to consider:

  • Where is the data located?
  • How is the data going to be retrieved?
  • Who will maintain and update the original data?
  • Who is going to update the dashboard’s data once it is created?
  • How should the dashboard be structured?

Design Layout

KISS- Everyone knows the Rule – Keep It Simple….

Dashboards, with their colors and graphics, usually resemble a magazine layout. Appearance is important but don’t get so focused on the graphics and layout that you lose the data.  This is becoming more and more important as dashboards gain in popularity and everyone is trying to make them look so pretty.
Edward Tufte, the acknowledged “founder” of dashboards, discusses basic techniques for improving the display of quantitative information. He has an interesting website which can be found at http://www.edwardtufte.

Below are some basic rules to keep in mind as you design a dashboard:

  • Plan and design a mockup so that you know what you are doing with your space.
  • If there is too much data, it is difficult to see what is really going on so keep it clean and simple.
  • Do not use a lot of different colors.
  • Do not use a lot of different fonts.
  • Format consistently – For example, do not use accounting format in one section and currency format in another section.
  • Select a single background color for charts or make the chart areas transparent.
  • Use custom formats for large numbers and/or abbreviate where possible.
  • Use white space to indicate sections to create the idea of more space (no borders).
  • Only include needed graphics such as charts.
  • Don’t get cute- it is not a PowerPoint presentation.
  • Generally, the top of your dashboard will contain overview information and more specific information should be found underneath it.

Dashboard Structure

dashboard design

Basically, you need to break your dashboard area into sections.
At an absolute minimum, you should have 3 sections in your workbook/files:

1.Data/Input Section

  • The Data section is where the raw data is. If you are importing from other programs, such as Access or QuickBooks, this is where you would want the data to reside

2. Analysis Section

  • The Analysis section contains the formulas, which pull the data from the Input section, and organizes it so that it becomes information. This is the section that will feed the Presentation section.

3.Dashboard (Presentation) Section

  • The Presentation section pulls and/or reshapes the key information from the Analysis section into the actual Dashboard.

Other sections to consider adding include:

  • Control Section
    • Many people recommend having a Control section or sheet that would contain informative that would be used multiple times in a dashboardsuch as listing of dates, listing of products or sales regions. In other words, data that you could reference or link to multiple times.
  • Help Section
    • Some people recommend having a Help section or sheet, which can be very useful, if one or more people are maintaining the dashboard You can also include comments and explanations of calculations there as well. You may think you will remember why you put a formula together a certain way, however, there is a good chance that you may forget what your reasoning was a year later.
  • Table of Contents or Reports Page
    • A Table of Contents can be very useful if you are using a lot of worksheets. You may also want to consider having a Reports Page and then pull the Dashboard from that page.   Make sure your set-up allows for flexibility.

For example, your raw data section may contain Sales by month by sales person by product for last year and this year as well as budget numbers.  In the Analysis section, you may have created a pivot table that summarizes sales by product for the month and quarter. The presentation (dashboard) section may display the quarterly sales by product as compared to budget or last year.

Keeping all the data together in one workbook is obviously the easiest method; however, these sections can all be in separate workbooks depending upon your preference and the volume of data and analysis. This is one of the reasons that preplanning and layout are so important. You don’t want to be in the middle of your creation and suddenly realize that it won’t work or that you forgot an important component that now needs to be incorporated in somehow.

When I reread this section, I realized that most of the information included here was also included in a course I wrote earlier on designing a database.  I cannot emphasize enough that preplanning and design are the most important aspects of a dashboard.  If you don’t know who your audience is and what they want the information for then you have just wasted a lot of your time as well as theirs.

Leasing Question – Anyone have a recommendation?

Hey, I had a question and was hoping that someone could answer it for me.

I have someone looking for a low-cost system to to calculate lease assets and liabilities and related accounting under the new lease standard.
Any recommendations are appreciated.


accounting for leases cpe

Fixed Decimal Places


I have a Fixed Decimal Place tip, in my Must Know Excel Tips Tricks and Tools for the CPA Ebook.  Eric Robinson read my tip and sent me his own tip on fixed decimal places.
If you are unfamiliar with the Fixed Decimal feature in Excel, it is pretty cool.
 If you need to enter columns of numbers, with a fixed number of decimals, into a worksheet, you can have Excel enter the decimals for you, using the fixed decimal feature. So, if you set the decimal point to 2 and then type 12345, Excel will display 123.45. It is very easy to use but you do have to turn it on and off so Eric’s macro tip is very handy.
I have a link below if you wish to read more about the feature.
Eric’s macro allows you to switch back and forth between no decimal place and  decimal places. Switching back and forth can be tedious if you are, for example, entering a long column of check numbers (no decimal) and check amounts (2 decimal places).  I have shared his macro instructions below. Thanks so much  Eric.

Sub ToggleAutoDecimal()
‘ ToggleAutoDecimal Macro
‘ Macro recorded by Eric Robinson to handle fixed decimal places
    Application.FixedDecimal = Not (Application.FixedDecimal)


End Sub

Here is a link to the instructions on how to use fixed decimals on my Excel-diva blog.

fixed decimal place


Excel has a number of date functions.  Unfortunately, they don’t all work if Excel treats the date as text.  When working with downloaded data, sometimes the date must be converted from text to a format recognized by Excel.

One method is to use the DATEVALUE function.  This function converts a date in text format to a number that represents the date in Excel date-time code.
Open the exercise workbook and select the Datevalue worksheet.

In cell A1, is the text ‘12/31/11.  In cell B1, type =DATEVALUE(A1)
datevalue 1

The value in cell B1 is the Excel date-time code.  However, it requires some formatting to make sense.
Format cell B1 as a date either by using the Home menu and Number selection or the Home menu Format Cells selection.

datevalue2Format B1 in the date format of xx/xx/xxxx.datevalue3

You can tell that the date in cell A1 is in text format due to its left justification in the cell while the date in B1 is a date by its right justification in the cell.

This is an excerpt from our Must Know Functions for CPAs.

Quick & Dirty Charts


There are a couple of ways to create a chart. We are going to discuss the  two quickest methods. This is an excerpt from our course  “Creating Effective Charts in Excel“.

Often you just want a quick and dirty chart –one that doesn’t have to look that great since it is just for you. In this case, you select your data and press the F11 key and a full size chart is displayed on a separate sheet.  If you want the chart to appear on the same sheet as your data, then press ALT+F1 instead of F11.

It really is that easy!


 Using F11

1. Open your worksheet.
2. Select the data you want to chart.

You want to make sure not to include totals when you select your data as it will skew the chart.
You want to select your column headings so that you have labels in your chart.
3. Press F11.

A full size chart appears on a new worksheet entitled Chart 1.

How easy was that?

You will notice that it is lacking titles and other documentation that would be needed for other viewers but for your individual use it will generally suffice.


1.Open your worksheet.
2. Select the data you want to chart.

You want to make sure not to include totals when you select your data as it will skew the chart.
You want to select your column headings so that you have labels in your chart.
3. Press Alt key and the F1 key at the same time.

You should see a small chart appear on the sheet with your data. You will notice that it is lacking titles and other documentation that would be needed for other viewers but again -for your individual use it will generally suffice.
The chart is considered a  graphic object so you can move and resize it if it is in the way of your data.

quick & dirty charts







Check out our course Creating Effective Charts in Excel.

Searching on Google

Google Search Tips

Searching on Google

Many of us search on Google every day for research and/or fun.  There are certain embedded features in Google that can make your search more specific and help return more items that are of interest to you.  With the ability to use more specific search parameters, the answer you seek is more likely to be on the first page of the results rather than on a later page.

This list is in no particular order.  Hopefully, you find these features and tips useful and productive.

  1. Use quotes to search for specific words or phrases in a specific order. If you put the words subprime collateralized debt obligations into the Google search box without quotes, you are likely to have results that contain one or more of those words, but not all of those words in that specific order.  However, if you enclosed the phrase in quotes to be “subprime collateralized debt obligations”, you would then receive results that contained only those words in that specific order.  This is an important tip to use in combination with other tips that follow.
  2. Use a minus sign to exclude a word or words from your search. If you search for the phrase, loan pool, you may receive results that refer to swimming pools.  However, you can eliminate any results that contain the word swimming in them by typing loan pools -swimming in the Google search box.  To use the minus sign, it is important that there is a space before the minus sign for this feature to work properly.  If you want to eliminate more than one word, use another minus sign and the word to be excluded.  Continuing the earlier example, if you wanted to eliminate both swimming and mortgage from the results, type loan pools -swimming -mortgage.
  3. Use a colon to only include certain sites in your search. Accounting research tends to gravitate toward sites like or  So, if you only want results from these sites, use a colon and then the site for which the search is to be limited.  As an example, if you want information on lease accounting and want to restrict it to information on, type lease accounting  The space before the colon is important for this feature to work.  Results are even more specific if is enclosed in quotes.  The most specific search is lease accounting :””
  4. Use an asterisk (*) as a wildcard. If you don’t know a specific word in a phrase, place the phrase in quotes and use an asterisk (*) as a wildcard for Google to find.  As an example, you remember that under the old capital lease rules, a certain percentage of the estimated useful life made the lease a capital lease.  In the Google search box, type “* percent of the estimated useful life”.  Your answer should be in one of the results.
  5. Use “OR” for multiple search criteria. If you want information regarding multiple criteria, the OR feature may be used.  As an example, type CPA credential OR cgma in the Google search box.  You receive information regarding both credentials as well as information that compares both credentials.  The capitalization of OR is important for this feature to work.
  6. Use “..” to search for a range of numbers. The best example is if your search is limited to specific years.  Assume that you wanted to see the AICPA Code of Professional Conduct that was in effect in the years 2011 through 2013.  In the Google search box, type AICPA Code of Professional Conduct..2011 2013.  Once again, for a more specific search, AICPA Code of Professional Conduct may be enclosed in quotes.
  7. Use Google to do the math. Simply type in a simple or complex equation in the search box and an answer appears.  Google applies the correct order of operations to the result.  Remember that the order of operations is parentheses, exponentiation, multiplication, division, arithmetic and subtraction (PEMDAS) So, if you type 9+5*6-3 in the Google search box, the correct order of operations is applied and the answer is 36.
  8. Use define. You can define any word in Google.  Simply type the word define in front of the word.  You may want a good definition of the word workpaper.  Type define in front of the word in the Google search box.  Without define in front of it, the results are not confined to a definition.
  9. Convert money. Type the denomination that you have and the denomination in that you wish to convert to in the Google search box.  If you type $100 in Euros in the search box, the Euro equivalent of $100 results.
  10. Use Related: to find a related web site. If you like the contents of a web site and want to know if there are other similar web sites, use the Related: command.  For example, I like the Wall Street Journal web site,  To find other similar web sites, I would type in the Google search box.  The results are a list of web sites similar to

If you found this blog interesting, check out our new CPE course, Optimizing Search Results with Google.


LEN Function

LEN Function – Counting Characters in a Cell


LEN() is one of those functions that you wonder about. LEN is a text function and depending upon what you are doing it is so useful. It counts the number of characters in a cell.  I use it all the time if I want to drop a minus sign at the end of a number of  if I imported values that have turned into text because the numbers have a CR after them.

In this example, I had a column of stores and they all began with Contoso. Since it was a bit redundant I wanted to get rid of the word Contoso at the beginning of each store name.

LEN function

Now, using =RIGHT(A2,18) works for the first and second store as they are 18 characters in length when Contoso is excluded but the Kennewick Store only needs 16 characters instead of 18 so the formula doesn’t work for that store or some of the others on the list that are of different lengths.
An easy way around this is to combine RIGHT with LEN.

=LEN(A2) = 26 counts  26 characters in cell A2.
Every store name has the word Contoso  in it and Contoso has 7 characters and that is what we want to eliminate.

So, =RIGHT(A2,LEN(A2)-7) is the answer.
When you break it down the formula ends up being =RIGHT(26-7) for the first store. 26 characters less the first 7 characters. Same for the second store. Now the store name in A3 has fewer characters so LEN counts 23 instead of 26 and ends up 23-7 = 16 characters  so it would display Kennewick Store. As you copy it down, LEN will count the number of characters that comprise the various store names and then the formula will subtract the first 7 characters.  Pretty slick and definitely useful.

X Axis Units in Excel Charts

X Axis Units- Excel Charts

I thought I would share this problem I ran into charting so that you don’t start banging your head against the wall as I considered doing.
I have used this same data in a many different versions of Excel and never had a problem until Excel 2016.
I charted some data and I went merrily on my way until at some point I noticed big problems with my X axis.
Do you see the problem? Take a look at the X axis. At first glance, it looks okay and then… hmm.. issues here.

Excel 2016, by default, apparently uses days so it is showing the first day of the month.
Unfortunately, most businesses are more interested in the last day of the month.chart units - dates

To fix this, right-click on the X axis and select Format Axis and change the base from Months to Days.
x Axis - format axis

Your chart should now correctly reflect the data from Column A.

revised chart - x axis




I am in the process of updating my Pivot Table course and decided that I take another look at  the GetPivotData function.
GET PIVOT DATA is an Excel function that pulls data from your pivot table. I personally don’t use it  because if you hide a field you referenced in GETPIVOTDATA, the function stops working. Sometimes, just rearranging the fields in the table can impact the function as well. However, I did want you to be aware of it.
If you try to multiply a total in a pivot table, you will immediately see that Excel automatically references the GETPIVOTDATA function.  This is a problem because if you copy the formula,  you will get the same answer for the entire column or row.
In the example below, I opened Pivot_Rearrange.xlsx and removed the Product Name field from the Pivot Table.  Next I  clicked on cell C5, as I wanted to multiply B5 by 1.10 to estimate next year’s order amount by carrier.

Instead of showing =B5 it shows =GETPIVOTDATA(“Order Amount”,$A$3,”Ship Via”, “FedEx”)*1.10

Pivot Table Function GETPIVOT



Notice that A3 is treated as an absolute cell reference. If you copy the formula down,  you will get the same answer for all of the Ship Via carriers.  As a work around, I know many people who copy and paste the pivot table into another workbook if they want to do any math on the pivot table.  However, I found a great tip on the website on how to avoid this problem.

  1. Go up the Options icon and click the drop-down arrow to the right of it.
  2. Uncheck Generate GetPivotData.

Pivot Table- GETPIVOT 2




Now, if you go back to cell C5 and recreate the formula, you will see  the formula bar now shows =B5*1.10. It will now copy down correctly. (Excel will not correct the GETPIVOTDATA formula so you need to delete it and then type in =B5*1.1.)





The best part is that once you uncheck it, Excel remembers so you do not have to keep unchecking the Generate GetPivotData icon every time you want to use a formula that references the pivot table.
This is an excerpt from my new Pivot Table Course that will be out the first week of June 2016.


The AGGREGATE Function

The AGGREGATE function is a very powerful function for summarizing an array of data.
It combines 19 different ways to summarize data into one function!
While it is a powerful function, my favorite reason for using AGGREGATE is its ability to SUM data with errors. 

The syntax for the AGGREGATE function is =AGGREGATE(function #, options, array).
The function #’s are listed below:

aggregate function



















As you can see, your options permit you to ignore nested subtotals, hidden rows as well as error values.
One of my biggest pet peeves is trying to sum data that has error values.  The AGGREGATE function makes this easy.
Assume yousum column with errors have data in cell A1:A6, and that you want to sum it.
However, because there is an error value in A5, the SUM function will return an error as shown in the screenshot on the left.
You can easily work around this by using the AGGREGATE function.



In this example, I clicked in A7 and  typed =AGGREGATE(

  • A list of the various function #’s appears.  Note that if you elect to use the Insert Function dialog box, this handy list of functions does not appear.  We want to SUM the data, so function # 9 should be used, followed by a comma.



  • After the comma, a list of options is presented.
  • AGGREGATE DIALOG BOX OF OPTIONSIn this case, use option number 6, Ignore error values and place a comma after this.





  • Lastly, identify the array to be summed.  This will be accomplished by using the cursor to highlight A1:A6.
  • Close the parenthesis.
  • Press Enter.








The “Must Know” component of the AGGREGATE function is how to SUM data with errors.  It is a very powerful function, though.  You may find other uses for it in your professional assignments.


This is an excerpt from “Must Know Functions for CPAs”.

-Professional Ethics- CPA Case Study

Professional Ethics – A Case Study for CPAs in Business

CPAs in business have unique threats to ethical compliance. Many times, these threats come from their employer. This makes for a challenging environment when confronting a superior over an ethical quandary as it could result in the dismissal of the CPA from his/her employment or result in the CPA resigning from his/her employment. Maintaining professional ethics can be difficult. While the loss of a client is important to the CPA in public practice, the loss of the sole source of income of the CPA in business is much more frightening and impactful. This makes compliance with professional ethical standards challenging in difficult situations. In some situations, it can be a choice between ethics and providing for your family.

Ethics - Decision Time

Ethical dilemna

Therefore, being reminding of the compliance threats and compliance obligations is of utmost importance to the CPA in business. In using case studies, some cases provide a clear answer. Stealing is wrong…Period. However, there are many situations where the answer is not as clear. These situations present a quandary which must be resolved.
One such case study follows.
As the controller of a small distributor and a CPA, you are closing the books for the year. The owner, who is well past retirement age, has announced that a buyer for the business has been secured. Once the sale transaction has been completed, the members of senior management, including you, the controller, will receive a significant bonus. This bonus will be the equivalent of 150% of one year’s salary.
While reviewing the balance sheet, you notice that there are a number of expensive inventory items that are aged. Usually, inventory turns about once every 90 days, and these have been on the books for almost a year.
You approach the VP of Operations with your concern. He tells you that a competitor had devised a better product at a much lower cost. This product was unsaleable at any price. However, due to the cost of this product, writing it off would be a material adjustment and would certainly impact the purchase price and may put the entire sale in jeopardy. He says that it would be in everyone’s best interest to ignore it. “The buyer did their due diligence. If they missed it, it’s their problem, not ours.”
You bring the situation up to the owner. He says, “Don’t screw this up. I’ve worked a lifetime for this deal and I’m paying you a lot of money to make it happen.”
You know that everyone will be upset if the sale is not consummated or the bonus amount is reduced. Personally, you want the bonus. While you can always find another job, the bonus will buy a new car and pay off your student loans. But, you also know that the inventory is not properly stated and this will be determined at some point in time. You can always say that you just missed it. After all, you’re just the controller, not an operations guy.
What should the controller do?
Ethical Core Foundations to consider
Integrity and objectivity – These core foundations require the CPA to be honest, unbiased, not be influenced by personal prejudices and not subordinate judgment to personal gain. This case presents what is right – to value the inventory properly by writing it off or using a valuation allowance, versus ignoring the facts, as discovered, for personal gain.
Due care – Requires that the standards of the accounting profession are upheld at all times. Ignoring a known fact does not uphold the standards of the profession.
Threats to Ethical Behavior
The threats in this case are self-interest, undue influence and advocacy. The self-interest threat manifests itself in the desire to obtain the promised bonus. It is a significant amount of money. Undue influence come into play in the form of implied or actual coercion from the owner and company officers. They also have a significant stake in the success of the transaction. Their self-interest may result in coercing you.
Since there are no company safeguards noted in this case, the only possible safeguards are those created by the profession. A company safeguard would be a board of directors with whom a concern may be shared or an audit committee. The owner of the company has already made his wishes known. The only safeguard in this instance is your training in ethical behavior.

Code of Professional Conduct Rules
The AICPA Code of Professional Conduct addresses Integrity and Objectivity, stating that a member shall maintain objectivity and integrity, shall be free of conflicts of interest, and shall not knowingly misrepresent facts or subordinate his or her judgment to others. The Code also requires the general standard of Due Professional Care which requires that the CPA use reasonable care and diligence in the performance of work. The Accounting Principles Rule would also require that inventory be properly valued in accordance with GAAP. Lastly, negligence in the preparation of financial statements and records is an Act Discreditable.

professional ethics for cpas

Professional Code of Conduct

Example Thought Process
It is pretty clear that knowing there is an inventory issue and not recording the appropriate adjustment violates the core foundations of the CPA profession as well as many specific rules applicable to the CPA in business. You can try to tell yourself that you can plead ignorance. However, that would not be ethical. In this case, the ethical path would be to insist that the inventory adjustment be made. If this is refused, then you should disassociate yourself from this entity. While a big bonus sounds very nice, the money is fleeting. Discovery of unethical behavior can ruin your entire career.
For a variety of cases discussing the ethical obligations of the CPA in business, try the Ethics for CPAs in Business course at

– Privacy of Tax Return Data

Tax Return Data – Privacy Issues

I read an interesting article the other day on the privacy of your tax return.
Everyone, of course, needs to be aware that your tax-return information can reside in many places, whether you are doing just your own taxes or those of others.
The IRS share information with state tax agencies, law enforcement and others, which is sometimes required by law, other times not. However, it says that it tries to limit sharing to the relevant needed information.
As you know, all tax preparers, including CPAs who prepare and sign returns for clients, have strong privacy and ethics rules. Many state boards and the AICPA specify a minimum number of years’ tax return information must be maintained but there is little discussion concerning a maximum period.
If you file electronically, the files are transmitted to the IRS by the use of outside firms such as Intuit (Turbo Tax) and H&R Block or commercial firms such as Thomson Reuters.
According to the IRS, different transmissions have different retention policies but again no limit on how long these tax software companies and others can retain the information. A good question to consider, is if you change software vendors, when does that first vendor stop holding onto the data and what do they do with it?
It doesn’t sound like that big a deal but given the treasure trove of data and information and the recent success of so many cyber thieves, it is something to consider. So, where does that leave you?

Privacy- man on desert island

man sitting on a deserted island by himself.

Honestly, on a desert island by yourself. Rules on data breaches and data privacy are generally covered by state rules and regulations and there are a lot of differences within some states. Some State Boards of Accountancy discuss deliberate privacy violations, in their ethics rules, but the area of retention by third parties, electronic transmissions and data breaches is just beginning to be discussed.
Just food for thought – if you do taxes for clients, you may want to explore your state rules and regulations a bit and understand your responsibilities.

New Lease Accounting Standard

ASU 2016-02

lease accounting standards

a binder with the word leasing on it.

In February, 2016, the FASB released its new lease accounting standard, ASU 2016-02.
This standard changes most elements of lease accounting for lessees and practically eliminates the use of those leases currently classified as operating leases as an off-balance-sheet financing vehicle, except for short-term leases (those with a term of one year or less).

Under the new standard, leases with a term of greater than 12 months will require the recognition of a right of use (ROU) asset and a lease liability. The asset and corresponding liability will be initially measured by the present value of the lease payments. The discount rate used in this calculation is the rate implicit in the lease or, in the absence of an implicit rate, the lessees incremental borrowing rate.
Leases will be classified as either a finance lease (similar to today’s capital lease) or an operating lease. For finance leases, the ROU asset will be amortized but interest expense on the lease liability must also be computed. This will result in the recognition of both interest expense and amortization expense. For operating leases, the ROU asset will be amortized on a straight line basis, resulting in only lease expense. In both cases, the corresponding lease liability will decrease as payments are made.
Implementation of this new standard for public companies will be those years after December 15, 2018. For non-public companies, the standard is effective for years after December 15, 2019.
The IASB also published new lease accounting standards in January, 2016. The IASB adopted an approach that requires lessees to account for all leases as finance leases, except short-term leases. Check out our new course by Steven Bragg – Accounting for Leases.

3 Most Common Financial Reporting Errors

Three Most Common Reasons for Restating Financial Statements

reporting errors due to complex rules

2 women looking at complex calculation

I just finished reading an interesting article in the Wall Street Journal on financial reporting.
Did you know that over 650 companies filed financial revisions or restatements last year?
In most cases, it appears that the reasons for the misstatements has more to do with confusion with complex accounting and tax rules than anything else.
I am sure that not many of you are surprised by that conclusion.  Adding rules to rules seems to be the norm these days.
Can you guess what the 3 most common financial reporting errors are?

I am sure almost all of you immediately guessed Tax which is correct; however, surprisingly, tax  is not the most common error.
How many of you guessed the other two?
The first is  Debt and Equity and  the second is Cash Flows.
Accounting for Debt and Equity errors are often due to dealing with complicated hedging and derivatives which
can be difficult to understand.
Cash flow errors can be easy to make- (my least favorite area of accounting!).
Taxes – well need I say more? It seems as though the tax code grows more complex on a daily basis!
Just a few reasons why continuing professional education remains so important. When you have a chance check out our courses on cash flow, hedging and derivatives and our tax section which is rapidly growing.

Source Data in a Pivot Table

Source Data – Pivot Table

When you close an Excel file, containing a pivot table, Excel can save the source data in the file or clear it from memory. If you go to Options>Pivot Table Options you will see that Excel by default saves the source data with the file.
source data
The advantage of this, is that the file will open a little quicker and you do not need to refresh the pivot table. The disadvantage is that if you copy the sheet containing the pivot table and email that sheet to someone then they have access to all the source data! Another disadvantage is that your file is larger.If you uncheck the Save source data with file, your file will be smaller, but will take a little longer to open as Excel needs to rebuild the pivot cache of data. If you do uncheck the box, it is recommended that you check the Refresh data when opening the file.   Microsoft keeps moving this option icon around.  if you can’t find the Option icon on the ribbon, try right-clicking in the pivot table itself.  Check out our Excel CPE course on Pivot Tables.


Date Function

EOMONTH screenshot

screenshot of a function dialog box

EOMONTH displays the serial number of the last day of the specified month.
You can use this to calculate maturity dates and due dates that occur on the last day of the month.
In the example below, I have 1/2/2016 in cell A1. In another cell, I have typed =EOMONTH(A1,2) which tells Excel to look at the date in cell A1 and display the serial date of the last day of two months out which is March 31, 2016.
I keep saying serial number. In the screenshot below it shows 42460 as the answer even though in the bottom of the dialog box you can see the Formula result is 3/31/2016. If you want to see the date format then you need to go to the number format and select Short Date or some other date format.
If I had typed =EOMONTH(A1,1) it would have displayed the serial number of one month out which would be February 29, 2016.  If I had typed =EOMONTH(A1,-1) then it would display 12/31/2015.

Ways to Sum Data with Errors

Excel Techniques to Sum Data with Errors

oops- signThere are times that you need to know the sum of numeric data even though there may be errors in it.  The most common of these error values are #DIV/0!, #NUM!, #N/A and #VALUE!.  #DIV/0! refers to a situation where Excel is trying to divide by zero or by a cell that is blank.  This is mathematically impossible, hence the error value.  The #NUM! error value refers to a situation where there is a problem with the underlying formula and the #VALUE! error value most often occurs when Excel is trying to perform a mathematical operation with text.
There are numerous functions and techniques in Excel to sum data with errors.  Some have been recently developed by Microsoft and are much easier to use than the older techniques.  We’re going to start with the oldest technique first, then progress to the newer, easier techniques so that we cover all the versions of Excel.
Oldest Technique – ISERROR
The ISERROR function in Excel evaluates a cell and returns TRUE if there is an error and FALSE if there is not an error.  Because you only have a TRUE value or a FALSE value to work with, this function must generally be paired with an IF function to be useful.  The IF function in Excel is structured so that there is a value if TRUE and a value if FALSE.
Here’s an example:  More…..

Go To Special

The Special Uses of Go To Special in Excel

Hidden on the Home ribbon in Microsoft Excel, within the Find & Select Button, is an Excel feature that has some very special and generally unknown uses for the CPA.  The feature is Go To Special.  It’s the fourth option on the list when the Find & Select button is clicked.
Go To Special


Steps to an Optimized Finance Function

Optimizing a Finance Function

The following steps, once executed, will enhance the value of the finance function in any organization by ensuring that decision useful data is produced timely and accurately.
Focus on internal controls to ensure that data is accurate
Proper internal controls will help ensure that data is accurate. This is the most important, but also most neglected, aspect of financial management. Bad data leads to bad decisions.
Streamline accounting processes
The purpose of streamlining accounting processes is to minimize the number of times people need to “touch” data. Any time data is being keyed into spreadsheets indicates an opportunity to streamline. Identifying bottlenecks and accompanying solutions with a focus on automation is an important element of this step. The use of checklists, recurring journal entries and other “basics” will also contribute greatly to a more streamlined process.
Accelerate financial reports
Good internal controls will produce reliable data. A streamlined accounting process will make the data accessible on a timely basis. If you’ve got those steps nailed, it’s not a stretch to accelerate your financial reporting timeline.
Meaningful budgets and forecasts with accompanying analysis
Everyone needs a budget, right? Budgets are a roadmap to your business goals and, like using any map, you need to track your progress toward your intended goal. The budgeting process is very detailed and results in a detailed plan. Forecasts should be viewed as more frequent, higher level updates to the budget with comparisons to both the budget and prior year. Both should be accompanied by appropriate analysis.
Identification of key performance indicators to be measured and reported daily/weekly/monthly
Key performance indicators are the drivers of your business. This is information that you absolutely need to know to make business decisions. Each business has its own drivers and “hot buttons”. Some need to be reported daily so that business managers are making decisions with the best information possible. Others may be reported weekly or even monthly.
Analysis is a great value added function. Proper analysis can answer current questions such as, “Why are margins declining when sales are up?” to “Should we enter into this new line of business?” Proper analysis is also essential to pricing decisions. For many businesses, a Price, Volume, Mix, Cost analysis is essential to understand the impact of these elements on margins.
This is the ultimate goal. To have all preceding steps functioning smoothly so that time may be taken to formulate strategy and quantify the impact of strategic alternatives.

Gantt Chart for Project Scheduling in Excel

Gantt Chart using Excel

A Gantt Chart is used in project scheduling.  It takes each activity and provides a sideways bar to visually show when that activity occurs within the total project.Excel can be used for Gantt Charts using the IF function, the AND function and Conditional Formatting.  Assume that your project consists of the following:
figure 1

To continue the chart, add the weeks to the right that will be used to represent which project activity occurs during each time period.

figure 2

The IF function can be used to determine in which week(s) an activity occurs.  Using the first activity, we want to know if Week 1 in cell D2 is both greater than or equal to the start week in cell B3 and less than or equal to the end week in cell C3.  Put in plain English, we want an IF function that says IF D2 >= B3 AND D2 <= C3, then do something. Fortunately, Excel has an AND function that can be combined with the IF function.  The syntax for the AND function is =AND(logical 1, logical 2, logical 3, etc.)  In this case, the AND function would be =AND(D2>=B3,D2<=C3).  This says that the 1 in cell D2 must be both greater than or equal to the 1 in cell B3 AND less than or equal to the 2 in cell C3. When the above is paired with the IF function, it looks like this:
figure 3

Now, we need to know what to do if the IF condition is TRUE.  In this example, let’s put the word “RESERVE” in the cell.  It can be any word or number, as long as it is consistent.  If the condition is FALSE, let’s put a blank in the cell.

Press enter.


Now, it would be nice to copy this formula.  However, when a formula is copied, the cell references are relative.  This means that if the formula in D3 is copied down to D4, all the row references are adjusted down one row.  If that happens, we’re no longer comparing the 1 in cell D2.  Now, we’re comparing to the IF function in cell D3.
To overcome this problem, Excel allows us to use absolute cell references.  Absolute cell references hold a particular row or column (or both) in place when formulas are copied.  It is done using a $ sign in front of the row and/or column being used in the formula.
In this case, if the formula is copied down, we know that we always want the amount in row 2 and also always want the amounts in columns B and C.  Put a $ sign in front of the 2 in D2 and in front of the references for columns B and C.gantt6

Press enter.  Copy the down to the end of the activities.


Now, with the range D3:D11 selected, grab the lower right corner with your mouse (you should see a plus sign), click and drag over to column P.


Give it the sanity test to make sure that where the word RESERVE is reflected that it represents a scheduled activity. While this would work, it’s not a real Gantt Chart.  A Gantt Chart uses horizontal bars to reflect a scheduled activity.  This is where the Conditional Formatting is used.Conditional Formatting will color the contents of a cell or the entire cell based on a defined condition.  In this case, we’re going to color the entire cell black if it contains the word RESERVE.Keep the area D3:P11 selected.  Click on Conditional Formatting in the Home ribbon at the top of the screen.  Then, click on Highlight Cells Rules, then click on Equal to.  The following dialog box will appear:GANTT9


In the EQUAL TO: box, type RESERVE.  In the drop down box following “with”, click on the drop down arrow and select custom format…
Select the Fill tab and then select the color black for the color options. GANTT10

Click OK.  Click any other area of the spreadsheet to deselect the range and your Gantt Chart is ready for use.



It’s an easy way to create a Gantt Chart without special software.  You just need to know the IF function, the AND function, and Conditional Formatting.  We also covered absolute cell references to make it easier to copy the formulas down and across.

Worksheet Design Tips

Excel Tips on Entering Data and Formatting

Brian Cane’s Excel Tips show how to effectively enter information and to format more effectively.
His source – a public domain Ibbotson (the famous Nobel Laureate) workbook. Please click on the attached sheet to view.
A go-with Tip1 is when a formula references the cell above.
So the cell above is the header and causes the formula to #error! and that is why users have a different formula for the first cell in the column. In my example tip1 in the prior attachment I show how to deal with the situation when the general formula requires a 1. When the formula needs a zero (most often the case) then use the N function.

So if for example you have a common BoY to EoY (beginning of year in say column B to end of year in column F) progression where you code B2=0 and B3=F2 and fill down because coding B2=F1 will give a #Value!. But if you code B2=N(F1) you can fill that down because N(“EoY”) will return 0 and N(any number) will return the number. Thus avoiding different row1 formulas.
Thanks Brian for the tips.
I would like to add to Brian’s tips and emphasize that you should always check your formulas and make sure they are consistent and correct.
If you make the first entry a different formula, it is easy to forget that and you may try to copy that first formula down at a later point in time which could result in an incorrect answer. People sharing your worksheet may copy or use the original formula not realizing that it changes after the first entry.  Another problem is that people frequently create a formula, copy it  down and never check any of the other entries to make sure they are correct. Often- due to a lack of absolute references the formula in the third or fourth row may be incorrect even though the original formula in the first row is correct.- This happens to a lot of people using Vlookup tables.  Also, sometimes people find an error and fix that cell but forget to go back to the original cell and fix the problem there and recopy it. Finally, hard coding numbers in can be problematic as those numbers might change.
It is good worksheet design to make sure that formulas are correct and consistent and that cell references are used  instead of hard coding numbers into formulas.

Formatting Alternate Rows

Formatting alternate Rows

Thanks to Diana Lawless for sending in this Excel Format painter icontip.

Using the Shift key and the Format Painter icon together allows you to format an entire worksheet quickly. If you are formatting alternate rows or columns quickly and do want not mess around with Conditional formatting rules this is a great shortcut.
Diana wanted to format every other row of her 1,000+ row worksheet with a variety of formats to enhance readability.

To change formatting of the spreadsheet:

  • Format line(s) as needed
  • Highlight formatted line(s)
  • Left click Format Painter (paintbrush)
  • Hold ‘Shift’ key
  • Tap ‘End’ key
  • Tap down arrow key
  • Release ‘Shift’ key
    And voila! The formatting was automatically copied to every line in the spreadsheet.
    If you have inserted new rows in an already formatted worksheet, this is a really quick way to fix any formatting problems.

Conditional Formatting and =MOD()

An alternative method, as mentioned above, is to use conditional formatting. If you use conditional formatting and the =MOD() function it is a quick way to create a general ledger type of effect with rows displayed in alternate colors.

Below are the steps you can use to apply a rule to data. In my example here I am using the MOD function and telling Excel to shade every second row of my P&L so that it is easier to read. I am specifying 2 in the MOD function so that every second row is shaded and I am specifying green as that is the traditional accounting color but obviously you can select any color you want – just make sure that it is light enough that that the data can be seen through it.

Formatting dialog box

Formatting dialog box

1. Select your data

2. Click Conditional Formatting on the Home Ribbon.
3. Select New Rule.
4. Click on Use a Formula to determine which cells to format.
5. In the rule description, type =MOD(row(),2)=0.
6. Click Format.
7. Select a light green fill – this is usually associate with accounting general ledgers.
8. Click OK.
9. Click OK.

It should look something like the image below. It really does improve readability.

general ledger formatingt with =MOD

general ledger formatingt with =MOD

An Automated Alternative in Excel

Automated alternative to FlashFill (and the “Text to Columns” Wizard)
My Excel tip is not so much about a favorite “tip or trick” as it is about a favorite concept. Tools like FlashFill and the “Text to Columns” Wizard are neat and certainly have their place, but I prefer an automated approach when appropriate. I emphasize “when appropriate” because one should always exercise good professional judgment when determining the correct tools and course of action to solve problems or complete tasks.
There are two reasons for my preference for automation when appropriate. First, an automated process usually requires less cumulative effort in repetitive process situations. Second, an automated process often can be shifted to lower-skilled workers, freeing those with higher skills to do other tasks that cannot be so delegated.
Let’s look at an example. In the spreadsheet below, each employee name in column B is composed of the employee’s last name and first name, separated by a comma delimiter and a space. If this is a “one-off” situation (particularly one having only a few names), you could (and perhaps should) use FlashFill or the “Text to Columns” Wizard to separate the names into their individual components.
Let’s say, however, that this data must be collected and separated each month for a large number of employees. Let’s also say that MS-Access (or a similar tool) is not available, and you do not want (or know how) to script code in VBA. Nesting functions can be an excellent way to automate tasks in Excel and create some interesting results.
For our example, Excel doesn’t have a “reverse concatenate” or “split” function, so we’ll have to get creative. In column C, we have extracted the last name from column B by nesting the SEARCH function inside the LEFT function. In column D, we have used the same approach, but substituted the RIGHT function for the LEFT function. Because the formula cell references are relative, they easily adjust as we copy them to the remaining cells. For reference, the two formulas are included in columns F and G.excel screenshot

Now let’s explain what just happened, using cell B3 for our explanation. In column C, we used the left function to extract the leftmost characters of cell B3 for the Last Name.

The second parameter of the LEFT function is used to specify the number of characters to return. Here, we have let Excel calculate the correct number of characters to return by using the SEARCH function to return the position of the comma in the cell contents — but we’re not done. We don’t want the comma returned, so we must adjust the calculated number of characters to exclude it.
Now we want to extract the First Name from cell B3. To do this, we use a combination of the RIGHT, SEARCH, and LEN functions.
Here, too, we allow Excel to provide the correct number of characters for the second parameter of the primary function. Conceptually, we will simply subtract the Last Name from cell B3 to arrive at the First Name.
However, we have a problem. We know the width of column B, but we don’t know the length of the content in each cell in column B without doing a lot of manual counting.
The answer? Let Excel figure it out, using the LEN function. The resulting formula tells Excel to subtract the leftmost characters from the cell content starting from (and excluding) the comma, and return the remaining (rightmost) characters. Once again, we exclude the comma from the results.
In actual fact, we should be asking Excel to exclude both the comma and the space (rather than the comma alone) from the results in both formulas. However, Excel is smart enough to trim the leading and trailing spaces from the results.
How can we set this monthly task up for delegation to an employee with lower skills? One way would be to house the employee names in one spreadsheet (or workbook) and house the formulas in another. Assuming data updates cannot be automated, the lower-skilled employee would simply copy or re-key the data into the specified position of the designated worksheet and the formulas would do the rest.

This tip is a guest blog post  from L.Keith Jordan, CPA
The LEN function in particular is totally underutilized. The LEN function in particular is very underutilized. If you want some other examples of what to do with it check out my Excel-Diva blog post on LEN.


Copying Only Visible Cells

Go To Special

Have you ever subtotaled data or produced a pivot table and wanted to use the summarized information for further calculations?  Normally if you copy filtered or subtotaled data, all the data, including the hidden rows, is copied.There is an easy way  around this. It’s in the Go To Special feature of Excel and the option is labeled Visible cells only.
Assume that you have the following simplified data:
First, use subtotal to accumulate the sum of sales by product line. After sorting the data by product line and using the subtotal feature, the following is obtained:
fig-2As you can see, the subtotaled data hides some columns. Continuing to work with the subtotaled data may cause a mess, especially if you want to create a new subtotal.
An easy way around this issue is to copy the data. If a basic copy/paste was performed in an area that wasn’t within the same set of rows on the worksheet or was on another worksheet, you would have all the detail data rather than just the summarized data. However, you can avoid this by using the Go To Special feature and the Visible cells only option.

First, select the subtotaled data. fig -3

Next, click on Find & Select in the Home ribbon and select Go To Special.

Go To Special dialog box






When Go To Special has been selected, a dialog box appears with more options.
go special dialog box

Select Visible cells only, then click OK.

result of using visible cells only

Notice that each cell now appears to be highlighted by a box. Right click anywhere in the area and select copy. The box highlights around each cell appear as if they’re moving. Right click on cell A20 and select Paste Special then Values (This is important. It tells Excel to only paste the values in the cells rather than the formulas.)

paste optionsAfter the Values option has been selected from Paste Special, the summarized data, without all the detail, is now available to work with.


excel worksheet result


I use this technique a lot with pivot tables. Many times, I want to reference a summarized column of information on a pivot table for subsequent calculations. This can be done with pivot table data. However, if the pivot table is large and you want to copy formulas involving pivot table data, it’s not always easy as pivot table data contains absolute cell references (eg. Copying a formula down that contains the first piece of summarized information in a pivot table will continue to only use the data in that first cell.)
So, if I want to work with pivot table data, I generally isolate the visible cells using Go To Special, then copy only the visible cells and use Paste special-Values to copy the data to another worksheet and perform further analysis.

Flash Fill – Leaves Text Functions in the Dust!

flash fill drop down icon


Excel 2013 has a cool feature that will save a lot of time for those of you that are always using Text functions such as =LEFT and =RIGHT to extract information.
Flash Fill can recognize patterns and extract the information you need with a click.
For example, in the worksheet below I have Employee Name concatenated together in one cell. If I wanted to extract Last Name from Column B  I would have to nest the text function with at least one other function as all the entries are of different lengths. With Flash Fill all I had to do was type the last name Harris into cell C2 and then select C2 through C5.
flash fill example -#1
I then clicked on Fill, in the Edit Group, and selected Flash Fill.
Excel filled in C3 through C5 with the same pattern as in C2 which was the last name of the employees.
In G2, I typed 2013 as I want to only display year from the data in Column F. How would you finish it? Select G2 through G5 and then select Fill>Flash Fill and the years will display in G2 through G5.
You can also undo it or take Excel’s suggestions if it does not follow the pattern as you select by clicking on the Flash icon that displays beside the new data. In the screenshot below you can see the Flash icon at G3.flash fill - undo

Grouping Data by Date

calendarTechniques for Grouping Data by Date in Excel

If you have a large data set that includes transaction dates, you may want to aggregate amounts by certain dates.  The problem is that you have a separate date for each day of the year.  This makes any meaningful analysis difficult.
It would help if just the month or year could be extracted so that data could be accumulated.  Well, there are functions for that.  There’s a year function, a month function and a day function that will extract the year, month and day from a date.
The syntax is the same for all functions.  Using =year as an example, the syntax is =year(cell with date).  The example below uses 7/15/2015. More….


Only Copy Cells with Data

A Method to Copy Only Cells with Data

Have you ever had a couple of columns of data that you wanted to combine into one column?  What do you do when there are blank spaces in some of the columns of data?  Many will cut and paste the data from one column to the other, then manually delete the rows containing blanks.  This begs the question; Is there an easier way?
One way is to use the GoTo Special feature in Excel.  This feature is located in the Home menu, under Find & Select on the far right hand side of the toolbar.jfog-1a


As an example, assume that the following data in in Excel and you want it in one column for analysis:

Let’s copy only the cells that are not blank from B1:B7 to column A, under A7.
Select cells B1:B7
Click Find & Select and choose GoTo Special.

This menu will appear.

go to special dialog box


Select the radio button next to Constants and press OK.


Notice how only the cells with data are now shaded.  Hover over any of the three shaded cells and right click.  Select Copy.  Then, select A8, right click and select Paste.

Only the non-blank cells are now pasted into column A.

This technique can save quite a bit of time if you’re working with a large amount of data that requires manipulation into one column.  It also works with any data that needs to be moved and contains unneeded blanks.

Federal Estate Tax Portability Act

Federal Estate Tax Portability
Federal estate tax portability means that a surviving spouse can inherit the estate and gift tax exemption of a surviving spouse.  Simply put, a couple can pass up to $10.86 million ($5.43 million for each spouse) in 2015 without the need for a trust.
To elect portability, a federal estate tax return, Form 706, must be timely filed at the death of the first spouse.
Example:Assume that Barney and Betty have joint assets of $10 million.  Barney dies first and all the assets pass to Betty under the unlimited marital deduction.  Barney uses none of his $5.43 million exemption.
Without Portability:  Barney’s exemption goes unused and is wasted.  Upon Wilma’s death, the amount of the $10 million estate that exceeds her $5.43 exemption will be subject to federal estate tax of approximately $1.6 million ($10.00M less exemption of $5.43M=$4.57M x 35% federal estate tax rate).
With Portability:  A form 706 is properly filed to transfer Barney’s exemption to Wilma.  Upon Wilma’s death, the $10 million estate is less than her $10.86 million exemption and the estate is passed to her heirs free of any federal estate taxes, saving $1.6 million.

Printing Orientation

I don’t know about you but I get very annoyed at how many times I have to click to change the page orientation to landscape. Personally, I think landscape should be the default.  If you are continually going in and changing it here is a quick fix – Add the Orientation icon to your Excel Ribbon. Here is how you do it.

  • Click on File>Options>Customize Ribbon.
  •  In the first column, click on the drop-down arrow and select All Commands.
  • You are going to want to scroll down and select the first Orientation icon that you see in that first column: however you need to create a new group or a new tab in the 2nd column before you can add the Orientation icon to the Ribbon.
  • Once you have created the new tab or new group, in that second column, then you then want to click the Orientation icon in the first column and then select Add.
    Everyone will want it in different places but this is what I did- just to give you an idea.
    I first created a New Group on the Home Tab and right-clicked to name it Print. I kept the new tab called Print, on the Home tab, selected. and then I clicked on the Orientation icon and clicked Add.  I now have a new group called print with the Orientation icon at the end of my Home tab. When I click on it I can select Portrait or Landscape.   Nice eh?  (If you uncheck any Main Tabs- you need to check them again otherwise they will not display when you go back out to Excel.)

printing orientation in excel orientation1

The N() function. Comments Anyone?

Most people are familiar with Insert>Comment. Just right-click on a cell and select Insert>Comment. Very easy. Right-click on the comment to display it, edit it or delete it. You know there is a comment in a cell because of the red triangle in the upper right corner of the cell.

Using comments can be a pain as it is additional step and if you display the comment sometimes it can obscure other cells. A more interesting way to document formulas, in particular, is to use the N() function.
The N(item) function returns a zero when the item is a string so if I typed =Count(A1.A5)+N(“I am only counting numbers”) then the answer would be 2.=Count(A1.A5)=2+0 =2
N function This can be a nice quick way to provide information if you are creating a complex formula and want to remember what you are doing.

In the example below, I identified that I was looking up Unit Price based upon the Product ID   Now, if I had looked up Product Name instead of Unit Price, I would have received an error message if I had included the N() function. Can you guess why?  In that case instead of the resulting formula being 17.95+0=17.95 the formula would have been = “Bing Cherry”+0 =#VALUE!   You can’t add text and numbers together.  So, this may be useful, to some of you, if you use complex formulas and are always scratching your head wondering what the heck it says.N function with Vlookup

Deleting Blank Rows

Yes- Everyone knows how to delete a blank row  but what if you have downloaded a lot of data and see that you have sporadic blanks throughout rows and rows of data?  For instance,in the example below, I have 8 Customers but only sales for 3 and and a lot of extra blank rows too. What if you only wanted to see Customers who had sales. Sorting or filtering would help but still leave you with rows that you don’t want and could be time consuming.  The most efficient way to do this would be to select the Sales column as that is what you are most Excel datasheetdeleting blank rowsinterested in and then go to Find and Select on the Home tabSelect Go To Special...and select Blanks. Click OK.
Excel selects and displays in gray all the blank cells.
blank cells selected


Once all the blank cells have been selected, go to Delete on the Home tab and click the dropdown arrow and select
Delete Sheet Rows.delete cells


Excel deletes all the empty cells in the Sales Column that you selected so you end up with the 3 sales numbers and the corresponding Customers. Cullen and Lambert for example are deleted as they did not have any sales.

remaining rows

You do want to be careful though.If you had data that you wanted to keep in Column C, for example, that might get deleted.


Why did I only select the Sales column? In this particular example, it would not have mattered if I had selected both Column A and Column B; however, if I had different data and selected both columns I might have a problem. For example, in the screen shot below, I added sales at row 6 without a corresponding name. What do you think happens if I select both Column A and Column B and deleted all blank rows?  Yes, you guessed it- I would get the same answer as above and only have the 3 names and 3 sales figures. Cell A6 is blank so Excel deleted that sheet even though there were sales. If you selected multiple columns and then delete the blanks, Excel will end up only displaying cells that contain data in all the corresponding columns.excel datasheet



Computing a Loan Payment & Creating an Amortization Schedule

Computing the Periodic Payment for a Loan

Excel Financial Function

Computing the periodic payment is probably the most used Excel financial function simply because it is something everyone uses.
Let’s walk through an example.
You want to purchase a car and finance $24,000 over 24 months at an interest rate of 4%.   So, the question is –What is the monthly payment?
Here we have a value today (Pv) of $24,000.  We want a monthly payment for 24 months (Nper) at an annual interest rate of 4%.
First, we need to change the annual interest rate to a monthly one by dividing 4% by 12 = .0033333  Let’s reflect this in our worksheet.
Matching the rate to the payment frequency is important.  If you want to compute a monthly payment and have an annual rate, the rate must be divided by 12.  (So many people forget this little fact which is why I am really emphasizing it here.)

Present Value calculation
Click on cell B5, then click on the Fx button at the top left of the formulas tab.
At the top of the window that opens, select the category “Financial, then find and select PMT at the bottom.

Click OPMT-2K.




Fill in the cell references for Rate, Nper and PV as shown below: Rate is B3, Nper is B2 and PV is B1.
Click OK.
The monthly payment is $1,042.20.  Remember, since this was a monthly payment, the annual interest rate was adjusted by dividing it by 12.Also, please note that the answer is negative as it is considered an outflow. Yes- you can tell the programmer was not a CPA 🙂  The easiest way to fix this is to go up to the formula bar and put a negative sign in front of PMT so that it looks like =-PMT(B3,B2,B1)

Let’s  double-check our work by making an amortization schedule.
An amortization schedule starts with the initial loan amount and progresses it forward using the monthly interest rate and computed monthly payment.

Start with the beginning balance of $24,000.  Add interest to this amount at 4% divided by 12 (=B1*.04/12).  Reflect the payment as a negative amount and sum across the row (B4:D4) for the ending balance.


The new beginning balance in B5 should be the result in E4 (the formula in B5 will be =E4).  Copy these down 23 rows (for a total of 23 periods).  The ending balance should be very close to zero, but may not be exactly zero due to rounding (the use of only two decimal points in the payment).

amortization schedule

This proves that the monthly payment for a loan of $24,000 over 24 months is correct and provides us with the interest and remaining balance at the end of each payment period.

Mortgages work identically except that the number of periods is generally longer.


Dupont Model

Dupont Model

The DuPont Model is a methodology that computes Return on Equity by component parts.
Business owners and shareholders are very interested in their level of return.
A method of consulting with business owners and shareholders on ways to boost Return on Equity would be very valuable to the CPA.
The DuPont Model was first used by the DuPont Corporation in the 1920’s. Dupont logoIt breaks down the Return on Equity formula into three basic components; Net Profit Margin, Asset Turnover and Equity Multiplier. This can be used in conjunction with industry averages or competitor information to pinpoint opportunities to improve Return on Equity (ROE).
ROE = Net Profit Margin x Asset Turnover x Equity Multiplier
or ROE = (Net Income / Sales) x (Sales / Total Assets) x (Total Assets / Equity).
Some of the calculations did not show well in the blog so click here to access the PDF file on the Dupont Model.

Conditional Formatting-Icons

Using ICONS- Conditional Formatting

When I started writing my Excel Driven Dashboard course a couple of years ago I really started appreciating a more visual approach to Excel.
Now I am always incorporating color and images into spreadsheets wherever possible.
So, to that end, I want to talk about icon sets in Excel and how they can be useful.
In the example, I am going to go through I have a column of units sales that is compared against budget and a variance column.
If you have more than a couple of rows, it is difficult to quickly identify your problem areas. You may be able to sort the variance column it but I think using conditional formatting is more useful and helps to convey a better picture of what is going shop

Here is my data.  Yes- everyone knows about my love of chai and lattes!

Column D shows the variance. In case you are wondering I used the accounting format as I like the parentheses around negatives rather than the minus sign which is a little more difficult to see (maybe I am just getting old??)

Excel spreadsheet

Anyway, this is a short list but it is still difficult to determine which products are doing well compared against budget.  Using conditional formatting and the associated icon sets will make it much clearer.

1.First, select the data in the Variance column (D4.D12)
2.Go to the Home ribbon and click on Conditional Formatting and select Icon Sets.
3.Scroll down to the bottom and select More Rules

4.Go down to Icon Style and select one you like. I personally prefer ones that displays well on paper as well as online.

icon_rulesSo, I wanted a green check mark to display on all the positive variances where actual is better than budget, a yellow to display if there is no variance and a red x to display if the variance is negative so:

5. Select >= from the drop-down list, type 1 in the Value section and select Number from the Type drop-down.
6.In the second row, select >=from the drop-down list, type in the Value section and select Number from the Type drop-down.
Yes- having a < or <= would be nice – who the heck programs this stuff anyway?

7.Click OK.

Icon set dialog box

Voila – at a glance you can see where the problem areas are.

Now, you may find that too busy so you do have some options.

If you want to edit a Conditional Formatting rule – it is very easy – Select your data and  go back and click on Conditional Formatting and then select Manage Rules.

Click on Edit Rule…. 

show_icon only

Click and put an x in Show Icon only. This will hide the numbers and you will only see the icons.

no cell icon

An alternative if you want it clearer is to hide the green check marks so that you can focus on the problem products.
To do that, click on the drop-down beside the green check mark and select No Cell Icon from the top of the dialog box. You could also do this for the yellow exclamation if you were not concerned with products that made budget-barely.

Now, doesn’t this bottom screenshot look pretty clear and tell the story at a glance.

conditional formatting

Now, I also like to see the numbers and even if I edit the rule and de-select Show Icons Only, it still looks messy so what I like to do is create an additional column and have the icons display there.  That way, I get the best of both worlds- the numbers and the visual impact.
So, I clicked in cell E4 and typed =D4 and then copied it down so I have the same numbers in Column D and E.
icon_last_dialogboxThen I selected Column E and did the steps 4-6 as outlined above and then clicked the Show Icons Only box and then selected No Cell Icon for the green check mark and the yellow exclamation point.  You have to admit this looks a lot cleaner.


Wow.. .time for that cup of coffee now!

picture of coffee

Using the Altman Z Score to Predict Bankruptcy

Using the Altman Z Score to Predict Bankruptcy

– Guest Post by Joe Helstrom, CPA

Bankruptcy Predication - a foretller




The Altman Z score for bankruptcy prediction was first published by Edward Altman, an Assistant Professor of Finance at New York University, in 1968. He was trying to find financial ratios that could distinguish between a healthy company and one that might be distressed and enter bankruptcy.
To accomplish his goal, he evaluated common financial ratios of approximately 66 companies, all with assets or more than $1M. Approximately half of the firms had a bankruptcy in their past while the other half did not. He then used a statistical technique called Multiple Discriminant Analysis to determine which ratios were most predictive of bankruptcy and the proportion of those ratios to use.
Upon testing, Altman’s model was found to be 80-90% accurate in predicting bankruptcy one year prior to the event. However, it is also inaccurate 15% – 20% of the time in predicting bankruptcy, predicting that a firm will go bankrupt when it doesn’t.
While the accuracy rate is not 100%, it is still very good. It is so good that the Altman model has gained wide acceptance among accountants, auditors and even the courts.
Altman’s original model was designed for publicly traded manufacturing firms with assets of greater than $1 million. However, he has subsequently created two additional models to be used for private manufacturing companies and private non-manufacturing companies.
The Altman models use the following ratios:
 A. Working Capital/Total Assets
B. Retained Earnings/Total Assets

  C. Earnings Before Interest and Taxes/Total Assets
  D. Equity/Total Liabilities
  E. Sales/Total Assets
Note that for public manufacturing companies, Equity is defined as the market value of equity. For non-public manufacturing companies, Equity is simply book equity.
The proportions of each ratio change with each model.
For a public manufacturing company, the Altman Z Score model is (note Equity is market value of equity):
Z Score = 1.2*A + 1.4*B + 3.3*C + 0.6*D + .999*E
• A score of less than 1.81 has a high likelihood of bankruptcy
• A score of 1.81 – 2.99 is in the “Gray” area
• A score of greater than 2.99 is a safe company

For a private non-manufacturer, the Altman Z Score model is:
Z Score = 6.56*A + 3.26*B + 6.72*C + 1.05*D
• A score of less than 1.1 has a high likelihood of bankruptcy
• A score of 1.1 – 2.6 is in the “Gray” area
• A score of greater than 2.6 is a safe company
Note that the private non-manufacturer model does not use Sales/Total Assets.
The original model was developed for manufacturers. The non-manufacturer model was intended to fit service companies. A continuing criticism is that the model does not work for financial institutions.

Password Protect an Excel Workbook

How to password protect an Excel workbook

Everything these days seems to require a password.. and of course everyone has different specifications. I would love to hear from people on how they safeguard their passwords. Send me an email with your method- send to “”

Below is a guest post from Joe Helstrom on protecting a workbook.
To ensure that unauthorized users do not access a workbook, a workbook can be password protected. Without the password, users cannot access the worksheets within the workbook. It is an effective control as long as the password is limited only to those users with authorized access, is properly safeguarded and is not shared with anyone else. A password is not very effective if it’s on a sticky note attached to your computer monitor.  However, I am sure that none of you do that.

1. Click on File
2. Click Info
3. Select Protect Workbook

steps to protect Excel spreadsheet






4. Click Encrypt with Password

password protect dialog box in excel
Once you have selected a password, you’ll be asked to re-enter it.
steps for encrypting Excel worksheet




Beware! If you lose the password, it cannot be recovered. Also note that passwords are case sensitive.
When you save the workbook, the password will be required to open it. Keep in mind that there is no way of recovering a lost password in Excel.

This feature not only password protects the workbook. It also uses AES 128 bit encryption which makes the file more secure.
Passwords should be strong. It is recommended that they be at least 7 characters long, contain both uppercase and lowercase letters as well as numbers and symbols.

Using Workday to Calculate Invoice Dates

This is just a quick tip as Excel is not necessarily that date friendly.
If you want to calculate an invoice date or a project completion date use the WORKDAY() function.
Select a cell and then select the Formula tab and then select Date& Time

The syntax is as follows:
–Start_date Required. A date that represents the start date. Make sure to use a date and not text
–Days Required. The number of nonweekend and nonholiday days before or after start_date.
A positive value for days yields a future date; a negative value yields a past date.
–Holidays Optional. An optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates

In the example below, I selected cell D3 which is where I wanted the due date to display.  F1 represents the number of days the invoice is due after from the invoice date which is in cell B3.  My start date was B3 and the number of days was 30, as shown in F1. I opted not to include holidays which is optional.
The resulting answer is 42095 which is a serial number.  This is not incorrect – it is just not formatted as a date. 
 Depending on how you have formatted your spreadsheet you may need to format D3 to display the actual date rather than the serial number. 


I opted to set up F1 as an input cell and used absolute cell references so that if I had a series of invoices I could just copy the formula down. The advantage of the input cell is that if I decide to give everyone 45 days instead of 30 days, I only have to change the value in F1.




If you use dates a lot you may be interested in an entry on my Excel-Diva blog entitled Converting a Date to a Calendar Quarter.




Repair Regulation Relief – Relief or Stomach Ache?

The IRS has revamped the tangible property regulations for the 2014 tax year.  The new procedure allows small businesses to change a method of accounting under the final tangible property regulations on a prospective basis for the first taxable year beginning on or after Jan. 1, 2014.

“Taxpayers must evaluate certain expenditures to determine whether the costs are immediately deductible repair cots or capital improvements that need to be depreciated. In addition, regulations issued at Section 1.162-3 provide new guidance on when a taxpayer can deduct costs incurred to acquire “materials and supplies.”

The IRS is waiving the requirement to complete and file a Form 3115 for small business taxpayers that choose to use this simplified procedure for 2014.

The new simplified procedure is generally available to small businesses, including sole proprietors, with assets totaling less than $10 million or average annual gross receipts totaling $10 million or less. Details are in Revenue Procedure 2015-20, posted February 13, 2015 on

For a great discussion on the pros and cons and details check out this Forbes article.  Repair Regulation Relief – What Does It Really Mean? (Not as Much as you think).

IRS. gov and

Preventing Duplicate Entries using Data Validation in Excel

If you are setting up a spreadsheet for someone else to enter data this is a neat trick. You can use the Data Validation and the CountIF feature to check for duplicate entries and then not allow the user to enter the duplicate data.

Let’s assume that the data you want to check is in Column A.
Either select tpreventing duplicate entrieshe entire Column or select the cells in Column A where the data is going to be entered.
Click on the Data tab and select Data Validation from the Data Tools group

  • In Allow: dropdown box, Select Custom
  • In formula type =CountIF(A:A,A1)=1
  • Click OK.
  • Now go to  Column A and enter some data in a few cells and then try to enter a duplicate piece of data. Excel will pop up with a restricted dialog box and tell you to retry.


This works great for new data however if you select cells where there is already data, even duplicate data, Excel will just ignore it. Also, if someone copies and pastes data into Column A it will override the data validation.

If you did not want to select all of Column A but instead wanted to select cells A5:A50 then the formula would be =CountIF($A$5:$A$50,A5)=1. If you forget the absolute cell references it will not work properly.





Welcome to my Blog for CPAs


Since we have redesigned our entire website, I thought I would change up my blog a bit and make it a bit different than my  Excel-Diva blog.
This blog is directed primarily to CPAs and other business professionals. It is going to cover practical applications of Excel and other software; however, it will also discuss different accounting and financial issues that may  be of interest to you.     Excel-Diva  will remain up since it already has so many Excel tips and how-to’s  out there and I may continue to add some information out there as well.
I hope you find the blog interesting and useful.



  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue

  • ” This was my first experience with and I was very impressed. I will definitely consider taking CPE through in the future.”   12/2017

    - Christopher Q.

  • “It is rare that i can actually say that I enjoyed a CPE course but I found this one informative and very interesting, relying more on the author’s examples and not just quoting tax code!  [Healthcare Reform: The affordable Care Act Tax Provisions]

    - Thomas