A Wilmington NC CPA discusses Business Analysis using QuickBooks and Excel

Let’s examine some common types of business analysis used for ongoing management and strategic planning. As always, we’ll examine these issues through the eyes of the CPA for Wilmington NC Widgets, our demo company.

Background for the Wilmington NC Widget Scenario

Wilmington NC Widgets is a retail widget sales company with a widget manufacturing component. They’ve had a CPA CFO for several years now to manage the accounting, cash flow, budgeting, payroll, tax management etc. WNW uses QuickBooks with an inventory add on module as their accounting software. But the CPA at Wilmington NC Widgets knows his real job is monitoring and analyzing a multitude of business parameters to give WNW a competitive edge in the marketplace.

Performance Dashboard

WNW is a closely held S corporation with two family members running it. Over the years the CPA at Wilmington NC Widgets knows how these folks think about their business. So he generates a monthly collection of graphs, reports and financial analysis, formatted to best display the underlying data, as part of ongoing management and strategic planning. Aggregated QuickBooks bookkeeping and accounting data is transformed into managerial tools.

“Excel allows you to combine your aggregated QuickBooks bookkeeping data with business statistics to build essential managerial tools.”
Gary Bode, CPA
Wilmington NC accountant

QuickBooks Bookkeeping and Limitations: Using Excel

QuickBooks is a bookkeeping and accounting program. It also has some managerial tools. But it isn’t designed to be a business analysis program per se. However, the CPA at Wilmington NC Widgets uses QuickBooks as the source data for most of the performance dashboard. How? By customizing QuickBooks reports and then exporting them into Excel, Microsoft’s excellent spreadsheet program. And then tweaking them through:

• Additional calculations.
• Turning them into graphs.
• Turning them into Pivot Charts.
• Simple sorting and conditional formatting.

Graphical Representation of Data

The management and CPA at Wilmington NC Widgets are human. Humans are visually oriented. Trends buried in pages of numerical sometimes pop out immediately when graphed. Excel has a great variety of graphs, but when it really comes down to conveying information, three or four types remain the most useful. While a dual axis line graph is illustrated above, simple pie charts, bar graphs and bubble charts are appropriate too depending on which displays the information most intuitively.

The demo graph shows two consecutive 12 month periods of revenue for a business component, with the percentage of change between them shown as points against the secondary axis. The current 12 month revenue series includes a trend line, based on a three month moving average.

“Wilmington NC Widgets has become too complex for the management and CPA to run by feel. A monthly performance dashboard is used to monitor and analyze multiple business parameters. Graphs and other visual enhancements facilitate informational conveyance.”
Gary Bode, CPA Wilmington NC

Basics: Sorting and Conditional Formatting

In some cases, the CPA just sorts and applies conditional formatting to reports exported from QuickBooks to Excel. Why bother? Even though the management and CPA of Wilmington NC Widgets are intimately familiar with operations, it is difficult to immediately find the relevant information in rows and columns of numbers.

WNW sells over 600 types of widgets. And the demand for some types of widgets changes over time. So an example of a basic sort and format spreadsheet the CPA would run is a QuickBooks report showing the number of each widget sold during the month, the total revenue of each type of widget during the month (remember the unit price can change during the month), and the average cost of each type of widget. After exporting into Excel, a column calculating gross profit on sales from each type of widget is calculated. [total sales revenue – (# of widgets sold x average cost)]. Then conditional formatting is applied that colors a cell yellow when gross profit is below a specific dollar figure, say $200. Another column is added showing the percentage of total monthly revenue each type of widget represents. Finally a sort is applied such that the widgets are listed in descending order, based on the percentage of total widget revenue. So the aggregated QuickBooks bookkeeping has been turned into a single report that flags widgets generating less than $200 a month and sorts all widgets by their percentage contribution to total widget sales.

If you’d like a free initial consult on any accounting, managerial or tax issues, please call us at (910) 399-2705.