Business Services

Activities

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

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

Part A – creating a spreadsheet

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

Stationery	Jan	Feb	Mar	Apr	May	Jun
Highlighters	20	40	0	60	0	10
Whiteboard markers	20	0	0	0	0	0
Notepads	30	60	20	0	20	30
Pencils	30	10	0	0	10	0
Pens	60	20	40	10	0	20
A4 Envelopes	300	20	150	0	200	50
Post-it Notes	300	0	50	30	50	0
Miscellaneous	500	300	800	1000	600	500
A4 Paper (reams)	2500	800	1200	600	1000	300
Monthly Totals

Average Monthly Total
Highest Monthly Total
Lowest Monthly Total

Enter the formula required to calculate the total expenditure for the month of January. Copy this formula for the other months
Enter the formula required to calculate the total expenditure for Highlighters. Copy this formula for all other stationery items
Enter the formulae to obtain Average of Monthly Totals, Highest Monthly Total and Lowest Monthly Total. Format these results to no decimal places
Sort the spreadsheet into alphabetical order by Stationery
Format the data so that figures are displayed as currency
Format the spreadsheet so that it is displayed appropriately (bold, shading, borders, centred vertically and horizontally, landscape, fit to one page etc)
Insert a footer which shows the Filename, Task 1, Your Name
Print two copies of the spreadsheet, one showing the formulae and one showing the results

Part B – editing a spreadsheet

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

	Jan	Feb	Mar	Apr	May	Jun
A3 Coloured paper	500	100	200	50	150	80
A4 Lever arch files	1400	700	1000	400	200	150

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

Part C – creating a chart

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
Print a final copy showing the results

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

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

First_name	Last_name	Weekly Wages	Weekly Commission	Voluntary Payment
Andrea	Epinidis	$450.00	$ 81.25	5%
Jane	Hobbs	$600.00	$137.50	10%
Bob	Jankowski	$450.00	$62.50	0%
Ingrid	Johns	$450.00	$25.00	5%
Ervien	Lee	$450.00	$80.00	7.5%
Julie	Singh	$450.00	-	5%
Sebastian	Tran	$450.00	-	9%
Your name	Your name	$400.00	-	3%

Save the document as Superannuation to an appropriate location
Add a total row to calculate the totals for appropriate columns
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
To compare the Employer and Employee contributions, create a suitable chart on a separate sheet
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
Print two copies, one showing the results and the other showing the formulae, including row and column headings for the formula copy only