ICAITU130A Create and use simple spreadsheets

ICAITU130A Create and use simple spreadsheets

This unit covers creating and using simple spreadsheets and charts through the use of spreadsheet software.

Critical aspects of evidence
Integrated demonstration of all elements of competency and their performance criteria
Design appropriate type and use of spreadsheet
Use of cell-based formulae

Activities
As an administrative assistant for Freedom Travel Pty Ltd, one of your duties is to maintain financial data using spreadsheet software.

Activity 1
The manager, Kim Leigh, has asked you to create a spreadsheet to record monthly stationery expenses.

Part A – creating a spreadsheet

1 Create a new spreadsheet and enter the data as shown below. Save the spreadsheet with the filename STATIONERY, to an appropriate location

2 Enter the formula required to calculate the total expenditure for the month of January. Copy this formula for the other months

3 Enter the formula required to calculate the total expenditure for Highlighters. Copy this formula for all other stationery items

4 Enter the formulae to obtain Average of Monthly Totals, Highest Monthly Total and Lowest Monthly Total. Format these results to no decimal places

5 Sort the spreadsheet into alphabetical order by Stationery

6 Format the data so that figures are displayed as currency

7 Format the spreadsheet so that it is displayed appropriately (bold, shading, borders, centred vertically and horizontally, landscape, fit to one page etc)

8 Insert a footer which shows the Filename, Task 1, Your Name

9. Print two copies of the spreadsheet, one showing the formulae and one showing the results

A spreadsheet - Task 1 Theerasak

Part B – editing a spreadsheet

1 Kim has asked you to amend the spreadsheet as follows
-Insert two rows at the top of the spreadsheet then key in the heading,Freedom Travel Pty Ltd on one line, Stationery Expenses below
-Some supplies have been omitted. Add two rows and enter the following data

-The company no longer uses Whiteboard markers. Delete that row
-Sort the spreadsheet so that the Total column is in descending order

2 Print two copies of the amended spreadsheet, one showing the formulae and one showing the results

Part C – creating a chart
1 Kim needs to present half yearly expenses to the board of directors and has requested a chart that compares stationery expenses for the six months. Create an exploded pie chart as an object on the worksheet
2 Print a final copy showing the results

Activity 2

Kim has asked you to create a spreadsheet that that will track superannuation contributions. There are two types of contribution payable

-Employer contribution, currently 9% of gross wages
-Employee voluntary contribution, which varies for each employee according to wages, commission and percentage of wages contributed

1 Design a spreadsheet for the month of November 2003, based on the following data. You will need to calculate

-Total monthly wages, which includes commission for travel consultants
-Employer superannuation contribution (be sure to use an absolute reference)
-Voluntary superannuation contribution
-Total superannuation contributed

2 Save the document as Superannuation to an appropriate location
3 Add a total row to calculate the totals for appropriate columns
4 Kim has asked you to calculate the highest, lowest and average employer superannuation contributions. Calculate these figures in a summary area below the total row
5 To compare the Employer and Employee contributions, create a suitable chart on a separate sheet
6 You have been asked to print a copy of the spreadsheet for a staff meeting. Ensure that the spreadsheet is displayed appropriately, including a title, landscape orientation, “fit to one page”, centred vertically and horizontally, header/footer etc
7 Print two copies, one showing the results and the other showing the formulae, including row and column headings for the formula copy only

Superannuation chart