Reference no: EM13669089

Question 1:

DVD Wonderland 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: Transformers - Age of Extinction, Hobbit 2, and Guardians of the Galaxy

Weekly Hire:

These titles consist of: Spiderman 2, Maleficent, and If I Stay

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

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 January and March 2015:

Month	Strawberries & Cream	Mint Choc Chip	Rum and Raisin
January	3500	1500	4000
February	6500	2500	7000
March	4500	1800	3600

The cost to produce each tub of gelato is as follows:

Strawberries & Cream    $6.70
Mint Choc Chip             $5.50
Rum and Raisin             $9.20

The selling price is determined by applying a mark-up percentage on cost as follows:
Strawberries & Cream gelato is marked up by 80%
Mint Choc Chip is marked up by 120%
Rum and Raisin is marked up by 75%

Required:

1. Using the data above, prepare a Revenue table for gelato, for each month.

2. Using the data above, prepare a Profit Summary per month, for each gelato flavour.

Question 3:

George's Vegetables Online is an online vegetable 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:

Category Lower Limit     Sales Category

$0-$999                         Poor
$1,000-$4,999                Average
$5,000-$6,999                Good
$7,000 or more              Excellent

Sales for the year ended 30^th June 2014 consisted of:

Customer Name       Amount

Jackson                 $1,100.00

Brians                    $500.00

Swanson                $ 800.00

Pullman                  $2,500.00  

Papadopolis            $6,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 Bright Lights 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:

Classification Levels
Level	Hourly pay
1 2 3 4 5 6	$        15.00
	$        20.00
	$        25.00
	$        37.50
	$        40.00
	$        45.00

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

Bright Lights Ltd has the following employees:

TABLE B:

Employee name	Classification Level
Joe Baird	5
Penny Styles	4
Pip Smith	6
John Mignon	2
Carol Jamison	1
Paula Rang	2
Tony Richmond	4
Troy Walters	1
Debbie Strong	3
Elsa Wang	6

The hours worked for the week ended 30th July, 2014 are as follows:

TABLE C:

Employee name	Hours worked
Troy Walters	57
Carol Jamison	39
Debbie Strong	40
Elsa Wang	52
John Mignon	30
Joe Baird	45
Paula Rang	45
Pip Smith	48
Penny Styles	52
Tony Richmond	54

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

Overfime Hmrt

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 the IF function to calculate the overtime amount per employee.

9. Use a formula to calculate the Total Pay.