Question 1-
DVD Kiosk is a retail DVD shop. The owner would like to set up an online order form, to allow customers to select the movie they would like and which night of the week they would prefer.
The categories are as follows:
New Releases: These titles consist of: The Star Wars, Inside Out, Bring him home
Weekly Hire: These titles consist of: The Grand Hotel, Birdman, The Lego Movie Required:
Set up a drop down list for each category of DVD listed above and a drop down list to select the day of the week for hire.
Your column headings for your drop down lists should be:
|
New Releases Hire Day Weekly DVDs Hire Day
|
Question 2-
Pizza-to-go is a frozen pizza business specialising in large pizzas for shops and cafes. The following table shows the expected number of pizzas to be sold between April and June 2016:
|
Month
|
Ham & Pineapple
|
Supreme
|
Tandoori Chicken
|
|
April
|
2700
|
1500
|
3200
|
|
May
|
5500
|
2500
|
6000
|
|
June
|
3500
|
1900
|
2700
|
The cost to produce each pizza is as follows:
Ham & Pineapple $4.50
Supreme $3.20
Tandoori Chicken $5.40
The selling price is determined by applying a mark-up percentage on cost as follows:
Ham & Pineapple pizza is marked up by 70%
Supreme pizza is marked up by 110%
Tandoori Chicken pizza is marked up by 65%
Required:
1. Using the data above, prepare a Revenue table for pizza, for each month.
2. Using the data above, prepare a Profit Summary per month, for each pizza type.
Note: Only enter the information once as data. Both tables must be all formula driven. Use absolute referencing where appropriate. 4 marks out of the total 20 marks will be awarded for presentation i.e. borders, shading, titles and alignment.
Question 3-
Delite Fruit Online is an online fruit delivery service. The manager would like you to set up a LOOKUP table to summarise his monthly sales by category.
The category limits are as follows:
|
Sales Range
|
Sales Category
|
|
$0-$999
|
Poor
|
|
$1,000-$5,699
|
Average
|
|
$5,700-$7,499
|
Good
|
|
$7,500 or more
|
Excellent
|
Sales for the year ended 30th June 2015 consisted of:
|
Customer Name
|
Amount
|
|
Jackson
|
$ 1,100.00
|
|
Brians
|
$ 500.00
|
|
Swanson
|
$ 800.00
|
|
Pullman
|
$ 2,500.00
|
|
Papadopolis
|
$ 7,500.00
|
|
Peng
|
$ 8,000.00
|
|
Wang
|
$ 1,900.00
|
|
Clifford
|
$ 5,900.00
|
Required: Using VLOOK up function, set up a LOOKUP table to classify the above sales by category as listed in the above table. Your table should be sorted in alphabetical order.
Question 4-
The following employees work for Cleardrinks Galore Ltd, a public company specialising in production of soft drinks and flavoured water. The employees are paid an hourly rate, based on their Job Classification level. If an employee works more than 38 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:
|
Classification Levels
|
|
Level
|
Hourly pay
|
|
1
|
$ 30.00
|
|
2
|
$ 40.00
|
|
3
|
$ 50.00
|
|
4
|
$ 65.00
|
|
5
|
$ 75.00
|
|
6
|
$ 85.00
|
The overtime hourly rate is $100 per hour for ALL employees, regardless of their classification level.
Cleardrinks Galore Ltd has the following employees:
TABLE B:
|
Employee name
|
Classification Level
|
|
Tim Holton
|
5
|
|
Ricky Mortini
|
4
|
|
Jennifer Leepoz
|
6
|
|
Selina Geemak
|
2
|
|
Willard Smith
|
1
|
|
Russell Creak
|
2
|
|
Rafael Nooderly
|
4
|
|
Novak Djoker
|
1
|
|
Lara Bangle
|
3
|
|
Kath Hudson
|
6
|
The hours worked for the week ended 30th Jan, 2016 are as follows:
TABLE C:
|
Employee name
|
Hours worked
|
|
Novak Djoker
|
37
|
|
Willard Smith
|
39
|
|
Lara Bangle
|
40
|
|
Kath Hudson
|
52
|
|
Selina Geemak
|
30
|
|
Tim Holton
|
35
|
|
Russell Creak
|
47
|
|
Jennifer Leepoz
|
48
|
|
Ricky Mortini
|
55
|
|
Rafael Nooderly
|
45
|
Required:
1. Copy Table A into your worksheet. Set up a 'range' for the data. Name the range 'classification level'. 2. Copy Table B to the same worksheet.
a. Add a third column titled 'hourly pay'.
b. Use the VLOOKUP function to pick up the correct hourly pay rate for each employee from the 'classification level' range set up in part 1 above.
c. Create a 'range' for the data in these 3 columns. Name the range 'pay rate table'. This range will be used in Question 6 below.
3. Using Table C data and keeping employee order unchanged, set up a payroll table to calculate the total payroll for each employee.
Your main payroll table should have the following headings:
|
Employee Name
|
Hours worked
|
Regular Hours
|
Overtime Hours
|
Hourly Pay
|
Base Amount
|
Overtime
|
Total Pay
|
4. Use the IF function to calculate the Regular Hours column.
5. Use a formula to calculate the Overtime hours.
6. Use the VLOOKUP function with reference to the range created in Question 3 part (c) to determine the Hourly Pay for each employee.
7. Use a formula to calculate the Base Amount for each employee.
8. Use a suitable function to calculate the overtime amount per employee. Using another formula, identify the employee that earned maximum overtime.
9. Use a formula to calculate the Total Pay.
10. All columns must be totalled as required.
11. Correct use of cell referencing, use of functions and formulas. Professional presentation.