Reference no: EM132393976

ASSESSMENT  – 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	[email protected]	$1,000,000.00	$150,000.00	15 days
ABC Groceries	127, Parramatta Road, Homebush, NSW-2140	0281246597	[email protected]	$1,250,000.00	$145,000.00	20 days
Crystal Drinks	125, CBD Sydney-2000	0254123654	[email protected]	$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:

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
PunditMukherjee	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