Monthly Balance Spreadsheet

DESCRIPTION

Download or edit the template Monthly Balance Spreadsheet , which is compliant and can be used by different alternatives such as LibreOffice, OpenOffice, Microsoft Word, Excel, Powerpoint or Office 365. OffiDocs is one of the options to edit this template online.

This template is intended to be used to track the expenses in a house during a month.

The expenses are divided in 8 categories and the month is divided in 4 weeks.

The name of the categories is configurable, except for the eighth one, which is named “Fixed” and may be further divided in up to 8 subcategories.

Also configurable is the name of the month and some parameters of the graph such as the colors of the ranges.

Monthly Balance Spreadsheet This template is intended to be used to track the expenses in a house during a month.

The expenses are divided in 8 categories and the month is divided in 4 weeks.

The name of the categories is configurable, except for the eighth one, which is named “Fixed” and may be further divided in up to 8 subcategories.

Also configurable is the name of the month and some parameters of the graph such as the colors of the ranges.

The template has two sheets which will be explained bellow.

The balance sheet This sheet, depicted in Fig.

1, is were each expense must be inserted, it is divided in the four weeks of the month with three weeks of seven days and the fourth week being stretched until the end of the month.

Below each week there are several lines that may be filled with the expenses.

The fields are labeled as: “Note”, where I suggest you put the name of the establishment or some note that helps you identify the expense; “Category”, where you must provide one of the eight categories mentioned before; “Date”, where I suggest you to put the date of the expense respecting the chronological order and the 4-weeks division; “Money”, where you must insert a positive amount of money you’ve expended.

For each expense, the fields “Category” and “Money” are required and are used to compute the totals and the monthly balance, the others are simply suggested to keep the data organized.

Below each week’s expense list, the corresponding totals are presented per category.

They represent the amount reserved for that week (+), the amount expended (-), and the balance (=).

In a similar manner, at the right upper corner there is the “Monthly Balance” where the amount of money reserved, expended, and the balance is presented, this time considering the whole month, again per category.

Finally, at the lower right corner there is a graph to provide a visual aid showing the balance situation.

The graph uses different colors to represent different ranges, along with the total expended and the corresponding percentage of the money reserved for the month in the center of the graph.

The settings The other sheet, depicted in Fig.

2, provides a way to tune the balance sheet according with the user preferences and needs.

The colors of the ranges of the graph, for example, may be selected allowing a color blind person to use it.

This and the other settings are listed bellow: The name of the seven categories (the “Fixed” category cannot be changed) plus the name of the subcategories inside the “Fixed” category.

These all may be renamed in the left table under the “Category” column.

The amount of money reserved for each one of those (sub)categories per each week, and, consequently, the amount of money reserved for the entire month.

These may be entered in the same table mentioned above.

The total for each week and the “Monthly total” is presented bellow the table.

(Beta) The four ranges of the graph in the balance sheet.

Each value must be a fraction of the reserved “Monthly total” and must be entered as a percentage in the first column of the table in the right side.

Keep in mind that the graph has a fixed size and will be linearly spaced; therefore, there is a trade-off between the size of each range.

That said, the default values will likely meet your expectations.

The colors of the ranges of the graph and the respective backgrounds.

These may be entered as RGB (red, green, blue) values in the same table mentioned in the previous item.

The value of each component must be between 0 (min.

) and 255 (max.

) and the resulting color should appear under the column “Preview”.

For more information on the RGB color model google it or visit: https://en.

wikipedia.

org/wiki/RGB_color_model.

Not-so-frequently-asked questions These are some questions that came to my mind, since I probably won’t have time to maintain this project as much as I would want.

Q: Why only 8 categories? A: To save space and show the entire sheet at once; my monitor is 22” with a resolution of 1080p and the balance sheet fits well in it.

Adding any other category I would need to be constantly scrolling or I would need to reduce the space reserved to the expenses under each week, which is already short.

Q: Speaking of which, how can I make the sheet fit in my monitor? A: Zoom in or out by holding CTRL while scrolling the mouse wheel or using your preferred method.

Q: What is the “Fixed” category for? A: For a cost that does not change from one month to other.

I put in there my internet bill as a subcategory, for example, among other things.

Q: Why the fourth week is longer than the other three? A: Because I think it is more convenient (and easier) to divide the first weeks in seven days and it is more common to stretch the money in the end of the month anyway.

Q: Why only one month? A: I have made another spreadsheet with the twelve months plus line and bar graphs to track a year worth of expenses.

However, the LibreOffice became a little unstable with the amount of cross-references and the graphs weren’t updating; sometimes the figure on top of the graph even disappeared in some of the sheets.

Q: Speaking of the graph, how did you made it? A: It is a actually a doughnut graph with transparent background and a semi-transparent PNG image on top of it, all grouped together.

The PNG image was made in another awesome open source software called Inkscape (https://inkscape.

org/), by the way.

The color ranges are obtained through more involved calculations; if you are curious they are in the cells behind the graph itself.

Q: And regarding the colors, how did you make them appear aside their RGB values? A: That is another trick pulled out with a horizontal bar graph without axes or legend and with a transparent background; the size of all the bars is the same and the actual colors are calculated in the cells behind the bar graph.

Q: I tried changing a cell and received an error, how can I customize/translate this cell? A: The spreadsheet is password-protected to prevent messing with unintended cells and making the spreadsheet stop working, but the password is empty – if you really need to change something.

Just keep in mind that some cells contain the result of calculations.

Also, some strings are used in the formulae and need to be replaced there too, for example: the “Category” and the “Money” cells below each week are used to fill the table “Total” under each week using the formulae in the hidden rows 33 and 39.

Also, the name of the “Settings sheet is referenced in several cells; therefore, renaming/deleting that sheet will irreversibly break the “Balance” sheet.

Accounting, Budget, Checkbook, Notes,

SCREENSHOTS

Free download template Monthly Balance Spreadsheet integrated with the OffiDocs web apps