PROJECTS
Excel budget and expense project
Skills and techniques used
DAX functions, VLOOKUP, SUMIFS, DATEDIF, TEXT, EOMONTH, IF, data validation, scroll bars, data visualizations, conditional formating, pivot table, filters and slicers, VBA.
(To view the file properly it is best to download it and open it in excel as some elements like scroll bars and data validation do not work bellow)
STEPS
1. I created an excel format for the rest of the course. The values in this step were provided, except the cash balance value is
calculated by a simple subtraction of total monthly expense from total monthly income.
2. Next I added income and expenses to my workbook that were provided by the teacher of the course. Also I updated the summary
of the budget sheet so that the total monthly income and expenses are now sums of the imported income and expenses tables.
3. In step 3 I imported a transaction history table that was provided in the course and created a spending summary sheet. In this
sheet I calculated spending summaries from the transaction history table by category. I did this by copying all the category cells
from the imported table, copy pasting into the new sheet, removing duplicates and using the SUMIFS function to make the calculations.
4. Next I created a savings calculator sheet where I calculated how much monthly savings I would need to reach a specific savings goal.
I did this by first subtracting my current savings from my savings goal and later using the DATEDIF function to calculate the number
of months from today until my target date. Then I simply divided the result of my initial subtraction by the result of my DATEDIF
function. After that I also calculated what % of my monthly income that represents.
5. In the next part of the course I added a daily budget row in out Budget and Expense Summary table, where I calculated how much we
can spend daily until the end of the month based on our current cash balance. I did this by using EOMONTH function to calculate the
number of days until the end of the month and used the result to divide cash balance by it. After that, with the help of TEXT and EOMONTH functions I created a greeting for the user by writing out “Happy (todays day name).
You have (amount of days until end of month) days left until end of month.”
6. In the next step of the project I added a conditional option to the Savings Summary sheet. I added the option for the client to
either deduct the savings needed value from the monthly budget or to not. If the value in the cell is “Yes” then the savings needed
amount will be deducted from the total monthly budget amount. Any other value in the cell will result in the amount not being deducted.
This was achieved with the help of an IF function.
7. The next step was to fix my input of “Yes” or “No” cell that I created in the previous cell. The issue is that anything else than
“Yes” will result in the amount not being deducted from the total budget, even “YES”. To do that I used DATA VALIDATION to make a list
of Yes and No, so that those are the only options possible for the cell.
Also I created 2 scroll bars, one for savings goal and the other for target date. I made the first scroll bar increment by 1 up to a
maximum of 100. I linked it to a cell that multiplies it by 1000 so that the savings goal increments by 1000 from 0 to 100 000$.
For the other scroll bar I linked it to a cell and put a maximum value of 120 and the incremental change at 1. I then used the DATE
function in the Target Date cell, where for the MONTH part of the function I added the value in the cell linked to the scroll bar.
So the Target Date cell increments from today to 120 days in the future.
8. Next I inserted a bar and column chart. One representing income vs expenses and the other spending done by category.
I also added conditional formating to the Cash Balance cell, so that it will turn red in case it goes negative.
9. In the next step I structured the data in the excel file into tables and gave them names. I then updated all the formulas to reflect
the tables and not just rows and columns. After that I imported a new categories table and used it in the Transaction History table
to create new category groups using the VLOOKUP function.
10. Later I created a pivot table using my transaction history table. On rows I put category and description and on columns I put
purchase date (Year, Month). The values are the sum of debit. You can filter the pivot table by category group and aside of that
I also added a slicer that you can use to filter the table by category. I froze panes so that you can move through the table and find
information more easily.
11. In the last step I imported some new raw data and recorded a macro that updates the table in the correct format (changes format
of the Date column and Debit column, moves the Category column into the correct position and removes the Transaction ID column).
After this is done I move the updated data to my Transaction History table which automaticaly populates the Category Group column and
updates the pivot table.
