As a spreadsheet tool with instantaneous calculation features, programmable returns, and graphing capabilities Excel is a program that is more or less made for budget creation and tracking. Given a wide list of selections and options, users can choose between the simple and complex when tracking their monthly expenditures versus money on hand. Just as the options and features are disperse, so too are the choices that come when constructing the budget.
The easiest and best budget plan for an Excel novice is a step by step analysis of what is spent and when, on an outlined over view of the month in question. This setup would start with the amount of money on hand and spending limit, regardless of money coming into the equation. Because months are different and expenditures vary, especially in geographic locations with changing seasons, then it would be best to track the budget over a year to determine any adjustments that may work into the budget at a later date.
All budget data sheets can yield graphs of the user’s choosing by selecting data columns and rows yet to be discussed. To track each month and the cost/gain ratio, there are three viable options to consider: Separate Monthly, Continuous Monthly, and Staggered Monthly, over the course of one year.
Separate Monthly:
In a separated approach all months are kept on individual data sheets and labeled with the name of the month they correspond with. On the left hand side of the sheet several rows down, the first column (A) would relate to the corresponding date. If the label of that month is written in, days corresponding to the number in the month can be listed in numerical whole numbers, or better, listed as MM/DD/YYYY, as the program will automatically fill in the proper days after dragging the edge down to a desired location.
In the next column (B) could be the money coming in, and only filled when a profit is made on the corresponding day. The third column (C) would contain the money spent, and filled out on the same day. Finally the fourth column (D) would contain the budget limit as the initial label, and then show the amount left at the end of each day of the month. The equation for this row space may look as the following: =D3+B4-C4
Using the drag feature, the user would fill each row for days in the month.
Continuous Monthly:
In the continuous form, all months are on one sheet for the entire year. It would follow the same format as above using the MM/DD/YYYY pattern for the length of the entire year (easily filled in by dragging), but this time the first value in the D label would be the estimated budget for the entire year. With a projected goal listed in the E column, the user could refer to that number to ensure that they are within or under their intended budget.
Staggered Monthly:
With a staggered approach the months are kept on the same data sheet as the continuous sheet, but are separated by reserved spaces for additional budget calculations. In this way, it is possible to add other useful features that would inform the user of whether they are meeting their budget goal after each monthly period using an IF worksheet function.
An IF worksheet function is a simple program that could compare the final day’s value in the money remaining column with the intended budget condition, to inform the user of whether they are over or under without human calculation. A sample condition may look like the following.
Given that the budget is $2,000 a month, and the user starts with an initial quantity of $40,000 the IF equation could look like the following for January.
IF((D1-D32)<=2000, “Within Budget”, “Over Budget”)
This states that if D1 (which is the 40,000) minus the balance at the end of the month (which should be 38,000 or greater) is less than or equal to 2,000, then it will list the words ‘Within Budget’. If the balance exceeds the 38,000 mark, then it will show the words ‘Over Budget’.
For the next month, the block starts again but this time uses =D32 as the starting value for the D column. In this way the user only has to add gains and losses throughout the course of the year after setting it up. Other options and features can be accessed through the help topic and index within the Excel program.