Small Business Budget Series 2

In this second part of the budget series, I will show you how to expand the yearly budget workbook to include your business’ monthly expenses and link it to your yearly budget spreadsheet. If you remember, in the first part we created a yearly budget spreadsheet. In the end, all you will have to do is enter your monthly expenses into one spreadsheet within the same workbook and the yearly totals will be automatically updated. To make your life super easy – because I know you are busy taking care of your business or starting a new one – you can download the template for this tutorial and always feel free to refer to the tutorial for any questions or tweaks. Remember I will continue using a fictitious online company named “Web2hits” that specializes in search engine optimization, as an example.

Step 1. Open Microsoft Excel 2007, go to Open and find the Yearly Budget workbook that you created or downloaded from Small Business Budget Series 1, if not click on this link to go to the original tutorial and you can download and save it on your pc. Note: You should save this yearly budget spreadsheet as a different copy and keep it in your PC files so you can maintain the original numbers you forecasted.

Step 2. Now click on “sheet 2” on the left hand bottom corner to open “sheet 2” within the yearly budget workbook. You will use this sheet to build the monthly expenses spreadsheet that will link to sheet 1. Before, you do anything else you rename “sheet 1” and “sheet2.” Right click on “sheet 1” and click “rename” and enter “Yearly Budget” and do the same for “sheet 2.” I named “sheet 1” = Yearly Budget and “sheet 2” = Monthly Expenses.

Step 3. Next enter the title “Monthly Expenses” in cell A1, in cell A3 enter “Categories,” and in cells B3 through M3 enter the months “Jan- 2011” to “Dec-11.” Now in cells A4 through A17 enter the categories from the “Yearly Budget” worksheet. I also went ahead and made the text bold for the headings – It should look something like this:

Step 4. Now in cell N3, you need to enter “Totals.” Before we continue, I am going to add some amounts for the first two months under the categories so you can see how the totals will be linked. Don’t forget to format these cells to currency – by right clicking and selecting “Format Cells,” clicking on “Number>Category>Currency” – In your case, you will enter actual numbers since the template will have all the formulas set and the links ready to go.

Step 5. Next click on cell N4, go to the formula menu, and select AutoSum, then select cells B4 through M4. And repeat this for cells N5 through N17.

Step 6. Now we are going to link these monthly category totals to the “Yearly Budget” spreadsheet. To do this, you need to first click on cell N4, and select copy. Next go to the “Yearly Budget” spreadsheet and click on cell B4, click “Paste” and go down to “Paste Link” and select it. Now repeat this step for cells N4 through N17. Now the “Yearly Budget” sheet will reflect any changes that you enter in the “Monthly Expenses” spreadsheet.

Now we are done, great job! Feel free to download the template and start tracking those monthly expenses. go to part 3 of the series where I will show you how to use pivot tables and charts to track and review expenses for your small business. Good Luck!

Download the Monthly Budget Spreadsheet Now