Assessment Description-
Learning Outcome: Demonstrate technical knowledge with MYOB and Excel applications
Task 1: Gelato-to-go is a gelato ice cream making business specialising in large tubs of gelato for shops and cafes. The following table shows the expected number of gelato tubs to be sold between April and Jun 2016:
|
Month
|
Strawberries & Cream
|
Choc chip
|
Rum and Raisin
|
|
April
|
3400
|
1500
|
4100
|
|
May
|
6500
|
2500
|
6000
|
|
June
|
4500
|
1800
|
3800
|
The cost to produce each tub of gelato is as follows:
- Strawberries & Cream - $5.70
- Choc Chip - $6.50
- Rum and Raisin - $9.30
The selling price is determined by applying a mark-up percentage on cost as follows:
- Strawberries & Cream gelato is marked up by 60%
- Choc Chip Is marked up by 50%
- RUM and Raisin is marked up by 100%
REQUIRED:
A. Using the data above, prepare a Revenue table for gelato, for each month.
B. Using the data above, prepare a Profit Summary per month, for each gelato flavor (must calculate the profit directly. You CANNOT add another table for total cost).
C. Recalculate the profit for each for each product but this time using a different formula. (Double check - your two profit tables must be exactly the same).
D. Overall presentation i.e. borders, shading, titles and alignment.
Task 2: The enclosed worksheet ACC 202 Task 2 contains some of !he scores of different teams from matches played ill season 2014 at different venues.
Required:
Using suitable formulas (like IF, Gourd) to find and suitably present the following:
A. "Margin" for each round (margin is always a positive number) - insert additional column/s as appropriate.
B. The winning team for each round - Insert additional column/s as appropriate.
C. The losing team for each round - insert additional column/s as appropriate.
D. Identify the team(s) that won maximum number of rounds, overall.
E. Your work need be clearly presented and suitably labelled.
Task 3: The Following employees work for Bright Electricals Ltd, a public company specialising in production of electrical components. The employees are paid an hourly rate, based on their Job Classification level. If an employee works more than 40 hours, they will be paid the overtime rate for the additional hours.
The Following table summarises the classifications and level of pay per classification:
Table A-
|
Level
|
Hourly pay
|
|
1
|
$15
|
|
2
|
$20
|
|
3
|
$25
|
|
4
|
$30
|
|
5
|
$35
|
|
6
|
$45
|
|
7
|
$50
|
|
8
|
$55
|
|
Overtime*
|
$65
|
*The overtime hourly rate is $65 per hour for ALL employees, regardless of their classification level
Bright Lights Ltd has the hollowing employees: Table B
|
Staff name
|
Level
|
Hrs worked
|
|
Simonette
|
5
|
32
|
|
Dat
|
4
|
41
|
|
Jemma
|
4
|
78
|
|
Rosemarie
|
5
|
20
|
|
Jessica
|
2
|
41
|
|
'Nicole
|
8
|
56
|
|
Kellie
|
7
|
30
|
|
Alenna
|
3
|
58
|
|
Mulomaa
|
8
|
47
|
|
Tara
|
3
|
44
|
|
Yifang
|
8
|
22
|
|
Thomas
|
4
|
68
|
|
Suriya
|
7
|
37
70
|
|
Xunyi
|
7
|
|
Noble
|
3
|
56
|
|
Rulande
|
1
|
49
|
REQUIRED: You are required to setup a table which includes the following details:
|
Staff Name
|
Level
|
Hours worked
|
Hourly pay
|
Overtime hours (if any)
|
Regular pay
|
Overtime pay
|
Total pay
|
|
Typed
|
Must use a formula in this column
|
Please note
A. The above table must be sorted by level first and then by staff name.
B. Marks will be awarded for presentation and correct use of cell referencing, use of functions and formulae (e.g. VLOOKUP, IF, Sum)
C. All columns must be totalled where appropriate.