Create and use simple spreadsheets
Key terms and concepts
Term Definition
Absolute and relative cell addresses Absolute cell addresses – references to a particular cell or groups of cells that refer to a specific location or locations.
Relative cell addresses – references to a cell or group of cells that changes depending on the location.
Axis title Text that explains what data a chart axis represents.
Cells The intersection of columns and rows, boxes in which can a single piece of data can be entered
Data range A group of cells containing data
Division A function that determines how many times one quantity is contained in another
Efficiency of formulae Effectiveness of formulae to produce the required result using the least number of steps
Formatting Specifying the properties or visual layout and look of a document
Formulae Expressions used to calculate data to produce a desired result
Headers / footers Areas, either in the top (header) or bottom (footer) of documents, that can contain document information that repeats on all printed pages
Legend Explanatory text that describes the meaning of chart elements
Multiplication A function that calculates by multiplying cell entries
Recycling The reusing of objects. For example, in a business environment this can refer to
• sending used paper for remanufacture/using paper that has been remanufactured
• reusing office stationery, such as envelopes
Spreadsheet A computer program that displays data in rows and columns on a screen, and lets you create and manipulate numerical data electronically
Subtraction A function that calculates the numerical difference between cell entries
Sum A function which is used to add the numbers in a range of cells
Text Words treated as data by a computer
Important notes
Using safe work practices
Using safe work practices refers to the ergonomic requirements, work organisation and conservation techniques that should be considered in a workplace environment.
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
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
2.
3. Enter the formula required to calculate the total expenditure for the month of January. Copy this formula for the other months
4. Enter the formula required to calculate the total expenditure for Highlighters. Copy this formula for all other stationery items
5. Enter the formulae to obtain Average of Monthly Totals, Highest Monthly Total and Lowest Monthly Total. Format these results to no decimal places
6. Sort the spreadsheet into alphabetical order by Stationery
7. Format the data so that figures are displayed as currency
8. Format the spreadsheet so that it is displayed appropriately (bold, shading, borders, centred vertically and horizontally, landscape, fit to one page etc)
9. Insert a footer which shows the Filename, Task 1, Your Name
10. Print two copies of the spreadsheet, one showing the formulae and one showing the results
Part B – editing a spreadsheet
1. Kim has asked you to amend the spreadsheet as follows
a. Insert two rows at the top of the spreadsheet then key in the heading,
Freedom Travel Pty Ltd on one line, Stationery Expenses below
b. 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
c.
d. The company no longer uses Whiteboard markers. Delete that row
e. 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
Key terms and concepts
Term Definition
Absolute and relative cell addresses Absolute cell addresses – references to a particular cell or groups of cells that refer to a specific location or locations.
Relative cell addresses – references to a cell or group of cells that changes depending on the location.
Axis title Text that explains what data a chart axis represents.
Cells The intersection of columns and rows, boxes in which can a single piece of data can be entered
Data range A group of cells containing data
Division A function that determines how many times one quantity is contained in another
Efficiency of formulae Effectiveness of formulae to produce the required result using the least number of steps
Formatting Specifying the properties or visual layout and look of a document
Formulae Expressions used to calculate data to produce a desired result
Headers / footers Areas, either in the top (header) or bottom (footer) of documents, that can contain document information that repeats on all printed pages
Legend Explanatory text that describes the meaning of chart elements
Multiplication A function that calculates by multiplying cell entries
Recycling The reusing of objects. For example, in a business environment this can refer to
• sending used paper for remanufacture/using paper that has been remanufactured
• reusing office stationery, such as envelopes
Spreadsheet A computer program that displays data in rows and columns on a screen, and lets you create and manipulate numerical data electronically
Subtraction A function that calculates the numerical difference between cell entries
Sum A function which is used to add the numbers in a range of cells
Text Words treated as data by a computer
Important notes
Using safe work practices
Using safe work practices refers to the ergonomic requirements, work organisation and conservation techniques that should be considered in a workplace environment.
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
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
2.
3. Enter the formula required to calculate the total expenditure for the month of January. Copy this formula for the other months
4. Enter the formula required to calculate the total expenditure for Highlighters. Copy this formula for all other stationery items
5. Enter the formulae to obtain Average of Monthly Totals, Highest Monthly Total and Lowest Monthly Total. Format these results to no decimal places
6. Sort the spreadsheet into alphabetical order by Stationery
7. Format the data so that figures are displayed as currency
8. Format the spreadsheet so that it is displayed appropriately (bold, shading, borders, centred vertically and horizontally, landscape, fit to one page etc)
9. Insert a footer which shows the Filename, Task 1, Your Name
10. Print two copies of the spreadsheet, one showing the formulae and one showing the results
Part B – editing a spreadsheet
1. Kim has asked you to amend the spreadsheet as follows
a. Insert two rows at the top of the spreadsheet then key in the heading,
Freedom Travel Pty Ltd on one line, Stationery Expenses below
b. 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
c.
d. The company no longer uses Whiteboard markers. Delete that row
e. 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
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%
Your name Your name $400.00 - 3%
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%
Your name Your name $400.00 - 3%
2.
3. Save the document as Superannuation to an appropriate location
4. Add a total row to calculate the totals for appropriate columns
5. 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
6. To compare the Employer and Employee contributions, create a suitable chart on a separate sheet
7. 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
8. Print two copies, one showing the results and the other showing the formulae, including row and column headings for the formula copy only
Activities answers
3. Save the document as Superannuation to an appropriate location
4. Add a total row to calculate the totals for appropriate columns
5. 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
6. To compare the Employer and Employee contributions, create a suitable chart on a separate sheet
7. 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
8. Print two copies, one showing the results and the other showing the formulae, including row and column headings for the formula copy only
Activities answers
Activity 1
The tables below show the correct results of the calculations in this activity. To see the completed spreadsheet, with suggested formulae, formatting and chart, download the Excel spreadsheet Stationery.
Part A - creating a spreadsheet
Stationery Jan Feb Mar Apr May Jun Total
A4 Envelopes $300.00 $20.00 $150.00 $- $200.00 $50.00 $720.00
A4 Paper (reams) $2,500.00 $800.00 $1,200.00 $600.00 $1,000.00 $300.00 $6,400.00
Highlighters $20.00 $40.00 $- $60.00 $- $10.00 $130.00
Miscellaneous $500.00 $300.00 $800.00 $1,000.00 $600.00 $500.00 $3,700.00
Notepads $30.00 $60.00 $20.00 $- $20.00 $30.00 $160.00
Pencils $30.00 $10.00 $- $- $10.00 $- $50.00
Pens $60.00 $20.00 $40.00 $10.00 $- $20.00 $150.00
Post-it Notes $300.00 $- $50.00 $30.00 $50.00 $- $430.00
Whiteboard Markers $20.00 $- $- $- $- $- $20.00
Monthly Total $3,760.00 $1,250.00 $2,260.00 $1,700.00 $1,880.00 $910.00 $11,760.00
Average Monthly Total $1,960
Highest Monthly Total $3,760
Lowest Monthly Total $910
Part B - editing a spreadsheet
A4 Envelopes $300.00 $20.00 $150.00 $- $200.00 $50.00 $720.00
A4 Paper (reams) $2,500.00 $800.00 $1,200.00 $600.00 $1,000.00 $300.00 $6,400.00
Highlighters $20.00 $40.00 $- $60.00 $- $10.00 $130.00
Miscellaneous $500.00 $300.00 $800.00 $1,000.00 $600.00 $500.00 $3,700.00
Notepads $30.00 $60.00 $20.00 $- $20.00 $30.00 $160.00
Pencils $30.00 $10.00 $- $- $10.00 $- $50.00
Pens $60.00 $20.00 $40.00 $10.00 $- $20.00 $150.00
Post-it Notes $300.00 $- $50.00 $30.00 $50.00 $- $430.00
Whiteboard Markers $20.00 $- $- $- $- $- $20.00
Monthly Total $3,760.00 $1,250.00 $2,260.00 $1,700.00 $1,880.00 $910.00 $11,760.00
Average Monthly Total $1,960
Highest Monthly Total $3,760
Lowest Monthly Total $910
Part B - editing a spreadsheet
Freedom Travel Pty Ltd
Stationery Expenses 2003
Stationery Jan Feb Mar Apr May Jun Total
A4 Paper (reams) $2,500.00 $800.00 $1,200.00 $600.00 $1,000.00 $300.00 $6,400.00
A4 Lever arch files $1,400.00 $700.00 $1,000.00 $400.00 $200.00 $150.00 $3,850.00
Miscellaneous $500.00 $300.00 $800.00 $1,000.00 $600.00 $500.00 $3,700.00
A3 Coloured Paper $500.00 $100.00 $200.00 $50.00 $150.00 $80.00 $1,080.00
A4 Envelopes $300.00 $20.00 $150.00 $- $200.00 $50.00 $720.00
Post-it Notes $300.00 $- $50.00 $30.00 $50.00 $- $430.00
Notepads $30.00 $60.00 $20.00 $- $20.00 $30.00 $160.00
Pens $60.00 $20.00 $40.00 $10.00 $- $20.00 $150.00
Highlighters $20.00 $40.00 $- $60.00 $- $10.00 $130.00
Pencils $30.00 $10.00 $- $- $10.00 $- $50.00
Monthly Total $5,640.00 $2,050.00 $3,460.00 $2,150.00 $2,230.00 $1,140.00 $16,670.00
Average Monthly Total $2,778
Highest Monthly Total $5,640
Lowest Monthly Total $1,140
Part C - creating a chart
Activity 2
Stationery Expenses 2003
Stationery Jan Feb Mar Apr May Jun Total
A4 Paper (reams) $2,500.00 $800.00 $1,200.00 $600.00 $1,000.00 $300.00 $6,400.00
A4 Lever arch files $1,400.00 $700.00 $1,000.00 $400.00 $200.00 $150.00 $3,850.00
Miscellaneous $500.00 $300.00 $800.00 $1,000.00 $600.00 $500.00 $3,700.00
A3 Coloured Paper $500.00 $100.00 $200.00 $50.00 $150.00 $80.00 $1,080.00
A4 Envelopes $300.00 $20.00 $150.00 $- $200.00 $50.00 $720.00
Post-it Notes $300.00 $- $50.00 $30.00 $50.00 $- $430.00
Notepads $30.00 $60.00 $20.00 $- $20.00 $30.00 $160.00
Pens $60.00 $20.00 $40.00 $10.00 $- $20.00 $150.00
Highlighters $20.00 $40.00 $- $60.00 $- $10.00 $130.00
Pencils $30.00 $10.00 $- $- $10.00 $- $50.00
Monthly Total $5,640.00 $2,050.00 $3,460.00 $2,150.00 $2,230.00 $1,140.00 $16,670.00
Average Monthly Total $2,778
Highest Monthly Total $5,640
Lowest Monthly Total $1,140
Part C - creating a chart
Activity 2
The table below shows the correct results of the calculations in this activity. To see the completed spreadsheet, with suggested formulae, formatting and chart, download the Excel spreadsheet Superannuation.
Superannuation spreadsheet
Month ending 28 November 2003 Employer Contribution 9.0%
First Name Last Name Wages Commission Total Employer contribution Voluntary
contribution rate Voluntary contribution Total contribution
Andrea Epinidis $1,800.00 $325.00 $2,125.00 $191.25 5.0% $106.25 $297.50
Jane Hobbs $2,400.00 $137.50 $2,537.50 $228.38 10.0% $253.75 $482.13
Bob Jankowski $1,800.00 $250.00 $2,050.00 $184.50 0.0% $- $184.50
Ingrid Johns $1,800.00 $25.00 $1,825.00 $164.25 5.0% $91.25 $255.50
Ervien Lee $1,800.00 $100.00 $1,900.00 $171.00 7.5% $142.50 $313.50
Julie Singh $1,800.00 $- $1,800.00 $162.00 5.0% $90.00 $252.00
Sebastian Tran $1,800.00 $- $1,800.00 $162.00 9.0% $162.00 $324.00
Your name Your name $1,600.00 $- $1,600.00 $144.00 3.0% $48.00 $192.00
Total $14,800.00 $837.50 $15,637.50 $1,407.38 $893.75 $2,301.13
Employer contribution summary
Average contribution $175.92
Highest contribution $228.38
Lowest contribution $144.00
contribution rate Voluntary contribution Total contribution
Andrea Epinidis $1,800.00 $325.00 $2,125.00 $191.25 5.0% $106.25 $297.50
Jane Hobbs $2,400.00 $137.50 $2,537.50 $228.38 10.0% $253.75 $482.13
Bob Jankowski $1,800.00 $250.00 $2,050.00 $184.50 0.0% $- $184.50
Ingrid Johns $1,800.00 $25.00 $1,825.00 $164.25 5.0% $91.25 $255.50
Ervien Lee $1,800.00 $100.00 $1,900.00 $171.00 7.5% $142.50 $313.50
Julie Singh $1,800.00 $- $1,800.00 $162.00 5.0% $90.00 $252.00
Sebastian Tran $1,800.00 $- $1,800.00 $162.00 9.0% $162.00 $324.00
Your name Your name $1,600.00 $- $1,600.00 $144.00 3.0% $48.00 $192.00
Total $14,800.00 $837.50 $15,637.50 $1,407.38 $893.75 $2,301.13
Employer contribution summary
Average contribution $175.92
Highest contribution $228.38
Lowest contribution $144.00
Superannuation chart
Test your knowledge
Select the correct answer for each of the following
1. If cell C4 contains a formula. Where would you see the formula displayed?
a. In cell C4
b. In the Formula bar
c. In the Name box
d. In the Status bar
2. How can you tell which cell in a worksheet is the active cell?
a. It is blinking
b. It is displayed in reverse video
c. It is surrounded by a heavy border
d. All of the above
3. How can you change the active cell?
a. Only by clicking in a different cell
b. Only by using the arrow keys to move to a different cell
c. By clicking or by using the arrow keys
d. By using the Taskbar
4. Which of the following options is controlled through the Page Setup command in Excel?
a. Margins
b. Orientation (portrait or landscape)
c. Headers and footers
d. All of the above
5. Which of the following is a valid Excel formula?
a. =C4*D4
b. B4*D4
c. Both =C4*D4 and B4*D4
d. Neither =C4*D4 nor B4*D4
6. The function =AVERAGE(A6:A8) is the equivalent of the formula
a. A6+A7+A8/3
b. =(A6+A7+A8/3)
c. =(A6+A7+A8)/3
d. =(A6+A8)/2
7. Which of the following is a valid cell range?
a. B6
b. A2:A3
c. C4:G9
d. All of the above
8. Which of the following formats will display a number with a dollar sign ($1234.56)?
a. Accounting format
b. Currency format
c. Both Accounting and Currency formats
d. Neither Accounting nor Currency format
Answers
Answer True or False for the following statements
True False
9. Excel cell references refer to the row by letter and the column by number.
10. All Excel formulae must be preceded by the equal sign (=).
11. An Excel worksheet may contain several workbooks.
12. A Function is a built-in mathematical formula.
13. If your worksheet is one column too wide, you have no choice but to print it on two pages.
14. A cell range may be as small as a single cell or as large as the entire worksheet.
Test your knowledge answers
1. If cell C4 contains a formula. Where would you see the formula displayed?
b. In the Formula bar
2. How can you tell which cell in a worksheet is the active cell?
c. It is surrounded by a heavy border
3. How can you change the active cell?
c. By clicking or by using the arrow keys
4. Which of the following options is controlled through the Page Setup command in Excel?
d. All of the above
5. Which of the following is a valid Excel formula?
a. =C4*D4
6. The function =AVERAGE(A6:A8) is the equivalent of the formula:
c. =(A6+A7+A8)/3
7. Which of the following is a valid cell range?
d. All of the above
8. Which of the following formats will display a number with a dollar sign ($1234.56)?
c. Both Accounting and Currency formats
9. Excel cell references refer to the row by letter and the column by number.
False – the reverse is true, references are referred to by number for rows and letter for columns
10. All Excel formulas must be preceded by the equal sign (=).
True
11. An Excel worksheet may contain several workbooks.
False – in Excel a workbook is the file in which you work and each workbook can contain several sheets
12. A Function is a built-in mathematical formula
True
13. If your worksheet is one column too wide, you have no choice but to print it on two pages.
False – from Page Setup, it is possible to select “Fit to 1 page”
14. A cell range may be as small as a single cell or as large as the entire worksheet.
True
No comments:
Post a Comment