Excel Spreadsheets

When clients arrive at our office, some arrive with a shoebox full of receipts, while others are more organized and have spreadsheets and supporting statements ready for our review. The accuracy of a tax return depends on the quality of the information and documentation provided by the client. An Excel spreadsheet is an excellent tool to help keep you organized and can greatly simplify handing over information to your accountant at tax time. 

How Excel Can Help You

• Excel is a readily available, inexpensive alternative to software programs like Quicken and QuickBooks.
• Staying organized with Excel will help provide more accurate tax returns, and that can translate into more money in your pocket.
• Staying organized with Excel will also make your accountant’s job easier, meaning lower tax preparation fees.
• Excel can be applied to both your personal finances and small business recordkeeping providing clearer readings of your financial condition.

Using Excel for Personal Finance

Household Budgets

The most common use of Microsoft Excel for personal finance is budgeting. Using Excel to track your finances provides an easy and accurate reading of your monthly income, your expenses and the amount you can afford to put into savings. When creating a budget, it is important that you include all forms of income, whether from wages, your small business, or your investments. 

You will then need to account for all expenses from cost of housing and transportation, to meals, entertainment and date-nights. And don’t forget about the baby sitter for the kids. Your budget should also account for savings, including long-term/retirement, emergency savings, and specific/large purchases. With so many numbers to juggle, budgeting can be a struggle, but with Excel, all income, expenses, and savings are categorized, making it easier to stay on target and leaving nothing to chance. 

Don’t know where to start? Microsoft 365 has free budgeting templates to explore.

Assets & Portfolios

With its ability to view and sort holdings, including purchase price, periodic closing prices, and returns, Excel is a great tool for keeping track of investments in an organized manner. However, it is capable of much more than replicating a an account statement. Metrics like the standard deviation of an asset or portfolio, the percentage of return, and the overall profitability of an investment can all be calculated once you learn a few simple formulas. 

Excel worksheets can be as simple or as complicated as you want to make them.  One of the many advantages of using formulas is that once you enter a calculation you can reference the cell with your result in other places in your worksheet. Excel can update your rate of return by asset class and even create a chart of year-to-date earnings for your entire portfolio.  

How Business Owners Can Use Excel

The Basics

Using Excel is a useful and cost-effective tool to help run your business. Accounting software can be expensive and for many small businesses may not be necessary. Excel can track your business income and expenses. This is especially useful at tax time and saves you time and money when preparing to meet with your accountant.  Just as for personal finances, Microsoft 365 is a great resource of templates for your small business.

Personalize Your Chart of Accounts 

Whether you create a file from scratch or download an online template, the first step in building your business’s bookkeeping system is a Chart of Accounts.  This is a list of categories that will cover the transactions generated in your business. Any income or expense should be easily assigned to one of these accounts.  

Get started by making a list of every category of income and expense in your business. That way, when recording transactions, it will be easy to assign them to the appropriate account.  If you buy material or have labor costs for a particular job, the type of accounts you create for these expenses is called a Cost of Goods Sold (COG). COGs help you track the profitability of the products or projects you complete, without including overhead and operating expenses.  

Tracking Your Income and Expenses

Now you are ready to create a file where you will record all the transactions of the business.  Set up a worksheet labeling the first two columns for the date of the transaction and the payee. Then continue across the page assigning each account to a column.  At the bottom create totals for each column.  If your business is very small, a single worksheet may suffice for the entire year. More likely you will want to keep track of each month’s activity.  It’s a snap to do this in Excel. Create a new worksheet in the same file by clicking on the “+” next to the open worksheet tab.  Repeat 10 times and label each tab by month.  Now you can quickly review your monthly income and expenses.  

Set up Your Income Statements

An Income Statement is a specific arrangement of the totals for the account activity you have tracked for a period of time.  To create an Income Statement for your business, you will need to roll up your sleeves and learn some new tricks in Excel.  Downloading an online template will still require you integrate your transactions worksheet data with the Income Statement. If you have the will, you can do it, but business owners who want generate financial statements from their business data generally buy accounting software. 

If you decide that creating an Income Statement using Excel is more than you want to tackle, relax. Your accountant will be thrilled you have gotten this far! Your work recording your business transactions will make tax time easier all around.  

If you have any questions about businesses taxes or how to prepare to file them, we can help. We can also walk you through simple accounting procedures for your home and business so preparing your taxes or quarterly tax payments are not nearly as scary. Give us a call at 215-497-8888 or send us an email.