Assessment Details
Qualification Code/Title
Assessment Type Assessment -02 (Project) Time allowed
Due Date Location AHIC Term / Year
Unit of Competency
National Code/Title BSBITU402 Develop and use Complex spreadsheets
Student Details
Student Name Student ID
Student Declaration: I declare that the work submitted is my own, and has not been copied or plagiarised from any person or source. Signature: ____________________________
Date: _____/______/__________
Assessor Details
Assessor’s Name
RESULTS (Please Circle) SATISFACTORY NOT SATISFACTORY
Feedback to student:
............................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
Student Declaration: I declare that I have been assessed in this unit, and I have been advised of my result. I am also aware of my appeal rights.
Signature: _______________________________
Date: ______/_______/___________
Assessor Declaration: I declare that I have conducted a fair, valid, reliable and flexible assessment with this student, and I have provided appropriate feedback.
Signature: ________________________________________
Date: ______/_______/___________
Instructions to the Candidates
? This assessment is to be completed according to the instructions given below in this document.
? Should you not answer the tasks correctly, you will be given feedback on the results and gaps in knowledge. You will be entitled to one (1) resubmit in showing your competence with this unit.
? If you are not sure about any aspect of this assessment, please ask for clarification from your assessor.
? Please refer to the College re-submission and re-sit policy for more information.
? If you have questions and other concerns that may affect your performance in the Assessment, please inform the assessor immediately.
? Please read the Tasks carefully then complete all Tasks.
? To be deemed competent for this unit you must achieve a satisfactory result with tasks of this Assessment along with a satisfactory result for another Assessment.
? This is an Open book assessment which you will do in your own time but complete in the time designated by your assessor. Remember, that it must be your own work and if you use other sources then you must reference these appropriately.
? Resources required completing the assessment tasks are Learner guide, PowerPoint presentation, Unit Assessment Pack (UAP), Access to other learning materials such as textbooks, Access to a computer, the Internet and word-processing system such as MS Word and Spread Sheet such as MS Excel
? Submitted document must follow the given criteria. Font must be Times New Roman, Font size need to be 12 and line spacing has to be Single line.
? This is Individual Assessments. Once you have completed the assessment, please upload the softcopy of the Assessment into AHIC Moodle.
? Plagiarism is copying someone else’s work and submitting it as your own. Any Plagiarism will result in a mark of Zero.

ASSESSMENT 2 – PROJECT
Mr. John Smith is the owner of JS Spices, a restaurant that sells Meals (both dine in and take away) and Drinks. The restaurant is located at CBD Sydney and its customers are mostly repeat customers. Following are the information provided by the owners. The selling price of Take Away Meal and Drinks is $10 and $2 respectively.
Suppliers:
JS has three major suppliers who supply stationary, food ingredients and drinks. They all have a 30 days’ payment terms. The detail of the supplier as on June 30, 2016 are as follows:
Name Address Contact no Email Purchase amount Due Payment term
Canterbury supplies 231, Canterbury Road, Canterbury, NSW-2193 0287654321 cs@gmail.com $1,000,000.00 $150,000.00 15 days
ABC Groceries 127, Parramatta Road, Homebush, NSW-2140 0281246597 ag@gmail.com $1,250,000.00 $145,000.00 20 days
Crystal Drinks 125, CBD Sydney-2000 0254123654 cd@crystal.com.au $120,000.00 $87,000.00 10 days
Record keeping:
All the records are kept by John in his computer. The computer can also be accessed by Supervisors for business purpose only. Back up of the information is made in an external drive by John on a weekly basis. John also keeps the record manually in the office store for any requirement in the future.
Staffing:
John works full time as the manager in the business. Besides he has 1 supervisor, 1 Chef, 2 Kitchen staffs and 4 waiters/waitresses in the business. The Chef and Supervisor work full time and the other staffs work on a casual role. Following table summarises the detail of the work performed by the staff last week:

