In this tutorial, you will create an Excel budget workbook to produce a summary of a Virtual Assistant’s monthly operating expenses for 2016.
When working out your monthly operating expenses, be aware that they can include both fixed and variable costs. Fixed costs usually remain the same from month to month and include items such as rent and membership dues. Variable costs vary from month to month and include items such as postage, ink and travel costs.
Once you set up your monthly operating expenses spreadsheet, you can use it to log your actual expenses each month which will make filing your yearly taxes much easier. If you are just starting out with your virtual assistant business or other business, your monthly budget will also help you plan your expenses for your first year in business.
Rename a Worksheet and Use AutoFill
Excel is an excellent tool for creating and maintaining detailed budgets. An Excel Workbook is a file that contains one or more worksheets. When you first start Excel, it displays a blank workbook with a single worksheet named Sheet1 that is displayed as a tab at the bottom of the screen.
- Double-click the Sheet1 tab and rename it Monthly Operating Expenses
- Click in Cell A1 and add the title VA Monthly Operating Expenses and then Click in Cell A2 and add the Subtitle 2016 Estimated Budget Summary as shown below: Note: We will format the cells later.
- Click in Cell A3 and type Item and tap the tab key on your keyboard once then in Cell B3 type Jan and click the Enter button.
- Position the mouse pointer on the bottom right corner of the active cell and you should see the fill handle appear as a black cross.
- Drag across over the next eleven cells (cell B3 to M3). You should see the outlined row and screen tip. Release the mouse to fill the adjacent cells.
- You should see the AutoFill Options button appear after the cells are filled.
- Click the AutoFill Options button and Choose Fill Without Formatting
- Enter the remaining text and data entries as shown below: Note: If you wish, you may modify this to add in only the items and data that pertain to your own monthly operating expenses.
Use Autosum to Add in Total Rows and Columns
- Click in Row N3 and type Totals
- Click in Cell N4 and then click the Home Tab > Editing Group > AutoSum button
- You will see the proposed formula in the formula bar which should show a range of B4:M4. The proposed formula should be =SUM(B4:M4).
- You will see that AutoSum will add a flashing marquee around the range. Excel is asking you if this is the range you want to SUM and gives you the option to accept and complete the Entry or make adjustments or cancel. If your screen looks like the picture below, click Enter on the Formula Bar to complete the entry. Your total should be 6000.
Finish the spreadsheet by adding a Total Row for each month. Type Totals in Row A21 then click in Cell B21 and follow the AutoSum procedure explained above to total the Jan column. Your Formula should be =SUM(B4:B20)
Use the Autofill function expained above to copy the Total Formulas for each month and for the Total Column. Your finished worksheet should look like the following with a Grand Total of 14229.71 if you are using the exact figures in this tutorial.
Up Next – Formatting and Styling Your Spreadsheet
- Merge and Center the Titles
- Apply Cell Styles
- Format the number cells in Accounting Style with 2 decimal points
- Add styling to the spreadsheet
- Use Themes