Financial budget
- May. 8, 2021
 | Example files with this article: |
Introduction
In this article I will demonstrate a worksheet containing formulas to create a personal budget in Excel. Our aim is twofold: manual input should be reduced to the bare minimum. Formulas should run automatically.
Pictures
Here's a first picture of the worksheet:
Please welcome John & Mary ! We are going to have a closer look at their budget, and look how they approach the budget input and calculations. Column D (Amount) and column E (Frequency) are very important for the budget data entry. We can observe that John receives a salary equal to 2000, whole Mary receives an unemployment benefit of 650. John also receives a holiday pay, in the month of May, while Mary has a second job with the local bakery shop. It gives her 70 every Saturday. You are right: this worksheet and my calculations allow the user to enter an amount at other intervals than the "month", which is the usual time interval for data entry. I find that the budget needs to be easier and more tailored to the needs of the users. In any case, formulas will calculate revenues based on the number of Saturday's in every month.
This leaves us with many possibilities. Rather than calculating and entering the monthly amounts ourselves, you can have Excel do the heavy work based on the frequency.
You can stretch this approach a lot, as (e.g.) Mary happens to smoke and buys a pack of cigarettes (6 ?) every day. This is row 16 in the worksheet. Other expenses could include utilities or the house or the car. It seems that April 2021 will be difficult for John and Mary. This is caused by the travel budget for April.
Row 27 shows the totals by month, not rounded. Row 4 shows the rounded monthly totals, whereby the rounding can be entered in cell B3 (25 ? here). Cell F3 is the net result on a yearly basis. You can choose to use more decimals should you prefer to.
Below is a table consisting of all possible frequencies in the worksheet:
| Frequency |
| January |
| February |
| March |
| April |
| May |
| June |
| July |
| August |
| September |
| October |
| November |
| December |
| 1x a month |
| 2x a month |
| 3x a month |
| 4x a month |
| 5x a month |
| 1x every 4 weeks |
| 1x every 3 weeks |
| 1x every 2 weeks |
| 1x a week |
| 2x a week |
| 3x a week |
| 4x a week |
| 1x every workday |
| 1x every weekday |
| 1x every quarter in month 1 (Jan/Apr/Jul/Oct) |
| 1x every quarter in month 2 (Feb/May/Aug/Nov) |
| 1x every quarter in month 3 (Mar/Jun/Sep/Dec) |
| 1x every trimester in month 1 (Jan/May/Sep) |
| 1x every trimester in month 2 (Feb/Jun/Oct) |
| 1x every trimester in month 3 (Mar/Jul/Nov) |
| 1x every trimester in month 4 (Apr/Aug/Dec) |
| 1x every semester in month 1 (Jan/Jul) |
| 1x every semester in month 2 (Feb/Aug) |
| 1x every semester in month 3 (Maa/Sep) |
| 1x every semester in month 4 (Apr/Okt) |
| 1x every semester in month 5 (Mei/Nov) |
| 1x every semester in month 6 (Jun/Dec) |
| Soccer season |
| Summer |
| Every Monday |
| Every Tuesday |
| Every Wednesday |
| Every Thursday |
| Every Friday |
| Every Saturday |
| Every Sunday |
| Every weekend |
| Odd months |
| Even months |
| 1x a year, spread over the year |
| 2x a year, spread over the year |
| 3x a year, spread over the year |
| 4x a year, spread over the year |
| 5x a year, spread over the year |
| 6x a year, spread over the year |
| 7x a year, spread over the year |
| 8x a year, spread over the year |
| 9x a year, spread over the year |
| 10x a year, spread over the year |
| 11x a year, spread over the year |
| 12x a year, spread over the year |
| 13x a year, spread over the year |
| 14x a year, spread over the year |
| 15x a year, spread over the year |
| 16x a year, spread over the year |
| 17x a year, spread over the year |
| 18x a year, spread over the year |
| 19x a year, spread over the year |
| 20x a year, spread over the year |
| 21x a year, spread over the year |
| 22x a year, spread over the year |
| 23x a year, spread over the year |
| 24x a year, spread over the year |
| Spread according to workdays |
| Spread according to weekdays |
Budget by person
Besides the total budget for John & Mary, we are also interested in the separate budget for them. Basically, how much does each one contribute to the total ? That's easy for the own income and expenses, we can aggregate them. Each person has the own income and expenses. But the common revenues and expenses must be attributed to each one of them. I do it like this: both person take a share of the common amounts:
We can learn from the table that each one has the own amounts (100% and 0%), and also that John takes 75% of the common amounts while Mary bears the remaining 25%. This allocation keys will allow us to calculate totals by person:
In other words, the formulas in the green table will look at 2 concepts of "Person":
- The revenue or cost is for whom ? See column PERSON
- We want to calculate the budget for whom ? See cell B2
Closing off
Tools like this enable us to easily calculate the financial budget for yourself (including partner, others, ...). It will be much easier to enter the data and come to a financial equilibrium. Please download the workbook near the top of the page and let me know your finds or remarks !