Staff ID Name Position Hour worked Pay rate
N00011 John Smith Manager 38 $35.00
N00012 James Pattinson Chef 38 $32.00
N00014 David Nugyen Supervisor 38 $30
N00019 Allan Manly Kitchen staff 26 $25
N00021 Blenda Lee Kitchen staff 21 $25
N00022 Sue Cameron Waitress 19 $27
N00024 Justin Lee Waiter 22 $27
N00026 Julia Taylor Waitress 27 $27
N00049 Nadia Akhter Waitress 32 $27
Note: All the staffs worked for the whole year
Customers:
All the retail customers pay cash. Last year total retail sales stood at $2,500,000.00. However, John has 5 corporate customers as follows who purchase on credit with 15 days credit terms. Following are the details of average weekly credit sales of his customers for the year ended on 30 June 2016.
Customer Address Contact no Purchase quantity Weekly
(Meal) Purchase quantity Weekly
(Drinks)
ABC Traders 121, CBD, Sydnet-2000 0215468974 650 540
Tariq & Sons 285, Castlereagh Street, Sydney CBD-2000 0411256897 540 500
Pacific Foundation 141, Liverpool Street, Sydney CBD-2000 0421569874 450 500
Central Computers 156, Market Street, Sydney CBD-2000 0413265897 920 1000
Johny & Sons 185, Kent Street, Sydney CBD-2000 0412369812 240 200
Based on the above information, answer the following questions. You must upload your Excel file in Moodle and hand out the printed copy of the assessment to your trainer.
1. Using the information provided, identify at least three spreadsheets the business is likely to need and describe their purpose.
2. Provide 3 suitable communication methods to communicate with the relevant personnel.
3. Create a spreadsheet (name: weekly payroll) to record the salary expenses for individual employee as well as total of the business. To do that you must ensure the followings:
a. A new staff naming Scott Hanlon (N00051) has joined the business as a kitchen staff 2 days ago. He worked 11 hours and his pay rate is as of the other kitchen staff. Include his detail in the spreadsheet as well.
b. Add additional columns to calculate Weekly Gross, tax amount, net pay and Annual Gross (Four Column). Visit ATO website with the following link to calculate tax amount:
https://www.ato.gov.au/Calculators-and-tools/Host/?anchor=TWC&anchor=TWC#TWC/questions
c. Make the heading bold and centre justify
d. Place individual lines around each cell and a single bold border around the main heads
e. You must use formula to do the calculation where required.
f. Suggest how the business can keep this record to comply with its policy.
g. Save the file using [your initial JS Spices Payroll ddmmyy]; for example, [ACJS Spices Payroll 241116]
4. Open another spreadsheet in the saved file naming Corporate Customer. Use the information under the heading “Customers” from the scenario to answer the followings:
a. Create a table using the information as given.
b. Create 3 additional columns for Annual Total sales (Meals), Annual Total Sales (Drinks) and annual Total Sales.
c. Create a suitable graph and format chart titled to “Sales – 2016” showing the amount of sales to the Corporate customers for the year.
5. Open the third spreadsheet using the suppliers detail and name it “Suppliers”. Complete the following tasks in the spreadsheet using formulas.
a. Calculate the “Total Purchase” and “Total Due” amount.
b. Calculate the average payable amount of the suppliers.
6. Create the fourth spreadsheet and calculate the Profit & Loss Statement by using the information from the other spreadsheets and the following information:
i. Opening Inventory : $ 50,000.00
ii. Closing inventory : $ 40,000.00
iii. Rent : $ 520,000.00
iv. Other expenses : $ 300,000.00
7. Enter the following student list with their name, Id and Marks achieved in Process Financial
Transactions and extract interim Report unit.
Student Result
Process Financial Transactions and Extract Interim Report
Student Name Student Id Total Marks Out of 100
R. Smith AHIC 100006 50
John Abraham AHIC 100007 69
Pundit Mukherjee AHIC 100008 79
Deon Smith AHIC 100009 67
Shelley AHIC 1000010 89
Rayan AHIC 1000011 74
Loren AHIC 1000012 45
Shown AHIC 1000013 36
Jevon AHIC 1000014 48
To pass in this unit student must have to achieve at least 50% marks that is 50 out of 100. If they get less than 50 he will be recorded as “Fail” and who is getting 50 or above will be recorded as “Pass”.
a. Put this information in a excel worksheet
b. Create Macro to record “Pass” and “fail”
c. Use the Macro Key to create the result of all the students
d. Unfortunately, one of the student’s name is not in the list. How will you edit the work-sheet and Macro to put his Name, ID and Marks in Excel Sheet. Student name is Rasel, Id: AHIC 1000015 and his mark is 64.
e. After finishing your work, write your Name and ID at the header of the worksheet and then write your name at the bottom of the worksheet.
f. Provide screenshots of the “print command” and “page layout” that you will need to print the result. Make sure that you maintain the following requirements.
a) Top and bottom margin 0.5
b) Left and right margin 0.8
c) Landscape layout
--END--