Assignment

Create a new workbook that will allow you to budget the monthly expenses (creation date will be checked).

Label the worksheet tabs as:

Demographics

Jan

Feb

March

Summary

On the demographic page list the demographic information (marital status, income(s), housing choice and costs, car choice and cost, along with your name and your spouse's name

Format this information so that it is clear and aesthetically pleasing.

Insert an appropriate image (picture or clip art) on the Demographics page.

Put a dynamic date on the page.

In the header of the page put a static date, your name, your class section, and your instructor's name.

In the footer of the page put the page number.

Group the Jan, Feb, and March worksheets (a real one would have 12 months, but 3 is sufficient for this assignment!) and add the following features:

The words Month and January (you will change February and March to their appropriate names after the sheets are ungrouped).

Several blank rows and then column labels for the following items:

Item

Budget

Expense

Income

Difference

Under the ITEM heading, list the following 25 items for your budget:

Income = 42,000 a year

Significant other's income (omit if none exists) = 35,000 a year

Income tax (calculation is 25% of the total income if single, 20% of the total income if married)

Housing (as per choice on demographic sheet) = 880

Escrow

Gas and electric utilities = 100

Water/Sewer = 40

Telephone (land)

Telephone (cell) = 120

Car payment (as per choice on demographic sheet) = 224

Second car payment (omit if only one car) = 480

Car insurance = 80

Second car insurance (omit if only one car) = 80

Gas and car maintenance = 81

Second car gas and car maintenance (omit if only one car) = 156

Food costs ($250 per adult, $50 for infant, $100 per child)

Alcohol (beer, wine) or cigarettes (if used add $40/month for each) = 50

Entertainment (parties, eating out, movies, etc.) = 100

Cable, internet access = 90

Clothing ($200 per adult and $50 per child) = 400

Student loan payments

Credit card payments = 340

Gifts, supplies, toys = 0

Miscellaneous expenses = 50

Savings

Fill in the Income column using the demographic data under the Income heading.

SUM the Budget (do not include the Income in the budget column), Expense, and Income columns.

Format cells that have money values as currency.

For each item determine the budgeted amount and fill in the value in the budget column. All of the budget (including savings) must add up exactly to the sum of the incomes.

Format all cells to be aesthetically pleasing with color and font style. Use currency formatting as appropriate.

In the Difference column create a formula that will subtract the expense from its budgeted amount.

Fill in the values that stay constant (e.g., housing, phone) in the expense and budget columns. Some values should vary from month to month.

Set these pages to print on a single page.

NOW, ungroup the pages.

On the first month, format the difference column so that values that are negative appear with a pink background and values that are above zero appear with a light green background and values that are zero have no fill color (conditional formatting).

Use the format painter to format the other months with the same conditional formatting.

Change the month names to match the tabs.

You now get to spend the money. Use reasonable values. Some values will differ from month to month and others will remain constant.

At least one month should show that expenses go over the budgeted amounts for the month.

On the Summary page:

Create a listing of all budgeted items (use copy and paste).

Put the same column headings as the monthly sheets.

Use cell referencing to add the January through March budgeted amounts.

Use 3D cell referencing to add the expenses from the three months and put the totals under the Expense column.

Use cell referencing to add the incomes from the three months and put the totals under the Income column and Income row.

Sum the Budget, Income, and Expense columns.

Conditionally format the expenses to appear in red with a pink background if they are more than the budgeted amount for that item, or dark green with a pale yellow background if they are less than the budgeted amount for the item.

You want to create a pie chart to show your expenses but there are too many categories. On the Summary page, several rows below the Item column, create a list of the following categories.

Housing

Car

Food

Education

Loans

Entertainment

Miscellaneous

Use the SUM function to add together expenses so that all of the expenses on your summary sheet are represented in one of the categories.

Use the SUM function to show the categories add to the same total as the expenses.

Create a pie chart to show your expenses. Remember what you have learned about charts. Keep the chart on the Summary page.

Format the Summary page to print on two pages: one for the listing of items and the second to include the category information and pie chart.