Project Name: My Statements Overview
Purpose: The purpose of this project is to get a good overview of your own finances using account (e.g. bank, credit card) statements and find out if you are in control of your finances (estimations vs actuals).
Industries: Working with account statements is typical for finance and accounting industries due to its nature however any industry will usually work with own account statements for instance for reconciliation purposes.
This project is recommended to people who meet the following conditions:
- Access to bank statement (your own)
- If you work with someone's bank statement please make sure to have permission, protect the data and not share the data with other people (Data Protection Act).
- Analytical skills e.g. Review statement file structure.
- Design skills e.g. Define a structure that will meet the project objectives.
- Planning skills e.g. Create and follow your own project plan.
- Technical skills e.g. Knowledge of tools that will allow you to meet the project objectives.
- Testing skills
You can use either SQL Server (Star Schema recommended) or Excel (Power BI) to complete this project.
To prepare for this project project we recommend to study our learning plans.
We will point you to the right learning plans when we discuss specific requirements.
This is a test project. So far we have Main and Secondary objectives created and later on we will provide learning materials and suggest typical steps to complete the project (feel free to do it your own way).
This project is to achieve the following main objectives:
- Get a clear view of spending and income from all yours accounts by completing the following tasks:
- Write down how much you think (estimation without using actual data) you spent and earned by month in the last 12 months
- Calculate average monthly spend and income by month using the last 12 months' data.
- Compare your estimations with actual data and if there are significant discrapencies investigate the cause.
- Provide an easy access to all account statement activity (IN and OUT transactions) from all accounts that can used by a PivotTable (Excel) which should allow easier implementation of future requirements.
- Hint: PivotTable can use the following sources SSAS Cube, PowerPivot or a "flat file" structure (not recommended)
- Create a process that allows to refresh one statement in less than 5 minutes.
- 5 minutes is end to end so login to account, dowload statement, process the data and refresh the graphs etc.
- Ensure the data contains all accounts
- Don't forget about Credit Cards, PayPal, Saving accounts or any other account where you keep money.
- Ensure spending and income data is accurate
- Think how you will treat transfers between accounts, deposits (they are later on returned), purchase refunds and so on.
KEBI Project secondary objectives can be treated as "nice to have" objectives and should be part of the project if you want to add them as long as they do not impact negatively the quality and timeframe of the delivery of main objectives.
The secondary objectives are:
- Create target (forecasts) spending and income by month for the next 12 months (add new month every month).
- Create a graph the compares actual spending and income by month with estimations (last 12 month) and target data (forects).
- Introduce new graph that shows 3 month moving average spending and income and decide which graph is better for your data (by month or 3 months moving average)
- Think about how you can ensure you can hit your targets and be in control of your spending.
- Develop a solution that can be used by anyone who wants to try out your solution.
- Document the solution so anyone can accurately follow the process and avoid pitfalls.
- Ensure you have a backup of the solution.
- Ensure the solution is of high quality and is stable.