Reference no: EM13137724

Question 1 
DVD Breezy 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: Deep Sea, Death by Chocolate, and Roaming Dinosaurs 

Weekly Hire: 
These titles consist of: Beaches, The Money Pit, and One Fine Day 

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 

Ensure your drop down list shows each movie with a different corresponding hire day. 

________________________________________________________________________ 

Question 2 

Peters Dairy is an ice cream business. The following table shows the expected number 
of ice-creams to be sold between January and March 2014: 


Month Magnum Splice Cornetto 
January 150 50 200 
February 300 75 350 
March 200 40 180 

The cost of each ice-cream is as follows: 
Magnum $15 
Splice $10 
Cornetto $20 

The selling price is determined by applying a mark-up percentage on cost as follows: 
Magnum ice-creams are marked up by 100% 
Splice are marked up by 50% 
Cornetto are marked up by 75% 

Required: 
1. Using the data above, prepare a Revenue table for ice-cream, for each month. 
(10 marks) 
2. Using the data above, prepare a Profit Summary per month, for each ice-cream. 
(10 marks) 

Note: Only enter the information once as data. Both tables must be all formula driven. 
Use absolute referencing where appropriate. 

_______________________________________________________________________ 

Question 3 

Greenway Vegies is an online Fruit and Vegetable 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: 

Category 
Sales 
Category 
Lower Limit 
$0-$999 Poor 
$1,000-$4,999 Average 
$5,000-$6,999 Good 
$7,000 or more Excellent 
Sales for the year ended 30 
Customer Name Amount 
Jones 1,100.00mce_markernbsp;
Brown 500.00mce_markernbsp;
Smith 800.00mce_markernbsp;
Palmer 2,500.00mce_markernbsp;
Adams 6,500.00mce_markernbsp;
Peters 8,000.00mce_markernbsp;
Wang 1,900.00mce_markernbsp;
Chen 5,900.00mce_markernbsp;
th 
June 2013 consisted of: 

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 Coban Ltd, a public electronic company. 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: 

The overtime hourly rate is $80 per hour for ALL employees, regardless of their classification 
level. 

Coban has the following employees: 

Employee name Classification Level 
John Blair 5 
Peter Jones 4 
Penny Sushi 6 
Jenny Mistletoe 2 
Carol Spencer 1 
Paul Wang 2 
Tony Romeo 4 
Bob Wilson 1 
Diana Rossi 3 
Elvis Costanzo 6 

Classification Levels 
Level Hourly pay 
1 $ 30.00 
2 $ 40.00 
3 $ 55.00 
4 $ 65.00 
5 $ 70.00 
6 $ 75.00 


________________________________________________________________________ 



The hours worked for the week ended 30th November, 2013 are as follows: 

Employee name Hours 
worked 
John Blair 42 
Peter Jones 50 
Penny Sushi 46 
Jenny Mistletoe 25 
Carol Spencer 37 
Paul Wang 45 
Tony Romeo 42 
Bob Wilson 35 
Diana Rossi 38 
Elvis Costanzo 50 

Required: 

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 

1. Use the IF function to calculate the Regular Hours column. (11 marks) 
2. Use a formula to calculate the Overtime hours. (3 marks) 
3. Set up a table and use the VLOOKUP function to determine the Hourly Pay for each employee. 
(10 marks) 
4. Use a formula to calculate the Base Amount for each employee. (4 marks) 
5. Use the IF function to calculate the overtime amount per employee. 
6. Use a formula to calculate the Total Pay. 

Marks will be awarded for presentation and correct use of cell referencing, use of functions and 
formulas. No marks will be awarded if the correct function or formula is not used or if data has 
been manually entered.