1. Use the demographic file of information page to choose the following (click HERE for the file):
   1. Marital status (remember this is some future time)
   2. Income (include spouse)
   3. Housing status from choice
   4. Car(s) from choices
2. Create a new workbook that will allow you to budget the monthly expenses (creation date will be checked).
3. Label the worksheet tabs as:
   1. Demographics
   2. Jan
   3. Feb
   4. March
   5. Summary
4. On the demographic page list the demographic information above (marital status, income(s), housing choice and costs, car choice and cost, along with your name, your spouse's name and children's names).
5. Format this information so that it is clear and aesthetically pleasing.
6. Insert an appropriate image (picture or clip art) on the Demographics page.
7. Put a dynamic date on the page.
8. In the header of the page put a static date, your name, your class section, and your instructor's name.
9. In the footer of the page put the page number.
10. 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:
    1. The words Month and January (you will change February and March to their appropriate names after the sheets are ungrouped).
    2. Several blank rows and then column labels for the following items:
       1. Item
       2. Budget
       3. Expense
       4. Income
       5. Difference
    3. Under the ITEM heading, list the following 25 items for your budget:
       1. Income
       2. Significant other's income (omit if none exists)
       3. Income tax (calculation is 25% of the total income if single, 20% of the total income if married)
       4. Housing (as per choice on demographic sheet)
       5. Escrow
       6. Gas and electric utilities
       7. Water/Sewer
       8. Telephone (land)
       9. Telephone (cell)
       10. Car payment (as per choice on demographic sheet)
       11. Second car payment (omit if only one car)
       12. Car insurance
       13. Second car insurance (omit if only one car)
       14. Gas and car maintenance
       15. Second car gas and car maintenance (omit if only one car)
       16. Food costs ($250 per adult, $50 for infant, $100 per child)
       17. Alcohol (beer, wine) or cigarettes (if used add $40/month for each)
       18. Entertainment (parties, eating out, movies, etc.)
       19. Cable, internet access
       20. Clothing ($200 per adult and $50 per child)
       21. 1. 1. Student loan payments
              2. Credit card payments
              3. Gifts, supplies, toys
              4. Miscellaneous expenses
              5. Savings
           2. Fill in the Income column using the demographic data under the Income heading.
           3. SUM the Budget (do not include the Income in the budget column), Expense, and Income columns.
           4. Format cells that have money values as currency.
           5. 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.
           6. Format all cells to be aesthetically pleasing with color and font style. Use currency formatting as appropriate.
           7. In the Difference column create a formula that will subtract the expense from its budgeted amount.
           8. 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.
           9. Set these pages to print on a single page.
       22. NOW, ungroup the pages.
           1. 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).
           2. Use the format painter to format the other months with the same conditional formatting.
           3. Change the month names to match the tabs.
       23. You now get to spend the money. Use reasonable values. Some values will differ from month to month and others will remain constant.
       24. At least one month should show that expenses go over the budgeted amounts for the month.
       25. Email your instructor and request a "CHANCE" card. Chance cards are used in the game of Monopoly by players picking them up after landing on a space that says "CHANCE." Chance cards can be positive or negative for players, giving them money in their pockets or taking their money away and sending them to jail. Although you cannot go to jail with this assignment, you could get an expenditure or unexpected money from the instructor to use in your budget. In business - even though you plan, unexpected things might happen to change your plans - the CHANCE cards simulate that situation.
       26. Enter the "Chance" amount into the spreadsheet. Put a comment in this cell explaining this expenditure.
       27. On the Summary page:
           1. Create a listing of all budgeted items (use copy and paste).
           2. Put the same column headings as the monthly sheets (Budget, Income, Expense and Difference)
           3. Use 3D cell referencing to add the budgeted amounts from the three months and put the totals under the Budget column.
           4. Use 3D cell referencing to add the expenses from the three months and put the totals under the Expense column.
           5. Use 3D cell referencing to add the incomes from the three months and put the totals under the Income column and Income row.
           6. Use 3D cell referencing to add the differences from the three months and put the totals under the Difference column.
           7. Sum the Budget, Income, Expense, and Difference columns.
           8. 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.
       28. You want to create a pie chart to show your expenses but there are too many categories. On the Summary page, several rows belo
       29. 1. Housing
           2. Car
           3. Food
           4. Education
           5. Loans
           6. Entertainment
           7. Miscellaneous
       30. 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.
       31. Use the SUM function to show the categories add to the same total as the expenses.
       32. Create a pie chart to show your expenses. Remember what you have learned about charts. Keep the chart on the Summary page.
       33. 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

