Category: AccountingWEB articles

May 18

Farewell Peachtree Accounting, Hello Sage 50

By David H. Ringstrom, CPA



After thirty-five years, the venerable software program known as Peachtree Accounting is adopting a new moniker – Sage 50 Accounting. The software has changed hands several times over its life, most recently purchased by Sage in 1998. Sage has embarked on a global campaign to unify the naming of its products , so MAS 90 and MAS 900 are now Sage 100 ERP, while Timberline is known as Sage 300 Construction and Real Estate.


Fortunately for long-time Peachtree users, the name change is the primary distinguishing aspect of what would have been Peachtree Accounting 2013. The user interface is exactly the same as before, with a few new features added:


  • Users no longer have to change accounting periods to reconcile accounts for previous periods.
  • A new Insurance tab appears on vendor record screen.
  • A new Set Multiple Records as Inactive screen simplifies the process of indicating which customers and vendors you no longer expect to transact business with.
  • Waste-Free Check Printing allows users to print long remittances on plain paper, rather than sacrificing additional check forms just to generate additional stubs.
  • International users can now enter amounts with up to fourteen digits before the decimal and five digits after. Most small businesses would never need to enter such amounts, but certain currencies around the world often result in such large amounts, which previously couldn't be recorded as full amounts within the software.
  • Sage 50 builds on the Business Intelligence feature introduced in Peachtree 2012, which gives businesses the ability to seamlessly analyze their accounting records in Excel.
  • A new Sage E-Marketing service empowers users to launch social media campaigns from within the software.
  • An improved QuickBooks Conversion utility.
  • A bit of trivia for long-time Peachtree users – according to Wikipedia the first version of Peachtree Accounting appeared in 1977, six years prior to the arrival of Lotus 1-2-3.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

May 09

Microsoft Excel: Thirteen Double-Click Tricks

By David H. Ringstrom, CPA



The impact of single-clicking on a menu or object in Microsoft Excel is generally intuitive, but double-clicking sometimes reveals hidden shortcuts. In this article I'll discuss a baker's dozen of techniques where double-clicking can save you time and effort.
1. Skip the OK button: Unless you're clicking a checkbox, double-click on your choice within any dialog box in Excel to select that option and click OK simultaneously. For instance, press Ctrl-1 to display the Format Cells dialog box. Double-click on any format within the Number tab to apply the formatting and close the dialog box in one step.
2. Maximize file windows: Double-click on the title bar of Excel's Open or Save As windows to see more files and folders. Double-click the title bar again to restore the default window size. You can double-click on Excel's title bar or a worksheet window as well.
3. Split a worksheet: Double-click either of the split handles in Figure 1 to view two sections of the same worksheet at the same time. Double-click on a split to remove it.



Figure 1: Double-click the slit buttons to view two sections of a worksheet simultaneously.



4. Fill handle: Just about everyone knows that you can grab the little black notch in the right-hand corner of a cell and drag down to fill a range of cells. Double-click this Fill Handle instead to copy data or formulas down until a blank cell is encountered in the adjacent column. In Excel 2010 and later, this feature copies down to the bottom of the contiguous block of cells, meaning it can skip past blanks in the immediately adjacent column.
5. Lock the Format Painter: Most users click once on the Format Painter to transfer formatting from one cell or drawing an object to another. To apply the same formatting to a number of non-contiguous cells, double-click the Format Painter and then format as many cells as you wish. Press Escape once you finish with the Format Painter.
6. Rename a worksheet tab: Although you can right-click on a worksheet tab and choose Rename, double-click the tab to immediately edit the name.
7. Adjust column widths: Double-click the right edge of any column on the worksheet frame to automatically resize that column to fit the width of the longest cell entry. Or, select two or more columns and double-click the right edge of a column to resize multiple columns at once.
8. Collapse the ribbon: Double-click any ribbon tab in Excel 2007 and later versions to collapse or expand the ribbon interface, thereby giving you more (or fewer) rows onscreen.
9. Navigate a worksheet: Double-click any border of a worksheet cell to move your cursor in that direction until it encounters a blank cell or the worksheet frame.
10. Edit a cell: Double-click any worksheet cell to edit its contents in place rather than moving your mouse to the formula bar.
11. Identify Precedent Cells: This technique requires that you first turn off the Allow Editing Directly in Cells option under the Advanced section of the Options window in Excel 2007 and later. In Excel 2003, this option appears on the Edit tab of Excel's Options window and is labeled Edit Directly in Cell. In either case, once you turn the option off, double-clicking a worksheet cell will highlight the precedent cells that a given formula refers to.
12. Close Excel: Double-click the Excel logo in the top left-hand corner of any version in Excel (or the Office button in Excel 2007) to exit the program. You may be prompted to save any open workbooks.
13. Pivot Table Drill-Down: Double-click on any numeric value within a pivot table to create a new worksheet that reveals the underlying records that comprise that amount.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

