Many small businesses rely on Microsoft Excel spreadsheets to keep track of important data. Despite the large selection of accounting software programs for businesses, Microsoft Excel is the tried-and-true spreadsheet option for most, especially for small businesses. In this guide we’re providing you with some helpful information on how to use Excel for financial accounting, we’re also showing you some handy Excel shortcuts you can use; saving you lots of time and effort. This article will show you how to; create a company budget in Excel and how to best Track your cash-flow. We’ve also provided some handy templates which you may find useful including a small-business planning calendar and small-business expense sheet.
Excel Shortcuts & Handy Tips
Use these shortcuts instead of using your mouse to click. Highlight the cells or word that you want to edit and click the Ctrl button and then the letter.
CTRL & B = Bold
CTRL & C = Copy
CTRL & X = Cut
CTRL & V = Paste
CTRL & F = Find
Excel basics and formulas
Like an interactive sheet of graph paper, Excel spreadsheets are divided into rows and columns that intersect to create boxes known as cells. Columns are labeled alphabetically (A, B, C…), and rows are labeled numerically (1, 2, 3…). You can enter alphanumeric data into any cell and use formulas and functions to perform calculations on that data. Then you can create charts and graphs to display the results.
To add up two numbers you can use the AutoSum button or you can type the equals sign:
To use AutoSum click on the AutoSum button on the ribbon and Excel suggests the fields that contain the numbers you want to add. If these are not correct you can just edit them in the box.
To use the equals sign, just type = in the box and select the boxes that you want to add and press enter.
Note that Excel offers a full range of custom programmable formulas that can be inserted to organise and compute data. These instructions can be used to process simple functions such as adding, subtracting, and multiplying values or calculate dates, dollar amounts, and averages. Use more advanced formulas to generate auto-formatted findings, change text from uppercase to lowercase, or combine data from multiple columns into a cohesive whole.
Chart your data
1. Select the data that you want to chart.
2. On the Insert tab, in the Charts group, click the chart type that you want to use, and then click a chart subtype.
3. Use the Chart Tools to add chart elements such as titles and data labels, and to change the design, layout, or format of your chart.
For more information about how to create a chart, see Chart your data.
1. Select the cells that you want to format.
2. On the Home tab, in the Number group, click the Dialog Box Launcher next to Number (or just press CTRL+1).
3. In the Category list, click the format that you want to use, and then adjust settings, if necessary. For example, if you’re using the Currency format, you can select a different currency symbol, show more or fewer decimal places, or change the way negative numbers are displayed.
For more information about how to format numbers and available number formats, see Format numbers in a worksheet or Available number formats.
Creating Company Budget in Excel:
- Open Microsoft Excel, which automatically starts a new, blank spreadsheet on the workspace. Click into the first row, row 1, and type the name of the spreadsheet, such as "Mc Carthy’s Busines Solutions Inc. Monthly Budget Total."
- Click into the third row, second cell, B3. Type the first area of expenses for the business, such as "Salary." Press the "Tab" key on the keyboard to move into the next cell to the right and type another expense, such as "Facility." Continue to tab over, adding more cell headers such as "Insurance," "Office rent," "Utilities" and "Travel."
- Click the cursor into the fourth cell of the first column, A4. Type a department name, such as "Human Resources" or "Programmers" or names of actual employees who have purchasing/spending power within the organisation. Press the "Enter" key to drop into the cell below, A5, and add another name. Continue adding names and pressing "Enter" until all factions involved with company spending are listed on the sheet.
- Highlight all of the names in the first column. Right-click and select "Format Cells." Click the "Fill" tab and click a lightly colored square to shade the names and make them stand out. Click the "Font" tab, increase the number in the "Size" column and select "Bold" for the "Font style" column. Click the "OK" button and the changes are applied.
- Highlight the expense headers in row 3 and perform the same formatting. Highlight the title of the worksheet and perform the same formatting, but increase the size of the words slightly larger than the headers so the title stands out.
- Highlight the cells of one column under a column header along with the blank cell at the bottom of the column, such as all of the cells under "Salary." Click the "Home" tab at the top of the screen. Click the "AutoSum" button, which has a small Greek "sigma" symbol on the right side of the ribbon/toolbar at the top of the screen. The cell fills with "=SUM()." This means the worksheet will auto-calculate and total the numbers when you input them into the spreadsheet, giving you a monthly total for the column. Repeat the highlighting and "AutoSum" process for each column.
- Highlight each of the new "AutoSum" cells, which are in a row, plus one blank cell. Click the "AutoSum" button one more time. This will create a grand total of all of the columns, which will show the exact amount spent that month. The total appears in the blank cell. This number may be compared to the monthly number typed in the "Total" of the worksheet header (see the final step).
- Click the "File" tab, select "Save As," type a title for the spreadsheet and save it to the computer.
- Open the spreadsheet each month and type the month's budget maximum amount to be spent next to the word "Total" in the spreadsheet header. For example: "Grandpa's Carvings Inc. Monthly Budget Total November €2,400." Input the fiscal data, then save the spreadsheet with a new name or version number, such as "BudgetMay2013." The original worksheet remains the template to use each time.
Tracking Company Cash Flow
Tracking your cash flow is a smart way to help you ensure small business success. You can create a simple cash flow budget in Excel to project the amount of cash you'll have in the bank throughout the year. Then, if you see a few lean months coming down the road, you have time to prepare for them.
- Create a cash flow budget in Excel by listing your sources of income and categories of expenditure. You can find much of this information in your small business accounting software or end-of-year tax reports.
- Open a new worksheet and enter these income sources and expense items into two separate groupings in column A. Leave two blank rows between income and expense items. Across the top of the worksheet, enter the months January to December.
- Opposite each income and expense item for each month of the year, enter the amount you anticipate you will receive or pay-out in that month. When the amount is the same for all months, enter one month's data and drag on the fill handle in the bottom right corner of the cell to enter the value into the remaining months of the year.
- Enter all the estimates and then total the monthly income and expense amounts. Below the total expenses for the first month you can calculate the month-end bank balance. The balance is your starting bank balance, plus the total income and minus expenses for that month.
- In the next cell to the right build another formula -- in this case the starting bank balance will be the value in the cell to the left. Once you have built this second formula you can drag across the remainder of the cells in this row to fill them.
- Now you can see your anticipated bank balances and you can see – and plan for -- those months where cash flow might be low.
Microsoft has lots of free handy templates for you to use.
To find a template in Excel 2010, do the following:
1. On the File tab, click New.
2. Under Available Templates, do one of the following:
- To reuse a template that you’ve recently used, click Recent Templates, click the template that you want, and then click Create.
- To use your own template that you already have installed, click My Templates, select the template that you want, and then click OK.
- To find a template on Office.com, under Office.com Templates, click a template category, select the template that you want, and then click Download to download the template from Office.com to your computer.
NOTE: You can also search for templates on Office.com from within Excel. In the Search Office.com for templates box, type one or more search terms, and then click the arrow button to search.
Some of our recommended templates are:
Business expense budget12 Month Cash-flow StatementSmall Business CalendarSmall Business Expense Sheet