Archive

Archive for the ‘Budget’ Category

Daily Budget

February 15, 2010 Leave a comment

When creating a budget in excel whether it is for your personal finances, your business, or both the basic layout and structure is the same. Your columns describe your time line and your rows describe your categories or sources of income and expenses. If you are using something to track your income and expenses like QuickBooks or Excel then you will want to set up your categories in a manner consistent with what you have in that program.

The key to setting up a budget that works is really not just setting up a budget, but setting up something that is fun and easy to use as well as something that makes you check back with it regularly so that your attention is repeatedly placed on it. Otherwise you can overspend and then go back and say oops! Oh well, better luck next month! That won’t really help your situation though.

MS Excel makes it very easy to set up and customize your budget in exactly the manner that best fits your situation. It’s the flexibility of excel that makes it so great, and you can easily expand upon things to increase the level of detail.

Your budget will start to get real exciting when you begin to see the results. It’s sort of like working out at the gym. At first it is a chore, until people start noticing. Then it gets exciting. Well the budget gets exciting when you tie it up at the end of the month and see that you can transfer $1,000 to your savings account where you were never able to do that before.

In addition to learning how to set up a budget, this video tutorial teaches you a number of skills in excel – some simple, and some pretty advanced:

Simple formulas – addition, subtraction, and division

Weekday formula

Vlookup

Getting the date to light up when it matches today’s date

Conditional Formatting with a conditional formula embedded.

How to calculate and analyze your variances

Formatting to make it easy to read.

Please enjoy the video tutorial – MS Excel – Setting up a daily budget by clicking on the image:


How to track your business in excel part 3

November 11, 2009 Leave a comment

For a better quality version of the video web cast:

http://nerdenterprises.na6.acrobat.com/p84834671/

Visit our Excel blog for more: http://www.msexcelguru.com

Here we really complete the picture for you by showing you how to enter and then report on your financial transactions.

We show you how to set up the hyperlinks so that if you expand upon this concept you will be able to incorporate any new items in the ‘Home’ or ‘Menu’ Tab.

We review the setup of a list and data validation to update the way the source account works, because that account really needs to represent sources of payments and deposits. This also makes it possible to rely on the “Destination” accounts for tracking all income, expenses, asset purchases and payments on liabilities.

Now that the template is set up we can show you how to enter your transaction data:

This is what the template looks like with everything entered. If there was a deposit to be entered the Source account would be the bank account where the money was deposited and the destination account would be the appropriate income account. This is not set up to show invoices to and payments on accounts receivable. This can be done but would have to be done on a private consult because it would be very involved.

Once we have data in we can begin to report on it. We show you in the last 3 minutes of the web cast how to create a pivot table on this data reporting on expense by category and then expanding upon that by showing you how to further report the expenses daily based on the date of the transaction.

Figure 1 – Pivot Table – Daily Expenses click for larger image

We will post a bonus web cast going into more detail on these pivot tables and how you can use them to report quickly on the information in the sales and transactions.

Tracking Your Credit Card Information

September 2, 2009 Leave a comment

On 9/17/08 we recorded a web cast that showed you how to do some simple yet powerful things in terms of tracking information.

MS Excel is a great tool for keeping track of any information that can be listed. Credit Cards are a great example. In this web cast we show you how to set up a dashboard showing you each of your credit cards and all of the relevant and important information so that when you need to you can manage your credit cards from one convenient place. We also demonstrate some advanced formatting for dates and phone #’s. Also when it comes to credit cards the utilization is an important thing to understand and be aware of and in this example we show you and explain to you how this works and how it is calculated.

Formatting your forms in MS Excel is very important to make your information presentable and easy to read. Also by using Data Filters you can easily isolate important information. We show you how to do these things in this web cast.

Please enjoy the video web cast here: http://nerdenterprises.acrobat.com/p25375480/

For a much more advanced version of what we create during this web cast and a related tutorial on how to use it, click here: http://nerdenterprises.com/product_info.php?products_id=74

The 10 Minute Household Budget

August 24, 2009 Leave a comment

You want to keep your household budget simple and make sure it includes everything. Ms Excel of course is the tool we use for any kind of budget. You want to lay your budget out in a month by month format – even if you are already well into the year you want to start from January. This makes it all uniform in terms of having a series of 12 columns making up one year. Otherwise you have a partial year in the first year and it won’t match up. This makes it easy to copy and paste, then do a search and replace to expand the months and add another year as you will see demonstrated in this video tutorial.

The first step is to lay out the income – set up a unique line for each source of income (ie Payroll, 1099, other..).

Then you will lay out the expenses. I’ve seen people lay out some pretty complex and hard to follow budgets in terms of the expenses. You will see that in this budget we keep it very simple. If you want to look at credit card debt and look at the interest rates and monthly payments, that should be done in a separate schedule. Then you can tie the payment total into the main schedule. This keeps it simple. You can see a tutorial on how to use our Credit Cards Template here: http://www.nerdenterprises.com/Web_Casts/Debt_Management_Tutorial/Debt_Management_Tutorial.htm This can easily be brought in to a template like the budget we are creating in this budget tutorial.

The trick is to keep the budget simple. What happens all too often is that we complicate things and then we don’t want to spend time on this sort of thing because it is complicated and painful!

Watch the 10 minute household budget video tutorial here: http://nerdenterprises.acrobat.com/p87449821/

Payroll – using excel to plan your new hires

August 19, 2009 Leave a comment

We use excel for the planning and analytics. Many people ask me how companies get into the situation where they cannot afford to stay in business because they’ve over extended themselves. My first rule in good financial management is paying attention to your finances. This means more than just going out and hiring someone at the “market” rate. Once you have that information you have to lay it out in terms of the true cost and make sure you can afford that rate. Then you either know you can, or you have to negotiate with someone – maybe you can offer more than just a salary, but a great opportunity for the person to grow & learn, or maybe you can lower the salary to something you can afford as a base and make the rest performance based.

We also show you how you can calculate and determine your commission structure for sales people. The power of excel is in being able to plan things before you commit and start actually spending money so that you can be sure of what you can afford. This is the critical mistake so many businesses make because they don’t do this.

Please enjoy the web cast by clicking here: http://nerdenterprises.acrobat.com/p90748849/