May 04

Improve Your Excel Skills with this Free Excel Training

How are your Excel skills? Average? Enough to get by? Have you ever thought that with just a little training, you may be able to get your work done faster, and maybe reduce the errors that occasionally sneak in?

Click here to read the full text of the article at AccountingWEB.com.

Apr 30

Excel Tip: Calculating Straight-Line Depreciation

 
By David Ringstrom, CPA



The SLN feature in Excel enables you to compute straight-line deprecation for an asset. The function has three arguments: Cost, Salvage, and Life.


Cost is the original cost of the item, Salvage is the proceeds you expect to receive upon disposal, while Life is the useful life of the asset specified in years.


Thus, SLN would return $1,000 for a $5,000 asset with a salvage value of zero and a five-year useful life.



 A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Apr 08

Cloud Computing Versus Software as a Service

By David H. Ringstrom, CPA



As our world becomes more and more connected, the terms used to describe online services blur into abstraction. In this article, I'll clarify the terms “cloud computing” versus “software as a service,” often referred to as SaaS. In some ways, it's like describing two sides of the same coin. However, there are some clear distinctions, along with risks and rewards to keep in mind.


The Internet is increasingly being referred to as the cloud. One of the earliest mentions of cloud computing is in the paper The Self-governing Internet: Coordination by Design published by MIT Professors Sharon Eisner Gillett and Mitchell Kapor in 1996. Readers of a certain age will remember one of Kapor's other ideas, a product called Lotus 1-2-3 . Regardless, historically the central computers that run the nation's telephone network were often diagrammed on flowcharts as a cloud. The intricacies of networked computers that comprise the Internet are so complex that the term has been co-opted for use in our modern society.


In the early days of computers, users rented time on a mainframe computer. A few decades later, we all became accustomed to having our own personal computers on our desk, upon which we installed shrink-wrapped software. We became responsible for upgrading our computers, software, and backing up our data. As often happens in life, though, things are going full circle where we're returning to the days of renting time on someone else's computer. Instead of a single mainframe computer though, today we may utilize a bank of computers residing in a data center in an undisclosed location. Instead of being relegated to working only at our desk, today we often use mobile devices to carry out tasks unimaginable just a few years ago.


In general, cloud computing can be thought of as any instance where you're using a computer that resides outside of your physical location. Most users encounter cloud computing in the form of software as a service. You might pay a fee for the service, such as QuickBooks Online , Salesforce.com, or Microsoft's Office 365 , or you may pay in a nonmonetary fashion through an advertising-supported and/or information-gathering models, such as Gmail, Mint, or Facebook.


With all of these applications, you're relying on software installed and maintained on remote computers. Most often SaaS is delivered via your web browser, so long as you have a connection to the Internet, you're able to carry out tasks that may be business or personal in nature.  With this background in mind, I can provide some distinctions between cloud computing and software as a service:

  • Cloud computing gives you access to an environment that you can customize or build out to suit your needs. With SaaS, you're limited to the features and capabilities written into the software, but cloud computing offers the ability to increase server capacity or storage space on demand.
  • Cloud computing offers elasticity, meaning your resources and costs can increase or decrease with your demands. SaaS typically involves a set fee per user, per month, so costs and the functionality offered tend to be fixed.
  • In short, cloud computing is highly customizable, whereas SaaS offers more of one-size-fits-all approach.
