Personal budget analysis with spreadsheets
Secondary level - 15-18 years old - Duration: 3 sessions of 45 min.
Introductory question
What if you could «hack» your financial future using just one tool you already know? How can a simple budget be the roadmap to achieving your goals, from buying a new video game to planning your first trip?
Explanatory summary
A budget is a fundamental tool for financial health that allows you to take control of your money. In this activity, you will learn how to use spreadsheets (such as Google Sheets or Excel) to create a personal budget from scratch. You will model your income and expenses, organize transactions by category to understand where your money goes, and simulate how different decisions can help you reach your savings goals faster.
Skills to be developed
- Cognitive: Data analysis, critical thinking, problem solving and strategic planning.
- Socioemotional: Self-management, responsible decision making and future planning.
- Internships: Digital literacy (spreadsheets), financial literacy and information organization.
Standards linked: ISTE (Empowered Learner), Common Core (Mathematical Practice).
Learning objectives
- Create a functional spreadsheet to record personal income and expenses.
- Categorize transactions to identify and analyze consumption patterns.
- Apply basic formulas (ADD, SUBTRACT) to calculate balances and totals.
- Simulate the impact of financial decisions on a short-term savings goal.
Materials
| Economic (Basic) | Standard (Recycled/Digital) | Pro (Advanced) |
|---|---|---|
| Paper, pencil and basic calculator to make a manual draft of the budget. | Computer, tablet or smartphone with access to free spreadsheet software (Google Sheets, LibreOffice Calc, Numbers). | Subscription to advanced software (Microsoft Excel 365) with premium templates and data analysis tools. |
Prioritize the use of free digital tools and make sure to protect your personal information.
Step-by-step procedure
- Phase 1: Fundamentals and Structure - Discussion on sources of income and types of expenses. Design of the spreadsheet structure with columns: Date, Description, Category, Income (€), Expense (€), Balance (€). (45 min)
- Phase 2: Registration and Formulas - Entering example data (real or fictitious) for a month. Application of the `SUM` formula to totalize income/expenses and creation of a cumulative balance formula. (45 min)
- Phase 3: Analysis and Simulation - Creation of a pie chart to visualize the percentage of expenses by category. Definition of a savings goal and scenario simulation («What happens if I reduce my ‘leisure’ spending by 20%?»). Discussion on ethics and privacy of financial data. (45 min)
Formative evaluation
- How can an «unforeseen expenses» category affect your monthly savings goal?
- Explain the difference between a fixed and a variable income, and how you would manage it on your sheet.
- What privacy risks are there when handling your financial information on online platforms?
Evidence: Completed spreadsheet, answers to reflection questions, participation in simulation discussion.
Rubric (4 levels)
| Criteria | Initial | Basic | Advanced | Expert |
|---|---|---|---|---|
| Structure | The sheet has columns, but they are confusing or incomplete. | The sheet includes the basic columns (date, description, income, expense). | The sheet is well organized, with clear categories and consistent formatting. | The sheet includes advanced elements such as conditional formatting or data validation. |
| Use of Formulas | Calculates balances manually. | Use the `SUM` formula correctly for totals. | Correctly applies `SUM` and a functional cumulative balance formula. | Use additional formulas (e.g. `AVERAGE`, `IF`) for further analysis. |
| Analysis | Records data without interpreting them. | You can identify the largest expense for the month. | Interpret data and graphs to explain their consumption patterns. | Proposes concrete adjustments to your budget based on data analysis. |
Differentiation
- Supports: Provide a template with the structure and formulas already created, so that they only have to enter the data.
- Extensions: Research and add a savings percentage calculation to the total income. Project the budget to 6 months.
- Challenges: Create a dashboard with dynamic graphics that update automatically.
Connections to daily life
- Finance: Foundation for economic independence, planning of important purchases (cell phone, car), saving for studies, avoiding debts.
- Values: It encourages responsibility, discipline, honesty (by recording expenses) and planning for the future.
Safety and sustainability
Security: It is crucial to protect your financial information. Use strong passwords for your accounts and files. Do not share your personal or financial data on public networks. Be wary of emails or messages asking for confidential information (phishing).
Sustainability: A budget can be a tool for conscious consumption. By analyzing your spending, you can make more sustainable choices by supporting local businesses or choosing products with less environmental impact.
Guide created by STEM Tutorials Generator (Gutenberg). CC BY-NC-SA 4.0 license.