Exercise

Create a workbook that contains worksheets, tables and charts that can help an individual manage a personal budget. Unlike the exercises in the text, you must build this project individually, from scratch, and it should include at least the following features:

• The workbook must include worksheets for:

  • Budget — List the budget categories (e.g., food, shelter and clothing), the total budget for each item, the amount spent so far for that budget item and amount left to spend. There should be at least six categories.
  • Expenses — List individual expenses (e.g., circus peanuts), the date of purchase, cost and budget category. There should be at least twelve sample expenses.

  Note that you will likely need to use the SUMIF() function to compute the amount spent so far for each budget category based on the listed expense entries (see the text for more information on how to use this function). Note also that it would be wise to use a named range to refer to the expense types and amounts on the Expense worksheet from the summary table on the Budget worksheet.

• Build your budget and expense worksheets as tables so they can be sorted and filtered at will using Excel’s table functions.

• Include totals for the budget, amount spent and amount remaining.

• The amount spent on each category in the Budget table must automatically update when new purchases are added to the Expenses table.

• Include an appropriate header for each table, including your name(s).

• The workbook must include distinct and visible warnings for cases in which:

  • An individual budget category is overspent.
  • The expenses as a whole exceed the total budget.

• The workbook must include charts that help the user visualize the following:

  • The amount spent and remaining for each budget category.
  • The relative proportions of the budget allocated to the different categories.
  • The daily expenses incurred throughout the month.

Fill your workbook with reasonable, but not personal data with sufficient quantity to demonstrate your system. Beyond these requirements, you are free to design and implement the budget system as you wish.

Grading

You must do the projects on your own, that is, without a partner.

• 20% — Data (i.e., the sample field, record and file values appropriate for the given domain)
• 20% — Formulae (i.e., the computation of the amount spent in each category, totals)
• 20% — Formatting (i.e., headers, numeric formats, budget overrun warnings)
• 20% — Tables (i.e., sorting, sum totals, formatting)
• 20% — Charts (i.e., the appropriate chart types with clear titles)