Some examples of what may be considered pure cloud computing include:
  • Amazon Simple Storage Service (Amazon S3)  – This service allows you to store and retrieve an unlimited amount of data, at anytime of day, from any computer connected to the Internet.
  • Microsoft's Windows Azure  – This service provides virtual servers that can be used for application development and delivery.
  • Rackspace.com — Similar to Windows Azure, Rackspace.com provides servers for hire, but with a wider array of operating systems to choose from.



A primary benefit to cloud computing is that users outsource the care and maintenance of servers to firms that specialize in that capability. When demand warrants, new servers can be brought online in minutes, rather than the days required when a company maintains its own data center. Any sort of computer-based application can be hosted on cloud-based computers, from a website or shopping cart to custom programs for internal use. Thus, with cloud computing, the user is generally responsible for maintaining the applications on the server, while the hosting companies maintain the underlying physical equipment and operating system.

For SaaS, end users are removed from maintaining both the application and the server equipment. Benefits of SaaS versus desktop programs include:

  • Applications, such as QuickBooks Online, allow you to access accounting records from anywhere in the world, instead of from specific computers within your office.
  • New features appear in the software automatically, so there's no need to purchase a software upgrade to be physically installed on each of your computers.
  • Your data is backed up automatically, so a local hard drive crash won't affect your data.
Despite all of the benefits that cloud computing and SaaS provide, there are still risks to consider and manage:


  • Consider the recent situation with megaupload.com, where certain purported illegal actions by a subset of users caused everyone using the service to lose access to data. Think about a toddler having a certain bodily function in a public swimming pool – everyone has to suddenly get out of the water. Similarly, actions by one or more rogue users can cause unexpected and dramatic disruptions for everyone else sharing a cloud-based resource.
  • Both cloud computing and SaaS involve trust, in that you're trusting an organization to hold up its end of the bargain. Intuit, maker of QuickBooks, last year experienced a spate of outages that caused business interruptions for users of their myriad online services.
  • A service you trust and rely on could suddenly change hands, such as Facebook's recent acquisition of Instagram [9]. You may then be forced to find a new service provider if you have philosophical differences with the new owner of a tool that you've relied on or if customer service levels start to slip to unacceptable levels.
  • If you stop paying for the service, access to your data can be immediately terminated. However, many providers offer a grace period. For instance, if you cancel your QuickBooks Online account, your data is maintained for a year, should you decide to resubscribe to the service.




A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 29

Excel Tip: Text Centering Trick

 
By David Ringstrom, CPA



Need to center a title or other data across your worksheet? Most users gravitate to the Merge Cells command, which can wreak havoc with many of Excel's features.


Instead, select the cells you wish to center text within, and then press Ctrl-1 to display the Format Cells dialog box.
On the Alignment tab, choose Center Across Selection from the Horizontal drop-down list.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 19

What Version of Excel is on Your Desktop?

By David Ringstrom



Here are seven quick Excel tips for: converting data, amortization tables, spanning Excel across multiple monitors, filtering for blank rows in Excel 2010, recovering unsaved workbooks in Excel 2010, identifying duplicate values in Excel 2007/2010, and recovering damaged Excel workbooks.


Data Conversion Tricks
From time to time you may encounter worksheets where dates are stored in yyyy-mm-dd format, such as 2012-12-31 as opposed to 12/31/2012. You can convert such inputs with just a few mouse clicks.
  1. Select the column or cells containing the dates you wish to convert.
  2. Choose Data and then Text to Columns.
  3. Click Next twice, then on the third tab choose Date, and then YMD.
  4. Click Finish to complete the process.
Instant Amortization Table
You can add an amortization table to any Excel workbook with a few mouse clicks:
  1. Right click on any worksheet tab and then choose Insert.
  2. Navigate to the Spreadsheet Solutions tab and then double click on Loan Amortization.
