BSBTEC302 & BSBTEC402
4719955-23812500Assessment
BSBTEC302 & BSBTEC402
Design and produce Spreadsheets &
Design and produce Complex Spreadsheets
Assessment Instructions for the Candidate
Requirement:
Access to a computer loaded with Microsoft Word and Excel.
Access to the Internet - Student Portal.
industry software packages/applications for producing spreadsheets and user instructions
digital device user information
relevant legislation and codes of practice
relevant organisational policies and procedures
workplace documentation and resources, including style guide.
Calculator.
It is recommended that on completion of this unit, participants should be able to produce accurate work with not more than two re-submissions.
On completion
Upload your assessment document to the online platform.
Ensure the files you submit are clearly named following all task requirements.
Reasonable Adjustment
If you are unable to complete the assessment as designed, please raise a support ticket to discuss with your Assessor.
Assessment Outcomes
There are only two possible results of any assessment:
Competent
Not yet competent
Competent
An assessment of Competent recognises that you have clearly demonstrated to your assessor that you have current skills and knowledge as outlined in the units of competency for which application was made.
Not Yet Competent
If an assessment of Not Yet Competent is made, the assessor will give you specific feedback as to the reasons for the result. This will also provide information about gaps found in the assessment of competency. You may also discuss options to address the gaps in competency with your assessor.Student Name
Tracey Holwell
Date of Assessment
05-Nov-23
Part A
Prepare to Develop Spreadsheets
Question 1
Answer the following questions
Summarise what each of the following Excel functions do:
COUNT:
Click here to enter text.PMT:
Click here to enter text.IF:
Click here to enter text.NOW:
Click here to enter text.Select which of the following four (4) file names are not valid for Excel spreadsheets:
Type Yes if a Valid file name, or No if Not a valid file name.
Saes.xlsx
Click here to enter text.Sales?.xlsx
Click here to enter text.First/Qtr.xlsx
Click here to enter text.Sales*.xls
Click here to enter text.Sales_Fst_Qtr_14.xlsx
Click here to enter text.Sales14.xlsx
Click here to enter text.List three (3) different methods you can use to save your work when closing a file
1
Click here to enter text.2
Click here to enter text.3
Click here to enter text.Describe how the formatting and design of a spreadsheet can affect the presentation and readability of data. Consider the appearance, structure and layout of the spreadsheet and how this may impact the user of the spreadsheet.
Click here to enter text.Provide an example of a formatting or design option that improves the presentation or readability of the spreadsheet for the user
Click here to enter text.
Question 2
Download the OfficeWise document, and/or speak to your workplace WHS Officer about policies in your workplace, then answer the following questions:
List five (5) ways that you can improve the overall office layout, to increase work effectiveness and decrease hazards.
1
Click here to enter text.2
Click here to enter text.3
Click here to enter text.
4
Click here to enter text.5
Click here to enter text.List two (2) substances or situations that can cause health issues in the office environment
1
Click here to enter text.2
Click here to enter text.
Question 3
Download the Office Awareness PDF document, review and answer the following questions:
List three (3) practices to take into consideration for conserving resources in the workplace
1
Click here to enter text.
2
Click here to enter text.3
Click here to enter text.Briefly explain the requirement for rest breaks within a workplace. Consider the requirements from an employers and employees point of view.
Click here to enter text.
Question 4
Download the Policies & Procedures Extract Spreadsheet Requirements document and review the information provided. List at least one organisational requirement to address each of the following: Data Entry, Storage, Creation and Output, and Presentation requirements
1 Data Entry Requirements
Click here to enter text.2 Storage Requirements
Click here to enter text.
3 Creation and Output Requirements
Click here to enter text.4 Presentation Requirements
Click here to enter text.Question 5
Search Excel help for validation. Explain why you might Apply data validation to cells.
Click here to enter text.
Question 6
From the following list of features, select those that are cloud-based spreadsheet software, PC-based spreadsheet software, or can be found in both.
Type Cloud, PC, or Both for each feature listed.
Feature of Spreadsheet software Cloud based, PC based, or both
Font Formatting
Click here to enter text.Insert Charts
Click here to enter text.Create Macro
Click here to enter text.Real-time Sharing and Editing (Co-authoring)
Click here to enter text.Use Functions
Click here to enter text.Customise Ribbon
Click here to enter text.
Part B
Create and Use Spreadsheets
To complete the following tasks you will require access to Microsoft Excel.
Refer to your Learner Guide and Excel help when required.
Ensure you consider ergonomics, WHS policies and procedures and conservation techniques when completing this assessment.
TASK 1 - Create an Excel Template
Create a new Excel workbook
Name the worksheets as follows:
Sheet 1 Sales
Sheet 2 Expenses
Sheet 3 Totals
Enter the following column and row headings:
Sales sheet
Name Jan Feb Mar
Betty Craig John Expenses sheet
Account Jan Feb Mar
Wages Rent Telephone Insurance Office supplies
Totals sheet
Account Jan Feb Mar
Income
Expenses
Net Profit/Loss
Add a total row (5) and a total column (E) to the Sales sheet
Add a total row (7) and a total column (E) to the Expenses sheet
Add a total column (E) to the Totals sheet
0n the Sales and Expenses sheets, use the Sum function to create totals for each column and row.
On the Totals sheet, use the Sum function to create totals for the Income and Expenses rows.
Insert a relevant title at the top of each sheet in row 1 that spans 6 columns and is centre aligned. Format the font size to 18 and Bold.
Save your file as an Excel Template file named Sales Template and close the file.
Review Task:
Ensure your Excel file has an extension of .xltx
If your file does not have this .xltx extension then you have not saved it as an Excel Template and will need to repeat step 10 above
SUBMISSION ACTIVITY
Document 1 Sales Template.xltx
TASK 2 Using your Template File
Open your template file Sales Template.xltx
Save the file as an Excel Workbook named Sales Quarter 1
Enter the following data
Sales sheet
Name Jan Feb Mar
Betty 1400 850 3000
Craig 700 77 -45
John 390 900 22
Expenses sheet
Account Jan Feb Mar
Wages 900 890 950
Rent 200 200 200
Telephone 55 55 55
Insurance 77 500 77
Office supplies 22 60 90
0n the Totals sheet, enter a formula in the Net Profit/Loss row (5), that will calculate Income less Expenses for each month and the Total.
On the Totals sheet, insert formulas linked to the monthly totals from the Sales and Expenses sheets.
On the Totals sheet, add a new row below your net profit on the Totals sheet and insert an IF statement for each month that shows the words Net Profit if income is greater than expenses, otherwise show the words Net Loss
In Column F of the Sales and Expenses sheets, calculate the value of each income and expense row against the combined total. You must use absolute cell referencing in your formula.
Use cell formatting to format the % columns as a Percentage with 1 decimal place.
Format all cells on all sheets that have dollar values, to display as follows:
With a $ symbol.
To 0 decimal places.
Negative values in red with a negative sign or brackets.
Insert a chart on a separate sheet showing Total Sales for each Employee. Ensure you:
Select a chart type appropriate for the data.
Label all axis and
Label all data series.
Include an appropriate chart title and legend.
Name the sheet Sales Chart
Create another chart on a separate sheet showing Net profit for each month. Ensure you:
Select a chart type appropriate for the data.
Label all axis.
Label all data series.
Include an appropriate chart title and legend.
Name the sheet Profit Chart
Print your charts to PDF files named Sales Chart and Profit Chart. Include both pdf files in your submission.
Review Task:
Ensure your Excel file has an extension of .xlsx
SUBMISSION ACTIVITY
Document 2 Sales Quarter 1.xlsx
Document 3 Sales Chart.pdf
Document 4 Profit Chart.pdf
TASK 3 Review Your Spreadsheet
Review the tables in the Sales Quarter 1 file created in Task 2. Explain the automations you have applied, and how they have increased efficiency.
Include the Excel features, functions, shortcuts or timesavers you have used in the spreadsheet.
According to the Policies and Procedures extract, you are required to ensure the formulas in your spreadsheet are correct. Provide one or more examples of techniques you used to check that your formulas were correct (such as testing if the formula refers to the correct cells on the sheet or whether it is producing an error).
Include any logic or reasoning you applied in your testing and indicate specific features of Excel you used.
Note: The Policy & Procedures extract can be downloaded from your assessment Submission page, where you downloaded this assessment document, and must be referred to when required.
Click here to enter text.
TASK 4 Importing
Using the Get External Data from Text option in Excel, import the file Text File.txt into a new Excel worksheet.
Do not apply Table formatting.
Note: The Text File required for this task must be downloaded from your assessment Submission page, where you downloaded this assessment document.
Save as an Excel workbook called Home
Enter the following information into the spreadsheet
Home Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Pay 385 385 385 400 400 400 400 400 400 400 400 430
Other Income 0 0 0 0 0 0 0 0 0 0 0 0
Total Income
Rent 80 80 80 80 80 80 80 90 90 90 90 90
Holidays 0 0 0 50 0 0 0 210 0 0 0 0
Leisure 75 55 48 75 40 48 56 180 30 35 60 150
Electricity 49 0 0 43 0 0 29 0 0 31 0 0
Gas 46 0 0 51 0 0 32 0 0 35 0 0
Telephone 0 37 0 0 35 0 0 36 0 0 43 0
Car 0 0 91 0 0 13 0 0 74 27 0 0
Petrol 20 25 25 20 25 20 20 50 25 25 20 30
Food 60 60 60 75 60 55 50 80 55 60 70 90
Other Expenses 55 45 100 65 75 150 100 75 45 34 60 100
Total Expenses
Using the Sum function, enter a formula that will calculate the Total Income and Total Expenses for each month.
Format the data as follows:
Ensure text is Arial font size 10
Total Income and Total Expenses rows should have a top and bottom border
Format the row labels (category titles) running down the left side of the sheet to be bold
Format the rest of the labels to be Italic
Format all numeric cells to Currency and 0 decimal places
Rename the worksheet Sheet 1 as Home.
Enter a formula that will calculate the Annual Total Income in cell N4 and the Annual Total Expenses in cell N15.
Enter a formula that will calculate the Surplus/ (Deficit) amount in cell for B17 to N17. Check that your formula is correct for each month, as well as for the Total Year
Save your file
Review Task:
Ensure your Excel file has an extension of .xlsx
SUBMISSION ACTIVITY
Document 5 Home.xlsx
TASK 5 Macros
Continue using the Excel workbook Home from Task 4
Start recording a macro in this workbook and name the macro Print Settings
Print preview the Home worksheet
Adjust the Scaling so that when the worksheet is printed it fits entirely on 1 page
Stop recording and save the workbook, ensuring it is saved as a Macro-Enabled workbook.
Take a print screen (PrtScn) of the macro code ensuring this shows the orientation setting and paste as a picture into Sheet 2 of the Home workbook
Rename Sheet 2 as MacroCode
Edit the macro so the Orientation is equal to Landscape.
Save your macro-enabled file
Review Task:
Ensure your Excel file has an extension of .xlsm
If your file does not have this .xlsm extension then you have not saved it as Excel Macro-Enabled and will need to repeat step 5 above
SUBMISSION ACTIVITY
Document 6 Home.xlsm
TASK 6 Create a Calculations Worksheet
Create a new Excel workbook called Calculations
Enter the following data in two adjacent columns in your Calculations worksheet. You must enter the column headings in Row 1.
ITEM AMOUNT
Pay $4,785
Other Income $200
Total Income $4,985
Rent $1,010
Electricity $152
Gas $164
Telephone $151
Other Expenses $904
Total Expenses $2,381
Total Savings $2,604
Leaving one (1) empty column to the right of the Amount column, create a formula that will divide each value by 12, to provide the average monthly amount for each item.
Leaving three (3) empty Rows below Total Savings, enter the following Data:
1) 35 2
2) 100 26
3) 49 4
4) 26 75
Leaving one (1) empty column to the right of the data, create a formula that will multiply the two figures on each Row to provide the result.(For example, 1) is 35 multiplied by 2)
In cell B22, use the Count function to count how many cells have data in them, from Cell A2 to F18.
Save your file after completing these tasks.
Review Task:
Ensure your Excel file has an extension of .xlsx
SUBMISSION ACTIVITY
Document 7 Calculations.xlsx
CHECKLIST
Documents to be uploaded
The following documents should be uploaded to the Student Portal for marking.
DOCUMENT DOCUMENT NAME
Assessment Completed Assessment document.
Document 1 Sales Template.xltx
Document 2 Sales Quarter 1.xlsx
Document 3 Sales Chart.pdf
Document 4 Profit Chart.pdf
Document 5 Home.xlsx
Document 6 Home.xlsm
Document 7 Calculations.xlsx
You have now completed this Assessment.
