Reference no: EM133019930

BSBITU402 DEVELOP AND USE COMPLEX SPREADSHEETS

ASSESSMENT TASK 1:

QUESTION 1
a) List the four (4) key documents that an organisation may have available internally to use to instruct their employees on how ensure document design is consistent with company and/or client requirements.
b) What is the main document an external client would use to provide information about particular design elements they require in document design and production?
c) What are the essential elements of the document you identified in (b) above?
d) What are the 6 basic principles of document design?
QUESTION 2
Research the following eight (8) software applications that businesses may use to assist in document design, production and storage, and list their functions and features in the tables following
QUESTION 3
For each of the applications in the preceding Question 2, list in the following most direct source of help available if you encounter basic design difficulties in the design, and in the production, of business documents using that particular application.
QUESTION 4
In addition to the software applications identified in Question 2 above, what are the three (3) typical office equipment technologies a business can use to produce business documents in-house? Provide a brief outline of each.
QUESTION 5
a) How often is a document auto-saved by default when using Microsoft Word?
b) What are the two (2) methods within Microsoft Word that you can use to try to retrieve a version of a document you created in Microsoft Word if your computer ‘crashes'?
QUESTION 6
a) Outline what are organisational plans and their purpose.
b) Outline what an organisational policy is and its purpose.
c) Outline what an organisational procedure is and its purpose.
QUESTION 7
An organisation will typically have its own particular requirements relating to how data is captured and input into or converted into electronic records, how the data is stored and how it is presented. What are the common key features that every organisation should aim to achieve through those requirements?
QUESTION 8
Describe how each of the following formatting and design techniques may impact on the presentation and readability of data and information-
QUESTION 9
Outline the steps to take when closing a file on an external hard drive or USB to ensure you can exit the application without data loss or damage of data)
QUESTION 10
By referring to best practices, explain what should be included in typical organisational requirements for each of the following:
QUESTION 11
An organisation may have specific policies governing the transfer and security of data. What are the eight (8) key features that should be included in a data or cyber security policy according to business.gov.au?
QUESTION 12
Explain the following advanced Microsoft Excel/Access/Google Sheets spreadsheet functions-
a) SUMPRODUCT FUNCTION
b) INDIRECT FUNCTION
c) DATABASE FUNCTIONS
d) ARRAY FORMULAS
e) FORECAST FUNCTION
f) TREND FUNCTION
g) PIVOT TABLES
h) MACROS
i) SCRIPTS
QUESTION 13
Describe what culturally appropriate communication is?
QUESTION 14
In a meeting environment, list for each of the following options the most appropriate communication technique?
i. words that include or words that exclude?
ii. when referring to women, use the word " ladies" or the word "women"?
iii. Using gender specific or gender neutral pronouns when discussing hypothetical cases?
QUESTION 15
Using Best Bite Café a guide, outline organisational procedures for formal meetings.
QUESTION 16
Using Best Bite Café a guide, outline how organisational procedures for informal meetings are different to organisational procedures for formal meetings.
QUESTION 17
List below the key provisions of each of the following Australian legislation relating to meetings, including what provisions may be within constitutions or Article of Association.
QUESTION 18
List below the ethical practices that should be followed when organising and running meetings.

ASSESSMENT TASK 2: PROJECT and ROLE PLAY

PART A - DESIGN BUSINESS DOCUMENTS
Activity 1
1) review style guides-
Review the Best Bite Café Style Guide and the Marino Enterprises Style Guide provided as-
• Handout - Best Bite Café Style Guide, and
• Handout - Marino Enterprises Style Guide
to familiarise yourself with the house / style guide requirements for this business.
For Part A of this assessment, you must conform to these style guides where relevant and wherever appropriate.
2) Meet with business owner (Roleplay)-
Your Assessor will role play as the owner of the business. When you have familiarised yourself with the style guides, review Questions 3 to 5 that follow, and the similar questions in Activity 5 which refer to the second document.
Before you complete your written answers to those questions, you are to meet with the business owner (role played by you Assessor) to discuss and clarify/confirm your interpretation and understanding of the format and style requirements for each of the two (2) documents you have been asked to design and produce.
During the meeting you must:
1. Provide an adequate understanding of the Style Guide requirements relevant to the design and production of the Customer Details form, including:
- the specific layout and/or style requirements
- formats for naming document files
- where soft copies of the documents should be stored
- the software application(s) you propose to use for each document
- formats for naming document files
- where soft copies of the documents should be stored
2. Provide an adequate understanding of the Style Guide requirements relevant to the design and production of the Weekly Beverage Sales form, including:
- the specific layout and/or style requirements
- formats for naming document files
- where soft copies of the documents should be stored
- the software application(s) you propose to use for each document
- formats for naming document files
- where soft copies of the documents should be stored
3. Use open-ended question(s) to interact effectively and obtain/clarify requirements
4. Use a closed ended question to interact effectively and confirm requirementses
5. Seek clarifications and respond appropriately through the use of questioning and active listening techniques
Your Assessor will complete the following checklist whilst you discuss the document design requirements.

