Welcome to CPA at Law, helping individuals and small businesses plan for the future and keep what they have.

This is the personal blog of Sterling Olander, a Certified Public Accountant and Utah-licensed attorney. For over thirteen years, I have assisted clients with estate planning and administration, tax mitigation, tax controversies, small business planning, asset protection, and nonprofit law.

I write about any legal, tax, or technological information that I find interesting or useful in serving my clients. All ideas expressed herein are my own and don't constitute legal or tax advice.

Savings Incentive Match Plan for Employees

A Savings Incentive Match Plan for Employees (SIMPLE Plan) is a written salary-reduction arrangement that allows small businesses that meet certain requirements to make retirement contributions on behalf of eligible employees. A SIMPLE Plan "is ideally suited as a start-up retirement savings plan for small employers not currently sponsoring a retirement plan." A SIMPLE Plan is established by a written agreement and setting up Individual Retirement Accounts for employees.

In order to be eligible to establish and maintain a SIMPLE Plan, a business can not maintain or sponsor another retirement plan and must have 100 or fewer employees who earned $5,000 or more in the prior year. All of the employees in this category are eligible to participate in the plan and the employer may not impose more restrictive eligibility requirements. The employer is required to make either a non-elective contribution of 2% of each eligible employee’s compensation or a match of the employee’s elective salary reduction of up to 3% of the employee’s compensation.

Employees can make salary reduction contributions up to $12,000 in 2013, plus catch-up contributions of $2,500 for individuals 50 or older. Elective deferrals of an employee’s wages are included in Form W-2 wages for social security and Medicare purposes only. Employer contributions to a SIMPLE Plan are excluded from the gross income of the employee and deductible by the employer.

While the contribution limits of a SIMPLE Plan are lower than some other small employer retirement plan options, SIMPLE IRA Plans do not have the start-up and operating costs of other plans, nor is there any filing requirement for the employer. Furthermore, because SIMPLE Plan contributions can reduce salary, they can be used to reduce payroll or self-employment tax when compared to some other retirement plans.

Excel's Sumproduct Function

Excel's SUMPRODUCT function is more useful than it appears.  On its surface, SUMPRODUCT simply "returns the sum of the products of corresponding ranges or arrays." The easiest way to demonstrate this is to use the following table:

 A   B 
 1   5   3 
 2   2   6 
 3   0   10 
 4   1   8 

The formula =SUMPRODUCT(A1:A4,B1:B4) yields 35, which is the sum of the products of columns A and B, calculated as follows: (5*3)+(2*6)+(0*10)+(1*8).

While this functionality alone can be useful in some contexts, the real power of SUMPRODUCT comes from utilizing criteria. The values in any column can be logically tested as TRUE or FALSE in Excel. The logical value TRUE is represented by the number one and the logical value FALSE is represented by zero.

With that in mind, we can analyze a larger table that lists a client's name, the business entities associated with that client, and the due date and fee amount of the annual renewal filing required by the state the entity is formed in:

 A   B   C   D   E   F 
 1   Client   Entity Name   Entity Type   State   Month Due   Amount 
 2   John Doe   ABC Corporation   Corp   UT   Apr   15 
 3   John Doe   Doe Family Partnership   LP   UT   Dec   15 
 4   John Doe   Doe Rental Property, LLC   LLC   DE   Jun   250 
 5   John Doe   Doe Equipment, LLC   LLC   DE   Jun   250 
 6   Bill Smith   Bill Smith, DDS, Inc.   Corp   NV   Jan   325 
 7   Bill Smith   Smith Family Partnership   LP   WY   Dec   50 
 8   Bill Smith   Smith Equipment, LLC   LLC   DE   Jun   250 
 9   Bill Smith   Smith Business Property, LLC   LLC   NV   Oct   325 
 10   Bill Smith   Smith Rental Property, LLC   LLC   NV   Oct   325 
 11   Bill Smith   Smith Vacation Home, LLC   LLC   WY   Feb   50 

Suppose that Bill Smith is wondering what he will be paying each year for all his annual state renewal filings. This can be easily calculated with the following formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),F2:F11). The result is 1,325.

The array in column A must equal "Bill Smith" in order for the figure in column F to be included in the total. The first four rows do not equal Bill Smith so they return FALSE, and the remaining rows do equal Bill Smith so they return TRUE. The "--" turns the Boolean values TRUE and FALSE into the integer value 1 and 0 respectively. The products of the values from column A and the values in column F added together equal 1,325.

Of course, the above result is also possible with the following SUMIF formula: =SUMIF(A2:A11,"Bill Smith",F2:F11). However, SUMIF does a poor job of handling multiple criteria, while SUMPRODUCT can. Following are some additional examples of SUMPRODUCT formulas and the information they return.

To find the total fees due for Bill Smith's LLCs, use this formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),--(C2:C11="LLC"),F2:F11), which results in 950.

To find the total fees due for Bill Smith's Nevada LLCs, use this formula: =SUMPRODUCT(--(A2:A11="Bill Smith"),--(C2:C11="LLC"),--(D2:D11="NV"),F2:F11), which results in 650.

To find what all clients will be paying for LLC fees in the month of June, use this formula: =SUMPRODUCT(--(C2:C11="LLC"),--(E2:E11="Jun"),F2:F11), which results in 750.

In summary, SUMPRODUCT simply returns the sum of the products of corresponding ranges or arrays. However, if multiple arrays are used as filters, its usefulness increases dramatically.