Spanning Excel Across Multiple Monitors
Multiple monitor users often launch two different sessions of Excel when they need to see two spreadsheets side by side. Doing so can be viable in certain circumstances, but typically it’s more effective to have a single Excel session that spans both monitors. To do so, open any version of Excel and then double click on the words Microsoft Excel at the top of the screen. Your Excel window should now only cover part of a single monitor. Position your cursor over the title bar and hold down the left mouse button to move Excel up to the top corner of the screen. Next, use your mouse to drag the right-hand corner of Excel across the second monitor.
To then see two different workbooks side by side, choose View, Arrange All, and then double click Vertical. In Excel 2003 and earlier, choose Window, Arrange, and then double click Vertical.


Excel 2010: Filtering for Blank Rows
The Filter command, which appears on both the Home and Data tabs in Excel 2007 and 2010, allows users to pick and choose which rows to display within a list. However, it can be tedious trying to isolate blank rows from a lengthy spreadsheet. You must click the Filter arrow, unclick All, and then scroll down to the bottom to click Blanks. In Excel 2010, simply type the word Blanks in the search box instead.


Excel 2010: Recovering Unsaved Workbooks
It's painful when you accidentally (or even purposefully) close a workbook without saving and then regret it. In Excel 2010, you can sometimes recover your work. To do so, choose File, Info, click the Manage Versions button, and then choose Recover Unsaved Workbooks. Excel 2010 automatically keeps a copy of many (but not all) unsaved workbooks for a few days.


To make the most of this feature, set your AutoRecover option to two minutes instead of the default of ten. To do so, click File, Options, and then Save. Make sure that Save AutoRecover Information settings is set to two minutes and that Keep the Last Autosaved Version if I Close Without Saving is checked as well.


Identifying Duplicate Values in Excel 2007/2010
Select a range of cells. Then, on the Home tab, click Conditional Formatting, Highlight Cells Rules, and then Duplicate Values. Select a formatting option and then click OK. To isolate duplicate instances, right click on a highlighted cell, then choose Filter, and then Filter by Selected Cell's Color.


Recovering Damaged Excel Workbooks
Many users overlook the Repair Workbook feature in Excel. To access it, navigate to the Open window in the usual manner, and then click once a workbook name. Click the arrow on the right-hand side of the Open button, and then choose Open and Repair. This will sometimes correct problems with a malfunctioning workbook. If this step doesn't resolve your problem, install the free OpenOffice product , and use the Calc spreadsheet program to try to open your damaged Excel workbook.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 19

Excel Tip: Text Boxes

 
By David Ringstrom, CPA


The lowly text box is one of many unsung features in all versions of Excel. While it lurks on the Drawing toolbar of Excel 2003 and earlier, the Text Box icon resides prominently on the Insert tab of Excel 2007 and 2010.

 

Use text boxes anytime you need to put a paragraph or more of text into a spreadsheet. This saves you from having to manually merge cells and wrap text within your worksheet.
In addition, Excel 2007 and 2010 users can create bulleted lists within a text box by selecting text within a text box and making a choice from the right-click menu. To create a text box, simply click the icon, draw a box on your worksheet with your mouse, and then add your text.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Mar 07

Seven Quick Excel Tips

By David Ringstrom



Here are seven quick Excel tips for: converting data, amortization tables, spanning Excel across multiple monitors, filtering for blank rows in Excel 2010, recovering unsaved workbooks in Excel 2010, identifying duplicate values in Excel 2007/2010, and recovering damaged Excel workbooks.


Data Conversion Tricks
From time to time you may encounter worksheets where dates are stored in yyyy-mm-dd format, such as 2012-12-31 as opposed to 12/31/2012. You can convert such inputs with just a few mouse clicks.
  1. Select the column or cells containing the dates you wish to convert.
  2. Choose Data and then Text to Columns.
  3. Click Next twice, then on the third tab choose Date, and then YMD.
  4. Click Finish to complete the process.


Instant Amortization Table
You can add an amortization table to any Excel workbook with a few mouse clicks:
  1. Right click on any worksheet tab and then choose Insert.
  2. Navigate to the Spreadsheet Solutions tab and then double click on Loan Amortization.


