INSTRUCTIONS

Start the workbook, then in the first sheet: add a Text Box and include Student Name, Student ID, and due Date. Create an annual Income Table containing 2 columns and 5 rows corresponding to the following income categories: 1) Annual Gross Income/ Wages = $44,976 2) Tax paid = 19 cents for each $1 over $18,200 3) Net Income = Gross Income less Tax paid 4) Income from government allowances: $67 per fortnight 5) Miscellaneous, or other income: $35 per week

Create an Expenses table containing columns for the following 6 months of the year: Jul, Aug, Sep, Oct, Nov, and Dec of 2021. To add 8 rows corresponding to the following expenses categories. Make sure to fill up each cell with various amounts (It is okay for the Rent, Phone and Internet to have similar payment throughout the year). 1) Rent or Mortgage payment. 2) Utility Bills (Water, Gas, Electricity, Council Rates). 3) Groceries/ Household Food. 4) Motor Vehicle Expenses (MVE) - Registration, Insurance, Fuel and Repairs and/or Public Transport. 5) Medical (Doctors, Prescriptions, Health Insurance). 6) Phone and Internet. 7) Entertainment (Holiday, Dining out, Movies, Concert, Games, etc.). 8) Other (Include anything that is not included in the above categories, such as Education expenses, School fees, Child-care, Clothing, Hairdresser, Gym, Pet costs etc.).

Calculate Total Income and Expenses for the year 1) At the base of your Income Table, add a row for Total Yearly Net Income and use the AutoSum feature or a Formula to calculate the yearly net income total. 2) At the base of your Expenses Table, add a row for Total Monthly Expenses using the AutoSum feature or a Formula. Calculate your total 6 months expenses then multiply by 2 to get the figure for 12 months.

Create an annual summary table which includes the following figures: 1) Annual Net Income Link the Total Yearly Net Income from the Income Table. 2) Annual Expenses Link the Total Yearly Expenses from the Expenses Table. 3) Annual Deficit or Surplus Use a formula to subtract the Total Annual Expenses from the Total Annual Net Income.

Format the Three Tables 1) Format all the figures in your three tables, as dollar amounts with No decimal places (if there is any decimal). 2) Format each table using the Format as Table function on the Home tab. 3) Clearly label your three tables as Income, Expenses and Annual Summary. 4) Rename the Sheets (within the Workbook) accordingly: Name, Income, Expenses, Summary.

5) Add Conditional Formatting for the Expenses Table and highlight Cells rules:

- Greater than $120 with Green Fill with Dark Green text. - Less than $120 with Light Red Fill with Dark Red Text.

Create two charts/graphs 1) Create a Pie Chart comparing the categories in your Expenses table for the month of December 2021 (Tip: Do not include the total amount in the chart). Give the chart a suitable title (above the Chart), add More Data Label Options the Percentage, take away the Value, and add the Legend. 2) Create a Column Chart comparing the three figures in your Annual Summary Table (Annual Income, Annual Expenses and Annual Deficit or Surplus). Give the Chart a suitable title and make sure to include the following Chart Elements: Axes, Axis Titles, Gridlines, Data Labels (Outside End) and More Data Label Options (Value). Modify the colors for each column.

Margins and Layout Ensure tables and charts can be easily read and printed within each page, with appropriate margins and layout. The Workbook should be no more than 4 pages.