Competency Code: BSBITU304

Buy paper about this assignment here (Email us:


Competency Code: BSBITU304

Competency Name: Produce Spreadsheets

Student Name: __________________________ Date: _________________

Student Declaration
I understand and declare that:
 The assessment process has been thoroughly explained to me and I am ready to
undertake formal assessment.
 The information and documents submitted will be used to assess my competency
against the competency standards.
 The evidence presented is my own work and has not been copied or completed by
another person.

Time Allowed: One week


 This case study consists of 1 Project with Parts A – D
 Instructions must be followed and answers must reflect the requirements of the instructions
 All projects must be completed
 Re-assessment must be negotiated with your trainer if required.

Trainer and Assessor Use only

Assessor comments:

Attempt 1 Satisfactory  Not yet Satisfactory  Date: ________
Attempt 2 Satisfactory  Not yet Satisfactory  Date: ________
Attempt 3 Satisfactory  Not yet Satisfactory  Date: ________

Student Signature: ____________________________________

Trainer/Assessor Signature: ______________________________

Due date for reassessment (if required) ______________________________
BSBITU304 27/07/2016 V001 Page 1 of 3

Prior to commencement of the below activities, check your workstation to ensure that it is set up
ergonomically correct for your individual needs.
All projects must be completed using energy and resource conservation.
You have 3 hours to complete Parts A – C of the project.

1. Produce the following spreadsheet in Microsoft Excel or a similar spreadsheeting program:

John’s Wine Store
Red Wine Sales June 2015
Wine (Bottles) Week 1 Week 2 Week 3 Week 4 Total Average
Browns 2010 Cabernet Sauvignon 11 32 27 15
Penfolds 2014 Merlot 17 15 22 24
Hunter Valley 2004 Shiraz 29 21 19 26
Taylors 2013 Shiraz 23 19 12 19
Yalumba 2009 Cabernet
Sauvignon 15 23 24 23
Clare Valley 2008 Pinot Noir 19 31 18 21
Templeton 2011 Rose 26 16 21 34
Hardys 2008 Merlot 17 20 26 23

2. Follow the organisational requirements below to edit your spreadsheet. You must complete every
a. Merge and centre the first two rows. Make the writing in these 2 rows bold and increase the
font size to 14
b. Use the formula functions to fill out the missing totals and the averages (as shown in yellow)
c. Create a new CELL STYLE called ‘Style 1’ (see p. 42) and format as follows:
 Numbers should have no decimal places
 Font should be Arial, size 11
 Show all borders
 Everything BELOW the first 2 rows should be formatted using the style sheet
d. Increase the column size so that all of the wine names fit on one line
e. Make the writing in the total row and column bold
3. Proofread for spelling mistakes
4. Check/Test the formulas
5. Save as “Wine Sales”
6. Do a print preview and make any necessary adjustments
7. Print the spreadsheet so that it fits on one page

BSBITU304 27/07/2016 V001 Page 2 of 3

8. Copy and paste the table from Part A into a new excel spreadsheet
9. Edit the table so that you can see all of the formulas you used to find the totals and averages
10. Save as “Wine Sales Formulas”
11. Do a print preview and make any necessary adjustments
12. Print the spreadsheet so that it fits on one page


13. Open the spreadsheet “Wine Sales” that you completed in Part A
14. On a new worksheet in that workbook, choose and create two different types of charts that
properly represent the information in the table relating to the four weeks of sales figures ONLY (do
not include the total and average figures)
15. The chart must display all of the following organisational requirements:
a. The title at the top
b. The legend at the right hand side
c. Lablel the Y Axis “Bottles Sold”
d. Label the X Axis “Type of Wine”
16. Save the document
17. Do a print preview and print the charts so that they both fit on one page

Time Started: Time Finished:

18. List at least 2 ways you adjusted your workstation to ensure that you adhered to ergonomic
19. List at least 2 ways that you practiced energy and resource conservation techniques while
producing the spreadsheet (how did you save energy, paper, ink, etc?)
20. List at least 1 excel function or method you used to check/test your formulas
21. List at least 2 sources of help you used to overcome problems when designing and producing the
22. Before submitting your work, compare your project with another student.
a. Compare and discuss your total and average results in Part A
b. Compare and discuss the formulas you used in Part B
c. Compare and discuss the chart types you selected in Part C
Student Name _____________________________________
When submitting this assessment, there should be 4 pages in total:
 One spreadsheet for Part A
 One spreadsheet for Part B
 One sheet with 2 charts for Part C
 A separate page with your answers for Part D

Buy paper about this assignment here (Email us:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: