In Partnership with…
AiTC Advanced Institute of Training & Consultancy aspire to be the leading provider of Training and Consulting to the Business, Resources & Infrastructure Industries. This is to be achieved through close industry connection and networks, continuous improvement, integrity and teamwork of all working within AiTC.
AiTC’s mission is to help our learners to grow personally and professionally and to increase their opportunities in life. AiTC will achieve this through the quality of our service, trainers and assessors with the highest level of industry knowledge, quality and industry specific resources.
Version Control
Version Number / Date Released / Author / Notes / Approval by / Review Date / Reviewed ByV1-14 / 3 July 14 / AiTC / Needed Approval / SCC / Dec 2014
Table of Contents
Elements and Performance Criteria 4
Instructions 5
Assessment 1 – Fizzy Drinks 5
Assessment 2 – Stationary for March 6
Assessment 3 – Reticulation cost calculator 7
Assessment 4 – Yearly budget for purchasing a car 8
Assessment 5 – Cash Flow Calculator 9
Assessment 6 – Tax Invoice 10
Assessment #7 – “Cars R Us” 11
Assessment #8 – “Accident Statistics” 12
Assessment 9 – Student Marks 13
Assessment 10- – Car Buyers Guide 14
Assessment 11- – Stock take 14
Assessment 12- – Pay Rates 15
Assessment 13- – Dropdown controls 16
Assessment 14- – Tornado 17
Elements and Performance Criteria
1. Select and prepare resources
1.1. Adhere to ergonomic, work organisation and occupational health and safety requirements
1.2. Use energy and resource conservation techniques to minimise wastage
1.3. Identify spreadsheet task requirements in relation to data entry, storage, output and presentation
2. Plan spreadsheet design
2.1. Ensure spreadsheet design suits the purpose, audience and information requirements of the task
2.2. Ensure spreadsheet design enhances readability and appearance, and meets organisational and task requirements for style and layout
2.3. Use style sheets and automatic functions to ensure consistency of design and layout
3. Create spreadsheet
3.1. Ensure data is entered, checked and amended to maintain consistency of design and layout, in accordance with organisational and task requirements
3.2. Format spreadsheet using software functions to adjust page and cell layout to meet information requirements, in accordance with organisational style and presentation requirements
3.3. Ensure formulae are tested and used to confirm output meets task requirements, in consultation with appropriate personnel as required
3.4. Use manuals, user documentation and online help to overcome problems with spreadsheet design and production
4. Produce simple charts
4.1. Select chart type and design that enables valid representation of numerical data, and meets organisational and task requirements
4.2. Create charts using appropriate data range in the spreadsheet
4.3. Modify chart type and layout using formatting features
5. Finalise spreadsheets
5.1. Preview, adjust and print spreadsheet and any accompanying charts, in accordance with task requirements
5.2. Ensure data input meets designated time lines and organisational requirements for speed and accuracy
5.3. Name and store spreadsheet in accordance with organisational requirements and exit the application without data loss/damage
Instructions
Create all the following spreadsheets, saving them in separate worksheets named ‘BSBITU304A – Assessments 1-14’ within the one file. Formulas MUST be used wherever possible.
Assessment 1 – Fizzy Drinks
Create a spreadsheet using the following information. You have been asked to prepare a spreadsheet using the results of a survey on the types of fizzy drinks people prefer. 3000 people where asked what their favorite fizzy drink is, and what their next preference would be if their favorite was not available.
1) / Enter the raw data below, applying as many presentation Features (Font, Font Size, Font Colour, Number Formats and Colour, Cell Shading, Text Rotation, etc) to it as you wish.2) / Apply appropriate number formats to your numbers.
3) / Centre your spreadsheet horizontally on the page
4) / Give your spreadsheet an appropriate title and centre it across your spreadsheet.
5) / Select the best page orientation for your spreadsheet.
6) / Adjust the column width and row height to suit the layout you have selected.
7) / Create formula's to calculate the percentage of the total number of people surveyed that preferred a specific fizzy drink as their first preference.
8) / Create formula's to calculate the percentage of the total number of people surveyed that preferred a specific fizzy drink as their second preference.
9) / Create a suitable chart showing the 1st and 2nd preferences data (not %). Chart must include overall title, legend and X and Y axis labels.
10) / Setup a page number for this spreadsheet and place it in the footer.
Drink / 1st Preference / 2nd Preference
Pepsi / 342 / 403
Coca Cola / 359 / 367
7Up / 238 / 290
Lift / 215 / 190
Mountain Dew / 321 / 311
Fanta / 103 / 80
Ginger Beer / 180 / 380
Leed Lemonade / 217 / 120
Lemonade / 215 / 105
L&P / 425 / 414
Vanilla Coke / 203 / 67
Don't Knows / 182 / 273
Assessment 2 – Stationary for March
Create a spreadsheet using the following information. You have been tasked with creating a spreadsheet to generate and stationery order for the month of March.
1) / Enter the raw data below, applying as many presentation Features (Font, Font Size, Font Colour, Number Formats and Colour, Cell Shading, Text Rotation, etc) to it as you wish.2) / Apply appropriate number formats to your numbers.
3) / Center your spreadsheet horizontally on the page
4) / Give your spreadsheet an appropriate title and centre it across your spreadsheet.
5) / Select the best page orientation for your spreadsheet.
6) / Adjust the column width and row height to suit the layout you have selected.
7) / Create formula's to calculate totals for each stationery item.
8) / Create a formula to calculate the order total for the month.
9) / Create a formula to calculate the G.S.T component of the order. Note all prices include G.S.T, and the value of G.S.T is 12.5% or divide by 9.
10) / Create a formula to calculate the net total (pre G.S.T total) of the order for the month.
11) / Setup an appropriate header for this spreadsheet.
12) / Setup a page number for this spreadsheet and place it in the footer.
13) / Create a suitable chart showing the Items and Quantity.
Description / Quantity / Cost per item
Note Pad / 202 / 2.85
Highlighter Pen / 280 / 2.95
Ball Point Pen Blue (pkt) / 520 / 2.50
Ball Point Pen Red (pkt) / 340 / 2.50
Ball Point Pen Green (pkt) / 250 / 2.50
Exercise Book 1B / 35 / 4.95
Cello-tape / 75 / 2.95
Manila Folders / 100 / 2.95
A4 Refill Pad / 90 / 5.95
Writing Pad / 86 / 3.95
Pencil Sharpeners / 110 / 0.95
Pencils / 603 / 0.5
Colour Pencils (pkt) / 50 / 2.95
Felt Pens (pkt) / 40 / 3.95
Staples (pkt) / 75 / 3.50
Stapler / 30 / 9.95
Hole Punch / 25 / 14.95
Ring Binder / 45 / 10.95
Assessment 3 – Reticulation cost calculator
Create the spreadsheet below, creating the necessary formulas that will allow you to automatically calculate the cost of your reticulation equipment requirements. A formula is needed for the highlighted cells.
Reticulation Cost CalculatorItem / Type / Item Cost / Number / Sub Total
Sprinkler / Full / $1.95 / 4 / $0.00
Three quarter / $1.50 / 6 / $0.00
Half / $1.50 / 8 / $0.00
One quarter / $1.50 / 9 / $0.00
PVC Pipe / Straight (6m) / $1.15 / 3 / $0.00
T joint / $0.81 / 34 / $0.00
T joint (spr.) / $0.90 / 22 / $0.00
90° elbow / $0.50 / 23 / $0.00
45° elbow / $1.15 / 24 / $0.00
Riser / $0.65 / 78 / $0.00
Valve joiner / $0.85 / 54 / $0.00
Glue / Tin / $3.50 / 4 / $0.00
Valve / Solenoid / $31.00 / 3 / $0.00
Manual / $3.50 / 7 / $0.00
Station Timer / 6 Station / $130.00 / 2 / $0.00
8 Station / $150.00 / 1 / $0.00
12 Station / $190.00 / 1 / $0.00
Wire / Electric (per m) / $0.75 / 240 / $0.00
TOTAL = / $0.00
Assessment 4 – Yearly budget for purchasing a car
Create the spreadsheet below, creating the necessary formulas that will allow you to automatically calculate:
1. The petrol cost if either your mileage, fuel economy of petrol price changes.
2. The total annual costs for your vehicle.
3. Insert a suitable graphic.
YEARLY BUDGET FOR PURCHASING A CARTotals
COSTS / Standing / Repayments for Year / $2,455.00
Insert suitable graphic here / Licence / $456.00
Stamp Duty / $789.00
Insurance / $1,234.00
TOTAL / $XXX
Running / Petrol Costs / $AAA
/ Service & Maintenance / $1,500.00
TOTAL / $YYY
TOTAL COSTS (X + Y) / $ZZZ
Petrol Calculator
Kilometres = / 10,000 / Cost / Km / $0.00
litres/100km = / 8.00
$ per litre = / 1.35
Petrol Costs / $AAA
Page 11 of 19
Assessment 5 – Cash Flow Calculator
1. Create a suitable spreadsheet, including all formulas to replace the X’s below, to calculate the Cash flow for ABC Corporation.
2. Format the chart to a suitable business quality standard.
3. Create a line chart showing the total income, total expenses and cash surplus as separate lines on the one chart.
ABC Corporation – Cash Flow Forecast and Record for 2011July / Aug / Sept / Oct / Nov / Dec / Jan / Feb / Mar / Apr / May / June
INCOME($) / Sales / 9000 / 12100 / 9000 / 8300 / 11000 / 9000 / 9000 / 6000 / 9000 / 9000 / 2000 / 9000
Interest / 200 / 200 / 200 / 200 / 200 / 200 / 200 / 200 / 200 / 200 / 200 / 200
TOTAL INCOME / X / X / X / X / X / X / X / X / X / X / X / X
EXPENSES($)
Fixed / Equipment / 2000 / 2000 / 2000 / 2000 / 2000 / 2000 / 2000 / 2000 / 2000 / 2000 / 2000 / 2000
Repay Loans / 380 / 380 / 380 / 380 / 380 / 380 / 380 / 380 / 380 / 380 / 380 / 380
Other / 100 / 100 / 100
Variable / Labour / 4700 / 6700 / 4700 / 4700 / 6700 / 4700 / 4700 / 2300 / 4700 / 2100 / 4700 / 4700
Materials / 1280 / 4000 / 1280 / 1280 / 1280 / 1280 / 1280 / 430 / 1280 / 390 / 1280 / 1280
Other / 2140 / 1300
TOTAL EXPENSES / X / X / X / X / X / X / X / X / X / X / X / X
GROSS PROFIT / X / X / X / X / X / X / X / X / X / X / X / X
CASH SURPLUS / X / X / X / X / X / X / X / X / X / X / X / X
Page 11 of 19
Assessment 6 – Tax Invoice
Re-create the following invoice using MS Excel. Use a whole A4 page to create it. Ensure that you use formulas to calculate the ‘Total Amount Payable’ and the value for GST (formula for GST value is Total Amount Payable divided by 11).
Assessment #7 – “Cars R Us”
Download the CARS R US spreadsheet from the Learning Resources section, on the BSBITU304A page on the Cert 3 Business website.
You are required to use a range of Excel functions to format the spreadsheet such that it has the following functions:
1. The first worksheet (called Assessment 7 – Cover) is to be a cover page with the company logo, Mission statement as well as a spot for the employee to put their name
2. The next worksheet (called Assessment 7 – Sales report A) is to consist of all the sales data from the CARS R US worksheets #1, 2 and 3 combined together.
3. On that worksheet, perform the following calculations
a. Calculate the ($) amount of sales per month. Save as sheet ‘Assessment 7 – Sales report A’
b. Calculate the number of each car sold. Save as Assessment 7 – Sales report B’.
c. Calculate the total value of money spent by a particular client. Save as Assessment 7 – Sales report C’.
d. The employee gets 5% commission on sales each month. The client would like a table detailing how much commission is being paid each month. Save as Assessment 7 – Sales report D’.
e. Insert a suitable chart for items A-D.
Assessment #8 – “Accident Statistics”
Create the spread sheet below in Excel. It is the data for the number of pedestrians that were killed in the United States during 1994 in motor vehicle crashes. Then perform the following procedures in Excel.
Pedestrians Killed by Time of Day and Day of Week