Demographic file

Project demographic information Page The purpose of this assignment is to create a fictional budget for your family (sometime in the future) with the following guidelines. Your first task is to choose your status that you will have then (or have now if you like). Status Options: 1. single, 2. married with no children, 3. married with one child under 6 4. married with two children, one under 6 and the other under 12 Income: You have been offered a job making $42,000 a year. 1. If married with no children your spouse makes $35,000 per year. 2. If married with one child your spouse makes $42,000 per year. 3. If married with more than one child your spouse makes $45,000 per year. Housing Choices: Max occupancy Option # Home Price Monthly (does not include escrow) Escrow amt (Property tax, insurance) 1 1 Apartment, 2bdrm, 1 bath, 1000 sq ft, downtown $1075 1 2 Apartment, 2 bdrm, 1 bath 1500 sq ft, suburbs $620 2 3 Townhouse apt 3 bdrm, 2 bath, 1500 sq ft, suburbs $880 3 4 Apartment, 2 bdrm, suburbs, 1700 sq ft, suburbs $1400 4 5 House, Basic 3 bdrm, two story 1900 sq ft, good schools, suburb $100,000 $687 $150 4 6 House 3 bdrm, 2 bath, 1800 sq ft, suburbs $150,000 $1030 $200 5 7 House 4 bdrm, 2.5 bath, 2100 sq ft, good schools, suburbs $200,000 $1374 $250 5 8 House, (nice and new) 4 bdrm, 2500 sq ft, good schools, suburbs $250,000 $1718 $300 10 Other housing option may be chosen under the following conditions: The house must be found in the homes for sale section of the local newspaper or from listings in a local realty agency. Please copy the ad showing description, location, number of bedrooms and asking price. Assume 10% was put down and the rest as a loan. Use the Excel payment (PMT) function to determine the monthly payment using an 8% interest rate for 30 years (360 months) Compute the monthly escrow at 0.1% of the selling price for property tax and $50 per month for insurance. Car Choices: If married then both persons must have a car. Type Year Model Loan Amt Monthly Additional Comments Passenger 2016 Ford Focus 13,000 $416 Small car Passenger 2017 Hyundai 16,000 $512 Small car Sports 2014 Tiburon 12,000 $384 (holds 2 only) Passenger 2014 Infinity 14,000 $448 Van 2015 Ford Explorer 12,000 $384 SUV 2008 Chevy Tahoe 3,700 $118 SUV cost SUV 2017 BMW 30,000 $960 SUV cost SUV 2005 Chevy Tahoe 7,200 $230 SUV cost Passenger 2018 Toyota Corolla 15,000 $480 Small car Passenger 2008 Toyota Corolla 7,000 $224 Small car Passenger 2015 VW Beetle 10,000 $320 Small car Truck 2012 Dodge Ram 20,000 $640 SUV cost Van 2010 Ford Explorer 5,000 $160 SUV cost Van 2018 Ford Explorer 16,000 $512 SUV costs OTHER Other car options may be chosen under the following conditions: The car must be drivable and listed in a local car advertisement. The down payment will take care of title, tax tags and salesmans commission. Use Excel payment function (PMT) to calculate the monthly payment at an interest rate of 10% over 3 years. Additional car notations: Allow $90 per month per car for gasoline and maintenance if living in the suburbs Allow $60 per month per car for gasoline and maintenance if living downtown. Add 10% to the gas and maintenance cost if an SUV, Van or Truck. Deduct 10% of the gas and maintenance cost if car is small. Add an additional $75per month for repairs if car is more than 7 years old