Income and expense calculations are right in the wheelhouse for Microsoft Excel. This spreadsheet program offers a host of bookkeeping tools, and it can be used to generate income statements, profit and loss statements, and cash flow statements. Actually, all three of these reports are essentially income versus expense calculations that simplify the task of managing your business finances and planning for the future. Excel is flexible enough that you can format the information in any way that meets your needs.

Every business needs to track income and expense on one level or another — and often on multiple levels. The simplest type of tracking is all the way down at the bottom line. For example, you could sum your salaries for January and place that number in cell B2. Total purchases for the month would go into cell B3, and utility payments would be entered into cell B4. One-twelfth of the annual expenses goes into cell B5. Cell B6 could hold the total of cells B2 through B5, representing your total expenses for January. Then cell B8 can hold your income for the month. Cell B10 contains the result of B8 minus B6 and you've got your income statement for the month. If you repeat that for every month of the year, you can graph your income, expenses and net profit (or loss) month by month.

If all is going well with your business, you might not need any more detail than what you developed in the previous step. On the other hand, you may want to make sure that your business is operating efficiently and you're maximizing your profits. To do that, you'll need to make your spreadsheet a little more granular and include a finer level of detail. For example, you could create another table in your Excel spreadsheet to track salaries. That table would include each employee's hourly rate and number of hours worked. You could also use this table to compare different employees and examine variations in their work from month to month. You would total all the salaries in that table, and then transfer that number to the appropriate cell in the summary table.

You can apply that same level of detail to your expenses. You could make a utility payment table, for example. With separate entries for your phone bill, electricity, water, heat and Internet services, you'll have direct access to these separate costs. You could use this information to rapidly identify changes that might indicate a problem. The sooner you identify problems, the more quickly you can address them. Again, you would sum the monthly utilities and transfer the sum to the summary table developed in Step 1.

Adding granularity to your income calculations is one of the most effective business planning tools. Again, create a new table on your Excel spreadsheet, this one just for income. Here you'll track not just the dollars but also the units you are moving to generate those dollars. The units could be meals or pounds of food at a restaurant, number of blankets and sheets at a linen store or number of rooms filled at an inn. That level of detail allows you to see where you're making your money so you'll know either what you should emphasize or where you need to improve your efficiency. Again, transfer the total to the income spot on your monthly summary sheet.

Once you have historical data on your income and expenses, along with data on the number of units you're selling, you can begin to make predictions for the future and set realistic target goals. You can calculate sales per employee, costs per unit sold, net income per hour you are open — there are many possibilities, all of which open up if you've developed your income vs. expense statements in sufficient detail. Then you can calculate backwards: set profit goals and determine how many units you need to sell, how many hours you need to stay open, how many employees you need to hire.

About the Author


Photo Credits

  • Comstock/Comstock/Getty Images