REQUIRED:
This is an individual assignment and must be completed using Microsoft Excel.
These introductory excel skills you learn will be used in later units and in your
professional life. Employers expect students to have good worksheet skills.
Each exercise must be completed on a separate worksheet. Marks will not be
awarded for any manual calculations. Ensure you enter all required data at the
beginning of your answer and use the required formulas and functions to
present your answer. All dollar amounts must be provided in currency format
and 2 decimal places. You will also be assessed on professional presentation.
Use tables, borders and colours where appropriate.
Submit this assignment as an electronic assignment via the CISM4000 subject
page on the student portal. Instructions are on the front page of the portal.
Ensure that your work can be clearly identified with your student ID and FULL
student name. Late penalties will apply for all late submissions.
Any work which has been copied or shared between students will result in a
Fail grade. This assignment must be your own work.

_______________________________________________________________________

Question 1 (6 marks)
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 (20 marks)

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 (7 marks)

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.00$
Brown 500.00$
Smith 800.00$
Palmer 2,500.00$
Adams 6,500.00$
Peters 8,000.00$
Wang 1,900.00$
Chen 5,900.00$
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 (42 marks)

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.