Business Services

Home > Business Services > Business Services (120/240 hours) > Create and use simple spreadsheets > Create and use simple spreadsheets

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

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	Totals
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
rmat 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

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

Stationery	Jan	Feb	Mar	Apr	May	Jun	Totals
A4 Envelopes	$300	$20	$150	$0	$200	$50	$720
A4 Paper (reams)	$2,500	$800	$1,200	$600	$1,000	$300	$6,400
Highlighters	$20	$40	$0	$60	$0	$10	$130
Miscellaneous	$500	$300	$800	$1,000	$600	$500	$3,700
Notepads	$30	$60	$20	$0	$20	$30	$160
Pencils	$30	$10	$0	$0	$10	$0	$50
Pens	$60	$20	$40	$10	$0	$20	$150
Post-it Notes	$300	$0	$50	$30	$50	$0	$430
Whiteboard markers	$20	$0	$0	$0	$0	$0	$20
Monthly Totals	$3,760	$1,250	$2,260	$1,700	$1,880	$910	$11,760
Average Monthly Total	$1,960
Highest Monthly Total	$3,760
Lowest Monthly Total	$910



SPREADSHEET TASK 1 REZA

Stationery	Jan	Feb	Mar	Apr	May	Jun	Totals
A4 Envelopes	300	20	150	0	200	50	720
A4 Paper (reams)	2500	800	1200	600	1000	300	6400
Highlighters	20	40	0	60	0	10	130
Miscellaneous	500	300	800	1000	600	500	3700
Notepads	30	60	20	0	20	30	160
Pencils	30	10	0	0	10	0	50
Post-it Notes	300	0	50	30	50	0	430
Whiteboard markers	20	0	0	0	0	0	20
Monthly Totals	3700	1230	2220	1690	1880	890	11610
Average Monthly Total	1935
Highest Monthly Total	3700
Lowest Monthly Total	890

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

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

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

Freedom Travel Pty Ltd
Stationery Expenses
Stationery	Jan	Feb	Mar	Apr	May	Jun	Totals
A4 ENVELOPES	$300	$20	$150	$0	$200	$50	$720
A3 COLOURED PAPER	$500	$100	$200	$50	$150	$80	$1,080
A4 LEVER ARCH FILES	$1,400	$700	$1,000	$400	$200	$150	$3,850
A4 Paper (reams)	$2,500	$800	$1,200	$600	$1,000	$300	$6,400
Highlighters	$20	$40	$0	$60	$0	$10	$130
Miscellaneous	$500	$300	$800	$1,000	$600	$500	$3,700
Notepads	$30	$60	$20	$0	$20	$30	$160
Pencils	$30	$10	$0	$0	$10	$0	$50
Pens	$60	$20	$40	$10	$0	$20	$150
Post-it Notes	$300	$0	$50	$30	$50	$0	$430
Monthly Totals	$5,640	$2,050	$3,460	$2,150	$2,230	$1,140	$16,670
Average Monthly Total	$2,778
Highest Monthly Total	$5,640
Lowest Monthly Total	$1,140


SPREADSHEET	TASK 2	REZA	S

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

Freedom Travel Pty Ltd

Stationery Expenses

Stationery

Jan

Feb

Mar

Apr

May

Jun

Totals

A4 ENVELOPES

$300

$20

$150

$200

$50

$720

A3 COLOURED PAPER

$500

$100

$200

$50

$150

$80

$1,080

A4 LEVER ARCH FILES

$1,400

$700

$1,000

$400

$200

$150

$3,850

A4 Paper (reams)

$2,500

$800

$1,200

$600

$1,000

$300

$6,400

Highlighters

$20

$40

$60

$10

$130

Miscellaneous

$500

$300

$800

$1,000

$600

$500

$3,700

Notepads

$30

$60

$20

$30

$160

Pencils

$30

$10

$50

Pens

$60

$20

$40

$10

$20

$150

Post-it Notes

$300

$50

$30

$50

$430

Monthly Totals

$5,640

$2,050

$3,460

$2,150

$2,230

$1,140

$16,670

Average Monthly Total

$2,778

Highest Monthly Total

$5,640

Lowest Monthly Total

$1,140

SPREADSHEET

TASK 3

HARRY

Suggested response

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

a. Total monthly wages, which includes commission for travel consultants

b. Employer superannuation contribution (be sure to use an absolute reference)

c. Voluntary superannuation contribution

d. Total superannuation contributed

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%
SHEFRIL REZA	SHEFRIL REZA	$400.00	-	3%

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

Suggested response

Back to contents