Spanning Excel Across Multiple Monitors
Multiple monitor users often launch two different sessions of Excel when they need to see two spreadsheets side by side. Doing so can be viable in certain circumstances, but typically it’s more effective to have a single Excel session that spans both monitors. To do so, open any version of Excel and then double click on the words Microsoft Excel at the top of the screen. Your Excel window should now only cover part of a single monitor. Position your cursor over the title bar and hold down the left mouse button to move Excel up to the top corner of the screen. Next, use your mouse to drag the right-hand corner of Excel across the second monitor.
To then see two different workbooks side by side, choose View, Arrange All, and then double click Vertical. In Excel 2003 and earlier, choose Window, Arrange, and then double click Vertical.


Excel 2010: Filtering for Blank Rows
The Filter command, which appears on both the Home and Data tabs in Excel 2007 and 2010, allows users to pick and choose which rows to display within a list. However, it can be tedious trying to isolate blank rows from a lengthy spreadsheet. You must click the Filter arrow, unclick All, and then scroll down to the bottom to click Blanks. In Excel 2010, simply type the word Blanks in the search box instead.


Excel 2010: Recovering Unsaved Workbooks
It's painful when you accidentally (or even purposefully) close a workbook without saving and then regret it. In Excel 2010, you can sometimes recover your work. To do so, choose File, Info, click the Manage Versions button, and then choose Recover Unsaved Workbooks. Excel 2010 automatically keeps a copy of many (but not all) unsaved workbooks for a few days.
To make the most of this feature, set your AutoRecover option to two minutes instead of the default of ten. To do so, click File, Options, and then Save. Make sure that Save AutoRecover Information settings is set to two minutes and that Keep the Last Autosaved Version if I Close Without Saving is checked as well.


Identifying Duplicate Values in Excel 2007/2010
Select a range of cells. Then, on the Home tab, click Conditional Formatting, Highlight Cells Rules, and then Duplicate Values. Select a formatting option and then click OK. To isolate duplicate instances, right click on a highlighted cell, then choose Filter, and then Filter by Selected Cell's Color.


Recovering Damaged Excel Workbooks
Many users overlook the Repair Workbook feature in Excel. To access it, navigate to the Open window in the usual manner, and then click once a workbook name. Click the arrow on the right-hand side of the Open button, and then choose Open and Repair. This will sometimes correct problems with a malfunctioning workbook. If this step doesn't resolve your problem, install the free OpenOffice product, and use the Calc spreadsheet program to try to open your damaged Excel workbook.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Mar 01

Quick Steps to Take in Advance of Google’s Privacy Policy Changes

By David Ringstrom, CPA



If you use any Google products, such as Gmail, Google, YouTube, etc., you've probably been bombarded with a blizzard of “hey, this is important, we're changing our privacy policy” prompts. In theory, Google is simplifying privacy control with a single overarching policy. Cynics, however, may consider this more privacy theater, where we're given the illusion of having control over our actions on the Internet, while companies continue to aggregate even more information about us as individuals.


Regardless, if you use Google products on or after March 1, 2012, you'll give them implicit permission to aggregate your web search history, YouTube viewing habits, and other information Google has gleaned about you into a single mega-dossier. Previously, search histories were siloed and controlled by individual privacy policies, but going forward, all your information will be stirred together into a big data soup.
If you haven't already done so, it would be a good idea to read Google's new privacy policy.



Although you can't stop Google from tracking you in myriad ways, you can prevent your web search history from being automatically added to your dossier:
  1. Log into your Google account: https://accounts.google.com/Login.
  2. Next, visit the Google Web History page: https://www.google.com/history/.
  3. If a “No Thanks” button appears, your web history isn't being collected. Otherwise, click the “Remove All History” button, and then click OK on the confirmation prompt.


To see step-by-step pictures, visit the Electronic Frontier Foundation's (EFF) DeepLinks blog post on the topic. A second post documents how to remove your YouTube viewing and search history.

According to the EFF, turning off Google's history features doesn't prevent it from gathering information for internal purposes, but collected data is partially anonymized after eighteen months. Although much has changed in the past six years on the Internet, the EFF article Six Tips to Protect Your Search Privacy published in 2006 is now more relevant than before.



A previous version of this article first appeared on www.accountingweb.com
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Feb 03

