Task 4

Your Team Leader (lecturer) has requested a spreadsheet showing the sales, expenses, and gross profits for the past five years as shown below. The spreadsheet must be completed and delivered within two days.

Create a new spreadsheet and enter the data fromTable 1 below.Use your textbook, resources, and MS Excel online Help, if required.

Table 1: Gross Profits for the past five years:

Year	2020	2021	2022	2023	2024	Total
Sales	$380,000	$522,510	$669,670	$859,000	$968,510	AutoSum Function
Expenses (30% of Sales)	Formula	Formula	Formula	Formula	Formula	AutoSum Function
Gross Profit	Formula	Formula	Formula	Formula	Formula	Formula

Table 2: Average Gross Profit

Year	2023	2024	Average Gross Profit
Gross Profit	Copy Gross Profit amount from above	Copy Gross Profit amount from above	Average Formula (NOT a Function)

Formula requirements:

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

• Insert formulas to calculate the Expenses and Gross Profit for each year. Expenses are calculated as 30% of sales.Enter 30% in a cell outside the table and use absolute cell referencing in the expense formula.

• Enter a separate table the same as Table 2 above. Insert the formula to calculate the average Gross Profit for the years 2023 and 2024.

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

Edit to include the following:

• Create a pie chart that shows Sales over the 5 years.

• Add appropriate chart title.

• Using the 'Move chart' function, move the chart to a new worksheet in the same workbook.

• Name the new sheet, 'Pie Chart'.

• Use a calculator to manually check that the formulas work correctly. Amend if required.

• Save your file as 'Task 4 Gross Profit.xlsx'.

Submit your draft for approval

Submit your draft in LEARN to the Team Leader (lecturer). You are confirming the formulas have been tested and output meets the task requirements. You do not need to compress (zip) the files.

The Team Leader (lecturer) will check your template and notify you on LEARN if any changes (resubmissions) are required.