More Spreadsheet Tools and Techniques
In This Chapter
- Using spreadsheet templates
- Building a budget template
- Developing a shadow accounting system
- Filtering and analyzing data
- Creating supporting schedules
No matter what accounting software you choose, you’re always going to bump into limitations when it comes to analyzing data or tracking additional information your accounting software can’t manage. For instance, in Chapter 14, we discuss a supporting schedule for prepaid expenses. Your accounting software requires you to lump all prepaid expenses into a single bucket, but you’ll want to maintain a listing of the amortization rate of each expense if you have more than one prepaid expense at a given time. Spreadsheets give you an easy way to supplement weaknesses such as this in your accounting software.
You also might find it helpful to create a budget for your business. Most accounting software programs have a budget feature, but these are often limited to carrying forward actual amounts from prior years. Within a spreadsheet, you can develop calculations that allow you to do much more targeted forecasting than just simply raising last year’s office supply expense account by 5 percent.
Over the years, we’ve seen people use spreadsheets as their accounting software. This is very risky because at some point, your transactions will overwhelm your spreadsheet. However, we will propose using a spreadsheet to create a shadow accounting system you reconcile to your accounting software. Doing so allows you to forecast cash flow and ensure that key transactions don’t get lost amidst the numbers in your accounting software.
We close the chapter with examples of ways you can analyze data from your accounting software, such as by creating charts and filtering lists of data.
Spreadsheet Templates
In Chapter 21, we walked you through an exercise of building an amortization schedule from scratch. Although the exercise was a useful vehicle for giving you some exposure to how spreadsheets work, we’re happy to report that you won’t need to build such a schedule ever again.
Spreadsheet templates are prebuilt forms you can use and sometimes adapt to your own purposes. You’ll find it easiest to locate templates in Microsoft Excel, but even cloud-based spreadsheets such as Google Sheets have templates you can use.
Accessing Templates
In Microsoft Excel, you can access templates in two ways. The first is to choose File, New, and search for the template you want, as shown in the following figure. In this case, you’d enter “loan amortization” in the search field and press Enter. Double-click on the template that appears, and you have a ready-to-use amortization schedule.
A second way is to right-click on any tab within an existing spreadsheet and choose Insert. Click the Spreadsheet Solutions tab in Microsoft Excel to choose from templates such as a billing statement, expense report, loan amortization schedule, and more. The benefit of this method is that the template will be inserted in your current workbook instead of as a standalone workbook.
ACCOUNTING HACK
Spreadsheets don’t have to be all about accounting. In Microsoft Excel, you can use the search term “NCAA” to locate a bracket template to use when college basketball’s tournament time rolls around in March. You also can search for other sports, recipes, and movies.
Spreadsheet templates can give you a jump-start on a number of aspects of your business.
Business plan templates, for example, can help you organize your thoughts on a new business you’re contemplating starting. You also might need to present a business plan to a bank or other lender if you need to borrow money. Marketing budget templates can help you forecast how to best allocate funds to promote your business. Employee shift schedule templates can give you a framework for scheduling employees. Buy versus lease templates have built-in calculations that can help you determine whether you’d be better served buying or leasing equipment or vehicles for your business. And breakeven analysis templates can help you determine the minimum price needed to cover your costs. Once you know this amount, you then can use other calculations to determine the markup you should add to your products to deliver an adequate gross profit.
DEFINITION
The markup on a product is the difference, usually shown as a percentage, between its selling price and its cost. The selling price of a $100 item with a 40 percent markup is $140 ($100 × 140 percent). In every case, you’ll add 100 percent to the desired markup percentage and then multiply by the unit cost. The words markup and gross profit are sometimes used interchangeably. Gross profit is the selling price for an item less the selling costs. On your profit and loss report, you might see the gross profit for your entire business instead of just a single product.
There are a dizzying number of spreadsheet templates available. If you’re using Microsoft Excel, you can search within the software itself. Or do an internet search for the name of your spreadsheet program and a specific term, such as Google Sheets budget template. Most of the results will be free to use, but some may require a nominal fee.
Developing the Foundation of a Budget
Budgeting is a key part of a successful business. By using budgeting tools to predict, compare, and analyze your business performance, you can have a clear picture of where you’re going and how you’re going to get there.
With most software programs, the easiest way to start a budget for the coming year is to start with all the income and expenses from the current year. You then can apply a percentage increase or decrease to individual accounts, or even across the board to all accounts if you think everything will increase or decrease at the same pace. More likely, you’ll probably examine each account individually and decide, based on your experience and knowledge of your business, how you expect those accounts to perform in the coming year.
For example, you might expect income to increase by 10 percent, salaries to increase similarly, but other expenses like rent and utilities and office supplies to remain the same from the current year to the next. That kind of analysis helps you nail down your budget numbers.
Once you have the budget in place, it’s important to rely on it regularly for information about your business. At least quarterly, you should compare your budget to how you’re actually doing and see if you’re on target to meet the budget for the rest of the year.
BOTTOM LINE
Budgets aren’t written in stone (unless you’re a government office), so it’s okay to make adjustments during the year. The idea is for the budget to be a conduit toward success, not an albatross that dogs you when things don’t go as expected.
Business budgets can take many forms, and everyone approaches budgeting from a different angle. Here, we show you an easy way to get a jump-start on creating a budget for your business. Assuming you want to create a budget for an entire year, a customized report from your accounting software can get you started quickly.
The exact steps you’ll carry out will vary based on your accounting software, but we’ll demonstrate the techniques in both Xero and QuickBooks to give you a good idea what to do in your program. In short, we’ll generate a 12-month income statement in Xero and again in QuickBooks, export the reports to Excel, and convert the reports to budgets. Along the way, we’ll explain some nuances you need to know to avoid frustration with this seemingly simple task. You’ll likely discover other uses for a 12-month income statement as well.
Exporting from Xero
To export a 12-month report in Xero, choose Reports, Compare Periods, and make these selections:
- Date: Set this to the last month of your calendar year, or actually the end of the 12-month period you want to analyze.
- Period: Set this field to 1 month.
- Compare With: Set this field to Previous 11 Periods.
Click Update to see the report.
When you have the report arranged on-screen, you’ll export the report to your accounting software. From the Export button in the lower-right hand corner of your report, you can make one of three choices:
Excel: This enables you to create an Excel 97 to 2003–compatible workbook almost any modern spreadsheet can open.
PDF: This creates a file you must open with Adobe Acrobat Reader (acrobat.com/reader) or a competing PDF viewer. PDF files are not suitable for spreadsheet analysis.
Google Docs: This choice enables you to post the report to your Google Docs account, within which you can use the Google Sheets application as we discussed in Chapter 21.
When exporting reports from a cloud-based software such as Xero or QuickBooks Online, you’re prompted to save the file onto your computer if you choose the Excel format. You must then double-click on the resulting download icon to open the file in your spreadsheet program. Conversely, when you export a report from a desktop-based accounting software, you’ll find that the report generally opens automatically in your spreadsheet program.
RED FLAG
As of this writing, when you export Excel files from Xero, the files end in .xls. Be careful when opening such files in Excel 2007 and later because certain features in these versions of Excel are incompatible with .xls spreadsheets. You also won’t be able to insert templates into .xls workbooks. To resolve this issue, convert the files to .xlsx using the Convert command either on the main menu or in the Info section of the File menu. This updates the file and restores full functionality. QuickBooks Online recently added the .xlsx format as an export choice, but your accounting software might still use the old .xls format.
Exporting from QuickBooks Desktop
To export a 12-month report in the desktop versions of QuickBooks, choose Reports, Company & Financial, and Profit & Loss Standard. You’ll then carry out two steps using the drop-down lists across the top to create a 12-month report:
- Dates: Change this to Last Fiscal Year or use the From and To fields to specify a given date range.
- Show Columns: Change this from Total Only to Month.
At this point, a 12-month report will appear on-screen. Next, click the Excel button, choose Create New Worksheet, and click Export. Your report will automatically open in Excel.
In most cases, the exported report contains formulas that will recalculate automatically if you change any of the numbers in your report. This can give you a helpful jump-start on any analysis you need to perform. The trade-off is that the report format might not lend itself to some of the features you want to use in your spreadsheet program.
Most accounting programs enable you to export on-screen reports to an Excel spreadsheet format.
RED FLAG
Some accounting programs apply a special Text format to certain columns of exported reports. Because of this, if you insert formulas in or adjacent to a column with Text format, you’ll often see the formula itself in the cell instead of the result, as you’d expect. The solution is to change the number format before you add any formulas. In Excel, click on the affected column and choose General from the list in the Number section of the Home tab. CSV files only contain text and numbers—without formatting—so you won’t experience this problem with those exports.
Building Your Budget
At this point, you should have a 12-month income statement available in Excel. Assuming you have some familiarity with functions in Excel, you can use this format as the basis for projecting revenue and expenses for an upcoming calendar or fiscal year. Edit the values in various cells as needed to adjust the budget to reflect your assumptions and projections for the next year. As noted, not every accounting software export includes formulas in the total rows, so you might need to build new formulas for the budget total rows.
Exporting a 12-month income statement from your accounting software into a spreadsheet gives you a jump-start on developing a budget or performing other types of forecasting and analysis.
You might be able to pull in templates, such as the breakeven analysis we discussed earlier, or other tools to help you use intricate calculations that allow you much more control over your forecast rather than just increasing last year’s numbers by, say, 5 percent.
Once you get your budget developed, you can either manually type the results back into your accounting software or, depending on the program you’re using and your spreadsheet skills, convert your budget into a format you can import into your accounting software.
Maintaining a Shadow Accounting System
Accounting in general is oriented to be a historical record of your business. However, as a business owner, you also must look to the future—to forecast your short-term cash flow needs, for example. Your accounting software might offer a forecasting feature, but cash flow forecasts don’t generally lend themselves to one-size-fits all solutions. You might find it helpful to use a spreadsheet program instead to develop a shadow accounting system.
In effect, this will be a very simplified version of your accounting records you can use to forecast cash flow and ensure you don’t miss handling an expected transaction. Everyone has different requirements, so the solution we suggest here might not be an ideal fit for your business. But hopefully it will spark ideas that might work for you.
Another benefit to creating a shadow accounting system is that your numbers are broken down into an understandable format you can keep reconciled with your books. This gives you another way to summarize revenues and expenses and forecast upcoming cash requirements. We have two primary sections in the spreadsheet: Cash Inflows and Cash Outflows.
The first column of our shadow accounting system is a listing of customers who presently have outstanding invoices. Adjacent columns cover time periods that make sense for your business, which might be daily, weekly, semimonthly, or monthly. Our example assumes we’re able to arrange paying most of our bills on the fifteenth and the last day of the month.
Forecasting Inflows
You’ll notice in the following example that the spreadsheet leads off with Beginning Cash. The ultimate goal of this spreadsheet is to be able to accurately forecast cash flows. Cash flow forecasts involve both known amounts and unknown amounts, which we address as follows:
- The Customer rows represent unpaid invoices from your customers and when you expect to receive payment.
- The Projected Revenue row represents the amount of revenue you expect to collect during that time period.
- The Total Cash Available row enables you to see your forecasted ending cash balance for that period of time.
Here is the beginning of our shadow accounting system that can forecast cash balances.
The Vendor rows shown in the preceding example assume your business typically has recurring bills for the same set of vendors. The amounts shown can either be actual or forecasted amounts you need to pay each vendor during that time period.
The Ending Cash balance should match your accounting software for periods that have already elapsed, and it will show your projected Ending Cash balance for future periods.
Depending on your spreadsheet skills, you may be able to make portions of the forecasting spreadsheet update automatically by exporting reports from your accounting software, but there’s also value in taking the time to manually key in the amounts. Doing so forces you to reflect on the realities of your business. Glancing at on-screen reports doesn’t always force the necessary deep thinking required to successfully plan for your business’ short-term needs.
Analyzing Accounting Data
As you gain familiarity with your accounting software, you might find yourself frustrated by the limited ways you can analyze your data. Or you may tire of repetitively changing parameters on reports just so you can get a different view of your data.
This is another area where exporting reports to a spreadsheet can be rather beneficial.
Filtering Data
Even accountants can get overwhelmed easily by looking at too much data at once. The Filter feature available in many spreadsheet programs gives you an easy way to temporarily collapse a list of data down to just the rows you need to see at a given time.
To filter your data, first select any cell within a list of data and then choose Filter from the Data menu in Excel or Google Sheets. When you do, each column in your list will get a little arrow from which you can select the rows you’d like to see on-screen.
You can filter on as many columns as you want, which enables you to hone in on exactly the data you need to see. This is helpful in locating transactions you might struggle to find in your general ledger, such as those we discuss in Chapter 7. Filtering also enables you to isolate transactions by customer name, vendor name, account, or amount.
The following example shows the Filter arrows in place on a Vendor Summary report exported from QuickBooks Online. By clicking the arrow in cell B6 and then choosing Number Filters, we can display a submenu of options for filtering ranges of numbers. For instance, this can help you quickly identify expenses above a specific threshold.
A hidden benefit of the Filter feature in a spreadsheet is that the drop-down list at the top of each column shows you one of each item in a list. It’s far easier to turn on the filter feature and take a quick look to see if what you’re trying to find is there or not than page down through a report hoping what you’re looking for will catch your eye.
The Filter feature available within many spreadsheets enables you to collapse a list to just items that meet certain criteria.
Quick Analysis
Microsoft Excel in particular offers so many analytical features most spreadsheet users are unaware of. Recognizing that most users can’t easily get their head around features like Conditional Formatting, Charts, PivotTables, and so on, Excel 2013 and later versions offer a Quick Analysis feature.
For example, if you export your Profit and Loss report to Excel as described earlier, you can select, say, the Revenue section and click the Quick Analysis button that appears below your selection. From there, hovering over any command previews various analytical features in Excel.
As shown in the following screenshot, positioning your mouse over the Data Bars command adds bars to the numbers you’ve selected. The larger the number, the larger the bar; the smaller the number, the smaller the bar. Hover over any Quick Analysis feature to preview the effect a given analytical feature adds to your spreadsheet, or click on the command to apply the setting.
The Quick Analysis feature in Excel 2013 and later allows you to add data visualization features to your accounting reports.
Recommended Charts
The Quick Analysis feature in Excel 2013 and later offers a couple choices of charts, but you also can click the More command on the Charts tab to display the Recommended Charts feature. This allows you to quickly visualize what your data would look like in a variety of charts.
Excel offers dozens of different types of charts, and it can often be daunting to determine which type of chart works best to tell the story for a given set of data. A preview of each chart, based on the data you selected, appears on-screen when you hover over each of the recommended chart options. Just click on a given chart to add it to your spreadsheet. A Recommended Charts command also appears on the Insert menu of Excel 2013 and later.
If you’ve explored Excel much, you’ve probably heard of PivotTables but, like many users, assume they’re an advanced feature beyond your reach. In reality, PivotTables are easy to use as long as your data is in the expected format, which is a list within a spreadsheet that has a title at the top of each column.
For example, in the desktop versions of QuickBooks, an ideal report for using PivotTables is the Transaction Detail by Account. Here’s how to run this report:
1. Choose Reports, Accountant & Taxes, and Transaction Detail by Account.
2. When the report appears on-screen, change the Total By drop-down list at the top of the report to Total Only and then export the spreadsheet to Excel.
3. Delete any blank rows from the report. QuickBooks Desktop in particular adds a blank row beneath column headings.
4. Select any cell within your list, and click Recommended PivotTables on the Insert tab to view different summaries of your accounting data.
The Recommended PivotTables feature in Excel 2013 and later provides instant summaries of list-based reports.
You can also use the features we describe throughout this chapter in Excel 2010 and earlier, but the newest versions of Excel offer more enhancements. The Complete Idiot’s Guide to Microsoft Excel 2010 2-in-1 by Richard Rost is a useful reference for anyone using Excel 2007 or 2010. Features such as charts, PivotTables, and conditional formatting can feel intimidating at first, but they’re often some of the easiest features to use because Excel performs the number-crunching and presentation for you, often with just a few mouse clicks.
Maintaining Supporting Schedules
In Chapter 14, we discussed how your accounting software won’t always be able to store the amount of detail you need to monitor a given account balance. For instance, we talked about prepaid insurance, where you pay for an entire year at once but must move 1⁄12 of the amount to an expense account each month.
If you only have one prepayment amount you’re amortizing in this fashion, your accounting software will have the supporting detail. But if you have two or more prepaid expenses, particularly those that are amortizing over different rates, you’ll be hard-pressed to keep track of where the different prepaid balances stand.
In this case, you could create a supporting schedule in a spreadsheet to help you keep track.
Supporting schedules, such as this one that tracks prepaid expenses, can provide detail your accounting software cannot.
Amortizing (or amortization) is the process of decreasing a balance over a period of time. When you amortize a prepaid expense, you reduce the prepaid amount month by month until the prepayment is extinguished. In a similar fashion, you amortize loans such as a car payment or mortgage by paying down a portion of it each month.
The Least You Need to Know
- Before you start creating spreadsheets from scratch, check the Excel template library. Chances are good that what you need is already there.
- Budgeting gives you a map for the year ahead, keeps you focused, and allows you to compare actual performance with the expectations you had when you began the year.
- Using a shadow accounting system provides you with a simple way to maintain a forecast of cash flow so you don’t have to wonder whether or not you’re going to have the ability to meet future commitments.
- Supporting schedules help you store activity related to account balances that your accounting software can’t store.