Free Spreadsheet-Based Form 1040 Available for 2011 Tax Year

By David H. Ringstrom, CPA


Glenn Reeves of Burlington, Kansas, recently released his fifteenth annual spreadsheet-based version of the U.S. Individual Tax Return, commonly known as Form 1040. Since 1997, Reeves has pursued this “labor of love,” which means he allows any taxpayer to download and use the spreadsheet for free.


Reeves' spreadsheet empowers tax payers to use their tax return spreadsheet applications, such as Microsoft Excel, OpenOffice, or Gnumeric Portable. As you can see in Figure 1, the form closely mirrors the official IRS format, but Reeves doesn't guarantee that the IRS will accept printed versions of the form. He personally files his return online after using the spreadsheet to compute his return.


Figure 1: Reeves has updated his Excel-based version of IRS Form 1040 for the 2011 tax year


The 2011 version of the spreadsheet includes both pages of Form 1040 as well as the following supplemental schedules:
  • Schedule A – Itemized Deductions
  • Schedule B – Interest and Ordinary Dividends
  • Schedule C – Profit or Loss from Business
  • Schedule D – Capital Gains and Losses, along with its worksheet
  • Schedule E – Supplemental Income and Loss
  • Schedule L – Standard Deduction for Certain Filers
  • Schedule SE – Self-Employment Tax
  • Form 6251 – Alternative Minimum Tax – Individuals
  • Form 8949 – Sales and Other Dispositions of Capital Assets


The spreadsheet also includes several worksheets:
  • Schedule D Worksheet
  • Line 10 – State and Local Income Tax Refund Worksheet
  • Lines 16a and 16b – Simplified Method Worksheet taxable annuities and pension benefits
  • Lines 20a and 20b – Social Security Benefits Worksheet
  • Line 32 – IRA Deduction Worksheet
  • Line 44 – Qualified Dividends and Capital Gain Tax Worksheet
  • Line 51 – Child Tax Credit Worksheet


Five additional worksheets round out the tool:
  • W-2 input forms that support up to four employers for each spouse
  • 1099-R retirement input forms for up to four payers for each spouse
  • SSA-1099 input form to record Social Security Benefits
  • A tax table
  • Change log that records revisions to the spreadsheet


All of the worksheets in the 1040 workbook are password protected. Most of the underlying formulas are hidden, but you're free to add new worksheets to the file or create links to other workbooks. Reeves notes that the spreadsheet is constructed in such a fashion that proper use of the spreadsheet means a user won't need to access any of the protected cells. New this year, though, is the ability to add comments to the unprotected cells, which wasn't possible in previous versions. As shown in Figure 2, the spreadsheet also offers some limited error checking.


Figure 2: The spreadsheet offers limited error-checking prompts.


Although Reeves gives the spreadsheet away, he does accept appreciation contributions, which he reports on his tax return as income. He also donates 10 percent of any contributions to his church. Spreadsheet-based versions of Form 1040 are available for tax years 1996 through 2011 on Reeves' website.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Feb 02

Excel Tip: Table Feature Enables Effective Analysis

By David H. Ringstrom, CPA


Excel 2007 and 2010 users often overlook the useful Table feature, which can streamline data analysis. This feature is a major overhaul of the List feature that has long lurked on the Data menu of earlier versions of Excel. In this article, I'll introduce you to the Table feature and demonstrate several ways it can help you work faster in Excel.
When you convert a range of data in a worksheet to a table, several things happen simultaneously, as shown in Figure 1:
  • Excel places a Filter arrow at the top of each column.
  • Excel applies a table-style format, which shades alternating rows.
  • A Design tab appears in the ribbon, from which you can manage aspects of the table.

Figure 1: Excel's Table feature simplifies data analysis.

Other benefits of tables include:
  • The headings from the first row move up into the worksheet frame when you scroll down if the entire list can't be displayed on the screen. This means you don't have to freeze the worksheet panes to keep the title row in view.
  • Pivot tables based on tables have better integrity, as tables automatically expand to incorporate new data that you append. You must still refresh the pivot table when you add data to the table, but you won't need to manually resize the source data range.
  • Formulas that you add within the table get copied down the entire column automatically, as shown in Figure 4.
