COMP106 introduction to spreadsheets

Purpose

To assess your ability to:

• use SUM, AVERAGE, MEDIAN, MIN, and MAX functions edit
• and Format Multiple Worksheets at the Same Time navigate
• a Workbook and Rename Worksheets use Date and Time
• Functions
• create a Summary Sheet with Column Sparklines
• chart Data with a Pie Chart
• format a Pie Chart
• use COUNTIF and IF functions and apply conditional formatting
• format and Print a Large Worksheet merge and Center Data and
• Format Cell Styles

Action Items (TO BE DONE INDIVIDUALLY):

1. Use the demographic file of information page to choose the following:
   • Marital status (remember this is some future time)
   • Income (include spouse)
   • Housing status from choice
   • 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:
   • Demographics
   • Jan
   • Feb
   • March
   • 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
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:
       • Item
       • Budget
       • Expense
       • Income
       • Difference
    3. Under the ITEM heading, list the following 25 items for your budget:
       • Income
       • Significant other's income (omit if none exists)
       • 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)
       • Escrow
       • Gas and electric utilities
       • Water/Sewer
       • Telephone (land)
       • Telephone (cell)
       • Car payment (as per choice on demographic sheet)
       • Second car payment (omit if only one car)
       • Car insurance
       • Second car insurance (omit if only one car)
       • Gas and car maintenance
       • Second car gas and car maintenance (omit if only one car)
       • Food costs ($250 per adult, $50 for infant, $100 per child)
       • Alcohol (beer, wine) or cigarettes (if used add $40/month for each)
       • Entertainment (parties, eating out, movies, etc.)
       • Cable, internet access
       • Clothing ($200 per adult and $50 per child)
       • Student loan payments Credit card payments
       • Gifts, supplies, toys
       • Miscellaneous expenses
       • Savings
    4. Fill in the Income column using the demographic data under the Income heading.
    5. SUM the Budget (do not include the Income in the budget column), Expense, and Income columns.
    6. Format cells that have money values as currency.
    7. For each item determine the budgeted amount and fill in the value in the budget All of the budget (including savings) must add up exactly to the sum of the incomes.
    8. Format all cells to be aesthetically pleasing with color and font style. Use currency formatting as appropriate.
    9. In the Difference column create a formula that will subtract the expense from its budgeted amount.
    10. 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.
    11. Set these pages to print on a single page.
11. 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.
12. You now get to spend the money. Use reasonable values. Some values will differ from month to month and others will remain constant.
13. At least one month should show that expenses go over the budgeted amounts for the month.
14. 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.
15. Enter the "Chance" amount into the spreadsheet. Put a comment in this cell explaining this expenditure.
16. 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.
    3. Use cell referencing to add the January through March budgeted amounts.
    4. Use 3D cell referencing to add the expenses from the three months and put the totals under the Expense column.
    5. Use cell referencing to add the incomes from the three months and put the totals under the Income column and Income row.
    6. Sum the Budget, Income, and Expense columns.
    7. 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.
17. 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

18. 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.
19. Use the SUM function to show the categories add to the same total as the expenses.
20. Create a pie chart to show your expenses. Remember what you have learned about charts. Keep the chart on the Summary page.
21. 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.