Task 2

You are required to design and create a spreadsheet to present sales income data to management at the meeting scheduled for next week.

Use your textbook and MS Excel online Help, if required.

Open a new MS Excel spreadsheet. Enter the data below.

Service Income
Income Categories	2019	2020	2021
Share Portfolios	$5600	$5800	formula
Banking Services	$9844	$9040	formula
Debt Elimination	$500	$750	formula
Insurance	$2390	$3500	formula
Superannuation	$3110	$2940	formula
TOTAL	AutoSum Function	AutoSum Function	AutoSum Function
Average Service Income	Formula (NOT a Function)	Formula (NOT a Function)	Formula (NOT a Function)
Difference between 2021 and 2020 service income			Formula

Formula requirements:

• Formulas must include all four arithmetic operators on your spreadsheet i.e., addition, subtraction, multiplication, and division,

• Calculate the service income for 2021, an increase of 10% on the 2020 figures.

• Enter 10% in a cell outside the table. Define the cell as 'Sales_Increase'. Use this Define name to create the 2021 formulas. Fill the formula down for all income categories.

• Enter a function to total each year in the 'TOTAL row'.
• Enter a formula to calculate the Average Service Income amounts for each year.

• Enter a formula at the bottom of the 2021 column to calculate the difference between 2021 and 2020 service income.

Edit to include the following:

• Your manager has requested the following:
  • 2019 figures to be in blue font,
  • 2020 figures to be in green font, and
  • 2021 figures to be in red font.

Ensure the font colours enhance the figures to make them clear and legible.

• Insert a Column chart on Sheet 1 to show the sales figures for the 3 years. Add appropriate titles.

• Change the chart series colours to match the spreadsheet e.g.,
  • 2019 - blue,
  • 2020 - green, and
  • 2021 - red.

• Change the labels on the series to reflect the years i.e., 2019, 2020 and 2021.

• Check the calculations to ensure formulas are correct. Amend if necessary.

• Refer to the organisational policies and procedures. Spreadsheets must meet organisational Spreadsheet Style Guidelines requirements (found in Learn).

• Copy the spreadsheet from Sheet 1 to Sheet 2.

• In Sheet 2, modify the following:
  • Sort the Service Income Categories into ascending alphabetical order.
  • Create a 3D column chart to show Service Income for the years 2020 and 2021.
  • Format and modify the chart, to Chart Style 3.
  • Ensure the chart meets the organisational Spreadsheet Style Guidelines.
  • Enter a header: left hand side - your name, centre - worksheet name, right hand side - date.
  • Enter a document footer - left hand side - page number.
• Rename 'Sheet 1' tab to 'Service Income'.
• Save your file as 'Task 2 Service Income.xlsx' and exit the application. Submit the file in LEARN.