Long-time Excel users may remember a Table command on the Data menu of Excel 2003 and earlier. This command was renamed Data Table in Excel 2007 and later, and it appears under the What-If Analysis button on the Data tab of the ribbon. A Data Table shows you how changing one or two variables in a formula will affect the outcome. Conversely, the Table feature is used to analyze lists of data.
To create a table, follow the numbered steps in Figure 2.


Figure 2: You can convert a list to a table with four mouse clicks.



You can then add a Total Row as shown in Figure 3. Formulas within the Total Row only tally the visible rows, making it easy to view real-time statistics as you filter the table.
Figure 3: The Total Row in a table only tallies the visible rows.



Figure 4 illustrates another special characteristic with regard to formulas that you add to tables. You may wish to turn off the Use Table Names in Formulas setting, which appears in the Formulas section of Excel's Options window – the formula in Figure 4 would appear as =[@[Total Sales]]/[@[Cases Sold]] if I had left this option on.
Figure 4: Formulas that you add to a table are copied down automatically.



At any point, you can convert a table back to a normal range – click the Convert to Range button on the Design tab. Keep in mind that this command doesn't remove the formatting or total row. To save time, you may wish to turn off any undesired table features before you convert your data back to a normal range.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Feb 01

On the Horizon: A New Version of Microsoft Office

By David H. Ringstrom, CPA


Microsoft announced that the next version of its flagship productivity suite has entered the “technical preview” phase of its development cycle. This program allows Microsoft customers to test and provide feedback on what's currently known as Office 15. This testing phase is a precursor to the public beta testing program expected to commence this summer. Recent Office public beta programs have allowed any interested user to download and take new versions for a spin prior to formal release.


Microsoft is being tight-lipped about new features, but a blog post by P.J. Hough, CVP of Development, notes “With Office 15, for the first time ever, we will simultaneously update our cloud services, servers, and mobile and PC clients for Office, Office 365, Exchange, SharePoint, Lync, Project, and Visio.” This could be interpreted to mean that we'll see tighter integration between the desktop versions of Excel, Word, PowerPoint, and OneNote as well as their web-app, cloud-based, and mobile counterparts. The user interface for Office 15 could be up for overhaul. Windows 8 centers around a new Metro look that's designed to be operated by keyboard, mouse, finger, or pen in the same fashion as on a computer, tablet, or smartphone.


The suite will probably be dubbed Office 2012 or 2013, and it could be released alongside Windows 8 toward the end of 2012. With that said, no definitive announcements have been made regarding either product, but a Developer Preview of Windows 8 is currently available for pre-beta testing.



A previous version of this article first appeared on www.accountingweb.com .


About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Jan 13

Deloitte Buys Boutique Mobile App Firm

By David Ringstrom



Big 4 accounting firm Deloitte started the new year by purchasing Seattle-based Ubermind. The 200-person firm develops mobile apps, e-commerce, and content management solutions for such companies as Apple, Target, Alaska Airlines, TruTV, Amtrak, and REI. The acquisition is designed to complement the range of services that the 180,000 person global services firm provides its clients. Janet Foutty, national managing director of technology at Deloitte told AccountingWEB, “From the boardroom to the living room, and everywhere in between, the next-generation enterprise is upon us. Fusing the art and science of emerging technologies is revolutionizing our clients' businesses – as well as our own. The acquisition combines the creative and technical strengths of both organizations to bring depth and scale to digital strategy, design, engineering, and integration for the marketplace.”


Foutty further notes, “With this acquisition, we are now able to offer our clients industry-focused mobile apps, designed from the ground up for enterprise integration and market deployment, to help them address virtually any digital technology challenge in their business – now and toward the future. This transaction amplifies Deloitte's full lifecycle professional services relating to strategy, creative, mobile, web, eCommerce, content, and enterprise mobile enablement to help clients gain business advantage.”