Roleplay #1 - meeting with business owner

ACTIVITY 1 continued
1) What are the specific layout and/or style requirements relevant to the design of the Customer Details form template as discussed and clarified with the business owner (your Assessor)?
2) What filename will you use for your first draft of the Customer Details form?
3) Where will you save the file electronically?
Part A Activity 2
You are to design the template for the Customer Details form. Customers will be asked to complete this form so that the business can prepare and maintain a database of its customers for promotional purposes.
The data to be captured must include at a minimum-
• Name
• Mobile No
• Home Suburb and postcode
• Email address
• Preferred drink*
* Each customer is to be asked to nominate their preferred alcoholic beverage on the form by choosing one of the 20 alcoholic beverages which Best Bite Café offer in their drinks' menu.
The drinks' menu is provided as
• Handout - Best Bite Beverages Menu
1) Nominate below what software application will you use to design the Customers Details form template.
2) Nominate below which of three (3) of the basic design principles you will incorporate as you design the Customers Details form template
3) Proof read the Beverages Menu following and list below any errors and layout issues. Note that you must not carry forward any errors into the documents you design.
4) Complete the first draft of your Customers Details form template, and print it and, after you are satisfied that it meets style and layout requirements, save it electronically as a draft template

Part A Activity 3
1) Canvas and reach agreement and consensus with 3 other students and your Assessor on a time to attend a meeting where you will present and test your Customer Details form. Best Bite Café do have a Meeting Policy or Procedure and templates for Agendas and Meeting Minutes, which are provided as-
• Handout - Best Bits Café Meeting Policy and Procedure
• Handout - Best Bite Café Agenda template (also provided below if you prefer to complete in this assessment book)
• Handout - Best Bite Meeting Minutes template (also provided below if you prefer to complete in this assessment book)
Note: You must follow the relevant policy/procedures and use the templates for this activity.
2) Prepare the following meeting documents for distribution to organise the meeting. The Notice of Meeting must be prepared electronically as an email.-
• A Notice of the meeting which-
o indicates whether it is a formal or informal meeting
o advises the purpose of the meeting
o Confirms the time and venue
o Advises invitees of the required outcomes of the meeting
o Advises the participants of the requirements of them
The Notice must be prepared electronically as an email
• An Agenda which includes in the agenda items-
o Welcome/ Purpose/Meeting papers
o Completing the forms/Feedback/Recommendations
The Agenda may be prepared by hand using the template in this booklet or electronically using the Best Bite Café Agenda template handout
• Meeting papers which include-
o Best Bite Café Style Guide
o Draft Customer Details form
» Addressed to three (3) other students as customers
» Addressed to Assessor as business owner
» Confirms a date and a time of the meeting
» Advises venue as Meeting Room B
» Advises it is a formal meeting
» Advises the required outcomes include
o Successful trialling of a new document
o Conclusions on appropriateness of layout
o Conclusion's ion efficiency of data capture
o Recommended modifications to document if any)
o of the meeting
» Advises the participants that they will be of the required to -
o attempt to complete the forms
o provide feedback
o offer recommendations if required
» Agenda attached which includes-
» Welcome
» Date, venue, Chair, Minute the same as in the Meeting Notice
» Purpose of meeting
» Meeting Papers attached which include-

Part A Activity 3 continued
3) How many days before the meeting must you send the Notice of meeting?
4) What must you send with the Notice of Meeting?
5) How many days after receipt of the Meeting Notice must invitees conform attendance or advise inability to attend?

Part A Activity 4 - Role play
You are now to Chair the meeting at the allocated time as agreed with your Assessor and the other students attending the role play. You are also to take the Minutes of the meeting.
Your Assessor will observe you whilst participating in this role play.

1) What is the time limit to ask for approval of the minutes?
2) What is the time limit to dispatch the copies of the minutes?
3) Make any amendments to your Customer Details form if any identified in steps 4 and 6 above to ensure the form meets all of the task requirements.

1) What is the time limit to ask for approval of the minutes?
2) What is the time limit to dispatch the copies of the minutes?
3) Make any amendments to your Customer Details form if any identified in steps 4 and 6 above to ensure the form meets all of the task requirements.
Part A Activity 5
You are to design the internal Weekly Beverages Sales form to record the weekly sales of the alcoholic beverages. This form will be used by staff to manually record the quantities of sales of each stock item each day for every week to enter into an inventory management system.
Relevant details of the stock items are provided as:
• Handout - Best Bite Café Inventory Items Report
The sales quantities will be recorded manually on the form you design so that the sales quantities can be efficiently entered into an inventory management system.
The form may also be integrated electronically as part of the input process for the inventory management system you will develop in Part B of this assessment.

