An automated profit and loss statement based on user-inputted transactional data
Created With:
Excel
VBA
This project was created using only Excel and VBA for maximum compatibility. The main welcome screen serves as an introduction to the workbook with the rest of the tabs hidden. This forces the user to click on the "Welcome" button.
The "Welcome" button runs a custom VBA code to take you to the transactions page and implements a GUI tutorial on how the worksheet works and how to input transactions.
The "Settings" button shows a GUI form the user can use to change model settings and parameters.
There is a hidden Query tab that uses PowerQuery to automatically extract and transform the transactions entered by the user. This report gets summarized with a PivotTable that is used later. Data validation is used to enforce integrity.
The "Build Model" button serves as the heart of this project. When the user clicks this button it will run a VBA code that inserts 2 new sheets (a dashboard and a model), lay out the structure and labels of the model, format the model to financial modeling best practices, write formulas to query the data from the PivotTable, write formulas to calculate financials, and finally create graphs on the dashboard based on aforementioned financials. This is all done automatically in a few seconds with no intervention from the user.
The user can change the settings or transaction data and click "Build Model" again to automatically rebuild the model.