The acquisition price hasn't been disclosed publicly, but Seattle-based GeekWire estimates that Deloitte paid in the range of $40 to $50 million for Ubermind. Mobile apps are often described as “disruptive” as they often enable previously unattainable commerce and lifestyle actions. Ubermind's previous projects range from serious to whimsical: the firm won plaudits for the iPad, iPhone, and Android apps it developed for Target, but is also known for the iLightr app that simulates a perennial concert experience. Deloitte isn't the only behemoth moving into mobile development.


Just days ago Walmart acquired Portland-based mobile app firm Small Society. This tiny firm has developed mobile apps for Starbucks, Whole Foods, Amazon, Zipcar, and the Democratic National Committee. Walmart appears to be focused on improving its own online presence. According to a press release from Walmart Labs, “We're here to serve people with busy lives and much bigger concerns than the coolness of our apps. If we provide them a way to save time, save money, and get done with their shopping so they can enjoy their loved ones, we've achieved success.”
This nascent acquisition trend may accelerate in 2012 as smart phones and tablet computers continue their race toward ubiquity. For example, it's doubtful that Walmart will have Small Society develop any more mobile apps for Amazon, so another press release may be in the offing soon.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

Nov 10

Intuit’s New QuickBooks Online Accountant Brings the Cloud – and Its Benefits – to Accountants

By David H. Ringstrom, CPA



QuickBooks Online, a cloud-based accounting solution for small businesses, has experienced 40 percent growth over the past year; it now has 280,000 users. More and more small business owners are reaping the benefits of anytime, anywhere access to their accounting records. Further, cloud-based solutions, such as QuickBooks Online, eliminate the need to install periodic software patches and updates because new features are added automatically.


While QuickBooks Online makes things easier for day-to-day users, it can make the accountants' tasks harder because commands get buried within the software. In response to this, Intuit has introduced a new product: QuickBooks Online Accountant (QBOA). This add-on to QuickBooks Online is aimed squarely at helping accountants serve their clients more efficiently.


A simple sign in to QBOA gives the accountant access to all of his or her clients' accounting records (see Figure 1). This dashboard allows accountants to maintain contact information for all of their online clients in one place.


Figure 1: A single sign in grants access to multiple clients.


A new “Accountant” tab (see Figure 2) appears within a client's books and enables access to several time-saving features. “This provides one place for all of an accountant's tools and reports as well as a snapshot reconciliation of all accounts,” said Samir Khosla, director of product management for Intuit Accounting Professionals Division. “Plus, updates appear automatically, so the product always has the latest features.” (Keep in mind that only the accountant has access to the Accountant tab; it will not appear to end users when they sign in to QuickBooks Online.)


Figure 2: The Accountant tab adds features and aggregates frequently used tools.


The Accountant tab assembles the most frequently used accountant tools on a single screen – the “Accountant Center.” Features include:
  • The ability to reclassify transactions in batch, which allows the accountant to change the general ledger account and/or class for multiple transactions all at one time.
  • A simple way to find deleted or voided transactions.
  • The ability to write off multiple invoices in batch.
  • A reconciliation snapshot that makes it easy to monitor bank and credit card accounts. QBOA also lists problems that can affect reconciliations, such as changed transactions and auto adjustments (see Figure 3). Khosla said, “This is one of my favorite features, as the accountant is notified if a client takes a shortcut during reconciliation.”


Figure 3: Reconciliation issues are brought to the accountant's attention.


QBOA also offers some reporting enhancements:
  • Accountants can mark journal entries as “Adjusting Journal Entries” and then generate an adjusted trial balance report, also known as a working trial balance.
  • Comparison reports allow one-click access to this year/last year profit and loss and balance sheet reports. While such reports can be created in QuickBooks Online, they must be built by hand.


Accountants are invited to try QBOA free of charge during Intuit's pilot phase. To access QBOA, go to http://accountant.intuit.com/qboa. According to Khosla, “the focus has been on time-savings, and eventually [QBOA] will be a subscription-based product.” Pricing will be announced after the pilot phase.


Intuit also is offering practitioners up to 6 free CPE credits through a virtual conference on cloud-based accounting and tax solutions. The conference will be presented November 15-16, 2011, from 10:00 a.m. to 5:00 p.m. central standard time.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link