• Nominate below what software application will you use to design the Weekly Beverages Sales form. You must not use the same software application that you used in Activity 2.
• Nominate below which two (2) of the three (3) basic design principles you will incorporate as you design the Weekly Beverages Sales form.
• What are the specific layout and/or style requirements relevant to the design of the Weekly Beverages Sales form as discussed and clarified with the business owner (your Assessor)?

PART B - DESIGN AND USE A SPREADSHEET FOR THE INVENTORY MANAGEMENT SYSTEM

You will be observed by your Assessor whilst undertaking Activities 1 to 6 of this Part B of the Assessment to ensure that you-
• use appropriate strategies to organise your work tasks and energy and resource conservation techniques, and
• that you follow organisational and safe work practices including ergonomic requirements.

STUDENT PART B TASK INSTRUCTIONS
You are to design an Inventory Management System incorporating linked spreadsheets to track and control the alcoholic beverages inventory and sales.

The Inventory Management System must:
» contain at least the following four (4) linked spreadsheets-
o Inventory
o Weekly Sales Qty
o Weekly Sales $
o Orders and Deliveries Received
» be able to receive input of weekly sales data from the Weekly Beverages Sales form you developed in Part A of this assessment
» be able to receive input of deliveries received data
» be able to calculate, using formulae with relative and/or absolute cell references, by item and category the relevant data to manage the alcoholic beverages inventory including:
• weekly opening stock quantities and amounts
• weekly sales $ amounts by input of sale quantities
• weekly closing stock quantities and amounts
• required purchases needed ( top up qty) based on fixed minimum stock levels, and
» include macros to protect cells with formulae
» be able to create graphs with labels and titles from the data in the spreadsheet
» conform with any relevant requirements contained in the Best Bite Style Guide.
For the Inventory Management System, you must use an appropriate software application. You may use or modify any existing templates available from an appropriate software application, or you may choose to design your own templates.
You must adhere to organisational requirements ensuring consistency of style, design and layout, as well naming and storage conventions.
The Inventory Management System reports that will be required as evidence of the output from the linked spreadsheets include:
1. Sales Qty by Week printed report by Category and by Inventory Item Number showing:
• Description
• Unit Selling Price
• Sales Qty for week 1
• Sales Qty for week 2
2. Sales Values by Week printed report by Category and by Inventory Item Number showing:
• Description
• Unit Selling Price
• Sales Qty for week 1
• Sales Qty for week 2
3. Printed pie charts illustrating
a. the break- up of alcoholic beverages sales by wine category (Sparkling Wine, Red Wine, White Wine)
b. the break- up of alcoholic beverages sales by total wines and total beers
4. Detailed Inventory Management System printed report by Category and by Inventory Item Number with corresponding item description detailing:
• Opening Stock Qty Week 1
• Week 1 Opening Inventory Value
• Week 1 Sales Qty
• Week 1 Stock Received
• Opening Stock Qty Week 2
• Week 2 Sales Qty
• Week 2 Stock Received
• Closing Stock Qty Week 2
• Week 2 Closing Inventory Value
• Minimum Stock (qty) Level
• Need to Purchase (Top up) Qty
If you encounter problems or difficulties in designing your spreadsheet, refer to user manuals and user documentation and/or online help to resolve them yourself rather than seeking your Trainer or Assessor's assistance.

Part B Activity 1
1) Research practices for inventory management systems for small businesses and analyse the requirements of the inventory management system described above to determine the specifications for the Inventory Management System spreadsheets you are going to develop. List below the key specifications for:
• values to be maintained
• data inputs
• output from calculations
• reporting
• presentation.
1) Nominate below what software application you will use to design the Inventory Management System spreadsheets.
2) What are the specific layout and/or style requirements relevant to the Inventory Management spreadsheets?
3) Develop your Inventory Management System and before entering any data and save it electronically as a draft copy.
4) What filename did you use for your first draft of the Inventory Management System spreadsheet?
5) Where did you save the file?

Part B Activity 2
1) The table below has been produced by inputting data from the completed Weekly Beverages Sales form you developed in Part A.

Part B Activity 3
1) Evaluate the data entry tasks in Activity 2 above and identify at least two (2) tasks that could be automated to improve accuracy and efficiency.
2) Modify your Inventory Management spreadsheets to incorporate automation of the task you identified above.
3) Use the "page set-up' criteria to prepare the spreadsheets and then preview and produce and print the linked spreadsheet which details:
• Weekly Sales Qty by Category and by Inventory Item Number and Description listing for each Week with:
• Qty for each week for each item
• Qty for each week for each wine category (Total Sparkling Wine, Total Red Wine, Total White Wine)
• Qty for each week for Total Wines and for Total Beers
• Qty for each week for Total Alcoholic Beverages
On the soft copy version of your spreadsheets, highlight in yellow the cell where you source each of the following response before you print the relevant report.
4) From your Weekly Sales Qty linked spreadsheets, what is the qty value calculated for total sales for Total Alcoholic Beverages for Week 1?
5) From your Weekly Sales Qty linked spreadsheets, what is the qty value calculated for total sales for Week 2?
6) From your Weekly Sales Qty linked spreadsheets, what is the qty value calculated for Total Wines for Week 1?
7) What is the spreadsheet formulae you used to calculate the qty value calculated for Total Wines for Week 1?
8) From your Weekly Sales Qty linked spreadsheets, what is the qty value calculated for Total Beers for Week 1?
9) What is the spreadsheet formulae you used to calculate the qty value calculated for Total Beers for Week 1?
10) What is the value shown on your linked spreadsheet for the sales qty for week 1 for House Sparkling Wine?
11) What is the spreadsheet formulae you used to calculate the sales qty for week 1 for House Sparkling Wine?

Part B Activity 4
1) Use the "page set-up' criteria to prepare the spreadsheets and then preview and produce the linked spreadsheet:
• Sales

s by Week printed report by Category and by Inventory Item Number showing:
• Description
• Unit Selling Price
• Total Sales

On the soft copy version of your spreadsheets, highlight in yellow the cell where you source each of the following responses before you print the relevant report.

2) From your Inventory linked spreadsheets, what is the value calculated for total sales for Week 1?
3) From your Inventory linked spreadsheets, what is the value calculated for total sales for Week 2?
4) What is the value shown on your linked spreadsheet for the sales

5) What is the spreadsheet formulae you used to calculate the sales

s for week 1 for House Sparkling Wine?
6) What is the spreadsheet formulae you used to calculate the sales

8) In your Weekly Sales spreadsheet, prepare a pie chart to illustrate and compare the Week 1 sales dollar values for:
• Total Sparkling Wine
• Total Red Wine
• Total White Wine, and
• Total Beers
9) What is the value shown on your pie chart for Total Sparkling Wine for Week 1?
10) What is the value shown on your pie chart for Total Red Wine for Week 1?
11) What is the value shown on your pie chart for Total White Wine for week 1?
12) What is the value shown on your pie chart for Total Beers for Week 1?
13) Print the pie chart for Week 1 to include with your submission.
14) In your Weekly Sales spreadsheet, prepare a second (2nd) pie chart to illustrate and compare the Week 2 sales for:
• Total Sparkling Wine
• Total Red Wine
• Total White Wine, and
• Total Beers
15) What is the value shown on your pie chart for Total Sparkling Wine for Week 2?
16) What is the value shown on your pie chart for Total Red Wine for Week 2?
17) What is the value shown on your pie chart for Total White Wine for week 2?
18) What is the value shown on your pie chart for Total Beers for Week 2?
19) Print the pie chart for Week 2 to include with your submission.

Part B Activity 5
1) Use the "page set-up' criteria to prepare the spreadsheets and then preview and produce the Detailed Inventory Management System printed report by Category and by Inventory Item Number showing:
• Description
• Opening Stock Qty Week 1
• Week 1 Opening Inventory Value
• Week 1 Sales Qty
• Week 1 Stock Received
• Opening Stock Qty Week 2
• Week 2 Sales Qty
• Week 2 Stock Received
• Closing Stock Qty Week 2
• Week 2 Closing Inventory Value
• Minimum Stock (qty) Level
• Need to Purchase (Top up) Qty

On the soft copy version of your spreadsheets, highlight in yellow the cell where you source each of the following response for 5) to & 7) following before you print the relevant reports.
2) From your Inventory linked spreadsheets, what is the value calculated for the Opening Inventory for Week 1?
3) From your Inventory linked spreadsheets, what is the value calculated for the Opening Inventory for Week 2?
4) From your Inventory linked spreadsheets, what is the value calculated for the Closing Inventory for Total beverages for Week 2?
5) List below the order top up quantities "needed to purchase" at the end of Week 2 for each inventory item as calculated by your linked spreadsheets. You do not need to list items with zero values.
6) What is the spreadsheet formulae you used to calculate the needed to purchase qty value calculated for each Tsingtao?
7) Present electronic copies of your report and graphs to your Assessor (who is role playing as the business owner) for feedback and make any changes requested before you finalise your assessment submission.
8) Submit a hard copy of your report to confirm the above calculation results.
Part B Activity 6
1) Save and close your spreadsheets as a new workbook as the "final" version in the appropriate location and format and file-naming conventions.